31.MyBatis映射文件的深入

1.动态sql之if

userDao:

public interface UserDao2
{
    public List<User> findByCondition(User user);
}

userMapper2.xml:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="Dao.UserDao2">

    <!--查询-->
    <select id="findByCondition" resultType="user" parameterType="user">
        select * from user
        <where>
            <if test="id!=0"> /*如果user参数中的id不为0,那就加上and id=#{id}这个条件*/
                and id=#{id}
            </if>
            <if test="username!=null"> /*如果user参数中的username不为空,那就加上and username=#{username}这个条件*/
                and username=#{username}
            </if>
            <if test="password!=null">
                and password=#{password}
            </if>
        </where>
    </select>

</mapper>

测试：

    @Test
    public void test2() throws IOException
    {
        InputStream inputStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession = sqlSessionFactory.openSession(true);
        UserDao2 mapper = sqlSession.getMapper(UserDao2.class);

        User user = new User();
        //user.setId(1);
        //user.setUsername("张三");
        List<User> userList = mapper.findByCondition(user);
        System.out.println(userList);
    }

2.动态sql之foreach

 userDao:

public interface UserDao2
{
    public List<User> findByIds(List<Integer> ids);
}

userMapper:

    <select id="findByIds" parameterType="list" resultType="entity.User">
        /*select * from user where id in(1,2,3)*/
        select * from user
        <where>
            /*item负责接收list的每一个值,open到closed对应id in()，里面的1,2,3对应item,separator为逗号代表以逗号为分隔。
              foreach内部的值就是 传递过来的参数list 的每个项的值，对应item="id"的id
            */
            <foreach collection="list" open="id in(" close=")" item="id" separator=",">
                #{id}
            </foreach>
        </where>
    </select>

测试:

    @Test
    public void test3() throws IOException
    {
        InputStream inputStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession = sqlSessionFactory.openSession(true);
        UserDao2 mapper = sqlSession.getMapper(UserDao2.class);

        List<Integer> ids = new ArrayList<Integer>();
        ids.add(1);
        ids.add(3);
        List<User> userList = mapper.findByIds(ids);
        System.out.println(userList);
    }

sql片段抽取 ：

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="Dao.UserDao2">

    <!--sql语句的抽取-->
    <sql id="selectUser">select * from user</sql>
    <!--查询-->
    <select id="findByCondition" resultType="user" parameterType="user">
        <include refid="selectUser"></include>
        <where>
            <if test="id!=0"> /*如果user参数中的id不为0,那就加上and id=#{id}这个条件*/
                and id=#{id}
            </if>
            <if test="username!=null"> /*如果user参数中的username不为空,那就加上and username=#{username}这个条件*/
                and username=#{username}
            </if>
            <if test="password!=null">
                and password=#{password}
            </if>
        </where>
    </select>

    <select id="findByIds" parameterType="list" resultType="entity.User">
        /*select * from user where id in(1,2,3)*/
        <include refid="selectUser"></include>
        <where>
            /*item负责接收list的每一个值,open到closed对应id in()，里面的1,2,3对应item,separator为逗号代表以逗号为分隔。
              foreach内部的值就是 传递过来的参数list 的每个项的值，对应item="id"的id
            */
            <foreach collection="list" open="id in(" close=")" item="id" separator=",">
                #{id}
            </foreach>
        </where>
    </select>

</mapper>

import Dao.UserDao;
import Dao.UserDao2;
import entity.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.Test;

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

public class UserMapperTest
{

    @Test
    public void findByCondition() throws IOException
    {
        InputStream inputStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession = sqlSessionFactory.openSession(true);
        UserDao2 mapper = sqlSession.getMapper(UserDao2.class);

        User user = new User();
        //user.setId(1);
        //user.setUsername("张三");
        List<User> userList = mapper.findByCondition(user);
        System.out.println(userList);
    }

    @Test
    public void findByIds() throws IOException
    {
        InputStream inputStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession = sqlSessionFactory.openSession(true);
        UserDao2 mapper = sqlSession.getMapper(UserDao2.class);

        List<Integer> ids = new ArrayList<Integer>();
        ids.add(1);
        ids.add(3);
        List<User> userList = mapper.findByIds(ids);
        System.out.println(userList);
    }
}