how to use em.merge() to insert OR update for jpa entities if primary key is generated by database?

Question

I have an JPA entity like this:

@Entity
@Table(name = "category")
public class Category implements Serializable {
    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Basic(optional = false)
    @Column(name = "id")
    private Integer id;

    @Basic(optional = false)
    @Column(name = "name")
    private String name;

    @OneToMany(cascade = CascadeType.ALL, mappedBy = "category")
    private Collection<ItemCategory> itemCategoryCollection;

    //...
}

Use Mysql as the underlying database. "name" is designed as a unique key. Use Hibernate as JPA provider.

The problem with using merge method is that because pk is generated by db, so if the record already exist (the name is already there) then Hibernate will trying inserting it to db and I will get an unique key constrain violation exception and not doing the update . Does any one have a good practice to handle that? Thank you!

P.S: my workaround is like this:

public void save(Category entity) {

    Category existingEntity = this.find(entity.getName());
    if (existingEntity == null) {
       em.persist(entity);
       //code to commit ...
    } else {
        entity.setId(existingEntity.getId());
        em.merge(entity);
        //code to commit ...
    }
}

public Category find(String categoryName) {
    try {
        return (Category) getEm().createNamedQuery("Category.findByName").
                setParameter("name", categoryName).getSingleResult();
    } catch (NoResultException e) {
        return null;

    }
}

Answer 1

How to use em.merge() to insert OR update for jpa entities if primary key is generated by database?

Whether you're using generated identifiers or not is IMO irrelevant. The problem here is that you want to implement an "upsert" on some unique key other than the PK and JPA doesn't really provide support for that (merge relies on database identity).

So you have AFAIK 2 options.

Either perform an INSERT first and implement some retry mechanism in case of failure because of a unique constraint violation and then find and update the existing record (using a new entity manager).

Or, perform a SELECT first and then insert or update depending on the outcome of the SELECT (this is what you did). This works but is not 100% guaranteed as you can have a race condition between two concurrent threads (they might not find a record for a given categoryName and try to insert in parallel; the slowest thread will fail). If this is unlikely, it might be an acceptable solution.

Update: There might be a 3rd bonus option if you don't mind using a MySQL proprietary feature, see 12.2.5.3. INSERT ... ON DUPLICATE KEY UPDATE Syntax. Never tested with JPA though.

Answer 2

I haven't seen this mentioned before so I just would like to add a possible solution that avoids making multiple queries. Versioning.

Normally used as a simple way to check whether a record being updated has gone stale in optimistic locking scenario's, columns annotated with @Version can also be used to check whether a record is persistent (present in the db) or not.

This all may sound complicated, but it really isn't. What it boils down to is an extra column on the record whose value changes on every update. We define an extra column version in our database like this:

CREATE TABLE example
(
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  version INT,   -- <== It really is that simple!
  value VARCHAR(255)
);

And mark the corresponding field in our Java class with @Version like this:

@Entity
public class Example {
    @Id 
    @GeneratedValue
    private Integer id;

    @Version  // <-- that's the trick!
    private Integer version;

    @Column(length=255)
    private String value;
}

The @Version annotation will make JPA use this column with optimistic locking by including it as a condition in any update statements, like this:

UPDATE example 
SET value = 'Hello, World!' 
WHERE id = 23
AND version = 2  -- <-- if version has changed, update won't happen

(JPA does this automatically, no need to write it yourself)

Then afterwards it checks whether one record was updated (as expected) or not (in which case the object was stale).

We must make sure nobody can set the version field or it would mess up optimistic locking, but we can make a getter on version if we want. We can also use the version field in a method isPersistent that will check whether the record is in the DB already or not without ever making a query:

@Entity
public class Example {
    // ...
    /** Indicates whether this entity is present in the database. */
    public boolean isPersistent() {
        return version != null;
    }
}

Finally, we can use this method in our insertOrUpdate method:

public insertOrUpdate(Example example) {
    if (example.isPersistent()) {
        // record is already present in the db
        // update it here
    }
    else {
        // record is not present in the db
        // insert it here
    }
}