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!