Incrementing a counter when using hibernate

[trondgzi]

Hi all!

On my site I would like to have a few randomly selected images shown everytime the user refreshes the page. I'm using Spring 1.1.5, Hibernate 2.1.8 with the OpenSessionInViewFilter


Images are stored on disk and filename, size etc are stored in a mysql table.

To select images:
SELECT id, file, counter from image_table ORDER BY RAND() LIMIT 3;

And then to increment the counter for every image I would:
UPDATE image_table SET counter = counter + 1 WHERE id IN (selected id's);


I have managed to create the select statement using session.createSQLQuery, but I haven't found a way to use the Query interface with an update statement.


Any help would be much appreciated.


Trond

[irbouho]

AFAIK Hibernate2 does not support UPDATE queries. You may either switch to Hibernate3, use plain JDBC or provide a mapping to your Table / Class.

HTH

[trondgzi]

AFAIK Hibernate2 does not support UPDATE queries. You may either switch to Hibernate3, use plain JDBC or provide a mapping to your Table / Class.

I have a mapping for my Class. And my DAO extends the HibernateDAOSupport class. I'm using a HibernateTransactionManager and these transactionAttributes:

<property name="transactionAttributes">
    <props>
        <prop key="getRandomImages">PROPAGATION_REQUIRED</prop>
        <prop key="*">PROPAGATION_REQUIRED,readOnly</prop>
    </props>
</property>

And getRandomImages is implemented like this:

public List getRandomImages() {
    HibernateTemplate ht = getHibernateTemplate();

    Session session = getSession();
    Query selectQuery = session.createSQLQuery("SELECT {img.*} FROM image {img} ORDER BY RAND()", "img", RandomImage.class);
    selectQuery.setMaxResults(3);

    List imgList = null;
    try {
        imgList = selectQuery.list();
    } catch (HibernateException e) {
        throw SessionFactoryUtils.convertHibernateAccessException(e);
    }

    for (RandomImage img : imgList) {
        img.counter++;
        ht.update(img);
    }

    return imgList;
}

I'm no expert at Hibernate (obviously) nor transactions...
Would this be enough to ensure that the counters are incremented properly?
Only one thread will access the method at the time?


Trond

[alesj]

Use native SQL with Hibernate (in order to use rand function). Then simply increment counter property and let Hibernate do the update (while still in open session - no need for explicit call to update - dirty check will pick it up) or call session.update explicitly.