Mybatis的动态sql使用

动态SQL的<if>


动态SQLd的<foreach>
foreach标签的属性含义如下：
<foreach>标签用于遍历集合，它的属性：
•collection：代表要遍历的集合元素，注意编写时不要写#{}
•open：代表语句的开始部分
•close：代表结束部分
•item：代表遍历集合的每个元素，生成的变量名
•sperator：代表分隔符


SQL片段抽取
<sql id="selectUser" select * from User</sql>

UserMapper.java

public interface UserMapper {

    List<User> findAll();

    User findById(int id);

    List<User> findUser(User user);

    List<User> findByIds(List<Integer> ids);
}

UserMapper.xml

<mapper namespace="com.code_g.dao.UserMapper">

<!--    抽取sql语句-->
    <sql id="selectUser">
        select * from user
    </sql>

<!--    动态if语句-->
    <select id="findAll" resultType="User">
        select * from user
    </select>

    <select id="findById" resultType="User" parameterType="int">
        select * from user where id = #{id}
    </select>

    <select id="findUser" parameterType="User" resultType="User">
        select * from user
        <where>
            <if test="id!=0">
                id = #{id}
            </if>
            <if test="username != null">
                and username=#{username}
            </if>
            <if test="password != null">
                and password=#{password}
            </if>
        </where>
    </select>


<!--    动态foreach语句-->
    <select id="findByIds" parameterType="list" resultType="User">
        <include refid="selectUser"/>
        <where>
            <foreach collection="list" open="id in (" close=")" item="id" separator=",">
                #{id}
            </foreach>
        </where>
    </select>


</mapper>

测试方法

    @Test
    //动态sql的if语句
    public void test6() throws IOException {
        InputStream resource = Resources.getResourceAsStream("SqlMapConfig.xml");
        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(resource);
        SqlSession sqlSession = sessionFactory.openSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        User user = new User();
        user.setId(1);
//        user.setUsername("zhangsan");
//        user.setPassword("123");
        List<User> users = mapper.findUser(user);
        System.out.println(users);
    }

    @Test
    //动态foreach语句
    public void test7() throws IOException {
        InputStream resource = Resources.getResourceAsStream("SqlMapConfig.xml");
        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(resource);
        SqlSession sqlSession = sessionFactory.openSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        List<Integer> list = new ArrayList<>();
        list.add(1);
        list.add(3);
        List<User> users = mapper.findByIds(list);
        System.out.println(users);
    }