威凡网全力打造:网页编程、软件开发编程、平面设计、服务器端开发、操作系统等在线学习平台!学编程,上威凡网!
数据库>> Mysql Sqlserver Oracle SQlite Access Sybase SQL其他
当前位置:首页 > 数据库 > Mysql
上一节 下一节
 使用Hibernate+MySql+nativeSQL的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,就出了错(同样的数据结构和数据)。

?

查询方法是:

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

    ?

    ?


    申明:本教程内容由威凡网编辑整理并提供IT程序员分享学习,如文中有侵权行为,请与站长联系(QQ:254677821)!
    上一节 下一节
    相关教程  
    其他教程  
    Mysql
    Sqlserver
    Oracle
    SQlite
    Access
    Sybase
    SQL其他

    违法和不良信息举报中心】邮箱:254677821@qq.com
    Copyright©威凡网 版权所有 苏ICP备2023020142号
    站长QQ:254677821