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.
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.
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?
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.
create the DB schema with tables and sequences using the init-db-test.sql script
switch to hibernate.hbm2ddl.auto=validate
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.
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.
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.
Tr with Flyway. 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.