Problems encountered when using select last_insert_id () in Spring

I do not know how to deal with MySQL today, but I do not know how to obtain the inserted id after inserting the record. After reading the document, I found the select last_insert_id () and executed this query after inserting it, you can get the auto-increment id.
After being available in your own program, you don't get the desired results, so you suspect that there is no problem in Spring, because there is no problem through the Basic JDBC test, so you should track Spring JDBC, after reading the source code, you can see that the database is obtained in Spring.ConnectionOf:Connection Con=DataSourceUtils. getConnection (getDataSource ();, ah, you can only blame yourself for the lack of Spring skills, so you cannot execute after executing insertSelect last_insert_id (), because select last_insert_id () is true to the currentFor Connection insertion and update operations, there is no way to get the key value of the new record in Spring.

We found the following article, from IT168, titled "Generation Policy Check of Spring Application Data primary keys", which is excerpted as follows:


   Generally, after adding a domain object, you must obtain the corresponding primary key value. Using the application layer to maintain primary keys is conducive to program performance optimization and application portability improvement to a certain extent. In the database auto-incrementing primary key scheme, if the JDBC driver cannot bind the primary key corresponding to the new record, you need to manually execute the query statement to obtain the corresponding primary key value. For highly concurrent systems, this can easily return an incorrect primary key. Use DataFieldMaxValueIncreme with CacheNter can obtain the primary key values in batches at a time for multiple domain objects to be inserted. Its execution performance is very high.

Use the auto-incrementing primary key of the database
   We often use the auto-increment field of data as the table's primary key, that is, the primary key value is not generated at the application layer, but is generated by the database when a new record is added. In this way, the application layer does not know the primary key value of the object before saving the object, but must save the data before returning the primary key value from the database. In many cases, we need to obtain the primary key value of the new object after persistence. In the orm framework such as Hibernate, after the new object persists, Hibernate will automatically bind the primary key value to the object, which brings a lot of convenience to program development.

   In the JDBC 3.0 specification, when a new record is added, the primary key value automatically generated by the database can be bound to the Statement or PreparedStatement. When using Statement, you can bind the primary key value using the following methods:
Int executeUpdate (String SQL, int autoGeneratedKeys)
You can also use Connection to create a value-added PreparedStatement:
PreparedStatement prepareStatement (String SQL, int autoGeneratedKeys)

   When the autoGeneratedKeys parameter is set to Statement. RETURN_GENERATED_KEYS, the primary key value generated by the database can be bound. If it is set to Statement. NO_GENERATED_KEYS, the primary key value is not bound. The following code demonstrates the process of binding Statement and obtaining the primary key value generated by the database:
Statement stmt = Conn. createStatement (); String SQL = "Insert into t_topic (topic_title, user_id) VALUES ('test topic ', '123 ') "; Stmt.exe cuteUpdate (SQL, Statement. RETURN_GENERATED_KEYS); ① specify the auto-incrementing primary key value of the bound table. = Stmt. getGeneratedKeys (); If (Rs. next ()){ Int Key = Rs. getInt (); ② get the auto-increment primary key value of the corresponding table}

Using this technology, Spring provides a way to return the primary key value of the newly added record:
Int update (PreparedStatementCreatorPsc, KeyHolder generatedKeyHolder)
Org. springframework. jdbc. support. KeyHolder is a callback interface. Spring uses it to save the primary key corresponding to the new record. The interface method of this interface is described as follows:
 Number getKey () throws InvalidDataAccessApiUsagEException
When only one row of data is inserted and the primary key is not a composite key and a number type, the new primary key value can be directly returned through this method. If it is a composite primary key or multiple primary keys are returned, this method throws InvalidDataAccessApiUsagEException. This method is the most commonly used method, because we insert only one data entry at a time and the primary key field type is Numeric;

 Map getKeys () throws InvalidDataAccessApiUsagEException
If it is a composite primary key, the column name and column value constitute an Entry in the Map. If multiple primary keys are returned, this method throws InvalidDataAccessApiUsagEException exception;

 List getKeyList ():
If multiple primary keys are returned, that is, multiple records are added to the PreparedStatement, each primary key corresponds to a Map, and multiple maps constitute a List.

   Spring uses the KeyHolder interface to refer to a general implementation class GeneratedKeyHolder. This class returns the self-increasing primary key value when a new record is added. If we want to add a forum Forum object and load the primary key value to the object, we can adjust it according to the following code:

Public Void AddForum (final Forum forum) {final String SQL = " Insert into t_forum (forum_name, forum_desc) VALUES (?,?) " ; KeyHolder keyHolder = New GeneratedKeyHolder (); ① create a primary key owner getJdbcTemplate (). update ( New PreparedStatementCreator() {Public PreparedStatement createPreparedStatement (Connection conn) throws SQLException {PreparedStatement ps = Conn. prepareStatement (SQL); ps. setString ( 1 , Forum. getForumName (); ps. setString ( 2 , Forum. getForumDesc ()); Return Ps ;}}, keyHolder); forum. setForumId (keyHolder. getKey (). intValue (); ② obtain the primary key from the primary key owner}

    In this way, after addForum (final Forum forum) is called to add a forum domain object, forum will have the corresponding primary key value for future use.
In versions earlier than JDBC 3.0, PreparedStatement cannot bind a primary key. If the table auto-increment key is used (such as MySql auto increment or SqlServer identity) it will challenge obtaining the correct primary key value-because you must immediately execute another query statement to obtain the new primary key after inserting data. Table 1 provides query statements for different databases to obtain the latest auto-incrementing primary key values:
      Table 1 obtain new primary key values for different databases

 

Database	Obtain the query statement of the new primary key
DB2	IDENTITY_VAL_LOCAL ()
Informix	SELECT dbinfo ('sqlca. sqlerrd1 ') FROM <TABLE>
Sybase	SELECT @ IDENTITY
SqlServer	SELECT SCOPE_IDENTITY () or SELECT @ IDENTITY
MySql	SELECT LAST_INSERT_ID ()
HsqlDB	Call identity ()
Cloudscape	IDENTITY_VAL_LOCAL ()
Derby	IDENTITY_VAL_LOCAL ()
PostgreSQL	SELECT nextval ('<TABLE> _ SEQ ')

   
   If the database concurrency rate is high, for example, before the primary key is queried after the record is inserted, the database executes Several SQL statements for inserting records, the primary key value returned through table 1 is the primary key value of the last insert statement, instead of the expected primary key value. Therefore, it is insecure to use a query statement to obtain the table's auto-incrementing key value. This is why some databases (such as Oracle and Firebird) intentionally do not provide auto-incrementing keys, but only provide sequences, sequence requires you to obtain the primary key value before adding a record. Oracle uses SELECT <SEQUENCE_NAME>. nextval from dual to obtain the next value of the sequence, while FireBird uses SELECT GEN_ID (<SEQUENCE_NAME> 1) from rdb $ DATABASE to obtain the next value of the sequence. In section 10.4.1, we will also explain the knowledge about auto-increment keys at the application layer.


Primary Key generated at the application layer
      Spring JDBC supports auto-incrementing keys and row sets. The auto-incrementing key object allows us to provide primary key values for new records at the application layer without relying on the auto-incrementing keys of the database. RowSet is introduced in JDK 1.4, which allows data operations when the connection is disconnected. In this section, we will introduce how to use RowSet in Spring JDBC.

Auto-increment Key Usage
      Generally, databases provide auto-incrementing keys, such as the auto_increment of MySql and the identity field of SqlServerr. Spring allows you to generate primary key values at the application layer. Therefore, org. springframework. jdbc. support. incrementer. datafieldmaxvaluvalucreme is defined. Nter interface provides two primary key generation solutions: first, generate a primary key through sequence; second, generate a primary key through a table. Based on the primary key generation method and the database, Spring provides several implementation classes, as shown in 1:

          Figure 1 DateFieldValueIncremente R inheritance class diagram
      Based on different primary key generation methods, you may need to configure information such as the table name, primary key field name, or sequence name. The following uses Oracle and MySql as examples to explain how to generate primary key values using sequence and table fields.

DataFieldMaxValueIncreme The nter interface defines three methods to obtain the next primary key value:
Optional int nextIntValue (): gets the next primary key value. The primary key data type is int;
 Long nextLongValue (): gets the next primary key value. The primary key data type is long;
Optional String nextStringValue (): obtains the value of the next primary key. The data type of the primary key is String;
      AbstractDataFieldMaxValu in its abstract implementation class EIncrementer provides several important attributes. incrementerName defines the name of the sequence or primary key table. If the returned primary key is of the String type, the paddingLength attribute may be used, it allows you to specify the length of the primary key to be returned, and fill in 0 before the insufficient part.

      HsqlMaxValueIncrementer and MySQLMaxValueIncrementer The two primary key value generators work based on tables. Use the columnName attribute to define the name of the primary key column, and use the cacheSize attribute to define the number of cached primary keys. When the primary key value in the memory is used up, the generator will obtain the cacheSize primary keys at a time, this reduces the number of Data Accesses and improves application performance.

      We use DateFieldValueIncremente R gets the primary key value from the database to compensate for this defect. First, adjust the PostJdbcDao code and add DateFieldValueIncremente R attribute, and obtain the next primary key value from the sequence:
      Code listing 13 use DateFieldValueIncremente R generates a primary key

Public class PostJdbcDao extends JdbcDaoSupport implements PostDao {private DataFieldMaxValueIncremeNter incret; ① primary key value generator public Void AddPost (final Post post ){... Getjdbctemplate(cmd.exe cute (SQL, New AbstractLobCreatingPreparedStatementCallback ( This . LobHandler) {protected Void SetValues (PreparedStatement ps, LobCreator lobCreator) throws SQLException {ps. setInt ( 1 , Incre. nextIntValue (); ② get the next primary key value... }});}... // Omitting the get/setter Method }
At ②, we get the next primary key value through incret. nextIntValue.


Generate primary key values in sequence
      Create a seq_post_id sequence in the Oracle database, which provides the primary key value for t_post. The following is the script for creating seq_post_id:

Create Sequence seq_post_id increment By 1 Start With 1 ;

  Then, adjust the Spring configuration and use OracleSequenceMaxValueInCrementer acts as the primary key generator:

< Bean Id = "Incret" Class = "Org. springframework. jdbc. support. incrementer. OracleSequenceMaxValueIncrementer" > < Property Name = "IncrementerName" Value = "Seq_post_id" /> ① Specify the sequence name < Property Name = "DataSource" Ref = "DataSource" /> ② Set the data source </ Bean > < Bean Id = "PostDao" Parent = "Dao" Class = "Com. baobaotao. dao. jdbc. PostJdbcDao" > < Property Name = "LobHandler" Ref = "OracleLobHandler" /> < Property Name = "Incret" Ref = "Incret" /> ③ Add a primary key generator </ Bean >

 Generate primary key values as tables
 Create a t_post_id table in Mysql for maintaining the t_post primary key. The following is an SQL script for creating the table and inserting initialization:

Create table t_post_id (sequence_id Int ) Type = MYISAM;
Insert into t_post_id values (0 );

   Because the concurrent access volume of the primary key maintenance table is large, it is best to declare it as the MYISAM type. In addition, you need to provide the initial value for the table so that the subsequent primary key value increases over this.
After being changed to a MySql database, we only need to make small adjustments to the Spring Configuration:

< Bean Id = "Incret" Class = "Org. springframework. jdbc. support. incrementer. MySQLMaxValueIncrementer" > < Property Name = "IncrementerName" Value = "T_post_id" /> ① Set the table name for maintaining the primary key < Property Name = "ColumnName" Value = "Sequence_id" /> ② Name of the column used to generate the primary key value < Property Name = "CacheSize" Value = "10" /> ③ Cache size < Property Name = "DataSource" Ref = "DataSource" /> </ Bean > < Bean Id = "PostDao" Parent = "Dao" Class = "Com. baobaotao. dao. jdbc. PostJdbcDao" > < Property Name = "LobHandler" Ref = "DefaultLobHandler" /> < Property Name = "Incret" Ref = "Incret" /> </ Bean >      Both incrementerName and columnName are easy to understand. cacheSize determines the number of Primary keys returned at a time. Here we set it to 10. When MySQLMaxValueIncrementer is used for the first time # MySQLMaxValueIncrementer when nextIntValue () is used to obtain the primary key value The number of t_post_id. sequence_id increases by 10, and the value of the primary key is obtained from the cache when the nextIntValue () method is called nine times. The value of t_post_id. sequence_id is increased by 10 again until the nextIntValue () method is called again for 10th times.

Summary
      The primary key production method can be divided into Application Layer generation and database generation from the production location. The application layer uses database sequences or tables to generate primary keys. This method ensures program portability and security, and improves the running efficiency through the cache mechanism. Some databases support the primary key generation mechanism of the auto-incrementing key of data tables. In versions earlier than JDBC 3.0, Statement cannot automatically obtain the corresponding primary key of the newly added record. After inserting data, you need to immediately execute a database-related primary key to obtain the SQL statement to obtain the corresponding primary key value. In the case of high database concurrency, an incorrect primary key value may be obtained. In this case, it is a good option to prepare the primary key value at the application layer before inserting data.


In addition, you need to set setReturnGeneratedKeys (true) before executing update in SqlUpdate );