Executing SQL Script File in Java

1. Overview

In this tutorial, we’ll discuss how to run a SQL script from Java. As part of this, we’ll explore two libraries, MyBatis and Spring JDBC. MyBatis provides the ScriptRunner class, and Spring JDBC provides ScriptUtils to read SQL script files directly from disks and run them on target databases.

We’ll also implement a custom DB utility to read the SQL statements from a file and then execute them in batches.

To keep things simple and make the code up and running in no time, let’s use the widely used in-memory H2 embedded database for testing purposes. Let’s see them all in action.

2. Execute SQL Script Using MyBatis ScriptRunner

First, let’s add the Maven dependency for mybatis by including the following in the pom.xml:

<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <version>3.5.7</version>
</dependency>

Now, let’s take a look at the class MyBatisScriptUtility:

public class MyBatisScriptUtility {
    public static void runScript(
      String path,
      Connection connection
    ) throws Exception {
      ScriptRunner scriptRunner = new ScriptRunner(connection);
      scriptRunner.setSendFullScript(false);
      scriptRunner.setStopOnError(true);
      scriptRunner.runScript(new java.io.FileReader(path));
    }
}

As evident in the above code, ScriptRunner gives the option to execute the script line by line as well as the full script in one go.

Before executing the SQL file, let’s take a look at it:

-- Create the employees table if it doesn't exist
CREATE TABLE  employees (
    id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);

-- Insert employee records
INSERT INTO employees (id, first_name, last_name, department, salary)
VALUES (1, 'John', 'Doe', 'HR', 50000.00);

INSERT INTO employees (id, first_name, last_name, department, salary)
VALUES (2, 'Jane', 'Smith', 'IT', 60000.00);
--More SQL statements ....

As we can see, the above file consists of a mix of block comments, single-line comments, blank lines, create table statements and insert statements. This enables us to test the parsing capabilities of the libraries discussed in this article.

The implementation for executing a full script file is straightforward. For this, the entire file is read from the disk and passed on as a string argument to the method java.sql.Statement.execute(). Hence, we’d prefer to run it line by line:

@Test
public void givenConnectionObject_whenSQLFile_thenExecute() throws Exception {

    String path = new File(ClassLoader.getSystemClassLoader().getResource("employee.sql").getFile()).toPath().toString();
    MyBatisScriptUtility.runScript(path, connection);

    Statement statement = connection.createStatement();
    ResultSet resultSet = statement.executeQuery("SELECT COUNT(1) FROM employees");
    if (resultSet.next()) {
        int count = resultSet.getInt(1);
        Assert.assertEquals("Incorrect number of records inserted", 20, count);
    }
}

In the above example, we’ve used an SQL file that creates an employees table and then inserts 20 records into it.

3. Execute SQL Script Using Spring JDBC ScriptUtils

Moving on, it’s time to examine the ScriptUtils class. Let’s first take care of the Maven dependency:

<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-jdbc</artifactId>
    <version>6.1.13</version>
</dependency>

After this, let’s take a look at the class SpringScriptUtility:

public class SpringScriptUtility {
    public static void runScript(String path, Connection connection) {
        boolean continueOrError = false;
        boolean ignoreFailedDrops = false;
        String commentPrefix = "--";
        String separator = ";";
        String blockCommentStartDelimiter = "/*";
        String blockCommentEndDelimiter = "*/";

        ScriptUtils.executeSqlScript(
          connection,
          new EncodedResource(new PathResource(path)),
          continueOrError,
          ignoreFailedDrops,
          commentPrefix,
          separator,
          blockCommentStartDelimiter,
          blockCommentEndDelimiter
        );
    }
}

As we see above, ScriptUtils provides many options to read the SQL file. Hence it supports multiple database engines that employ different delimiters for comment identification beyond the typical “–,” “/*” and “*/”. Furthermore, there are two more arguments continueOnError, and ignoreFailedDrops which serve self-evident purposes.
Unlike MyBatis library, ScriptUtils doesn’t give the option to run the full script but rather prefers to run the SQL statements one by one. This can be confirmed by looking at its source code.

Let’s take a look at the execution:

@Test
public void givenConnectionObject_whenSQLFile_thenExecute() throws Exception {
    String path = new File(ClassLoader.getSystemClassLoader()
      .getResource("employee.sql").getFile()).toPath().toString();
    SpringScriptUtility.runScript(path, connection);

    Statement statement = connection.createStatement();
    ResultSet resultSet = statement.executeQuery("SELECT COUNT(1) FROM employees");
    if (resultSet.next()) {
        int count = resultSet.getInt(1);
        Assert.assertEquals("Incorrect number of records inserted", 20, count);
    }
}

In the above method, we are simply invoking SpringScriptUtility.runScript() with the path and connection object.

4. Executing SQL Statements in Batches Using JDBC

So far, we’ve seen that both libraries pretty much support executing SQL files. But neither of them gives the option to run the SQL statements in batches. This is an important feature for executing large SQL files.

Hence, let’s come up with our own SqlScriptBatchExecutor:

static void executeBatchedSQL(String scriptFilePath, Connection connection, int batchSize) throws Exception {
    List<String> sqlStatements = parseSQLScript(scriptFilePath);
    executeSQLBatches(connection, sqlStatements, batchSize);
}

The above implementation can be summed up in two lines: the method parseSQLScript() fetches the SQL statements from the file, and executeSQLBatches() executes them in batches.

Let’s take a look at the method parseSQLScript():

static List<String> parseSQLScript(String scriptFilePath) throws IOException {
    List<String> sqlStatements = new ArrayList<>();

    try (BufferedReader reader = new BufferedReader(new FileReader(scriptFilePath))) {
        StringBuilder currentStatement = new StringBuilder();
        String line;
        while ((line = reader.readLine()) != null) {
            Matcher commentMatcher = COMMENT_PATTERN.matcher(line);
            line = commentMatcher.replaceAll("");

            line = line.trim();

            if (line.isEmpty()) {
                continue;
            }

            currentStatement.append(line).append(" ");

            if (line.endsWith(";")) {
                sqlStatements.add(currentStatement.toString());
                logger.info(currentStatement.toString());
                currentStatement.setLength(0);
            }
        }
    } catch (IOException e) {
       throw e;
    }
    return sqlStatements;
}

We use COMMENT_PATTERN = Pattern.compile(“–.*|/\\*(.|[\\r\\n])*?\\*/”) to identify the comments and the blank lines, and then remove them from the SQL file. Like MyBatis, we also support only the default comment delimiters.

Now, we can take a look at the method executeSQLBatches():

static void executeSQLBatches(Connection connection, List<String> sqlStatements, int batchSize) 
        throws SQLException {
    int count = 0;
    Statement statement = connection.createStatement();

    for (String sql : sqlStatements) {
        statement.addBatch(sql);
        count++;

        if (count % batchSize == 0) {
            logger.info("Executing batch");
            statement.executeBatch();
            statement.clearBatch();
        }
    }
    if (count % batchSize != 0) {
        statement.executeBatch();
    }
    connection.commit();
}

The above method takes the list of SQL statements, iterates through it, and then executes it when the batch size grows to the value of the argument batchSize.

Let’s see the custom program in action:

@Test
public void givenConnectionObject_whenSQLFile_thenExecute() throws Exception {
    String path = new File(
      ClassLoader.getSystemClassLoader().getResource("employee.sql").getFile()).toPath().toString();
    SqlScriptBatchExecutor.executeBatchedSQL(path, connection, 10);
    Statement statement = connection.createStatement();
    ResultSet resultSet = statement.executeQuery("SELECT COUNT(1) FROM employees");

    if (resultSet.next()) {
        int count = resultSet.getInt(1);
        Assert.assertEquals("Incorrect number of records inserted", 20, count);
    }
}

It executes the SQL statements in two batches, 10 statements in each. Notably, the batch size here is parameterized so that it can be adjusted according to the number of SQL statements in the file.

5. Conclusion

In this article, we learned about the database utilities provided by MyBatis and Spring JDBC to execute SQL files. We found that Spring JDBC is more flexible in parsing the SQL files. Additionally, we developed a custom utility that supports batch executions of the SQL statements.