最近在写 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中的一个方法，能够自动处理数据库管理的自动提交，默认开启

为了提高效率，关闭了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

JDBC批量处理

setAutoCommit 优化批量处理

“上层建筑”——Mybits