mybatis常用sql拼写



<mapper namespace = "com.company.people">

<select id="findList" resultType="People">
  select a.name as name, a.age as age
  from people a
  <where>
    <if test="title !=null and title!='' and title !='1'.toString()">
        a.title like
         <if test="dbName=='oracle'"> '%'||#{title}||'%'</if>
         <if test="dbName=='mssql'"> '%'+#{title}+'%'</if>
         <if test="dbName=='mysql'"> concat('%',#{title},'%')</if>
    </if>
  </where>
 <choose>
   <when test="page !=null and page.orderBy !=null and page.OrderBy !=''">
       order by ${page.orderBy}
   </when>
   <otherwise>
       order by a.update_date desc
   </otherwise>
 </choose>
</select>

<insert id="insert">
 insert into people(
     id,
     name,
     age,
     del_flag
 )values(
   #{id},
   #{name},
   #{age},
   #{del_flag}
 )
<insert>

<update id="update">
 update people set
  name=#{name},
  age=#{age}
  where id=#{id}
</update>

一次插入一个list
<insert id="saveAllPeople" parameterType="List">
 insert into people(
  id,
  name,
  age,
  del_flag
 )
 <if test="list.get(0).dbName=='oracle'">
  <foreach collection="list" item="e" separator="union all">
   select
   #{e.id},
   #{e.name},
   #{e.age},
   #{e.del_flag}
  </foreach>
  </if>
 <if test="list.get(0).dbName=='mssql' or list.get(0).dbName=='mysql'">
  valuse
  <foreach collection="list" item="e" open="" sparator="," close="">
   (
   #{e.id},
   #{e.name},
   #{e.age},
   #{e.del_flag}
   )
  </foreach>
 </if>
</insert>