I'm developing a spring web application using Spring Roo only for the generation of the persistence layer classes (The web application itself is developing by using the ZK framework), making use of Roo active record pattern.
I have the simple problem to retrieve an entity from the db (specifically from a persistent class User, the corresponding table being named my_user), let the the fields be edited in the UI, and then save the modified entity on db.
If I'm doing things right, in my controller classes I have methods for (A) retrieving user data having the username available: this is done by calling
User user = User.findUsersByUsernameEquals(username).getSingleResult()
and seems to work fine (data is correctly shown on the UI) and (B) saving the entity data back to the db. For this I call the method
user.merge()
The problem is that when calling merge(), the following exception is generated and modified data is consequently not saved on the database.
org.hibernate.exception.ConstraintViolationException: could not execute statement; nested exception is javax.persistence.PersistenceException:
org.hibernate.exception.ConstraintViolationException: could not execute statement[SQL: 1062, 23000]
...
caused by:
...
com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException:
Duplicate entry '<username value>' for key '...'
Notes:
1) The User entity as an auto generated Id key. The username field (not key) is marked as --unique when generating with Roo. If I try regenerating the schema removing the unique requirement on username, the effect of merge is creating a second record on the DB (without giving any exception), with the same username and a differend Id value (which is clearly not the desired behaviour of my application).
2) Looking at the logs, hibernate (persistence provider used under JPA), actually tries to perform a sql INSERT statement (I had expected an UPDATE):
[DEBUG] (org.hibernate.SQL:104) insert into my_user (address, email, enabled, firstname, password, surname, telephone, username, version) values (?, ?, ?, ?, ?, ?, ?, ?, ?)
Hibernate: insert into my_user (address, email, enabled, firstname, password, surname, telephone, username, version) values (?, ?, ?, ?, ?, ?, ?, ?, ?)
3) If I invoke user.persist() instead of user.merge() I got the same exception as before (this was just an attempt, invoking .persist() shouldn't be the correct way to save a modified entity if I understood right).
I'm just starting JPA and Roo programming, so I'm not sure if I'm doing things the correct way. I got the impression that the problem might be related with the entity becoming detached between the moment I read data with findUsersByUsernameEquals and merge(), but this is just a supposition.
Thanks in advance,
I think I've found the solution, thanks to this thread: JPA Hibernate merge performs insert instead of update
The problem was due to the fact that Roo automatically adds a version field when creating jpa entities. I was populating my initial database with a set of INSERT INTO where I didn't set values for this field, causing it to be initialized with null.
If I set this to 0 in my INSERT INTO statement the code works. (probably this never happens when creating directly instances from java code with persist(), which I guess correctly initializes the version field)