Stateless ORM Updates/Deletes

• Can you see the issues with this code?
• The extra Select Statement
• No Optimistic Concurrency Checking
• General Rule: Stateless applications and Updates/Deletes
• UpdateSql as an Alternative

Can you see the issues with this code?

In my opinion the following code has two significant issues. Do you know what they are?

// JPA: Code example with Issues

public void updateEmployee(int empId, String name, long salary, Date hireDate){

  EntityManager em = jpaResourceBean.getEMF().createEntityManager();
  try{
    em.getTransaction().begin();
    Employee employee = em.find(Employee.class, empId);

    employee.setName(name);
    employee.setSalary(salary);
    employee.setHiredate(hireDate);

    em.getTransaction().commit();
  }finally{
    em.close();
  }
}

I realise this is just a code example. My concern is that I have seen quite a number of similar code examples for updates (or deletes) and I have not seen any comments to acknowledge the issues with the code.

This is a 'Heads Up' for people in case its not obvious.

These issues exist with both Ebean and JPA (actually ORM in general).

// Ebean: Code example with Issues

public void updateEmployee(int empId, String name, long salary, Date hireDate){

  try{
    Ebean.beginTransaction();
    Employee employee = Ebean.find(Employee.class, empId);

    employee.setName(name);
    employee.setSalary(salary);
    employee.setHiredate(hireDate);

    Ebean.commitTransaction();
  }finally{
    Ebean.endTransaction();
  }
}

The extra Select Statement

The above code performs an additional query. For some this extra performance cost is acceptable. However for people with big busy databases you probably don't want to have an additional query for every update (or delete) performed like this. Your DBA may have words :)

No Optimistic Concurrency Checking

You may also note that Optimistic Concurrency Checking has been bypassed. There is Optimistic Concurrency Checking between the query and the update but not for the 'user think time'. The nasty thing about this is that any 'lost updates' will occur 'silently' - and no one may notice for some time.

Refer Optimistic Concurrency Checking

General Rule: Stateless applications and Updates/Deletes

For me there is a general rule that for Stateless applications (where you don't have the original bean) and you perform updates or deletes you need to think about these two issues.

ORM is not necessarily the best approach in this situation.

UpdateSql as an Alternative

If you are looking to perform the update in a single statement and also take into account Optimistic Concurrency Checking you can use code like this below.

// Note: We need our 'Version' Column/Property (Typically stored in Html hidden input) 
// which is updTime in this example 

public void updateEmployee(int empId, String name, long salary, Date hireDate, Timestamp updTime){

  // Put this SQL in a file (updateEmp.sql)
  // "update emp set name=:name, salary=:salary, hiredate=:hiredate where id=:id and updTime=:updTime

  String sql = Ebean.getSql("updateEmp");
  
  UpdateSql update = new UpdateSql(sql);
  update.setParameter("name", name);
  update.setParameter("salary", salary);
  update.setParameter("hideDate", hideDate);

  update.setParameter("id", empId);

  // this is the version column/property
  update.setParameter("updTime", updTime);

  int rows = Ebean.execute(update);
  if (rows == 0) {
    String msg = "This Employee has been changed by someone else";
    throw new OptimisticLockingException(msg);
  }
}