Mybatis动态sql语句拼接学习

Mybatis中可以使用标签实现sql语句的动态拼接,更加简化我们的使用

1.< if >,< where >标签的使用

• 使用场景:我们根据用户名id和姓名查询,但条件中可能包含id也可能包含name字段,
• 第一步:环境配置
• 第二步:接口方法书写:

List<Person> queryByidAndname(Person person);

• 配置xml映射文件:

<select id="queryByidAndname" resultMap="pMap" parameterType="com.offcn.pojo.Person">
        select * from person
        <where>
            <if test="pid!=null and pid!=0">
              AND  pid=#{pid}
            </if>
            <if test="pname!=null">
                AND pname=#{pname}
            </if>
        </where>
    </select>

• 书写测试代码:

 @Test
    public void testqueryByidAndname() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactory build = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = build.openSession();
        PersonMapper mapper = sqlSession.getMapper(PersonMapper.class);
        Person person = new Person();
        person.setPname("lisi");
        List<Person> people = mapper.queryByidAndname(person);
        people.forEach(a-> System.out.println(a));
        sqlSession.close();

    }

• 总结

标签	作用
< where>	类似where 1=1
< if>	判断查询条件的字段值是否存在,如果没有则不拼接
and +条件	在if标签里面记得要写and,不能省略

2.< choose >标签的使用

• 使用场景:如果有pid则根据pid查询,如果没有pid则根据pname查询,如果都没有则查询全部
• 直接书写配置文件:

 <select id="queryByidAndname1" resultMap="pMap" parameterType="com.offcn.pojo.Person">
        select * from person
        <where>
           <choose>
               <when test="pid!=0">
                   and pid=#{pid}
               </when>
               <when test="pname!=null">
                   and pname=#{pname}
               </when>
               <otherwise>
                   and 1=1
               </otherwise>
           </choose>
        </where>
    </select>

• 测试代码一样,只是这里调用的方法不一样了:

 @Test
    public void testqueryByidAndname1() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactory build = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = build.openSession();
        PersonMapper mapper = sqlSession.getMapper(PersonMapper.class);
        Person person = new Person();
        person.setPid(9);
        person.setPname("lisi");
        List<Person> people = mapper.queryByidAndname1(person);
        people.forEach(a-> System.out.println(a));
        sqlSession.close();

    }

3.< set >标签的使用

• 使用场景:对数据进行更新,如果属性有值就更新,否则不更新.
• 定义接口方法:

void updatePerson(Person person);

• 书写映射文件代码:

<select id="updatePerson" resultMap="pMap" parameterType="com.offcn.pojo.Person">
        update person
        <set>
            <if test="pname!=null">pname=#{pname}</if>
            <if test="adress!=null">adress=#{adress}</if>
        </set>
        where pid=#{pid}
    </select>

• 测试代码:

 @Test
    public void testupdatePerson() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactory build = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = build.openSession();
        PersonMapper mapper = sqlSession.getMapper(PersonMapper.class);
        Person person = new Person();
        person.setPid(9);
        person.setPname("李信");
        mapper.updatePerson(person);
        System.out.println("更新成功");
        sqlSession.commit();
        sqlSession.close();

    }

4.< foreach >标签的使用

• 使用场景:进行sql查询时,可能出现结果是一个集合,所以这里通过foreach标签来解决.
• < foreach>标签属性介绍:

属性名	作用
collection	代表遍历的类型,如果为集合,则值为collection,如果为数组,则值为array
open	代表语句开始部分,一般为:open=" 字段名 in("
close	代表结束部分,一般为:close=")"
item	代表遍历集合的每个元素,生成的变量名
sperator	分隔符

• 书写代码,根据一组pid来查询人物信息:

<select id="queryByintarr" resultMap="pMap" parameterType="list">
        select * from person
        <where>
            <foreach collection="collection" open="pid in (" close=")" item="pid" separator=",">
                #{pid}
            </foreach>
        </where>
    </select>

• 测试代码:

@Test
    public void testqueryByintarr() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactory build = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = build.openSession();
        PersonMapper mapper = sqlSession.getMapper(PersonMapper.class);
        List<Integer> ids=new ArrayList<>();
        ids.add(6);
        ids.add(9);
        List<Person> people = mapper.queryByintarr(ids);
        people.forEach(a-> System.out.println(a));
        sqlSession.close();

    }

5.< trim >标签的使用

• 该标签可以重写set/where标签
• 重写set更新标签:

<select id="updatePerson1" resultMap="pMap" parameterType="com.offcn.pojo.Person">
        update person
        <trim prefix="set" suffixOverrides=",">
            <if test="pname!=null">pname=#{pname},</if>
            <if test="adress!=null">adress=#{adress},</if>
        </trim>
        where pid=#{pid}
    </select>

• 重写where标签:

 <select id="updatePerson2" resultMap="pMap" parameterType="com.offcn.pojo.Person">
        select * from person
        <trim prefix="where" suffixOverrides=" and|or ">
            <if test="pid!=0 and pid!=null">and pname=#{pname}</if>
            <if test="adress!=null"> and adress=#{adress},</if>
        </trim>
    </select>