I am stuck with this Hibernate thing and dont know how to figure it out. Please help !
So I have these two tables:
POSITION
positionid(PK), description
JOB
jobid(PK),positionid(FK),description
How do I use HQL in Hibernate to fetch all jobs with their corresponding position descriptions ?
Edit: So this what I am trying to achieve:
JOBID POSITION.DESCRPTION JOB.DESCRIPTION
1 Teacher Science Teacher
2 Coach Football Coach
and so on for all job's in JOB table. I am trying to figure out what will be HQL for this.
I have put together the following code till now :
position.hbm.xml
<hibernate-mapping>
<class name="com.XXXX.model.Position" table="POSITION">
<id name="positionID" type="int" column="POSITIONID" >
<generator class="assigned"/>
</id>
<property name="description">
<column name="DESCRIPTION" />
</property>
<set name="jobs">
<key column="positionID" />
<one-to-many class="com.XXXX.model.Job" />
</set>
</class>
</hibernate-mapping>
job.hbm.xml
<hibernate-mapping>
<class name="com.XXXX.model.Job" table="JOB">
<id name="jobID" type="int" column="JOBID" >
<generator class="assigned"/>
</id>
<property name="description">
<column name="DESCRIPTION" />
</property>
<many-to-one name="position" class="com.XXXX.model.Position" column="positionID" />
</class>
</hibernate-mapping>
Position.java
public class Position {
private int positionID;
private String description;
private Set<Job> jobs = new HashSet<Job>();
// Getters and Setters for all the above three follows...
}
Job.java
public class Job {
private int jobID;
private String description;
private Position position;
// Getters and Setters for all the above three follows...
}
In my code now I use
session.createQuery("from Position as p left join p.positionID as pid").list();
I know its not exactly correct and I am getting the follow error:
java.lang.NullPointerException
at org.hibernate.hql.ast.HqlSqlWalker.createFromJoinElement(HqlSqlWalker.java:317)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.joinElement(HqlSqlBaseWalker.java:3268)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.fromElement(HqlSqlBaseWalker.java:3060)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.fromElementList(HqlSqlBaseWalker.java:2938)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.fromClause(HqlSqlBaseWalker.java:688)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:544)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.selectStatement(HqlSqlBaseWalker.java:281)
.........................
...........................
Can someone tell me how to fix this please ?
If you only want these three columns, then the HQL should be
select job.id, position.decription, job.description
from Job job
left join job.position position
This query will return a List<Object[]>
and each Object array in the list will contain these three elements.
It would be much more natural however to use this query:
select job from Job job
left join fetch job.position
which would load all the jobs with their position. The query would return a List, and you would be able to access the three information uou want using job.getId()
, job.getPosition().getDescription()
, and job.getDescription()
.
The syntax of HQL is described with examples in the reference documentation, a must-read.