最近在写 YummyTranslator 的时候，需要将一个 .txt 格式的 wordlist 导入数据库中，做下拉建议匹配。由于框架限制，是叫GPT用Rust写的处理逻辑，没什么大毛病，但是RUST复杂的语言规则看的我 “晕头转向”🤯，但是也激起了我对编程语言操纵数据库的兴趣，故研究了一下老朋友JAVA在数据库相关的操作，写下这篇BLOG，记录我的整个探索过程。

基础结构——JDBC

Java对于数据库的操作中，依赖着 java.sql 这个包，提供了处理数据库操作的规范 JDBC。

package java.sql
Provides the API for accessing and processing data stored in a data source (usually a relational database) using the Java programming language. This API includes a framework whereby different drivers can be installed dynamically to access different data sources. Although the JDBC API is mainly geared to passing SQL statements to a database, it provides for reading and writing data from any data source with a tabular format. The reader/writer facility, available through the javax.sql.RowSet group of interfaces, can be customized to use and update data from a spread sheet, flat file, or any other tabular data source.

JDBC处理数据库的逻辑可以简单概括为 1.创建数据库连接 .getConnection -> 2.执行SQL .execute -> 3.关闭连接

graph TD
    A[导入JDBC] --> B{加载JDBC数据库驱动};
    B --> C[DriverManager获取Connection];
    C --> D{创建Statement或PreparedStatement};
    D --> E[执行SQL语句];
    E --> F{处理ResultSet};
    F --> G[关闭ResultSet/Statement/Connection];
    G --> J[结束];

    subgraph "1. 准备驱动"
        A
        B
        C
    end

    subgraph "2. 执行SQL"
        D
        E
    end
    
    subgraph "3. 处理与关闭"
        F
        J
    end

    C -- 失败 --> K((SQLException));
    E -- 失败 --> K;
    F -- 失败 --> K;

JDBC批量处理

对于JDBC的批量插入处理，我写了一个这样的逻辑：

public void main() {
    // 连接数据库
    Connection conn = null;
    try {
        conn = DriverManager.getConnection("jdbc:sqlite:wordList.db");
        System.out.println("Connected to database");
    } catch (SQLException e) {
        throw new RuntimeException(e);
    }

    //读取文件
    File wordList = new File("wordList.txt");
    String word = null;
    if (!wordList.exists()) {
        System.out.println("File not found");
        return;
    } else {
        try {
            byte[] data = Files.readAllBytes(wordList.toPath());
            word = new String(data, StandardCharsets.UTF_8);
            System.out.println("读取到数据: \n" + word);
            System.out.println("--------------------\n");
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }

    // 执行SQL语句
    Statement stmt = null;
    try {
        stmt = conn.createStatement();
        stmt.execute("CREATE TABLE IF NOT EXISTS words (word TEXT)"); // 创建表
    } catch (SQLException e) {
        throw new RuntimeException(e);
    }
    
    PreparedStatement pstmt = null;
    long start = System.currentTimeMillis();    //记录效率
    try {
        pstmt = conn.prepareStatement("INSERT INTO words(word) VALUES (?)"); // 插入数据
        System.out.println("正在将单词写入数据库...");
        for (String line : word.split("\\R")) {
            String v = line.trim();
            if (v.isEmpty()) continue;
            pstmt.setString(1, v);
            pstmt.addBatch();
        }
        pstmt.executeBatch();
    } catch (SQLException e) {
        throw new RuntimeException(e);
    }
    long end = System.currentTimeMillis();
    System.out.println("写入完成，耗时 " + (end - start) + " ms");
}

SQLite成功插入

插是插完了，但是作为一个 Ender 看着5分钟的时长很不爽，产生了优化的想法

setAutoCommit 优化批量处理

setAutoCommit是定义在 java.sql中的一个方法，能够自动处理数据库管理的自动提交，默认开启

void setAutoCommit(boolean autoCommit)
throws SQLException
Sets this connection's auto-commit mode to the given state. If a connection is in auto-commit ?>mode, then all its SQL statements will be executed and committed as individual transactions. >Otherwise, its SQL statements are grouped into transactions that are terminated by a call to either >the method commit or the method rollback. By default, new connections are in auto-commit >mode.
The commit occurs when the statement completes. The time when the statement completes >depends on the type of SQL Statement:

For DML statements, such as Insert, Update or Delete, and DDL statements, the statement is >complete as soon as it has finished executing.

For Select statements, the statement is complete when the associated result set is closed.

For CallableStatement objects or for statements that return multiple results, the statement is >complete when all of the associated result sets have been closed, and all update counts and >output parameters have been retrieved.

NOTE: If this method is called during a transaction and the auto-commit mode is changed, the >transaction is committed. If setAutoCommit is called and the auto-commit mode is not changed, >the call is a no-op.

Parameters:
autoCommit - true to enable auto-commit mode; false to disable it
Throws:
SQLException - if a database access error occurs, setAutoCommit(true) is called while >participating in a distributed transaction, or this method is called on a closed connection
See Also:
getAutoCommit()

为了提高效率，关闭了setAutoCommit 的自动commit，改成手动模式：

YuanS@Yuan MINGW64 ~/Documents/25FALL/SQLite/java_JDBC/SQLiteDemo (main)
$ git log
commit bafef19fa885b223c58b65a435f0dee2fb81837b (HEAD -> main)
Author: yuan-125h <yuan.sn@outlook.com>
Date:   Sat Sep 27 18:27:29 2025 +0800

    setAutoCommit(false)

commit f62872a14a2f65c9d4e5c9c4fbe8bbbb53a23eb3
Author: yuan-125h <yuan.sn@outlook.com>
Date:   Sat Sep 27 18:24:45 2025 +0800

    setAutoConmit

YuanS@Yuan MINGW64 ~/Documents/25FALL/SQLite/java_JDBC/SQLiteDemo (main)
$ git diff f62872a14a2f65c9d4e5c9c4fbe8bbbb53a23eb3 bafef19fa885b223c58b65a435f0dee2fb81837b
diff --git a/.idea/vcs.xml b/.idea/vcs.xml
new file mode 100644
index 0000000..94a25f7
--- /dev/null
+++ b/.idea/vcs.xml
@@ -0,0 +1,6 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<project version="4">
+  <component name="VcsDirectoryMappings">
+    <mapping directory="$PROJECT_DIR$" vcs="Git" />
+  </component>
+</project>
\ No newline at end of file
diff --git a/src/main/java/site/geekie/web/Main.java b/src/main/java/site/geekie/web/Main.java
index c6a8e76..1757eaa 100644
--- a/src/main/java/site/geekie/web/Main.java
+++ b/src/main/java/site/geekie/web/Main.java
@@ -10,6 +10,8 @@ public void main() {
     try {
         conn = DriverManager.getConnection("jdbc:sqlite:wordList.db");
         System.out.println("Connected to database");
+        // 手动事务：关闭自动提交
+        conn.setAutoCommit(false);
     } catch (SQLException e) {
         throw new RuntimeException(e);
     }
@@ -19,6 +21,8 @@ public void main() {
     String word = null;
     if (!wordList.exists()) {
         System.out.println("File not found");
+        // 返回前关闭连接
+        try { if (conn != null) conn.close(); } catch (SQLException ignore) {}
         return;
     } else {
         try {
@@ -39,7 +43,7 @@ public void main() {
     } catch (SQLException e) {
         throw new RuntimeException(e);
     }
-
+
     PreparedStatement pstmt = null;
     long start = System.currentTimeMillis();    //记录效率
     try {
@@ -52,9 +56,19 @@ public void main() {
             pstmt.addBatch();
         }
         pstmt.executeBatch();
+        // 批量成功，提交事务
+        conn.commit();
     } catch (SQLException e) {
+        // 异常回滚
+        try { if (conn != null) conn.rollback(); } catch (SQLException ignore) {}
         throw new RuntimeException(e);
     }
     long end = System.currentTimeMillis();
     System.out.println("写入完成，耗时 " + (end - start) + " ms");
+
+    // 清理资源与恢复自动提交
+    try { if (pstmt != null) pstmt.close(); } catch (SQLException ignore) {}
+    try { if (stmt != null) stmt.close(); } catch (SQLException ignore) {}
+    try { if (conn != null) conn.setAutoCommit(true); } catch (SQLException ignore) {}
+    try { if (conn != null) conn.close(); } catch (SQLException ignore) {}
 }
(

嗯嗯，48ms 快了很多非常nice

“上层建筑”——Mybits

困了等心情好了再写🤪....

Yuan's Blog

JAVA中的数据库处理

基础结构——JDBC

package java.sql

JDBC批量处理

setAutoCommit 优化批量处理

“上层建筑”——Mybits