ibatis.net学习笔记(四) 简单查询语法

上篇介绍了配置文件 这篇介绍下核心的sql语法

一 简单查询

 <select id="SelectAll" resultMap="FullResultMap">
      SELECT * FROM IDCreater
    </select>

这个SQL 返回结果是 FullResultMap  就是我们直接配置的对应关系

c# 调用语法

string stmtId = "IDCreaterMap.SelectAll"; 
return base.SqlMapper.QueryForList<EIDCreater>(stmtId, iDName);

这样就返回一个List;

 <select id="SelectByIDName" parameterClass="String" resultMap="FullResultMap" extends="SelectAll">
      WHERE
      (IDCreater.IDName = #IDName#)
    </select>

注意这个配置 他是扩展于 SelectAll 这个ID的  那么我们可以直接接着写条件了 where xx=xxxx

调用语法同上

 

--------------------------------------------------------------------------------------------------------------------------------------

update

  <update id="UpdateByIDName" parameterClass="IDCreater">
      UPDATE IDCreater SET   IDName=#IDName# ,    CurrentCount=#CurrentCount# ,    Description=#Description# ,    CreateTime=#CreateTime#         WHERE IDName = #IDName#
    </update>

调用语法

string stmtId = "IDCreaterMap.UpdateByIDName";
base.SqlMapper.Update(stmtId, entity);

看到这里可能大家要问 如果我只更新两个字段 难道非要把整个entity传进去吗?

我们可以这样写这个map 穿一个Hashtable

  <update id="UpdateByIDName" parameterClass="Hashtable">
      UPDATE IDCreater SET   IDName=#IDName# ,    CurrentCount=#CurrentCount#   WHERE IDName = #IDName#
    </update>

调用语法 注意Hashtable字段要对上

           Hashtable ht = new Hashtable();
            ht.Add("IDName", idName);
            ht.Add("CurrentCount", currentCount);

string stmtId = "IDCreaterMap.UpdateByIDName";
base.SqlMapper.Update(stmtId, ht);

 --------------------------------------------------------------------------------------------------------------

Delete

 <delete id="DeleteByIDName" parameterClass="string">
      DELETE FROM IDCreater  WHERE IDName = #IDName#
    </delete>

调用语法

string stmtId = "IDCreaterMap.DeleteByIDName";
base.SqlMapper.Update(stmtId, iDName);

------------------------------------------------------------------------------------------------------------

insert 

<insert id="InsertIDCreater" parameterClass="IDCreater">
      INSERT INTO IDCreater(IDName, CurrentCount, Description, CreateTime )
      VALUES(#IDName# , #CurrentCount# , #Description# , #CreateTime#  )
  </insert>

如果我们插入的时候同时要的到自增的ID怎么写呢?

这样 写个例子 在下面加一列属性 selectkey

<insert id="InsertIDCreater" parameterClass="IDCreater">
      INSERT INTO IDCreater(IDName, CurrentCount, Description, CreateTime )
      VALUES(#IDName# , #CurrentCount# , #Description# , #CreateTime#  )
     <selectKey resultClass="System.Int32" Property="IDName" type="post">
      SELECT @@IDENTITY AS value
    </selectKey>
  </insert>

若需要返回自增列的调用方法

string stmtId = "NewsMap.InsertIDCreater";
Object obj = base.SqlMapper.Insert(stmtId, entity);
return (System.Int32)obj;