在Java中執行SQL腳本文件

1. 概述

在本教程中，我們將討論如何從 Java 運行 SQL 腳本。作為本文的一部分，我們將探索兩個庫：MyBatis 和 Spring JDBC。 MyBatis 提供了[ScriptRunner](https://mybatis.org/mybatis-3/apidocs/org/apache/ibatis/jdbc/ScriptRunner.html)類，Spring JDBC 提供了[ScriptUtils](https://docs.spring.io/spring-framework/docs/current/javadoc-api/org/springframework/jdbc/datasource/init/ScriptUtils.html)來直接從磁盤讀取 SQL 腳本文件並在目標數據庫上運行。

我們還將實現一個自定義數據庫實用程序來從文件中讀取 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);
 }
 }

在上面的示例中，我們使用了一個 SQL 文件來創建一個employees表，然後向其中插入 20 條記錄。

更好奇的讀者還可以去翻閱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);
 }
 }

在上面的方法中，我們只是使用path和connection對象調用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 上找到。