Hibernate Native SQL Query retrieving entities and collections
Solution 1
HHH-2831 Native SQL queries with addJoin or return object arrays instead of single Entities
This behaviour is caused by a known bug. Doh, should have searched harder!
Solution 2
Expanding on Mathews answer. To force hibernate to only return a list of persons do:
List<Person> peopleWithBooks = session.createSQLQuery(
"select {p.*}, {b.*} from person p, book b where <complicated join>").
.addEntity("p", Person.class)
.addJoin("b", "p.books")
.addEntity("p", Person.class)
.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY)
.list();
Associated Book entities will be fetched and initialized without a additional call to the db.
The duplicate
.addEntity("p", Person.class)
is necessary because
.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY)
operates on the last entity added.
Solution 3
The following works for me:
session.createSQLQuery("select p.*, b.* from person p, book b where <complicated join>").
.addEntity("person", Person.class).addJoin("book", "person.books").list();
This returns an Object[]
containing a list of Person
, each of which contains a list of Book
s. It does this in a single SQL select. I think your problem is that you don't specifically alias person to anything.
EDIT: The method returns an Object[], but the array is populated with Person instances, and only Person instances.
If Hibernate doesn't understand how to map to your classes or if it can't understand how to map the join, it will return a list of objects. Make sure you only have one Person
/Book
combination on each line.
Solution 4
AFAIK, it is not possible to get a "merged" entity back from a SQL query. You will get back only an object array. What I did in this situation was that I created a new constructor for my merged entity that took an array of objects as it's argument. Then I constructed that manually.
Solution 5
Should your query be on the person table instead of person_books?
session.createSQLQuery("select * from person")
.addEntity("person", Person.class)
.addJoin("book", "person.books")
.list();
Ben
Updated on July 05, 2022Comments
-
Ben over 1 year
This is my situation, I have two basic POJO's which I've given a simple hibernate mapping :
Person - PersonId - Name - Books Book - Code - Description
My SQL Query returns rows that look like this :
PERSONID NAME CODE DESCRIPTION -------- ---------- ---- ----------- 1 BEN 1234 BOOK 1 1 BEN 5678 BOOK 2 2 JOHN 9012 BOOK 3
My hibernate query looks like this :
session.createSQLQuery("select personid, name, code, description from person_books") .addEntity("person", Person.class) .addJoin("book", "person.books") .list();
This is per section : 18.1.3 of the hibernate documentation : http://docs.jboss.org/hibernate/core/3.6/reference/en-US/html/querysql.html#d0e17464
What I expect to get in my list is 2 Person Objects with the contained book objects in the collection of books :
List |- Ben | |- Book 1 | '- Book 2 '- John '- Book 3
What I am actually seeing is this :
List |- Object[] | |- Ben | | |- Book 1 | | '- Book 2 | '- Book 1 |- Object[] | |- Ben | | |- Book 1 | | '- Book 2 | '- Book 2 '- Object[] |- John | '- Book 3 '- Book 3
Does anyone know if it's possible to get what I want using this method?
-
Ben about 12 yearsI have simplified this example for the sake of being concise. In my actual usage "person_books" is a complicated query, hibernate has no way of getting the information for a "book" without me providing it in the query. I am retrieving a large number of rows for Person and would prefer to not have to perform an extra query to find the Books for every Person. If I can't find a solution I will probably be using this method but just skipping over rows where the Person doesn't change.
-
Ben about 12 yearsWhat I was expecting was a list of Person, not a list of Object[] with duplicates of people, when they own more than one book. See my answer for the link to the hibernate jira bug report.
-
Matthew Farwell about 12 yearsSorry, to make myself clear, the method returns an Object[], but the array is populated with Person, and only Person. I don't have any books in there.
-
T3rm1 almost 10 yearsThis would fetch everything for Person and Book. What if you wanted to select only certain properties to improve performance. As I understand anything other than * or p.* does not work.
-
v.ladynev over 7 yearsUpdate: correct the link. @ehrhardt answer fixes this issue for Hibernate 5.1: stackoverflow.com/a/17210746/3405171
-
v.ladynev over 7 yearsUsing
{p.*}, {b.*}
also fixes an additional subselects problem forbooks
. -
dhamu about 4 yearsDoes this fetch only a single book per person, regardless the no of books associated with a person? I have a similar problem and used this solution. It returns a set for the 'joined' entity, however doesn't return all the values in it. Set size is 1.
-
Tomáš Záluský over 3 yearsThe workaround with duplicate
.addEntity
helped me. I found this question after issuing similar question with another proposals - stackoverflow.com/q/62554257/653539