Today in the use of MySQL but do not know how to deal with, insert a record do not know how to get the ID just inserted, after checking the document found select LAST_INSERT_ID (), after inserting the query to get the ID, overjoyed.
You can't get the results you want in your program, so you suspect spring, because there's no problem with the basic JDBC test, so just follow the spring JDBC and look at the source before you see it, so get the database in spring. ConnectionOfConnectionCon=Datasourceutils.getconnection (Getdatasource ()); Hey, you can only blame yourself. Spring is not good enough, so you can't execute it after the insertSelect LAST_INSERT_ID () because the Select LAST_INSERT_ID () is true to the currentConnection insert and update operations, how to get the key value of new records in spring, there is no way to go to Google.
Found the following article from IT168, titled "Spring Application Data primary key generation strategy inventory", excerpt as follows:
In general, after adding a domain object, you need to get the corresponding primary key value. Using the application layer to maintain the primary key is beneficial to the optimization of program performance and the improvement of application portability. In the case of a database self-adding primary key, if the JDBC driver cannot bind the primary key of the new record, it is necessary to manually execute the query statement to get the corresponding primary key value, which can easily return the wrong primary key for a highly concurrent system. Datafieldmaxvalueincreme with CacheNter, the primary key value of the batch can be obtained at one time for use when inserting the domain object multiple times, and its execution performance is very high.
using a database's self-added primary key
We often use the self added field of the data as the primary key of the table, or the primary key value is not generated in the application layer, but is generated by the database when the record is added. In this way, the application layer does not know the primary key value of the object until the object is saved, but must save the data before returning the primary key value from the database. In many cases, we need to get the primary key value after the new object is persisted. In the ORM framework such as Hibernate, after the new object is persisted, hibernate will automatically bind the primary key value to the object, which brings a lot of convenience to the development of the program.
In the JDBC 3.0 specification, when new records are added, the primary key values that are automatically generated by the database are allowed to be bound to statement or PreparedStatement. When using statement, you can bind primary key values in the following ways:
int executeupdate (String sql,int Autogeneratedkeys)
You can also create a preparedstatement that is bound to add value by connection:
PreparedStatement preparestatement (String sql, int autogeneratedkeys)
When the Autogeneratedkeys parameter is set to the Statement.return_generated_keys value, the primary key value produced by the database is bound, and the primary key value is not bound when set to Statement.no_generated_keys. The following code demonstrates the process of statement binding and getting the primary key value produced by the database:
Statement stmt = conn.createstatement (); String SQL = " INSERT into T_topic (topic_title,user_id) VALUES (' Test subject ', ' 123 ') " ; Stmt.executeupdate (Sql,statement.return_generated_keys); ① Specifies the binding table self-added primary key value ResultSet rs = Stmt.getgeneratedkeys (); if (Rs.next ()) { int key = rs.getint (); ② get corresponding table self-added primary key value}
Spring leverages this technique to provide a way to return the primary key value of a new record:
int update (PreparedStatementCreator PSC, Keyholder generatedKeyHolder
Org.springframework.jdbc.support.KeyHolder is a callback interface that spring uses to save the primary key for the new record, and the interface method for that interface is described as follows:
number Getkey () throws Invaliddataaccessapiusag Eexception
When only one row of data is inserted and the primary key is not a compound key and is a numeric type, This method allows you to return a new primary key value directly. This method throws Invaliddataaccessapiusag eexception if it is a composite primary key, or if more than one primary key is returned. This method is the most commonly used method because, in general, we only insert one data at a time and the primary key field type is a numeric type;
Map Getkeys () throws Invaliddataaccessapiusag Eexception
If it is a composite primary key, the column name and column values form a entry in the map. If more than one primary key is returned, the method throws a Invaliddataaccessapiusag eexception exception;
List getkeylist ():
If multiple primary keys are returned, That is, PreparedStatement adds more records, then each primary key corresponds to a map, and multiple maps make up a list.
spring for the Keyholder interface refers to a generic implementation class generatedKeyHolder, which returns the value of the self growing primary key when a new record is added. Suppose we want to load the primary key value into the object after adding the Forum plate object, you can adjust 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 holder 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 ()) ② get primary key from host key}
in this way, after calling Addforum (final Forum Forum) new Forum domain object, Forum will have the corresponding primary key value to facilitate subsequent use.
In the version prior to JDBC 3.0, PreparedStatement could not bind the primary key, and if the use of table self-add keys (such as MySQL's auto increment or SQL Server identity) would pose a challenge to getting the correct primary key value- Because you must immediately execute another query that gets the new primary key after inserting the data. Table 1 shows the query statements for different databases to obtain the latest self-added primary key values:
table 1 Different databases get the newly added primary key value
|
Database
|
Get query statement with new primary key
|
|
DB2
|
Identity_val_local ()
|
|
Informix
|
SELECT dbinfo (' Sqlca.sqlerrd1 ') from <TABLE>
|
|
Sybase
|
SELECT @ @IDENTITY
|
|
Sql server
|
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 concurrency rate of the database is high, for example, before executing a query primary key after inserting a record, the database executes several SQL statements that insert records, at which point the primary key value returned through Table 1 is the primary key value of the last INSERT statement, not the primary key value we want. So it is not safe to use a query to get the table's self added key value, which is why some databases (such as Oracle, Firebird) intentionally do not provide a bootstrap, but only the reason for the sequence, which forces you to get the primary key value before adding records. Oracle obtains the next value of the sequence through the select <sequence_name>.nextval from dual, while Firebird through select gen_id (<SEQUENCE_NAME> 1) The from rdb$database gets the next value of the sequence. In the 10.4.1 section, we will also talk about the application layer of the self-add key related knowledge.
Application layer generates primary key
Spring JDBC provides the support of the self-add and rowset, which allows us to provide primary key values for new records in the application tier without relying on the database's self-enhancement. Rowset is introduced in JDK 1.4, which allows data to be manipulated in the event of a disconnected connection, and in this section we will describe how to use rowset in spring JDBC.
Use of self-adding keys
The general database provides the function of the auto_increment, such as MySQL, sqlserverr identity field, etc. Spring allows you to generate primary key values at the application layer, which defines the Org.springframework.jdbc.support.incrementer.DataFieldMaxValueIncremeThe Nter interface provides two scenarios for generating a primary key: first, generating a primary key through a sequence, and second, generating a primary key through a table. Depending on how the primary key is generated and the database, Spring provides several implementation classes, as shown in Figure 1:
Figure 1 DatefieldvalueincrementeR Inheritance Class diagram
Depending on how the primary key is generated, you may need to configure information such as table name, primary key field name, or sequence name. Below, we take Oracle and MySQL as examples of how to use sequences and table fields to produce primary key values.
DatafieldmaxvalueincremeThe Nter interface defines 3 methods for obtaining the next primary key value:
int nextintvalue (): Gets the next primary key value, the primary key data type is int;
long nextlongvalue (): Gets the next primary key value, and the primary key data type is long;
string nextstringvalue (): Gets the next primary key value, and the primary key data type is String;
In its abstract implementation class AbstractdatafieldmaxvaluIn Eincrementer, several important properties are provided, where Incrementername defines the name of a sequence or primary key table, and if the primary key returned is a string type, the Paddinglength property may come in handy, which allows you to specify the length of the primary key to return. The insufficient part of the front complements 0.
Hsqlmaxvalueincrementer and MysqlmaxvalueincrementerThe two primary key value generator works on a table. By defining the name of the primary key column by the ColumnName property, the number of primary keys that are cached is defined by the CacheSize property, and when the primary key value in memory is exhausted, the generator acquires the primary key of the CacheSize one at a time, which reduces the number of data accesses and improves the performance of the application.
We passed the DatefieldvalueincrementeR Gets the primary key value from the database to compensate for this defect. First, adjust the Postjdbcdao code and add DatefieldvalueincrementeR property and obtains the next primary key value from the sequence by it:
Code Listing 13 using DatefieldvalueincrementeR produces primary key
public class Postjdbcdao extends Jdbcdaosupport implements Postdao {private Datafieldmaxvalueincrementer ; ① primary key value generator public void Addpost (final post post) {... getjdbctemplate (). Execute (SQL,new Abstractlobcreatingpreparedstatementcallback ( this. Lobhandler) {protected void setvalues ( PreparedStatement PS, Lobcreator lobcreator) throws SQLException {Ps.setint (1, Incre.nextintvalue ()); ② gets the next primary key value ...} }); } ... // omit Get/setter method }
At ②, we get the next primary key value through Incre.nextintvalue ().
generating primary key values in a sequential fashion
Create a seq_post_id sequence in the Oracle database that uses this sequence to provide the primary key value for T_post, and here is the script to create the seq_post_id:
Create sequence seq_post_id increment by 1-start with 1;
Next, adjust the configuration of spring and useOraclesequencemaxvaluein Crementer as the primary key generator:
<Bean Id= "Incre" Class= "Org.springframework.jdbc.support.incrementer.OracleSequenceMaxValueIncrementer" Property name = "Incrementername" value = "seq_post_id" /> ① specified 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 = ' Incre ' Ref = "Incre" /> ③ Add primary key primary key generator </ bean "
Generating primary key values in a table
in MySQL, create a t_post_id table for maintaining the T_post primary key, the following is the SQL script to create the table and insert initialization:
CREATE TABLE t_post_id (sequence_id int) Type = MYISAM;
INSERT into t_post_id values (0);
because primary key maintenance tables have a large number of concurrent accesses, it is a good idea to declare them as MyISAM types, in addition to providing an initial value for the table so that subsequent primary key values are incremented on top of this.
After adjusting to the MySQL database, we just need to make a small adjustment to the spring configuration:
<Bean Id= "Incre"Class= "Org.springframework.jdbc.support.incrementer.MySQLMaxValueIncrementer"><Property Name= "Incrementername" Value= "t_post_id"/> ① sets the table name to maintain the primary key <Property Name = "ColumnName" value = "sequence_id" /> ② column name used to generate primary key values . Property Name = "CacheSize" value = "Ten" /> ③ 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 = "Incre" ref = "Incre" /> </ Bean .
Incrementername and ColumnName are easy to understand, CacheSize decide the number of primary keys to return at a time, here we set to 10. When the first time through the Mysqlmaxvalueincrementer# Nextintvalue () Gets the primary key value, MysqlmaxvalueincrementerWill make t_post_id. The sequence_id is incremented by 10 and the primary key value is obtained from the cache when the Nextintvalue () method is called on subsequent 9 calls. The Nextintvalue () method will not be t_post_id again until the 10th time that it is called again. The sequence_id field value is incremented by 10, so the loop repeats.
Summary
The mode of production of primary key can be divided into two ways: Application layer generation and database generation. The application layer uses the database sequence or table to generate the primary key, which can guarantee the portability and security of the program, and can improve the running efficiency through the caching mechanism. Some databases support the primary key generation mechanism for the data table's statement, and in previous versions of JDBC 3.0, the corresponding primary key for the new record could not be automatically obtained by using the. At this point, after inserting the data, immediately execute a database-related primary key to get the SQL statement to get the corresponding primary key value, in case of high concurrency of the database, it is possible to get the incorrect primary key value. In this case, it is a good alternative to have the primary key value prepared in advance of the application layer before inserting data.
In addition, you need to set Setreturngeneratedkeys (true) before Sqlupdate performs update.
