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

MYSQL obtains the auto-incrementing primary key. [four methods] After we perform the insert operation on mysql in the 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 primary key auto_increment of the record after insert: using the insertRow () method provided by JDBC3.0 through getGeneratedKeys () provided by JDBC3.0 () use the SQL select LAST_INSERT_ID () function to run the SQL @ IDENTITY variable 1. since JDBC2.0 adopts the insertRow () method provided by 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 = stm T.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 rs content. insertRow (); // insert record rs. last (); // move the cursor to the last line int autoIncKeyFromRS = rs. getInt ("priKey"); // obtain the primary key preKey rs of the newly inserted record. close (); rs = null; System. out. println ("Key returned for inserted row:" + autoIncKeyFromRS);} finally {// rs, stmt close () Cleanup} advantages: more common early practices disadvantages: the code for operations on the ResultSet cursor is lengthy. 2. use the getGeneratedKeys () method [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); // specifies 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. advantages of calling the getGeneratedKeys () interface of Statement: 1. easy to operate and concise code 2. jdbc3.0 Standard 3. high efficiency, because no additional access to the database is provided here,. before jdbc3.0, each jdbc driver implementation had 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 more information 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 generatedKey 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 disadvantages: getGeneratedKey of JDBC3.0 S (), requires an additional database query. 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. ". 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. The effect of the SELECT @ IDENTITY variable is the same as that of 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. "This document points to this important addition: whether it is SELECT LAST_INSERT_ID () or SELECT @ IDENTITY, for an insert statement to insert multiple records, only the generatedKey of the first insert record will always be 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.