MYSQL getting auto-incrementing primary keys [4 methods] _ MySQL

MYSQL obtains the auto-incrementing primary key. [four methods] bitsCN.com

MYSQL obtains the auto-incrementing primary key. [four methods]

After executing the insert operation on mysql in an application, we need to obtain the auto-incrementing primary key of the insert record. This article introduces four methods in the java environment to obtain the value of the record primary key auto_increment after insert:

Using the insertRow () method provided by JDBC2.0

Using the getGeneratedKeys () method provided by JDBC3.0

Use the SQL select LAST_INSERT_ID () function

Use the SQL @ IDENTITY variable

1. use the insertRow () method provided by JDBC2.0

Since jdbc2.0, it can be executed in the following way.

[Java]

Statement stmt = null;

ResultSet rs = null;

Try {

Stmt = conn. createStatement (java. SQL. ResultSet. TYPE_FORWARD_ONLY, // Create Statement

Java. SQL. ResultSet. CONCUR_UPDATABLE );

Stmt.exe cuteUpdate ("drop table if exists autoIncTutorial ");

Stmt.exe cuteUpdate (// create demo table

"Create table autoIncTutorial ("

+ "PriKey int not null AUTO_INCREMENT ,"

+ "DataField VARCHAR (64), primary key (priKey ))");

Rs = stmt.exe cuteQuery ("SELECT priKey, dataField" // Retrieve data

+ "FROM autoIncTutorial ");

Rs. moveToInsertRow (); // Move the cursor to the row to be inserted (uncreated pseudo record)

Rs. updateString ("dataField", "auto increment here? "); // Modify the content

Rs. insertRow (); // Insert record

Rs. last (); // Move the cursor to the last row

Int autoIncKeyFromRS = rs. getInt ("priKey"); // Obtain the primary key preKey of the newly inserted record

Rs. close ();

Rs = null;

System. out. println ("Key returned for inserted row :"

+ AutoIncKeyFromRS );

} Finally {

// Rs, stmt close () cleaning

}

Advantage: Early general practices

Disadvantage: the cursor of the ResultSet needs to be operated, and the code is lengthy.

2. use the getGeneratedKeys () method provided by JDBC3.0

[Java]

Statement stmt = null;

ResultSet rs = null;

Try {

Stmt = conn. createStatement (java. SQL. ResultSet. TYPE_FORWARD_ONLY,

Java. SQL. ResultSet. CONCUR_UPDATABLE );

//...

// Omit several rows (create demo table as shown in the preceding example)

//...

Stmt.exe cuteUpdate (

"Insert into autoIncTutorial (dataField )"

+ "Values ('Can I Get the Auto Increment Field? ')",

Statement. RETURN_GENERATED_KEYS); // specify to the driver that the generatedKeys must be automatically obtained!

Int autoIncKeyFromApi =-1;

Rs = stmt. getGeneratedKeys (); // get the auto-incrementing 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 method only requires two steps: 1. activate automatic key acquisition during executeUpdate; 2. call the getGeneratedKeys () interface of Statement.

Advantages:

1. easy to operate and concise code

2. jdbc3.0 standard

3. high efficiency, because there is no additional access to the database

Here,

A. Before jdbc3.0, each jdbc driver implementation has its own interface for obtaining auto-incrementing primary keys. In mysql jdbc2.0 driver org. gjt. mm. mysql, the getGeneratedKeys () function is implemented in org. gjt. mm. mysql. jdbc2.Staement. getGeneratedKeys. In this way, portability will be greatly affected. JDBC3.0 makes up for this through the standard getGeneratedKeys.

B. for details about getGeneratedKeys (), visit the official website: OracleJdbcGuide.

3. use the SQL select LAST_INSERT_ID () function

[Java]

Statement stmt = null;

ResultSet rs = null;

Try {

Stmt = conn. createStatement ();

//...

// Omit table creation

//...

Stmt.exe cuteUpdate (

"Insert into autoIncTutorial (dataField )"

+ "Values ('Can I Get the Auto Increment Field? ')");

Int autoIncKeyFromFunc =-1;

Rs = stmt.exe cuteQuery ("SELECT LAST_INSERT_ID ()"); // Obtain the generatedKey through an additional query

If (rs. next ()){

AutoIncKeyFromFunc = rs. getInt (1 );

} 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 about this method, that is, the extra query of the struct function LAST_INSERT_ID ().

Advantage: simple and convenient

Disadvantage: compared with getGeneratedKeys () of JDBC3.0, an additional database query is required.

Supplement:

1. this function is defined in the mysql5.5 manual as "returns a BIGINT (64-bit) value representing the first automatically generated value successfully inserted for an AUTO_INCREMENT column as a result of the most recently executed INSERT statement. ". Document click here

2. this function is "thread-safe" in the connection dimension ". That is to say, each mysql connection will have a result that independently saves LAST_INSERT_ID () and will only be updated by the last insert operation of the current connection. That is, when two connections execute the insert statement at the same time, the LAST_INSERT_ID () called separately will not overwrite each other. For example, LAST_INSERT_ID () returns 100 after table A is inserted. After Table B is inserted, LAST_INSERT_ID () returns 101, 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 execution results. The experiment process is not detailed here.

3. based on the above point, if insert is executed simultaneously under the same connection, the return values of the two operations may overwrite each other. Because LAST_INSERT_ID () is isolated at the connection level. In this case, getGeneratedKeys () can be better, because getGeneratedKeys () is at the statement level. For multiple statement of the same connection, getGeneratedKeys () will not be overwritten.

4. use the SELECT @ IDENTITY variable

This method has the same effect as LAST_INSERT_ID. The official documentation says: "This variable is a synonym for the last_insert_id variable. it exists for compatibility with other database systems. you can read its value with SELECT @ identity, and set it using SET identity. "Documentation click here

Important addition:

Whether it is SELECT LAST_INSERT_ID () or SELECT @ IDENTITY, if multiple records are inserted in an insert statement, only the generatedKey of the first inserted record is returned. for example:

[Java]

Insert into t VALUES

-> (NULL, 'Mary '), (NULL, 'Jane'), (NULL, 'Lisa ');

LAST_INSERT_ID (), @ IDENTITY only returns the generatedKey of the record where 'Mary 'is located.

BitsCN.com