Geek Answers Handbook

Continued Spring SQL ScriptUtils line (using H2)

I have the following SQL script (initDB.sql)

CREATE TABLE FFShareHistorical ( ID int NOT NULL AUTO_INCREMENT, PX_LAST Double DEFAULT NULL, PX_OPEN Double DEFAULT NULL, PX_HIGH Double DEFAULT NULL, PX_LOW Double DEFAULT NULL, PRIMARY KEY (ID))

and would like to execute it using Spring ScriptUtils (4.1.4.RELEASE), i.e.

 Resource rc = new ClassPathResource("initDB.sql"); ScriptUtils.executeSqlScript(dataSource.getConnection(), rc);

The problem is that it seems that line breaks are interpreted as the completion of an SQL statement, i.e. The error stack trace looks like this:

 org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement at line 1 of resource class path resource [initDB.sql]: CREATE TABLE FFShareHistorical ( ID int NOT NULL AUTO_INCREMENT, ; nested exception is org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement "CREATE TABLE FFSHAREHISTORICAL ( ID INT NOT NULL AUTO_INCREMENT, "; expected "identifier"; SQL statement: CREATE TABLE FFShareHistorical ( ID int NOT NULL AUTO_INCREMENT, [42001-176] at org.h2.message.DbException.getJdbcSQLException(DbException.java:344) at org.h2.message.DbException.getSyntaxError(DbException.java:204) at org.h2.command.Parser.readColumnIdentifier(Parser.java:3068) at org.h2.command.Parser.parseCreateTable(Parser.java:5722) at org.h2.command.Parser.parseCreate(Parser.java:4122) at org.h2.command.Parser.parsePrepared(Parser.java:351) at org.h2.command.Parser.parse(Parser.java:306) at org.h2.command.Parser.parse(Parser.java:278) at org.h2.command.Parser.prepareCommand(Parser.java:243) at org.h2.engine.Session.prepareLocal(Session.java:442) at org.h2.server.TcpServerThread.process(TcpServerThread.java:265) at org.h2.server.TcpServerThread.run(TcpServerThread.java:160) at java.lang.Thread.run(Unknown Source) at org.springframework.jdbc.datasource.init.ScriptUtils.executeSqlScript(ScriptUtils.java:475) at org.springframework.jdbc.datasource.init.ScriptUtils.executeSqlScript(ScriptUtils.java:393) at org.springframework.jdbc.datasource.init.ScriptUtils.executeSqlScript(ScriptUtils.java:372) at ch.qpmlib.flatfiledatabase.jdbc.dao.FFDBSetup.setupDB(FFDBSetup.java:26) at ch.qpmlib.flatfiledatabase.jdbc.main.SpringMain.setupDB(SpringMain.java:30) at ch.qpmlib.flatfiledatabase.jdbc.main.SpringMain.main(SpringMain.java:17) Caused by: org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement "CREATE TABLE FFSHAREHISTORICAL ( ID INT NOT NULL AUTO_INCREMENT, "; expected "identifier"; SQL statement: CREATE TABLE FFShareHistorical ( ID int NOT NULL AUTO_INCREMENT, [42001-176] at org.h2.message.DbException.getJdbcSQLException(DbException.java:344) at org.h2.message.DbException.getSyntaxError(DbException.java:204) at org.h2.command.Parser.readColumnIdentifier(Parser.java:3068) at org.h2.command.Parser.parseCreateTable(Parser.java:5722) at org.h2.command.Parser.parseCreate(Parser.java:4122) at org.h2.command.Parser.parsePrepared(Parser.java:351) at org.h2.command.Parser.parse(Parser.java:306) at org.h2.command.Parser.parse(Parser.java:278) at org.h2.command.Parser.prepareCommand(Parser.java:243) at org.h2.engine.Session.prepareLocal(Session.java:442) at org.h2.server.TcpServerThread.process(TcpServerThread.java:265) at org.h2.server.TcpServerThread.run(TcpServerThread.java:160) at java.lang.Thread.run(Unknown Source)

So the question is, how can I use ScriptUtils with a multi-line SQL script? I am especially looking for some kind of "line break" character that I can add to a script or a way to tell Spring ScriptUtils to delete all newline characters.

+5
source share
3 answers

There is documentation in your archive. Look at the following places:

  • spring -framework-4.1.4.RELEASE-distance / spring -framework-4.1.4.RELEASE / documents / Javadoc-api / org / springframework / JDBC / data source / initialization /
  • spring -framework-4.1.4.RELEASE-distance / spring -framework-4.1.4.RELEASE / documents / spring -framework-reference /

executeSqlScript(...) for internal use of a ResourceDatabasePopulator for executing SQL scripts.

ResourceDatabasePopulator provides a simple object API for programmatically populating, initializing, or cleaning up a database using SQL scripts defined in external resources.

ResourceDatabasePopulator provides options for customization -

  • character encoding
  • operator separator
  • comment delimiters
  • error handling flags used in parsing and running scripts

Each of the configuration options has a default value.

The default statement separator in SQL scripts is set to ";" .

also:

  • static void executeSqlScript(Connection connection, EncodedResource resource) Execute the specified SQL script using the default parameters for statement delimiters, comment delimiters, and exception handling flags.

  • static void executeSqlScript(Connection connection, EncodedResource resource, boolean continueOnError, boolean ignoreFailedDrops, String commentPrefix, String separator, String blockCommentStartDelimiter, String blockCommentEndDelimiter) Execute the specified SQL script.

  • static void executeSqlScript(Connection connection, Resource resource) Execute the specified SQL script using the default parameters for statement delimiters, comment delimiters, and exception handling flags.

However, there is a mechanism to return to "\n" when there is no ";" . See @chuchikaeschtli for more details.

+3
source

As it turned out, there is no problem using a multi-line operator. However, if the statement does not end with a ";", then ScriptUtils will revert to using "\ n" as the delimiter. So in this case, just ending the script with ";" i.e.

 CREATE TABLE FFShareHistorical ( ID int NOT NULL AUTO_INCREMENT, PX_LAST Double DEFAULT NULL, PX_OPEN Double DEFAULT NULL, PX_HIGH Double DEFAULT NULL, PX_LOW Double DEFAULT NULL, PRIMARY KEY (ID));

will do the job.

+5
source

ScriptUtils has explicit support for such scenarios.

Just pass ScriptUtils.EOF_STATEMENT_SEPARATOR as the value for separator .

Here is the Javadoc for EOF_STATEMENT_SEPARATOR :

End of file (EOF) SQL statement delimiter: "^^^ END OF SCRIPT ^^^".

This value can be represented as a separator on executeSqlScript(Connection, EncodedResource, boolean, boolean, String, String, String, String) to indicate that SQL SCRIPT contains one statement (potentially spanning multiple lines) without an explicit statement separator. Note that such SCRIPT should not contain this value; it's just a virtual expression separator.

Hi,

Sam (author of Spring TestContext Framework)

0
source

More articles:


All Articles