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

来源：互联网发布：青岛seo搜索排名优化编辑：程序博客网时间：2024/05/18 00:52

对于如下的表结构：

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

阅读全文

0 0