从 Java 运行 SQL 脚本，有两个库：MyBatis 和 Spring JDBC。MyBatis 提供了ScriptRunner类，Spring JDBC 提供了ScriptUtils来直接从磁盘读取 SQL 脚本文件并在目标数据库上运行。
使用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 文件来创建一个员工表，然后向其中插入 20 条记录。
使用Spring JDBC ScriptUtils执行SQL脚本
让我们首先处理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 () 。
使用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("-.*|/\\*(.|[\\\\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 语句的数量进行调整。
总结
比较了 MyBatis 和 Spring JDBC，我们发现 Spring JDBC 在解析 SQL 文件方面更加灵活。此外，我们还开发了一个支持批量执行 SQL 语句的自定义实用程序。