java - java HQL LEFT JOIN: 连接所需的路径

我是Hibernate的新手,我有一个关于HQL LEFT JOIN的问题。

我尝试LEFT JOIN 2个表,patient和provider,在第二个表上得到 "Path expected for join",

下面是2个表和类的映射:

patient.hbm.xmL:


<?xml version="1.0"?>


<!DOCTYPE hibernate-mapping PUBLIC"-//Hibernate/Hibernate Mapping DTD 3.0//EN""http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">


<hibernate-mapping package="com.ccg.db.test">


 <class name="patient" table="patient">


 <id name="patientId" column="patientId" type="int">


 <generator class="native"/>


 </id>


 <property name="patientName" type="string" >


 <column name="patientName" /> 


 </property>


 <property name="providerId" type="string" >


 <column name="provId" /> 


 </property>


 <many-to-one name="provider" column="providerId" class="provider" /> 


 </class>


</hibernate-mapping>



provider.hbm.xml:


<?xml version="1.0"?>


<!DOCTYPE hibernate-mapping PUBLIC"-//Hibernate/Hibernate Mapping DTD 3.0//EN""http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">


<hibernate-mapping package="com.ccg.db.test">


 <class name="provider" table="provider">


 <id name="providerId" column="providerId">


 <generator class="native" />


 </id>


 <property name="providerName" column="providerName" />


 </class>


</hibernate-mapping>



pojo:


patient.java



package com.ccg.db.test;



import java.io.Serializable;


import java.util.List;


import org.hibernate.Session;



public class patient 


implements Serializable


{


 private int patientId;


 private String patientName;


 private String providerId; // foreign key to provider



 private static final long serialVersionUID = 81073;



 public static void load(Session session, List<String> values){


 patient PatientInfo = new patient(); 



 PatientInfo.setPatientId(Integer.parseInt(values.get(0)));


 PatientInfo.setPatientName( values.get(1));


 PatientInfo.setProviderId( values.get(2) );



 session.save( PatientInfo );


 }



 /**


 * @return the PatientId


 */


 public int getPatientId() {


 return patientId;


 }



 /**


 * @param PatientId the PatientId to set


 */


 public void setPatientId(int PatientId) {


 this.patientId = PatientId;


 }



 /**


 * @return the PatientName


 */


 public String getPatientName() {


 return this.patientName;


 }



 /**


 * @param PatientName the PatientName to set


 */


 public void setPatientName(String PatientName) {


 this.patientName = PatientName;


 }



 /**


 * @return the ProvId


 */


 public String getProviderId() {


 return this.providerId;


 }



 /**


 * @param id the ProviderId to set


 */


 public void setProviderId( String id ) {


 this.providerId = id;


 }



 /**


 * @return the ProvId


 */


 public String getProvider() {


 return this.providerId;


 }



 /**


 * @param id the ProviderId to set


 */


 public void setProvider( String id ) {


 this.providerId = id;


 } 



 /**


 * @param args


 */


 public static void main(String[] args) {


 // TODO Auto-generated method stub


 }


}



provider.java:



package com.ccg.db.test;



import java.io.Serializable;


import java.util.List;



import org.hibernate.Session;



public class provider 


implements Serializable


{


 private String providerId;


 private String providerName;



 //private int patientId;


 //private int providerSpec; 



 private static final long serialVersionUID = 81073;



 public static void load(Session session, List<String> values){


 provider ProviderInfo = new provider(); 



 ProviderInfo.setProviderId( values.get(0) );


 ProviderInfo.setProviderName( values.get(1));


 //ProviderInfo.setProviderSpec( Integer.parseInt(values.get(2)) );



 session.save( ProviderInfo );


 }



 /**


 * @return the ProviderName


 */


 public String getProviderName() {


 return providerName;


 }



 /**


 * @param ProviderName the ProviderName to set


 */


 public void setProviderName(String name) {


 this.providerName = name;


 }



 /**


 * @return the ProvId


 */


 public String getProviderId() {


 return this.providerId;


 }



 /**


 * @param id the ProvId to set


 */


 public void setProviderId( String id ) {


 this.providerId = id;


 }



 /*


 public int getPatientId() {


 return this.patientId;


 }



 public void setPatientId( int id ) {


 this.patientId = id;


 }


 */



 /**


 * @param args


 */


 public static void main(String[] args) {


 // TODO Auto-generated method stub


 }


}



以下是LEFT JOIN查询:


select


 pat.patientId, pat.patientName


from


 patient as pat 


left join


 provider as pro 


where


 pat.providerId = pro.providerId



下面是结果:

0:50:08,479 INFO query:156 - Query = outerJoin
10:50:08,479 INFO query:157 - select
pat.patientId, pat.patientName
from
patient as pat 
left join
provider as pro 
where
pat.providerId = pro.providerId
10:50:08,698 ERROR PARSER:33 - Path expected for join!
10:50:08,698 ERROR PARSER:33 - Invalid path: 'pro.providerId'
10:50:08,698 ERROR PARSER:33 - right-hand operand of a binary operator was null
10:50:08,698 ERROR query:184 - Problem generating query.
org.hibernate.hql.ast.QuerySyntaxException: Path expected for join! [select
pat.patientId, pat.patientName
from
com.ccg.db.test.patient as pat 
left join
provider as pro 
where
pat.providerId = pro.providerId
]
at org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:31)
at org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:24)
at org.hibernate.hql.ast.ErrorCounter.throwQueryException(ErrorCounter.java:59)
at org.hibernate.hql.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:235)
at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:160)
at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:111)
at org.hibernate.engine.query.HQLQueryPlan.(HQLQueryPlan.java:77)
at org.hibernate.engine.query.HQLQueryPlan.(HQLQueryPlan.java:56)
at org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:72)
at org.hibernate.impl.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:133)
at org.hibernate.impl.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:112)
at org.hibernate.impl.SessionImpl.createQuery(SessionImpl.java:1623)
at com.ccg.db.query.QueryManager.query(QueryManager.java:163)
at com.ccg.db.query.QueryManager.query(QueryManager.java:139)
at com.ccg.db.query.QueryManager.main(QueryManager.java:80)

时间:

我去掉patient的provider id属性,并且只引用provider,那么你的查询应该如下所示。


select pat.patientId, pat.patientName 


from patient as pat 


left join pat.provider as pro



在你的案例中,join并没有什么意义,因为你没有查询provider的属性,所以查询可能更有意义,


select pat 


from patient as pat 


join pat.provider as pro 


where pat.patientName = 'John' 


and pro.name = 'United Healthcare'



...