mybatis动态插入sql语句

if标签

1. if标签就类似于jstl的if标签

where标签

1. 在执行多条件选择时，可能会多出一个and 关键字。譬如：

    <select id="getEmpAndDep"  resultType="emp" databaseId="mysql">    select last_name,email,id,gender from employee    where   <if test="id!=null">    id=#{id}   </if>     <if test="lastName!=null and lastName!=''">      and last_name like #{lastName}   </if>    <if test="gender==0 or gender==1">      and gender=#{gender}   </if>    <if test="email!=null and email.trim()!=''">      and email=#{email}   </if>    </select> 

1. 那么，当id=null时候，拼接的语句就会多出来一个and。
2. where标签可以删除多余的前缀and /or，然后加上where
3. 将动态语句嵌入where标签中即可

 <where>   <if test="id!=null">    id=#{id}   </if>     <if test="lastName!=null and lastName!=''">      or last_name like #{lastName}   </if>    <if test="gender==0 or gender==1">      and gender=#{gender}   </if>    <if test="email!=null and email.trim()!=''">      and email=#{email}   </if>   </where>

set标签

1. 和where用法差不多，在执行更新语句时，可能会多余一个逗号。
2. 用于替代set关键字，用法如下

    <set>     <if test="lastName!=null">        last_Name=#{lastName},     </if>     <if test="email!=null">        email=#{email},     </if>     <if test="gender!=null">         gender=#{gender}     </if>    </set>

trim标签

<trim prefix="set" suffixOverrides=","></trim>

1. 在语句前插入prefix指明的前缀，suffixOverrides 是对末尾指明的多余的符号清除。
2. 可以用来代替set标签和where标签

<trim prefix="WHERE" prefixOverrides="and"></trim>

choose 标签

<update id="updateEmp"  >        update employee         <set>        <choose>            <when test="lastName!=null">                   last_Name=#{lastName},                   </when>            <when test="email!=null">                   email=#{email},                  </when>            <when test="gender!=null">                    gender=#{gender},                   </when>            <otherwise>                gender=1                </otherwise>        </choose>        </set>      where id=#{id}    </update>

1. 类似于elseif 或者switch case语句
2. 一旦一个匹配上，其余的便不再执行。

foreach标签

1. collection 指定要遍历的集合，list类型的参数会特殊处理封装在map中，key就叫list
2. item 将当前遍历出的元素赋值给指定的变量
3. separator 每个元素之间的分隔符
4. open 在遍历出所有的结果之后拼接一个开始的字符串
5. close 遍历出所有的结果后拼接一个结束的字符
6. index 索引 遍历list 的时候是索引，遍历map的时候是map的key，item即为当前项的值。

用来批量查询

查询语句：select * from employee where id in (1,2,3);

  <select id="getByIterator" resultType="emp">  select * from employee where id in  <foreach collection="map" item="item" open="(" close=")" separator=",">   #{item}  </foreach>  </select>

用来批量插入

1. 第一种，利用values
2. 第二种，同一行执行多条sql插入语句。需要在连接参数上进行配置。

jdbc.url=jdbc:mysql://localhost:3306/mybatis?allowMultiQueries=true&&useUnicode=true&&characterEncoding=utf8

 第一种：<insert id="BatchInsert" >    insert into employee (last_name,email,gender)Values    <foreach collection="list" item="item" separator=",">      (#{item.lastName},#{item.email},#{item.gender})    </foreach>        </insert>

第二种<insert id="BatchInsert2" >   <foreach collection="list" item="item" separator=";">    insert into employee (last_name,email,gender)Values      (#{item.lastName},#{item.email},#{item.gender})    </foreach>        </insert>

oracle环境下多条记录插入

1. 在begin 和 end 间写入多条插入语句
2. 利用中间表

sql语句begin    insert into employee (last_name,email,gender)    Values ("hou","[e-mail]",1);    insert into employee (last_name,email,gender)    Values ("hou","[e-mail]",1);end;    

foreach代码<insert id="BatchInsert2" >   <foreach collection="list" item="item" open="begin" close="end">    insert into employee (id,last_name,email,gender)Values      (employee_seq.nextval,#{item.lastName},#{item.email},#{item.gender})    </foreach>        </insert>

insert into employee (id,last_name,gender,email) select employeeseq.nextval,last_name,gender,email from(select  "[e-mail]" email,"hou" last_name,0 gender from dual unionselect  "[e-mail]" email,"hou" last_name,0 gender from dual )

foreach语句<insert id="BatchInsert2" >insert into employee (id,last_name,gender,email) select employee_seq.nextval,last_name,gender,email from( <foreach collection="list" item="item" separator="union">    select #{item.lastName} last_name,#{item.email} email,#{item.gender} gender </foreach>      </insert>