mysql varchar长度过大引起hibernate原生态SQL错误:No Dialect mapping for JDBC type: -1错误的解决方法

先把异常抛出来:

Hibernate: select sum(num) from ( select count(*) as num from cm_facility_wrong as fw left outer join cm_conf as cf on fw.faw_room_id=cf.conf_id left outer join cm_facility as f on fw.fa_id=f.fa_id where 1=1 and fw.faw_room_type=0 union all select count(*) as num from cm_facility_wrong as fw left outer join cm_classroom as cr on fw.faw_room_id=cr.cr_id left outer join cm_facility as f on fw.fa_id=f.fa_id where 1=1 and fw.faw_room_type=1 ) as t

Hibernate: select fw.faw_id,fw.faw_room_id,fw.faw_room_type, fw.fa_id,fw.faw_type,fw.faw_desc,fw.faw_time,fw.faw_man, fw.faw_r_company,fw.faw_man,fw.faw_phone,fw.faw_r_time, fw.faw_memo,fw.faw_state, cf.conf_name,f.fa_no,f.fa_name from cm_facility_wrong as fw   left outer join cm_conf as cf on fw.faw_room_id=cf.conf_id left outer join cm_facility as f on fw.fa_id=f.fa_id where 1=1 and fw.faw_room_type=0  union all select fw.faw_id,fw.faw_room_id,fw.faw_room_type, fw.fa_id,fw.faw_type,fw.faw_desc,fw.faw_time,fw.faw_man, fw.faw_r_company,fw.faw_man,fw.faw_phone,fw.faw_r_time, fw.faw_memo,fw.faw_state, cr.cr_name,f.fa_no,f.fa_name from cm_facility_wrong as fw left outer join cm_classroom as cr on fw.faw_room_id=cr.cr_id left outer join cm_facility as f on fw.fa_id=f.fa_id where 1=1 and fw.faw_room_type=1 limit ?
WARN - Unhandled Exception thrown: class org.hibernate.MappingException
2009-6-24 20:40:15 org.apache.catalina.core.StandardWrapperValve invoke
严重: Servlet.service() for servlet action threw exception
org.hibernate.MappingException: No Dialect mapping for JDBC type: -1
at org.hibernate.dialect.TypeNames.get(TypeNames.java:56)
at org.hibernate.dialect.TypeNames.get(TypeNames.java:81)
at org.hibernate.dialect.Dialect.getHibernateTypeName(Dialect.java:370)
at org.hibernate.loader.custom.CustomLoader$Metadata.getHibernateType(CustomLoader.java:559)
at org.hibernate.loader.custom.CustomLoader$ScalarResultColumnProcessor.performDiscovery(CustomLoader.java:485)
at org.hibernate.loader.custom.CustomLoader.autoDiscoverTypes(CustomLoader.java:501)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1796)
at org.hibernate.loader.Loader.doQuery(Loader.java:674)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
at org.hibernate.loader.Loader.doList(Loader.java:2220)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)
at org.hibernate.loader.Loader.list(Loader.java:2099)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:289)
at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1695)
at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:142)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:152)
at net.bolue.other.dao.impl.CmFacilityWrongDAOImpl.findAll(CmFacilityWrongDAOImpl.java:93)
at net.bolue.other.service.impl.CmFacilityWrongServiceImpl.findAll(CmFacilityWrongServiceImpl.java:37)
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:585)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:307)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:182)
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.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
at $Proxy33.findAll(Unknown Source)
at net.bolue.other.action.CmFacilityWrongAction.findByAll(CmFacilityWrongAction.java:212)
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:585)
at org.apache.struts.actions.DispatchAction.dispatchMethod(DispatchAction.java:270)
at org.apache.struts.actions.DispatchAction.execute(DispatchAction.java:187)
at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:431)
at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:236)
at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1196)
at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:432)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
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:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:286)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:845)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447)
at java.lang.Thread.run(Thread.java:595)

由于此查询语句需要使用union语句,因此必须要用原生态的SQL来写.在表中有两个字段类型为varchar,长度我都设为1000.

结果做这个查询时就出了问题,SQL语句hibernate已经运行出来了,说明SQL语句写的没有问题,后来google了半天,在中文google里找了找,也有写报类似错误的,但是都没有出现我这个type: -1错误,于是就google英文页面,找到了一篇介绍这个解决办法的文章:http://opensource.atlassian.com/projects/hibernate/browse/HHH-1483,还是英文google资料多而且准啊.

出现这个原因是说hibernate中mysql的varchar类型与数据库中的varchar类型不能对应起来,因此只要在Hibernate里把对应的数据类型成功映射起来就可以成功执行了。

解决方法如下:

import java.sql.Types;

import org.hibernate.Hibernate;

public class MySQL5Dialect extends org.hibernate.dialect.MySQL5Dialect {
public MySQL5Dialect() {
   super();
   // register additional hibernate types for default use in scalar
   // sqlquery type auto detection
   registerHibernateType(Types.LONGVARCHAR, Hibernate.TEXT.getName());
}
}

新建一个MySQL5Dialect类,然后在hibernate配置文件或spring配置文件中将hibernate.dialect改成刚才写的那个就可以了.如

<prop key="hibernate.dialect">xxx.util.MySQL5Dialect</prop>

总结:当在hibernate中使用原生态的SQL时,如果在数据库varchar的字段的长度设为过大(比如说为1000)的话,那么就需要自己补充注册新的类型映射,不光是varchar类型,其它类型字段设置过大也会有这样的问题,比如说decimal类型等等,同样也是按这种方法来解决.

hibernate有时还真有点搞人的.

http://hi.baidu.com/sunjsp/blog/item/fa7b57efe4a7b8e6ce1b3eb1.html