如何在Java中用单条SQL语句向两个关联表插入数据？

错误原因 #

你用AND连接两个INSERT语句是语法错误，AND是SQL中的逻辑运算符，仅用于条件判断（比如WHERE子句），不能用来分隔多个独立的INSERT操作。

正确的单条SQL写法 #

不同数据库对多语句SQL的支持略有差异，以下是通用和数据库特定的实现方式：

1. 分号分隔多语句（适用于多数数据库，MySQL需额外配置） #

直接用分号分隔两个INSERT语句，形成单条SQL字符串：

INSERT INTO users (id, password, firstName, lastName, emailAddress, enrollDate, lastAccess, enabled, type) 
VALUES (100222222, 'password', 'Robert', 'McReady', 'bob.mcready@dcmail.ca', '2016-03-07', '2015-09-03', true, 's');
INSERT INTO students (id, programCode, programDescription, year) 
VALUES (100222222, 'a', 'b', 3);

2. 数据库特定的原子性插入（以PostgreSQL为例） #

如果需要保证两个插入操作的原子性（要么都成功，要么都失败），可以用WITH子句结合INSERT...RETURNING：

WITH inserted_user AS (
    INSERT INTO users (id, password, firstName, lastName, emailAddress, enrollDate, lastAccess, enabled, type) 
    VALUES (100222222, 'password', 'Robert', 'McReady', 'bob.mcready@dcmail.ca', '2016-03-07', '2015-09-03', true, 's')
    RETURNING id
)
INSERT INTO students (id, programCode, programDescription, year)
SELECT id, 'a', 'b', 3 FROM inserted_user;

这种写法能确保只有当users表插入成功后，才会向students表插入数据，避免数据不一致。

Java预编译语句实现 #

情况1：使用分号分隔的多语句 #

注意：MySQL需要在JDBC URL中添加allowMultiQueries=true参数，PostgreSQL默认支持。Java代码如下：

String sqlInsert = "INSERT INTO users (id, password, firstName, lastName, emailAddress, enrollDate, lastAccess, enabled, type) " +
                   "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?); " +
                   "INSERT INTO students (id, programCode, programDescription, year) " +
                   "VALUES (?, ?, ?, ?)";

try (Connection conn = DriverManager.getConnection(url, user, pass);
     PreparedStatement pstmt = conn.prepareStatement(sqlInsert)) {
    // 设置users表的参数
    pstmt.setInt(1, 100222222);
    pstmt.setString(2, "password");
    pstmt.setString(3, "Robert");
    pstmt.setString(4, "McReady");
    pstmt.setString(5, "bob.mcready@dcmail.ca");
    pstmt.setDate(6, Date.valueOf("2016-03-07"));
    pstmt.setDate(7, Date.valueOf("2015-09-03"));
    pstmt.setBoolean(8, true);
    pstmt.setString(9, "s");
    
    // 设置students表的参数
    pstmt.setInt(10, 100222222);
    pstmt.setString(11, "a");
    pstmt.setString(12, "b");
    pstmt.setInt(13, 3);
    
    // 执行多语句
    pstmt.executeUpdate();
} catch (SQLException e) {
    e.printStackTrace();
}

情况2：使用PostgreSQL的WITH原子性插入 #

如果是PostgreSQL数据库，用预编译语句实现原子插入：

String sqlInsert = "WITH inserted_user AS (" +
                   "INSERT INTO users (id, password, firstName, lastName, emailAddress, enrollDate, lastAccess, enabled, type) " +
                   "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?) RETURNING id" +
                   ") " +
                   "INSERT INTO students (id, programCode, programDescription, year) " +
                   "SELECT id, ?, ?, ? FROM inserted_user";

try (Connection conn = DriverManager.getConnection(url, user, pass);
     PreparedStatement pstmt = conn.prepareStatement(sqlInsert)) {
    // 设置users表参数
    pstmt.setInt(1, 100222222);
    pstmt.setString(2, "password");
    pstmt.setString(3, "Robert");
    pstmt.setString(4, "McReady");
    pstmt.setString(5, "bob.mcready@dcmail.ca");
    pstmt.setDate(6, Date.valueOf("2016-03-07"));
    pstmt.setDate(7, Date.valueOf("2015-09-03"));
    pstmt.setBoolean(8, true);
    pstmt.setString(9, "s");
    
    // 设置students表的其他参数
    pstmt.setString(10, "a");
    pstmt.setString(11, "b");
    pstmt.setInt(12, 3);
    
    pstmt.executeUpdate();
} catch (SQLException e) {
    e.printStackTrace();
}

注意事项 #

• 无论哪种方式，都建议开启事务（conn.setAutoCommit(false)），确保两个插入操作的原子性，避免出现一个表插入成功另一个失败的情况。
• 使用预编译语句时，尽量用占位符?代替硬编码值，防止SQL注入。

内容的提问来源于stack exchange，提问作者Deep