动态SQL

动态SQL ，即通过Mybatis提供的各种标签对条件做出判断以实现动态拼接SQL语句。这里的条件判断使用的是OGNL表达式。 常用的动态SQL标签有<if><where> <choose/> <foreach>等。

1.Dao层实现

public interface IStudentDao {
    /**
     * 通过学生id查找相应学生信息
     * @param id 学生id
     * @return 学习实体
     */
    Student selectStudentById(int id);
    /***
     * 插入学生数据
     * @param student 学生实体
     */
    void insertStudent(Student student);
    /***
     * 通过map 封装多个查询条件
     * @param map  map
     * @return 实体数组.....
     */
    List<Student> selectStudentsByMap(Map<String, Object> map);
    /****
     * 动态查询SQL If 形式
     * @param student 学生实体
     * @return 实体数组
     */
    List<Student> selectStudentIf(Student student);
    /***
     * 动态查询SQL Where 形式
     * @param student 学生实体
     * @return 实体数组
     */
    List<Student> selectStudentWhere(Student student);
    /***
     * 动态查询 SQL choose 类似于 switch case  若有第一个条件
     * 则添加1 无则添加2
     * @param student
     * @return
     */
    List<Student> selectStudentChoose(Student student);
    /***
     * 动态查询SQL  foreach 查询数组中符合条件的.....
     * @param studentIds 学生id数组
     * @return 学生实体数组....
     */
    List<Student> selectStudentForeachArray(Object[]studentIds);
    /***
     * 动态SQL 
     * @param list 和上一样....
     * @return
     */
    List<Student> selectStudentForeachList(List<Student> list);
}

2.配置文件 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 -->
<mapper namespace="com.evecom.common.IStudentDao">



    <select id="selectStudentById" parameterType="int" resultType="Student">

        select id,name,score,hobby from student where id = #{id}

    </select>

    <insert id="insertStudent">
        insert into student(id,name,score,hobby)
        values(#{id},#{name},#{score},#{hobby})
    </insert>

    <select id="selectStudentsByMap" resultType="Student">

        select id,name,score,hobby from student where name like '%' #{name} '%' and
        score > #{score}

    </select>

    <select id="selectStudentIf" resultType="Student">

        select * from student where 1=1
        <if test="name != null and name != ''">
            and name like '%' #{name} '%'
        </if>
        <if test="score > 0 ">
            and score > #{score}
        </if>
    </select>

    <select id="selectStudentWhere" resultType="Student">
        select * from student
        <where>

            <if test="name != null and name != ''">
                name like '%' #{name} '%'
            </if>
            <if test="score > 0">
                and score > #{score}
            </if>

        </where>

    </select>

    <select id="selectStudentChoose" resultType="Student">
        select * from student
        <where>
            <choose>
                <when test="name != null and name != ''">
                    name like '%' #{name} '%'
                </when>
                <when test="hobby != null and hobby != ''">
                    and hobby like '%' #{hobby} '%'
                </when>
                <otherwise>
                    and 1 != 1
                </otherwise>
            </choose>
        </where>
    </select>

    <select id="selectStudentForeachArray" resultType="Student">
        select * from student
        <if test="array != null and array.length > 0">
            where id in
            <foreach collection="array" open="(" close=")" item="myid"
                separator=",">
                #{myid}
            </foreach>
        </if>
    </select>

    <select id="selectStudentForeachList" resultType="Student">
        select * from student
        <if test="list != null and list.size > 0">
            where id in
            <foreach collection="list" open="(" close=")" item="stu"
                separator=",">
                #{stu.id}
            </foreach>
        </if>

    </select>

</mapper>

3.写测试用例。

public class MybatisTest {
    private IStudentDao dao;
    private SqlSession session;
    Logger log = Logger.getLogger(log4jDemo.class);

    @Before
    public void setup() {
        session = MyBatisUtil.getSqlSession();
        dao = session.getMapper(IStudentDao.class);
    }

    @After
    public void tearDown() {
        if (session != null) {
            session.close();
        }
    }


    /*
    @Test
    public void test02() {
        Map<String, Object> map = new HashMap<String, Object>();
        map.put("name", "三");
        map.put("score", 88.0);
        map.put("hobby", "球");

        List<Student> students = dao.selectStudentsByMap(map);

        for (Student student : students) {
            System.out.println(student);
        }

    }
    */

    /*
    @Test
    public void test03() {
        Student student = new Student();
        student.setName("三");
        student.setScore(89.0);
        List<Student> list = dao.selectStudentIf(student);
        for (Student student2 : list) {
            System.out.println(student2);
        }
    }
    */
    /*
    @Test
    public void test04() {
        Student student = new Student();
        student.setName("三");
        student.setScore(89.0);
        List<Student>list = dao.selectStudentWhere(student);
        for (Student student2 : list) {
            System.out.println(student2);
        }
    }
    */
    /*
    @Test
    public void test05() {
        Student student = new Student();
//      student.setName("三");
        student.setHobby("球");
        List<Student> students = dao.selectStudentChoose(student);
        for (Student student2 : students) {
            System.out.println(student2);
        }
    }
    */
    /*
    @Test
    public void test06() {
        Object[] studentIds = new Object[] {3,11};
        List<Student> students = dao.selectStudentForeachArray(studentIds);
        for (Student student : students) {
            System.out.println(student);
        }
    }
    */
    @Test
    public void test07() {
        List<Student> list = new ArrayList<Student>();
        Student student = new Student();
        Student student2 = new Student();
//      list.add(3);
//      list.add(11);
        student.setId(3);
        student2.setId(11);
        list.add(student);
        list.add(student2);
        List<Student> students = dao.selectStudentForeachList(list);
        for (Student student3 : students) {
            System.out.println(student3);
        }
    }
}