CODEWITHSUNDEEP
javahibernatenative-sql
Ben
Ben

Reputation: 552

Hibernate Native SQL Query retrieving entities and collections

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?

Upvotes: 24

Views: 54435

Answers (5)

Basanth Roy
Basanth Roy

Reputation: 6490

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 its argument. Then I constructed that manually.

Upvotes: 1

Ben
Ben

Reputation: 552

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!

Upvotes: 7

ehrhardt
ehrhardt

Reputation: 2396

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.

Upvotes: 40

Matthew Farwell
Matthew Farwell

Reputation: 61695

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 Books. 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.

Upvotes: 13

legendofawesomeness
legendofawesomeness

Reputation: 2911

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();

Upvotes: 1

Related Questions