Auto incrementation not working for @Id tagged field


#1

Dear All,

I tried to use HIbernate with the lovely in memory database of H2.
My try was disturbed though, because the auto incrementation of the @Id annontated id field did not happen right.

My entity class is

@Entity(name = "RepoTestClass")
@Table(name = "RepoTestClass")
public class RepoTestClass implements Serializable {

	@Id
	@GeneratedValue(strategy = GenerationType.AUTO)
	Long id;

	@Column(name = "applicationId")
	String applicationId;
	@Column(name = "name")
	String name;
	@Column(name = "status")
	String status;
	@Column(name = "timeOfRecord")
	Long timeOfRecord;

	public RepoTestClass() {
	}

       //Getters and setters
}

So I first initiated the database with an SQL script creating two records in the database. After that I tried to persist another one into it, but it failed.
It failed with an “org.h2.jdbc.JdbcSQLException: Unique index or primary key violation” exception.
I debugged into the code and found that the newly created record would be inserted into the database with the id “1”. What is anomalious, is that my previously prepared records were initialized with the ids 1 and 2.
So my question is that: “Shouldn’t be Hibernate get the last id in the database and then append to it?”
Or saying in another way: "Why does the
Hibernate: create sequence hibernate_sequence start with 1 increment by 1 statement
appears in the log, whilst I have initialized the database with two records already. After this if it doesn’t catch the current state it can do anything (it does increment though), but it gets a wrong value.

Thanks for your helps!

Regards,
Bálint


#2

The problem is that you are mixing manually assigning with automatic ones. When you create records via a scripts, you should use the sequence as well to assign identifiers. Or, you can use negative numbers if you don’t want to have conflicts.


#3

Thank you Vlad to your reply. Can you please provide an example using sequence via SQL?


#4
INSERT INTO my_table(id, name)
VALUES (hibernate_sequence.NEXTVAL, 'Vlad');

#5

Thank you for the detailed instructions, but it did not worked for me.

I initialize the Spring bean of the entity manager factory as follows:

        @Bean
	public LocalContainerEntityManagerFactoryBean entityManagerFactory() {
		LocalContainerEntityManagerFactoryBean em
				= new LocalContainerEntityManagerFactoryBean();
		em.setDataSource(getDataSource());

		JpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
		em.setJpaVendorAdapter(vendorAdapter);
		em.setJpaProperties(additionalProperties());
                em.setPackagesToScan(new String[] { "com.acme.some.package" });

		return em;
	}

	@Bean
	public DataSource getDataSource() {
		EmbeddedDatabaseBuilder builder = new EmbeddedDatabaseBuilder();
		EmbeddedDatabase db = builder
				.setType(EmbeddedDatabaseType.H2)
				.setName("H2DatabaseTest")
				.addScript("init-db-test.sql")
				.build();
		return db;
	}

	Properties additionalProperties() {
		Properties properties = new Properties();
		properties.setProperty("hibernate.hbm2ddl.auto", "update");
		properties.setProperty("hibernate.dialect", "org.hibernate.dialect.H2Dialect");
		properties.setProperty("hibernate.id.new_generator_mappings", "true");
		properties.setProperty("hibernate.show_sql", "true");
		properties.setProperty("hibernate.format_sql", "true");

		return properties;
	}

In this way I use the SQL script that is parsed by the EmbeddedDatabaseBuilder. This builder cannot parse the above mentioned hibernate_sequence.NEXT_VAL field. I get a bunch of errors regarding that, like

org.springframework.jdbc.datasource.init.ScriptStatementFailedException:
Failed to execute SQL script statement #4 of class path resource 
[init-perf-db-test.sql]: INSERT INTO RepoTestClass VALUES ('abc_1234', 'Init-app', 'RUNNING', 1234567, hibernate_sequence.NEXT_VAL); 
nested exception is org.h2.jdbc.JdbcSQLException: Column "HIBERNATE_SEQUENCE.NEXT_VAL" not found;

So what is your suggestion how can I solve the issue?

Thanx,
Bálint


#6

INSERT INTO RepoTestClass VALUES (‘abc_1234’, ‘Init-app’, ‘RUNNING’, 1234567, hibernate_sequence.NEXT_VAL);

Try adding the column names to the INSERT statement. Maybe the id is not the last one. And, try to debug it and see why it fails on H2.


#7

I mean that the issue is that H2 gets initialized before Hibernate, and by default it does not contain the table hibernate_sequence nor its columns - so it cannot read them.


#8

You have two options:

  1. You create the schema yourself:

    • create the DB schema with tables and sequences using the init-db-test.sql script
    • switch to hibernate.hbm2ddl.auto=validate
  2. You create the schema with hibernate.hbm2ddl.auto=update, but use the hibernate.hbm2ddl.import_files configuration to point to the init-db-test.sql which should just add data.


#9

It is all OK to use the init script with the given hibernate.hbm2ddl.import_files option.
My question is though: can use @GeneratedValue(strategy = GenerationType.AUTO) for this or shall I use GenerationType.SEQUENCE?
If I have to use GenerationType.SEQUENCE, with what configuration - regarding that I tried to use it like the followings, but the data given in the init-db-test.sql didn’t appeared in the Hibernate system.

        @Id
	@GeneratedValue(strategy = GenerationType.SEQUENCE, generator="test_sequence")
	@SequenceGenerator(name = "test_sequence", sequenceName = "hibernate_sequence", allocationSize = 1)
	Long id;

Thank you!


#10

Use GenerationType.SEQUENCE if that’s what you want to use in production and don’t care about portability.


#11

Dear Vlad,

I tried to use the second variation you provided, but it did not worked.
At first I used GenerationType.AUTO with hibernate.hbm2ddl.auto=update, but then the init script is not executed.
When I a changed it to hibernate.hbm2ddl.auto=create, then it read up the file, but again the indices collided.

What can the solution be to both use my import script and have Hibernate properly inject id values well.


#12

Tr with FlywayDB. It’s a much better alternative than hbm2ddl.

For the 2nd approach, try with hibernate.hbm2ddl.auto=create. I’m already using this approach. Just fork the repository, uncomment this line, and run this test. You’ll see it working.

Your problem is because EmbeddedDatabase does not work properly with Hibernate. For that, you should ask the question on the Spring forum because it’s outside of the scope of Hibernate.


#13

Thank you! I will go then to the other forum about spring configuration of the DataSource object with H2.


#14

I am using DriverManagerDataSource class now, but updating the database still produces problems.

I get:

2018-07-24 15:41:18.410  WARN 9532 --- [           main] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 23505, SQLState: 23505
2018-07-24 15:41:18.410 ERROR 9532 --- [           main] o.h.engine.jdbc.spi.SqlExceptionHelper   : Unique index or primary key violation: "PRIMARY KEY ON PUBLIC.REPOTESTCLASS(ID)"; SQL statement:
insert into REPOTESTCLASS (applicationId, name, status, timeOfRecord, id) values (?, ?, ?, ?, ?) [23505-192]
2018-07-24 15:41:18.410 ERROR 9532 --- [           main] o.h.i.ExceptionMapperStandardImpl        : HHH000346: Error during managed flush [org.hibernate.exception.ConstraintViolationException: could not execute statement]

Which actually means that after correctly invoking the included sql script, the hibernate_sequence does not get incremented - it starts at 1.

Thanks for answers!


#15

Which actually means that after correctly invoking the included sql script, the hibernate_sequence does not get incremented - it starts at 1.

It means the SQL script did not use the sequence, or you told Hibernate to recreate the schema, therefore discarding anything the script did.

Just debug it to see what happens, and you’ll find out why it does not work.


#16

That can be the problem, that the script does not use the sequence.

But how should it? The solution provided above does not seem to work:

Column "HIBERNATE_SEQUENCE.NEXT_VAL" not found; SQL statement:
INSERT INTO REPOTESTCLASS VALUES (hibernate_sequence.NEXT_VAL, 'abc_1234', 'app_name', 'RUNNING', 1234567)

Thanks.


#17

As already explained, there’s already a solution provided.

Use FlywayDB or investigate with Spring how to make it work with their EmbeddedDatabase.

I already have you an example that works just fine without Spring, so take a look on that too.


#18

#19

#20