- 1.教师Dao
package com.edu.dao;
import java.util.List;
import com.edu.bean.Teacher;
public interface TeacherDao {
public Teacher geTeacherById(Integer id);
public List<Teacher> getTeachersLike(Teacher teacher);
/**
* birth\name\course\id=1
* @param teacher
* @return
*/
public List<Teacher> chooseTeachersLike(Teacher teacher);
public int updateTeacher(Teacher teacher);
public List<Teacher> getTeachersByIdIn(@Param("ids")List<Integer> ids);
}
- 2.Dao xml配置
a.若子句的开头为 “AND” 或 “OR”,where 元素也会将它们去除
b.不想使用所有的条件,而只是想从多个条件中选择一个使用,此时选择choose,经测3.5.0版本choose中不能加and
c.set 元素可以用于动态包含需要更新的列,忽略其它不更新的列
<?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.edu.dao.TeacherDao">
<select id="getTeachersLike" resultType="com.edu.bean.Teacher">
select * from t_teachers
<where>
<if test="birth!=null">
and #{birth} > birth
</if>
<if test="name!=null and name!=''">
and name like #{name}
</if>
<if test="course!=null and course!=''">
and course like #{course}
</if>
</where>
</select>
<select id="chooseTeachersLike" resultType="com.edu.bean.Teacher">
select * from t_teachers where
<choose>
<when test="birth!=null">
#{birth}>birth
</when>
<when test="name!=null and name!=''">
name like #{name}
</when>
<when test="course!=null and course!=''">
course like #{course}
</when>
<otherwise>
id = 1
</otherwise>
</choose>
</select>
<update id="updateTeacher">
update t_teachers
<set>
<if test="name!=null and name!=''">name=#{name},</if>
<if test="course!=null and course!=''">course=#{course},</if>
<if test="birth!=null">birth=#{birth},</if>
</set>
where id=#{id}
</update>
<select id="getTeachersByIdIn" resultType="com.edu.bean.Teacher">
select * from t_teachers where id in
<!-- collection 遍历指定集合的key,
seperator 每次遍历元素的分隔符
item 每个遍历出来的元素的变量名
open 自动拼接集合时,以什么开始
close 自动拼接集合时,以什么结束
index 遍历索引,如果是List,那么index就是角标整数
如果是Map,那么index就是key,item就是对应的value
-->
<foreach collection="ids" item="id_item" separator="," open="(" close=")">
#{id_item}
</foreach>
</select>
</mapper>
- 3.测试代码
@Test
public void TestDynamicSQL1() {
SqlSession sqlSession = factory.openSession();
TeacherDao teacherDao = sqlSession.getMapper(TeacherDao.class);
Teacher teacher = new Teacher(null, "%r%", "%c%", new Date());
List<Teacher> teachersLike = teacherDao.getTeachersLike(teacher);
teachersLike.forEach(System.out::println);
}
//在前面的choose语句中name 的优先级更高,所以course不会被设置
@Test
public void TestDynamicSQL2() {
SqlSession sqlSession = factory.openSession();
TeacherDao teacherDao = sqlSession.getMapper(TeacherDao.class);
Teacher teacher = new Teacher();
teacher.setName("%er%");
teacher.setCourse("%j%");
List<Teacher> teachersLike = teacherDao.chooseTeachersLike(teacher);
teachersLike.forEach(System.out::println);
}
//course设置为了空串儿,无法通过sql的条件判断,所以不会被设置
@Test
public void TestDynamicSQL3() {
SqlSession sqlSession = factory.openSession();
TeacherDao teacherDao = sqlSession.getMapper(TeacherDao.class);
Teacher teacher = new Teacher();
teacher.setId(3);
teacher.setName("Merr");
teacher.setCourse("");
teacherDao.updateTeacher(teacher);
sqlSession.commit();
sqlSession.close();
}
4.bind经常用于模糊查询
...
<bind name="coursePattern" value="'%'+course+'%'"/>
<if test="name !=null and name!=''">
and course like #{coursePattern}
</if>
...
5.sql抽取可重用的sql语句
<sql id="selectSql">
select * from t_teachers where
</sql>
<select id ="getTeacherById" resultMap="teacherMap">
<include refid="selectSql"></include>
</select>
6.补充:
OGNL表达式中两个隐含的变量:
_parameter:
1)如果传入的是单个参数,那么_parameter就代表这个参数
2)如果传入的是多个参数,那么_parameter就代表这些参数组合起来的Map
_databaseId:
需要提前在mybatis全局xml环境中设置数据库厂商的名字和ID
<databaseIdProvider type="DB_VENDOR">
<property name="MySQL" value="mysql"/>
<property name="Oracle" value="orcl"/>
</databaseIdProvider>
使用:
<if test ="_databaseId='mysql'" >
巴拉巴拉
</if>
<if test ="_databaseId='orcl" >
巴拉巴拉
</if>