I am developing a Spring application that uses JPA. Specifically, to generate the ids of my entities I am using the @GeneratedValue annotation. For example:
@Entity
public class SomeEntity {
@Id
@GeneratedValue
private Long id;
private String someField;
// Getters and Setters...
}
The thing is, I am preloading data into the database. For example, in someFile.sql I have regular INSERT statements:
INSERT INTO some_entity (id, some_field) VALUES (1, 'foo');
INSERT INTO some_entity (id, some_field) VALUES (2, 'bar');
...
Then, I just insert the data using the mysql command line client:
mysql someDB < someFile.sql
The problem is, when a try to persist a new entity using JPA, the first id which will be assigned is 1. However, that id was already being used by one of the entities I had preloaded to the database. This way, I am getting a duplicate key error:
2020-07-04 16:08:42.365 ERROR 847 --- [https-jsse-nio-8080-exec-9] o.h.engine.jdbc.spi.SqlExceptionHelper : Duplicate entry '1' for key 'PRIMARY'
I have considered using an initial value with a @SequenceGenerator. However, this approach could lead to having to change the code if the number of initial values I want to preload increases.
Any ideas on a different approach?
Well, the simplest solution is to make the script itself establish the correct value for the next generated id.
If you're using Hibernate <5 or Hibernate 5 with hibernate.id.new_generator_mappings=false, your setup will have resulted in id being an AUTO_INCREMENT column. In that case simply add
ALTER TABLE some_entity AUTO_INCREMENT = <last_inserted_id>
at the end of your script. Or, better still, try to get rid of the hardcoded id values altogether, if possible. You can retrieve the last inserted id using LAST_INSERT_ID() and use that value for setting foreign key columns in dependent rows.
If, on the other hand, you're using Hibernate 5 without any special config, you should find a hibernate_sequence table in your schema, which stores the next generated id to be used by Hibernate. You will need to add sth like:
UPDATE hibernate_sequence SET next_val= <max_inserted_id_for_any_table_plus_one>
As a side note, if you're using the latter approach by accident, you might want to switch to the former.