<?xml version="1.0" encoding="utf-8" ?>
<sqlMap namespace="News" xmlns="http://ibatis.apache.org/mapping" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" >
<alias>
<typeAlias alias="News" type="ESPS.Domain.News,ESPS.Domain" />
</alias>
<resultMaps>
<resultMap id="NewsResult" class="News">
<result property="NewsId" column="NEWS_ID" />
<result property="NewsTitle" column="NEWS_TITLE" />
<result property="NewsContent" column="NEWS_CONTENT" />
<result property="NewsReleaseDate" column="NEWS_RELEASE_DATE" />
<result property="CreationDate" column="CREATION_DATE" />
<result property="CreatedBy" column="CREATED_BY" />
<result property="LastUpdateDate" column="LAST_UPDATE_DATE" />
<result property="LastUpdatedBy" column="LAST_UPDATED_BY" />
</resultMap>
</resultMaps>
<parameterMaps>
<parameterMap id="NewsParameter" class="News">
<parameter property="NewsId" column="NEWS_ID" />
<parameter property="NewsTitle" column="NEWS_TITLE" />
<parameter property="NewsContent" column="NEWS_CONTENT" />
<parameter property="NewsReleaseDate" column="NEWS_RELEASE_DATE" />
<parameter property="CreationDate" column="CREATION_DATE" />
<parameter property="CreatedBy" column="CREATED_BY" />
<parameter property="LastUpdateDate" column="LAST_UPDATE_DATE" />
<parameter property="LastUpdatedBy" column="LAST_UPDATED_BY" />
</parameterMap>
</parameterMaps>
<statements>
<insert id="InsertNews" parameterClass="News" >
<selectKey resultClass="float?" type="pre" property="NewsId" >
SELECT ESPS_SEQUENCE.NEXTVAL AS VALUE FROM DUAL
</selectKey>
INSERT INTO ESPS_NEWS (NEWS_ID,NEWS_TITLE,NEWS_CONTENT,NEWS_RELEASE_DATE,CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY)
VALUES (#NewsId#,#NewsTitle#,#NewsContent#,#NewsReleaseDate#,#CreationDate#,#CreatedBy#,#LastUpdateDate#,#LastUpdatedBy#)
</insert>
<select id="GetNewsList" resultMap="NewsResult">
SELECT * FROM ESPS_NEWS
</select>
<select id="GetNewsById" resultMap="NewsResult" parameterClass="float?">
SELECT * FROM ESPS_NEWS
<dynamic prepend="WHERE">
NEWS_ID = #value#
</dynamic>
</select>
<select id="GetNewsListByParameter" resultMap="NewsResult" parameterClass="ESPS.Parameter.NewsParameter,ESPS.Parameter">
SELECT * FROM ESPS_NEWS
<dynamic prepend="WHERE">
<isNotNull prepend="AND" property="NewsTitle">
<![CDATA[
NEWS_TITLE = #NewsTitle#
]]>
</isNotNull>
<isNotNull prepend="AND" property="NewsContent">
<![CDATA[
NEWS_CONTENT = #NewsContent#
]]>
</isNotNull>
<isNotNull prepend="AND" property="NewsReleaseDate">
<![CDATA[
NEWS_RELEASE_DATE = #NewsReleaseDate#
]]>
</isNotNull>
<isNotNull prepend="AND" property="CreationDate">
<![CDATA[
CREATION_DATE = #CreationDate#
]]>
</isNotNull>
<isNotNull prepend="AND" property="CreatedBy">
<![CDATA[
CREATED_BY = #CreatedBy#
]]>
</isNotNull>
<isNotNull prepend="AND" property="LastUpdateDate">
<![CDATA[
LAST_UPDATE_DATE = #LastUpdateDate#
]]>
</isNotNull>
<isNotNull prepend="AND" property="LastUpdatedBy">
<![CDATA[
LAST_UPDATED_BY = #LastUpdatedBy#
]]>
</isNotNull>
</dynamic>
</select>
<select id="GetNewsListByAnyWhere" resultMap="NewsResult" parameterClass="ESPS.Parameter.NewsParameter,ESPS.Parameter">
SELECT * FROM ESPS_NEWS
<dynamic prepend="WHERE">
<isNotEmpty property="UnlimitedQuery">
<![CDATA[
$UnlimitedQuery$
]]>
</isNotEmpty>
<isEmpty property="UnlimitedQuery">
1=2
</isEmpty>
</dynamic>
</select>
<update id="UpdateNews" parameterClass="News">
<![CDATA[
UPDATE ESPS_NEWS
SET
NEWS_TITLE = #NewsTitle#,
NEWS_CONTENT = #NewsContent#,
NEWS_RELEASE_DATE = #NewsReleaseDate#,
CREATION_DATE = #CreationDate#,
CREATED_BY = #CreatedBy#,
LAST_UPDATE_DATE = #LastUpdateDate#,
LAST_UPDATED_BY = #LastUpdatedBy#
]]>
<dynamic prepend="WHERE">
NEWS_ID = #NewsId#
</dynamic>
</update>
<update id="UpdateNewsByParameter" parameterClass="ESPS.Parameter.NewsParameter,ESPS.Parameter">
UPDATE ESPS_NEWS
SET
<dynamic>
<isNotEmpty property="NewsTitle">
NEWS_TITLE = #NewsTitle#,
</isNotEmpty>
<isNotEmpty property="NewsContent">
NEWS_CONTENT = #NewsContent#,
</isNotEmpty>
<isNotEmpty property="NewsReleaseDate">
NEWS_RELEASE_DATE = #NewsReleaseDate#,
</isNotEmpty>
<isNotEmpty property="CreationDate">
CREATION_DATE = #CreationDate#,
</isNotEmpty>
<isNotEmpty property="CreatedBy">
CREATED_BY = #CreatedBy#,
</isNotEmpty>
<isNotEmpty property="LastUpdateDate">
LAST_UPDATE_DATE = #LastUpdateDate#,
</isNotEmpty>
<isNotEmpty property="LastUpdatedBy">
LAST_UPDATED_BY = #LastUpdatedBy#,
</isNotEmpty>
</dynamic>
NEWS_ID = NEWS_ID
<dynamic prepend="WHERE">
<isNotEmpty property="UnlimitedQuery">
<![CDATA[
$UnlimitedQuery$
]]>
</isNotEmpty>
<isEmpty property="UnlimitedQuery">
1=2
</isEmpty>
</dynamic>
</update>
<delete id="DeleteNews" parameterClass="News">
<![CDATA[
DELETE ESPS_NEWS
]]>
<dynamic prepend="WHERE">
NEWS_ID = #NewsId#
</dynamic>
</delete>
<delete id="DeleteNewsById" parameterClass="float?">
<![CDATA[
DELETE ESPS_NEWS
]]>
<dynamic prepend="WHERE">
NEWS_ID = #value#
</dynamic>
</delete>
<delete id="DeleteNewsByParameter" parameterClass="ESPS.Parameter.NewsParameter,ESPS.Parameter">
<![CDATA[
DELETE ESPS_NEWS
]]>
<dynamic prepend="WHERE">
<isNotNull prepend="AND" property="NewsTitle">
<![CDATA[
NEWS_TITLE = #NewsTitle#
]]>
</isNotNull>
<isNotNull prepend="AND" property="NewsContent">
<![CDATA[
NEWS_CONTENT = #NewsContent#
]]>
</isNotNull>
<isNotNull prepend="AND" property="NewsReleaseDate">
<![CDATA[
NEWS_RELEASE_DATE = #NewsReleaseDate#
]]>
</isNotNull>
<isNotNull prepend="AND" property="CreationDate">
<![CDATA[
CREATION_DATE = #CreationDate#
]]>
</isNotNull>
<isNotNull prepend="AND" property="CreatedBy">
<![CDATA[
CREATED_BY = #CreatedBy#
]]>
</isNotNull>
<isNotNull prepend="AND" property="LastUpdateDate">
<![CDATA[
LAST_UPDATE_DATE = #LastUpdateDate#
]]>
</isNotNull>
<isNotNull prepend="AND" property="LastUpdatedBy">
<![CDATA[
LAST_UPDATED_BY = #LastUpdatedBy#
]]>
</isNotNull>
</dynamic>
</delete>
<delete id="DeleteNewsbyAnyWhere" parameterClass="ESPS.Parameter.NewsParameter,ESPS.Parameter">
<![CDATA[
DELETE ESPS_NEWS
]]>
<dynamic prepend="WHERE">
<isNotEmpty property="UnlimitedQuery">
<![CDATA[
$UnlimitedQuery$
]]>
</isNotEmpty>
<isEmpty property="UnlimitedQuery">
1=2
</isEmpty>
</dynamic>
</delete>
</statements>
</sqlMap>
分析:
sqlMap节点包括4个子节点alias、resultMaps、parameterMaps、sataments。
下面我们一个个分析:
<alias>
<typeAlias alias="News" type="ESPS.Domain.News,ESPS.Domain" />
</alias>
(1)Type Alias节点
定义了本映射文件中的别名,以避免过长变量值的反复书写,此例中通过typeAlias节点为类“IBatisNetDemo.Domain.Person”定义了一个别名“Person”,这样在本配置文件中的其他部分,需要引用“IBatisNetDemo.Domain.Person”类时,只需以其别名替代即可。
resultMaps节点
resultMaps实现dotnet实体到数据库字段的映射配置:
<resultMaps>
<resultMap id="NewsResult" class="News">
<result property="NewsId" column="NEWS_ID" />
<result property="NewsTitle" column="NEWS_TITLE" />
<result property="NewsContent" column="NEWS_CONTENT" />
<result property="NewsReleaseDate" column="NEWS_RELEASE_DATE" />
<result property="CreationDate" column="CREATION_DATE" />
<result property="CreatedBy" column="CREATED_BY" />
<result property="LastUpdateDate" column="LAST_UPDATE_DATE" />
<result property="LastUpdatedBy" column="LAST_UPDATED_BY" />
</resultMap>
</resultMaps>
Statement配置:
⑴ ID
指定了操作ID,之后我们可以在代码中通过指定操作id 来执行此节点所定义的操作,如:
SqlMap.Update("UpdatePerson", person);
ID设定使得在一个配置文件中定义两个同名节点成为可能(两个update节点,以不同id区分)
⑵ parameterClass
指定了操作所需的参数类型,此例中update 操作以IBatisNetDemo.Domain.Person类型的对象作为参数,目标是将提供的Person实例更新到数据库。
parameterClass="Person"中,user为“IBatisNetDemo.Domain.Person”
类的别名,别名可通过typeAlias节点指定,如示例配置文件中的:
<typeAlias alias="Person" type="IBatisNetDemo.Domain.Person,IBatisNetDemo" />
⑶ <![CDATA[……]]>
通过<![CDATA[……]]>节点,可以避免SQL 中与XML 规范相冲突的字符对XML映射文件的合法性造成影响。
⑷ 执行更新操作的SQL,这里的SQL 即实际数据库支持的SQL 语句,将由IBatisNet填入参数后交给数据库执行。
⑸ SQL中所需的用户名参数,“# FirstName #”在运行期会由传入的Person对象的FirstName属性填充。
⑹ SQL 中所需的用户性别参数“# LastName #”,将在运行期由传入的user 对象的LastName属性填充。
⑺ SQL中所需的条件参数“#id#”,将在运行期由传入的Person对象的Person属性填充。
对于这个示例,IBatisNet在运行期会读取id 为“UpdatePerson”的update节点的SQL定义,并调用指定的user对象的对应getter方法获取属性值,并用此属性值,对SQL中的参数进行填充后提交数据库执行。
Statement配置包含了数个与Sql Statement相关的节点,<statement>元素是一个通用的能够包容任意类型sql的元素。我们可以用更多细节的元素。
这些细节元素提供更好的错误检查以及一些更多的功能。(例如,一个插入函数能够返回数据库自动生成的key)。以下表格总结了声明类型元素以及他们的特性和属性。