iBATIS.net直接执行sql语句

于 2015-04-21 00:00:11 发布
阅读量3.5k 收藏
点赞数
分类专栏： IBatis.Net 文章标签： IBatis.Net 执行 sql

在用iBatis.net与数据库打交道的过程中，发现iBatis.net拼接字符串不能直接拷贝然后在dbms里执行（获取runtime时的sql语句都是带@param1,@param2,@param3,...这样的形式）。有时候sql语句一个小小的标点(都怪自己学艺不精，呵呵)都会造成执行错误。其实，直接手写的sql字符串也可以通过iBatis来执行，而且支持select，insert，delete，update和存储过程。下面通过一段简单的代码来说明如何使用iBatis.net直接执行sql语句。

        /// <summary>
        /// 直接执行select语句
        /// </summary>
        /// <returns></returns>
        public IList<Person> ExecuteSelectSql(string sqlStr)
        {
            IList<Person> list = new List<Person>();
            if (!string.IsNullOrEmpty(sqlStr))
            {
                list = SqlMap.QueryForList<Person>("ExecuteSelectSql", sqlStr);
            }
            return list;
        }

Person.xml的方法对应的配置文件如下： 

    <!--直接执行select语句-->
    <statement id="ExecuteSelectSql"  parameterClass="string" remapResults="true" resultMap="SelectAllResult">
      $sql$
    </statement>

注意：
1、如果你没有设置remapResults="true"，测试时你会发现，尽管你传入的sql语句虽然不同，但总是执行返回第一次传入的sql语句的结果。原因就是iBatis.net的缓存设置在起作用，所以，一定要设置remapResults="true"。
2、$sql$的写法是安全的写法，<![CDATA[   $sql$   ]]>也比较常见，但是#sql#的写法可能自动生成''，sql语句就会有问题。所以最保险的方式就是采取$sql$的写法。
ps：示例使用iBatis.net直接成功执行select和insert操作。里面有一个分页的小功能。是笔者花了一天时间测试通过（就是常见的select top 方式），扩展性不是很好，但是很实用，而且很好调试，希望对你有帮助。
分页部分代码： 

using System;
using System.Collections.Generic;
using System.Text;
using System.Web;

namespace DotNet.Common.Util
{
    /// <summary>
    /// 分页实用类
    /// </summary>
    public sealed class AspNetPager
    {
        /// <summary>
        /// 获取页数
        /// </summary>
        /// <param name="nos">总记录数</param>
        /// <param name="recordCountPerPage">每页记录数</param>
        /// <returns></returns>
        public static int GetPageCout(object[] bos, int recordCountPerPage)
        {
            return (int)Math.Ceiling(((double)bos.Length / (double)recordCountPerPage));
        }

        /// <summary>
        /// 获取页数
        /// </summary>
        /// <param name="totalCount">总记录数</param>
        /// <param name="recordCountPerPage">每页记录数</param>
        /// <returns></returns>
        public static int GetPageCout(int totalCount, int recordCountPerPage)
        {
            int result = 0;
            if (totalCount % recordCountPerPage == 0)
            {
                result = totalCount / recordCountPerPage;
            }
            else
            {
                result = totalCount / recordCountPerPage + 1;
            }
            return result;
        }

        /// <summary>
        /// 写分页页码(没有表格)
        /// </summary>
        /// <param name="Response"></param>
        /// <param name="baseString"></param>
        /// <param name="pageCount">页数</param>
        /// <param name="nowPage">当前页数</param>
        /// <param name="recordCountPerPage">每页记录数</param>
        public static void RenderPager(HttpResponse Response, string baseString, int pageCount, int nowPage, int recordCountPerPage)
        {
            string pagerString = string.Empty;
            if (pageCount > 0)
            {
                pagerString = CreateLinkUrl(baseString, pageCount, nowPage, recordCountPerPage);
            }
            Response.Write(pagerString);
        }


        /// <summary>
        /// 写分页页码(有表格)
        /// </summary>
        /// <param name="Response"></param>
        /// <param name="baseString"></param>
        /// <param name="pageCount">页数</param>
        /// <param name="nowPage">当前页数</param>
        /// <param name="recordCountPerPage">每页记录数</param>
        public static void RenderTablePager(HttpResponse Response, string baseString, int pageCount, int nowPage, int recordCountPerPage)
        {

            if (pageCount > 0)
            {
                StringBuilder sbTable = new StringBuilder();
                sbTable.Append("<table><tr align='center'><td>");
                string pagerString = string.Empty;
                pagerString = CreateLinkUrl(baseString, pageCount, nowPage, recordCountPerPage);

                sbTable.Append(pagerString);
                sbTable.Append("</td></tr></table>");
                Response.Write(sbTable.ToString());
            }
        }


        /// <summary>
        /// 写分页页码(没有表格)
        /// </summary>
        /// <param name="Response"></param>
        /// <param name="baseString"></param>
        /// <param name="totalCount">总记录数</param>
        /// <param name="nowPage">当前页数</param>
        /// <param name="recordCountPerPage">每页记录数</param>
        public static void RenderPager(HttpResponse Response, int totalCount, int nowPage, int recordCountPerPage, string baseString)
        {
            int pageCount = GetPageCout(totalCount, recordCountPerPage);
            string pagerString = string.Empty;
            if (pageCount > 0)
            {
                pagerString = CreateLinkUrl(baseString, pageCount, nowPage, recordCountPerPage);
            }
            Response.Write(pagerString);
        }


        /// <summary>
        /// 写分页页码(有表格)
        /// </summary>
        /// <param name="Response"></param>
        /// <param name="baseString"></param>
        /// <param name="pageCount">总记录数</param>
        /// <param name="nowPage">当前页数</param>
        /// <param name="recordCountPerPage">每页记录数</param>
        public static void RenderTablePager(HttpResponse Response, int totalCount, int nowPage, int recordCountPerPage, string baseString)
        {
            int pageCount = GetPageCout(totalCount, recordCountPerPage);
            if (pageCount > 0)
            {
                StringBuilder sbTable = new StringBuilder();
                sbTable.Append("<table><tr align='center'><td>");
                string pagerString = string.Empty;
                pagerString = CreateLinkUrl(baseString, pageCount, nowPage, recordCountPerPage);

                sbTable.Append(pagerString);
                sbTable.Append("</td></tr></table>");
                Response.Write(sbTable.ToString());
            }
        }

        /// <summary>
        /// 生成分页字符串(显示页数和每页记录数相关)
        /// </summary>
        /// <param name="baseString"></param>
        /// <param name="pageCount">页数</param>
        /// <param name="nowPage">当前页数</param>
        /// <param name="recordCountPerPage">每页记录数(推荐记录数：10)</param>
        /// <returns></returns>
        private static string CreateLinkUrl(string baseString, int pageCount, int nowPage, int recordCountPerPage)
        {
            StringBuilder sb = new StringBuilder(" ");
            int from, to;
            if (nowPage - recordCountPerPage > 0)
            {
                from = nowPage - recordCountPerPage;
            }
            else
                from = 1;
            if (pageCount == 0)
                pageCount = 1;
            if (pageCount - nowPage - recordCountPerPage > 0)
            {
                to = nowPage + recordCountPerPage;
            }
            else
                to = pageCount;

            if (baseString.IndexOf("?") == -1)
                baseString += "?";
            else
                baseString += "&";
            sb.Append(string.Format("<a href={0}pageIndex=1 >首页</a>", baseString));
            if (pageCount > 1 && nowPage > 1)
            {
                sb.Append(string.Format("<a href={0}pageIndex=" + (nowPage - 1).ToString() + " >上一页</a>", baseString));
            }
            for (int i = from; i <= to; i++)
            {
                if (i == nowPage)
                    sb.Append( " <a href='javascript:void(0);' style='color:red;' >" + nowPage + "</a>");
                else
                    sb.Append(string.Format(" <a href={0}pageIndex={1} >{1}</a>", baseString, i));
            }
            if (pageCount > 1 && nowPage < pageCount)
            {
                sb.Append(string.Format("<a href={0}pageIndex=" + (nowPage + 1).ToString() + " >下一页</a>", baseString));
            }
            sb.Append(string.Format(" <a href={0}pageIndex={1} >尾页</a>", baseString, pageCount));
            return sb.ToString();
        }
    }
}

查询条件和sql拼接部分代码：
1、查询基类 

using System;
using System.Collections.Generic;
using System.Text;

namespace IBatisNetDemo.QueryCondition
{
    /// <summary>
    /// 查询条件基类
    /// </summary>
    [Serializable]
    public abstract class BaseQueryCondition
    {
        private int nowPage;
        /// <summary>
        /// 当前页
        /// </summary>
        public int NowPage
        {
            get { return nowPage; }
            set { nowPage = value; }
        }

        private int recordsPerPg;

        /// <summary>
        /// 每页记录数
        /// </summary>
        public int RecordsPerPg
        {
            get { return recordsPerPg; }
            set { recordsPerPg = value; }
        }


        private int totalCount;
        /// <summary>
        /// 总记录数
        /// </summary>
        public int TotalCount
        {
            get { return totalCount; }
            set { totalCount = value; }
        }

        private int totalPgCount;

        /// <summary>
        /// 总页数(根据总记录数和每页记录计算 )
        /// </summary>
        public int TotalPgCount
        {
            get { return totalPgCount; }
            set
            {
                try
                {
                    if (totalCount % recordsPerPg == 0)
                    {
                        totalPgCount = totalCount / recordsPerPg;
                    }
                    else
                    {
                        totalPgCount = totalCount / recordsPerPg + 1;
                    }
                }
                catch (DivideByZeroException dex)
                {
                    throw dex;
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
        }


        private string sortColumn;

        /// <summary>
        /// 排序的列名(通常都是ID或者其他整型字段,默认ID)
        /// </summary>
        public string SortColumn
        {
            get { return sortColumn; }
            set { sortColumn = value; }
        }

        private string sortDirection;

        /// <summary>
        /// 排序方向(DESC或者ASC  默认DESC)
        /// </summary>
        public string SortDirection
        {
            get { return sortDirection; }
            set { sortDirection = value; }
        }

        /// <summary>
        /// 取数据库中top num条记录sql语句
        /// </summary>
        /// <returns></returns>
        public virtual string GetSqlString()
        {
            return string.Empty;
        }

        /// <summary>
        /// 生成查询条件sql语句(连接在where后)
        /// </summary>
        /// <returns></returns>
        public virtual string GetWhereSqlString()
        {
            return string.Empty;
        }

        /// <summary>
        /// 生成获取总记录的sql语句(select count() from  的形式)
        /// </summary>
        /// <returns></returns>
        public virtual string GetTotalCountSqlString()
        {
            return string.Empty;
        }
    }
}

2、实际查询类 

using System;
using System.Collections.Generic;
using System.Text;
using IBatisNetDemo.Domain;

namespace IBatisNetDemo.QueryCondition
{
    [Serializable]
    public class PersonQuery : BaseQueryCondition
    {
        private Person query;
        /// <summary>
        /// 查询实体 (参数)
        /// </summary>
        public Person Query
        {
            get { return query; }
            set { query = value; }
        }

        /// <summary>
        /// 表名或者视图图名称
        /// </summary>
        public string TableName
        {
            get { return "person"; }
        }

        public PersonQuery() { }

        public PersonQuery(int nowPg, int recordsPerPg)
        {
            this.query = new Person();
            this.NowPage = nowPg;
            this.RecordsPerPg = recordsPerPg;
            this.SortColumn = "ID";
            this.SortDirection = SortDirection;
        }

        /// <summary>
        /// 构造函数 每次实例化 当前页数和总记录数初始化
        /// </summary>
        public PersonQuery(int nowPg, int recordsPerPg, string sortColumn, string sortDrection)
        {
            this.query = new Person();
            this.NowPage = nowPg;
            this.RecordsPerPg = recordsPerPg;

            if (string.IsNullOrEmpty(sortColumn))
            {
                this.SortColumn = "ID";
            }
            else
            {
                this.SortColumn = sortColumn;
            }

            if (string.IsNullOrEmpty(SortDirection))
            {
                this.SortDirection = "DESC";
            }
            else
            {
                if (string.Compare(sortDrection.ToUpper(), "DESC") != 0 && string.Compare(sortDrection.ToUpper(), "ASC") != 0)
                {
                    this.SortDirection = "DESC";
                }
                else
                {
                    this.SortDirection = SortDirection;
                }
            }
        }

        public override string GetSqlString()
        {
            string result = string.Empty;
            string sqlStr = GetWhereSqlString();
            if (this.NowPage == 1)
            {
                result = string.Format(" select top {0} PER_ID,PER_FIRST_NAME,PER_LAST_NAME,PER_BIRTH_DATE,PER_WEIGHT_KG,PER_HEIGHT_M "
                    + " from " + this.TableName + " "
                    + " where 1=1 {1} order by " + this.TableName + "." + this.SortColumn + "  " + this.SortDirection
                    , this.RecordsPerPg.ToString()
                    , sqlStr);
            }
            else
            {
                result = string.Format(" select top {0} PER_ID,PER_FIRST_NAME,PER_LAST_NAME,PER_BIRTH_DATE,PER_WEIGHT_KG,PER_HEIGHT_M "
                   + " from " + this.TableName + " "
                   + " where " + this.TableName + "." + this.SortColumn + "<(select min(" + SortColumn + ") from "
                   + " (select top {1} " + this.TableName + "." + this.SortColumn + " from " + this.TableName + "  where 1=1 {2} "
                   + " order by " + this.TableName + "." + this.SortColumn + " " + this.SortDirection + ") tbTemp) {2} "
                   + "order by " + this.TableName + "." + this.SortColumn + " " + this.SortDirection
                   , this.RecordsPerPg.ToString()
                   , ((this.NowPage - 1) * this.RecordsPerPg).ToString()
                   , sqlStr.ToString());
            }
            return result;
        }

        public override string GetWhereSqlString()
        {
            string result = null;
            if (this.query == null)
            {
                result = string.Empty;
            }
            else
            {
                System.Text.StringBuilder sqlStr = new StringBuilder(" ");
                if (!string.IsNullOrEmpty(query.FirstName))
                {
                    sqlStr.Append(string.Format("  and " + this.TableName + ".Per_First_Name  like '%{0}%' ", query.FirstName));
                }
                if (!string.IsNullOrEmpty(query.LastName))
                {
                    sqlStr.Append(string.Format("  and " + this.TableName + ".Per_Last_Name like '%{0}%' ", query.LastName));
                }
                result = sqlStr.ToString();
            }
            return result;
        }

        public override string GetTotalCountSqlString()
        {
            string result = string.Empty;
            string sqlStr = GetWhereSqlString();
            result = "select COUNT(" + this.SortColumn + ") as TotalCount from " + this.TableName + " where 1=1 " + sqlStr;
            return result;
        }
    }
}

在页面中的调用： 

<%@ Page Language="C#" AutoEventWireup="true"  CodeFile="Default.aspx.cs"  Inherits="IBatisWeb.Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>IBatis Test</title>
</head>
<body>
    <form id="form1" runat="server">
    
    <table>
    <tr>
    <td>FirstName:</td><td><asp:TextBox ID="txtFirstName" runat="server"></asp:TextBox>
         </td>
    </tr>
      <tr>
      <td>LastName</td><td><asp:TextBox ID="txtLastName" runat="server"></asp:TextBox>
        </td>
    </tr>
    <tr align="center"><td colspan="2">
        <asp:Button ID="btnSearch" runat="server" Text="查询" OnClick="btnSearch_Click" />
    </td></tr>
        
    <tr>
    <td colspan="2">
    <asp:GridView ID="gvModel" runat="server">
        </asp:GridView>
    </td>
    </tr>
    
    </table>
 <%--   写入一个分页 --%>
    <%DotNet.Common.Util.AspNetPager.RenderTablePager(Response, searchCondtion.TotalCount, searchCondtion.NowPage, 10, "Default.aspx?firstName=" + searchCondtion.Query.FirstName + "&lastName=" + searchCondtion.Query.LastName); %>
    </form>
</body>
</html>

页面的cs代码： 

using System;
using System.Data;
using System.Configuration;
using System.Collections.Generic;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using IBatisNetDemo;
using IBatisNetDemo.Domain;
using IBatisNetDemo.Service;
using IBatisNetDemo.QueryCondition;
using DotNet.Common.Util;

namespace IBatisWeb
{
    public partial class Default : System.Web.UI.Page
    {
        protected PersonQuery searchCondtion = new PersonQuery(1, 10, "PER_ID", "DESC");
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                /*直接执行sql语句*/
                PersonService ps = new PersonService();

                //Response.Write("Direct excute a insert sql:<br/>");
                //ps.ExecuteInsertSql();

                //Response.Write("Direct excute a select sql:<br/>");
                //IList<Person> listResults = ps.ExecuteSelectSql();

                Response.Write(listResults.Count.ToString() + "<br/>");

                foreach (Person item in listResults)
                {
                    Response.Write(item.FirstName + "_" + item.LastName + "<br/>");
                }

                //Response.Write("<br/>(The End)");

                /*简单分页*/
                if (!string.IsNullOrEmpty(Request["pageIndex"])) //说明点击了页码
                {
                    searchCondtion.NowPage = int.Parse(Request["pageIndex"]);
                    Person model = new Person();
                    model.FirstName = Request["firstName"];
                    model.LastName = Request["lastName"];
                    searchCondtion.Query = model;
                    BindingData(searchCondtion);
                }
            }
        }
        protected void btnSearch_Click(object sender, EventArgs e)
        {
            Person model = new Person();
            model.FirstName = this.txtFirstName.Text.Trim();
            model.LastName = this.txtLastName.Text.Trim();
            searchCondtion.Query = model;
            BindingData(searchCondtion);
        }

        /// <summary>
        /// 数据绑定
        /// </summary>
        /// <param name="query"></param>
        private void BindingData(PersonQuery query)
        {
            try
            {
                PersonService ps = new PersonService();
                string sqlCount = searchCondtion.GetTotalCountSqlString();
                searchCondtion.TotalCount = ps.ExecuteSelectCountSql(sqlCount); //算出总记录数
                string sql = searchCondtion.GetSqlString();
                IList<Person> listPersons = ps.ExecuteSelectSql(sql); //取出前num条记录

                this.gvModel.DataSource = listPersons;
                this.gvModel.DataBind();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
}
}

最后，希望您能给出改进意见和建议。
Demo下载： demo



转载自：http://www.cnblogs.com/jeffwongishandsome/archive/2009/10/26/1512550.html

jumtre
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值