[java/jdbc]插入数据时获取自增长主键的值

需求

数据库中的unum字段为自增长主键，使用sql语句插入时需要获取主键的值

insert into udata(upwd,utime,upath) values(?,?,?)

插入之后再查找显然效率太慢，其实jdbc提供了getGeneratedKeys()方法来获取主键

代码

PreparedStatement ps = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
ps.setObject(1,XXX);
.....
int rs=ps.executeUpdate();

ResultSet generatedKeys = ps.getGeneratedKeys();
if (generatedKeys.next()) {
 unum=generatedKeys.getInt(1); //得到主键
}

说明

如果报异常

Generated keys not requested. You need to specify Statement.RETURN_GENERATED_KEYS to Statement.execu

mysql-connector包在5.1.17之后需要在prepareStatement里加上额外参数(Statement.RETURN_GENERATED_KEYS或Statement.NO_GENERATED_KEYS)

ps = conn.prepareStatement(sql,PreparedStatement.RETURN_GENERATED_KEYS);