NOTE:
First of all, the following problem only occurs with the current release candidate 1 of Hibernate 3.0.
It worked perfectly with the latest beta versions!
I'd like to select all lecture sequences...
- which refer to a course containing the user (student) currently logged on AND
- which have a published questionnaire (= possibility for students to fill in) AND
- ensuring that the user (student) currently logged on hasn't already participated in the survey (= no SurveyParticipation object).
I will also provide a class diagram in order to understand the query.
Hibernate version:
3.0 rc1
Name and version of the database you are using:
MySQL 4.1.10
Class diagram:
Code causing the error:
Code:
HibernateUtil.beginTransaction();
final Session session = HibernateUtil.getSession();
final StringBuilder queryString = new StringBuilder().
append("SELECT ls FROM LectureSequence AS ls ").
append("JOIN ls.course.students AS studs ").
append("WHERE (studs.username = :username) ").
append("AND ls.questionnairesPublishedDate IS NOT NULL ").
append("AND ls.questionnairesPublishedDate <= :today ").
append("AND NOT EXISTS (FROM SurveyParticipation AS sp ").
append("WHERE (sp.lectureSequence = ls) ").
append("AND (sp.student.username = :username)) ").
append("ORDER BY ls.questionnairesPublishedDate ASC, ls.subject.name ASC");
final Query query = session.createQuery(queryString.toString());
query.setString("username", username);
query.setDate("today", Calendar.getInstance().getTime());
final List lectureSequences = query.list();
HibernateUtil.commitTransaction();
Full stack trace of any exception that occurs:Code:
WARN 28.02.2005 23:55:34,482 org.hibernate.util.JDBCExceptionReporter (JDBCExceptionReporter:logExceptions:57) - SQL Error: 1054, SQLState: 42S22
ERROR 28.02.2005 23:55:34,482 org.hibernate.util.JDBCExceptionReporter (JDBCExceptionReporter:logExceptions:58) - Unknown column 'ls' in 'where clause'
WARN 28.02.2005 23:55:34,492 org.hibernate.util.JDBCExceptionReporter (JDBCExceptionReporter:logExceptions:57) - SQL Error: 1054, SQLState: 42S22
ERROR 28.02.2005 23:55:34,492 org.hibernate.util.JDBCExceptionReporter (JDBCExceptionReporter:logExceptions:58) - Unknown column 'ls' in 'where clause'
WARN 28.02.2005 23:55:34,722 org.apache.struts.action.RequestProcessor (RequestProcessor:processException:507) - Unhandled Exception thrown: class org.hibernate.exception.SQLGrammarException
ERROR 28.02.2005 23:55:34,722 StandardWrapper[/w3s:action] (StandardWrapperValve:invoke:269) - Servlet.service() for servlet action threw exception
org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.ErrorCodeConverter.convert(ErrorCodeConverter.java:70)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:1502)
at org.hibernate.loader.Loader.list(Loader.java:1482)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:365)
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:268)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:782)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:74)
at de.w3s.webapp.struts.actions.SelectBlankQuestionnairesAction.execute(SelectBlankQuestionnairesAction.java:42)
at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:419)
at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:224)
at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1192)
at org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:412)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:689)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:214)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:407)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:825)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:738)
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:526)
at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
at java.lang.Thread.run(Unknown Source)
Caused by: java.sql.SQLException: Unknown column 'ls' in 'where clause'
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2851)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1534)
at com.mysql.jdbc.ServerPreparedStatement.serverPrepare(ServerPreparedStatement.java:1485)
at com.mysql.jdbc.ServerPreparedStatement.<init>(ServerPreparedStatement.java:151)
at com.mysql.jdbc.Connection.prepareStatement(Connection.java:1309)
at com.mysql.jdbc.Connection.prepareStatement(Connection.java:1281)
at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:351)
at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:302)
at org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.java:85)
at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1090)
at org.hibernate.loader.Loader.doQuery(Loader.java:362)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:203)
at org.hibernate.loader.Loader.doList(Loader.java:1499)
... 27 more