<?xml version="1.0" encoding="utf-8" ?> <!-- / // 文件描述:Blog映射xml // 文件作者:灵感软件 // 创建日期:2015/6/7 9:10:24 // 代码作用:sqlmap的sql语句/博客表 / --> <sqlMap namespace="DotText.Universal" xmlns="http://ibatis.apache.org/mapping" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <!--对应实体类--> <alias> <typeAlias alias="BlogModel" type="DotText.Universal.BlogModel" /> </alias> <!--返回值--> <resultMaps> <resultMap id="FullResultMap" class="BlogModel"> <result property="ID" column="ID" dbType="int"/> <result property="Title" column="Title" dbType="nvarchar"/> <result property="SubTitle" column="SubTitle" dbType="nvarchar"/> <result property="UserName" column="UserName" dbType="nvarchar"/> <result property="AddTime" column="AddTime" dbType="datetime"/> <result property="EditTime" column="EditTime" dbType="datetime"/> <result property="ApplyReason" column="ApplyReason" dbType="nvarchar"/> <result property="CloseReason" column="CloseReason" dbType="nvarchar"/> <result property="Style" column="Style" dbType="int"/> <result property="Path" column="Path" dbType="nvarchar"/> <result property="UserID" column="UserID" dbType="int"/> <result property="SkinID" column="SkinID" dbType="int"/> <result property="Show" column="Show" dbType="nvarchar"/> <result property="Status" column="Status" dbType="int"/> <result property="ViewTime" column="ViewTime" dbType="bigint"/> <result property="Point" column="Point" dbType="bigint"/> <result property="FavTime" column="FavTime" dbType="bigint"/> </resultMap> </resultMaps> <statements> <!--计算总记录数--> <select id="GetCountBlog" resultClass="System.Int32"> SELECT count(*) FROM [dbo].[Blog] </select> <!--查找所有--> <select id="SelectAllBlog" resultMap="FullResultMap"> SELECT * FROM [dbo].[Blog] </select> <!--查找所有分页 适合asp:ObjectDataSource--> <select id="SelectAllBlogPaging" parameterClass="System.Collections.Hashtable" resultMap="FullResultMap"> SELECT * FROM ( SELECT *,ROW_NUMBER() OVER(ORDER BY ID) AS num FROM dbo.[Blog] ) AS t WHERE t.num BETWEEN #startRowIndex#+1 AND #startRowIndex#+#maxRows# order by ID desc </select> <select id="SelectAllBlogDataTable" resultClass="System.Data.DataTable"> SELECT * FROM [dbo].[Blog] </select> <!--分页查找--> <select id="SelectAllBlogPage" parameterClass="System.Collections.Hashtable" resultMap="FullResultMap"> SELECT * FROM ( SELECT *,ROW_NUMBER() OVER(ORDER BY ID) AS num FROM dbo.[Blog] ) AS t WHERE t.num BETWEEN (#page#-1)*#rows#+1 AND #page#*#rows# order by ID desc </select> <!--条件查询--> <select id="SelectByKeyBlog" parameterClass="BlogModel" resultMap="FullResultMap" extends="SelectAllBlog"> WHERE ([Blog].[ID] = #ID,dbType=int#) </select> <!--获取同名的个数 <select id="GetBlogByName" parameterClass="System.String" resultMap="FullResultMap" extends="SelectAllBlog"> WHERE ([Blog].[Name] = #Name,dbType=nvarchar#) </select>--> <!--插入记录--> <insert id="InsertBlog" parameterClass="BlogModel"> INSERT INTO [dbo].[Blog] ( [Title] , [SubTitle] , [UserName] , [AddTime] , [EditTime] , [ApplyReason] , [CloseReason] , [Style] , [Path] , [UserID] , [SkinID] , [Show] , [Status] , [ViewTime] , [Point] , [FavTime] ) VALUES ( #Title,dbType=nvarchar# , #SubTitle,dbType=nvarchar# , #UserName,dbType=nvarchar# , #AddTime,dbType=datetime# , #EditTime,dbType=datetime# , #ApplyReason,dbType=nvarchar# , #CloseReason,dbType=nvarchar# , #Style,dbType=int# , #Path,dbType=nvarchar# , #UserID,dbType=int# , #SkinID,dbType=int# , #Show,dbType=nvarchar# , #Status,dbType=int# , #ViewTime,dbType=bigint# , #Point,dbType=bigint# , #FavTime,dbType=bigint# ) <selectKey resultClass="System.Int32" keyProperty="ID" > SELECT @@IDENTITY AS ID </selectKey> </insert> <!--更新记录--> <update id="UpdateBlog" parameterClass="BlogModel"> UPDATE [dbo].[Blog] SET #Title,dbType=nvarchar# , #SubTitle,dbType=nvarchar# , #UserName,dbType=nvarchar# , #AddTime,dbType=datetime# , #EditTime,dbType=datetime# , #ApplyReason,dbType=nvarchar# , #CloseReason,dbType=nvarchar# , #Style,dbType=int# , #Path,dbType=nvarchar# , #UserID,dbType=int# , #SkinID,dbType=int# , #Show,dbType=nvarchar# , #Status,dbType=int# , #ViewTime,dbType=bigint# , #Point,dbType=bigint# , #FavTime,dbType=bigint# WHERE ([Blog].[ID] = #ID,dbType=int#) </update> <!--删除记录--> <delete id="DeleteBlog" parameterClass="BlogModel"> DELETE FROM [dbo].[Blog] WHERE ([Blog].[ID] = #ID,dbType=int#) </delete> <!--删除记录 通过ID--> <delete id="DeleteBlogByID" parameterClass="System.Collections.Hashtable" > DELETE FROM [dbo].[Blog] WHERE ([Blog].[ID] = #ID,dbType=int#) </delete> </statements> </sqlMap>