JPA: Query that returns multiple entities

Question

I'm writing a JPQL query that joins across three tables. In my resultlist I would like to get all three entities per matching row (hope that makes sense).

Any ideas?

Hibernate 3.x is my JPA provider.

score 59 · Accepted Answer · answered Jul 30 '11 at 00:57

59

IIRC, you can do a SELECT o1, o2, o3 FROM EntityA o1, EntityB o2, EntityC o3 WHERE ...., and the result will be a List<Object[3]>, where the array contents will contain the o1,o2,o3 values.

answered Jul 30 '11 at 00:57

Tassos Bassoukos

16,017
2
36
40

12

@John: Additionally, you can invoke constructors from hql, so you could also do `select new Foo(o1, o2, o3)...` and get a List instead of a List – Ryan Stewart Jul 30 '11 at 01:59
4

Ok, but what if I want to use a JPA TypedQuery: myEntityManager.createQuery("select o1, o2 ...", ) – AgostinoX Aug 15 '11 at 21:13
What if you wanted to use setFirstResult that would actually apply to the concatenation of both lists? ie I want to be able to return a paginated list which is the concatenation of the result for the two entities being queried. – Thomas May 29 '14 at 13:17
@Thomas What, like [SQL UNION](http://stackoverflow.com/questions/15080555/jpql-equivalent-of-sql-query-using-unions-and-selecting-constants)? – Tassos Bassoukos May 29 '14 at 15:51
@Tassos, yes but the entities will have different number of fields etc... the more I think about it the more it seems very difficult to have out of the box. I guess I am dreaming of JPA wizardry here... – Thomas May 30 '14 at 07:53
@Thomas, the entities have each their own complement of fields, you're mixing your internal SQL and JPA models :-). `SELECT o1, o2, o3 FROM EntityA o1, EntityB o2 WHERE ...` will return to you rows with exactly 2 columns; if your JPA provider supports `SELECT o1 FROM EntityA o1 UNION ALL SELECT o2 from EntityB o2` then you will get a List with only one item, which is either EntityA or Entity B. – Tassos Bassoukos May 30 '14 at 12:06
And you cannot do a left outer joins between unrelated entities until you upgrade to Hibernate 5.1 https://hibernate.atlassian.net/browse/HHH-16 – kisna Jun 16 '17 at 19:15
How to do this query in a criteria ? – user1735921 Sep 27 '18 at 10:30
but how can we display an array of objects in JSF – Yogesh Doke Mar 27 '19 at 07:58

Cassio Seffrin · Answer 2 · 2014-05-27T01:22:09.187

41

This is a Spring Data sample, however its works the same way in JPA

//HQL query
 @Query("SELECT c,l,p,u FROM  Course c, Lesson l, Progress p, User u "
            + "WHERE c.id=l.courseId AND l.id = p.lessonId AND p.userId = u.id AND u.id=:userId AND c.id=:courseId")
    public List<Object[]> getLessonsWithProgress(@Param("userId") Integer userId, @Param("courseId")Integer courseId);

Then, I call this method and print the results:

List<Object[]> lst = courseRepository.getLessonsWithProgress(userId, courseId);
for (Object o[] : lst) {
    Course c = (Course) o[0];
    Lesson l = (Lesson) o[1];
    Progress p = (Progress) o[2];
    User u = (User) o[3];
    //all the classes: Course, Lesson, Progress and User have the toString() overridden with the database ID;    
    System.out.printf("\nUser: %s \n Lesson: %s \n Progress: %s \n Course: %s",u,l,p,c);
}

The output @Test is here:

User: com.cassio.dao.model.User[ id=1965 ] 
Lesson: com.cassio.dao.model.Lesson[ id=109 ] 
Progress: com.cassio.dao.model.Progress[ id=10652 ] 
Course: com.cassio.dao.model.Course[ id=30 ]

Cheers

edited May 27 '14 at 01:22

answered May 26 '14 at 20:58

Cassio Seffrin

7,293
1
54
54

hi Cassion, but what in case i just want only Coursname,LessionID,ProgressID and username..then what to do? how to handle list data. Because at that time this row can not be case by any class – utkal patel Mar 30 '16 at 06:56
Hi Utkal, considering my sample code, Course, Lesson, Progress and User are objects. You can get the desired String or Integer making @Override in toString() method of each class. In the meantime, the best practice to that case is change the HQL query, instead get Course c, you can do it that way SELECT c.name, l.id, progress.id, and u.firstname FROM ... Thus you will get the Strings and Integers instead the complex objects. – Cassio Seffrin Jun 20 '16 at 23:34
How can I get List – user1735921 Sep 27 '18 at 12:19
1

@user1735921 I dont think CriteriaQuery will be able to return an array of Object[], may you can achieve it trying to adapt it to a generic type , or may using a DTO Object. – Cassio Seffrin Oct 12 '18 at 02:37
1

@CassioSeffrin I realised its not possible, so had to make a custom projection class (or it can also return a List type if we don't wanna use a class where string is the alias to column and object is the value which can be type casted to the actual model field type). – user1735921 Oct 12 '18 at 11:16
1

@user1735921, you made the right choice. If you're working with spring data the projections are the best option, projections are what I mean DTO (data transfer object). Take a look at session 4: https://www.baeldung.com/spring-data-rest-projections-excerpts – Cassio Seffrin Oct 13 '18 at 01:48

Sorter · Answer 3 · 2013-09-09T14:09:27.003

Since You are asking JPA: Query that returns multiple entities, EclipseLink too comes under it. And I reached on this question googling for EclipseLink. So here is my solution. Hope it works for you.

TypedQuery<Object[]> query = entityManager.createQuery("select p from Post p where   p.publisher.pubId= :ID order by p.createdAt desc",
                Object[].class);
query.setParameter("ID", publisherID);

Then you can loop through the result objects and cast them accordingly.

for (Object result : query.getResultList()) {
            myList.add((Post) result);
        }

You can also try this,

Query query = entityManager.createQuery("select p from Post p where   p.publisher.pubId= :ID order by p.createdAt desc");

Reference:http://wiki.eclipse.org/EclipseLink/UserGuide/JPA/Basic_JPA_Development/Querying/JPQL

what if i need to get the data from 2 entities say publisher and books? — fiddle, Jul 27 '15 at 12:42

score 2 · Answer 4 · answered Mar 18 '19 at 09:17

In case of many to one or one to many relationship how to get multiple records of one of the entities? lets say A is one entity and B is another entity but they have one to many relationship and when you get result you expect B has one record and A has more than 1 record? My query is as below but I don't know how shall I get multiple records of 2nd entity?

@Query("SELECT wl, gr FROM WatchList as wl, GeozoneReference gr " +
            "WHERE wl.watchlistId = gr.objWatchList.watchlistId " +
            "AND wl.watchlistId =:watchlistId")
    List<Object[]> findWatchlistByWatchlistId(@Param("watchlistId") Long watchlistId);

JPA: Query that returns multiple entities

4 Answers4

Linked