Using Spring Batch with auto-configure and a non-standard database

Question

I am attempting to create a Spring Batch application. We use a SQL Anywhere database, which is effectively SQLSERVER, a known database type. To make things easier I am using @SpringBootApplication on my main class and @EnableBatchProcessing on my configuration class.

The problem is that my database driver, sybase.jdbc4.sqlanywhere.IDriver, returns a product name "SQL Anywhere" and this is not recognized by Spring, causing various errors. I was able to get past some of them by explicitly creating a JobRepositoryFactoryBean in my configuration class:

/**
 * We can't rely on Spring Boot as it can't set the database type properly.
 * 
 * By explicitly requiring the arguments in the constructor, we force the Autowiring
 * to occur.
 */
@Bean
public JobRepositoryFactoryBean jobRepositoryFactory(DataSource ds, PlatformTransactionManager tm) {
    JobRepositoryFactoryBean jf = new JobRepositoryFactoryBean();

    jf.setDataSource(ds);
    jf.setTransactionManager(tm);
    jf.setDatabaseType("SQLSERVER");
    jf.setTablePrefix("DBA.BATCH_");
    jf.setIsolationLevelForCreate("ISOLATION_SERIALIZABLE");    // only one instance at a time

    return jf;
}

However, DefaultBatchConfigurer fails in the intialize function, because it explicitly constructs its own JobExplorerFactoryBean.

I wonder if there is some simple way around this, or if I will have to duplicate the work in the DefaultBatchConfigurer class and define all the beans myself and remove the @EnableBatchProcessing annotation.

Erica Kane · Accepted Answer · 2017-04-06T15:38:00.693

I was able to solve this, and I hope it helps anyone trying to use Spring Batch with a database that is out-of-the-box. It is necessary to extend DefaultBatchConfigurer and override the createJobRepository() function. Also, you should disable the automatic job table creation.

Here is the class I created that can be used as a base for any SQL Anywhere Spring Batch job:

@EnableBatchProcessing
public class SqlAnywhereBatchConfigurer extends DefaultBatchConfigurer {

  @Autowired
  private DataSource dataSource;
  @Autowired
  private PlatformTransactionManager transactionManager;

  public SqlAnywhereBatchConfigurer() {
      super();
  }

  public SqlAnywhereBatchConfigurer(DataSource dataSource) {
      super(dataSource);
  }

  @Override
  protected JobRepository createJobRepository() throws Exception {
    JobRepositoryFactoryBean factory = new JobRepositoryFactoryBean();
    factory.setDataSource(dataSource);
    factory.setTransactionManager(transactionManager);
    factory.setDatabaseType("SQLSERVER");
    factory.afterPropertiesSet();
    return factory.getObject();
  }
}

Remember to use the schema-sqlserver.sql to set up your tables first.

You must define a datasource in your configuration, in application.properties I have:

# database
spring.datasource.url=jdbc:sqlanywhere:Server=<your server name>;port=2638
spring.datasource.username=<your username>
spring.datasource.password=<your password>
spring.datasource.driver-class-name=sybase.jdbc4.sqlanywhere.IDriver
# don't create tables on startup
spring.datasource.initialize=false
spring.batch.initializer.enabled=false

Finally, it is worth mentioning here that for SQL Anywhere at least the JdbcCursorItemReader does not work, because setting the fetch direction of the prepared statement causes a "not supported" SQL Exception to be thrown. You can get around this by extending JdbcCursorItemReader and overriding the applyStatementSettings function (plus a few setters) in your own class.

Using Spring Batch with auto-configure and a non-standard database

1 Answers1

Linked