mybatis详解三：动态sql的使用

一：创建mybatisConfig.xml文件和User.java（参考mybatis详解二）

二：创建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">
<!--namespace:值为对应的UserMapper.java的全路径名-->
<mapper namespace="mybatis1.mapper.UserMapper">
    <!--
        id:是对应接口的方法名getUserById
        parameterType：为getUserById方法的入参类型
        resultType：为getUserById方法的返回值类型
    -->
    <!--这么写有一个弊端，那就是username为空将会报错，因为username为空后sql语句是这样的：
        select　* fomr user where and sex = #{sex}
        那么我们如何在username为空的情形下将and去掉，让SQL语句变成这样
        select　* fomr user where sex = #{sex}
        这需要使用<where></where>标签，他相当于where关键字，作用是去掉第一个and或者or关键字
    -->
    <select id="getUserByCondition" parameterType="User" resultType="User">
        select * from `user` WHERE
        /*如果username为空他就不会被拼接到sql语句里*/
        <if test = "username != null  and username !=''">
            username = #{username}
        </if>

        <if test = "sex != null">
            AND sex = #{sex}
        </if>
    </select>

    <!--已达重复使用的目的-->
    <sql id="attributes">
        id, username, sex, address,birthday
    </sql>

    <select id="getUserByCondition1" parameterType="User" resultType="User">
        /*引用sql片段2*/
        select <include refid="attributes"/>
        from `user`
        /*where会去掉第一个前and 和 or。建议以后的where字段都替换成<where></where>biaoqian*/
        <where>
            <if test = "username != null  and username !=''">
               AND username = #{username}
            </if>

            <if test = "sex != null">
                AND sex = #{sex}
            </if>
        </where>

    </select>

    <update id="updateUser" parameterType="User">
        UPDATE `user`
        /*会去掉最后一个逗号*/
        <set>
            <if test = "username != null  and username !=''">
                username = #{username},
            </if>

            <if test = "sex != null">
                 sex = #{sex},
            </if>
        </set>
        <where>
            id = #{id}
        </where>
    </update>

    <!--
        如果id不为空：sql语句为select * from user where id = ?
        如果id为空 username不为空  sql语句为 select * from where usernaem =?
        如果id为空 username为空  SQL语句就为 select *　from where sex = ?

    -->
    <select id="getUserByCondition2" parameterType="User" resultType="User">
        SELECT <include refid="attributes"/>
        FROM `user`
        <where>
            <choose>
                <when test = "id !=null">
                    id =#{id}
                </when>
                <when test=" username !=null">
                   username = #{username}
                </when>
                <otherwise>
                    sex = #{sex}
                </otherwise>
            </choose>
        </where>
    </select>

    <select id="getUsersByIds" parameterType="UserVo" resultType="User">
        SELECT <include refid="attributes"/>
        FROM `user`
        <where>
            /*
            collection:指定输入对象中的集合属性
            item:每次遍历生成的对象
            open:开始遍历时的拼接字符串
            close:结束时拼接的字符串
            separator:遍历对象之间需要拼接的字符串
            select * from user where 1=1 and id in (1,2,3)*/
            <foreach collection="ids" item="id" separator="," open="id in(" close=")">
                #{id}
            </foreach>
        </where>
    </select>
</mapper>

三：创建UserMapper.java文件

package mybatis1.mapper;

import mybatis1.pojo.User;
import mybatis1.pojo.UserVo;

import java.util.List;

/**
 * Created by user on 2019/1/25.
 */
public interface UserMapper {
    public User getUserByCondition(User user);
    //获取用户信息通过条件
    public User getUserByCondition1(User user);
    //获取用户信息更加条件
    public User getUserByCondition2(User user);
    //获取多个用户
    public List<User> getUsersByIds(UserVo userVo);
}

四：创建测试用例

package mybatis1;
import mybatis1.mapper.UserMapper;
import mybatis1.pojo.User;
import mybatis1.pojo.UserVo;
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.Before;
import org.junit.Test;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
public class TestDemo {
    private SqlSession sqlSession = null;

    @Before
    public void init() throws IOException {
        //创建SqlSessionFactoryBuilder对象
        SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
        //读取mybatisConfig.xml配置文件
        InputStream in = Resources.getResourceAsStream("mybatisConfig.xml");
        //根据配置文件sqlSessionFactoryBuilder创建SqlSessionFactory对象
        SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(in);
        //创建一个与数据会话的对象
        sqlSession = sqlSessionFactory.openSession();
    }

    //获取用户通过条件
    @Test
    public void selectUserByCondition(){
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        User user = new User();
        user.setUsername("ccf");
        user.setSex(1);
        user = userMapper.getUserByCondition(user);
        System.out.println(user);
    }

    //更新用户
    @Test
    public void updateUser(){
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        User user = new User();
        user.setId("1");
        user.setUsername("zhangsan");
        user.setSex(2);
        //更新用户
        userMapper.updateUser(user);
        sqlSession.commit();
    }

    @Test
    //更加条件获取用户
    public void getUserByCondition2(){
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        User user = new User();
        //user.setId("1");
        //user.setUsername("zhangsan");
        user.setSex(2);
        user = userMapper.getUserByCondition2(user);
        System.out.println(user);
    }


    //获取多个用户
    @Test
    public void getUsersByIds(){
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        List<String> idsList = new ArrayList<>();
        idsList.add("1");
        idsList.add("2");
        UserVo userVo = new UserVo();
        userVo.setIds(idsList);
        List<User> userList = userMapper.getUsersByIds(userVo);
        System.out.println(userList);
    }
}

五：总结

1:使用动态标签可以解决jdbc 的sql语句拼接问题。