Mybatis(四)----动态SQL

UserInfo.java

package bean;

import java.util.List;

public class UserInfo {
    private Integer userId;
    private String userName;
    private String userSex;
    private Integer userAge;
    private String userAddress;
    private String userPass;
    private Integer userState;

    private List<String> userList;

    public UserInfo(){

    }

    public void setUserList(List<String> userList){
        this.userList = userList;
    }

    public List<String> getUserList(){
        return userList;
    }

    @Override
    public String toString() {
        return "UserInfo{" +
                "userId=" + userId +
                ", userName='" + userName + '\'' +
                ", userSex='" + userSex + '\'' +
                ", userAge=" + userAge +
                ", userAddress='" + userAddress + '\'' +
                ", userPass='" + userPass + '\'' +
                ", userState=" + userState +
                '}';
    }

    public void setUserId(Integer userId) {
        this.userId = userId;
    }

    public void setUserName(String userName) {
        this.userName = userName;
    }

    public void setUserSex(String userSex) {
        this.userSex = userSex;
    }

    public void setUserAge(Integer userAge) {
        this.userAge = userAge;
    }

    public void setUserAddress(String userAddress) {
        this.userAddress = userAddress;
    }

    public void setUserPass(String userPass) {
        this.userPass = userPass;
    }

    public void setUserState(Integer userState) {
        this.userState = userState;
    }

    public Integer getUserId() {
        return userId;
    }

    public String getUserName() {
        return userName;
    }

    public String getUserSex() {
        return userSex;
    }

    public Integer getUserAge() {
        return userAge;
    }

    public String getUserAddress() {
        return userAddress;
    }

    public String getUserPass() {
        return userPass;
    }

    public Integer getUserState() {
        return userState;
    }
}

UserMapper.java

package mapper;

import bean.UserInfo;

import java.util.List;

public interface UserMapper {
    List<UserInfo> findUserBySex(String userSex);
    List<UserInfo> findUserByTwoCondiction(UserInfo userInfo);
    List<UserInfo> findUserForeach(UserInfo userInfo);
    void updateUser(UserInfo ui);
}

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="mapper.UserMapper">

    <select id="findUserBySex" parameterType="java.lang.String" resultType="bean.UserInfo">
        select * from userinfo
        <!--条件不成立就不拼接if包裹的语句-->
        <if test="userSex!=null">
            where usersex = #{userSex}
        </if>
    </select>

    <select id="findUserByTwoCondiction" parameterType="bean.UserInfo" resultType="bean.UserInfo">
        select * from userinfo
        <where><!--两个条件都不满足，则不执行，有任何一个条件成立的话，则会自动拼接上wehre并且去除掉and-->
            <!--where也可以替换成trim  <trim prefix="where" prefixOverrides="and|or"> 有一个条件成立，则自动增加前缀where并且自动忽略and或or-->
            <if test="userSex!=null">
                and usersex = #{userSex}
            </if>
            <if test="userAge!=null">
                and userage > #{userAge}
            </if>
        </where>
    </select>

    <select id="findUserForeach" parameterType="bean.UserInfo" resultType="bean.UserInfo">
        select * from userinfo
        <trim prefix="where" prefixOverrides="and|or">
            <if test="userList!=null">
                userAddress in
                <!--依次为：被迭代的集合，被迭代的对象，以什么开头，分隔符，以什么结尾，索引号（map集合中的key）-->
                <foreach collection="userList" item="s" open="(" close=")" separator="," index="index">
                    #{s}
                </foreach>
            </if>
        </trim>
    </select>

    <update id="updateUser" parameterType="bean.UserInfo">
        update userinfo
        <trim prefix="set" suffixOverrides=",">
            <if test="userName!=null">
                username = #{userName},
            </if>
            <if test="userSex!=null">
                usersex = #{userSex},
            </if>
        </trim>
        where userId = #{userId}
    </update>
</mapper>

UserMapper.test

package test;

import bean.UserInfo;
import mapper.UserMapper;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

public class UserMapperTest {
    public static void main(String[] args) {
        InputStream is = UserMapperTest.class.getClassLoader().getResourceAsStream("config.xml");
        SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(is);
        SqlSession session = ssf.openSession();
        UserMapper mapper = session.getMapper(UserMapper.class);

        //测试findUserBySex()方法
        /*List<UserInfo> list = mapper.findUserBySex("男");//只打印男人，if条件体执行
        List<UserInfo> list2 = mapper.findUserBySex(null);//全部信息打印，if条件体没执行
        for (UserInfo userInfo:list) {
            System.out.println(userInfo);
        }*/

        //测试findUserByTwoCondiction()方法
        /*UserInfo userInfo = new UserInfo();
        List<UserInfo> list = mapper.findUserByTwoCondiction(userInfo);//打印出所有的信息

        //userInfo.setUserSex("男");//只打印出男人信息
        //list = mapper.findUserByTwoCondiction(userInfo);
        //userInfo.setUserAge(61);//只打印出61岁往上的男人信息
        //list = mapper.findUserByTwoCondiction(userInfo);

        userInfo.setUserAge(50);//打印出年龄在50岁以上的所有人
        list = mapper.findUserByTwoCondiction(userInfo);
        for (UserInfo ui:list) {
            System.out.println(ui);
        }*/

        //测试findUserForeach()方法
        /*UserInfo userInfo = new UserInfo();
        List<String> list = new ArrayList<>();
        list.add("西安");
        list.add("北京");
        list.add("上海");
        userInfo.setUserList(list);
        List<UserInfo> uiList = mapper.findUserForeach(userInfo);
        for (UserInfo ui:uiList) {
            System.out.println(ui);
        }*/

        //测试updateUser()方法
        UserInfo ui = new UserInfo();
        ui.setUserId(73);
        ui.setUserName("哈哈");
        ui.setUserSex("男");
        mapper.updateUser(ui);
        session.commit();
    }
}