This was my "Eureka!", run-around-and-high-five-everyone moment of the day.
THE SETUP
I am using Hibernate via Spring JPA to marshal two database tables to POJOs in my Java application. 99% of the time it is perfectly sufficient to use the Spring JPA standard call to
EntityManager#createQuery() to retrieve database records and marshal them to the appropriate
List<Class> collection.
In this particular example I have the following two classes:
@Entity
public class Library {
@Id
private String id;
@Column
private String name;
@OneToMany(fetch=javax.persistence.FetchType.LAZY)
@JoinColumn(name="id")
private List<Book> books;
....
}
@Entity
public class Book {
@Id
private String bookId;
private String name;
private String libraryId;
....
}
Then in the corresponding HQL query you can simply write:
SELECT DISTINCT library
FROM Library library
LEFT JOIN FETCH library.books
And results from
createQuery().getResultsList() would be returned as a convenient java object collection like so:
List<Library> libraries = entityManager.createQuery(hql).getResultsList();
THE DILEMMA
We use Sybase. Sybase is great, as long as your table statistics are up-to-date. So if you can imagine with me a world where statistics haven't been updated in over 6 months, and you have a scenario where explicitly specifying an index can mean the difference of tens of thousands of physical or logical reads. RE: PERFORMANCE.
But hibernate doesn't let you specify index hints in HQL, nor add them to the Query object after the fact. I also wanted to avoid modifying the existing entity models, so no cheating and changing
fetch.LAZY to
fetch.EAGER for a single use-case.
To restate the desired end result, I want to execute the following SyBase-specific SQL statement with index hints:
SELECT library.*, book.*
FROM Library library (index idx_library_pk)
LEFT JOIN Book book (index idx_book_library_id)
ON (library.Id = book.libraryId)
And end up with a List<Library> collection with the class member collection library.books prefetched, that is to say a list of Libraries with a populated list of Books from a single database query.
POTENTIAL SOLUTIONS... (that didn't work)
In most cases where an index hint is needed I would fall back to Hibernate's createNativeQuery() to send sybase-aware SQL with index hints to the database. For example,
SELECT book.*
FROM Library library (index idx_library_pk)
LEFT JOIN Book book (index idx_book_library_id)
ON (library.Id = book.libraryId)
WHERE library.Id = ?
This would work fine to get a set of Books, and marshal them to a java List<Book>.
But what if I specifically need a List<Library> collection (each instance of Library should have a populated .books collection of the corresponding Book objects)? The closest I could get to this with createNativeQuery() was parsing a List<Object[]> where each object array contained a joined pair of Library and Book objects. Transforming this manually into a List<Library> collection of unique Library objects and then appending each corresponding Book object into the appropriate library.books would be a manual process, inefficient, and a nightmare to read and maintain.
THE SOLUTION
Lo and behold, the Session.createSQLQuery().
Spring JPA keeps a pointer to the Hibernate session in the entityManager object. So if you have an open entityManager object you can perform the following magic:
Session session = (Session)entityManager.getDelegate();
Next, you can create a SQLQuery, which is really just a subtype of a hibernate Query:
SQLQuery query = session.createSQLQuery(sql);
We want to modify this line a bit to tell the SQLQuery that we want to marshal the results to the Library class:
SQLQuery query = session.createSQLQuery(sql) .addEntity( "library", Library.class );
This by itself would generate a List<Library> by directing hibernate to marshal the library alias to Library class objects. But we also have to explain that the book fields in our SQL query should map to the library.books collection in the mapped Library class:
SQLQuery query = session.createSQLQuery(sql) .addEntity( "library", Library.class ) .addJoin( "book", "library.books" );
This implicitly tells hibernate that the book fields will be mapped to Book objects, but this by itself yields us yet again a List<Object[]>, the closest we got with the more familiar Spring/JPA Hibernate getNativeQuery(). Hibernate doesn't inherently know how to properly marshal the books member collection. But where we reached a dead end with the Query object, SQLQuery gives us one last trick:
SQLQuery query = session.createSQLQuery(sql) .addEntity( "library", Library.class ) .addJoin( "book", "library.books" ) .addEntity( "library", Library.class ) .setResultTransformer( Criteria.DISTINCT_ROOT_ENTITY );
Due to a bug we oddly enough have to repeat the addEntity(), because setResultTransformer only works with the last entity referenced (we want a List<Library>, not List<Book>). But this does all the work for us of transforming a correlated list of libraries and books into a List<Library> with a populated member collection of Book objects.
So finally we execute the query, Hibernate transforms and marshals the results, and we can retrieve our POJO list:
List<Library> libraries = query.list();
Hurrah, we've done it!
SUMMARY
If you marshal database records to a Java class object (POJO) with Spring JPA and find the standard Hibernate EntityManager#createQuery() isn't sufficient, you have other options. Hibernate's EntityManager#createNativeQuery() works well when you are dealing with only a single entity. But for more complex relationships involving multiple entities mapped to multiple class objects Session#createSQLQuery() provides an interface to define object relationships and transform the query resultset into the specific class model you need with minimal code changes.
