动态SQL是mybatis非常重要强大的功能之一,我们自己写sql,想按照传过来的不同的条件变换不同的SQL,我们自己拼SQL的时候要注意,符号是不是多了或少了之类的问题,或者复杂查询包含各种各样的判断,比较麻烦。
mybatis中提供了动态SQL标签写在SQL语句中,mybatis利用这些标签提供强大的动态SQL功能。
1.if标签,用来判断参数值是否为空,test属性值符合OGNL判断表达式
<select id="getEmpsByConditionIf" resultType="com.atguigu.mybatis.bean.Employee">
select * from tbl_employee
<where>
<!--参数为String--!>
<if test="id!=null">
id=#{id}
</if>
<!-- ognl会进行字符串与数字的转换判断 "0"==0 -->
<if test="gender==0 or gender==1">
and gender=#{gender}
</if>
<!--参数为pojo--!>
<if test="employee.lastName == null or employee.lastName ='' ">
and employee.lastName=#{lastname}
</if>
</where>
</select>
2.where标签,查询条件,主要作用是简化SQL语句中where条件判断的书写
<select id="getEmpsByconditionIf" resultType="com.atguigu.mybatis.bean.Employee">
select * from tbl_employee
<where>
<if test="id!=null">
id=#{id}
</if>
<if test="lastName!=null && lastName!=""">
and last_name like #{lastName}
</if>
<if test="email!=null and email.trim()!=''">
and email=#{email}
</if>
<!-- ognl会进行字符串与数字的转换判断 "0"==0 -->
<if test="gender==0 or gender==1">
and gender=#{gender}
</if>
</where>
</select>
如果id为空,就判断lastName,如果where标签里的if全不成立,则不走where语句。
如果id为空,其他值不为空,打印出的SQL为
select * fromtbl_employee WHERE last_name like ? and email=? and gender=?
where标签会自动将第一个条件后的and或者or忽略
3.trim标签自定义字符串截取。trim标签中是整个字符串拼串后的结果
trim的4个属性:
prefix="":前缀:prefix给拼串后的整个字符串加一个前缀
prefixOverrides="":前缀覆盖:去掉整个字符串前边多余的字符
suffix="":后缀:给拼串后的整个字符串加一个后缀
suffixOverrides="":后缀覆盖:去掉整个字符串后边多余的字符
<select id="getEmpsByConditionTrim" resultType="com.atguigu.mybatis.bean.Employee">
select * from tbl_employee
<!-- 后面多出的and或者or where标签不能解决 -->
<!-- 自定义字符串的截取规则 -->
<trim prefix="where" suffixOverrides="and">
<if test="id!=null">
id=#{id} and
</if>
<if test="lastName!=null && lastName!=""">
last_name like #{lastName} and
</if>
<if test="email!=null and email.trim()!=''">
email=#{email} and
</if>
<!-- ognl会进行字符串与数字的转换判断 "0"==0 -->
<if test="gender==0 or gender==1">
gender=#{gender}
</if>
</trim>
</select>
这样打印出来就会把条件后的and去掉
控制台打印结果:select * from tbl_employee where last_namelike ? and email=? and gender=?
4.choose标签分支选择,从多个选项中选择一个。
choose标签按顺序判断内部when标签中的test条件是否成立,有一个成立,则choose结束。
<select id="getEmpsByConditionChoose" resultType="com.atguigu.mybatis.bean.Employee">
select * from tbl_employee
<where>
<!-- 如果带了id就用id查,如果带了lastName就用lastName查;只会进入其中一个 -->
<choose>
<when test="id!=null">
id = #{id}
</when>
<when test="lastName!=null">
last_Name like #{lastName}
</when>
<when test="email!=null">
email = #{email}
</when>
<otherwise>
gender = 0
</otherwise>
</choose>
</where>
</select>
测试方法:
public SqlSessionFactory getSqlSesssionFactory() throws IOException {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
return new SqlSessionFactoryBuilder().build(inputStream);
}
public void test07() throws IOException{
SqlSessionFactory sqlSesssionFactory = getSqlSesssionFactory();
SqlSession openSession = sqlSesssionFactory.openSession();
try {
EmployeeMapperDynamicSQL mapper = openSession.getMapper(EmployeeMapperDynamicSQL.class);
Employee employee = new Employee(null,"letty","123@163.com","0");
//测试choose
List<Employee> getEmpsByConditionChoose= mapper.getEmpsByConditionChoose(employee);
for (Employee emp : getEmpsByConditionChoose) {
System.out.println(emp);
}
openSession.commit();
} finally {
openSession.close();
}
}
控制台打印信息:
5.set标签,与if结合的动态更新
<update id="updateEmp">
update tbl_employee
<!-- 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>
where id=#{id}
</update>
set标签会自动把最后一个条件赋值语句后的逗号去掉,也就是gender=#{gender},后的逗号
控制台打印信息:
6.foreach标签遍历集合
foreach标签属性:
collection:指定要遍历的集合:
list类型的参数会特殊处理封装在map中,map的key就叫list
item:将当前遍历出的元素赋值给指定的变量
separator:元素之间的分隔符
open:遍历出所有结果拼接成一个开始的字符
close:遍历出所有结果拼接一个结束的字符
index:索引,遍历list的时候是索引
遍历map的时候index表示的就map的key,item就的map的值
#{变量名}就能取出变量的值也就是当前遍历出的元素
<select id="getEmpsByConditionForeach" resultType="com.atguigu.mybatis.bean.Employee">
select * from tbl_employee
<foreach collection="ids" item="item_id" separator="," open="where id in(" close=")">
#{item_id}
</foreach>
</select>
测试方法:
@Test
public void test07() throws IOException{
SqlSessionFactory sqlSesssionFactory = getSqlSesssionFactory();
SqlSession openSession = sqlSesssionFactory.openSession();
try {
EmployeeMapperDynamicSQL mapper = openSession.getMapper(EmployeeMapperDynamicSQL.class);
List<Employee> empsByConditionForeach=mapper.getEmpsByConditionForeach(Arrays.asList(1,2));
for (Employee emp : empsByConditionForeach) {
System.out.println(emp);
}
} finally {
openSession.close();
}
}
控制台信息:
7.foreach标签,mysql下foreach批量插入的两种方式
法1:
<!-- Mysql下批量保存,可以foreach遍历,mysql支持values(),(),()语法 -->
<insert id="addEmps">
INSERT INTO tbl_employee (
last_name,
email,
gender,
d_id
)
VALUES
<foreach collection="emps" item="emp" separator=",">
(#{emp.lastName},#{emp.email},#{emp.gender},#{emp.dept.id})
</foreach>
</insert>
法2:
<!-- 这种方式需要数据库连接属性allowMultiQueries=true;
这种分隔多个SQL可以用于其他的批量操作(删除,修改) -->
<insert id="addEmps">
<foreach collection="emps" item="emp" separator=";">
INSERT INTO tbl_employee (
last_name,
email,
gender,
d_id
)VALUES(#{emp.lastName},#{emp.email},#{emp.gender},#{emp.dept.id})
</foreach>
</insert>
测试方法:
@Test
public void testBatchSave() throws IOException {
SqlSessionFactory sqlSesssionFactory = getSqlSesssionFactory();
SqlSession openSession = sqlSesssionFactory.openSession();
try {
EmployeeMapperDynamicSQL mapper = openSession.getMapper(EmployeeMapperDynamicSQL.class);
List<Employee> emps =new ArrayList<>();
emps.add(new Employee(null,"zazh1","smith@atguigu.com","1",new Department(1)));
emps.add(new Employee(null,"zzen1","allen@atguigu.com","1",new Department(2)));
mapper.addEmps(emps);
openSession.commit();
} finally {
// TODO: handle finally clause
}
}
控制台信息:
INSERT INTOtbl_employee ( last_name, email, gender, d_id )VALUES(?,?,?,?) ; INSERT INTOtbl_employee ( last_name, email, gender, d_id )VALUES(?,?,?,?);