Mybatis--xml语句模版

<?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">
<mapper namespace="xxx">

<!-- 表字段  -->
<sql id="addFields">
　　id,
　　name,
　　money,
　　start_time,
　　end_time,
　　dscription,
　　is_delete
</sql>
  
<!-- 表字段映射  -->
<sql id="allFields">
    id,
    name,
    create_time as createTime,
    is_delete as isDelete
</sql>



<!-- 表名 -->
<sql id="table">
    user
</sql>

<!-- 查询条件 -->
<sql id="condition">
    <where> 1=1 
    <if test="id != null" >
      and id = #{id}
    </if>
    <if test="name != null and name != ''" >
      and name = #{name}
    </if>
    <if test="createTime != null" >
      and create_time = #{createTime}
    </if>
    <if test="isDelete != null" >
      and is_delete = #{isDelete}
    </if>
  </where>
</sql>

<!-- 模糊查询条件 -->
<sql id="vagueCondition">
    <where> 1=1 
    <if test="id != null" >
      and id  like "%"#{id}"%"
    </if>
    <if test="name != null and name != ''" >
    <!-- 查询拼接 第二种格式 '% ${name} %' -->
      and name like "%"#{name}"%"
    </if>
    <if test="createTime != null" >
      and create_time like "%"#{createTime}"%"
    </if>
    <if test="isDelete != null" >
      and is_delete like "%"#{isDelete}"%"
    </if>
  </where>
</sql>

<!-- 插入全字段 -->
<insert id="add" parameterType="xxx">
  insert into 
    <include refid="table" />
  　(<include refid="addFields"/>)
  values 
  (#{id},
   #{name},
   #{createTime}, 
   #{isDelete})
</insert>

<!-- 动态插入字段 -->
<insert id="addDynamic" parameterType="xxx">
  insert into 
    <include refid="table" />
  <trim prefix="(" suffix=")" suffixOverrides=",">
    <!-- test后是实体类字段 -->
    <if test="id != null">
      id,
    </if>
    <if test="name != null">
      name,
    </if>
    <if test="createTime != null">
      create_time,
    </if>
    <if test="isDelete != null">
      is_delete,
    </if>
  </trim>
  <trim prefix="values (" suffix=")" suffixOverrides=",">
    <if test="id != null">
      #{id},
    </if>
    <if test="name != null">
      #{name},
    </if>
    <if test="createTime != null">
      #{createTime},
    </if>
    <if test="isDelete != null">
      #{isDelete},
    </if>
  </trim>
</insert>

<!-- 批量插入 -->
<insert id="addInBatch" parameterType="com.hy.pojo.dal.dataobject.UserDO">
        insert into 
　　　　　　<include refid="table" /> 
　　　　　　(<include refid="addFields"/>) values 
        <foreach collection="collection" item="item" index="index" separator=",">
            (
                #{item.id},
                #{item.name},
                #{item.money},
                #{item.startTime},
                #{item.endTime},
                #{item.dscription},
                #{item.isDelete}
            )
        </foreach>
</insert>  


<!-- 删除 根据ID -->
<delete id="delete" parameterType="java.lang.Long">
  delete from 
    <include refid="table" />
  where id = #{id}
</delete>

<!-- 更新  根据ID-->
<update id="update" parameterType="xxx">
  update 
    <include refid="table" />
  <set>
    <if test="name != null">
      name = #{name},
    </if>
    <if test="createTime != null">
      create_time = #{createTime},
    </if>
    <if test="isDelete != null">
      is_delete = #{isDelete},
    </if>
  </set>
  where id = #{id}
</update>

 <!-- 查询 根据ID -->
<select id="get" parameterType="java.lang.Long" resultType="xxx">
  select
    <include refid="allFields" />
  from 
    <include refid="table" />
  where id = #{id}
</select>

 <!-- 查询 多条件 -->
<select id="getByMap" resultType="com.hy.pojo.dal.dataobject.UserDO" parameterType="Map" >
        select <include refid="allFields"/>
        from  
　　　　　　<include refid="table" />
        <include refid="condition"/>
</select>

<!-- 查询全部-->
<select id="gets" resultType="xxx">
    select
    <include refid="allFields" />
    from
    <include refid="table" />
</select>

<!-- 条件分页查询 -->
<select id="page" parameterType="Map" resultType="xxx">
  select
    <include refid="allFields" />
  from  
    <include refid="table" />
  <include refid="condition" />
  limit #{pageSize} offset #{start}
</select>

<!-- 条件分页模糊查询 -->
<select id="page" parameterType="xxx" resultType="xxx">
  select
    <include refid="allFields" />
  from  
    <include refid="table" />
  <include refid="vagueCondition" />
  limit #{pageSize} offset #{start}
</select>

<!-- 总记录数-->
<select id="count" resultType="long">
        select count(*) from <include refid="table" />
</select>

<!-- 动态查询记录数-->
<select id="getCount" parameterType="xxx" resultType="Integer">
  select count(1) from 
  <include refid="table" />
  <include refid="condition" />
</select>

</mapper>