对于如下的表结构:
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();
也可以获得自增主键值。