Execute SQL file from Spring JDBC Template

Question

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).

score 26 · Accepted Answer · answered Jun 09 '15 at 12:48

26

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)

answered Jun 09 '15 at 12:48

siphiuel

3,480
4
31
34

1

it seems to work. Does remove comments though (which SQLite can keep as description for columns), but that does not worry me now that much. – Ondrej Skalicka Jun 09 '15 at 13:04
Are there any ways to get number of rows changed using this executeSqlScript() method? – zygimantus Nov 06 '15 at 09:08
executeSqlScript are now deprecated. Any alternative? – Daniele Aug 10 '17 at 16:11
@Daniele usually deprecated methods have a hint on what to use instead in their JavaDoc. However, I don't see any deprecation info even on the latest [ScriptUtils](http://docs.spring.io/autorepo/docs/spring/5.0.x/javadoc-api/org/springframework/jdbc/datasource/init/ScriptUtils.html). Are you looking at a different class? – Dario Seidl Aug 12 '17 at 20:36
1

@DarioSeidl yes true. The deprecation started with spring Spring 4.0.3 in favor of using springframework.jdbc.datasource.init.ResourceDatabasePopulator – Daniele Aug 14 '17 at 09:35
@Daniele may be referring to the indeed deprecated [executeSQLScript](https://docs.spring.io/spring-framework/docs/4.2.6.RELEASE/javadoc-api/org/springframework/test/jdbc/JdbcTestUtils.html#executeSqlScript-org.springframework.jdbc.core.JdbcTemplate-org.springframework.core.io.support.EncodedResource-boolean-) methods provided by JdbcTestUtils, not the ScriptUtils.executeSQLScript() that (along with ResourceDatabasePopulator) is suggested as its replacement. – Glen Mazza Jul 11 '18 at 04:51

score 26 · Answer 2 · edited May 13 '20 at 13:07

I've solved the issue this way:

public void createDefaultDB(DataSource dataSource) {
    Resource resource = new ClassPathResource("CreateDefaultDB.sql");
    ResourceDatabasePopulator databasePopulator = new ResourceDatabasePopulator(resource);
    databasePopulator.execute(dataSource);
}

You can inject DataSource as usual:

import javax.sql.DataSource;
//...
@Autowired
private DataSource dataSource;

score 10 · Answer 3 · edited Feb 15 '21 at 12:50

try it

public void executeSqlScript(Connection connection,StringBuffer sql)throws SQLException{
         try {
             connection.setAutoCommit(false);//disable auto commit
             ScriptUtils.executeSqlScript(connection, new ByteArrayResource(sql.toString().getBytes()));
             connection.commit();//commit manually 
        } catch (SQLException e) {
            connection.rollback();
        }finally{
            connection.close();
        }
     }

score 0 · Answer 4 · answered Apr 13 '16 at 10:55

We can also achive through SQLExec. Below code is working for me.

import java.io.File;

import org.apache.tools.ant.Project;
import org.apache.tools.ant.taskdefs.SQLExec;

public class Test {

    public static void main(String[] args) {
        Test t = new Test();
        t.executeSql("");
    }

    private void executeSql(String sqlFilePath) {
        final class SqlExecuter extends SQLExec {
            public SqlExecuter() {
                Project project = new Project();
                project.init();
                setProject(project);
                setTaskType("sql");
                setTaskName("sql");
            }
        }

        SqlExecuter executer = new SqlExecuter();
        executer.setSrc(new File("test1.sql"));
        executer.setDriver("org.postgresql.Driver");
        executer.setPassword("postgres");
        executer.setUserid("postgres");
        executer.setUrl("jdbc:postgresql://localhost/test");
        executer.execute();
    }
}

score 0 · Answer 5 · answered Feb 22 '21 at 12:14

I was looking for a similar option for my project's case then I did stumble upon the following https://docs.spring.io/spring-framework/docs/current/javadoc-api/org/springframework/jdbc/datasource/init/ResourceDatabasePopulator.html

The provided Stackoverflow example is really neat and simple, if you want the Spring to handle the boilerplate sql handling on your behalf https://stackoverflow.com/a/23036217/1958683

Execute SQL file from Spring JDBC Template

5 Answers5

Linked