Hibernate appends LIMIT in every query automatically

Once in a morning I woke up with a very special issue in my web application and by afternoon it almost made me to pull out my hair.
I am using Spring hibernateDAOSupport at DAO layer of my application and am using hibernateTemplate with detachedcriteria to fetch records from my DB. This time I was assigned an intermittent issue where in the query API in DAO was not able to fetch correct number of records from my DB.
I always like challenges when it comes to, and I always need a couple of coffee mugs to get in DEBUG mode.

While debugging I jumped right away on the hibernate generated SQL where I was able to trace my issue. The issue was hibernate was appending "LIMIT ?" at end of my query even though I do not intended to do this.

    DetachedCriteria detachedCriteria = DetachedCriteria.forClass(ABC.class);
    detachedCriteria.add(Restrictions.in("col1", phone));
    detachedCriteria.add(Restrictions.isNotNull("col2"));
    list = getHibernateTemplate().findByCriteria(detachedCriteria);

Do you see any setMaXResults statement? Well I also don't as like you. Then why the ??? hibernate appending "LIMIT ?" these two words as add-on and making me smash my head on wall.

Wooooooooo but I stopped there and thought Hibernate Spring can not be blamed for this it should be something which I am doing. and now I at least know where am I going wrong. So lets find solution.

Solution:

Well after certain Google try I found some links, blogs people discussing about this. Now here are some work around for the same.

1. A quick hack is to reset maxResults to null or 0 when you use setMaxResults to fetch limited records from DB

2. Another is bit wordier but a nice approach where you need not to rest max results everytime

    protected List getWrappedStuff() {
    List results = new ArrayList();
    HibernateTemplate hibernateTemplate = getHibernateTemplate();
    hibernateTemplate.setMaxResults(10);

    StringBuilder hsql = new StringBuilder("select * from table1");

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

    return results; 
}

The question really is, is it a nice practice to use HibernateTemplate and how can we use this

All spring templates (hibernate, jdbc, rest, jpa etc.) have the same pros and cons:

Pro: They perform common setup routines for you, let you skip the boilerplate and concentrate on the logic you want.

Con: you are coupling your application tightly to the spring framework. For this reason, Spring recommends that HibernateTemplate no longer be used.

Specifically, what HibernateTemplate did for you was to automatically open and close sessions and commit or rollback transactions after your code executed. However, all of this can be achieved in an aspect-oriented way using Spring's Declarative Transaction Management.

So time for you to think.