Hibernate Template – Who Are You and Why Are You Adding A Limit To My Query?!

Using Spring and Hibernate can be fun at times, and then there are the days that make you want to pull out your hair.

We are using both on our current project, and saw some weird behaviour on one of our web pages. Every once in a while, we would see that the list of entries that we had on the page in question would be truncated. It often appeared to be limited 10 entries, although I swore I saw 11 once or twice. Every time the server was restarted, the page would appear properly but over time it would get truncated.

I always like a challenge, so I get into my debug mindset (a couple of Cokes followed by shotgunning 3 cigarettes) and turn on hibernate logging. I grab our query from the console:

select
        <a bunch of stuff>
    from
        <a table>
    inner join
        <to a bunch of tables>
    left outer join
        <to a bunch more tables>
    where
        data_source=?

which is exactly what I expected to see. Run the query against the database, and get our 139 rows back. Everything is working fine.

<time passes as I fiddle with the app trying to recreate the truncated list..>

Aha! The list is truncated! I run the query agains the database again, and… get our 139 rows back. So, the query seems to be working fine. Hmmmm.

So let’s look at the query. It’s not dynamic other than a single input:

public Collection<Thing> getThings(String name) {
    DetachedCriteria criteria = DetachedCriteria.forClass(Thing.class);
    DetachedCriteria subCriteria = criteria.createCriteria("owners");
    subCriteria.add(Restrictions.eq("name", name));
    criteria.setResultTransformer(CriteriaSpecification.DISTINCT_ROOT_ENTITY);
    return (List<Thing>)(getHibernateTemplate().findByCriteria(criteria));
}

Doesn’t seem to be an issue there. So I go diving through the console again and I see:

select
        <a bunch of stuff>
    from
        <a table>
    inner join
        <to a bunch of tables>
    left outer join
        <to a bunch more tables>
    where
        data_source=? limit ?

WTF? limit ??! Where is that coming from? I am not limiting anything in the query. I don’t want a limit on the number of results. If I had, I am pretty certain I would have typed it. Stupid ESL computers…

Ok, break point on setMaxResults in CriteriaImpl. Sure enough it is getting called when the above code gets called. But I haven’t set it anywhere…

Or have I?

Search -> File in Eclipse. Type ‘setMaxResults’ and I get one result:

private static final int MAX_TOP_ACTIVITY_COUNT = 10;

protected List<WrappedStuff> getWrappedStuff() {
    List<WrappedStuff> results = new ArrayList<WrappedStuff>();
    HibernateTemplate hibernateTemplate = getHibernateTemplate();
    hibernateTemplate.setMaxResults(MAX_TOP_ACTIVITY_COUNT);

    StringBuilder hsql = new StringBuilder("select s.prop1, count(s.prop2) from Stuff s inner join ... " +
                  "group by s.prop1 order by count(s.prop2) desc, s.prop1 asc");

    List<Object[]> queryResults = hibernateTemplate.find(hsql.toString());
    for (Object[] result : queryResults) {
        results.add(new WrappedStuff((String)result[0], (Long)result[1]));
    }

    return results;
}

Ok, so I am setting the maxResults for this query, but not the other one. I think I can make an attempt at reproducing the bug without having to aimlessly whack away.

Restart the server. Go to the page that executes the first query, see no limit in the SQL. Good. Go to the page that executes the second query, see a limit in that SQL. Go back to the first page, check the SQL and there is limit ? again!

I figure I better ask someone about this. I go talk to my friend, Joe Google, and low and behold the setMaxResults() method on HibernateTemplate never gets reset after a query, unless of course you manually reset it. Well, I’ll be damned.

So, I end up changing the offending query (the one that was setting the maxResults) to

private static final int MAX_TOP_ACTIVITY_COUNT = 10;

protected List<WrappedStuff> getWrappedStuff() {
    List<WrappedStuff> results = new ArrayList<WrappedStuff>();
    HibernateTemplate hibernateTemplate = getHibernateTemplate();
    hibernateTemplate.setMaxResults(MAX_TOP_ACTIVITY_COUNT);

    try {
        StringBuilder hsql = new StringBuilder("select s.prop1, count(s.prop2) from Stuff s inner join ... " +
                      "group by s.prop1 order by count(s.prop2) desc, s.prop1 asc");

        List<Object[]> queryResults = hibernateTemplate.find(hsql.toString());
        for (Object[] result : queryResults) {
            results.add(new WrappedStuff((String)result[0], (Long)result[1]));
        }
    } finally {
        hibernateTemplate.setMaxResults(0); // JDBC Default
    }

    return results;
}

Restart the server, test everything again, and eureka! Everything is magically working as anticipated.

I guess this is just one more reason for the ongoing debate on The end of HibernateTemplate

-mdg

It's only fair to share...
Share on FacebookGoogle+Tweet about this on TwitterShare on LinkedIn

Leave a Reply