当前位置:Gxlcms > mysql > 使用Hibernate+MySql+nativeSQL的BUG,以及解决方法

使用Hibernate+MySql+nativeSQL的BUG,以及解决方法

时间:2021-07-01 10:21:17 帮助过:11人阅读

使用Hibernate+MySql+native SQL的BUG,以及解决办法 ? 使用Hibernate+MySql+native SQL的BUG,以及解决办法 ? 转载自 http://blog.csdn.net/exsuns/article/details/5264125 ? 本来是mssql+hibernate+native SQL 应用的很和谐 但是到了把mssql换成mysql,就出

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

?

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

?

转载自 http://blog.csdn.net/exsuns/article/details/5264125

?

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

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

?

查询方法是:

[java] view plaincopy
  1. String?sql?=???
  2. "select?id?XXX_ID??from?t_tab";??
  3. List?list?=?session.createSQLQuery(sql)??
  4. .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)??
  5. .list();??

?


错误信息:

[css] view plaincopy
  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()??
  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()??
  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连接

[java] view plaincopy
  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里发现的这一段

?

[java] view plaincopy
  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. ???
  13. ????????//这个方法中的alias?为"id"而不是XXX_ID??
  14. ????????public?Object?extract(??
  15. ????????????????Object[]?data,??
  16. ????????????????ResultSet?resultSet,??
  17. ????????????????SessionImplementor?session)?throws?SQLException,?HibernateException?{??
  18. ????????????return?type.nullSafeGet(?resultSet,?alias,?session,?null?);??
  19. ????????}??
  20. ...........??
  21. ...........??
  22. ...........??

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

而不是 select id XXX_ID , name from tab

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

?

?

?

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

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

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

?

[java] view plaincopy
  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

?

[java] view plaincopy
  1. ResultSet?rs=stmt.executeQuery(sql);??
  2. ????????????ResultSetMetaData?rsmd?=?rs.getMetaData();??
  3. ????????????int?columnCount?=?rsmd.getColumnCount();??
  4. ????????????List?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

中的

?

[java] view plaincopy
  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. ??????????
  7. ????????if?(name?!=?null?&&?name.length()?==?0)?{??
  8. ????????????return?getField(column).getName();??
  9. ????????}??
  10. ??????????
  11. ????????return?name;??
  12. ????}??

修改为:

?

[java] view plaincopy
  1. public?String?getColumnName(int?column)?throws?SQLException?{??
  2. ????????return?getColumnLable(column);??
  3. ????}??

?

然后把JDBC重新打包一下

?

再运行就OK了.

最佳解决方法:

?

在jdbc.url中追加mysql参数 &useOldAliasMetadataBehavior=true 就可以解决。
if (this.useOldAliasBehavior) {
return getField(column).getName();
}

?

?

人气教程排行