Created datetime is not updated after insert in JPA when using trigger in MySQL

Question

ENVIRONMENT

1. Glassfish 4.0
2. JPA - Eclipselink 2.5.0
3. MySQL 5.5.29
4. Trigger in MySQL to Survey table:

Trigger's code

CREATE TRIGGER `survey_BINS` 
BEFORE INSERT ON survey 
FOR EACH ROW
SET NEW.created = NOW();

PROBLEM:

1. Insert a new survey to DB to table Survey
2. Created field got a proper value but entity in UI is still the old one without created datetime
3. If I sign out and refresh the page, I got the created datetime

QUESTION

1. How to avoid this situation?
2. Could @PrePersist notation be the answer in JPA entity?
3. Switching cache off could help, but how is the performance then?
4. Any idea or solution how to get updated entity after insert/update to UI?

Survey ENTITY

@Column(name = "created")
@Temporal(TemporalType.TIMESTAMP)
private Date created;

public Date getCreated() {
    return created;
}

public void setCreated(Date created) {
    this.created = created;
}

SurveyREST-service

@Stateless
@Path("com.insame.entity.survey")
public class SurveyFacadeREST extends AbstractFacade<Survey> {
@PersistenceContext(unitName = "com.sesame_insame_war_1.0-SNAPSHOTPU")
private EntityManager em;

public SurveyFacadeREST() {
    super(Survey.class);
}

@POST
@Override
@Consumes({"application/xml", "application/json"})
public void create(Survey entity) {
    super.create(entity);
}

@PUT
@Override
@Consumes({"application/xml", "application/json"})
public void edit(Survey entity) {
    super.edit(entity);
}

AbstractFacade

public void create(T entity) {
    getEntityManager().persist(entity);
}

public void edit(T entity) {
    getEntityManager().merge(entity);
}

Thanks, Sami

Answer 1

1. To avoid this situation you need to set the field not in database but in java. Because when you save entity it is being inserted into the cache. To get values that were updated in database you need to refresh the entity.

2. Yes this annotation could help you to solve the problem. @PrePersist and @PreUpdate or implementing onSave(Object[] state, String[] propertyNames, Type[] types) or onUpdate with same arguments

3. If you use second level cache and you will switch it off you can loose performance but also it can happen that you will not notice anything. Hibernate 2nd level cache is usually not giving a lot of performance improvement. Better use caching on higher level of abstraction. Such cache can give more performance improvement. But again you will not be able to use database data updates.

4. Use the solution from option number 2: @PrePersist and @PreUpdate.

From my personal experience I would not recommend you to use data updates with the help of triggers. For me they were slowing down database dramatically. Also If you use database triggers or stored proc it is less scalable than using java for it. And also it is difficult to use with caching, because you want to expire necessary cache regions on some data updates.

Answer 2

If you want to use triggers to assign fields on insert, check out EclipseLink's returningPolicy which can be used to retrieve the value after the insert. See http://eclipse.org/eclipselink/documentation/2.4/jpa/extensions/a_returninsert.htm#CIHHEGJE for the annotation description