mybatis之动态SQL

DongTaiMapper.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">

<!--动态sql举例-->
<mapper namespace="DongTai.UserDaoDT">

    <!-- if标签:只要test中的表达式为true，就会执行if标签中的条件。 -->
    <!-- where标签：自动判断是否需要加上"where"关键字-->
    <select id="selectByIf" parameterType="bean.User" resultMap="userList">
        select * from user
        <where>
            <if test="name != null">
                name LIKE '${name}%'
            </if>
        </where>
    </select>

    <!--choose标签:多个选项中选择一个。-->
    <select id="selectByChoose" parameterType="bean.User" resultMap="userList">
        select * from user
        <where>
            <choose>
                <when test="name != null">
                    name LIKE '${name}%'
                </when>
                <when test="age != null">
                    age LIKE '${age}%'
                </when>
                <otherwise>
                    1 = 1
                </otherwise>
            </choose>
        </where>
    </select>

    <!--trim标签：替换关键字-->
    <select id="selectByTrim" parameterType="bean.User" resultMap="userList">
        select * from user
        <trim prefix="WHERE" prefixOverrides="AND | OR">
            <if test="name != null">
                AND name LIKE '${name}%'
            </if>
            <if test="age != null">
                AND age LIKE '${age}%'
            </if>
        </trim>
    </select>

    <!-- where标签：自动判断是否需要加上"where"关键字和去掉"and"关键字-->
    <!-- select * from user name LIKE '${name}%' AND age LIKE '${age}%'-->
    <select id="selectByWhere" parameterType="bean.User" resultMap="userList">
        select * from user
        <where>
            <if test="name != null">
                name LIKE '${name}%'
            </if>
            <if test="age != null">
                AND age LIKE '${age}%'
            </if>
        </where>
    </select>

    <!--foreach标签：处理传参为List或数组的情况-->
    <!--SELECT * FROM user WHERE id IN (?,?,?...)-->
    <select id="selectByList" resultMap="userList">
        SELECT * FROM user WHERE id IN
        <foreach collection="list" open="(" separator="," close=")" item="item_id">
            #{item_id}
        </foreach>
    </select>

    <!--传参为数组-->
    <select id="selectByArray" resultMap="userList">
        SELECT * FROM user WHERE id IN
        <foreach collection="array" open="(" separator="," close=")" item="item_id">
            #{item_id}
        </foreach>
    </select>


    <!--批量返回数据类型定义-->
    <resultMap type="bean.User" id="userList">
        <!--property:对象属性; column:表字段名-->
        <result property="id" column="id"/>
        <result property="name" column="name"/>
        <result property="age" column="age"/>
    </resultMap>



</mapper>

TestDongTai.java

package DongTai;

import bean.User;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.*;

import java.io.IOException;
import java.io.Reader;
import java.util.ArrayList;
import java.util.List;

/*
* 使用mybatis举例，使用动态SQL举例
* */
public class TestDongTai {
    String resource = "mybatis-config-dongtai.xml";
    SqlSessionFactory sqlSessionFactory = null;
    SqlSession session = null;

//    if标签:只要test中的表达式为true，就会执行if标签中的条件
    @Test
    public void testSelectByIf() {
//        接口自动实例化
        UserDaoDT userDao = session.getMapper(UserDaoDT.class);
        User user = new User();
        user.setName("Tom");
        user.setAge("25");
        System.out.println("查询对象：" + user);
//        执行sql
        List listUser = userDao.selectByIf(user);
        System.out.println(listUser);
    }

//    choose标签:多个选项中选择一个
    @Test
    public void testSelectByChoose() {
//        接口自动实例化
        UserDaoDT userDao = session.getMapper(UserDaoDT.class);
        User user = new User();
//        user.setName("Tom");
        user.setAge("2");
        System.out.println("查询对象：" + user);
//        执行sql
        List listUser = userDao.selectByChoose(user);
        System.out.println(listUser);
    }

//    trim标签：替换关键字
    @Test
    public void testsSelectByTrim() {
//        接口自动实例化
        UserDaoDT userDao = session.getMapper(UserDaoDT.class);
        User user = new User();
//        user.setName("Tom");
        user.setAge("2");
        System.out.println("查询对象：" + user);
//        执行sql
        List listUser = userDao.selectByTrim(user);
        System.out.println(listUser);
    }

//    where标签：自动判断是否需要加上"where"关键字和去掉"and"关键字
    @Test
    public void testSelectByWhere() {
//        接口自动实例化
        UserDaoDT userDao = session.getMapper(UserDaoDT.class);
        User user = new User();
        user.setName("Tom");
        user.setAge("25");
        System.out.println("查询对象：" + user);
//        执行sql
        List listUser = userDao.selectByWhere(user);
        System.out.println(listUser);
    }

//    foreach标签：处理传参为List的情况
    @Test
    public void testSelectByList() {
//        接口自动实例化
        UserDaoDT userDao = session.getMapper(UserDaoDT.class);
        List idList = new ArrayList();
        idList.add(1);
        idList.add(2);
        idList.add(3);
        System.out.println("查询List：" + idList);
//        执行sql
        List listUser = userDao.selectByList(idList);
        System.out.println(listUser);
    }

//    foreach标签：处理传参为数组的情况
    @Test
    public void testSelectByArray() {
//        接口自动实例化
        UserDaoDT userDao = session.getMapper(UserDaoDT.class);
        Integer[] ids = {1, 2, 3};
        System.out.println("查询数组：" + ids);
//        执行sql
        List listUser = userDao.selectByArray(ids);
        System.out.println(listUser);
    }


    @Before
    public void before() {
//        System.out.println("Before");
        try {
            Reader read = Resources.getResourceAsReader(resource);
//            创建工厂
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(read);
//            创建session对象
            session = sqlSessionFactory.openSession();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    @After
    public void close() {
        session.close();
//        System.out.println("After");
    }

}

UserDaoDT.java

package DongTai;

import bean.User;

import java.util.List;


/*
* Mybatis使用动态SQL举例
* */
public interface UserDaoDT {

    public List<User> selectByIf(User user);

    public List<User> selectByChoose(User user);

    public List<User> selectByWhere(User user);

    public List<User> selectByTrim(User user);

    public List<User> selectByList(List list);

    public List<User> selectByArray(Integer[] ids);

}