很多数据库支持自动生成主键的数据类型。不过这通常(并不总是)是个私有的特性。SQL Map通过<insert>的子元素<selectKey>来支持自动生成的键值。它同时支持预生成(如Oracle)和后生成两种类型(如MS-SQL Server)。
1. 建表,将id设置为主键,且需加上auto_increment设置为自动增加
create table ibatis_db.t_product(pro_id int(6)
PRIMARY KEY auto_increment not null,
pro_description varchar(150) not null,
pro_price double not null);
2.product_sqlmap.xml中将
- <insert id="insertProduct"
- parameterClass="product">
- <![CDATA[
- insert into t_product(prd_id,prd_description,prd_price)
- values(#id#,#description#,#price#)
- ]]>
- </insert>
修改为:
- <!-- <span style="color: #ff0000;">Oracle SEQUENCE Example</span>
- -->
- <insert id="insertProduct-ORACLE" parameterClass="com.domain.Product">
- <selectKey resultClass="int" keyProperty="id" >
- SELECT STOCKIDSEQUENCE.NEXTVAL AS ID FROM DUAL
- </selectKey>
- insert into T_PRODUCT (PRD_ID,PRD_DESCRIPTION)
- values (#id#,#description#)
- </insert>
- <!--<span style="color: #ff0000;">Microsoft SQL Server IDENTITY Column Example</span>
- -->
- <insert id="insertProduct-MS-SQL" parameterClass="com.domain.Product">
- insert into T_PRODUCT (PRD_DESCRIPTION)
- values (#description#)
- <selectKey resultClass="int" keyProperty="id" >
- SELECT @@IDENTITY AS ID
- </selectKey>
- </insert>
- <!--<span style="color: #ff0000;">My sql SEQUENCE</span>
- -->
- <insert id="insertUser-MY-SQL" parameterClass="product">
- INSERT INTO T_PRODUCT (pro_description, pro_price) VALUES (#description#,#price#)
- <selectKey resultClass="int" keyProperty="id">
- SELECT LAST_INSERT_ID() as id
- </selectKey>
- </insert><span style="color: #000000;">
- </span>