MySQL 5运行正常的代码怎么到了MySQL4就运行错误了呢?
是不是mysql4 不支持 select * from XX as aa where aa.id in (select bb.id from XX as bb)
但是 select * from XX as aa where aa.id in (2,3)又能正常,select bb.id from XX as bb运行结果也有值2,3 课为什么完整合在一起出错呢,请大家帮忙
下边是程序中代码:
String hql = "from News n where n.entry.entryid in ("
+ "select e.entryid from Entry e where e.parentid=1 ) "
+ "or n.entry.entryid=1 order by n.lastmodifydate desc ";
List tempList = basicMan.findByHQL(hql);
生成的sqlHibernate: select news0_.NEWSID as NEWSID0_, news0_.ENTRYID as ENTRYID0_, news0_.NEWSTITLE as NEWSTITLE0_, news0_.CONTENT as CONTENT0_, news0_.KEYWORDS as KEYWORDS0_, news0_.LASTMODIFYDATE as LASTMODI6_0_, news0_.ORDERINDEX as ORDERINDEX0_, news0_.PICPATH as PICPATH0_ from zkq1f1_db.news news0_ where news0_.ENTRYID in (select entry1_.ENTRYID from zkq1f1_db.entry entry1_ where entry1_.PARENTID=1) or news0_.ENTRYID=1 order by news0_.LASTMODIFYDATE desc
当直接运行sqlselect entry1_.ENTRYID from zkq1f1_db.entry entry1_ where entry1_.PARENTID=1的时候有值,运行select news0_.NEWSID as NEWSID0_, news0_.ENTRYID as ENTRYID0_, news0_.NEWSTITLE as NEWSTITLE0_, news0_.CONTENT as CONTENT0_, news0_.KEYWORDS as KEYWORDS0_, news0_.LASTMODIFYDATE as LASTMODI6_0_, news0_.ORDERINDEX as ORDERINDEX0_, news0_.PICPATH as PICPATH0_ from zkq1f1_db.news news0_ where news0_.ENTRYID in (2,3) or news0_.ENTRYID=1 order by news0_.LASTMODIFYDATE desc
时也正常运行,
合在一起的时候就出错了,错误信息如下:
Exception in thread "main" org.springframework.dao.InvalidDataAccessResourceUsageException: could not execute query; nested exception is org.hibernate.exception.SQLGrammarException: could not execute query
at org.springframework.orm.hibernate3.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:615)
at org.springframework.orm.hibernate3.HibernateAccessor.convertHibernateAccessException(HibernateAccessor.java:412)
at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:424)
at org.springframework.orm.hibernate3.HibernateTemplate.executeWithNativeSession(HibernateTemplate.java:374)
at org.springframework.orm.hibernate3.HibernateTemplate.find(HibernateTemplate.java:917)
at com.kcheng.dao.BasicDImp.findByHQL(BasicDImp.java:29)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:310)
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 $Proxy5.findByHQL(Unknown Source)
at test.my.dao.TestDao.main(TestDao.java:39)
Caused by: org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:2223)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)
at org.hibernate.loader.Loader.list(Loader.java:2099)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:378)
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:338)
at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:172)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1121)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)
at org.springframework.orm.hibernate3.HibernateTemplate$30.doInHibernate(HibernateTemplate.java:926)
at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:419)
... 15 more
Caused by: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'select entry1_.ENTRYID from zkq1f1_db.entry entry1_ where entry
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2822)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1536)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1626)
at com.mysql.jdbc.Connection.execSQL(Connection.java:3031)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:943)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1049)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.logicalcobwebs.proxool.ProxyStatement.invoke(ProxyStatement.java:100)
at org.logicalcobwebs.proxool.ProxyStatement.intercept(ProxyStatement.java:57)
at $java.sql.PreparedStatement$$EnhancerByProxool$$d397000.executeQuery()
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1787)
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)
... 24 more
----------------------------------------------
怎么没人来回答呢,那只能用个笨办法了,写成两个sql语句,先查到id遍历出来,然后再查一遍