Spring Batch Unit Testing with HSQL

I am fairly new to Spring Batch. I was looking at the documentation on:

http://static.springsource.org/sprin...l/testing.html

and

http://static.springsource.org/sprin...structure.html

When I try to run a unit test to start a job, I get the following exception:

1545 [main] INFO org.springframework.jdbc.support.SQLErrorCodesFact ory - SQLErrorCodes loaded: [DB2, Derby, H2, HSQL, Informix, MS-SQL, MySQL, Oracle, PostgreSQL, Sybase]
org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [SELECT JOB_INSTANCE_ID, JOB_NAME from BATCH_JOB_INSTANCE where JOB_NAME = ? and JOB_KEY = ?]; nested exception is java.sql.SQLException: Table not found in statement [SELECT JOB_INSTANCE_ID, JOB_NAME from BATCH_JOB_INSTANCE where JOB_NAME = ? and JOB_KEY = ?]
at org.springframework.jdbc.support.SQLErrorCodeSQLEx ceptionTranslator.doTranslate(SQLErrorCodeSQLExcep tionTranslator.java:220)
at org.springframework.jdbc.support.AbstractFallbackS QLExceptionTranslator.translate(AbstractFallbackSQ LExceptionTranslator.java:72)
at org.springframework.jdbc.core.JdbcTemplate.execute (JdbcTemplate.java:607)
at org.springframework.jdbc.core.JdbcTemplate.query(J dbcTemplate.java:641)
at org.springframework.jdbc.core.JdbcTemplate.query(J dbcTemplate.java:670)
at org.springframework.jdbc.core.JdbcTemplate.query(J dbcTemplate.java:678)
at org.springframework.jdbc.core.JdbcTemplate.query(J dbcTemplate.java:710)
at org.springframework.jdbc.core.simple.SimpleJdbcTem plate.query(SimpleJdbcTemplate.java:187)
at org.springframework.batch.core.repository.dao.Jdbc JobInstanceDao.getJobInstance(JdbcJobInstanceDao.j ava:221)
at org.springframework.batch.core.repository.support. SimpleJobRepository.getLastJobExecution(SimpleJobR epository.java:253)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Nativ e Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Native MethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(De legatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.springframework.aop.support.AopUtils.invokeJoi npointUsingReflection(AopUtils.java:307)
at org.springframework.aop.framework.ReflectiveMethod Invocation.invokeJoinpoint(ReflectiveMethodInvocat ion.java:182)
at org.springframework.aop.framework.ReflectiveMethod Invocation.proceed(ReflectiveMethodInvocation.java :149)
at org.springframework.transaction.interceptor.Transa ctionInterceptor.invoke(TransactionInterceptor.jav a:106)
at org.springframework.aop.framework.ReflectiveMethod Invocation.proceed(ReflectiveMethodInvocation.java :171)
at org.springframework.aop.framework.JdkDynamicAopPro xy.invoke(JdkDynamicAopProxy.java:204)
at $Proxy5.getLastJobExecution(Unknown Source)
at org.springframework.batch.core.launch.support.Simp leJobLauncher.run(SimpleJobLauncher.java:94)
at com.reardencommerce.shared.jobservice.job.JobManag ement.startJob(JobManagement.java:167)
at com.reardencommerce.shared.jobservice.job.JobManag ementTest.testStartJob(JobManagementTest.java:84)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Nativ e Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Native MethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(De legatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.junit.runners.model.FrameworkMethod$1.runRefle ctiveCall(FrameworkMethod.java:44)
at org.junit.internal.runners.model.ReflectiveCallabl e.run(ReflectiveCallable.java:15)
at org.junit.runners.model.FrameworkMethod.invokeExpl osively(FrameworkMethod.java:41)
at org.junit.internal.runners.statements.InvokeMethod .evaluate(InvokeMethod.java:20)
at org.junit.runners.BlockJUnit4ClassRunner.runChild( BlockJUnit4ClassRunner.java:76)
at org.junit.runners.BlockJUnit4ClassRunner.runChild( BlockJUnit4ClassRunner.java:50)
at org.junit.runners.ParentRunner$3.run(ParentRunner. java:193)
at org.junit.runners.ParentRunner$1.schedule(ParentRu nner.java:52)
at org.junit.runners.ParentRunner.runChildren(ParentR unner.java:191)
at org.junit.runners.ParentRunner.access$000(ParentRu nner.java:42)
at org.junit.runners.ParentRunner$2.evaluate(ParentRu nner.java:184)
at org.junit.internal.runners.statements.RunBefores.e valuate(RunBefores.java:28)
at org.junit.runners.ParentRunner.run(ParentRunner.ja va:236)
at org.eclipse.jdt.internal.junit4.runner.JUnit4TestR eference.run(JUnit4TestReference.java:49)
at org.eclipse.jdt.internal.junit.runner.TestExecutio n.run(TestExecution.java:38)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRu nner.runTests(RemoteTestRunner.java:467)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRu nner.runTests(RemoteTestRunner.java:683)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRu nner.run(RemoteTestRunner.java:390)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRu nner.main(RemoteTestRunner.java:197)
Caused by: java.sql.SQLException: Table not found in statement [SELECT JOB_INSTANCE_ID, JOB_NAME from BATCH_JOB_INSTANCE where JOB_NAME = ? and JOB_KEY = ?]
at org.hsqldb.jdbc.Util.throwError(Unknown Source)
at org.hsqldb.jdbc.jdbcPreparedStatement.<init>(Unkno wn Source)
at org.hsqldb.jdbc.jdbcConnection.prepareStatement(Un known Source)
at org.apache.commons.dbcp.DelegatingConnection.prepa reStatement(DelegatingConnection.java:185)
at org.apache.commons.dbcp.PoolingDataSource$PoolGuar dConnectionWrapper.prepareStatement(PoolingDataSou rce.java:278)
at org.springframework.jdbc.core.JdbcTemplate$SimpleP reparedStatementCreator.createPreparedStatement(Jd bcTemplate.java:1322)
at org.springframework.jdbc.core.JdbcTemplate.execute (JdbcTemplate.java:585)
... 44 more

What it seems like is the HSQLDB is not set up, nor running. Exactly, how do I get an in-memory HSQLDB set up and running for my unit test. The 2 documents do not seem to have all the data.

Thanks,

Petet

I figured it out. You have to UN-JAR the spring-batch-core JAR file. Look under org/springframework/batch/core/ for schema-hsqldb.sql. Make sure this file is at the root of the classpath. For Maven users, this would be src/main/resources or src/test/resources.

You could change the batch-hsdqlb.properties file:

batch.schema.script=schema-hsqldb.sql

to

batch.schema.script=org/springframework/batch/core/schema-hsqldb.sql

But I have not tested this yet.

So, just to recap for anyone else looking for this information< here is what you need to get Spring Batch and HSQLDB working for Unit Tests:

• Have batch-hsqldb.properties in src/test/resoureces
• Have business-schema-hsqldb.sql in src/test/resources
• Have schema-hsqldb.sql in src/test/resources

batch-hsqldb.properties and business-schema-hsqldb.sql come from the spring-batch-samples artifact. While, schema-hsqldb.sql comes from spring-batch-core.

Quote:

---

Originally Posted by pminearo

I figured it out. You have to UN-JAR the spring-batch-core JAR file. Look under org/springframework/batch/core/ for schema-hsqldb.sql. Make sure this file is at the root of the classpath. For Maven users, this would be src/main/resources or src/test/resources.

---

For those testing using hsqldb you can also intialize your datasource like this and reference the sql script directly from the spring batch jar:

<jdbc:initialize-database data-source="dataSource" enabled="true">
<jdbc:script location="classpath:/sql/your_custom_test_schema.sql" />
<jdbc:script location="classpath:/sql/your_custom_test_data.sql" />
<jdbc:script location="classpath:/org/springframework/batch/core/schema-hsqldb.sql" />
</jdbc:initialize-database>

You can also look in the sample jobs. They use HSQLDB by default. http://static.springsource.org/sprin...les/index.html