A Computer Scientist's personal research notes Technology, Research, Spirituality
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.
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment