Hibernate @OneToMany抛出MySQLSyntaxErrorException：你的SQL语法有错误 [英] Hibernate @OneToMany throws MySQLSyntaxErrorException: You have an error in your SQL syntax

问题描述

我尝试从联系人中的一些字段以及电话列表检索列表。

为此，我正在使用联系人查询;我还创建了一个只包含我需要的字段的DTO。

查询是：

  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（）; 
  

DTO的代码是：

<$ p $ b $ this.id = id; 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 =电子邮件;
this.createdDate = createdDate;
this.phones =电话;
}

联系人：

  @Entity（name =CONTACT）
 public class Contact实现可识别，可序列化{
 public static final long serialVersionUID = 1L; 
 
 @Id 
 @GeneratedValue（strategy = GenerationType.AUTO）
私有长ID; 
 
私人长版; 
 
私人字符串名称; 
 
私人字符串电子邮件; 
 
私有字符串标题; 
 
 @OneToMany（级联= CascadeType.ALL，取= FetchType.EAGER，orphanRemoval =真）
 @JoinColumn（名称= CONTACT_ID，referencedColumnName = ID）
 @OrderBy（id ASC）
私人设置<联系电话> phones = new HashSet<>（）; 
 
  

联络电话：

 公共类ContactPhone实现可识别，可序列化{
 public static final long serialVersionUID = 1L; 
 
 @Id 
 @GeneratedValue（strategy = GenerationType.AUTO）
 @Column（name =ID）
私人长ID; 
 
 @ManyToOne 
 @JoinColumn（name =CONTACT_ID）
私人联系人联系人; 
 
私人字符串编号; 
 
 @ManyToOne（cascade = {CascadeType.PERSIST}）
 @JoinColumn（name =PHONE_LABEL_ID）
私人ContactPhoneLabel标签; 
} 
  

mySql ：Ver 14.14 Distrib 5.7.16，对于Win64（x86_64）

我收到一个错误，我不知道有什么问题。有没有人有任何想法？

错误：

  
导致：org.hibernate.exception.SQLGrammarException：无法从org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert（SQLExceptionTypeDelegate.java:63）
处获取org.hibernate中的ResultSet 
。 exception.internal.StandardSQLExceptionConverter.convert（StandardSQLExceptionConverter.java:42）
处org.hibernate.engine org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert（SqlExceptionHelper.java:109）
。 jdbc.spi.SqlExceptionHelper.convert（SqlExceptionHelper.java:95）
在org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract（ResultSetReturnImpl.java:79）
在org.hibernate.loader。 Loader.getResultSet（Loader.java:2117）
在org.hibernate.loader.Loader.executeQueryStatement（Loader.java:1900）
在org.hibernate.loader.Loader.executeQueryStatement（Loader.java： 1876年）
在org.hibernate.loader.Loader.doQuery（Loader.java:919）
 org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections（Loader.java:336）
 org.hibernate.loader.Loader.doList（Loader.java:2617）
 at org.hibernate.loader.Loader .doList（Loader.java:2600）
在org.hibernate.loader.Loader.listIgnoreQueryCache（Loader.java:2429）
在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）
位于org.hibernate.jpa.internal.QueryImpl.list（QueryImpl.java:606）
位于org.hibernate.jpa。 internal.QueryImpl.getResultList（QueryImpl.java:483）
 ... 203 more 
引起：com.mysql.jdbc.exceptions.jdbc4.M ySQLSyntaxErrorException：您的SQL语法中有错误;检查与您的MySQL服务器版本相对应的手册，以便在第1行的'contact colour_6_0_ from contact contact0_ inner join CONTACT_PHONE phones1_ on contact0'上使用正确的语法
 at sun.reflect.NativeConstructorAccessorImpl.newInstance0（Native Method）
。在sun.reflect.NativeConstructorAccessorImpl.newInstance（NativeConstructorAccessorImpl.java:62）
处java.lang.reflect.Constructor中sun.reflect.DelegatingConstructorAccessorImpl.newInstance（DelegatingConstructorAccessorImpl.java:45）
。 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.jdb c.MysqlIO.sqlQueryDirect（MysqlIO.java:2617）
 at com.mysql.jdbc.ConnectionImpl.execSQL（ConnectionImpl.java:2550）
 at com.mysql.jdbc.PreparedStatement.executeInternal（PreparedStatement。 Java的：1861）在com.mysql.jdbc.PreparedStatement.executeQuery（PreparedStatement.java:1962 
）
在org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery（WrappedPreparedStatement.java:504） 
 at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract（ResultSetReturnImpl.java:70）
 ... 219 more 
 
 
 
。由于：javax.persistence.PersistenceException：org.hibernate.exception.SQLGrammarException：无法从org.hibernate.jpa.spi中提取ResultSet 
。   .AbstractEntityManagerImpl.convert（AbstractEntityManagerImpl.java:1692）
在org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert（AbstractEntityManagerImpl.java:1602）
在org.hibernate.jpa.internal.QueryImpl.getResultList （QueryImpl.java:492）
在处sun.reflect.NativeMethodAccessorImpl sun.reflect.NativeMethodAccessorImpl.invoke0（本机方法）
 com.tim.core.persistence.client.impl.ContactPersistenceImpl.getAllContactsDTO（ContactPersistenceImpl.java:71）
。调用（NativeMethodAccessorImpl.java:62）
在sun.reflect.DelegatingMethodAccessorImpl.invoke（DelegatingMethodAccessorImpl.java:43）
在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）
在org.jboss.as.weld.ejb.Jsr299BindingsInterceptor.doMethodInterception（Jsr299BindingsInterceptor.java:82）
在org.jboss .as.weld.ejb.Jsr299BindingsInterceptor.processInvocation（Jsr299BindingsInterceptor.java:93）
在o rg.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）
在org.jboss.weld.ejb.AbstractEJBRequestScopeActivationInterceptor.aroundInvoke（AbstractEJBRequestScopeActivationInterceptor.java:64）
在org.jboss.as.weld.ejb.EjbRequestScopeActivationInterceptor .processInvocation（EjbRequestScopeActivationInterceptor。的java：在org.jboss.invocation.InterceptorContext.proceed（InterceptorContext.java:340 83）
）
。在org.jboss.as.ee.concurrent.ConcurrentContextInterceptor.processInvocation（ConcurrentContextInterceptor.java:45） 
在org.jboss.invocation.InterceptorContext.proceed（InterceptorContext.java:340）
在org.jboss.invocation.InitialInterceptor.processInvocation（InitialInterceptor.java:21）
在org.jboss .invocation.InterceptorContext.proceed（InterceptorContext.java:340）
在org.jboss.invocation.ChainedInterceptor.processInvocation（ChainedInterceptor.java:61）
在org.jboss.as.ee.component.interceptors .ComponentDispatcherInterceptor.processInvocation（ComponentDispatcherInterceptor.java:52）
在org.jboss.invocation.InterceptorContext.proceed（InterceptorContext.java:340）
在org.jboss.as.ejb3.component.pool.PooledInstanceInterceptor .processInvocation（PooledInstanceInterceptor.java:51）
在org.jboss.invocation.InterceptorContext .proceed（InterceptorContext.java:340）
 at org.jboss.as.ejb3.tx.CMTTxInterceptor.invokeInCallerTx（CMTTxInterceptor.java:254）
 ... 173 more 
 

Hibernate日志：

  2017-04-18 18：53：14,748 DEBUG [org.hibernate.hql.internal.ast.ErrorCounter]（MyScheduler_Worker-1）throwQueryException（）：没有错误
 2017-04-18 18:53 ：14,766 DEBUG [org.hibernate.hql.internal.ast.QueryTranslatorImpl]（默认任务-1）--- HQL AST --- 
 \- [QUERY]节点：'query'
 + -  [SELECT_FROM]节点：'SELECT_FROM'
 | +  -  [FROM]节点：'from'
 | | \- [RANGE]节点：'RANGE'
 | | +  -  [DOT]节点：'。'
 | | | +  -  [DOT]节点：'。'
 | | | | +  -  [DOT]节点：'。'
 | | | | | +  -  [DOT]节点：'。'
 | | | | | | +  -  [DOT]节点：'。'
 | | | | | | | +  -  [IDENT]节点：'com'
 | | | | | | | \- [IDENT]节点：'tim'
 | | | | | | \- [IDENT]节点：'core'
 | | | | | \- [IDENT]节点：'model'
 | | | | \- [IDENT]节点：'client'
 | | | \- [IDENT]节点：'Contact'
 | | \- [别名]节点：'c'
 | \- [SELECT]节点：'SELECT'
 | \- [CONSTRUCTOR]节点：'（'
 | +  -  [DOT]节点：'。'
 | | +  -  [DOT]节点：'。'
 | | | -  [DOT]节点：'。'
 | | | | +  -  [DOT]节点：'。'
 | | | | | +  -  [DOT]节点：'。'
 | | | | | | +  -  [IDENT]节点：'com'
 | | | | | | \- [IDENT]节点：'tim'
 | | | | | \- [ IDENT] Node：'core'
 | | | | \- [IDENT]节点：'dto'
 | | \- [IDENT]节点：'client'
 | \- [IDENT]节点：'MinimalContactDTO'
 | +  -  [DOT]节点：'。'
 | | +  -  [IDENT]节点：'c'
 | | \ -  [IDENT]节点：'id'
 | +  -  [DOT]节点：'。'
 | | +  -  [IDENT]节点：'c'
 | | \- [ IDENT]节点：'version'节点：'c'
 | | \- [IDENT]节点：'name'
 | +  -  [DOT]节点：'。'
 | | +  -  [IDENT]节点：'c'
 | | \- [IDENT]节点：'title'
 | +  -  [DOT]节点：'。'
 | | +  -  [IDENT]节点：'c'
 | | \- [IDENT]节点：'email'
 | +  -  [DOT]节点：'。'
 | | +  -  [IDENT]节点：'c'
 | | \- [IDENT]节点：'createdDate'
 | \- [DOT]节点：'。'
 | +  -  [IDENT]节点：'c'
 | \- [IDENT]节点：'phones'
 \- [WHERE]节点：'where'
 \- [OR]节点：'OR'
 +  -  [IS_NULL]节点：'为空'
 | \- [DOT]节点：'。'
 | +  -  [IDENT]节点：'c'
 | \- [IDENT]节点：'localRecordStatus'
 \- [IN]节点：'in'
 +  -  [DOT]节点：'。'
 | +  -  [IDENT]节点：'c'
 | \- [IDENT]节点：'localRecordStatus'
 \- [IN_LIST]节点：'inList'
 \  -  [COLON]节点：'：'
 \- [IDENT ] Node：'openStatusList'
 
 2017-04-18 18：53：14,767 DEBUG [org.hibernate.hql.internal.ast.ErrorCounter]（默认任务-1）throwQueryException（）：无错误
 
 
 ... 
 
 
 2017-04-18 18：53：14,806 DEBUG [org.hibernate.hql.internal.ast.HqlSqlWalker ]（默认任务-1）processQuery（）：（SELECT（{select clause）（（（。（。（。（。（。com tim）core）dto）client）MinimalContactDTO）（contact0_.ID contact0_.ID id） （contact0_.VERSION contact0_.ID版）（contact0_.name contact0_.ID名）（contact0_.title contact0_.ID标题）（contact0_.email contact0_.ID电子邮件）（contact0_.CREATED_DATE contact0_.ID createdDate）（。contact0_.ID电话）））（FromClause {level = 1}（CONTACT contact0_ CONTACT_PHONE phones1_））（其中（OR（是null（contact0_.LOCAL_RECORD_STATU S 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]（默认任务-1）使用FROM片段[CONTACT contact0_] 
 2017-04-18 18：53：14,808 DEBUG [org.hibernate.hql.internal.ast.util.JoinProcessor]（默认任务-1）使用FROM片段[内联接CONTACT_PHONE phones1_ on contact0_.ID = phones1_.CONTACT_ID ] 
 2017-04-18 18：53：14,808 DEBUG [org.hibernate.hql.internal.antlr.HqlSqlBaseWalker]（默认任务-1）选择>>结束[level = 1，statement = select] 
 2017-04-18 18：53：14,809 DEBUG [org.hibernate.hql.internal.ast.QueryTranslatorImpl]（默认任务-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元素} 
 | | +  -  [DOT] DotNode：'。'{propertyName = null，dereferenceType = UNKNOWN，getPropertyPath = null，path = com.tim.core.dto.client，no from元素} 
 | | | +  -  [DOT] DotNode：'。'{propertyName = null，dereferenceType = UNKNOWN，getPropertyPath = null，path = com.tim.core.dto，no来自元素} 
 | | | | +  -  [DOT] DotNode：'。'{propertyName = null，dereferenceType = UNKNOWN，getPropertyPath = null，path = com.tim.core，no从元素} 
 | | | | + [DOT] DotNode：'。'{propertyName = null，dereferenceType = UNKNOWN，getPropertyPath = null，path = com.tim，no来自元素} 
 | [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'{别名= 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'{别名= 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'{别名= 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'{别名= c，className = com.tim.core.model.client.Contact，tableAlias = contact0_} 
 | | \- [IDENT] IdentNode：'title'{originalText = title} 
 | +  -  [SELECT_COLUMNS] SqlNode：'as col_3_0_'
 | +  -  [点] DotNode： 'contact0_.email'{propertyName的电子邮件=，dereferenceType = PRIMITIVE，getPropertyPath =电子邮件，路径= c.email，tableAlias = contact0_，类名= com.tim.core.model.client.Contact，classAlias = c} 
 | | +  -  [ALIAS_REF] IdentNode：'contact0_.ID'{别名= 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'{别名= 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'{别名= c，className = com.tim.core.model.client.Contact，tableAlias = contact0_} 
 | | \- [IDENT] IdentNode：'phones'{originalText = phones} 
 | \- [SELECT_COLUMNS] SqlNode： '作为col_6_0_' 
 +  -  [FROM] FromClause： '从' FromClause {级= 1，fromElementCounter = 2，fromElements = 2，fromElementByClassAlias = [C]，fromElementByTableAlias = [phones1_ ，contact0_]，fromElementsByPath = [c.phones]，collectionJoinFromElementsByPath = []，impliedElements = []} 
 | \ [FROM_FRAGMENT] FromElement：'CONTACT contact0_'FromElement {显式，不是集合连接，不是读取连接，读取非惰性属性，classAlias = c，role = null，tableName = CONTACT，tableAlias = contact0_，origin = null，columns = {，className = com.tim.core.model.client.Contact}} 
 | \- [JOIN_FRAGMENT] FromElement： '内部连接上contact0_.ID = phones1_.CONTACT_ID CONTACT_PHONE phones1_' FromElement {明确的，不是一个集合加入，而不是一个fetch连接，获取非延迟特性，classAlias = NULL，角色=融为一体。 tim.core.model.client.Contact.phones，tableName = CONTACT_PHONE，tableAlias = phones1_，origin = CONTACT contact0_，columns = {contact0_.ID，className = com.tim.core.model.client.ContactPhone}} $ b $ $'$'$'$'$'$'$'$'$'$'$'$'$'$'$'$'$' \- [点] DotNode： 'contact0_.LOCAL_RECORD_STATUS'{propertyName的= localRecordStatus，dereferenceType = PRIMITIVE，getPropertyPath = localRecordStatus，路径= c.localRecordStatus，tableAlias = contact0_，类名= com.tim.core.model.client.Contact， classAlias = c} 
 | +  -  [ALIAS_REF] IdentNode：'contact0_.ID'{别名= 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'{别名= 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] （默认任务-1）throwQueryException（）：无错误
 2017-04-18 18：53：14,811 DEBUG [org.hibernate.hql.internal.ast.QueryTranslatorImpl]（MyScheduler_Worker-1）SQL：select systemprop0_。 ID为ID1_0_，systemprop0_.PROPERTY_NAME为PROPERTY2_0_，systemprop0_.PROPERTY_VALUE为PROPERTY3_0_，来自SYSTEM_PROPERTY systemprop0_，其中systemprop0_.PROPERTY_NAME =？ 
 2017-04-18 18：53：14,811 DEBUG [org.hibernate.hql.internal.ast.ErrorCounter]（MyScheduler_Worker-1）throwQueryException（）：无错
 2017-04-18 18： 53：14,811 DEBUG [org.hibernate.hql.internal.ast.QueryTranslatorImpl]（默认任务-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]（默认任务-1）SQL：选择contact0_.ID作为col_0_0_，contact0_.VERSION作为col_1_0_，contact0_.name作为col_2_0_，contact0_.title为col_3_0_，contact0_.email为col_4_0_，contact0_.CREATED_DATE为col_5_0_ ,.如从CONTACT col_6_0_ contact0_内部联接CONTACT_PHONE phones1_上contact0_.ID = phones1_.CONTACT_ID其中contact0_.LOCAL_RECORD_STATUS为空或在contact0_.LOCAL_RECORD_STATUS 
 2017年4月18日18（？）：53：14811 DEBUG [org.hibernate作为.hql.internal.ast.ErrorCounter]（默认任务-1）throwQueryException（）：无错误
  

解决方案

有一个。导致SQL查询问题：

 。因为col_6_0_ from CONTACT contact0 
  

无论如何映射都是错误的，所以您需要先解决这个问题。如果你想知道如何映射一个双向的 @OneToMany 关联，你真的需要阅读这篇文章。

ContactPhone 已经将FK映射到子方：

  @ ManyToOne 
 @JoinColumn（name =CONTACT_ID）
私人联系人联系人; 
  

父级方面，需要使用 mappedBy 改为：

  @OneToMany（cascade = CascadeType.ALL，orphanRemoval = true，mappedBy =contact）
 @OrderBy（id ASC）
私人设置<联系电话> phones = new HashSet<>（）; 
  

但是，那还不是全部。

为什么您要为集合使用 FetchType.EAGER 。这可能是代码嗅觉或反模式。使用代替延迟加载。

由于您使用的是 Set ，因此您需要确保正确实施equals和hashCode。如果您有自然ID（国际电话号码是唯一的），请您应该使用。否则，您可以使用实体标识符，但

更新

另外，您不能选择DTO投影中的一个集合，如下所示：

  SELECT new com.tim.core.dto.client.MinimalContactDTO（c.id ，c.version，c.name，c.title，c.email，c.createdDate，** c.phones **）
  

请记住，ResultSet就像电子表格，不像对象图。

你需要做的是改变你这样的DTO ：

  public MinimalContactDTO（
 Long id，Long version，String name，String title，String email，
 DateDate，ContactPhone电话）{
 ... 
} 
  

现在，您一次只能传递一部手机：

  SELECT new com.tim.core.dto.client.Minima lContactDTO（
 c.id，c.version，c.name，c.title，c.email，c.createdDate，p）
 from CONTACT c 
 JOIN c.phones p 
其中
（
 c.localRecordStatus IS NULL或
 c.localRecordStatus IN（：openStatusList））
）
   ResultTransformer 将类表ResutSet转换为图形。查看本文获取更多关于如何做到这一点的详细信息。
 
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

解决方案
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.

                        
这篇关于Hibernate @OneToMany抛出MySQLSyntaxErrorException：你的SQL语法有错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！