MySQL gets the self-increment primary key "4 ways"

• The InsertRow () method provided by JDBC2.0
• The Getgeneratedkeys () method provided by JDBC3.0
• Through the SQL Select LAST_INSERT_ID () function
• 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 print?

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. }

Statement stmt = null;                                ResultSet rs = null;try {stmt = Conn.createstatement (Java.sql.ResultSet.TYPE_FORWARD_ONLY,//Create statement    Java.sql.ResultSet.CONCUR_UPDATABLE);    Stmt.executeupdate ("DROP TABLE IF EXISTS autoinctutorial");            Stmt.executeupdate (//Creating Demo table "CREATE Table Autoinctutorial ("    + "Prikey INT not NULL auto_increment," + "DataField VARCHAR (+), PRIMARY KEY (Prikey))";    rs = Stmt.executequery ("Select Prikey, DataField"//Retrieve Data + "from Autoinctutorial");                                              Rs.movetoinsertrow ();              Move the cursor to the row to be inserted (pseudo record not created) rs.updatestring ("DataField", "AUTO INCREMENT here");                                                    Modify content Rs.insertrow ();                                                         Insert record rs.last ();        Move the cursor to the last line int autoinckeyfromrs = Rs.getint ("Prikey");                Gets the primary key of the newly inserted record Prekey rs.close ();    rs = null;  System.out.println ("Key returned for inserted row:" + Autoinckeyfromrs);} finally {//rs,stmt close () cleanup}

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 print?

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 {...}

statement stmt = null;                                ResultSet rs = null;try {stmt = Conn.createstatement (Java.sql.ResultSet.TYPE_FORWARD_ONLY,      Java.sql.ResultSet.CONCUR_UPDATABLE);            ...//omit several lines (Create demo table like the example above)//... stmt.executeupdate ("INSERT into Autoinctutorial (DataField)"                      + "VALUES (' Can I Get the Auto Increment Field? ')", Statement.return_generated_keys);    Indicates to the driver that an automatic acquisition of generatedkeys! is required    int autoinckeyfromapi =-1;                                  rs = Stmt.getgeneratedkeys ();    Get the self-increment primary key!    if (Rs.next ()) {Autoinckeyfromapi = Rs.getint (1);    } else {//throw an exception from here} rs.close ();    rs = null;  System.out.println ("Key returned from Getgeneratedkeys ():" + Autoinckeyfromapi);} 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 no additional access to the database is added here, A. Prior to jdbc3.0, each JDBC driver implementation has its own interface to obtain 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: Oraclejdbcguide3. Via SQL Select LAST_INSERT_ID () [Java]View Plaincopy print?

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 {...}

Statement stmt = null; ResultSet rs = null;try {    stmt = conn.createstatement ();    // ...    Omit build table    //...    Stmt.executeupdate (            "INSERT into Autoinctutorial (DataField)"            + "values (' Can I Get the Auto Increment Field? ')" );    int autoinckeyfromfunc =-1;    rs = Stmt.executequery ("Select last_insert_id ()");             Get Generatedkey    if (Rs.next ()) {        Autoinckeyfromfunc = rs.getint (1) with extra queries;    }  else {        //Throw an exception from here    }     rs.close ();    System.out.println ("Key returned from" +                       "' Select last_insert_id () ':" +                       Autoinckeyfromfunc);}  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 successfully in serted for an auto_increment column as a result of the most recently executed INSERT statement. ". Document Point this 2. 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 point, Getgeneratedkeys () is able to do better because getgeneratedkeys () is the statement level. The same connection multiple 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 print?

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

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

last_insert_id (), @ @IDENTITY will return only the generatedkey of the record where ' Mary ' is locatedSummary therefore, it is best to get the primary key of the insert record through the Getgeneratedkeys () function provided by JDBC3. Not only simple, but also high efficiency. In MyBatis, there are the relevant settings: [Java]View Plaincopy print?

1. <insert id="save" parametertype="Mappedobject" usegeneratedkeys="true" keyproperty="id" >
2. </insert>

(go) MySQL get self-increment primary key "4 ways"