项目中Mybatis动态sql

<?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="com.kaohe.mapper.PersonMapper">
    <select id="getPersonList"  resultType="com.kaohe.pojo.Person">
        select id,name,alarm,department,assessment_type,iscert , assessment_string from  person order by assessment_string
    </select>
    <select id="getPersonById" parameterType="string" resultType="com.kaohe.pojo.Person">
        select * from person where id=#{id}
    </select>
    <sql id="key">
        <trim suffixOverrides=",">
            <if test="id!=null">  id,</if>
            <if test="name!=null">  name,</if>
            <if test="alarm!=null">  alarm,</if>
            <if test="age!=null">  age,</if>
            <if test="sex!=null">  sex,</if>
            <if test="birthstring!=null">  birthstring,</if>
            <if test="post!=null">  post,</if>
            <if test="department!=null">  department,</if>
            <if test="bare_handed_defend!=null">  bare_handed_defend,</if>
            <if test="use_police_equipment!=null">  use_police_equipment,</if>
            <if test="use_weapons!=null">  use_weapons,</if>
            <if test="assessment_type!=null">  assessment_type,</if>
            <if test="assessment_string!=null">  assessment_string,</if>
            <if test="total!=null">  total,</if>
            <if test="remarks!=null">  remarks,</if>
            <if test="llkaoke!=null">  llkaoke,</if>
            <if test="iscert!=null">  iscert</if>
        </trim>
    </sql>
    <sql id="values">
        <trim suffixOverrides=",">
            <if test="id!=null">#{id},</if>
            <if test="name!=null">  #{name},</if>
            <if test="alarm!=null">  #{alarm},</if>
            <if test="age!=null">  #{age},</if>
            <if test="sex!=null">  #{sex},</if>
            <if test="birthstring!=null">  #{birthstring},</if>
            <if test="post!=null">  #{post},</if>
            <if test="department!=null">  #{department},</if>
            <if test="bare_handed_defend!=null">  #{bare_handed_defend},</if>
            <if test="use_police_equipment!=null">  #{use_police_equipment},</if>
            <if test="use_weapons!=null">  #{use_weapons},</if>
            <if test="assessment_type!=null">  #{assessment_type},</if>
            <if test="assessment_string!=null">  #{assessment_string},</if>
            <if test="total!=null">  #{total},</if>
            <if test="remarks!=null">  #{remarks},</if>
            <if test="llkaoke!=null">  #{llkaoke},</if>
            <if test="iscert!=null">  #{iscert}</if>
        </trim>
    </sql>
    <insert id="savePerson" parameterType="com.kaohe.pojo.Person">
        insert into person(<include refid="key"/>)
        values(<include refid="values"/>)
    </insert>

    <update id="updatePserson" parameterType="com.kaohe.pojo.Person">
       update person
         <set>
            <if test="name!=null"> name=#{name},</if>
            <if test="alarm!=null"> alarm=#{alarm},</if>
            <if test="age!=null"> age={age},</if>
             <if test="sex!=null"> sex={sex},</if>
             <if test="birthstring!=null"> birthday={birthstring},</if>
             <if test="post!=null"> post={post},</if>
             <if test="department!=null"> department={department},</if>
             <if test="bare_handed_defend!=null"> bare_handed_defend={bare_handed_defend},</if>
             <if test="use_police_equipment!=null"> use_police_equipment={use_police_equipment},</if>
             <if test="use_weapons!=null"> use_weapons={use_weapons},</if>
             <if test="assessment_type!=null"> assessment_type={assessment_type},</if>
             <if test="assessment_string!=null"> assessment_date={assessment_string},</if>
             <if test="total!=null"> total={total},</if>
             <if test="remarks!=null"> remarks={remarks},</if>
             <if test="llkaoke!=null"> llkaoke={llkaoke},</if>
            <if test="iscert!=null"> iscert={iscert</if>
         </set>
      where id#{id}
    </update>
    <delete id="deletePerson" parameterType="string">
        delete from person where id=#{id}
    </delete>

    <select id="getKuaiDaoQi" resultType="com.kaohe.pojo.Person">
        select id,name,alarm,department,assessment_type,iscert , assessment_string
        from  person
        where id in
        (select id  from person where assessment_type = ‘基本‘ and
        date_format(assessment_string,‘%y-%M-%d‘) &lt; DATE_ADD(CURRENT_DATE(),INTERVAL -18 MONTH)
        UNION
        select id  from person where assessment_type = ‘中级‘ and
        date_format(assessment_string,‘%y-%M-%d‘) &lt; DATE_ADD(CURRENT_DATE(),INTERVAL -30 MONTH)
        UNION
        select id  from person where assessment_type = ‘高级‘ and
        date_format(assessment_string,‘%y-%M-%d‘) &lt; DATE_ADD(CURRENT_DATE(),INTERVAL -42 MONTH)
        ) order by name
    </select>
<select id="queryPerson" resultType="com.kaohe.pojo.Person" parameterType="string">
    select * from person
    <where>
        <if test="name!=null&amp;name!=‘‘"> name=#{name}</if>
        <if test="alarm!=null&amp;alarm!=‘‘"> and alarm=#{alarm}</if>
    </where>

</select>
    <select id="queryPersonByAll" parameterType="string" resultType="com.kaohe.pojo.Person">
        select id,name,alarm,department,assessment_type,iscert , assessment_string from  person
   <where>
       <if test="name!=null&amp;name!=‘‘"> name like  CONCAT(‘%‘,CONCAT(#{name},‘%‘)) </if>
        <if test="alarm!=null&amp;alarm!=‘‘"> and  alarm like CONCAT(‘%‘,CONCAT(#{alarm},‘%‘))</if>
    <if test="department!=null&amp;department!=‘‘"> and department like CONCAT(‘%‘,CONCAT(#{department},‘%‘))</if>
   </where>
    </select>


</mapper>

项目中Mybatis动态sql

原文：https://www.cnblogs.com/liqisong/p/14453065.html