Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to join two unrelated entities using JPA and Hibernate

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?

like image 890
juan Avatar asked Aug 06 '12 03:08

juan


People also ask

Can JPQL join operations?

We can also join multiple entities in a single JPQL query: @Test public void whenMultipleEntitiesAreListedWithJoin_ThenCreatesMultipleJoins() { TypedQuery<Phone> query = entityManager.

Can a JPA entity have multiple OneToMany associations?

You can have as many of them as you want.

How do you fetch a one to many DTO projection with JPA and Hibernate?

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.


1 Answers

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.

like image 121
dimas Avatar answered Sep 18 '22 22:09

dimas