2021-04-04

mybatis动态sql

Mybatis-where-set

<select id="getBlog" parameterType="map" resultType="Blog">
        select * from javawork.blog
        <where>
      <if test="title != null">
            and title=#{title}
        </if>
        <if test="author != null">
            and author=#{author}
        </if>
        </where>
    </select>

Mybatis-where-where-choose

 <where>
           <choose>
               <when test="title != null">
                   and title=#{title}
               </when>
               <when test="author != null">
                   and author=#{author}

               </when>
                <otherwise>
                    and views=#{views}
                </otherwise>
           </choose>
       </where>

sql片段

<sql id="if-title-author">
        <if test="title != null">
            and title=#{title}
        </if>
        <if test="author != null">
            and author=#{author}
        </if>
    </sql>

在include 引用（sql片段不应该包含where）

		<where>
        <include refid="if-title-author"></include>
        </where>

foreach的简单使用

 <select id="getBlog3" resultType="blog" parameterType="map">
        select * from javawork.blog
        <where>
            <foreach collection="ids" item="id" open="and (" separator="or" close=")">
                id=#{id}
            </foreach>
        </where>
    </select>

查询

@Test
    public void getBlog3(){
        SqlSession sqlSession = mybatisUtil.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        Map map=new HashMap();
        List<Integer> list=new ArrayList();
        list.add(1);
        map.put("ids",list);
        List<Blog> blog3 = mapper.getBlog3(map);
        for (Blog blog : blog3) {
            System.out.println(blog);
        }
    }

总结：
动态sql就是在拼接sql语句，我们只要保证sql的正确性，按照sql的格式去排列组合就可以了。
建议：
·现在MySQL中写出完整的sql，再去对应的修改成为我们的动态sql实现通用即可。

学习多看官方文档，技术提升一点不难。