MyBatis The primary key generation strategy for different databases

This article transferred from: http://289972458.iteye.com/blog/1001851
Http://hi.baidu.com/zim_it/blog/item/8a2bd11205f5b56ecb80c4b7.html

When using MyBatis as a persistence layer, the INSERT statement defaults to returning the number of records inserted, rather than returning the primary key value of the record, and can be configured to do so if the business layer needs to get the primary key of the record

for the sequence primary key, Before you execute insert SQL, you must specify a primary key value for the records to be inserted, such as Oracle, DB2, which can be configured as follows:
to execute   CREATE SEQUENCE statement before using Oracle  mysql No, look down.
<insert id= "add" parametertype= "VO. Category ">
<selectkey resulttype=" Java.lang.Short "order=" before "keyproperty=" id ">
SELECT seq_test . Nextval from DUAL
</selectKey>
INSERT INTO category (Name_zh, parent_id,
Show_order, Delete_stat US, description
) VALUES (#{namezh,jdbctype=varchar},
#{parentid,jdbctype=smallint},
#{showorder,jdb Ctype=smallint},
#{deletestatus,jdbctype=bit},
#{description,jdbctype=varchar}
)
</insert>


for the table with the self-increment primary key, do not need the primary key when inserting, but automatically obtains a self-increment primary key in the inserting process, for example MySQL, may use the following two kinds of configuration way:

<insert id= "Add" parametertype = "Vo." Category "Usegeneratedkeys=" true "keyproperty=" id ">
INSERT INTO category (

Name_zh, parent_id, Show_order, Delete_status, description

) VALUES (

#{namezh,jdbctype=varchar}, #{parentid,jdbctype=smallint}, #{showorder,jdbctype=smallint},
#{deletestatus,jdbctype=bit}, #{description,jdbctype=varchar}
)
</insert>

Or

<insert id= "Add" parametertype= "VO. Category ">
<selectkey resulttype= "Java.lang.Short" order= "after" keyproperty= "id" >
SELECT last_insert_id () as ID
</selectKey>
INSERT INTO category (

Name_zh, parent_id, Show_order, Delete_status, description
) VALUES (

#{namezh,jdbctype=varchar},
#{parentid,jdbctype=smallint},
#{showorder,jdbctype=smallint},
#{deletestatus,jdbctype=bit},
#{description,jdbctype=varchar}
)
</insert>
After the insert operation is complete, the id attribute of the parameter category is already assigned.
If the primary key of the database table is not a self-increasing type, then the application layer is required to generate the primary key This is not much to say, the need for friends, can message exchange ·

-----------------------------------------
The following is the syntax for Oracle, where Oracle does not have a autoincrement, but the currval implemented with triggers is defined in the trigger.
<insert id= "Insert" parameterclass= "Profeekindobject" >
<! [cdata[
INSERT into T_pro_feekind (kindid,kindname,kindtype,enable)
VALUES (Seq_t_pro_feekind_id.nextval, #kindName #, #kindType #, #enable #)
]]>
<selectkey resultclass= "Java.lang.Integer" keyproperty= "Kindid" >
SELECT seq_t_pro_feekind_id. Currval as Kindid from DUAL
</selectKey>
</insert>

<!--Here's how to do it for MySQL---
<!--
<selectkey resultclass= "int" keyproperty= "id" >
SELECT @ @IDENTITY as ID
</selectKey>
-
Other reference codes:
When persisting an entity object (such as saving an object), if we do not use Selectkey, then we do not immediately get the id attribute of the Entity object, that is, the data table primary key
Java code
Permission Permission = new Permission ();
Permission.set ...

Permmisondao.createpermission (permission);
Assertnull (permission);
Permission Permission = new Permission ();
Permission.set ...

Permmisondao.createpermission (permission);
Assertnull (permission);

The Selectkey element is related to its position within the parent element

<insert id= "addpermission" parameterclass= "Permission" >
<selectkey resultclass= "int" keyproperty= "PermissionID" >
SELECT seq_p_permission. Nextval from DUAL
</selectKey>
INSERT into P_permission (
PermissionID, Permissioninfo, Permissionname, Permissionenname, URL
) VALUES (
#permissionId #, #permissionInfo #, #permissionName #, #permissionEnName #, #url #
)
</insert>

<insert id= "addpermission" parameterclass= "Permission" >
<selectkey resultclass= "int" keyproperty= "PermissionID" >
SELECT seq_p_permission. Nextval from DUAL
</selectKey>
INSERT into P_permission (
PermissionID, Permissioninfo, Permissionname, Permissionenname, URL
) VALUES (
#permissionId #, #permissionInfo #, #permissionName #, #permissionEnName #, #url #
)
</insert>

Mysql, SQL Server in the back
XML code

<insert id= "addpermission" parameterclass= "Permission" >
INSERT into P_permission (
PermissionID, Permissioninfo, Permissionname, Permissionenname, URL
) VALUES (
#permissionId #, #permissionInfo #, #permissionName #, #permiss
ionenname#, #url #
)
<selectkey resultclass= "int" keyproperty= "PermissionID" >
SELECT last_insert_id ()
</selectKey>
</insert>

<insert id= "addpermission" parameterclass= "Permission" >
INSERT into P_permission (
PermissionID, Permissioninfo, Permissionname, Permissionenname, URL
) VALUES (
#permissionId #, #permissionInfo #, #permissionName #, #permissionEnName #, #url #
)
<selectkey resultclass= "int" keyproperty= "PermissionID" >
SELECT last_insert_id ()
</selectKey>
</insert>

Like the above writing, and the location of the Selectkey is too close, ibatis sqlmap configuration file Selectkey element has a type attribute, you can specify pre or post representation before or after generation.
For Oracle, expressed as
XML code

<insert id= "addpermission" parameterclass= "Permission" >
<selectkey resultclass= "int" keyproperty= "PermissionID" type= "Pre" >
SELECT seq_p_permission. Nextval from DUAL
</selectKey>
INSERT into P_permission (
PermissionID, Permissioninfo, Permissionname, Permissionenname, URL
) VALUES (
#permissionId #, #permissionInfo #, #permissionName #, #permissionEnName #, #url #
)
</insert>

<insert id= "addpermission" parameterclass= "Permission" >
<selectkey resultclass= "int" keyproperty= "PermissionID" type= "Pre" >
SELECT seq_p_permission. Nextval from DUAL
</selectKey>
INSERT into P_permission (
PermissionID, Permissioninfo, Permissionname, Permissionenname, URL
) VALUES (
#permissionId #, #permissionInfo #, #permissionName #, #permissionEnName #, #url #
)
</insert>

Mysql, SQL Server, etc. are represented as:
XML code

<insert id= "addpermission" parameterclass= "Permission" >
<selectkey resultclass= "int" keyproperty= "PermissionID" type= "POST" >
SELECT last_insert_id ()
</selectKey>
INSERT into P_permission (
PermissionID, Permissioninfo, Permissionname, Permissionenname, URL
) VALUES (
#permissionId #, #permissionInfo #, #permissionName #, #permissionEnName #, #url #
)
</insert>

After the insert operation is complete, the id attribute of the parameter category is already assigned.
eg
Resourcesdao.insertselectiveandreturnid (RES);
Long NewID = Res.getresid ();