I have two tables - one containing Address and another containing Photographs. The only common field between them is the PersonID. These were mapped to two POJO Classes Address and Photo. I was able to fetch details in these tables by creating criteria and adding restrictions on the fields . How should we write a join on the two tables. Is it possible to get the result as two objects -Address and Photo.
I want to do a left join so that i can get records of persons without photos as well. I have read that this is possible only using hql but Can this be done using criterias as well?
We can also join multiple entities in a single JPQL query: @Test public void whenMultipleEntitiesAreListedWithJoin_ThenCreatesMultipleJoins() { TypedQuery<Phone> query = entityManager.
You can have as many of them as you want.
Fetching a one-to-many DTO projection with JPA and Hibernate. The postDTOMap is where we are going to store all PostDTO entities that, in the end, will be returned by the query execution. The reason we are using the postDTOMap is that the parent rows are duplicated in the SQL query result set for each child record.
You can easily write HQL query that will return result as two objects using Theta Join (as Adrian noted). Here is an example:
String queryText = "select address, photo from Address address, Photo photo "
+ " where address.personID=photo.personId";
List<Object[]> rows = session.createQuery(queryText).list();
for (Object[] row: rows) {
System.out.println(" ------- ");
System.out.println("Address object: " + row[0]);
System.out.println("Photo object: " + row[1]);
}
As you can see query returns list of Object[] arrays that represents each fetched row. First element of this array will contain one obejct and second element - another.
EDIT:
In case of left join I think you need to use native SQL query (not HQL query). Here how you can do this:
String queryText = "select address.*, photo.* from ADDRESS address
left join PHOTO photo on (address.person_id=photo.person_id)";
List<Object[]> rows = sess.createSQLQuery(queryText)
.addEntity("address", Address.class)
.addEntity("photo", Photo.class)
.list();
This should work for your case.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With