MySQL gets the primary key for the other table

Source: Internet
Author: User
Tags mysql in stmt

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
  1. Statement stmt = null;
  2. ResultSet rs = null;
  3. try {
  4. stmt = Conn.createstatement (Java.sql.ResultSet.TYPE_FORWARD_ONLY, //Create statement
  5. Java.sql.ResultSet.CONCUR_UPDATABLE);
  6. Stmt.executeupdate ("DROP TABLE IF EXISTS autoinctutorial");
  7. Stmt.executeupdate ( //Create demo table
  8. the CREATE TABLE autoinctutorial ("
  9. + "Prikey INT not NULL auto_increment,"
  10. + "DataField VARCHAR (+), PRIMARY KEY (Prikey))";
  11. rs = stmt.executequery ("Select Prikey, DataField" //Retrieving Data
  12. + "from autoinctutorial");
  13. Rs.movetoinsertrow (); //Move cursor to the row to be inserted (pseudo record not created)
  14. Rs.updatestring ("DataField", "AUTO INCREMENT here?"); //modify content
  15. Rs.insertrow (); //Insert record
  16. Rs.last (); //Move cursor to last row
  17. int autoinckeyfromrs = Rs.getint ("Prikey"); //Get the primary key for the record you just inserted prekey
  18. Rs.close ();
  19. rs = null;
  20. System.out.println ("Key returned for inserted row:"
  21. + Autoinckeyfromrs);
  22. } finally {
  23. //rs,stmt Close () cleanup
  24. }

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
  1. Statement stmt = null;
  2. ResultSet rs = null;
  3. try {
  4. stmt = Conn.createstatement (Java.sql.ResultSet.TYPE_FORWARD_ONLY,
  5. Java.sql.ResultSet.CONCUR_UPDATABLE);
  6. // ...  
  7. //Omit several lines (create demo table as above example)
  8. // ...  
  9. Stmt.executeupdate (
  10. "INSERT into Autoinctutorial (DataField)"
  11. + "VALUES (' Can I Get the Auto Increment Field? ')",
  12. Statement.return_generated_keys);  //To drive indicates the need to obtain generatedkeys! automatically
  13. int autoinckeyfromapi =-1;
  14. rs = Stmt.getgeneratedkeys ();  //Get self-increment primary key!
  15. if (Rs.next ()) {
  16. Autoinckeyfromapi = Rs.getint (1);
  17. } Else {
  18. //Throw an exception from here
  19. }
  20. Rs.close ();
  21. rs = null;
  22. System.out.println ("Key returned from Getgeneratedkeys ():"
  23. + Autoinckeyfromapi);
  24. } 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
  1. Statement stmt = null;
  2. ResultSet rs = null;
  3. try {
  4. stmt = Conn.createstatement ();
  5. // ...  
  6. //Omit Build table
  7. // ...  
  8. Stmt.executeupdate (
  9. "INSERT into Autoinctutorial (DataField)"
  10. + "VALUES (' Can I Get the Auto Increment Field? ')");
  11. int autoinckeyfromfunc =-1;
  12. rs = stmt.executequery ("Select last_insert_id ()"); //Get Generatedkey through additional queries
  13. if (Rs.next ()) {
  14. Autoinckeyfromfunc = Rs.getint (1);
  15. } Else {
  16. //Throw an exception from here
  17. }
  18. Rs.close ();
  19. System.out.println ("Key returned from" +
  20. "' Select last_insert_id () ':" +
  21. AUTOINCKEYFROMFUNC);
  22. } 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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.