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

使用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,以及解决办法

?

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

?

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

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

?

查询方法是：

1. String?sql?=???
2. "select?id?XXX_ID??from?t_tab";??
3. List
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()??
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连接

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. ???
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 里面的

?

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?columnName?=?new?ArrayList();??
5. ????????????for?(int?i?=?1;?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. ??????????
7. ????????if?(name?!=?null?&&?name.length()?==?0)?{??
8. ????????????return?getField(column).getName();??
9. ????????}??
10. ??????????
11. ????????return?name;??
12. ????}??

修改为:

?

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();
}

?

?