Spring batc, H2 - DataAccessResourceFailureException Could not obtain last_insert_id() Invalid parameter count for "LAST_INSERT_ID", expected count: "0"; SQL statement

March 24, 2016

Problem:

Environment:

  • Spring Batch
  • Test database: H2
org.springframework.dao.DataAccessResourceFailureException:  
Could not obtain last\_insert\_id();  
nested exception is org.h2.jdbc.JdbcSQLException:  
Invalid parameter count for "LAST\_INSERT\_ID", expected count:  
"0"; SQL statement:

update BATCH_JOB_SEQ set ID = last_insert_id(ID + 1)

<bean id="jobRepository" class="org.springframework.batch.core.repository.support.JobRepositoryFactoryBean">  
    <property name="dataSource" ref="dataSource" />  
    <property name="transactionManager" ref="transactionManager" />  
    <property name="databaseType" value="mysql" />  
</bean>

Solution:

<bean id="jobRepository" class="org.springframework.batch.core.repository.support.JobRepositoryFactoryBean">  
        <property name="dataSource" ref="dataSource" />  
        <property name="transactionManager" ref="transactionManager" />  
        <property name="databaseType" value="h2" />  
</bean>

, besides, i also had to add two sequences to my spring batch scripts:

(...)  
drop sequence if exists BATCH\_JOB\_EXECUTION\_SEQ;  
drop sequence if exists BATCH\_STEP\_EXECUTION\_SEQ;  
(...)  

(...)  
create sequence BATCH\_JOB\_EXECUTION\_SEQ start with 1;  
create sequence BATCH\_STEP\_EXECUTION\_SEQ start with 1;  
(...)