使用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(); 也可以获得自增主键值。