MyBatis+PostgreSQL+IDEA学习笔记(四)
四、动态SQL
1. 前言
用过JDBC的人都知道,在Java中无尽的做条件判断,然后无尽都拼接字符串,是十分痛苦的。MyBatis提供了一系列的标签,十分高效的解决了这个问题。
• if 判断
• choose (when, otherwise)
• trim 字符串截取(where(封装查询条件), set(封装修改条件))
• foreach 遍历集合
2. if
例:
EmployeeMapperDynamicSQL.java
package mybatis.dao;
import mybatis.bean.Employee;
import java.util.List;
public interface EmployeeMapperDynamicSQL {
//查询员工,要求,携带了哪个字段查询条件就带上这个字段的值
public List<Employee> getEmpsByConditionIf(Employee employee);
}
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="mybatis.dao.EmployeeMapperDynamicSQL">
<!--• if:判断-->
<select id="getEmpsByConditionIf" resultType="mybatis.bean.Employee">
select * from tb1_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>
</mapper>
MyBatisTest.java
@Test
public void test05() throws IOException {
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
//获取的SqlSession对象不会自动提交数据
SqlSession openSession = sqlSessionFactory.openSession();
try {
EmployeeMapperDynamicSQL mapper = openSession.getMapper(EmployeeMapperDynamicSQL.class);
Employee emp1=new Employee(null, "Jerry1", "0", null);
List<Employee> emps = mapper.getEmpsByConditionIf(emp1);
for(Employee emp: emps){
System.out.println(emp);
}
//手动提交
openSession.commit();
} finally {
openSession.close();
}
}
测试结果: