MyBatis 动态Sql
动态Sql
通过MyBatis提供的各种标签方法实现动态拼接Sql。
这些标签类似于JSTL标签,可以写控制语句动态的拼接Sql。
where-if标签
用于查找语句。
如果输入的时候添加了属性,if标签生效;如果if标签里的内容是添加的第一个会自动把其中的and去掉。
@Test
public void testIfWhere(){
SqlSession sqlSession = MyBatisUtil.getSqlSession();
Student student = new Student();
student.setName("张");
student.setAge(23);
student.setGender("女");
List<Student> list = sqlSession.selectList("student.selectByCondition",student);
for (Student student1 : list) {
System.out.println(student1);
}
}
<select id="selectByCondition" parameterType="Student" resultMap="studentMap">
select
<include refid="studentColumn"/>
from
student
<where>
<if test="name!=null and name!='' ">
and name like concat('%',#{name},'%')
</if>
<if test="age!=null">
and age=#{age}
</if>
<if test="gender!=null and gender!='' ">
and gender=#{gender}
</if>
</where>
</select>
set-if标签
用于更新语句。
与whwere-if相似,如果输入的时候添加了属性,if标签生效;如果if标签里的内容是添加的最后一个会自动把后面的“,”去掉。
<select id="updateByCondition" parameterType="Student">
update
student
<set>
<if test="name != null and name != ''">
name = #{name},
</if>
<if test="age != null">
age = #{age},
</if>
<if test="gender != null and gender != ''">
gender = #{gender},
</if>
</set>
where
id = #{id}
</select>
@Test
public void testUpdateByCondition(){
SqlSession sqlSession = MyBatisUtil.getSqlSession();
Student student = new Student();
student.setId(31);
student.setName("白七1");
student.setAge(33);
student.setGender("nan");
int count = sqlSession.update("student.updateByCondition",student);
System.out.println(count);
sqlSession.commit();
}
foreach标签
处理数组或者集合,MyBatis使用foreach标签解析。
<delete id="deleteAllByArray">
DELETE FROM `student`
WHERE id in
<foreach collection="array" open="(" close=")" item="id" separator=",">
#{id}
</foreach>
</delete>
<delete id="deleteAllByList">
DELETE FROM `student`
WHERE id in
<foreach collection="list" open="(" close=")" item="id" separator=",">
#{id}
</foreach>
</delete>
@Test
public void testDeleteAllByArray() {
int[] array = {66};
SqlSession sqlSession = MyBatisUtil.getSqlSession();;
int count = sqlSession.update("student.deleteAllByArray", array);
System.out.println("count: " + count);
sqlSession.commit();
sqlSession.close();
}
@Test
public void testDeleteAllByList() {
List<Integer> list = new ArrayList<Integer>();
list.add(68);
list.add(69);
SqlSession sqlSession = MyBatisUtil.getSqlSession();;
int count = sqlSession.update("student.deleteAllByList", list);
System.out.println("count: " + count);
sqlSession.commit();
sqlSession.close();
}
choose、when、otherwise
相当于 if----else if----else if ----else
如果用户填写了名字,这个搜索就按照名字来搜索,你即使填写了别的条件也不去拼接,
如果没有填写名字,填写了年龄,就按照年龄来搜索,
如果名字和年龄都没有填写,但是填写了性别就按照性别来查找。
name、age、gender搜索时候只能按照其中一个来搜索,但是优先级name>age>gender。
<select id="selectByCondition" parameterType="Student" resultMap="studentMap">
SELECT
<include refid="studentColumns"/>
FROM
`student`
WHERE
<choose>
<when test="name!=null and name!=''">
name LIKE concat('%', #{name}, '%')
</when>
<when test="age!=null">
age=#{age}
</when>
<when test="gender!=null and gender!=''">
gender=#{gender}
</when>
<otherwise>
</otherwise>
</choose>
</select>
@Test
public void testSelectByCondition() {
Student student = new Student();
student.setName("王");
student.setAge(23);
student.setGender("女");
List<Student> list = studentMapper.selectByCondition(student);
for (Student s : list) {
System.out.println(s);
}
}