The Oracle database is used all the time to implement the auto-increment field through the sequence, and the auto-increment id is obtained before the insertion, and can be saved and used in subsequent operations.
The Oracle database is used all the time to implement the auto-increment field through the sequence, and the auto-increment id is obtained before the insertion, and can be saved and used in subsequent operations.
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.Of: 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 current 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 Cache Nter 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:
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:
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
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
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:
Then, adjust the Spring configuration and use OracleSequenceMaxValueInCrementer acts as the primary key generator:
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:
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:
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 );
