When using MyBatis as the persistence layer, the insert statement defaults to not return the primary key value of the record, but returns the number of inserted records. If the business layer needs to obtain the primary key of the record, this function can be completed through configuration.
For Sequence primary key, before executing insert sql, a primary key value must be specified for the record to be inserted, such as Oracle and DB2. The following configuration method can be used:
<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_status, description)values (#{nameZh,jdbcType=VARCHAR},#{parentId,jdbcType=SMALLINT},#{showOrder,jdbcType=SMALLINT},#{deleteStatus,jdbcType=BIT},#{description,jdbcType=VARCHAR})</insert>For tables with self-increment primary keys, no primary key is needed when inserting, but a self-increment primary key is automatically obtained during the insertion process. For example, MySQL, the following two configuration methods can be adopted:
<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 insertion operation is completed, the id attribute of the parameter category has been assigned
If the primary key of the database table is not a self-increment type, then the application layer needs to generate the primary key..... I won’t say much about this. Friends who need it can leave a message to communicate..
The following is the writing method for Oracle. Oracle does not have autoincrement, but 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><!-- The following is the writing method 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 will 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... permisonDao.createPermission(permission); assertNull(permission); Permission permission = new Permission(); permission.set...permisonDao.createPermission(permission); assertNull(permission);
The selectKey element is related to its position in 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 and SQLServer are behind
Xml code
<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><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>
Written like the above, it is too closely related to the location of selectKey. The selectKey element of the sqlMap configuration file of iBatis has a type attribute, which can specify whether pre or post represents the generation before or after generation.
For Oracle, it is
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, SQLServer, etc. are expressed 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>
The above is the method of returning the primary key in MyBatis in MyBatis introduced to you by the editor. I hope it will be helpful to you. If you have any questions, please leave me a message and the editor will reply to you in time. Thank you very much for your support to Wulin.com website!