Mybatis动态sql及分页

mybatis动态sql

1. if

<!--模糊查询 带 if 判断-->

  <select id="selIf" resultType="java.util.Map" parameterType="java.util.Map">
    select * from t_mvc_book
    <if test="bname != null and bname != ''">
      <where>
        bname like #{
   bname}
      </where>
    </if>
  </select>

2.trim

  一样的sql语句拼接：prefix前缀，suffi 后缀。suffixOverrides 后缀覆盖
  <trim prefix="values (" suffix=")" suffixOverrides="," >

3.foreach

<!--foreach-->
  <!--collection代表要被循环参数集合。open和close代表开始和结束拼接字符串。separator代表item之间的分割符。
  item就是当前正在循环的变量定义-->
  <!--  select * from t_mvc_book where bid in (?,?,?,……) -->
  <select id="selBookIds" resultType="com.swx.model.Book" parameterType="java.util.List">
    select * from  t_mvc_book
  <!--这里做了个简单的是否为空的判断-->
    <if test="bookIds != null and bookIds.size > 0">
      where bid in
      <foreach collection="bookIds" open="("  close=")" separator="," item="bid">
        #{
   bid}
      </foreach>
    </if>
  </select>

BookMapper

//模糊查询 带if判断条件
    List<Map> selIf(Map book);

//    foreach
    List<Book> selBookIds(@Param("bookIds") List bookIds);

BookService

//模糊查询 if
    List<Map> selIf(Map book);
    //    foreach
    List<Book> selBookIds(List bookIds);

BookServiceImpl

@Override
    public List<Map> selIf(Map book) {
   
        return bookMapper.selIf(book);
    }

    @Override
    public List<Book> selBookIds(List bookIds) {
   
        return bookMapper.selBookIds(bookIds);
    }

测试：

 @Test
    public void selIf(){
   
        Map map = new HashMap();
        map.put("bname","%圣墟%");
        for (Map m : this.bookService.selIf(map)) {
   
            System.out.println(m);
        }
    }
    @Test
    public void selBookIds(){
   
        List bookIds = new ArrayList<>();
        bookIds.add(22);
        bookIds.add(44);
        bookIds.add(55);
        List <Book> bookList = this.bookService.selBookIds(bookIds);
        for (Book book : bookList) {
   
            System.out.println(book);
        }
    }

结果：

三种模糊查询

1.#{…}
2.${…}
3.concat{’%’,#{bname},’%’}

注意：#{…}自带引号，${…}有sql注入的风险

<select id="selLike1" resultType="com.swx.model.Book" parameterType="java.lang.String">
    select * from t_mvc_book
    <if test="bname != null and bname !=''">
      <where>
        bname like #{
   bname}
      </where>
    </if>

  </select>

  <select id="selLike2" resultType="com.swx.model.Book" parameterType="java.lang.String">
    select  * from t_mvc_book
    <if test="bname != null and bname != ''">
      <where>
        bname like '${bname}'
      </where>
    </if>
  </select>

  <select id="selLike3" resultType="com.swx.model.Book" parameterType="java.lang.String">
    select * from t_mvc_book
    <if test="bname != null and bname != ''">
      <where>
        bname like concat(concat('%',#{
   bname},'%'))
      </where>
    </if>
  </select>

BookMapper

//    模糊查询 #{}
    List<Book> selLike1(@Param("bname") String bname);
//    模糊查询 ${}
    List<Book> selLike2(@Param("bname") String bname);
//    模糊查询 concat
    List<Book> selLike3(@Param(