查了一些资料,说是在hibernate里可以直接执行sql语句的,我就写了下面的语句String t="select {ui}.systds,{ui}.moddsc,{ui}.trelev,{ui}.sytsno,{ui}.systno,IFNULL(rr2.rolid,'') rolid from VSysMod {ui} left join (select rolid,systno from TRolAut where rolid='102') rr2 on {ui}.systno=rr2.systno WHERE {ui}.empno='0'"; Query q = s.createSQLQuery(t,"ui",VSysMod.class); List l = q.list();但是运行后报错,错误如下: 2007-11-15 10:06:51,671 [net.sf.hibernate.util.JDBCExceptionReporter]-[WARN] SQL Error: 0, SQLState: S0022 2007-11-15 10:06:51,671 [net.sf.hibernate.util.JDBCExceptionReporter]-[ERROR] Co lumn 'ID0_' not found. 2007-11-15 10:06:51,671 [net.sf.hibernate.util.JDBCExceptionReporter]-[WARN] SQL Error: 0, SQLState: S0022 2007-11-15 10:06:51,671 [net.sf.hibernate.util.JDBCExceptionReporter]-[ERROR] Co lumn 'ID0_' not found. 2007-11-15 10:06:51,671 [net.sf.hibernate.util.JDBCExceptionReporter]-[ERROR] SQ LException occurred 2007-11-15 10:06:51,687 [org.springframework.jdbc.support.SQLStateSQLExceptionTr anslator]-[INFO] Translating SQLException with SQLState 'S0022' and errorCode '0 ' and message [Column 'ID0_' not found.]; SQL was [null] for task [HibernateAcce ssor] org.springframework.jdbc.BadSqlGrammarException: Bad SQL grammar [null] in task 'HibernateAccessor'; nested exception is java.sql.SQLException: Column 'ID0_' no t found. java.sql.SQLException: Column 'ID0_' not found. 这个什么意思呀?我写的语句哪里错了?拜托大家帮帮我
我又修改了一下语句,改为如下了,但还是报跟上面一样的错,我明明没查Column 'ID0_ ' ,为什么总报not found?String t = "select ui.systds,ui.moddsc,ui.trelev,ui.sytsno,ui.systno,rr2.rolid from VSYSMOD ui left join (select rolid,systno from TROLAUT where rolid='102') rr2 on ui.systno=rr2.systno WHERE ui.empno='0'"; Class[] c={VSysMod.class,RolAut.class}; String[] as={"ui","rr2"}; Query q = s.createSQLQuery(t,as,c); List l = q.list();
你這SQL語句在DB里能執行麼?
用的是mysql,可以的,我把后台输出的sql语句在mysql里执行过了,是可以查出数据的,下面这个就是tomcat上输出的语句 Hibernate: select ui.systds,ui.moddsc,ui.trelev,ui.sytsno,ui.systno,rr2.rolid fr om VSYSMOD ui left join (select rolid,systno from TROLAUT where rolid='102') rr2 on ui.systno=rr2.systno WHERE ui.empno='0'接着就是上面我发的那些错误了,我就觉得奇怪了,这个输出的语句在mysql里是可以执行的,而且我也没有查ID0_,本身我的表里就没有这个字段,只有ID,所以我肯定不会查的呀,为什么老是报Column 'ID0_ ' not found这个错呢?
问题基本解决了,现改为 String t = "select {ui.*},{rr2.*} from VSYSMOD {ui} left join (select * from TROLAUT where rolid= '102 ') {rr2} on {ui}.systno={rr2}.systno WHERE {ui}.empno= '0'"; Class[] c={VSysMod.class,RolAut.class}; String[] as={"ui","rr2"}; Query q = s.createSQLQuery(t,as,c); 但就是不能查询单个字段,只能查所有,不知道为什么,不过也算是解决问题了
Query q = s.createSQLQuery(t,"ui",VSysMod.class);
List l = q.list();但是运行后报错,错误如下:
2007-11-15 10:06:51,671 [net.sf.hibernate.util.JDBCExceptionReporter]-[WARN] SQL
Error: 0, SQLState: S0022
2007-11-15 10:06:51,671 [net.sf.hibernate.util.JDBCExceptionReporter]-[ERROR] Co
lumn 'ID0_' not found.
2007-11-15 10:06:51,671 [net.sf.hibernate.util.JDBCExceptionReporter]-[WARN] SQL
Error: 0, SQLState: S0022
2007-11-15 10:06:51,671 [net.sf.hibernate.util.JDBCExceptionReporter]-[ERROR] Co
lumn 'ID0_' not found.
2007-11-15 10:06:51,671 [net.sf.hibernate.util.JDBCExceptionReporter]-[ERROR] SQ
LException occurred
2007-11-15 10:06:51,687 [org.springframework.jdbc.support.SQLStateSQLExceptionTr
anslator]-[INFO] Translating SQLException with SQLState 'S0022' and errorCode '0
' and message [Column 'ID0_' not found.]; SQL was [null] for task [HibernateAcce
ssor]
org.springframework.jdbc.BadSqlGrammarException: Bad SQL grammar [null] in task
'HibernateAccessor'; nested exception is java.sql.SQLException: Column 'ID0_' no
t found.
java.sql.SQLException: Column 'ID0_' not found.
这个什么意思呀?我写的语句哪里错了?拜托大家帮帮我
from VSYSMOD ui left join (select rolid,systno from TROLAUT where rolid='102') rr2 on ui.systno=rr2.systno WHERE ui.empno='0'";
Class[] c={VSysMod.class,RolAut.class};
String[] as={"ui","rr2"};
Query q = s.createSQLQuery(t,as,c);
List l = q.list();
Hibernate: select ui.systds,ui.moddsc,ui.trelev,ui.sytsno,ui.systno,rr2.rolid fr
om VSYSMOD ui left join (select rolid,systno from TROLAUT where rolid='102') rr2
on ui.systno=rr2.systno WHERE ui.empno='0'接着就是上面我发的那些错误了,我就觉得奇怪了,这个输出的语句在mysql里是可以执行的,而且我也没有查ID0_,本身我的表里就没有这个字段,只有ID,所以我肯定不会查的呀,为什么老是报Column 'ID0_ ' not found这个错呢?
String t = "select {ui.*},{rr2.*} from VSYSMOD {ui} left join (select * from TROLAUT where rolid= '102 ') {rr2} on {ui}.systno={rr2}.systno WHERE {ui}.empno= '0'";
Class[] c={VSysMod.class,RolAut.class};
String[] as={"ui","rr2"};
Query q = s.createSQLQuery(t,as,c);
但就是不能查询单个字段,只能查所有,不知道为什么,不过也算是解决问题了