一.对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>