Java连接Oracle Thin数据库Insert语句语法错误排查求助

你的代码核心问题是SQL字段列表中Gender和Address1之间缺少逗号，拼接后的SQL会把这两个字段连写成GenderAddress1，直接导致Oracle抛出无效语法异常。

错误的SQL拼接片段： #

+ "FirstName, LastName, Salutation, DateOfBirth, Gender"
+ "Address1, Address2, Address3, City, State, PostalCode)"

修正后的SQL语句： #

String sql = "INSERT INTO Person("
        + "FirstName, LastName, Salutation, DateOfBirth, Gender, "
        + "Address1, Address2, Address3, City, State, PostalCode)"
        + " VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

额外优化建议： #

• 使用try-with-resources自动关闭数据库资源，避免连接泄漏：

try (Connection conn = ConnectionFactory.getConnection();
     PreparedStatement stmt = conn.prepareStatement(sql)) {
    stmt.setString(1, person.getFirstName());
    stmt.setString(2, person.getLastName());
    stmt.setString(3, person.getSalutation());
    stmt.setDate(4, person.getDateOfBirth());
    stmt.setString(5, person.getGender());
    stmt.setString(6, person.getAddress1());
    stmt.setString(7, person.getAddress2());
    stmt.setString(8, person.getAddress3());
    stmt.setString(9, person.getCity());
    stmt.setString(10, person.getState());
    stmt.setString(11, person.getPostalCode());
    stmt.executeUpdate();
    return true;
} catch (SQLException e) {
    e.printStackTrace();
    return false;
}

• Java 13+可使用文本块编写SQL，可读性更强，减少拼接错误：

String sql = """
        INSERT INTO Person(
            FirstName, LastName, Salutation, DateOfBirth, Gender,
            Address1, Address2, Address3, City, State, PostalCode
        ) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        """;

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