I try to retrieve a list with some fields from Contact and also a list of phones.
For this, I'm using a query to Contacts; Also I've created a DTO with only that fields that I need.
The query is:
final StringBuilder query = new StringBuilder();
query.append("SELECT new com.tim.core.dto.client.MinimalContactDTO(c.id, c.version, c.name, c.title, c.email, c.createdDate, c.phones) " +
" from CONTACT c ");
query.append("where ");
query.append("( c.localRecordStatus IS NULL ");
query.append("OR c.localRecordStatus IN (:openStatusList) ");
query.append(" ) ");
return em.createQuery(query.toString(), MinimalContactDTO.class)
.setParameter("openStatusList", getOpenStatusList())
.getResultList();
The code for DTO is:
public MinimalContactDTO(Long id, Long version, String name, String title, String email, Date createdDate, Set<ContactPhone> phones) {
this.id = id;
this.version = version;
this.name = name;
this.title = title;
this.email = email;
this.createdDate = createdDate;
this.phones= phones;
}
Contact:
@Entity(name = "CONTACT")
public class Contact implements Identifiable, Serializable {
public static final long serialVersionUID = 1L;
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;
private Long version;
private String name;
private String email;
private String title;
@OneToMany(cascade = CascadeType.ALL, fetch = FetchType.EAGER, orphanRemoval = true)
@JoinColumn(name = "CONTACT_ID", referencedColumnName = "ID")
@OrderBy("id ASC")
private Set<ContactPhone> phones = new HashSet<>();
}
ContactPhone:
public class ContactPhone implements Identifiable, Serializable {
public static final long serialVersionUID = 1L;
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
@Column(name = "ID")
private Long id;
@ManyToOne
@JoinColumn(name = "CONTACT_ID")
private Contact contact;
private String number;
@ManyToOne(cascade = {CascadeType.PERSIST})
@JoinColumn(name = "PHONE_LABEL_ID")
private ContactPhoneLabel label;
}
mySql: Ver 14.14 Distrib 5.7.16, for Win64 (x86_64)
I receive an error and I don't have any idea what is the problem. Has anyone any idea?
Error:
Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:63)
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:109)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:95)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:79)
at org.hibernate.loader.Loader.getResultSet(Loader.java:2117)
at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1900)
at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1876)
at org.hibernate.loader.Loader.doQuery(Loader.java:919)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:336)
at org.hibernate.loader.Loader.doList(Loader.java:2617)
at org.hibernate.loader.Loader.doList(Loader.java:2600)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2429)
at org.hibernate.loader.Loader.list(Loader.java:2424)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:501)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:371)
at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:216)
at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1326)
at org.hibernate.internal.QueryImpl.list(QueryImpl.java:87)
at org.hibernate.jpa.internal.QueryImpl.list(QueryImpl.java:606)
at org.hibernate.jpa.internal.QueryImpl.getResultList(QueryImpl.java:483)
... 203 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as col_6_0_ from CONTACT contact0_ inner join CONTACT_PHONE phones1_ on contact0' at line 1
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:404)
at com.mysql.jdbc.Util.getInstance(Util.java:387)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:941)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3870)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3806)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2470)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2617)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2550)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1962)
at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:504)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:70)
... 219 more
...
Caused by: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1692)
at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1602)
at org.hibernate.jpa.internal.QueryImpl.getResultList(QueryImpl.java:492)
at com.tim.core.persistence.client.impl.ContactPersistenceImpl.getAllContactsDTO(ContactPersistenceImpl.java:71)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.jboss.as.ee.component.ManagedReferenceMethodInterceptor.processInvocation(ManagedReferenceMethodInterceptor.java:52)
at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
at org.jboss.invocation.InterceptorContext$Invocation.proceed(InterceptorContext.java:437)
at org.jboss.as.weld.ejb.Jsr299BindingsInterceptor.doMethodInterception(Jsr299BindingsInterceptor.java:82)
at org.jboss.as.weld.ejb.Jsr299BindingsInterceptor.processInvocation(Jsr299BindingsInterceptor.java:93)
at org.jboss.as.ee.component.interceptors.UserInterceptorFactory$1.processInvocation(UserInterceptorFactory.java:63)
at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
at org.jboss.as.ejb3.component.invocationmetrics.ExecutionTimeInterceptor.processInvocation(ExecutionTimeInterceptor.java:43)
at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
at org.jboss.as.jpa.interceptor.SBInvocationInterceptor.processInvocation(SBInvocationInterceptor.java:47)
at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
at org.jboss.invocation.InterceptorContext$Invocation.proceed(InterceptorContext.java:437)
at org.jboss.weld.ejb.AbstractEJBRequestScopeActivationInterceptor.aroundInvoke(AbstractEJBRequestScopeActivationInterceptor.java:64)
at org.jboss.as.weld.ejb.EjbRequestScopeActivationInterceptor.processInvocation(EjbRequestScopeActivationInterceptor.java:83)
at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
at org.jboss.as.ee.concurrent.ConcurrentContextInterceptor.processInvocation(ConcurrentContextInterceptor.java:45)
at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
at org.jboss.invocation.InitialInterceptor.processInvocation(InitialInterceptor.java:21)
at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
at org.jboss.invocation.ChainedInterceptor.processInvocation(ChainedInterceptor.java:61)
at org.jboss.as.ee.component.interceptors.ComponentDispatcherInterceptor.processInvocation(ComponentDispatcherInterceptor.java:52)
at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
at org.jboss.as.ejb3.component.pool.PooledInstanceInterceptor.processInvocation(PooledInstanceInterceptor.java:51)
at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
at org.jboss.as.ejb3.tx.CMTTxInterceptor.invokeInCallerTx(CMTTxInterceptor.java:254)
... 173 more
Hibernate log:
2017-04-18 18:53:14,748 DEBUG [org.hibernate.hql.internal.ast.ErrorCounter] (MyScheduler_Worker-1) throwQueryException() : no errors
2017-04-18 18:53:14,766 DEBUG [org.hibernate.hql.internal.ast.QueryTranslatorImpl] (default task-1) --- HQL AST ---
\-[QUERY] Node: 'query'
+-[SELECT_FROM] Node: 'SELECT_FROM'
| +-[FROM] Node: 'from'
| | \-[RANGE] Node: 'RANGE'
| | +-[DOT] Node: '.'
| | | +-[DOT] Node: '.'
| | | | +-[DOT] Node: '.'
| | | | | +-[DOT] Node: '.'
| | | | | | +-[DOT] Node: '.'
| | | | | | | +-[IDENT] Node: 'com'
| | | | | | | \-[IDENT] Node: 'tim'
| | | | | | \-[IDENT] Node: 'core'
| | | | | \-[IDENT] Node: 'model'
| | | | \-[IDENT] Node: 'client'
| | | \-[IDENT] Node: 'Contact'
| | \-[ALIAS] Node: 'c'
| \-[SELECT] Node: 'SELECT'
| \-[CONSTRUCTOR] Node: '('
| +-[DOT] Node: '.'
| | +-[DOT] Node: '.'
| | | +-[DOT] Node: '.'
| | | | +-[DOT] Node: '.'
| | | | | +-[DOT] Node: '.'
| | | | | | +-[IDENT] Node: 'com'
| | | | | | \-[IDENT] Node: 'tim'
| | | | | \-[IDENT] Node: 'core'
| | | | \-[IDENT] Node: 'dto'
| | | \-[IDENT] Node: 'client'
| | \-[IDENT] Node: 'MinimalContactDTO'
| +-[DOT] Node: '.'
| | +-[IDENT] Node: 'c'
| | \-[IDENT] Node: 'id'
| +-[DOT] Node: '.'
| | +-[IDENT] Node: 'c'
| | \-[IDENT] Node: 'version'
| +-[DOT] Node: '.'
| | +-[IDENT] Node: 'c'
| | \-[IDENT] Node: 'name'
| +-[DOT] Node: '.'
| | +-[IDENT] Node: 'c'
| | \-[IDENT] Node: 'title'
| +-[DOT] Node: '.'
| | +-[IDENT] Node: 'c'
| | \-[IDENT] Node: 'email'
| +-[DOT] Node: '.'
| | +-[IDENT] Node: 'c'
| | \-[IDENT] Node: 'createdDate'
| \-[DOT] Node: '.'
| +-[IDENT] Node: 'c'
| \-[IDENT] Node: 'phones'
\-[WHERE] Node: 'where'
\-[OR] Node: 'OR'
+-[IS_NULL] Node: 'is null'
| \-[DOT] Node: '.'
| +-[IDENT] Node: 'c'
| \-[IDENT] Node: 'localRecordStatus'
\-[IN] Node: 'in'
+-[DOT] Node: '.'
| +-[IDENT] Node: 'c'
| \-[IDENT] Node: 'localRecordStatus'
\-[IN_LIST] Node: 'inList'
\-[COLON] Node: ':'
\-[IDENT] Node: 'openStatusList'
2017-04-18 18:53:14,767 DEBUG [org.hibernate.hql.internal.ast.ErrorCounter] (default task-1) throwQueryException() : no errors
...
2017-04-18 18:53:14,806 DEBUG [org.hibernate.hql.internal.ast.HqlSqlWalker] (default task-1) processQuery() : ( SELECT ( {select clause} ( ( ( . ( . ( . ( . ( . com tim ) core ) dto ) client ) MinimalContactDTO ) ( contact0_.ID contact0_.ID id ) ( contact0_.VERSION contact0_.ID version ) ( contact0_.name contact0_.ID name ) ( contact0_.title contact0_.ID title ) ( contact0_.email contact0_.ID email ) ( contact0_.CREATED_DATE contact0_.ID createdDate ) ( . contact0_.ID phones ) ) ) ( FromClause{level=1} ( CONTACT contact0_ CONTACT_PHONE phones1_ ) ) ( where ( OR ( is null ( contact0_.LOCAL_RECORD_STATUS contact0_.ID localRecordStatus ) ) ( in ( contact0_.LOCAL_RECORD_STATUS contact0_.ID localRecordStatus ) ( inList ? ) ) ) ) )
2017-04-18 18:53:14,807 DEBUG [org.hibernate.hql.internal.ast.util.JoinProcessor] (default task-1) Using FROM fragment [CONTACT contact0_]
2017-04-18 18:53:14,808 DEBUG [org.hibernate.hql.internal.ast.util.JoinProcessor] (default task-1) Using FROM fragment [inner join CONTACT_PHONE phones1_ on contact0_.ID=phones1_.CONTACT_ID]
2017-04-18 18:53:14,808 DEBUG [org.hibernate.hql.internal.antlr.HqlSqlBaseWalker] (default task-1) select >> end [level=1, statement=select]
2017-04-18 18:53:14,809 DEBUG [org.hibernate.hql.internal.ast.QueryTranslatorImpl] (default task-1) --- SQL AST ---
\-[SELECT] QueryNode: 'SELECT' querySpaces (CONTACT,CONTACT_PHONE)
+-[SELECT_CLAUSE] SelectClause: '{select clause}'
| \-[CONSTRUCTOR] ConstructorNode: '('
| +-[DOT] DotNode: '.' {propertyName=null,dereferenceType=UNKNOWN,getPropertyPath=null,path=com.tim.core.dto.client.MinimalContactDTO,no from element}
| | +-[DOT] DotNode: '.' {propertyName=null,dereferenceType=UNKNOWN,getPropertyPath=null,path=com.tim.core.dto.client,no from element}
| | | +-[DOT] DotNode: '.' {propertyName=null,dereferenceType=UNKNOWN,getPropertyPath=null,path=com.tim.core.dto,no from element}
| | | | +-[DOT] DotNode: '.' {propertyName=null,dereferenceType=UNKNOWN,getPropertyPath=null,path=com.tim.core,no from element}
| | | | | +-[DOT] DotNode: '.' {propertyName=null,dereferenceType=UNKNOWN,getPropertyPath=null,path=com.tim,no from element}
| | | | | | +-[IDENT] IdentNode: 'com' {originalText=com}
| | | | | | \-[IDENT] IdentNode: 'tim' {originalText=tim}
| | | | | \-[IDENT] IdentNode: 'core' {originalText=core}
| | | | \-[IDENT] IdentNode: 'dto' {originalText=dto}
| | | \-[IDENT] IdentNode: 'client' {originalText=client}
| | \-[IDENT] IdentNode: 'MinimalContactDTO' {originalText=MinimalContactDTO}
| +-[DOT] DotNode: 'contact0_.ID' {propertyName=id,dereferenceType=PRIMITIVE,getPropertyPath=id,path=c.id,tableAlias=contact0_,className=com.tim.core.model.client.Contact,classAlias=c}
| | +-[ALIAS_REF] IdentNode: 'contact0_.ID' {alias=c, className=com.tim.core.model.client.Contact, tableAlias=contact0_}
| | \-[IDENT] IdentNode: 'id' {originalText=id}
| +-[SELECT_COLUMNS] SqlNode: ' as col_0_0_'
| +-[DOT] DotNode: 'contact0_.VERSION' {propertyName=version,dereferenceType=PRIMITIVE,getPropertyPath=version,path=c.version,tableAlias=contact0_,className=com.tim.core.model.client.Contact,classAlias=c}
| | +-[ALIAS_REF] IdentNode: 'contact0_.ID' {alias=c, className=com.tim.core.model.client.Contact, tableAlias=contact0_}
| | \-[IDENT] IdentNode: 'version' {originalText=version}
| +-[SELECT_COLUMNS] SqlNode: ' as col_1_0_'
| +-[DOT] DotNode: 'contact0_.name' {propertyName=name,dereferenceType=PRIMITIVE,getPropertyPath=name,path=c.name,tableAlias=contact0_,className=com.tim.core.model.client.Contact,classAlias=c}
| | +-[ALIAS_REF] IdentNode: 'contact0_.ID' {alias=c, className=com.tim.core.model.client.Contact, tableAlias=contact0_}
| | \-[IDENT] IdentNode: 'name' {originalText=name}
| +-[SELECT_COLUMNS] SqlNode: ' as col_2_0_'
| +-[DOT] DotNode: 'contact0_.title' {propertyName=title,dereferenceType=PRIMITIVE,getPropertyPath=title,path=c.title,tableAlias=contact0_,className=com.tim.core.model.client.Contact,classAlias=c}
| | +-[ALIAS_REF] IdentNode: 'contact0_.ID' {alias=c, className=com.tim.core.model.client.Contact, tableAlias=contact0_}
| | \-[IDENT] IdentNode: 'title' {originalText=title}
| +-[SELECT_COLUMNS] SqlNode: ' as col_3_0_'
| +-[DOT] DotNode: 'contact0_.email' {propertyName=email,dereferenceType=PRIMITIVE,getPropertyPath=email,path=c.email,tableAlias=contact0_,className=com.tim.core.model.client.Contact,classAlias=c}
| | +-[ALIAS_REF] IdentNode: 'contact0_.ID' {alias=c, className=com.tim.core.model.client.Contact, tableAlias=contact0_}
| | \-[IDENT] IdentNode: 'email' {originalText=email}
| +-[SELECT_COLUMNS] SqlNode: ' as col_4_0_'
| +-[DOT] DotNode: 'contact0_.CREATED_DATE' {propertyName=createdDate,dereferenceType=PRIMITIVE,getPropertyPath=createdDate,path=c.createdDate,tableAlias=contact0_,className=com.tim.core.model.client.Contact,classAlias=c}
| | +-[ALIAS_REF] IdentNode: 'contact0_.ID' {alias=c, className=com.tim.core.model.client.Contact, tableAlias=contact0_}
| | \-[IDENT] IdentNode: 'createdDate' {originalText=createdDate}
| +-[SELECT_COLUMNS] SqlNode: ' as col_5_0_'
| +-[DOT] DotNode: '.' {propertyName=phones,dereferenceType=COLLECTION,getPropertyPath=phones,path=c.phones,tableAlias=phones1_,className=com.tim.core.model.client.ContactPhone,classAlias=null}
| | +-[ALIAS_REF] IdentNode: 'contact0_.ID' {alias=c, className=com.tim.core.model.client.Contact, tableAlias=contact0_}
| | \-[IDENT] IdentNode: 'phones' {originalText=phones}
| \-[SELECT_COLUMNS] SqlNode: ' as col_6_0_'
+-[FROM] FromClause: 'from' FromClause{level=1, fromElementCounter=2, fromElements=2, fromElementByClassAlias=[c], fromElementByTableAlias=[phones1_, contact0_], fromElementsByPath=[c.phones], collectionJoinFromElementsByPath=[], impliedElements=[]}
| \-[FROM_FRAGMENT] FromElement: 'CONTACT contact0_' FromElement{explicit,not a collection join,not a fetch join,fetch non-lazy properties,classAlias=c,role=null,tableName=CONTACT,tableAlias=contact0_,origin=null,columns={,className=com.tim.core.model.client.Contact}}
| \-[JOIN_FRAGMENT] FromElement: 'inner join CONTACT_PHONE phones1_ on contact0_.ID=phones1_.CONTACT_ID' FromElement{explicit,not a collection join,not a fetch join,fetch non-lazy properties,classAlias=null,role=com.tim.core.model.client.Contact.phones,tableName=CONTACT_PHONE,tableAlias=phones1_,origin=CONTACT contact0_,columns={contact0_.ID ,className=com.tim.core.model.client.ContactPhone}}
\-[WHERE] SqlNode: 'where'
\-[OR] SqlNode: 'OR'
+-[IS_NULL] IsNullLogicOperatorNode: 'is null'
| \-[DOT] DotNode: 'contact0_.LOCAL_RECORD_STATUS' {propertyName=localRecordStatus,dereferenceType=PRIMITIVE,getPropertyPath=localRecordStatus,path=c.localRecordStatus,tableAlias=contact0_,className=com.tim.core.model.client.Contact,classAlias=c}
| +-[ALIAS_REF] IdentNode: 'contact0_.ID' {alias=c, className=com.tim.core.model.client.Contact, tableAlias=contact0_}
| \-[IDENT] IdentNode: 'localRecordStatus' {originalText=localRecordStatus}
\-[IN] InLogicOperatorNode: 'in'
+-[DOT] DotNode: 'contact0_.LOCAL_RECORD_STATUS' {propertyName=localRecordStatus,dereferenceType=PRIMITIVE,getPropertyPath=localRecordStatus,path=c.localRecordStatus,tableAlias=contact0_,className=com.tim.core.model.client.Contact,classAlias=c}
| +-[ALIAS_REF] IdentNode: 'contact0_.ID' {alias=c, className=com.tim.core.model.client.Contact, tableAlias=contact0_}
| \-[IDENT] IdentNode: 'localRecordStatus' {originalText=localRecordStatus}
\-[IN_LIST] SqlNode: 'inList'
\-[NAMED_PARAM] ParameterNode: '?' {name=openStatusList, expectedType=org.hibernate.type.CustomType@67bb642c}
2017-04-18 18:53:14,811 DEBUG [org.hibernate.hql.internal.ast.QueryTranslatorImpl] (MyScheduler_Worker-1) HQL: SELECT sp FROM com.tim.base.model.SystemProperty sp WHERE sp.propertyName = :propertyName
2017-04-18 18:53:14,811 DEBUG [org.hibernate.hql.internal.ast.ErrorCounter] (default task-1) throwQueryException() : no errors
2017-04-18 18:53:14,811 DEBUG [org.hibernate.hql.internal.ast.QueryTranslatorImpl] (MyScheduler_Worker-1) SQL: select systemprop0_.ID as ID1_0_, systemprop0_.PROPERTY_NAME as PROPERTY2_0_, systemprop0_.PROPERTY_VALUE as PROPERTY3_0_ from SYSTEM_PROPERTY systemprop0_ where systemprop0_.PROPERTY_NAME=?
2017-04-18 18:53:14,811 DEBUG [org.hibernate.hql.internal.ast.ErrorCounter] (MyScheduler_Worker-1) throwQueryException() : no errors
2017-04-18 18:53:14,811 DEBUG [org.hibernate.hql.internal.ast.QueryTranslatorImpl] (default task-1) HQL: SELECT new com.tim.core.dto.client.MinimalContactDTO(c.id, c.version, c.name, c.title, c.email, c.createdDate, c.phones) from com.tim.core.model.client.Contact c where ( c.localRecordStatus IS NULL OR c.localRecordStatus IN (:openStatusList) )
2017-04-18 18:53:14,811 DEBUG [org.hibernate.hql.internal.ast.QueryTranslatorImpl] (default task-1) SQL: select contact0_.ID as col_0_0_, contact0_.VERSION as col_1_0_, contact0_.name as col_2_0_, contact0_.title as col_3_0_, contact0_.email as col_4_0_, contact0_.CREATED_DATE as col_5_0_, . as col_6_0_ from CONTACT contact0_ inner join CONTACT_PHONE phones1_ on contact0_.ID=phones1_.CONTACT_ID where contact0_.LOCAL_RECORD_STATUS is null or contact0_.LOCAL_RECORD_STATUS in (?)
2017-04-18 18:53:14,811 DEBUG [org.hibernate.hql.internal.ast.ErrorCounter] (default task-1) throwQueryException() : no errors
You can not select a collection in a DTO projection like this:
SELECT new com.tim.core.dto.client.MinimalContactDTO(c.id, c.version, c.name, c.title, c.email, c.createdDate, **c.phones**)
The ResultSet is like a spreadsheet, not like a graph of objects.
What you need to do is change your DTO like this:
public MinimalContactDTO(
Long id, Long version, String name, String title, String email,
Date createdDate, ContactPhone phone) {
...
}
Now, you can only pass one phone at a time:
SELECT new com.tim.core.dto.client.MinimalContactDTO(
c.id, c.version, c.name, c.title, c.email, c.createdDate, p)
from CONTACT c
JOIN c.phones p
where
(
c.localRecordStatus IS NULL OR
c.localRecordStatus IN (:openStatusList) )
)
Then, you can transform the table-like ResutSet into a graph using a Hibernate ResultTransformer.