使用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
string?sql?=???
"select?id?xxx_id??from?t_tab";??
list?list?=?session.createsqlquery(sql)??
.setresulttransformer(transformers.alias_to_entity_map)??
.list();??
?
错误信息：
[css] view plaincopy
org.hibernate.exception.sqlgrammarexception:?could?not?execute?query??
????at?org.hibernate.exception.sqlstateconverter.convert(sqlstateconverter.java:90)??
????at?org.hibernate.exception.jdbcexceptionhelper.convert(jdbcexceptionhelper.java:66)??
????at?org.hibernate.loader.loader.dolist(loader.java:2231)??
????at?org.hibernate.loader.loader.listignorequerycache(loader.java:2125)??
????at?org.hibernate.loader.loader.list(loader.java:2120)??
????at?org.hibernate.loader.custom.customloader.list(customloader.java:312)??
????at?org.hibernate.impl.sessionimpl.listcustomquery(sessionimpl.java:1722)??
????at?org.hibernate.impl.abstractsessionimpl.list(abstractsessionimpl.java:165)??
????at?org.hibernate.impl.sqlqueryimpl.list(sqlqueryimpl.java:175)??
????at?com.exsun.common.dao.basdaoimpl.findbysql(basdaoimpl.java:173)??
????at?com.exsun.arms.service.employeesalaryservice.getlist(employeesalaryservice.java:32)??
????at?com.exsun.arms.service.employeesalaryservice$$fastclassbycglib$$2d81000f.invoke()??
????at?net.sf.cglib.proxy.methodproxy.invoke(methodproxy.java:149)??
????at?org.springframework.aop.framework.cglib2aopproxy$cglibmethodinvocation.invokejoinpoint(cglib2aopproxy.java:700)??
????at?org.springframework.aop.framework.reflectivemethodinvocation.proceed(reflectivemethodinvocation.java:149)??
????at?org.springframework.transaction.interceptor.transactioninterceptor.invoke(transactioninterceptor.java:106)??
????at?org.springframework.aop.framework.reflectivemethodinvocation.proceed(reflectivemethodinvocation.java:171)??
????at?org.springframework.aop.interceptor.exposeinvocationinterceptor.invoke(exposeinvocationinterceptor.java:89)??
????at?org.springframework.aop.framework.reflectivemethodinvocation.proceed(reflectivemethodinvocation.java:171)??
????at?org.springframework.aop.framework.cglib2aopproxy$dynamicadvisedinterceptor.intercept(cglib2aopproxy.java:635)??
????at?com.exsun.arms.service.employeesalaryservice$$enhancerbycglib$$1e3e6d9f.getlist()??
????at?com.exsun.arms.action.employeesalaryaction.getlist(employeesalaryaction.java:110)??
????at?sun.reflect.nativemethodaccessorimpl.invoke0(native?method)??
????at?sun.reflect.nativemethodaccessorimpl.invoke(nativemethodaccessorimpl.java:39)??
????at?sun.reflect.delegatingmethodaccessorimpl.invoke(delegatingmethodaccessorimpl.java:25)??
????at?java.lang.reflect.method.invoke(method.java:597)??
????at?org.apache.struts.actions.dispatchaction.dispatchmethod(dispatchaction.java:269)??
????at?org.apache.struts.actions.dispatchaction.execute(dispatchaction.java:170)??
????at?org.springframework.web.struts.delegatingactionproxy.execute(delegatingactionproxy.java:110)??
????at?org.apache.struts.chain.commands.servlet.executeaction.execute(executeaction.java:58)??
????at?org.apache.struts.chain.commands.abstractexecuteaction.execute(abstractexecuteaction.java:67)??
????at?org.apache.struts.chain.commands.actioncommandbase.execute(actioncommandbase.java:51)??
????at?org.apache.commons.chain.impl.chainbase.execute(chainbase.java:191)??
????at?org.apache.commons.chain.generic.lookupcommand.execute(lookupcommand.java:305)??
????at?org.apache.commons.chain.impl.chainbase.execute(chainbase.java:191)??
????at?org.apache.struts.chain.composablerequestprocessor.process(composablerequestprocessor.java:283)??
????at?org.apache.struts.action.actionservlet.process(actionservlet.java:1913)??
????at?org.apache.struts.action.actionservlet.doget(actionservlet.java:449)??
????at?javax.servlet.http.httpservlet.service(httpservlet.java:627)??
????at?javax.servlet.http.httpservlet.service(httpservlet.java:729)??
????at?org.apache.catalina.core.applicationfilterchain.internaldofilter(applicationfilterchain.java:269)??
????at?org.apache.catalina.core.applicationfilterchain.dofilter(applicationfilterchain.java:188)??
????at?org.springframework.orm.hibernate3.support.opensessioninviewfilter.dofilterinternal(opensessioninviewfilter.java:198)??
????at?org.springframework.web.filter.onceperrequestfilter.dofilter(onceperrequestfilter.java:76)??
????at?org.apache.catalina.core.applicationfilterchain.internaldofilter(applicationfilterchain.java:215)??
????at?org.apache.catalina.core.applicationfilterchain.dofilter(applicationfilterchain.java:188)??
????at?com.exsun.common.util.menufilter.dofilter(menufilter.java:103)??
????at?org.apache.catalina.core.applicationfilterchain.internaldofilter(applicationfilterchain.java:215)??
????at?org.apache.catalina.core.applicationfilterchain.dofilter(applicationfilterchain.java:188)??
????at?org.springframework.web.filter.characterencodingfilter.dofilterinternal(characterencodingfilter.java:96)??
????at?org.springframework.web.filter.onceperrequestfilter.dofilter(onceperrequestfilter.java:76)??
????at?org.apache.catalina.core.applicationfilterchain.internaldofilter(applicationfilterchain.java:215)??
????at?org.apache.catalina.core.applicationfilterchain.dofilter(applicationfilterchain.java:188)??
????at?org.apache.catalina.core.standardwrappervalve.invoke(standardwrappervalve.java:213)??
????at?org.apache.catalina.core.standardcontextvalve.invoke(standardcontextvalve.java:172)??
????at?org.apache.catalina.core.standardhostvalve.invoke(standardhostvalve.java:127)??
????at?org.apache.catalina.valves.errorreportvalve.invoke(errorreportvalve.java:117)??
????at?org.apache.catalina.core.standardenginevalve.invoke(standardenginevalve.java:108)??
????at?org.apache.catalina.connector.coyoteadapter.service(coyoteadapter.java:174)??
????at?org.apache.coyote.http11.http11processor.process(http11processor.java:873)??
????at?org.apache.coyote.http11.http11baseprotocol$http11connectionhandler.processconnection(http11baseprotocol.java:665)??
????at?org.apache.tomcat.util.net.pooltcpendpoint.processsocket(pooltcpendpoint.java:528)??
????at?org.apache.tomcat.util.net.leaderfollowerworkerthread.runit(leaderfollowerworkerthread.java:81)??
????at?org.apache.tomcat.util.threads.threadpool$controlrunnable.run(threadpool.java:689)??
????at?java.lang.thread.run(thread.java:619)??
caused?by:?java.sql.sqlexception:?column?'id'?not?found.??
????at?com.mysql.jdbc.sqlerror.createsqlexception(sqlerror.java:1072)??
????at?com.mysql.jdbc.sqlerror.createsqlexception(sqlerror.java:986)??
????at?com.mysql.jdbc.sqlerror.createsqlexception(sqlerror.java:981)??
????at?com.mysql.jdbc.sqlerror.createsqlexception(sqlerror.java:926)??
????at?com.mysql.jdbc.resultsetimpl.findcolumn(resultsetimpl.java:1144)??
????at?com.mysql.jdbc.resultsetimpl.getstring(resultsetimpl.java:5616)??
????at?org.hibernate.type.stringtype.get(stringtype.java:41)??
????at?org.hibernate.type.nullabletype.nullsafeget(nullabletype.java:184)??
????at?org.hibernate.type.nullabletype.nullsafeget(nullabletype.java:210)??
????at?org.hibernate.loader.custom.customloader$scalarresultcolumnprocessor.extract(customloader.java:497)??
????at?org.hibernate.loader.custom.customloader$resultrowprocessor.buildresultrow(customloader.java:443)??
????at?org.hibernate.loader.custom.customloader.getresultcolumnorrow(customloader.java:340)??
????at?org.hibernate.loader.loader.getrowfromresultset(loader.java:629)??
????at?org.hibernate.loader.loader.doquery(loader.java:724)??
????at?org.hibernate.loader.loader.doqueryandinitializenonlazycollections(loader.java:259)??
????at?org.hibernate.loader.loader.dolist(loader.java:2228)??
????...?62?more??
?
?
看到最后caused by: java.sql.sqlexception:
所以我试试用jdbc连接
[java] view plaincopy
public?class?testmysqljdbc?{??
????public?static?void?main(string[]?args)?throws?exception,?illegalaccessexception,?classnotfoundexception?{??
????????class.forname("com.mysql.jdbc.driver").newinstance();??
???????????connection?con=java.sql.drivermanager.getconnection("jdbc:mysql://localhost:3306/arms?useunicode=true&characterencoding=utf8&","root","");??
???????????statement?stmt=con.createstatement();??
????????????resultset?rs=stmt.executequery("select?id?xxx_id,name??from?t_tab");??
????????????while(rs.next())??
????????????{??
???????????????system.out.printf("id:%s___name:%s/n",rs.getstring("xxx_id"),rs.getstring("name"));??
????????????}??
????????????rs.close();??
????????????stmt.close();??
????????????con.close();??
????}??
}??
?
可以很正常的运行。
?
?
后来仔细又看了一下hibernate的执行错误
caused by: java.sql.sqlexception: column 'id' not found.
是id 找不到 不是xxx_id 找不到。这意思貌似hibernate就没按别名alias 来取值
跟踪了一下hibernate
其中在customer里发现的这一段
?
[java] view plaincopy
public?class?scalarresultcolumnprocessor?implements?resultcolumnprocessor?{??
????????private?int?position?=?-1;??
????????private?string?alias;??
????????private?type?type;??
????????public?scalarresultcolumnprocessor(int?position)?{??
????????????this.position?=?position;??
????????}??
????????public?scalarresultcolumnprocessor(string?alias,?type?type)?{??
????????????this.alias?=?alias;??
????????????this.type?=?type;??
????????}??
???
????????//这个方法中的alias?为"id"而不是xxx_id??
????????public?object?extract(??
????????????????object[]?data,??
????????????????resultset?resultset,??
????????????????sessionimplementor?session)?throws?sqlexception,?hibernateexception?{??
????????????return?type.nullsafeget(?resultset,?alias,?session,?null?);??
????????}??
...........??
...........??
...........??
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
public?void?performdiscovery(metadata?metadata,?list?types,?list?aliases)?throws?sqlexception?{??
????????????if?(?alias?==?null?)?{??
????????????????alias?=?metadata.getcolumnname(?position?);??
????????????}??
????????????else?if?(?position?0?)?{??
????????????????position?=?metadata.resolvecolumnposition(?alias?);??
????????????}??
????????????if?(?type?==?null?)?{??
????????????????type?=?metadata.gethibernatetype(?position?);??
????????????}??
????????????types.add(?type?);??
????????????aliases.add(?alias?);??
????????}??
?
这里就是拼装将来要根据列名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
resultset?rs=stmt.executequery(sql);??
????????????resultsetmetadata?rsmd?=?rs.getmetadata();??
????????????int?columncount?=?rsmd.getcolumncount();??
????????????list?columnname?=?new?arraylist();??
????????????for?(int?i?=?1;?i?<=?columncount;?i++)?{??
????????????????string?tmp?=?rsmd.getcolumnname(i)?+?"___"?+?rsmd.getcolumnlabel(i);??
????????????????system.out.println(tmp);??
}??
三个实现,结果都是一样的.
?
mysql的jdbc要获取alias只能用getcolumnlable,不能用getcolumnname
而hibernate取字段名称的时候就只用 getcolumnname.
解决办法有两个,一个是改hibernate,再不就得改mysql.
怕hibernate对别的数据库实现有影响
所以就拿mysql的jdbc驱动开刀了.
把com.mysql.jdbc.resultsetmetadata
中的
?
[java] view plaincopy
public?string?getcolumnname(int?column)?throws?sqlexception?{??
????????if?(this.useoldaliasbehavior)?{??
????????????return?getfield(column).getname();??
????}??
????????string?name?=?getfield(column).getnamenoaliases();??
??????????
????????if?(name?!=?null?&&?name.length()?==?0)?{??
????????????return?getfield(column).getname();??
????????}??
??????????
????????return?name;??
????}??
修改为:
?
[java] view plaincopy
public?string?getcolumnname(int?column)?throws?sqlexception?{??
????????return?getcolumnlable(column);??
????}??
?
然后把jdbc重新打包一下
?
再运行就ok了.
最佳解决方法：
?
在jdbc.url中追加mysql参数 &useoldaliasmetadatabehavior=true 就可以解决。
if (this.useoldaliasbehavior) {
return getfield(column).getname();
}
?
?
【说明】：
本文章由站长整理发布，文章内容不代表本站观点，如文中有侵权行为，请与本站客服联系(QQ:254677821)！