Hibernate @OneToMany throws MySQLSyntaxErrorExcept

2019-05-23 09:33发布

站内文章 / Java
22 0 0

问题:

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

回答1:

There is a . that's causing the SQL query issue:

. as col_6_0_ from CONTACT contact0

The mappings are wrong anyway, so you need to fix that first. If you want to know how to map a bidirectional @OneToMany association, you really need to read this article.

Since the ContactPhone already maps the FK on the child-side:

@ManyToOne
@JoinColumn(name = "CONTACT_ID")
private Contact contact;

The parent-side, need to use mappedBy instead:

@OneToMany(cascade = CascadeType.ALL, orphanRemoval = true, mappedBy = "contact")
@OrderBy("id ASC")
private Set<ContactPhone> phones = new HashSet<>();

But, that's not all.

Why do you use FetchType.EAGER for a collection. That's either a Code Smell or an Anti-Pattern. Use lazy loading instead.

Since you are using a Set, you need to make sure you implement equals and hashCode properly. If you have a natural id (international phone number is unique), you should use that. Otherwise, you can use the entity identifier but only if you do this trick.

Update

Also, 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**)

Remember that the ResultSet is like a spreadsheet, not like a graph of objects.

What you need to do is change you 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 Hibernate ResultTransformer. Check out this article for more details on how you can do that.



标签： java mysql hibernate hibernate-mapping hsqldb
举报

收藏的人(0)

Ta的文章 更多文章
登录 后发表评论
0条评论
还没有人评论过~