Spring Boot+Java操作H2数据库建表触发42001-214错误求助

1. 修复CREATE TABLE语法错误 #

触发错误的核心原因是创建表的SQL语句末尾缺少右括号，导致语法不完整。同时Java实体类的salary为Long类型，H2的INTEGER类型无法匹配其存储范围，需改为BIGINT避免数据溢出。修正后的创建表语句如下：

PreparedStatement createTable = connection.prepareStatement("CREATE TABLE IF NOT EXISTS payrolls(" +
        "employee VARCHAR(20)," +
        "period VARCHAR(20)," +
        "salary BIGINT)");

2. 完善事务回滚逻辑 #

原代码捕获SQL异常后仅打印日志，未执行事务回滚，会导致部分插入的数据残留，破坏数据一致性。需在catch块中添加回滚操作：

catch (SQLException e) {
    System.out.println(e.getMessage());
    connection.rollback(); // 执行事务回滚
}

3. 修正响应返回逻辑 #

原代码无论数据库操作成功与否，都返回"Added successfully!"，会误导调用方。需添加状态标记，区分成功/失败场景：

boolean isSuccess = true;
try (Connection connection = DriverManager.getConnection(url)) {
    connection.setAutoCommit(false);
    try (PreparedStatement createTable = connection.prepareStatement("CREATE TABLE IF NOT EXISTS payrolls(" +
                    "employee VARCHAR(20)," +
                    "period VARCHAR(20)," +
                    "salary BIGINT)");
         PreparedStatement statement = connection.prepareStatement(
                    "INSERT INTO payrolls (employee, period, salary) VALUES (?, ?, ?) ")) {

        createTable.executeUpdate();
        for (UserPayroll payroll : payrolls) {
            statement.setString(1, payroll.getEmployee());
            statement.setString(2, payroll.getPeriod());
            statement.setLong(3, payroll.getSalary());
            statement.executeUpdate();
        }
        connection.commit();
    } catch (SQLException e) {
        System.out.println(e.getMessage());
        connection.rollback();
        isSuccess = false;
    }
} catch (SQLException e) {
    System.out.println("1 " + e.getMessage());
    isSuccess = false;
}

Map<String, String> response = new HashMap<>();
if (isSuccess) {
    response.put("status", "Added successfully!");
    return ResponseEntity.ok().body(response);
} else {
    response.put("status", "Failed to add payrolls!");
    return ResponseEntity.status(HttpStatus.INTERNAL_SERVER_ERROR).body(response);
}

4. 额外优化建议 #

• 替换原生JDBC为Spring Boot的JdbcTemplate，简化资源管理与代码编写。
• 将表创建逻辑移至应用启动初始化脚本（如src/main/resources/schema.sql），避免每次请求执行建表操作，提升性能。

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