I am new to hibernate and I met a following problem:
I got "Path expected for join!" exception when I tried to run this query:
String hql = "select avg(t.price) from Ticket t JOIN Flight f WHERE f.number = '" + flightNumber + "'";
Query query = this.session.createQuery(hql);
List<Double> list = query.list();
I wanted to select average price of tickets that have been sold for a given flight.
I have checked these links, but I did not solve my problem:
HQL left join: Path expected for join
hql inner join Path expected for join! error
My code is:
Flight.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>
<class name="pck.Flight" table="flight" catalog="airbook">
<id name="id" type="java.lang.Integer">
<column name="id" />
<generator class="identity" />
</id>
<many-to-one name="sourceairport" class="pck.Sourceairport" fetch="select">
<column name="sourceairportid" />
</many-to-one>
<many-to-one name="destinationairport" class="pck.Destinationairport" fetch="select">
<column name="destinationairportid" />
</many-to-one>
<property name="number" type="string">
<column name="number" length="30" />
</property>
<property name="date" type="timestamp">
<column name="date" length="19" />
</property>
<property name="miles" type="java.lang.Integer">
<column name="miles" />
</property>
<property name="numberofseats" type="java.lang.Integer">
<column name="numberofseats" />
</property>
<property name="airplane" type="string">
<column name="airplane" length="30" />
</property>
<set name="tickets" table="ticket" inverse="true" lazy="true" fetch="select">
<key>
<column name="flightid" />
</key>
<one-to-many class="pck.Ticket" />
</set>
</class> </hibernate-mapping>
Ticket.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>
<class name="pck.Ticket" table="ticket" catalog="airbook">
<id name="id" type="java.lang.Integer">
<column name="id" />
<generator class="identity" />
</id>
<many-to-one name="flight" class="pck.Flight" fetch="select">
<column name="flightid" />
</many-to-one>
<many-to-one name="passenger" class="pck.Passenger" fetch="select">
<column name="passengerid" />
</many-to-one>
<property name="price" type="java.lang.Double">
<column name="price" precision="22" scale="0" />
</property>
</class>
</hibernate-mapping>
All the other queries without JOIN work fine. I do not know where the problem is.
The correct query is:
select avg(t.price) from Ticket t join t.flight f where f.number = :flightNumber
And altogether with query execution:
Transaction tx = session.beginTransaction();
String hql = "select avg(t.price) from Ticket t join t.flight f where f.number = :flightNumber";
Query query = this.session.createQuery(hql).setString("flightNumber", flightNumber);
List<Double> list = query.list();
tx.commit();
Best Answer
As explained in the question you linked to, and in the Hibernate documentation, joins use associations between entities. So the correct query is
Also note that using parameters is a much better solution than concatenating values directly in the query. It handles quoting and escaping automatically, and doesn't have any risk of HQL injection.