MyBatis3动态SQL

1. MyBatis3动态SQL

1. if 条件

2. choose，when和otherwise条件

3. where条件
1) 自动加上where；
2) 如果where子句以and或者or开头，则自动删除第一个and或者or；

4. trim条件
功能和where元素类似，提供了前缀，后缀功能，更加灵活；
prefix="where"：表示加入前缀where；
prefixOverrides="and|or"：表示自动覆盖第一个"and"或者"or"；

5. foreach循环

6. set条件
1) 自动加上set；
2) 自动剔除最后一个逗号"，"；

2. 动态SQL实例

jdbc.driverClassName=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/db_mybatis
jdbc.username=root
jdbc.password=root

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <properties resource="jdbc.properties"/>
    <typeAliases>
        <package name="com.andrew.model"/>
    </typeAliases>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC" />
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc.driverClassName}" />
                <property name="url" value="${jdbc.url}" />
                <property name="username" value="${jdbc.username}" />
                <property name="password" value="${jdbc.password}" />
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <package name="com.andrew.mappers"/>
    </mappers>
</configuration>

package com.andrew.util;
import java.io.InputStream;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
public class SqlSessionFactoryUtil {
    private static SqlSessionFactory sqlSessionFactory;
    public static SqlSessionFactory getSqlSessionFactory() {
        if(sqlSessionFactory==null){
            InputStream inputStream=null;
            try {
        inputStream=Resources.getResourceAsStream("mybatis-config.xml");
        sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
            } catch(Exception e) {
                e.printStackTrace();
            }
        }
        return sqlSessionFactory;
    }
    public static SqlSession openSession() {
        return getSqlSessionFactory().openSession();
    }
}

package com.andrew.model;
public class Student {
    private Integer id;
    private String name;
    private Integer age;
    public Student() {
        super();
    }
    public Student(String name, Integer age) {
        super();
        this.name = name;
        this.age = age;
    }
    public Student(Integer id, String name, Integer age) {
        super();
        this.id = id;
        this.name = name;
        this.age = age;
    }
    @Override
    public String toString() {
        return "Student [id=" + id + ", name=" + name + ", age=" + age + "]";
    }
    // getter and setter
}

package com.andrew.mappers;
import java.util.List;
import java.util.Map;
import com.andrew.model.Student;
public interface StudentMapper {
    public List<Student> searchStudents(Map<String,Object> map);
    public List<Student> searchStudents2(Map<String,Object> map);
    public List<Student> searchStudents3(Map<String,Object> map);
    public List<Student> searchStudents4(Map<String,Object> map);
    public List<Student> searchStudents5(Map<String,Object> map);
    public int updateStudent(Student student);
}

<?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.andrew.mappers.StudentMapper">
    <resultMap type="Student" id="StudentResult">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <result property="age" column="age"/>
    </resultMap>
    <select id="searchStudents" parameterType="Map" resultMap="StudentResult">
        select * from t_student 
        where gradeId = #{gradeId}
        <if test="name!=null">
            and name like #{name}
        </if>
        <if test="age!=nulll">
            and age = #{age}
        </if>
    </select>
    <select id="searchStudents2" parameterType="Map" resultMap="StudentResult">
        select * from t_student 
        <choose>
            <when test="searchBy=='gradeId'">
                 where gradeId = #{gradeId}
             </when>
             <when test="searchBy=='name'">
                 where name like #{name}
             </when>
             <otherwise>
                 where age = #{age}
             </otherwise>
        </choose>
    </select>
    <select id="searchStudents3" parameterType="Map" resultMap="StudentResult">
        select * from t_student 
        <where>
            <if test="gradeId!=null">
                gradeId = #{gradeId}
            </if>
            <if test="name!=null">
                and name like #{name}
            </if>
            <if test="age!=nulll">
                and age = #{age}
            </if>
        </where>
    </select>
    <select id="searchStudents4" parameterType="Map" resultMap="StudentResult">
        select * from t_student 
        <trim prefix="where" prefixOverrides="and|or">
            <if test="gradeId!=null">
                gradeId = #{gradeId}
            </if>
            <if test="name!=null">
                 and name like #{name}
            </if>
            <if test="age!=nulll">
                 and age = #{age}
            </if>
        </trim>
    </select>
    <select id="searchStudents5" parameterType="Map" resultMap="StudentResult">
        select * from t_student 
        <if test="gradeIds!=null">
            <where>
                 gradeId in 
                 <foreach item="gradeId" collection="gradeIds" open="(" separator="," close=")">
                     #{gradeId}
                 </foreach>
             </where>
        </if>
    </select>
    <update id="updateStudent" parameterType="Student">
        update t_student
        <set>
            <if test="name!=null">
                name=#{name},
            </if>
            <if test="age!=null">
                age=#{age},
            </if>
        </set>
        where id=#{id}
    </update>
</mapper>

package com.andrew.service;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.ibatis.session.SqlSession;
import org.apache.log4j.Logger;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import com.andrew.mappers.StudentMapper;
import com.andrew.model.Student;
import com.andrew.util.SqlSessionFactoryUtil;
public class StudentJunitTest {
    private static Logger logger = Logger.getLogger(StudentJunitTest.class);
    private SqlSession sqlSession = null;
    private StudentMapper studentMapper = null;
    @Before
    public void setUp() throws Exception {
        sqlSession = SqlSessionFactoryUtil.openSession();
        studentMapper = sqlSession.getMapper(StudentMapper.class);
    }
    @After
    public void tearDown() throws Exception {
        sqlSession.close();
    }
    @Test
    public void testSearchStudents1() {
        Map<String, Object> map = new HashMap<String, Object>();
        map.put("gradeId", 2);
        // map.put("name", "%3%");
        // map.put("age", 23);
        List<Student> studentList = studentMapper.searchStudents(map);
        for (Student student : studentList) {
            System.out.println(student);
        }
    }
    @Test
    public void testSearchStudents2() {
        Map<String, Object> map = new HashMap<String, Object>();
        map.put("searchBy", "gradeId");
        map.put("gradeId", 2);
        map.put("name", "%6%");
        map.put("age", 22);
        List<Student> studentList = studentMapper.searchStudents2(map);
        for (Student student : studentList) {
            System.out.println(student);
        }
    }
    @Test
    public void testSearchStudents3() {
        Map<String, Object> map = new HashMap<String, Object>();
        map.put("gradeId", 2);
        map.put("name", "%李%");
        map.put("age", 11);
        List<Student> studentList = studentMapper.searchStudents3(map);
        for (Student student : studentList) {
            System.out.println(student);
        }
    }
    @Test
    public void testSearchStudents4() {
        Map<String, Object> map = new HashMap<String, Object>();
        // map.put("gradeId", 2);
        // map.put("name", "%五%");
        map.put("age", 22);
        List<Student> studentList = studentMapper.searchStudents4(map);
        for (Student student : studentList) {
            System.out.println(student);
        }
    }
    @Test
    public void testSearchStudents5() {
        Map<String, Object> map = new HashMap<String, Object>();
        List<Integer> gradeIds = new ArrayList<Integer>();
        // gradeIds.add(1);
        gradeIds.add(2);
        map.put("gradeIds", gradeIds);
        List<Student> studentList = studentMapper.searchStudents5(map);
        for (Student student : studentList) {
            System.out.println(student);
        }
    }
    @Test
    public void testUpdateStudent() {
        Student student = new Student();
        student.setId(1);
        student.setName("张三2");
        student.setAge(12);
        studentMapper.updateStudent(student);
        sqlSession.commit();
    }
}