动态SQL
<!--
• if:判断
• choose (when, otherwise):分支选择;带了break的swtich-case
如果带了id就用id查,如果带了lastName就用lastName查;只会进入其中一个
• trim 字符串截取(where(封装查询条件), set(封装修改条件))
• foreach 遍历集合
-->
bean:
public class Department {
private Integer id;
private String departmentName;
private List<Employee> emps;
public Department(Integer id) {
super();
this.id = id;
}
public Department() {
super();
// TODO Auto-generated constructor stub
}
public List<Employee> getEmps() {
return emps;
}
public void setEmps(List<Employee> emps) {
this.emps = emps;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getDepartmentName() {
return departmentName;
}
public void setDepartmentName(String departmentName) {
this.departmentName = departmentName;
}
@Override
public String toString() {
return "Department [id=" + id + ", departmentName=" + departmentName + "]";
}
}
public class Employee {
private Integer id;
private String last_name;
private String email;
private String gender;
private Department department;
public Employee(Integer id, String last_name, String email, String gender, Department department) {
super();
this.id = id;
this.last_name = last_name;
this.email = email;
this.gender = gender;
this.department = department;
}
public Department getDepartment() {
return department;
}
public void setDepartment(Department department) {
this.department = department;
}
public Employee(Integer id, String last_name, String email, String gender) {
super();
this.id = id;
this.last_name = last_name;
this.email = email;
this.gender = gender;
}
public Employee(){
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getLast_name() {
return last_name;
}
public void setLast_name(String last_name) {
this.last_name = last_name;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public String getGender() {
return gender;
}
public void setGender(String gender) {
this.gender = gender;
}
@Override
public String toString() {
return "Employee [id=" + id + ", last_name=" + last_name + ", email=" + email + ", gender=" + gender + "]";
}
}
接口 EmployeeMapperDynamicSQL
public interface EmployeeMapperDynamicSQL {
//查询员工,要求,携带了哪个字段查询条件就带上这个字段的值
public List<Employee> getEmpsByConditionIf(Employee employee);
public List<Employee> getEmpsByConditionTrim(Employee employee);
public List<Employee> getEmpsByConditionChoose(Employee employee);
public void updateEmp(Employee employee);
public List<Employee> getEmpsByConditionForeach(@Param("ids")List<Integer> ids);
public void addEmps(@Param("emps")List<Employee> emps);
}
EmployeeMapperDynamicSQL.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.mybatis.dao.EmployeeMapperDynamicSQL">
<!--
• if:判断
• choose (when, otherwise):分支选择;带了break的swtich-case
如果带了id就用id查,如果带了lastName就用lastName查;只会进入其中一个
• trim 字符串截取(where(封装查询条件), set(封装修改条件))
• foreach 遍历集合
-->
<!-- 查询员工,要求,携带了哪个字段查询条件就带上这个字段的值 -->
<!-- public List<Employee> getEmpsByConditionIf(Employee employee); -->
<select id="getEmpsByConditionIf" resultType="com.mybatis.bean.Employee">
select * from tbl_employee
<where>
<!-- test: 判断表达式
从参数中取值判断
-->
<if test="id!=null">
and id = #{id}
</if>
<if test="last_name!=null and last_name !=''">
and last_name like #{last_name}
</if>
<if test="email!=null and email.trim()!=''">
and email = #{email}
</if>
<if test="gender==0 or gender==1">
and gender = #{gender}
</if>
</where>
</select>
<!-- public List<Employee> getEmpsByConditionTrim(Employee employee); -->
<select id="getEmpsByConditionTrim" resultType="com.mybatis.bean.Employee">
select * from tbl_employee
<!-- 后面多出的and或者or where标签不能解决
prefix="":前缀:trim标签体中是整个字符串拼串 后的结果。
prefix给拼串后的整个字符串加一个前缀
prefixOverrides="":
前缀覆盖: 去掉整个字符串前面多余的字符
suffix="":后缀
suffix给拼串后的整个字符串加一个后缀
suffixOverrides=""
后缀覆盖:去掉整个字符串后面多余的字符
-->
<trim prefix="where" suffixOverrides="and">
<if test="id!=null">
id = #{id} and
</if>
<if test="last_name!=null and last_name !=''">
last_name like #{last_name} and
</if>
<if test="email!=null and email.trim()!=''">
email = #{email} and
</if>
<if test="gender==0 or gender==1">
gender = #{gender}
</if>
</trim>
</select>
<!-- public List<Employee> getEmpsByConditionChoose(Employee employee); -->
<select id="getEmpsByConditionChoose" resultType="com.mybatis.bean.Employee">
select * from tbl_employee
<where>
<!-- 如果带了id就用id查,如果带了lastName就用lastName查;只会进入其中一个 -->
<choose>
<when test="id!=null">
id=#{id}
</when>
<when test="last_name!=null">
last_name like #{last_name}
</when>
<when test="email!=null">
email = #{email}
</when>
<otherwise>
gender = 0
</otherwise>
</choose>
</where>
</select>
<!-- public void updateEmp(Employee employee); -->
<update id="updateEmp">
update tbl_employee
<set>
<if test="last_name!=null">
last_name=#{last_name},
</if>
<if test="email!=null">
email=#{email},
</if>
<if test="gender!=null">
gender=#{gender}
</if>
</set>
where id=#{id}
<!--
Trim:更新拼串
update tbl_employee
<trim prefix="set" suffixOverrides=",">
<if test="last_name!=null">
last_name=#{last_name},
</if>
<if test="email!=null">
email=#{email},
</if>
<if test="gender!=null">
gender=#{gender}
</if>
</trim>
where id=#{id} -->
</update>
<!--public List<Employee> getEmpsByConditionForeach(List<Integer> ids); -->
<select id="getEmpsByConditionForeach" resultType="com.mybatis.bean.Employee">
select * from tbl_employee
<!--
collection:指定要遍历的集合:
list类型的参数会特殊处理封装在map中,map的key就叫list
item:将当前遍历出的元素赋值给指定的变量
separator:每个元素之间的分隔符
open:遍历出所有结果拼接一个开始的字符
close:遍历出所有结果拼接一个结束的字符
index:索引。遍历list的时候是index就是索引,item就是当前值
遍历map的时候index表示的就是map的key,item就是map的值
#{变量名}就能取出变量的值也就是当前遍历出的元素
-->
<foreach collection="ids" item="item_id" separator=","
open="where id in(" close=")">
#{item_id}
</foreach>
</select>
<!-- 批量保存 -->
<!-- public void addEmps(@Param("emps")List<Employee> emps); -->
<insert id="addEmps">
insert into tbl_employee(last_name,email,gender,d_id)
values
<foreach collection="emps" item="emp" separator=",">
(#{emp.last_name},#{emp.email},#{emp.gender},#{emp.department.id})
</foreach>
</insert>
<!-- 这种方式需要数据库连接属性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> -->
</mapper>
测试类
public class MybatisTest {
public SqlSessionFactory getSqlSessionFactory() throws IOException {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
return new SqlSessionFactoryBuilder().build(inputStream);
}
@Test
public void testDynamicSQL() throws IOException{
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession openSession = sqlSessionFactory.openSession();
try {
EmployeeMapperDynamicSQL mapper = openSession.getMapper(EmployeeMapperDynamicSQL.class);
Employee employee = new Employee(3,"Admin",null,null);
/*List<Employee> emps = mapper.getEmpsByConditionIf(employee);
for (Employee employee2 : emps) {
System.out.println(employee2);
}*/
//测试 Trim
/*List<Employee> emps2 = mapper.getEmpsByConditionTrim(employee);
for (Employee employee2 : emps2) {
System.out.println(employee2);
}*/
//测试Choose
/*List<Employee> emps3 = mapper.getEmpsByConditionChoose(employee);
for (Employee employee2 : emps3) {
System.out.println(employee2);
}*/
//测试 set 更新
/*mapper.updateEmp(employee);
openSession.commit();*/
/*List<Employee> emp2 = mapper.getEmpsByConditionForeach(Arrays.asList(1,2,3,4));
for (Employee employee2 : emp2) {
System.out.println(employee2);
}*/
} finally {
openSession.close();
}
}
@Test
public void testBatchSave() throws IOException{
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession openSession = sqlSessionFactory.openSession();
try {
EmployeeMapperDynamicSQL mapper = openSession.getMapper(EmployeeMapperDynamicSQL.class);
List<Employee> emps = new ArrayList<Employee>();
emps.add(new Employee(null, "smith", "smith@qq.com", "1",new Department(1)));
emps.add(new Employee(null, "alien", "alien@qq.com", "0",new Department(2)));
emps.add(new Employee(null, "TTT", "TTT@qq.com", "1",new Department(1)));
emps.add(new Employee(null, "hhh", "hhh@qq.com", "1",new Department(1)));
mapper.addEmps(emps);
openSession.commit();
}finally {
openSession.close();
}
}
}