在Java中执行SQL脚本文件

最后修改:2024年6月4日

by baeldung

1. 概述

在这个教程中，我们将讨论如何从Java运行SQL脚本。我们将探讨两个库：MyBatis和Spring JDBC。MyBatis提供了ScriptRunner类，而Spring JDBC则提供了ScriptUtils来直接从磁盘读取SQL脚本文件并在目标数据库上执行它们。

我们还将实现一个自定义DB工具，从文件中读取SQL语句并分批执行它们。

为了简化并使代码快速上手，我们将使用广泛使用的内存中的H2嵌入式数据库进行测试。让我们看看它们是如何工作的。

2. 使用MyBatis ScriptRunner执行SQL脚本

首先，通过在pom.xml中添加以下内容来添加MyBatis的Maven依赖：

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

现在，让我们看看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));
    }
}

如代码所示，ScriptRunner提供了按行执行脚本以及一次性执行整个脚本的选项。

在执行SQL文件之前，让我们先看一下它：

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

我们可以看到，这个文件包含块注释、单行注释、空行、创建表语句和插入语句。这使我们能够测试本文中讨论的库的解析能力。

执行完整脚本文件的实现很简单。为此，我们将从磁盘读取整个文件，并将其作为字符串参数传递给java.sql.Statement.execute()方法。因此，我们更倾向于逐行执行：

@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);
    }
}

在上面的例子中，我们使用了一个创建employees表并插入20条记录的SQL文件。

对这个主题更感兴趣的读者也可以查看ScriptRunner的源代码。

3. 使用Spring JDBC ScriptUtils执行SQL脚本

接下来，是时候检查ScriptUtils类了。首先处理Maven依赖：

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

之后，让我们看看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
        );
    }
}

如上所述，ScriptUtils提供了许多选项来读取SQL文件，因此支持多种使用不同注释标识符（如除典型“--”，“/”和“/”之外的）的不同数据库引擎。此外，还有两个额外的参数continueOnError和ignoreFailedDrops，它们的作用显而易见。与MyBatis库不同，ScriptUtils并没有提供执行整个脚本的选项，而是更倾向于逐条执行SQL语句。这可以从其源代码中确认。

让我们看看执行过程：

@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);
    }
}

在上述方法中，我们简单地调用SpringScriptUtility.runScript()方法，传入路径和连接对象。

4. 使用JDBC分批执行SQL语句

到目前为止，我们已经看到这两个库都支持执行SQL文件。但它们都没有提供分批执行SQL语句的功能。对于执行大型SQL文件来说，这是一个重要特性。

因此，让我们自己实现一个SqlScriptBatchExecutor：

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

这个实现可以概括为两行：parseSQLScript()方法从文件中获取SQL语句，而executeSQLBatches()方法分批执行它们。

让我们看看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;
}

我们使用COMMENT_PATTERN = Pattern.compile("–.*|/\*(.|\[\r\n\])*?\*/")来识别注释和空行，然后从SQL文件中移除它们。与MyBatis一样，我们也只支持默认的注释分隔符。

现在，我们可以看看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();
    }
    connnection.commit();
}

这个方法接收SQL语句列表，遍历它们，并在达到参数batchSize的值时执行它们。

让我们看看自定义程序的运作：

@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);
    }
}

它将SQL语句分两批执行，每批10条语句。值得注意的是，这里批量大小是可配置的，可以根据文件中的SQL语句数量进行调整。

5. 总结

在这篇文章中，我们了解了MyBatis和Spring JDBC提供的数据库工具来执行SQL文件。我们发现Spring JDBC在解析SQL文件方面更为灵活。此外，我们还开发了一个自定义工具，支持SQL语句的批量执行。

如往常一样，本教程的代码可以在GitHub上找到。

« 上一篇： Java Weekly, 第504期
» 下一篇： 使用Java更新Amazon S3中的现有对象