MySQL gets the primary key for the other table

Typically, after inserting an insert operation on MySQL in the app, we need to get the self-increment primary key for the inserted record. This article describes the 4 ways in which the Java environment obtains the value of the record primary key auto_increment after insert:

1. The InsertRow () method provided by JDBC2.0
2. The Getgeneratedkeys () method provided by JDBC3.0
3. Through the SQL Select LAST_INSERT_ID () function
4. Through the SQL @ @IDENTITY variable

1. The InsertRow () method provided by JDBC2.0

Since jdbc2.0, this can be done in the following way.

[Java]View Plaincopy

2. Statement stmt = null;
4. ResultSet rs = null;
6. try {
8. stmt = Conn.createstatement (Java.sql.ResultSet.TYPE_FORWARD_ONLY, //Create statement
10. Java.sql.ResultSet.CONCUR_UPDATABLE);
12. Stmt.executeupdate ("DROP TABLE IF EXISTS autoinctutorial");
14. Stmt.executeupdate ( //Create demo table
16. the CREATE TABLE autoinctutorial ("
18. + "Prikey INT not NULL auto_increment,"
20. + "DataField VARCHAR (+), PRIMARY KEY (Prikey))";
22. rs = stmt.executequery ("Select Prikey, DataField" //Retrieving Data
24. + "from autoinctutorial");
26. Rs.movetoinsertrow (); //Move cursor to the row to be inserted (pseudo record not created)
28. Rs.updatestring ("DataField", "AUTO INCREMENT here?"); //modify content
30. Rs.insertrow (); //Insert record
32. Rs.last (); //Move cursor to last row
34. int autoinckeyfromrs = Rs.getint ("Prikey"); //Get the primary key for the record you just inserted prekey
36. Rs.close ();
38. rs = null;
40. System.out.println ("Key returned for inserted row:"
42. + Autoinckeyfromrs);
44. } finally {
46. //rs,stmt Close () cleanup
48. }

Advantages: Early and more general practice

Cons: You need to manipulate resultset cursors, the code is verbose.

2. The Getgeneratedkeys () method provided by JDBC3.0 [Java]View Plaincopy

2. Statement stmt = null;
4. ResultSet rs = null;
6. try {
8. stmt = Conn.createstatement (Java.sql.ResultSet.TYPE_FORWARD_ONLY,
10. Java.sql.ResultSet.CONCUR_UPDATABLE);
12. // ...  
14. //Omit several lines (create demo table as above example)
16. // ...  
18. Stmt.executeupdate (
20. "INSERT into Autoinctutorial (DataField)"
22. + "VALUES (' Can I Get the Auto Increment Field? ')",
24. Statement.return_generated_keys);  //To drive indicates the need to obtain generatedkeys! automatically
26. int autoinckeyfromapi =-1;
28. rs = Stmt.getgeneratedkeys ();  //Get self-increment primary key!
30. if (Rs.next ()) {
32. Autoinckeyfromapi = Rs.getint (1);
34. } Else {
36. //Throw an exception from here
38. }
40. Rs.close ();
42. rs = null;
44. System.out.println ("Key returned from Getgeneratedkeys ():"
46. + Autoinckeyfromapi);
48. } finally {...}

This approach requires only 2 steps: 1. Activate auto Get key when Executeupdate, 2. Call Statement's Getgeneratedkeys () interface
Advantages: 1. Easy to operate, code concise 2. jdbc3.0 Standard 3. High efficiency because there is no additional access to the database
In addition, a. Before jdbc3.0, each JDBC driver implementation has its own interface to get the self-increment primary key. In MySQL jdbc2.0 's driverIn Org.gjt.mm.mysql, the Getgeneratedkeys () function is implemented in Org.gjt.mm.mysql.jdbc2.Staement.getGeneratedKeys (). With this direct reference, portability can have a lot of impact. JDBC3.0 through the standard Getgeneratedkeys to make up for this very well. B. About Getgeneratedkeys (), the official website also has more detailed explanation: Oraclejdbcguide
3. Via SQL Select LAST_INSERT_ID () [Java]View Plaincopy

2. Statement stmt = null;
4. ResultSet rs = null;
6. try {
8. stmt = Conn.createstatement ();
10. // ...  
12. //Omit Build table
14. // ...  
16. Stmt.executeupdate (
18. "INSERT into Autoinctutorial (DataField)"
20. + "VALUES (' Can I Get the Auto Increment Field? ')");
22. int autoinckeyfromfunc =-1;
24. rs = stmt.executequery ("Select last_insert_id ()"); //Get Generatedkey through additional queries
26. if (Rs.next ()) {
28. Autoinckeyfromfunc = Rs.getint (1);
30. } Else {
32. //Throw an exception from here
34. }
36. Rs.close ();
38. System.out.println ("Key returned from" +
40. "' Select last_insert_id () ':" +
42. AUTOINCKEYFROMFUNC);
44. } finally {...}

There is nothing to say in this way, that is, an extra query function last_insert_id (). Advantages: Simple and convenient disadvantage: the relative JDBC3.0 of Getgeneratedkeys (), requires an additional database query.
Add:1. This function, defined in the mysql5.5 manual, is: "Returns a BIGINT (64-bit) value representing the first automatically generated value SUCCESSF Ully inserted for an auto_increment column as a result of the most recently executed INSERT statement. ". Document Point this2. This function is "thread safe" on the connection dimension. That is, each MySQL connection will have a result of saving last_insert_id () independently, and that is, when 2 connections execute the INSERT statement at the same time, the last_insert_id () that are called separately do not overwrite each other. For a chestnut: connect a after inserting a table last_insert_id () returns 100, last_insert_id () returns 101 after connection B is inserted into the table, but connection a repeats LAST_INSERT_ID (), always returns 100 instead of 101. This can be verified by monitoring the number of MySQL connections and the execution results, and this does not detail the experimental process. 3. Based on the above point, if you execute the insert simultaneously on the same connection, the return value of the 2 operations may overwrite each other. Because last_insert_id () is the connection level. This, Getgeneratedkeys () is able to do better because getgeneratedkeys () is the statement level. The same connection several times Statement,getgeneratedkeys () are not covered by each other.
4. Via SQL SELECT @ @IDENTITYThis is the same way as the last_insert_id () effect. The official website document says: "This variable was a synonym for the last_insert_id variable. It exists for compatibility and other database systems. You can read their value with SELECT @ @identity, and set it using set identity. "Document Point this

Important additions:Whether a select last_insert_id () or a SELECT @ @IDENTITY, inserting multiple records for an INSERT statement will always return only the generatedkey of the first inserted record. such as: [Java]View Plaincopy

1. INSERT into T VALUES
2. (NULL, ' Mary '), (null, ' Jane '), (null, ' Lisa ');

LAST_INSERT_ID (), @ @IDENTITY will return only the generatedkey of the record where ' Mary ' is located

MySQL gets the primary key for the other table