SQL 'limit' the number of results in Hibernate


In my java application, I use Hibernate to access objects from the database.  For queries, I often use the Criteria classes.  One time, I needed to apply a limit to a query because I only wanted to show the top 5 objects, then provide the user to a link to another page with full results, if they wanted more. 

So, to apply the limit to my Criteria query, I went:

criteria.setFirstResult(0).setMaxResults(5);

However, I noticed that my page was now only showing the top 2 results.  I was well aware that there were more than 2 results.  Why wasn't it showing 5?  I had set the limit to 5, but why was it only giving me 2 objects?

It turns out that it was because my query was joining with other tables, and the limit of 5 was being applied to some of the intermediate joins along the way.

I learned how to fix it from theotherian's post about the problem.  I love the title: hibernate, joins, and max results: a match made in hell .  In summary, to solve the problem, I had to make a change to my model object.  In my case, the model object is Event.java because my query was returning a List of Event objects.

Throughout Event.java, I looked for all the attributes that were joined with other objects (tables).  Basically, everywhere I had used @OneToMany.  For each of these attributes, beneath the
@OneToMany annotation, I added two more annotations.  First, I changed the fetch mode to SELECT  (so, add this annotation:  @Fetch(FetchMode.SELECT).  Second, I set the batch size to 2 (add this annotation: @BatchSize(size = 2)).

This fixed my problem.  Running the query again, it was now returning my top 5 instead of some mysteriously lower number.

No comments:

Post a Comment

Translate