13mybatis动态SQL

动态语句 if
BookMapper.java 映射接口
public interface BookMapper {

public List<Book> query();
public List<Book> query(Map<String,Object> map);
}

BookMapper.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="com.fz.mapper.BookMapper">
<resultMap id="bookmap" type="book">
<id column="book_id" property="id"/>
<result property="name" column="book_name"/>
<result property="price" column="book_price"/>
</resultMap>

<select id="query" resultMap="bookmap">
select * from book where 1=1
<if test="name!=null">
and book_name like #{name}
</if>
<if test="price!=null">
and book_price = #{price}
</if>
</select>
</mapper>

null和“”

test = "price != null"
test = ‘price == ""‘
if test="#{}"条件可以随便写
把<if test="takeWay == ‘1‘ and workday != null "> 错误

改为<if test=‘takeWay == "1" and workday != null ‘> 正确
或改为<if test="takeWay == ‘1‘.toString() and workday != null ">即可。 正确

test = ‘"java".equals(name)‘

原因是：mybatis是用OGNL表达式来解析的，在OGNL的表达式中，’1’会被解析成字符，java是强类型的，char 和 一个string 会导致不等，所以if标签中的sql不会被解析。
总结下使用方法：单个的字符要写到双引号里面或者使用.toString()才行！

select * from student where name = ‘张三‘;
select * from student where name like ‘%张三%‘;

<if test=‘name!=null and name.indexOf("java")!=-1‘>
where book_name like #{name}
</if>

<select id="query" statementType="PREPARED" parameterType="map" resultMap="mp">
select * from book where 1=1
<if test=‘"《java开发》".equals(name)‘>
and book_name = #{name}
</if>
<if test=‘name!=null and name.indexOf("java")!=-1‘>
and book_name like #{name}
</if>
</select>

程序代码
Map<String,Object> m = new HashMap<String,Object>();
m.put("name","%java%");
List<Book> bks = bdao.query(m);
System.out.println(bks.size());
for(Book b : bks){
System.out.println(b.getName());
}
bks = bdao.query();
System.out.println(bks.size());
for(Book b : bks){
System.out.println(b.getName());
}

m.put("price",28);
//m.remove("name");
bks = bdao.query(m);
System.out.println(bks.size());
for(Book b : bks){
System.out.println(b.getName());
}

Map<String,Object> m = new HashMap<String,Object>();
map.put("name","%李%");
map.put("age",18);

Map<String,Object> m = new HashMap<String,Object>();
m.put("name","赵六");
//m.put("age",18);
List<Student> sts = sm.search(m);
System.out.println(sts.size());

动态语句 choose, when, otherwise
相当于 java switch 语句
更像
if(){
}elseif()
}else{
}elseif(){
}else{
}
choose when otherwise 条件

<select id="" parameterType="Map" resultType="student">
select * from student
<choose>
<when test="name==‘李四‘">
where name = #{name}
</when>
<when test="age==18">
where age>18
</when>
<otherwise>
where 1=1
</otherwise>

</choose>

</select>

<select id="query" statementType="PREPARED" parameterType="map" resultMap="mp">
select * from book where 1=1
<choose>
<when test="name==‘php‘">
and book_name like #{name}
</when>

<when test="price>20">
and book_price>=20
</when>
<otherwise>
and book_price>50
</otherwise>
</choose>
</select>

where 条件 自动添加where 条件
<select id="" parameterType="Map" resultType="student">
select * from student
<where>
<if test="">
age=18
</if>
<if test="">
and name = #{name}
</if>
</where>

</select>

动态SQL语句 trim, where, set

trim条件 自动加where 自动取掉and 或 or关键字

where
<select id="query" statementType="PREPARED" parameterType="map" resultMap="mp">
select * from book
<where>
<if test="name!=null">
or book_name=#{name}
</if>
<if test="price!=null">
or book_price > #{price}
</if>
</where>

</select>

set 会自动去掉，
<update id="update" parameterType="map">
update book
<set>
<if test="name!=null">book_name=#{name},</if>
<if test="price!=null">book_price=#{price},</if>
</set>
<where>
<if test="id!=null">
and book_id = #{id}
</if>
<if test="name!=null">
and book_name = #{name}
</if>
</where>
</update>

trim
<trim prefix="SET" suffixOverrides=","></trim> 相当于<set>
<set>

</set>

<trim prefix="where" prefixOverrides="and|or"></trim> 相当于<where>
<where>

</where>

<update id="update" parameterType="map">
update book

<trim prefix="set" suffixOverrides=",">
<if test="name!=null">book_name=#{name},</if>
<if test="price!=null">book_price=#{price},</if>
</trim>

<trim prefix="where" prefixOverrides="and|or">
<if test="id!=null">
and book_id = #{id}
</if>
<if test="name!=null">
and book_name = #{name}
</if>
</trim>
</update>

<select id="" parameterType="Map" resultType="student">
select * from student
<trim>
<if test="">
age=18
</if>
<if test="">
and name = #{name}
</if>
</trim>

</select>

动态SQL语句 foreach

foreach循环
<select>
select * from db_student
<if test="ids!=null">
<where>
id in
<foreach item="id" collection="ids" separator="," open="(" close=")">
#{id}
</foreach>
</where>
</if>
</select>

<select id="search" parameterType="Map" resultType="student">
select * from db_student
<if test="ids!=null">
<where>
id in
<foreach item="id" collection="ids" separator="," open="(" close=")">
#{id}
</foreach>
</where>
</if>
</select>

<select id="queryin" statementType="PREPARED" parameterType="map" resultMap="mp">
select * from book
<where>
<if test="ids!=null">
book_id in
<foreach item="abc" collection="ids" open="(" separator="," close=")">
#{abc}
</foreach>
</if>
</where>
</select>

<delete id="delete" statementType="PREPARED" parameterType="map">
delete from book
<where>
<if test="ids!=null">
and book_id in
<foreach item="abc" collection="ids" open="(" separator="," close=")">
#{abc}
</foreach>
</if>
<if test="id!=null">
and book_id = #{id}
</if>
</where>
</delete>

Map<String,Object> m = new HashMap<String,Object>();
List<Integer> list = new ArrayList<Integer>();
list.add(3);
list.add(5);
list.add(6);
m.put("ids",list);
m.put("ids",new int[]{3,4});
List<Student> sts = sm.search(m);
System.out.println(sts.size());

set 条件 用于修改语句
自动添加set 关键字，自动去除最后的逗号 ,
<update id="" parameterType="student">
update db_student
<set>
<if test="name!=null">
name=#{name},
</if>
<if test="age!=null">
age=#{age},
</if>

</set>
where id=#{id}
</update>

13mybatis动态SQL

标签：move   添加   integer   表达式   sel   column   app   开发   equals