TIMESTAMP column not updating

Question

I am using Struts2, Spring and Hibernate in my application and database is MySQL 5.5. I have this table in database:

create table if not exists campaigns(
    id int(10) not null auto_increment,
    campaignId int(25) not null unique,
    createdBy int(25) not null REFERENCES users(userId),
    campaignName varchar(255) not null,
    subject varchar(500),
    body varchar(50000),
    modifiedOn TIMESTAMP,
    triggeredOn date,
    numberOfTargets int(10),
    primary key (id, campaignId)
);

And I save and update the "Campaign" objects with the following methods (hibernate-mapping through hbm files) :

public boolean addCampaign(long createdBy, String campaignName) throws NoSuchAlgorithmException {
    Campaign campaignObject = new Campaign();
    SecureRandom generatedHash = SecureRandom.getInstance("SHA1PRNG");
    campaignObject.setCampaignId(new Integer(generatedHash.nextInt()));
    campaignObject.setCreatedBy(createdBy);
    campaignObject.setCampaignName(campaignName);
    getHibernateTemplate().save(campaignObject);
    getSession().flush();
    return true;
}

public Date updateCampaign(String campaignId, String subject, String body) throws NumberFormatException {
    Campaign campaign = getCampaignByCampaignId(Long.parseLong(campaignId));
    if(campaign != null) {
        campaign.setSubject(subject);
        campaign.setBody(body);
        getHibernateTemplate().save(campaign);
        getSession().flush();
        return campaign.getModifiedOn();
    }
    return null;
}

The "modifiedOn" column updates when I run a update query on database. But hibernate is failing to update it. Thanks for your time.

JB Nizet · Accepted Answer · 2011-09-14T09:08:27.797

First of all, save is for inserting a new entity. You should not use it when updating an attached entity. An attached entity's state is automatically written in the database (if changed) at flush time. You don't need to call anything to have the state updated.

And Hibernate won't magically re-read the row it has inserted/updated to get the generated timestamp. A specific @Generated annotation is needed to do that. But it will decrease the performance of the application.

I would use a pre-insert/pre-update hook to set the modifiedOn value programmatically in the entity, and avoid auto-modified timestamps in the database.

score 1 · Answer 2 · edited May 23 '17 at 12:19

1

In addition to JB Nizet's response, if modifiedOn is being updated by a trigger, take a look at org.hibernate.Session#refresh().

In case the field is updated by a trigger, when hibernate saves the entity it has the old date, the trigger udpates the record at DB level (not the hibernate entity), and then when the Session closes, at commit time, Hibernate sees the entity as dirty because the modifiedOn field has a different value in DB. So, another update is launched and it is as if the trigger never updated the field. refresh() will update the entity's state with the one from the DB after the update, and the trigger execution, so they can be in synch at commit time.

public Date updateCampaign(String campaignId, String subject, String body) 
        throws NumberFormatException {
    Campaign campaign = getCampaignByCampaignId(Long.parseLong(campaignId));
    if(campaign != null) {
        campaign.setSubject(subject);
        campaign.setBody(body);
        getHibernateTemplate().update(campaign);
        getSession().flush();
        getSession.refresh(campaign);
        return campaign.getModifiedOn();
    }
return null;
}

edited May 23 '17 at 12:19

Community

1
1

answered Sep 14 '11 at 09:04

Xavi López

27,550
11
97
161

Tried this with a bit of correction, but still the "modifiedOn" column is not updating. Any idea how to debug this instead of setting a value for it ? – tusar Sep 14 '11 at 09:58
Make Hibernate show the queries it is launching by setting `true` in the hibernate config. This way you'll see if the entity is being refreshed, and more important, if another update is being launched at commit time. – Xavi López Sep 14 '11 at 10:07
2

From http://dev.mysql.com/doc/refman/5.0/en/timestamp.html : *Automatic updating does not apply if the TIMESTAMP column is explicitly assigned a value other than NULL*. Since this field is auto-updated by MySQL, you should mark it as insertable = false, updatable = false in the mapping. – JB Nizet Sep 14 '11 at 10:07
1

Also: you don't need to call update. An attached entity is managed by Hibernate automatically. Its state is automatically written to the database at flush time. upate is used to update a *detached* entity. – JB Nizet Sep 14 '11 at 10:09
update="false" insert="false" in hbm mapping is working ! Awesome suggestion @JB Nizet !!! Thanks to all friends for investing time. Again I am confused about which answer to accept... :-( – tusar Sep 14 '11 at 10:33
If you want my opinion, accept JB Nizet's. He has honestly given more valuable approaches. – Xavi López Sep 14 '11 at 10:36
Or you could accept Xavi's one: he told you about refresh, and needs more reputation than I do :-) You should also +1 every useful answer. – JB Nizet Sep 14 '11 at 10:41
@XaviLópez, you too. I am requesting you guys, since you had helped me in the past. Please have a look at my another problem (not related to this ) http://stackoverflow.com/q/8937158/778687 – tusar Apr 03 '12 at 05:31

TIMESTAMP column not updating

2 Answers2