使用Hibernate+MySql+native SQL的BUG,以及解决办法

2022-09-16 09:28:46

 

本来是mssql+hibernate+native SQL 应用的很和谐

但是到了把mssql换成mysql，就出了错(同样的数据结构和数据)。

查询方法是：

  1. String sql =
  2. "select id XXX_ID  from t_tab";
  3. List<Map> list = session.createSQLQuery(sql)
  4. .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
  5. .list();

错误信息：

  1. org.hibernate.exception.SQLGrammarException: could not execute query
  2. at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:90)
  3. at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
  4. at org.hibernate.loader.Loader.doList(Loader.java:2231)
  5. at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2125)
  6. at org.hibernate.loader.Loader.list(Loader.java:2120)
  7. at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:312)
  8. at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1722)
  9. at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:165)
  10. at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:175)
  11. at com.exsun.common.dao.BasDaoImpl.findBySql(BasDaoImpl.java:173)
  12. at com.exsun.arms.service.EmployeesalaryService.getList(EmployeesalaryService.java:32)
  13. at com.exsun.arms.service.EmployeesalaryService$$FastClassByCGLIB$$2d81000f.invoke(<generated>)
  14. at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:149)
  15. at org.springframework.aop.framework.Cglib2AopProxy$CglibMethodInvocation.invokeJoinpoint(Cglib2AopProxy.java:700)
  16. at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)
  17. at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:106)
  18. at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
  19. at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:89)
  20. at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
  21. at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:635)
  22. at com.exsun.arms.service.EmployeesalaryService$$EnhancerByCGLIB$$1e3e6d9f.getList(<generated>)
  23. at com.exsun.arms.action.EmployeesalaryAction.getList(EmployeesalaryAction.java:110)
  24. at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
  25. at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
  26. at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
  27. at java.lang.reflect.Method.invoke(Method.java:597)
  28. at org.apache.struts.actions.DispatchAction.dispatchMethod(DispatchAction.java:269)
  29. at org.apache.struts.actions.DispatchAction.execute(DispatchAction.java:170)
  30. at org.springframework.web.struts.DelegatingActionProxy.execute(DelegatingActionProxy.java:110)
  31. at org.apache.struts.chain.commands.servlet.ExecuteAction.execute(ExecuteAction.java:58)
  32. at org.apache.struts.chain.commands.AbstractExecuteAction.execute(AbstractExecuteAction.java:67)
  33. at org.apache.struts.chain.commands.ActionCommandBase.execute(ActionCommandBase.java:51)
  34. at org.apache.commons.chain.impl.ChainBase.execute(ChainBase.java:191)
  35. at org.apache.commons.chain.generic.LookupCommand.execute(LookupCommand.java:305)
  36. at org.apache.commons.chain.impl.ChainBase.execute(ChainBase.java:191)
  37. at org.apache.struts.chain.ComposableRequestProcessor.process(ComposableRequestProcessor.java:283)
  38. at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1913)
  39. at org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:449)
  40. at javax.servlet.http.HttpServlet.service(HttpServlet.java:627)
  41. at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
  42. at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:269)
  43. at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188)
  44. at org.springframework.orm.hibernate3.support.OpenSessionInViewFilter.doFilterInternal(OpenSessionInViewFilter.java:198)
  45. at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76)
  46. at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:215)
  47. at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188)
  48. at com.exsun.common.util.MenuFilter.doFilter(MenuFilter.java:103)
  49. at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:215)
  50. at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188)
  51. at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:96)
  52. at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76)
  53. at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:215)
  54. at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188)
  55. at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
  56. at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:172)
  57. at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
  58. at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:117)
  59. at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:108)
  60. at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:174)
  61. at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:873)
  62. at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:665)
  63. at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:528)
  64. at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:81)
  65. at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:689)
  66. at java.lang.Thread.run(Thread.java:619)
  67. Caused by: java.sql.SQLException: Column 'id' not found.
  68. at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1072)
  69. at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:986)
  70. at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:981)
  71. at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:926)
  72. at com.mysql.jdbc.ResultSetImpl.findColumn(ResultSetImpl.java:1144)
  73. at com.mysql.jdbc.ResultSetImpl.getString(ResultSetImpl.java:5616)
  74. at org.hibernate.type.StringType.get(StringType.java:41)
  75. at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:184)
  76. at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:210)
  77. at org.hibernate.loader.custom.CustomLoader$ScalarResultColumnProcessor.extract(CustomLoader.java:497)
  78. at org.hibernate.loader.custom.CustomLoader$ResultRowProcessor.buildResultRow(CustomLoader.java:443)
  79. at org.hibernate.loader.custom.CustomLoader.getResultColumnOrRow(CustomLoader.java:340)
  80. at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:629)
  81. at org.hibernate.loader.Loader.doQuery(Loader.java:724)
  82. at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259)
  83. at org.hibernate.loader.Loader.doList(Loader.java:2228)
  84. ... 62 more

看到最后Caused by: java.sql.SQLException:

所以我试试用JDBC连接

  1. public class TestMySqlJDBC {
  2. public static void main(String[] args) throws Exception, IllegalAccessException, ClassNotFoundException {
  3. Class.forName("com.mysql.jdbc.Driver").newInstance();
  4. Connection con=java.sql.DriverManager.getConnection("jdbc:mysql://localhost:3306/arms?useUnicode=true&characterEncoding=utf8&","root","");
  5. Statement stmt=con.createStatement();
  6. ResultSet rs=stmt.executeQuery("select id XXX_ID,name  from t_tab");
  7. while(rs.next())
  8. {
  9. System.out.printf("id:%s___name:%s/n",rs.getString("XXX_ID"),rs.getString("name"));
  10. }
  11. rs.close();
  12. stmt.close();
  13. con.close();
  14. }
  15. }

可以很正常的运行。

后来仔细又看了一下hibernate的执行错误

Caused by: java.sql.SQLException: Column 'id' not found.

是ID 找不到 不是XXX_ID 找不到。这意思貌似hibernate就没按别名alias 来取值

跟踪了一下hibernate

其中在Customer里发现的这一段

  1. public class ScalarResultColumnProcessor implements ResultColumnProcessor {
  2. private int position = -1;
  3. private String alias;
  4. private Type type;
  5. public ScalarResultColumnProcessor(int position) {
  6. this.position = position;
  7. }
  8. public ScalarResultColumnProcessor(String alias, Type type) {
  9. this.alias = alias;
  10. this.type = type;
  11. }
  12. //这个方法中的alias 为"id"而不是XXX_ID
  13. public Object extract(
  14. Object[] data,
  15. ResultSet resultSet,
  16. SessionImplementor session) throws SQLException, HibernateException {
  17. return type.nullSafeGet( resultSet, alias, session, null );
  18. }
  19. ...........
  20. ...........
  21. ...........

hibernate是按照select id,name from tab来解释

而不是 select id XXX_ID , name from tab

这里跟到的数据果然是Hibernate把alias给忽略了。。。

-------------------------------------

继续跟踪hibernate代码.根据调用栈,单步跟踪很快找到

org.hibernate.loader.custom.CustomLoader$ScalarResultColumnProcessor 里面的

  1. public void performDiscovery(Metadata metadata, List types, List aliases) throws SQLException {
  2. if ( alias == null ) {
  3. alias = metadata.getColumnName( position );
  4. }
  5. else if ( position < 0 ) {
  6. position = metadata.resolveColumnPosition( alias );
  7. }
  8. if ( type == null ) {
  9. type = metadata.getHibernateType( position );
  10. }
  11. types.add( type );
  12. aliases.add( alias );
  13. }

这里就是拼装将来要根据列名get出来的结果的地方

alias = metadata.getColumnName( position )

getColumnName 跟进去就是JDBC的实现.

跟踪可以看到.从这里get出来的alias不是真正的aliasName,而是originalColumnName原始列名.

后来试着修改了hibernate各种方言的设置：MySQLDialect,MySQL5Dialect,MySQL5InnoDBDialect等等还是不行。

我用JDBC试了下

mysql-connector-java-5.1.9-bin.jar

mysql-connector-java-5.1.10-bin.jar

mysql-connector-java-5.1.11-bin.jar

  1. ResultSet rs=stmt.executeQuery(sql);
  2. ResultSetMetaData rsmd = rs.getMetaData();
  3. int columnCount = rsmd.getColumnCount();
  4. List<String> columnName = new ArrayList();
  5. for (int i = 1; i <= columnCount; i++) {
  6. String tmp = rsmd.getColumnName(i) + "___" + rsmd.getColumnLabel(i);
  7. System.out.println(tmp);
  8. }

三个实现,结果都是一样的.

mysql的JDBC要获取alias只能用getColumnLable,不能用getColumnName

而Hibernate取字段名称的时候就只用 getColumnName.

解决办法有两个,一个是改hibernate,再不就得改mysql.

怕Hibernate对别的数据库实现有影响

所以就拿mysql的JDBC驱动开刀了.

把com.mysql.jdbc.ResultSetMetaData

中的

  1. public String getColumnName(int column) throws SQLException {
  2. if (this.useOldAliasBehavior) {
  3. return getField(column).getName();
  4. }
  5. String name = getField(column).getNameNoAliases();
  6. if (name != null && name.length() == 0) {
  7. return getField(column).getName();
  8. }
  9. return name;
  10. }

修改为:

  1. public String getColumnName(int column) throws SQLException {
  2. return getColumnLable(column);
  3. }

然后把JDBC重新打包一下

再运行就OK了.

上一篇：有向无环图的应用—AOV网 和 拓扑排序


下一篇：在hibernate中用sql语句