数据持久层之——MyBatis

     一.对myibatis的认知

         我也是刚接触ibatis的，作为数据持久层的框架相比于ibatis我对于hibernate更加的熟悉。 先说说ibatis与hibernate的区别吧。两者都是数据持久层的框架用来表示ORM(对象的关系映射)。而hibernate是一款全自动的ORM，提供了从POJO到数据库表的全套映射机制。程序员往往只需定义好了POJO 到数据库表的映射关系，即可通过 Hibernate 提供的方法完成持久层操作。程序员甚至不需要对 SQL 的熟练掌握，Hibernate 会根据制定的存储逻辑，自动生成对应的 SQL 并调用 JDBC 接口加以执行。到数据库表的全套映射机制，ibatis是一款半自动的ORM，batis 的着力点，则在于POJO 与 SQL之间的映射关系。也就是说，ibatis并不会为程序员在运行期自动生成 SQL 执行。具体的 SQL 需要程序员编写，然后通过映射配置文件，将SQL所需的参数，以及返回的结果字段映射到指定 POJO。

  二.ibatis的SQL语句的书写

    1.这是xml文件的DTD约束：

       <?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >

   2.对应dao层和javaBEAN
<mapper namespace="hcb.dao.interfaceClass.approval.EventTodoInfoMapper" >
  <resultMap id="BaseResultMap" type="hcb.dto.approval.EventTodoInfo" >

    3.对应于数据库表的字段名和数据类型

    <id column="fake_id" property="fakeId" jdbcType="INTEGER" />
    <result column="create_datetime" property="createDatetime" jdbcType="TIMESTAMP" />
    <result column="update_datetime" property="updateDatetime" jdbcType="TIMESTAMP" />
    <result column="employee_uuid" property="employeeUuid" jdbcType="VARCHAR" />
    <result column="delete_at" property="deleteAt" jdbcType="VARCHAR" />
    <result column="approval_uuid" property="approvalUuid" jdbcType="VARCHAR" />
    <result column="approval_title" property="approvalTitle" jdbcType="VARCHAR" />
    <result column="approval_type" property="approvalType" jdbcType="VARCHAR" />
    <result column="approval_status" property="approvalStatus" jdbcType="VARCHAR" />
    <result column="missions_status" property="missionsStatus" jdbcType="VARCHAR" />
    <result column="missions_title" property="missionsTitle" jdbcType="VARCHAR" />
    <result column="missions_uuid" property="missionsUuid" jdbcType="VARCHAR" />
    <result column="missions_creator_uuid" property="missionsCreatorUuid" jdbcType="VARCHAR" />
    <result column="approval_creator_uuid" property="approvalCreatorUuid" jdbcType="VARCHAR" />
    <result column="event_todo_uuid" property="eventTodoUuid" jdbcType="VARCHAR" />
  </resultMap>
  <sql id="Base_Column_List" >
    fake_id, create_datetime, update_datetime, employee_uuid, delete_at, approval_uuid, 
    approval_title, approval_type, approval_status, missions_status, missions_title, 
    missions_uuid, missions_creator_uuid, approval_creator_uuid, event_todo_uuid
  </sql>

     4.查询语句
  <select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Integer" >
    select 
    <include refid="Base_Column_List" />
    from event_todo_info
    where fake_id = #{fakeId,jdbcType=INTEGER}
  </select>
  <select id="selectByApprovalUuid" resultMap="BaseResultMap" parameterType="java.lang.String" >
    select 
    <include refid="Base_Column_List" />
    from event_todo_info
    where approval_uuid = #{approvalUuid,jdbcType=VARCHAR}
  </select>
  <select id="selectByAssignmentUuid" resultMap="BaseResultMap" parameterType="java.lang.String" >
    select 
    <include refid="Base_Column_List" />
    from event_todo_info
    where missions_uuid = #{missionsUuid,jdbcType=VARCHAR} 

    模糊查询
  </select>
  <select id="eventTodoInfoList" resultMap="BaseResultMap" parameterType="java.util.HashMap">
    select
    <include refid="Base_Column_List" /> 
    from event_todo_info
    where delete_at is null and employee_uuid like CONCAT(CONCAT('%' #{employee_uuid,jdbcType=VARCHAR}),'%') limit #{start},#{limit}
  </select>
  <select id="countTotal"  resultType="java.lang.Integer">
    select count(*) from event_todo_info where delete_at is null
  </select>
  <select id="selectByEventTodoUuid" resultMap="BaseResultMap" parameterType="java.lang.String">
   select 
   <include refid="Base_Column_List" />
   from event_todo_info
   where event_todo_uuid = #{eventTodoUuid,jdbcType=VARCHAR} and delete_at is null
  </select>
  <select id="eventInfoSearchList" resultMap="BaseResultMap" parameterType="java.util.HashMap">
    select  
    <include refid="Base_Column_List" />  
    from event_todo_info
    where delete_at is null and employee_uuid like CONCAT(CONCAT( #{condition,jdbcType=VARCHAR}),'%')
    or approval_title like CONCAT(CONCAT('%' , #{condition,jdbcType=VARCHAR}),'%')
    or missions_title like CONCAT(CONCAT('%' , #{condition,jdbcType=VARCHAR}),'%') limit #{start},#{limit}
  </select>

    删除语句
  <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer" >
    delete from event_todo_info
    where fake_id = #{fakeId,jdbcType=INTEGER}
  </delete>

     插入语句
  <insert id="insert" parameterType="hcb.dto.approval.EventTodoInfo" >
    insert into event_todo_info (fake_id, create_datetime, update_datetime, 
      employee_uuid, delete_at, approval_uuid, 
      approval_title, approval_type, approval_status, 
      missions_status, missions_title, missions_uuid, 
      missions_creator_uuid, approval_creator_uuid, 
      event_todo_uuid)
    values (#{fakeId,jdbcType=INTEGER}, #{createDatetime,jdbcType=TIMESTAMP}, #{updateDatetime,jdbcType=TIMESTAMP}, 
      #{employeeUuid,jdbcType=VARCHAR}, #{deleteAt,jdbcType=VARCHAR}, #{approvalUuid,jdbcType=VARCHAR}, 
      #{approvalTitle,jdbcType=VARCHAR}, #{approvalType,jdbcType=VARCHAR}, #{approvalStatus,jdbcType=VARCHAR}, 
      #{missionsStatus,jdbcType=VARCHAR}, #{missionsTitle,jdbcType=VARCHAR}, #{missionsUuid,jdbcType=VARCHAR}, 
      #{missionsCreatorUuid,jdbcType=VARCHAR}, #{approvalCreatorUuid,jdbcType=VARCHAR}, 
      #{eventTodoUuid,jdbcType=VARCHAR})
  </insert>
  <insert id="insertSelective" parameterType="hcb.dto.approval.EventTodoInfo" >
    insert into event_todo_info
    <trim prefix="(" suffix=")" suffixOverrides="," >
      <if test="fakeId != null" >
        fake_id,
      </if>
      <if test="createDatetime != null" >
        create_datetime,
      </if>
      <if test="updateDatetime != null" >
        update_datetime,
      </if>
      <if test="employeeUuid != null" >
        employee_uuid,
      </if>
      <if test="deleteAt != null" >
        delete_at,
      </if>
      <if test="approvalUuid != null" >
        approval_uuid,
      </if>
      <if test="approvalTitle != null" >
        approval_title,
      </if>
      <if test="approvalType != null" >
        approval_type,
      </if>
      <if test="approvalStatus != null" >
        approval_status,
      </if>
      <if test="missionsStatus != null" >
        missions_status,
      </if>
      <if test="missionsTitle != null" >
        missions_title,
      </if>
      <if test="missionsUuid != null" >
        missions_uuid,
      </if>
      <if test="missionsCreatorUuid != null" >
        missions_creator_uuid,
      </if>
      <if test="approvalCreatorUuid != null" >
        approval_creator_uuid,
      </if>
      <if test="eventTodoUuid != null" >
        event_todo_uuid,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides="," >
      <if test="fakeId != null" >
        #{fakeId,jdbcType=INTEGER},
      </if>
      <if test="createDatetime != null" >
        #{createDatetime,jdbcType=TIMESTAMP},
      </if>
      <if test="updateDatetime != null" >
        #{updateDatetime,jdbcType=TIMESTAMP},
      </if>
      <if test="employeeUuid != null" >
        #{employeeUuid,jdbcType=VARCHAR},
      </if>
      <if test="deleteAt != null" >
        #{deleteAt,jdbcType=VARCHAR},
      </if>
      <if test="approvalUuid != null" >
        #{approvalUuid,jdbcType=VARCHAR},
      </if>
      <if test="approvalTitle != null" >
        #{approvalTitle,jdbcType=VARCHAR},
      </if>
      <if test="approvalType != null" >
        #{approvalType,jdbcType=VARCHAR},
      </if>
      <if test="approvalStatus != null" >
        #{approvalStatus,jdbcType=VARCHAR},
      </if>
      <if test="missionsStatus != null" >
        #{missionsStatus,jdbcType=VARCHAR},
      </if>
      <if test="missionsTitle != null" >
        #{missionsTitle,jdbcType=VARCHAR},
      </if>
      <if test="missionsUuid != null" >
        #{missionsUuid,jdbcType=VARCHAR},
      </if>
      <if test="missionsCreatorUuid != null" >
        #{missionsCreatorUuid,jdbcType=VARCHAR},
      </if>
      <if test="approvalCreatorUuid != null" >
        #{approvalCreatorUuid,jdbcType=VARCHAR},
      </if>
      <if test="eventTodoUuid != null" >
        #{eventTodoUuid,jdbcType=VARCHAR},
      </if>
    </trim>
  </insert>

      更新语句
  <update id="updateByPrimaryKeySelective" parameterType="hcb.dto.approval.EventTodoInfo" >
    update event_todo_info
    <set >
      <if test="createDatetime != null" >
        create_datetime = #{createDatetime,jdbcType=TIMESTAMP},
      </if>
      <if test="updateDatetime != null" >
        update_datetime = #{updateDatetime,jdbcType=TIMESTAMP},
      </if>
      <if test="employeeUuid != null" >
        employee_uuid = #{employeeUuid,jdbcType=VARCHAR},
      </if>
      <if test="deleteAt != null" >
        delete_at = #{deleteAt,jdbcType=VARCHAR},
      </if>
      <if test="approvalUuid != null" >
        approval_uuid = #{approvalUuid,jdbcType=VARCHAR},
      </if>
      <if test="approvalTitle != null" >
        approval_title = #{approvalTitle,jdbcType=VARCHAR},
      </if>
      <if test="approvalType != null" >
        approval_type = #{approvalType,jdbcType=VARCHAR},
      </if>
      <if test="approvalStatus != null" >
        approval_status = #{approvalStatus,jdbcType=VARCHAR},
      </if>
      <if test="missionsStatus != null" >
        missions_status = #{missionsStatus,jdbcType=VARCHAR},
      </if>
      <if test="missionsTitle != null" >
        missions_title = #{missionsTitle,jdbcType=VARCHAR},
      </if>
      <if test="missionsUuid != null" >
        missions_uuid = #{missionsUuid,jdbcType=VARCHAR},
      </if>
      <if test="missionsCreatorUuid != null" >
        missions_creator_uuid = #{missionsCreatorUuid,jdbcType=VARCHAR},
      </if>
      <if test="approvalCreatorUuid != null" >
        approval_creator_uuid = #{approvalCreatorUuid,jdbcType=VARCHAR},
      </if>
      <if test="eventTodoUuid != null" >
        event_todo_uuid = #{eventTodoUuid,jdbcType=VARCHAR},
      </if>
    </set>
    where fake_id = #{fakeId,jdbcType=INTEGER}
  </update>
  <update id="updateByPrimaryKey" parameterType="hcb.dto.approval.EventTodoInfo" >
    update event_todo_info
    set create_datetime = #{createDatetime,jdbcType=TIMESTAMP},
      update_datetime = #{updateDatetime,jdbcType=TIMESTAMP},
      employee_uuid = #{employeeUuid,jdbcType=VARCHAR},
      delete_at = #{deleteAt,jdbcType=VARCHAR},
      approval_uuid = #{approvalUuid,jdbcType=VARCHAR},
      approval_title = #{approvalTitle,jdbcType=VARCHAR},
      approval_type = #{approvalType,jdbcType=VARCHAR},
      approval_status = #{approvalStatus,jdbcType=VARCHAR},
      missions_status = #{missionsStatus,jdbcType=VARCHAR},
      missions_title = #{missionsTitle,jdbcType=VARCHAR},
      missions_uuid = #{missionsUuid,jdbcType=VARCHAR},
      missions_creator_uuid = #{missionsCreatorUuid,jdbcType=VARCHAR},
      approval_creator_uuid = #{approvalCreatorUuid,jdbcType=VARCHAR},
      event_todo_uuid = #{eventTodoUuid,jdbcType=VARCHAR}
    where fake_id = #{fakeId,jdbcType=INTEGER}
  </update>
</mapper>