hibernate timeout question

I've been fighting with trying to get a hibernate criteria-based query to
have a timeout, when it appears the underlying database (mysql4) may
not support query timeouts.

In a tomcat-based webapp, with spring and hibernate3, I am using
criteria-based queries in hibernate templates via callbacks, like
this:

Object results2 = getHibernateTemplate().executeFind(
    new HibernateCallback()
    {
        public Object doInHibernate(Session aSession)
            throws HibernateException
        {
            Criteria criteria = aSession.createCriteria(GeoChain.class);
            criteria.add(Expression.in("endGeoCoordinate", aCoordinates));
            criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
            criteria.setTimeout(1);

            return criteria.list();
        }
     });

The above is a simple example, I have some others which are much more
complicated queries.  We're using mysql4, and sometimes the more
complicated queries end up doing full table scans (even though indexes
are available, this is a whole other issue).  When this happens, as
one might expect, things take way too long.  So what I need to do is
figure out how to limit the query time.

I've read that the mysql4 jdbc drivers don't actually respect query
timeouts, which seems to be true since the timeout on the criteria
object as I've shown in the example does not seem to have any effect.

Without thinking, I put the operation using these queries into it's
own thread so I could keep my own timer, but of course that doesn't
work since the new thread doesn't share the same session as the
original.  After some thought, that  actually seems dangerous given
the non-threadsafe nature of most of the hibernate classes, so in a
way it's probably good it didn't work.

Does anyone know of a way to have/approximate a timeout in this
scenario?

Thanks!

--Tim

_______________________________________________
Juglist mailing list
[email protected]
http://trijug.org/mailman/listinfo/juglist_trijug.org