JPA disable execution of ALTER SEQUENCE

Question

I have a Spring Batch with Spring Boot application with an Oracle Wallet to connect and open JPA to handle persistence. I have some entities the primary keys of which are managed with sequences in the database, like this one:

CREATED 11/11/11
LAST_DDL_TIME   11/11/11
SEQUENCE_OWNER  EXAMPLE
SEQUENCE_NAME   EXAMPLE_SEQ
MIN_VALUE   1
MAX_VALUE   9999999999999999999999999999
INCREMENT_BY    1
CYCLE_FLAG  N
ORDER_FLAG  N
CACHE_SIZE  20
LAST_NUMBER 1111

and the corresponding entity annotation:

    @Id 
    @SequenceGenerator(name="seq_examples", sequenceName="EXAMPLE_SEQ", allocationSize = 1)  
    @GeneratedValue(strategy=GenerationType.SEQUENCE, generator="seq_examples")
    @Column (name="ID_EXAMPLE", nullable=false)
    private Integer id_example;

With this code JPA is trying constantly to execute an ALTER SEQUENCE, reading a lot about this, I have got two ways to solve the problem, with Spring Boot config file or with the persistence.xml config file.

I have tried both of them: config ->

    @Configuration
    public class ContextConfiguration {

        @Bean(name="springtest_entitymanager")
        public LocalContainerEntityManagerFactoryBean getEntityManagerFactoryBean(
        @Qualifier("vendorAdapter") JpaVendorAdapter jpaVendorAdapter, 
        @Value("${${enviroment}.databaseSchema}") String databaseSchema,
        @Qualifier("datasourceWalletExampleDB") DataSource dataSource,
        //@Value("${ConnectionFactoryProperties}") String ConnectionFactoryProperties,
        @Value("${${enviroment}.openjpa.ConnectionFactoryProperties}") String 
    ConnectionFactoryProperties,
        @Value("${${enviroment}.openjpa.log}") String logLevel
        ){
    
    Map<String, String> jpaProperties = new HashMap<String, String>();
    jpaProperties.put("openjpa.jdbc.Schema", databaseSchema);
    jpaProperties.put("openjpa.Log", logLevel); 
    jpaProperties.put("openjpa.ConnectionFactoryProperties", ConnectionFactoryProperties);

    jpaProperties.put("openjpa.jdbc.DBDictionary.disableAlterSequenceIncrementBy", "true");

    //debug only
    //jpaProperties.put("openjpa.jdbc.SynchronizeMappings", "buildSchema(ForeignKeys=true)");
    
    LocalContainerEntityManagerFactoryBean localContainerEntityManagerFactoryBean = new 
    LocalContainerEntityManagerFactoryBean();
    localContainerEntityManagerFactoryBean.setPersistenceUnitName("pu_Example");
    localContainerEntityManagerFactoryBean.setDataSource(dataSource);
    localContainerEntityManagerFactoryBean.setJpaVendorAdapter(jpaVendorAdapter);
    localContainerEntityManagerFactoryBean.setJpaPropertyMap(jpaProperties);
    return localContainerEntityManagerFactoryBean;
}
...

and persistence ->

<persistence-unit name="pu_Example"
    transaction-type="RESOURCE_LOCAL">

    <provider>org.apache.openjpa.persistence.PersistenceProviderImpl</provider>

    <class>exampleapp.dao.model.Example</class>
    
    
    <properties>
        <property name="openjpa.Log" value="DefaultLevel=ERROR" />
        <property name="openjpa.ConnectionFactoryProperties" value="PrettyPrint=true, 
    PrettyPrintLineLength=72" />
        <property name="openjpa.jdbc.Schema" value="EXAMPLE" />
        <property name="openjpa.jdbc.DBDictionary" value="DisableAlterSeqenceIncrementBy=true" />
        
    </properties>

</persistence-unit>

But it still keeps doing the alter sequence statement, what more can I do?

Some info that I have already read:

https://issues.apache.org/jira/browse/OPENJPA-2450

https://www-01.ibm.com/support/docview.wss?uid=swg1PI05956

How does the JPA @SequenceGenerator annotation work

Thanks to everybody who read this

did you check [this recomendation](http://openjpa.208410.n2.nabble.com/disableAlterSequenceIncrementBy-not-working-td7588520.html)? — Marmite Bomber, Nov 18 '19 at 19:05

score 0 · Answer 1 · answered Nov 19 '19 at 14:00

Both properties(persistence.xml / configuration class) works fine, but I had to run the application at least one time with alter sequence privilege, probably some pk was not in sync between JPA and the DB in the first run of it, i think because of the DB cache. In this case the DB was migrated from another enviroment.

After the run with alter sequence privileges, the privileges were removed and after few more runs, i have not seen any more issue.

Usefull info that i found about caching in sequences and my help others:

JPA disable execution of ALTER SEQUENCE

1 Answers1