mysql 别名 hibernate_hibernatesqlQuerymysql别名问题



项目环境：hibernate3.6.1 mysql5.7.3 驱动：mysql-connector-java-5.1.26

问题描述：项目是从一个sqlserver 数据库翻转到mysql数据库上的。通过sql查询，自动映射到对象上，但是总是报错。奇怪的问题：sql打印出来放到数据库客户端上

可以正常执行结果。但是sql就是查询不出来，报 Column 'account' not found. 这样的错。换过驱动，想过框架的调用方式，查询资料的时候，发现有人

说用query.addEntity("","").list() 可以解决问题，未做验证。因为不想花大力气从框架上变动。用驱动源码跟踪，发现原来，驱动根本就不识别别名。在查询

`userAccount`对应的值的时候，传到驱动的columnName 竟然是 “account”，这也是很多人在网上说的，如果sqlQuey 遇到两个表有同一个属性名，查询会

报错的bug的原因所在！

代码部分：

SQLQuery query = createSQLQuery(orderSql, values);

long totalCount = countSqlResult(sql, values);

page.setTotalItems(totalCount);

setPageParameterToSqlQuery(query, page);

List result = query.setResultTransformer(Transformers.aliasToBean(cl)).list();

page.setResult(result);

平台输出：

select

l.id,

userId,

u.account as `userAccount`,

ip,

ipAddr,

loginTime

from

t_loginlog l

left join

t_user u

on l.userId = u.id

where

1=1 limit ?

[2015-01-30 14:27:29,031][ERROR][http-8080-1] (JDBCExceptionReporter.java:234) - Column 'account' not found.

2015-1-30 14:27:29 org.apache.catalina.core.StandardWrapperValve invoke

严重: Servlet.service() for servlet ishare threw exception

java.sql.SQLException: Column 'account' not found.

at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1078)

at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)

at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:975)

at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:920)

at com.mysql.jdbc.ResultSetImpl.findColumn(ResultSetImpl.java:1171)

at com.mysql.jdbc.ResultSetImpl.getString(ResultSetImpl.java:5737)

at org.apache.commons.dbcp.DelegatingResultSet.getString(DelegatingResultSet.java:263)

at org.apache.commons.dbcp.DelegatingResultSet.getString(DelegatingResultSet.java:263)

at org.hibernate.type.descriptor.sql.VarcharTypeDescriptor$2.doExtract(VarcharTypeDescriptor.java:61)

at org.hibernate.type.descriptor.sql.BasicExtractor.extract(BasicExtractor.java:64)

at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:253)

at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:249)

at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:234)

at org.hibernate.loader.custom.CustomLoader$ScalarResultColumnProcessor.extract(CustomLoader.java:505)

at org.hibernate.loader.custom.CustomLoader$ResultRowProcessor.buildResultRow(CustomLoader.java:451)

at org.hibernate.loader.custom.CustomLoader.getResultColumnOrRow(CustomLoader.java:348)

at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:639)

at org.hibernate.loader.Loader.doQuery(Loader.java:829)

at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:274)

at org.hibernate.loader.Loader.doList(Loader.java:2533)

at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2276)

at org.hibernate.loader.Loader.list(Loader.java:2271)

at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:316)

at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1842)

at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:165)

at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:157)

at com.coolsoul.framework.orm.hibernate.HibernateDao.findSqlPage(HibernateDao.java:90)

at com.coolsoul.ishare.loginLog.service.LoginLogService.findPageList(LoginLogService.java:54)

at com.coolsoul.ishare.loginLog.service.LoginLogService$$FastClassByCGLIB$$fb9ca761.invoke()

at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:191)

at org.springframework.aop.framework.Cglib2AopProxy$CglibMethodInvocation.invokeJoinpoint(Cglib2AopProxy.java:688)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)

at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)

at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:621)

at com.coolsoul.ishare.loginLog.service.LoginLogService$$EnhancerByCGLIB$$5ea6d36b.findPageList()

at com.coolsoul.ishare.loginLog.controller.LoginLogController.queryList(LoginLogController.java:69)

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.springframework.web.bind.annotation.support.HandlerMethodInvoker.invokeHandlerMethod(HandlerMethodInvoker.java:176)

at org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.invokeHandlerMethod(AnnotationMethodHandlerAdapter.java:426)

at org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.handle(AnnotationMethodHandlerAdapter.java:414)

at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:790)

at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:719)

at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:644)

at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:560)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:710)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)

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:235)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)

at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88)

at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)

at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:230)

at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175)

at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)

at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:104)

at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)

at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:261)

at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:844)

at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:581)

at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447)

at java.lang.Thread.run(Thread.java:662)

解救方法：

困扰了我很久 ，找了很多资料，问题很简单，就是驱动不支持别名的问题，这里记录下，为后来人造桥！！！ 在连接mysql数据库的时候添加 useOldAliasMetadataBehavior=true 这个参数！！！！

jdbc.url=jdbc:mysql://127.0.0.1:3306/db?useUnicode=true&characterEncoding=utf8&useOldAliasMetadataBehavior=true

常用的几个较为重要的参数：

参数名称

参数说明

缺省值

最低版本要求

user

数据库用户名(用于连接数据库)

所有版本

password

用户密码(用于连接数据库)

所有版本

useUnicode

是否使用Unicode字符集，如果参数characterEncoding设置为gb2312或gbk

false

1.1g

characterEncoding

当useUnicode设置为true时，指定字符编码。比如可设置为gb2312或gbk

false

1.1g

autoReconnect

当数据库连接异常中断时，是否自动重新连接

false

1.1

autoReconnectForPools

是否使用针对数据库连接池的重连策略

false

3.1.3

failOverReadOnly

自动重连成功后，连接是否设置为只读

true

3.0.12

maxReconnects

autoReconnect设置为true时，重试连接的次数

3

1.1

initialTimeout

autoReconnect设置为true时，两次重连之间的时间间隔，单位：秒

2

1.1

connectTimeout

和数据库服务器建立socket连接时的超时，单位：毫秒，0表示永不超时，适用于JDK 1.4及更高版本

0

3.0.1

socketTimeout

socket操作(读写)超时，单位：毫秒。 0表示永不超时

0

3.0.1