Execute SQL file from Spring JDBC Template

java spring jdbctemplate
Ondrej Skalicka picture Ondrej Skalicka · Jun 9, 2015 · Viewed 30.5k times · Source

I'm trying to write a bit of code that reads a SQL file (multiple CREATE TABLE statements separated by ;) and executes all the statements.

In pure JDBC, I could write:

String sqlQuery = "CREATE TABLE A (...); CREATE TABLE B (...);"
java.sql.Connection connection = ...;
Statement statement = connection.createStatement();
statement.executeUpdate(sqlQuery);
statement.close();

and both (all) the statements got executed. When I tried to do the same in spring JdbcTemplate, only the first statement is executed though!

String sqlQuery = "CREATE TABLE A (...); CREATE TABLE B (...);"
org.springframework.jdbc.core.JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
jdbcTemplate.execute(sqlQuery);

Is there a way to execute multiple statements? While googling I found only solutions like "split the sqlQuery by ; manually" which of course is useless (it'd require much more parsing).

Answer

siphiuel picture siphiuel · Jun 9, 2015

Maybe Spring's ScriptUtils will be useful in your case. Especially executeSqlScript methods.

Note that DEFAULT_STATEMENT_SEPARATOR has a default value of ';' (see Constant Field Values)

Related questions

Jdbctemplate query for string: EmptyResultDataAccessException: Incorrect result size: expected 1, actual 0

I am using Jdbctemplate to retrieve a single String value from the db. Here is my method. public String test() { String cert=null; String sql = "select ID_NMB_SRZ from codb_owner.TR_LTM_SLS_RTN where id_str_rt = …

Read More
Spring JDBC Template for calling Stored Procedures

What is the correct way to invoke stored procedures using modern day (circa 2012) Spring JDBC Template? Say, I have a stored procedure that declares both IN and OUT parameters, something like this: mypkg.doSomething( id OUT int, name IN String, …

Read More
How to execute IN() SQL queries with Spring's JDBCTemplate effectivly?

I was wondering if there is a more elegant way to do IN() queries with Spring's JDBCTemplate. Currently I do something like that: StringBuilder jobTypeInClauseBuilder = new StringBuilder(); for(int i = 0; i < jobTypes.length; i++) { Type jobType = jobTypes[i]; if(…

Read More