有如下一个插入语句:
insert into article values(null, ?, ?, ?, ?, now(), 0);
用prepareStatement进行插入,如何获取该语句自动生成的键值呢?
以下为一段相关的代码,你可以找到答案哦!
表article对应的字段为(id, pid, rootid, title, content, date, isleaf)
Connection conn = DriverManager.getConnection(...);
//将自动提交设置为false
Boolean isAuto = conn.getAutoCommit();
conn.setAutoCommit(false);
String sql = "insert into article values(null, ?, ?, ?, ?, now(), 0)";
PreparedStatement pstmt = conn.prepareStatement(sql,
Statement.RETURN_GENERATED_KEYS);
pstmt.setInt(1, ...);
pstmt.setInt(2, 0); //rootid暂设为0
pstmt.setString(3, ...);
pstmt.setString(4, ...);
pstmt.execute();
//获得自动生成的键值
ResultSet rs = pstmt.getGeneratedKeys();
rs.next();
int id = rs.getInt(1);
//将刚才插入的记录中的rootid改为id的值
Statement stmt = conn.createStatement(conn);
sql = "update article set rootid = " + id + " where id = " + id;
stmt.executeUpdate(sql);
//将自动提交恢复现场
conn.commit();
conn.setAutoCommit(isAuto);
调试这段代码的时候遇到一个bug,说是有语法错误,最终确定是
sql = "update article set rootid = " + id + "where id = " + id;
的错误。找了半天,终于找到错误点,你能区分下面两条语句的不同吗?
(第二条为正确语句!万恶的空格呀。。。呀。。。呀。。)
sql = "update article set rootid = " + id + "where id = " + id;
sql = "update article set rootid = " + id + " where id = " + id;