Thursday, November 14, 2013

Adventures in marshalling multiple Java class objects from a custom database query in Spring JPA Hibernate

This was my "Eureka!", run-around-and-high-five-everyone moment of the day.


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:
public class Library {
    private String id;
    private String name;
    private List<Book> books;
public class Book {
    private String bookId;
    private String name;
    private String libraryId;

Then in the corresponding HQL query you can simply write:
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();


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.


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!


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.

Friday, January 25, 2013

It's the little things that make me hate Solaris

It's the little differences in Solaris 10 that really make it hard for me to feel comfortable in my new production environment. So in every distro of Linux going back as far as I can remember, if you want to schedule a cronjob to run every 5 minutes you would format the crontab entry something like this:
*/5 * * * * ~/
But in Solaris, trying to load this into your crontab generates a syntax error. Instead, you are expected to do something like this:
0,5,10,15,20,25,30,35,40,45,50,55 * * * * ~/
Why? Because Solaris 10 is still running a cron that's older than dirt. It's the little things that are starting to make me really hate Solaris.

Friday, January 18, 2013

Reflections on "A Memory of Light", the last novel in The Wheel of Time

Yesterday I finished reading "A Memory of Light", the last book in The Wheel of Time series by Robert Jordan and +Brandon Sanderson

A little over fifteen years ago +Jason Christ introduced the epic WoT series to a skinny Freshman student who had just started working together at a small University tech department. Jason carried a hefty hardback, and the characteristic cover-art of Darrell K. Sweet caught my curiosity, as I had read and loved the Lord of the Rings series by J.R.R. Tolkien since my mother had read to me The Hobbit as a small child. While I was a bit skeptical that any other author could come close to the same depth of character and the scope of their world and plots, I took the recommendation and dove into the first 6 novels that were available at the time. There may have been a few hundred slow pages in the middle, but overall Robert Jordan's vision well met the expectations set by the truly great first volume, "The Eye of the World".

To avoid being too spoiler-ish, for now I will refrain from jumping into a critique of specific plot points in AMoL. But I can say that it was gripping, emotional, mostly satisfying, and sometimes intentionally exhausting end to the series. Brandon Sanderson did a great job of picking up where Robert Jordan left off, and crafted a balanced, well-paced and compelling conclusion to the struggles of characters that readers have seen grown from small-country-folk into complex personalities of nearly deistic powers. The conclusion of the Last Battle between the forces of good and evil felt genuinely climactic, while allowing for a good resolution to *most* of the plot lines that had been building across thousands of pages and in our imaginations for years.

I certainly looked forward to reading this last entry in the series, but to paraphrase Robert Jordan it did not feel as much like The Ending, but it was an ending, and I hope that The Wheel of Time might someday come back around to us again.