数据结构
网络
关系数据库管理系统(RDBMS)
操作系统
Java
MS Excel
iOS
HTML
CSS
Android
Python
C语言编程
C++
C#
MongoDB
MySQL
Javascript
PHP
物理学
化学
生物学
数学
英语
经济学
心理学
社会学
服装学
法学如何使用JDBC获取插入查询中主键值（自动生成键）？
如果您使用**Statement**或**PreparedStatement**对象将记录插入到包含自动递增列的表中。
您可以使用**getGeneratedKeys()**方法检索由该对象生成的该特定列的值。
示例
让我们在MySQL数据库中创建一个名为**sales**的表，其中一列为自动递增列，使用如下所示的CREATE语句：
CREATE TABLE Sales( ID INT PRIMARY KEY AUTO_INCREMENT, ProductName VARCHAR (20), CustomerName VARCHAR (20), DispatchDate date, DeliveryTime time, Price INT, Location VARCHAR(20) );
检索自动生成的值（PreparedStatement对象）
下面的JDBC程序使用PreparedStatement将3条记录插入到Sales表（上面创建的）中，检索并显示其生成的自动递增值。
示例
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Time;
public class RetrievingData_AutoIncrement_Pstmt {
public static void main(String args[]) throws SQLException {
//Registering the Driver
DriverManager.registerDriver(new com.mysql.jdbc.Driver());
//Getting the connection
String mysqlUrl = "jdbc:mysql://127.0.0.1/sample_database";
Connection con = DriverManager.getConnection(mysqlUrl, "root", "password");
System.out.println("Connection established......");
//Query to Insert values to the sales table
String insertQuery = "INSERT INTO Sales (ProductName, CustomerName, DispatchDate, DeliveryTime, Price, Location) VALUES (?, ?, ?, ?, ?, ?)";
//Creating a PreparedStatement object
PreparedStatement pstmt = con.prepareStatement(insertQuery,Statement.RETURN_GENERATED_KEYS);
pstmt.setString(1, "Key-Board");
pstmt.setString(2, "Raja");
pstmt.setDate(3, new Date(1567315800000L));
pstmt.setTime(4, new Time(1567315800000L));
pstmt.setInt(5, 7000);
pstmt.setString(6, "Hyderabad");
pstmt.addBatch();
pstmt.setString(1, "Earphones");
pstmt.setString(2, "Roja");
pstmt.setDate(3, new Date(1556688600000L));
pstmt.setTime(4, new Time(1556688600000L));
pstmt.setInt(5, 2000);
pstmt.setString(6, "Vishakhapatnam");
pstmt.addBatch();
pstmt.setString(1, "Mouse");
pstmt.setString(2, "Puja");
pstmt.setDate(3, new Date(1551418199000L));
pstmt.setTime(4, new Time(1551418199000L));
pstmt.setInt(5, 3000);
pstmt.setString(6, "Vijayawada");
pstmt.addBatch();
//Executing the batch
pstmt.executeBatch();
//Auto-incremented values generated by the current PreparedStatement object
ResultSet res = pstmt.getGeneratedKeys();
System.out.println("Auto-incremented values of the column ID generated by the current PreparedStatement object: ");
while (res.next()) {
System.out.println(res.getString(1));
}
}
}输出
Connection established...... Records inserted...... Auto-incremented values of the column ID generated by the current PreparedStatement object: 1 2 3
检索自动生成的值（Statement对象）
下面的JDBC程序使用**Statement**将3条记录插入到Sales表（上面创建的）中，检索并显示其生成的自动递增值。
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class RetrievingData_AutoIncrement {
public static void main(String args[]) throws SQLException {
//Registering the Driver
DriverManager.registerDriver(new com.mysql.jdbc.Driver());
//Getting the connection
String mysqlUrl = "jdbc:mysql://127.0.0.1/sample_database";
Connection con = DriverManager.getConnection(mysqlUrl, "root", "password");
System.out.println("Connection established......");
//Creating the Statement object
Statement stmt = con.createStatement();
//Query to insert multiple rows
String insertQuery = "insert into sales (ProductName, CustomerName, DispatchDate, DeliveryTime, Price, Location) values"
+ "('Key-Board', 'Raja', DATE('2019-09-01'), TIME('11:00:00'), 7000, 'India'), "
+ "('Earphones', 'Roja', DATE('2019-05-01'), TIME('11:00:00'), 2000, 'Vishakhapatnam'), "
+ "('Mouse', 'Puja', DATE('2019-03-01'), TIME('10:59:59'), 3000, 'Vijayawada')";
//Executing the INSERT statement
stmt.executeUpdate(insertQuery, Statement.RETURN_GENERATED_KEYS);
System.out.println("Records inserted......");
//Retrieving the auto-generated (auto-incremented) keys
ResultSet rs = stmt.getGeneratedKeys();
System.out.println("Values of auto-generated keys: ");
while(rs.next()) {
System.out.println(rs.getInt(1));
}
}
}输出
Connection established...... Records inserted...... Values of generated keys: 1 2 3
更新于：2019年7月30日
4K+ 次浏览
广告