JPA merge fails due to duplicate key

Question

I have a simple entity, Code, that I need to persist to a MySQL database.

public class Code implements Serializable {

    @Id
    private String key;
    private String description;

    ...getters and setters...
}

The user supplies a file full of key, description pairs which I read, convert to Code objects and then insert in a single transaction using em.merge(code). The file will generally have duplicate entries which I deal with by first adding them to a map keyed on the key field as I read them in.

A problem arises though when keys differ only by case (for example: XYZ and XyZ). My map will, of course, contain both entries but during the merge process MySQL sees the two keys as being the same and the call to merge fails with a MySQLIntegrityConstraintViolationException.

I could easily fix this by uppercasing the keys as I read them in but I'd like to understand exactly what is going wrong. The conclusion I have come to is that JPA considers XYZ and XyZ to be different keys but MySQL considers them to be the same. As such when JPA checks its list of known keys (or does whatever it does to determine whether it needs to perform an insert or update) it fails to find the previous insert and issuing another which then fails. Is this corrent? Is there anyway round this other than better filtering the client data?

I haven't defined .equals or .hashCode on the Code class so perhaps this is the problem.

Answer 1

This depends on how your column is defined in mySQL. mySQL is the oddman out of databases in that VARCHAR and similar columns default to case insensitive matches. If you want XYZ and XyZ to be distinct legal options, you'll need to change your CREATE TABLE statement to create a case sensitive column (see docs for your version of mySQL.)

It's likely something like this:

CREATE TABLE code (
  key VARCHAR(32) BINARY,
  value VARCHAR(32) BINARY
)

Answer 2

I haven't defined .equals or .hashCode on the Code class so perhaps this is the problem.

Well, you really should, you don't want to inherit from the behavior of Object for Entities. Whether you want to use the primary key, do a case sensitive comparison, or use a business identity is another story but you certainly don't want to use reference equality. You don't want the following entities:

Code code1 = new Code();
code1.setKey("abc");

Code code2 = new Code();
code2.setKey("abc");

To be considered as different by JPA.

Second, if you want to be able to insert an Entity with XYZ as key and another one with XyZ, then you should use a case sensitive column type (you can make the varchar column case sensitive by using the binary attribute) or you'll get a primary key constraint violation.

So, to summarize:

• implements equals (and hashCode), decide whether you need case sensitive comparison of the key or not.
• use the appropriate column type at the database level.