如何使用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:///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:///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+ 次瀏覽

開啟你的職業生涯

完成課程獲得認證

開始學習
廣告