Ibatis automatically generated primary key (Oracle, ms SQL Server, MySQL)

From: http://www.javaeye.com/topic/215571

 

In the sqlmap configuration file of ibatisSelectkeyElement hasTypeAttribute, which can be specifiedPreOrPostIndicates that (Pre) Or generate later (Post).

Oracle settings

XML Code

1. <! -- Oracle sequence -->
2. <InsertId = "insertproduct-Oracle" parameterclass = "com. domain. Product">
3. <SelectkeyResultclass = "int" keyproperty = "ID" type = "pre">
4. <! [CDATA [select stockidsequence. nextval as ID from dual]>
5. </Selectkey>
6. <! [CDATA [insert into product (prd_id, prd_description) values (# ID #,# description #)]>
7. </Insert>

<!-- Oracle SEQUENCE --> <insert id="insertProduct-ORACLE" parameterClass="com.domain.Product">     <selectKey resultClass="int" keyProperty="id" type="pre">         <![CDATA[SELECT STOCKIDSEQUENCE.NEXTVAL AS ID FROM DUAL]]>     </selectKey>     <![CDATA[insert into PRODUCT (PRD_ID,PRD_DESCRIPTION) values(#id#,#description#)]]> </insert> 

Ms SQL Server Configuration

XML Code

1. <! -- Microsoft SQL Server identity column -->
2. <InsertId = "insertproduct-MS-SQL" parameterclass = "com. domain. Product">
3. <! [CDATA [insert into product (prd_description) values (# description #)]>
4. <SelectkeyResultclass = "int" keyproperty = "ID" type = "Post">
5. <! [CDATA [select @ identity as ID]>
6. <! -- Scope_identity () should be used for this method, but this function belongs to a domain function and needs to be executed in a statement block. -->
7. </Selectkey>
8. </Insert>

<! -- Microsoft SQL Server identity column --> <insert id = "insertproduct-MS-SQL" parameterclass = "com. domain. Product"> <! [CDATA [insert into product (prd_description) values (# description #)]> <selectkey resultclass = "int" keyproperty = "ID" type = "Post"> <! [CDATA [select @ identity as ID]> <! -- Scope_identity () should be used for this method, but this function belongs to a domain function and needs to be executed in a statement block. --> </Selectkey> </Insert>

The above ms SQL Server configuration is provided on the official website, but in fact it is quite risky! Use the following configurations to ensure that a valid primary key is obtained.

XML Code

1. <! -- Microsoft SQL Server identity column Improvement -->
2. <InsertId = "insertproduct-MS-SQL" parameterclass = "com. domain. Product">
3. <SelectkeyResultclass = "int" keyproperty = "ID">
4. <! [CDATA [insert into product (prd_description) values (# description #)
5. Select scope_identity () as ID]>
6. </Selectkey>
7. </Insert>

<! -- Microsoft SQL Server identity column Improvement --> <insert id = "insertproduct-MS-SQL" parameterclass = "com. domain. product "> <selectkey resultclass =" int "keyproperty =" ID "> <! [CDATA [insert into product (prd_description) values (# description #) Select scope_identity () as ID]> </selectkey> </Insert>

MySQL Configuration

XML Code

1. <! -- MySQL last insert id -->
2. <InsertId = "insertproduct-mysql" parameterclass = "com. domain. Product">
3. <! [CDATA [insert into product (prd_description) values (# description #)]>
4. <SelectkeyResultclass = "int" keyproperty = "ID">
5. <! [CDATA [select last_insert_id () as ID]>
6. <! -- The method last_insert_id () is bound to a database connection and belongs to the same unified session level. The preceding function problem of ms SQL Server will not occur. -->
7. </Selectkey>
8. </Insert>

<! -- MySQL last insert id --> <insert id = "insertproduct-mysql" parameterclass = "com. domain. Product"> <! [CDATA [insert into product (prd_description) values (# description #)]> <selectkey resultclass = "int" keyproperty = "ID"> <! [CDATA [select last_insert_id () as ID]> <! -- The method last_insert_id () is bound to a database connection and belongs to the same unified session level. The preceding function problem of ms SQL Server will not occur. --> </Selectkey> </Insert>

The above method ensures that the current auto-increment primary key is obtained when data is inserted to the maximum extent.