[jboss-user] [EJB3] - SqlResultMapping not working

[Craig Uss]

Craig Uss [https://community.jboss.org/people/craigmuss] created the discussion

"SqlResultMapping not working"

To view the discussion, visit: https://community.jboss.org/message/728678#728678

--------------------------------------------------------------
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 at 1c7728e]

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

Reply to this message by going to Community
[https://community.jboss.org/message/728678#728678]

Start a new discussion in EJB3 at Community
[https://community.jboss.org/choose-container!input.jspa?contentType=1&containerType=14&container=2029]

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.jboss.org/pipermail/jboss-user/attachments/20120406/590f7510/attachment-0001.html