I want to load SQL script files for my unit tests. As I am using Spring 2.5.2, I decided to use the SimpleJdbcTestUtils.executeScript()
method to load my script file, using the following code:
DriverManagerDataSource dataSource = ... // getting my DataSource defined in my Spring context
SimpleJdbcTemplate template = new SimpleJdbcTemplate(dataSource);
Resource resource = new ClassPathResource("/create-table.sql");
SimpleJdbcTestUtils.executeSqlScript(template, resource, true);
If I write each SQL statement in one line in the create-table.sql
file, then everything is ok. But if I write a statement on multiple lines, then I get an error, even if the statement is finished by a ;
.
Working script :
CREATE TABLE T_FOO (ID NUMERIC PRIMARY KEY, DATEID TIMESTAMP, IS_ACTIVE INTEGER DEFAULT 0 NOT NULL);
CREATE TABLE T_BAR (ID NUMERIC PRIMARY KEY, DATEID TIMESTAMP, IS_ACTIVE INTEGER DEFAULT 0 NOT NULL);
Not working script:
CREATE TABLE T_FOO (
ID NUMERIC PRIMARY KEY,
DATEID TIMESTAMP,
IS_ACTIVE INTEGER DEFAULT 0 NOT NULL);
CREATE TABLE T_BAR (
ID NUMERIC PRIMARY KEY,
DATEID TIMESTAMP,
IS_ACTIVE INTEGER DEFAULT 0 NOT NULL);
Error on second file:
org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [CREATE TABLE T_FOO (]; nested exception is java.sql.SQLException: Unexpected token: in statement [CREATE TABLE T_FOO (]
at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.translate(SQLStateSQLExceptionTranslator.java:111)
at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.translate(SQLErrorCodeSQLExceptionTranslator.java:322)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:404)
at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:519)
at org.springframework.jdbc.core.simple.SimpleJdbcTemplate.update(SimpleJdbcTemplate.java:237)
at org.springframework.test.jdbc.SimpleJdbcTestUtils.executeSqlScript(SimpleJdbcTestUtils.java:150)
at org.springframework.test.jdbc.SimpleJdbcTestUtils.executeSqlScript(SimpleJdbcTestUtils.java:113)
at foo.bar.HsqldbUtils.run(HsqldbUtils.java:95)
at foo.bar.SomeUnitTest.executeTests(SomeUnitTest.java:63)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.junit.internal.runners.TestMethodRunner.executeMethodBody(TestMethodRunner.java:99)
at org.junit.internal.runners.TestMethodRunner.runUnprotected(TestMethodRunner.java:81)
at org.junit.internal.runners.BeforeAndAfterRunner.runProtected(BeforeAndAfterRunner.java:34)
at org.junit.internal.runners.TestMethodRunner.runMethod(TestMethodRunner.java:75)
at org.junit.internal.runners.TestMethodRunner.run(TestMethodRunner.java:45)
at org.junit.internal.runners.TestClassMethodsRunner.invokeTestMethod(TestClassMethodsRunner.java:71)
at org.junit.internal.runners.TestClassMethodsRunner.run(TestClassMethodsRunner.java:35)
at org.junit.internal.runners.TestClassRunner$1.runUnprotected(TestClassRunner.java:42)
at org.junit.internal.runners.BeforeAndAfterRunner.runProtected(BeforeAndAfterRunner.java:34)
at org.junit.internal.runners.TestClassRunner.run(TestClassRunner.java:52)
at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:45)
at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:460)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:673)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:386)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196)
Caused by: java.sql.SQLException: Unexpected token: in statement [CREATE TABLE T_FOO (]
at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
at org.hsqldb.jdbc.jdbcStatement.fetchResult(Unknown Source)
at org.hsqldb.jdbc.jdbcStatement.executeUpdate(Unknown Source)
at org.springframework.jdbc.core.JdbcTemplate$1UpdateStatementCallback.doInStatement(JdbcTemplate.java:509)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:393)
As I am willing to create more complex tables, I prefer to write the SQL instruction in a more readable way, so using several lines. Is there a way to do that?
You're using Spring 2.5.2, but referring to the 2.5.6 documentation.
If you look at the javadoc for 2.5.2 (see here), you'll note that it doesn't support multi-line scripts.
This was apparently resolved in 2.5.4 (issue here).
You should upgrade to 2.5.6, or better yet, 3.0.x.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With