[FIXED] JPA Insert + Oracle Sequences and Junit with H2

Issue

I have a Spring MvC project using JPA and Oracle as DB, with this entity:

@Entity
@Table(name = "AUTORISATION_TAURU")
@NoArgsConstructor
@AllArgsConstructor
@Data
@Builder
@EqualsAndHashCode(of = {"autorisationTaurusId"})
public class AutorisationTauru implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "SEQ_TAURU")
@SequenceGenerator(sequenceName = "SEQ_AUTORISATION_TAURUS", allocationSize = 1, name = "SEQ_TAURU")
    @Column(name = "AUTORISATION_TAURUS_ID")
    private Long autorisationTaurusId;
..
}

in my xml config file, I have this;

<bean id="dataSource"
        class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="driverClassName" value="org.h2.Driver" />
    <property name="url" value="jdbc:h2:mem:~/test2;DB_CLOSE_DELAY=-1;MODE=Oracle;INIT=RUNSCRIPT FROM 'classpath:create_db.sql'\;
                    RUNSCRIPT FROM 'classpath:create_db2.sql'\;
                    RUNSCRIPT FROM 'classpath:create_func.sql'" />
    <property name="username" value="sa" />
    <property name="password" value="" />
</bean>



  <bean id="jpaVendorAdapter"
              class="org.springframework.orm.jpa.vendor.EclipseLinkJpaVendorAdapter">
   
        <property name="database" value="H2" />
       <bean id="entityManagerFactory"
              class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
        <property name="dataSource" ref="dataSource" />
        <property name="persistenceUnitName" value="bonanza-entities" />
        <property name="packagesToScan">
            <array>
              <value>com.bonanza.model</value>           
            </array>
        </property>
        <property name="jpaVendorAdapter" ref="jpaVendorAdapter" />
        <property name="jpaProperties">
            <props>
                <prop key="eclipselink.target-database">org.eclipse.persistence.platform.database.OraclePlatform</prop>
                
            </props>
        </property>
    </bean>

I have created the table I am doing the INSERT using the AUTO_INCREMENT option:

CREATE TABLE IF NOT EXISTS AUTORISATION_TAURU
(
  AUTORISATION_TAURUS_ID NUMBER ,

but when I run my local tests, I got this error:

Local Exception Stack: 
Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.7.7.v20200504-69f2c2b80d): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: org.h2.jdbc.JdbcSQLSyntaxErrorException: 
Syntax error in SQL statement "SELECT SEQ_AUTORISATION_TAURUS.NEXTVAL FROM[*] DUAL"; expected "identifier"; SQL statement:
SELECT SEQ_AUTORISATION_TAURUS.NEXTVAL FROM DUAL [42001-200]
Error Code: 42001
Call: SELECT SEQ_AUTORISATION_TAURUS.NEXTVAL FROM DUAL
Query: ValueReadQuery(sql="SELECT SEQ_AUTORISATION_TAURUS.NEXTVAL FROM DUAL")

If I add the sequel creation:

CREATE SEQUENCE SEQ_AUTORISATION_TAURUS
MINVALUE 1 MAXVALUE 9223372036854775807
START WITH 1 INCREMENT BY 1 CACHE 8 NOCYCLE;

I got this error when running the test:

    ... 43 more
Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'jpaMappingContext': Invocation of init method failed; nested exception is javax.persistence.PersistenceException: Exception [EclipseLink-28019] (Eclipse Persistence Services - 2.7.7.v20200504-69f2c2b80d): org.eclipse.persistence.exceptions.EntityManagerSetupException
Exception Description: Deployment of PersistenceUnit [bonanza-entities] failed. Close all factories for this PersistenceUnit.
Internal Exception: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.7.7.v20200504-69f2c2b80d): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: org.h2.jdbc.JdbcSQLSyntaxErrorException: Sequence "SEQ_AUTORISATION_TAURUS" already exists; SQL statement:

Solution

Probably the problem with your setup consists in that your tests are creating several times the Spring application context.

Every time a new test is run it will recreate the dataSource bean and, in addition, it will try to launch the H2 database initialization scripts.

In a normal case, the first test will create the H2 database folder and related stuff, the next will reuse it.

Depending of the content of those scripts it will work most of the times, but not always as in your case.

To avoid that problem you have several options.

On one hand, in this specific case, you can include the clause IF NOT EXISTS in your sequence creation code:

CREATE SEQUENCE IF NOT EXISTS SEQ_AUTORISATION_TAURUS...

In a general case, you can modify your script to take into account this fact and create if not exists the different H2 elements or first DROP and then CREATE every one you need.

On the other hand, Spring Test also offers you the @DirtiesContext annotation for a similar purpose:

Test annotation which indicates that the ApplicationContext associated with a test is dirty and should therefore be closed and removed from the context cache.

And:

@DirtiesContext may be used as a class-level and method-level annotation within the same class or class hierarchy. In such scenarios, the ApplicationContext will be marked as dirty before or after any such annotated method as well as before or after the current test class, depending on the configured methodMode() and classMode().

As you can see, you only need to annotate your class or test methods with this annotation and Spring will recreate the context accordingly:

@DirtiesContext(classMode = ClassMode.BEFORE_EACH_TEST_METHOD)

Please, be aware that this approach will have impact on the performance of your tests because the Spring application context need to be recreated although, on the other hand, it will provide you always a clean and deterministic - according to your initialization scripts - database state.

Answered By - jccampanero