IBatisNet 之 自动生成主关键字和Insert返回主键ID 很多系统支持自动生成主关键字。一些数据库厂商预先生成
(oracle)
,一些数据库厂商之后生成
(mssal mysql).
。如果你在
<insert>
元素中使用
<selectKey>
节,你就能获得一个预先生成的
key.
。下面的例子演示了这种方法:
<!—Oracle SEQUENCE Example --> <insert id="insertProduct-ORACLE" parameterClass="product"> <selectKey resultClass="int" Property="id" > SELECT STOCKIDSEQUENCE.NEXTVAL AS ID FROM DUAL
</selectKey> insert into PRODUCT (PRD_ID,PRD_DESCRIPTION) values
(#id#,#description#) </insert>
<!— Microsoft SQL Server IDENTITY Column Example -->
<insert id="insertProduct-MS-SQL"
parameterClass="product"> insert into PRODUCT (PRD_DESCRIPTION)
values (#description#) <selectKey resultClass="int"
Property="id" > SELECT @@IDENTITY AS ID </selectKey>
</insert>
上面是IbatisNet的iBATIS Data Mapper Developer Guide 上的说明:下面来介绍一下具体的应用和注意的地方: person.xml ?xml version="1.0" encoding="utf-8" ?>
<sqlMap namespace="Person" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance " xsi:noNamespaceSchemaLocation="SqlMap.xsd">
<!-- XML "behind" document for the People service class. -->
<alias> <typeAlias alias="Person" type="IbatisTest.Domain.Person, IbatisTest.Domain" /> </alias> <resultMaps> <resultMap id="SelectResult" class="Person"> <result property="Id" column="PER_ID" /> <result property="FirstName" column="PER_FIRST_NAME" /> <result property="LastName" column="PER_LAST_NAME" /> <result property="BirthDate" column="PER_BIRTH_DATE" /> <result property="WeightInKilograms" column="PER_WEIGHT_KG" /> <result property="HeightInMeters" column="PER_HEIGHT_M" /> </resultMap> </resultMaps> <insert id="Insert" parameterClass="Person"> insert into PERSON (PER_FIRST_NAME, PER_LAST_NAME, PER_BIRTH_DATE, PER_WEIGHT_KG, PER_HEIGHT_M) values ( #FirstName#, #LastName#, #BirthDate#, #WeightInKilograms#, #HeightInMeters#) <selectKey property="Id" type="post" resultClass="int"> select CAST(@@IDENTITY as int) as value </selectKey> </insert> </statements> </sqlMap> <selectKey> 节返回一个从 sql server 生成的自动生成关键字。
//新增一个员工
private void Button1_Click(object sender, System.EventArgs e)
{
IbatisTest.Domain.Person person = new IbatisTest.Domain.Person();
person.Id =-1;//这里的赋值很重要,否则会出错 person.FirstName = "--New Person--";
person.BirthDate = Convert.ToDateTime("2000-4-2");
PersonHelper personhelper = new PersonHelper();
personhelper.Insert(person);
}
//业务逻辑类方法 public int Insert (Person person) { try {
int result = -1;
result =(int)Mapper().Insert("Insert",person);
return result;
} catch(Exception ex) {
throw ex; }
}
Feedback
如果多主键不是如何处理?
对于多主键的配置问题:
此时主键由两个字段组成per_id 和 group_id,parameterClass应该如何写?需要自己在cs里面定义该表的主键类,然后映射回去吗?还是有更好的办法?期待能看到你的解决办法.谢谢
<select id="Select" parameterClass="int" resultMap="SelectResult">
select
PER_ID,
PER_FIRST_NAME,
PER_LAST_NAME,
PER_BIRTH_DATE,
PER_WEIGHT_KG,
PER_HEIGHT_M
from PERSON
<dynamic prepend="WHERE">
<isParameterPresent>
//如何配置?
PER_ID = #value# and group_ID=??
</isParameterPresent>
</dynamic>
</select>
http://www.cnblogs.com/shanyou/Messages/2005/10/24/260907.html
如果多主键不是如何处理?
对于多主键的配置问题:
此时主键由两个字段组成per_id 和 group_id,parameterClass应该如何写?需要自己在cs里面定义该表的主键类,然后映射回去吗?还是有更好的办法?期待能看到你的解决办法.谢谢
<select id="Select" parameterClass="int" resultMap="SelectResult">
select
PER_ID,
PER_FIRST_NAME,
PER_LAST_NAME,
PER_BIRTH_DATE,
PER_WEIGHT_KG,
PER_HEIGHT_M
from PERSON
<dynamic prepend="WHERE">
<isParameterPresent>
//如何配置?
PER_ID = #value# and group_ID=??
</isParameterPresent>
</dynamic>
</select>
http://www.cnblogs.com/shanyou/Messages/2005/10/24/260907.html
如果是多主键,这两个多主键一般是另外的几张表的外键。这种情况你不需要去自动生成主键阿,处理在一个事务中就可以保证数据的完整性了吧。
我一般是将多主键可以转化成单主键
可以这样配置
对于多主键的配置问题:
此时主键由两个字段组成per_id 和 group_id,parameterClass应该如何写?<select id="Select" parameterClass="Hashtable" resultMap="SelectResult">
select
PER_ID,
PER_FIRST_NAME,
PER_LAST_NAME,
PER_BIRTH_DATE,
PER_WEIGHT_KG,
PER_HEIGHT_M
from PERSON
<dynamic prepend="WHERE">
<isNotNull prepend="AND" property="PerID">PER_ID = #PerID#</isNotNull>
<isNotNull prepend="AND" property="GroupId">group_ID= #GroupId#</isNotNull>
</dynamic>
</select>
处理成Hashtable?
该Hashtable包含PerID和GroupID对值?
Hastable ht = new Hastable();
ht.Add('PerID','001');
ht.Add('GroupID','001');
public Person Select (Hastable ht)
{
return (Person) Mapper().QueryForObject ("Select", ht);
}
这样子处理吗?
<dynamic prepend="WHERE">
<isNotNull prepend="AND" property="PerID">PER_ID = #PerID#</isNotNull>
<isNotNull prepend="AND" property="GroupId">group_ID= #GroupId#</isNotNull>
</dynamic>
谢谢你的回复,并且共享你的想法.
对,处理成Hashtable,该Hashtable包含PerID和GroupID对值