Mybatis的动态sql和分页

Mybatis（2）

前言：Mybatis的动态sql和分页要在完成Mubatis入门搭建下实现

Mybatis动态sql

trim:去空格

<trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="bid != null">
                bid,
            </if>
            <if test="bname != null">
                bname,
            </if>
            <if test="price != null">
                price,
            </if>
 </trim>

if：如果 name 不为空 就进行if体拼接

<if test="bid != null">
                bid,
</if>
<if test="bname != null">
     			bname,
</if>
<if test="price != null">
     			price,
</if>

foreach：遍历集合 批量查询 通常用于in关键字

<select id="selectBooksIn" resultType="com.DZY.model.Book">
      select  * from t_mvc_book where bid in
        <foreach collection="bookIds" item="bid" open="(" close=")" separator=",">
           #{bid}
        </foreach>
  </select>

List<Book> selectBooksIn(@Param("bookIds") List bookIds);

模糊查询

#{...}

${...}

Concat

注：1) mybatis中使用OGNL表达式传递参数
   2) 优先使用#{...}
   3) #{...}自带引号，${...}有sql注入的风险

BookMapper

    List<Book> selectBooksLike1(@Param("bname")String bname);
    List<Book> selectBooksLike2(@Param("bname")String bname);
    List<Book> selectBooksLike3(@Param("bname")String bname);

BookMapper.xml

 <select id="selectBooksLike1" resultType="com.DZY.model.Book" parameterType="java.lang.String">
      select * from t_mvc_book
         <where>
             bname like #{bname}
         </where>
  </select>

  <select id="selectBooksLike2" resultType="com.DZY.model.Book" parameterType="java.lang.String">
    select * from t_mvc_book
    <where>
      bname like '${bname}'
    </where>
  </select>

  <select id="selectBooksLike3" resultType="com.DZY.model.Book" parameterType="java.lang.String">
    select * from t_mvc_book
    <where>
      bname like concat('%',#{bname},'%')
    </where>
  </select>

BookService

    @Override
    public List<Book> selectBookLike1(String bname) {
        return bookMapper.selectBookLike1(bname);
    }

    @Override
    public List<Book> selectBookLike2(String bname) {
        return bookMapper.selectBookLike2(bname);
    }

    @Override
    public List<Book> selectBookLike3(String bname) {
        return bookMapper.selectBookLike3(bname);