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.

Thursday, November 1, 2012

New city, new focus

This month has been full of changes for my household.

First, I accepted and started my new job with BJC HealthCare in St. Louis, MO. My official title is "Senior Analyst", I am working on the Healthcare Informatics team at the Center for Clinical Excellence, and my primary function is Java development.

Second, my wife, Natalie, the two kids and I have moved from Chicago to St. Louis. We are currently staying with family in the area. We are happy to be closer to Natalie's family in the area, but will miss the other Bishops in northern Illinois that we leave behind.

Our condo in Chicago is (hopefully) in the final stages of selling, so Natalie and I have started the process of looking at apartments and house shopping in the neighborhoods around my new office.

At the time I am writing this, I am well into the third week at my new job, and am wrapping up my first project. It's nice to feel challenged and productive! BJC is a great organization, and the staff are very knowledgeable and professional, but maintain a flexible and friendly environment. So far the job has been great, and I feel a very good fit for me personally and professionally.

My posts on will likely take a turn to more enterprise development topics, and I already have some material to finish writing about some of the inherent benefits and nagging challenges of using hibernate as a data abstraction layer in Java.

So as usual, thanks for following, and stay tuned for more!

Thursday, September 27, 2012

Android 4.1 Jelly Bean coming to the ASUS Transformer Pad Infinity TF700 and Prime TF201

ASUS stated yesterday that the official Android 4.1 Jelly Bean update will be rolling out to two of the ASUS Transformer Pads over the next two days.

There are reports over at XDA that the update has been rolling out to the ASUS Transformer Pad Prime TF201 starting early this morning.

I very recently picked up the ASUS Transformer Infinity TF700, and I absolutely love it, but felt a bit snubbed that the TF300 received the Jelly Bean update before my newer model tablet. It really is a slick device, and nearly a laptop replacement with the attachable keyboard-dock. I will be very excited to get the Jelly Bean goodness of Google Now and Butter UI running on my Infinity when it rolls out tomorrow.

UPDATE: XDA is reporting that the Jelly Bean update started rolling out to TF700's this morning. I can confirm this, as I finished installing the update myself minutes ago. I will try to post my impressions of the update soon!

Original Press Release from:

Thursday, August 2, 2012

"I am the 99%", the overlooked Android fragmentation story

When most people talk about OS fragmentation on Android devices, they usually are talking about vendors who are slow to push out updates to their older devices. More rarely I've heard mentioned the less-than-technical end-users who never bother to install available updates. I've personally been affected by both: Sprint is less than stellar at making their Sense UI updates to Google releases to my last two cellphones, and I am constantly reminding my friends and family of the advantages to getting updates to their phones that are available. For the latter, I've ended up being the guy actually installing the updates for them.

But I think there is an untold story here about a growing percentage of Android enthusiasts who are literally being forced to stay behind the curve. Insert "Help, help, I'm being repressed!" quote here.

I am a techie. I am an Android geek. I am an avid CyanogenMod follower, and simply must install the latest patch, update and sometimes even follow nightly builds when they are available (and arguably stable).

I am still running Gingerbread because Sprint (and now officially CyanogenMod) do not plan to release an ICS or Jellybean update for my device. 

I would spend my hard-earned cash and a lunch break to go buy a new Android smartphone today, but I am still months away from the end of my 2-year contract with Sprint. 

I realize there are some GSM providers that contact you to a SIM card instead of a single device, and that's great for them. But Sprint is largely a CDMA provider, so that isn't an option for me and anyone else who can't simply swap a SIM into a new phone and go. Sprint is literally making it impossible for large populations of their customers to benefit from the latest that Android has to offer by refusing to provide device updates, and making it cost-prohibitive to purchase new phones before the 2-year contractual waiting period is over.

I am one of the 99% not running the latest Android OS because I am contractually obligated to stay there until the end of 2012. Doesn't that seem like a bad business model for Sprint?

Friday, July 20, 2012

Programming is not Algebra... except when it is.

Programming is not Algebra:
... except when it is.

I loved reading the comments/discussion that followed this post.