Updating counters through Hibernate

Question

This is an extremely common situation, so I'm expecting a good solution. Basically we need to update counters in our tables. As an example a web page visit:

Web_Page
--------
Id
Url
Visit_Count

So in hibernate, we might have this code:

webPage.setVisitCount(webPage.getVisitCount()+1);

The problem there is reads in mysql by default don't pay attention to transactions. So a highly trafficked webpage will have inaccurate counts.

The way I'm used to doing this type of thing is simply call:

update Web_Page set Visit_Count=Visit_Count+1 where Id=12345;

I guess my question is, how do I do that in Hibernate? And secondly, how can I do an update like this in Hibernate which is a bit more complex?

update Web_Page wp set wp.Visit_Count=(select stats.Visits from Statistics stats where stats.Web_Page_Id=wp.Id) + 1 where Id=12345;

Answer 1

A stored procedure offers several benefits:

1. In case the schema changes, the code need not change if it were call increment($id)
2. Concurrency issues can be localized.
3. Faster execution in many cases.

A possible implementation is:

create procedure increment (IN id integer)
begin
    update web_page
      set visit_count = visit_count + 1
      where `id` = id;
end

Answer 2

The problem there is reads in mysql by default don't pay attention to transactions. So a highly trafficked webpage will have inaccurate counts.

Indeed. I would use a DML style operation here (see chapter 13.4. DML-style operations):

Session session = sessionFactory.openSession();
Transaction tx = session.beginTransaction();

String hqlUpdate = "update webPage wp set wp.visitCount = wp.visitCount + 1 where wp.id = :id";
int updatedEntities = s.createQuery( hqlUpdate )
        .setLong( "newName", 1234l )
        .executeUpdate();
tx.commit();
session.close();

Which should result in

update Web_Page set Visit_Count=Visit_Count+1 where Id=12345;

And secondly, how can I do an update like this in Hibernate which is a bit more complex?

Hmm... I'm tempted to say "you're screwed"... need to think more about this.