JavaEE MyBatis之动态SQL(教材学习笔记)
动态SQL是MyBatis的强大之处,下面一一学习
一、if语句
在MyBatis中<if>元素时最常用到的判断元素,它类似于Java中的if
1.创建chapter08项目,并将chapter06(chaoter06点这里)中的项目文件复制过来
2.修改映射文件CustomerMapper.xml,并使用if元素进行查询
<?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.itheima.mapper.CustomerMapper">
<select id="findCustomerByNameAndJobs" parameterType="Integer" resultType="com.itheima.po.Customer">
select * from t_customer where 1=1
<if test="username!=null and username !='' ">
and username like concat('%',#{username},'%')
</if>
<if test="jobs!=null and jobs!='' ">
and jobs #{jobs}
</if>
</select>
</mapper>
3.在测试类MybatisTest中编写测试方法findCustomerByNameAndJobsTest(),如下所示:
package com.itheima.test;
import java.util.List;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import com.itheima.po.Customer;
import com.itheima.utils.MybatisUtils;
public class MybatisTest {
@Test
public void findCustomerByNameAndJobsTest() {
SqlSession session = MybatisUtils.getSession();
Customer customer = new Customer();
customer.setUsername("jack");
customer.setJobs("teacher");
//执行sqlsession的查询方法,返回结果集
List<Customer> customers = session.selectList("com.itheima.mapper."
+"CustomerMapper.findCustomerByNameAndJobs",customer);
//打印结果
for(Customer customer2 : customers) {
System.out.println(customer2);
}
//关闭SqlSession
session.close();
}
}
4.查看运行结果
二、<choose> 、<when>、<otherwise>元素
这三个元素其实和java当中的switch.......case.......default作用差不多,
1.在映射文件中使用这三种元素执行动态SQL
<select id="findCustomerByNameOrJobs" parameterType="Integer" resultType="com.itheima.po.Customer">
select * from t_customer where 1=1
<choose>
<when test="username!=null and username !='' ">
and username like concat('%',#{username},'%')
</when>
<when test="jobs!=null and jobs!='' ">
and jobs=#{jobs}
</when>
<otherwise>
and phone is not null
</otherwise>
</choose>
</select>
2.编写测试方法
@Test
public void findCustomerByNameOrJobsTest() {
SqlSession session = MybatisUtils.getSession();
Customer customer = new Customer();
customer.setUsername("jack");
customer.setJobs("teacher");
//执行sqlsession的查询方法,返回结果集
List<Customer> customers = session.selectList("com.itheima.mapper."
+"CustomerMapper.findCustomerByNameOrJobs",customer);
//打印结果
for(Customer customer2 : customers) {
System.out.println(customer2);
}
//关闭SqlSession
session.close();
}
3.执行,查看结果
三、<set>元素
set元素用于更新操作,主要作用是在动态包含的sql语句前面加一个set关键字,并将sql语句中最后一个,逗号去掉
1.在映射文件中使用<set>元素使用动态sql
<update id="updateCustomer" parameterType="com.itheima.po.Customer">
update t_customer
<set>
<if test="username!=null and username !='' ">
username=#{username},
</if>
<if test="jobs!=null and jobs!='' ">
jobs=#{jobs},
</if>
<if test="phone!=null and phone!='' ">
phone=#{phone},
</if>
</set>
where id=#{id}
</update>
2.编写测试方法
@Test
public void updateCustomerTest() {
SqlSession session = MybatisUtils.getSession();
Customer customer = new Customer();
customer.setId(3);
customer.setPhone("13111122223");
//执行sqlsession的查询方法,返回结果集
int rows = session.update("com.itheima.mapper."
+"CustomerMapper.updateCustomer",customer);
if(rows>0) {
System.out.println("恭喜您插入了"+rows+"条数据");
}else {
System.out.println("插入失败了");
}
//提交事务
session.commit();
//关闭SqlSession
session.close();
}
3.执行测试方法并查看结果
四、<foreach>元素
foreach用于数组和集合循环遍历。
主要用于构建IN条件语句时使用
1.在映射文件中使用<foreach>建立基于in条件的查询
<select id="findCustomerByIds" parameterType="List" resultType="com.itheima.po.Customer">
select * from t_customer where id in
<foreach item="id" index="index" collection="list" open="(" separator="," close=")">
#{id}
</foreach>
</select>
2.编写测试代码
@Test
public void findCustomerByIdsTest() {
SqlSession session = MybatisUtils.getSession();
List<Integer> ids=new ArrayList<Integer>();
ids.add(1);
ids.add(2);
//执行sqlsession的查询方法,返回结果集
List<Customer> customers = session.selectList("com.itheima.mapper."
+"CustomerMapper.findCustomerByIds",ids);
//打印结果
for(Customer customer2 : customers) {
System.out.println(customer2);
}
//关闭SqlSession
session.close();
}
3.执行测试方法并查看结果