MyBatis动态sql查询

动态SQL

什么是动态sql：

mybatis核心 对sql语句进行灵活操作，通过表达式进行判断，对sql进行灵活拼接、组装。

需求

• 用户信息综合查询列表和用户信息查询列表总数这两个statement的定义使用动态sql。
• 对查询条件进行判断，如果输入参数不为空才进行查询条件拼接。

基础类：

• 数据库持久层类po（和数据表有映射关系）：

public class UserPo {
    private Integer id;
    private String username;
    private Date birthday;
    private String sex;
    private String address;

包装类vo（接受前台的表单数据从视图层传递到任何层）：

public class UserQueryVo {
    private UserPo userPo;

UserMapper.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="cn.domarvel.dao.UserMapper">
    <!--用户信息综合查询 
        #{userPo.username}:取出pojo包装对象中姓名值
        ${userPo.id}:取出pojo包装对象中用户id
    -->

    <select id="findUserByNamesAndId" parameterType="cn.domarvel.vo.UserQueryVo" resultType="cn.domarvel.po.UserPo">
        select * from user
        <!-- 
            where 可以自动去掉条件中的第一个and
        -->
        <where>
            <if test="userPo!=null">
                <if test="userPo.username!=null and userPo.username!=''">
                    and username like #{userPo.username}
                </if>
                <if test="userPo.id!=null">
                    and id=#{userPo.id}
                </if>
            </if>
        </where>
    </select>

    <!--用户信息综合查询总数
        parameterType：指定输入类型和findUserByNamesAndId一样
        resultType：输出结果类型
    -->
    <select id="findUserCount" parameterType="cn.domarvel.vo.UserQueryVo" resultType="_int">
        select count(id) from user
        <where>
            <if test="userPo!=null">
                <if test="userPo.username!=null and userPo.username!=''">
                    and username like #{userPo.username}
                </if>
                <if test="userPo.id!=null">
                    and id=#{userPo.id}
                </if>
            </if>
        </where>
    </select>
</mapper>

UserMapper.java

public interface UserMapper {
    public List<UserPo> findUserByNamesAndId(UserQueryVo userQueryVo) throws Exception;
    public int findUserCount(UserQueryVo userQueryVo) throws Exception;
}

测试代码

public class UserDaoTest {
    private SqlSessionFactory SqlSessionFactory;

    @Before
    public void init(){
        try {
            SqlSessionFactory=new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream("sqlMapConfig.xml"));
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    @Test
    public void findUserByNamesAndId() throws Exception{
        SqlSession sqlSession=SqlSessionFactory.openSession();
        UserMapper userMapper=sqlSession.getMapper(UserMapper.class);

        //创建封装类
        UserQueryVo userQueryVo=new UserQueryVo();

        //创建基础类
        UserPo userPo=new UserPo();
        userPo.setUsername("%小明%");

        userQueryVo.setUserPo(userPo);

        List<UserPo> results=userMapper.findUserByNamesAndId(userQueryVo);

        for(UserPo i:results){
            System.out.println(i);
        }
    }

    @Test
    public void findUserCount() throws Exception{
        SqlSession sqlSession=SqlSessionFactory.openSession();
        UserMapper userMapper=sqlSession.getMapper(UserMapper.class);

        //创建封装类
        UserQueryVo userQueryVo=new UserQueryVo();

        //创建基础类
        UserPo userPo=new UserPo();
        userPo.setUsername("%小明%");

        userQueryVo.setUserPo(userPo);

        int userCount=userMapper.findUserCount(userQueryVo);
        System.out.println(userCount);
    }
}

SQL片段

需求：

• 将上边实现的动态sql判断代码块抽取出来，组成一个sql片段。其它的statement中就可以引用sql片段。
• 方便程序员进行开发。

定义sql片段

在mapper.xml文件中定义

    <!-- 定义sql片段
        id:sql片段的唯一标识

        经验：是基于表单来定义sql片段，这样的话这样sql片段可重用性才高。
        在SQL片段中不要包括where
    -->
    <sql id="query_User_Username_Id">
        <if test="userPo!=null">
            <if test="userPo.username!=null and userPo.username!=''">
                and username like #{userPo.username}
            </if>
            <if test="userPo.id!=null">
                and id=#{userPo.id}
            </if>
        </if>
    </sql>

引用sql片段

在mapper.xml中定义的statement中引用sql片段：

    <!--用户信息综合查询 
        #{userPo.username}:取出pojo包装对象中姓名值
        ${userPo.id}:取出pojo包装对象中用户id
    -->

    <select id="findUserByNamesAndId" parameterType="cn.domarvel.vo.UserQueryVo" resultType="cn.domarvel.po.UserPo">
        select * from user
        <!-- 
            where 可以自动去掉条件中的第一个and
        -->
        <where>
            <!-- 引用sql片段的id，如果refid指定的id不在本mapper文件中，需要前边加namespace -->
            <include refid="query_User_Username_Id"></include>
            <!-- 在这里还可以引用其它的sql片段，因为这里总是要引入一个到多个id，所以这里不能把where写在sql片段中 -->
        </where>
    </select>

foreach

向sql传递数组或List，mybatis使用foreach解析

需求：

在用户查询列表中增加多个id输入查询。

sql语句如下：

两种方式：

select * from user where username like '%小明%' and (id=1 or id=2)

select * from user where username like '%小明%' and id in(1,2)

在输入参数类型中添加List<Integer> ids传入多个id

public class UserQueryVo {
    private UserPo userPo;
    private List<Integer> ids;

修改mapper.xml

    <!-- 定义sql片段
        id:sql片段的唯一标识

        经验：是基于表单来定义sql片段，这样的话这样sql片段可重用性才高。
        在SQL片段中不要包括where
    -->
    <sql id="query_User_Username_Id">
        <if test="userPo!=null">
            <if test="userPo.username!=null and userPo.username!=''">
                and username like #{userPo.username}
            </if>
            <if test="ids!=null">
                <!-- select * from user where username like '%小明%' and (id=1 or id=2) -->
                <foreach collection="ids" item="item_id" open="and (" close=")" separator="or">
                    <!--使用foreach遍历传入ids 
                        collection:指定输入对象中集合属性
                        item:每个遍历生成对象
                        open:开始遍历时拼接的串
                        close:结束遍历时拼接的串
                        separator:遍历的两个对象之间需要拼接的串
                    -->
                    id=#{item_id}<!-- 每次遍历循环的串 -->
                </foreach>
            </if>
        </if>
    </sql>

测试代码

    @Test
    public void findUserByNamesAndId() throws Exception{
        SqlSession sqlSession=SqlSessionFactory.openSession();
        UserMapper userMapper=sqlSession.getMapper(UserMapper.class);

        //创建封装类
        UserQueryVo userQueryVo=new UserQueryVo();

        //创建基础类
        UserPo userPo=new UserPo();
        userPo.setUsername("%小明%");

        userQueryVo.setUserPo(userPo);
        List<Integer> list=new ArrayList<Integer>();

        list.add(1);
        list.add(22);
        list.add(25);

        userQueryVo.setIds(list);

        List<UserPo> results=userMapper.findUserByNamesAndId(userQueryVo);

        for(UserPo i:results){
            System.out.println(i);
        }
    }

另外一个sql的实现：

                <!-- select * from user where username like '%小明%' and id in(1,2) -->
                <foreach collection="ids" item="item_id" open="and id in(" close=")" separator=",">
                    #{item_id}<!-- 每次遍历循环的串 -->
                </foreach>