动态SQL
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();
}
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">
<select id="getEmpsByConditionIf" resultType="com.mybatis.bean.Employee">
select * from tbl_employee
<where>
<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>
<select id="getEmpsByConditionTrim" resultType="com.mybatis.bean.Employee">
select * from tbl_employee
<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>
<select id="getEmpsByConditionChoose" resultType="com.mybatis.bean.Employee">
select * from tbl_employee
<where>
<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>
<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}
</update>
<select id="getEmpsByConditionForeach" resultType="com.mybatis.bean.Employee">
select * from tbl_employee
<foreach collection="ids" item="item_id" separator=","
open="where id in(" close=")">
#{item_id}
</foreach>
</select>
<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>
</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);
}
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();
}
}
}
