使用JDBC获取插入记录的自增主键值

对于如下的表结构：

mysql> describe persons; +-------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(255) | NO | | NULL | | | age | int(11) | YES | | NULL | | | birth | date | YES | | NULL | | | email | varchar(255) | YES | | NULL | | +-------+--------------+------+-----+---------+----------------+ 5 rows in set (0.00 sec)

id是自增的，一般我们在做插入操作的时候不会这样写INSERT INTO persons(id, name, age, birth, email) VALUES(?, ?, ? ,? ,?) ，而是省略id参数的设置，使其由MySQL数据库自动生成。 那么我们如何获取MySQL为我们生成的主键呢？

我们可以使用

PreparedStatement prepareStatement(String sql, int autoGeneratedKeys) throws SQLException;

autoGeneratedKeys:a flag indicating whether auto-generated keys should be returned; one of Statement.RETURN_GENERATED_KEYS or Statement.NO_GENERATED_KEYS.

使用：

@Test public void test() { Connection connection = null; PreparedStatement statement = null; ResultSet rs = null; int id = 0; try { connection = JDBCTools.getConnection(); String sql = "INSERT INTO persons(name, age, birth, email) VALUES(?, ?, ?, ?)"; statement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS); statement.setString(1, "xiya"); statement.setInt(2, 25); statement.setDate(3, new Date(new java.util.Date().getTime())); statement.setString(4, "5342735@qq.com"); //int result = statement.executeUpdate("INSERT INTO persons(name, age) VALUES('n3verl4nd',25)"); //int result = statement.executeUpdate("DELETE FROM persons WHERE id = 6"); statement.executeUpdate(); rs = statement.getGeneratedKeys(); if (rs.next()) { id = rs.getInt(1); System.out.println("插入数据的主键为：" + id); } ResultSetMetaData metaData = rs.getMetaData(); for (int i = 0; i < metaData.getColumnCount(); i++) { System.out.println(metaData.getColumnName(i + 1)); } } catch (SQLException e) { e.printStackTrace(); } finally { if (rs != null) { try { rs.close(); System.out.println("rs closed"); } catch (SQLException e) { e.printStackTrace(); } } if (statement != null) { try { statement.close(); System.out.println("statement closed"); } catch (SQLException e) { e.printStackTrace(); } } if (connection != null) { try { connection.close(); System.out.println("connection closed"); } catch (SQLException e) { e.printStackTrace(); } } //删除插入数据 testDelete(id); } }

通过statement.getGeneratedKeys() 获得了新生成的主键的ResultSet对象。 通过

ResultSetMetaData metaData = rs.getMetaData(); for (int i = 0; i < metaData.getColumnCount(); i++) { System.out.println(metaData.getColumnName(i + 1)); }

的输出结果来看， 该ResultSet 仅包含一列，列名为GENERATED_KEY。

MySQL数据库中，在执行插入操作后通过SELECT LAST_INSERT_ID(); 也可以获得自增主键值。