0 Replies Latest reply: Apr 6, 2012 10:59 AM by Craig Uss RSS

    SqlResultMapping not working

    Craig Uss Newbie

      I am trying to use @SqlResultMapping with one of my queries:

       

       

       

       

      query = this
           .getEntityManager()
           .createNativeQuery(
             "SELECT DISTINCT proj.*, mess.trigger_date"
               + "FROM ci_project as proj LEFT JOIN ci_project_message as mess "
               + "on proj.message_no = mess.message_no "
               + "WHERE proj.survey_contact = :userId",
             "surveyManagement").setParameter("userId", userId);
      

       

      I made a SqlResultMapping for this query:

       

      @SqlResultSetMapping(name = "surveyManagement", entities = {
          @EntityResult(entityClass = CiProject.class),
          @EntityResult(entityClass = CiProjectMessage.class, discriminatorColumn = "MESSAGE_NO", fields = { @FieldResult(name = "triggerDate", column = "trigger_date") }) })
      

       

      I am getting the following error when I try to execute this query:

       

      Caused by: org.hibernate.exception.SQLGrammarException: could not execute query

      at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:90)

      at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)

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

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

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

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

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

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

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

      at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:94)

      ... 252 more

      Caused by: java.sql.SQLException: Column 'MESSAGE1_4_1_' not found.

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

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

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

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

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

      at com.mysql.jdbc.ResultSetImpl.getInt(ResultSetImpl.java:2815)

      at org.jboss.resource.adapter.jdbc.WrappedResultSet.getInt(WrappedResultSet.java:698)

      at org.hibernate.type.IntegerType.get(IntegerType.java:51)

      at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:184)

      at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:173)

      at org.hibernate.loader.Loader.getKeyFromResultSet(Loader.java:1120)

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

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

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

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

      ... 259 more

      10:41:29,794 SEVERE [lifecycle] JSF1054: (Phase ID: RENDER_RESPONSE 6, View ID: /subViews/surveysList.xhtml) Exception thrown during phase execution: javax.faces.event.PhaseEvent[source=com.sun.faces.lifecycle.LifecycleImpl@1c7728e]

       

      I am new to using SqlResultMapping annotation. Any advice or point in the right direction will be greatly appreciated!