Nibatis实例(2)

1.配置文件

Employee.xml

<?xml version="1.0" encoding="utf-8" ?>
<sqlMap namespace="Test" xmlns="http://ibatis.apache.org/mapping" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

    <alias>
      <typeAlias alias="Employee" type="NIbatis.Entity.Employee" />
    </alias>
 
    <parameterMaps>
     
      <parameterMap id="insert-employee-param" class="Employee">
        <parameter property="EmployeeNumber" />
        <parameter property="FirstName" dbType="VARCHAR" />
        <parameter property="LastName" dbType="VARCHAR" />
        <parameter property="Title" dbType="VARCHAR" />
      </parameterMap>

      <parameterMap id="update-employee-param" class="Employee">
        <parameter property="Id" />
        <parameter property="EmployeeNumber" />
        <parameter property="FirstName" dbType="VARCHAR" />
        <parameter property="LastName" dbType="VARCHAR" />
        <parameter property="Title" dbType="VARCHAR" />
      </parameterMap>
    </parameterMaps>

  <resultMaps>
    <resultMap id="EmployeeList" class="Employee">
      <result property="Id" column="ID"/>
      <result property="EmployeeNumber" column="EMPLOYEE_NUMBER"/>
      <result property="FirstName" column="FIRST_NAME"/>
      <result property="LastName" column="LAST_NAME"/>
      <result property="Title" column="TITLE"/>
      <result property="Department" resultMapping="Test.Department"/>
    </resultMap>
  </resultMaps>
 
    <statements>
      <delete id="deleteEmployee" parameterClass="int">
        delete from Employee WHERE ID = #Id#
      </delete>

      <insert id="insertEmployee" parameterMap="insert-employee-param">
        insert into Employee (EMPLOYEE_NUMBER, FIRST_NAME,LAST_NAME,TITLE)
        values (?,?,?,?)
      </insert>

      <update id="updateEmployee" parameterClass="Employee">
        update Employee
        set EMPLOYEE_NUMBER = #EmployeeNumber#,FIRST_NAME = #FirstName#,
        LAST_NAME = #LastName#,TITLE = #Title#
        where ID = #Id#
      </update>

      <select id="GetEmployee" parameterClass="int" resultClass="Employee">
        SELECT
        ID AS Id,
        EMPLOYEE_NUMBER AS EmployeeNumber,
        FIRST_NAME AS FirstName,
        LAST_NAME AS LastName,
        TITLE AS Title
        From Employee
        Where ID = #Id#
      </select>

      <select id="GetAllEmployee" resultMap="EmployeeList" >
        SELECT Employee.*,Department.*
        From Employee,Department
        where Employee.DepartmentId = Department.DepartmentId
      </select>

      <select id="GetAllEmployeeByCondition" resultMap="EmployeeList" parameterClass="Employee">
        SELECT
        ID AS Id,
        EMPLOYEE_NUMBER AS EmployeeNumber,
        FIRST_NAME AS FirstName,
        LAST_NAME AS LastName,
        TITLE AS Title
        From Employee
        <dynamic prepend="WHERE">
          <isNotNull property="FirstName" prepend="and">
            FIRST_NAME like '%$FirstName$%'
          </isNotNull>
          <isNotNull property="LastName" prepend="and">
            LAST_NAME like '%$LastName$%'
          </isNotNull>
        </dynamic>
      </select>
    </statements>
</sqlMap>

Department.xml

<?xml version="1.0" encoding="utf-8" ?>
<sqlMap namespace="Test" xmlns="http://ibatis.apache.org/mapping" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

    <alias>
      <typeAlias alias="Department" type="NIbatis.Entity.Department" />
    </alias>

  <resultMaps>
    <resultMap id="Department" class="Department">
      <result property="DepartmentId" column="DepartmentId"/>
      <result property="Name" column="DepName"/>
    </resultMap>
  </resultMaps>
   
</sqlMap>

2.数据操作

//查询

int number = 1001;
Employee emp = EmployeeManager.GetEmployee(number);
if (emp != null)
{
Response.Write(emp.FirstName + ' ' + emp.LastName);
}

//增加
Employee emp = new Employee();
emp.FirstName = "kenny";
emp.LastName = "jiang";
emp.EmployeeNumber = 2005;
emp.Title = "SSE";
Mapper.Instance().Insert("insertEmployee", emp);

//删除

 ISqlMapper sqlMapper = Mapper.Instance();
Employee emp2 = Mapper.Instance().QueryForObject<Employee>("GetEmployee", 2);
 sqlMapper.Delete("deleteEmployee", 2);

 //修改
ISqlMapper sqlMapper = Mapper.Instance();
Employee emp = Mapper.Instance().QueryForObject<Employee>("GetEmployee", 1);
emp.FirstName = "kenny2";
emp.LastName = "jiang2";
emp.EmployeeNumber = 20052;
emp.Title = "SSE2";
sqlMapper.Update("updateEmployee", emp);

//查询全部

ISqlMapper sqlMapper = Mapper.Instance();
IList<Employee> empList = Mapper.Instance().QueryForList<Employee>("GetAllEmployee",null);
foreach (Employee emp in empList)
{
   Response.Write(emp.Title + "<br/>");
}

//按照条件查询
Employee empCondition = new Employee();
empCondition.FirstName = "k";
empCondition.LastName = "w";
ISqlMapper sqlMapper = Mapper.Instance();
IList<Employee> empList = Mapper.Instance().QueryForList<Employee>("GetAllEmployeeByCondition", empCondition);
foreach (Employee emp in empList)
{
Response.Write(emp.FirstName + "<br/>");
}

//多表查询
ISqlMapper sqlMapper = Mapper.Instance();
IList<Employee> empList = Mapper.Instance().QueryForList<Employee>("GetAllEmployee", null);
 foreach (Employee emp in empList)
{
Response.Write(emp.Title + "&nbsp;&nbsp;" + emp.Department.Name + "<br/>");
}