mybatis动态sql和分页

mybatis动态sql

讲解foreach标签

BookMapper.xml

<!--讲解foreach标签-->
  <select id="selectBooksIn" resultType="com.xfz.model.Book" parameterType="java.util.List">
    select * from t_mvc_book where bid in
    <foreach collection="bookIds" item="bid" open="(" close=")" separator=",">
      #{bid}
    </foreach>
  </select>

BookMapper.java
注意：不加@Param(“bookIds”) 会报错

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

BookServiceImpl.java

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

测试：

public void selectBooksIn() {
        List bookIds=new ArrayList();
        bookIds.add(16);
        bookIds.add(26);
        bookIds.add(36);
        for (Book book : this.bookService.selectBooksIn(bookIds)) {
            System.out.println(book);
        }
    }

模糊查询

三种方式：

2.1 参数中直接加入%%
2.2 使用${...}代替#{...}（不建议使用该方式，有SQL注入风险）  
          关键：#{...}与${...}区别？
          参数类型为字符串，#会在前后加单引号[']，$则直接插入值
          注：
          1) mybatis中使用OGNL表达式传递参数
          2) 优先使用#{...}
          3) ${...}方式存在SQL注入风险
2.3 SQL字符串拼接CONCAT

BookMapper.xml

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

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

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

BookMapper.java

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

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

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

BookServiceImpl.java

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

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

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

测试：

@Test
    public void selectBooksLike1() {
        for (Book book : this.bookService.selectBooksLike1("%圣墟%")) {
            System.out.println(book);
        }
    }

    @Test
    public void selectBooksLike2() {
        for (Book book : this.bookService.selectBooksLike1("%圣墟%")) {
            System.out.println(book);
        }
    }

    @Test
    public void selectBooksLike3() {
        for (Book book : this.bookService.selectBooksLike1("%圣墟%")) {
            System.out.println(book);
        }
    }

查询返回结果集的处理

resultMap：适合使用返回值是自定义实体类的情况
resultType：适合使用返回值的数据类型是非自定义的，即jdk的提供的类型

1. 使用resultMap返回自定义类型集合

BookMapper.xml

<select id="list1" resultMap="BaseResultMap">
     select * from t_mvc_book
  </select>

BookMapper.java

List<Book> list1();

2. 使用resultType返回List

BookMapper.xml

<select id="list2" resultType="com.xfz.model.Book">
     select * from t_mvc_book
  </select>

BookMapper.java

/*
     * 使用resultType返回List<T>
     * */
    List<Book> list2();

3. 使用resultType返回单个对象

BookVo.java

package com.xfz.model;

import java.util.List;

/**
 * @author xfz
 * @site www.xfz.com
 * @company zking
 * @create  2019-11-16 15:28
 *
 *
 * vo 类专门用来封装多表联查信息用于展示的
 * 封装查询条件
 */
public class BookVo extends Book{
    private List<Integer> bookIds;

    public List<Integer> getBookIds() {
        return bookIds;
    }

    public void setBookIds(List<Integer> bookIds) {
        this.bookIds = bookIds;
    }
}

BookMapper.xml

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

BookMapper.java

/*
     * 使用resultType返回单个对象
     * */
    List<Book> list3(BookVo bookVo);

4. 使用resultType返回List

BookMapper.xml

<select id="list4" resultType="java.util.Map">
  select * from t_mvc_book
</select>

BookMapper.java

/*
     * 使用resultType返回List<Map>，适用于多表查询返回结果集
     * */
    List<Map> list4();

5. 使用resultType返回Map<String,Object>，适用于多表查询返回单个结果集

BookMapper.xml

<select id="list5" resultType="java.util.Map" parameterType="java.util.Map">
  select * from t_mvc_book where bid = #{bid}
</select>

BookMapper.java

/*
     *  使用resultType返回Map<String,Object>，适用于多表查询返回单个结果集
     * */
    Map list5(Map map);

BookServiceImpl.java

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

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

    @Override
    public List<Book> list3(BookVo bookVo) {
        return bookMapper.list3(bookVo);
    }

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

    @Override
    public Map list5(Map map) {
        return bookMapper.list5(map);
    }

测试：

@Test
    public void list1(){
        for (Book book : this.bookService.list1()) {
            System.out.println(book);
        }
    }
    @Test
    public void list2(){
        for (Book book : this.bookService.list2()) {
            System.out.println(book);
        }
    }
    @Test
    public void list3(){
        BookVo bookVo=new BookVo();
        List bookIds=new ArrayList();
        bookIds.add(11);
        bookIds.add(22);
        bookIds.add(33);
        bookVo.setBookIds(bookIds);
        for (Book book : this.bookService.list3(bookVo)) {
            System.out.println(book);
        }
    }
    @Test
    public void list4(){
        for (Map map : this.bookService.list4()) {
            System.out.println(map);
        }
    }
    @Test
    public void list5(){
        Map map=new HashMap();
        map.put("bid",100);
        System.out.println(this.bookService.list5(map));
    }

分页查询

为什么要重写mybatis的分页？
Mybatis的分页功能很弱，它是基于内存的分页(查出所有记录再按偏移量offset和边界limit取结果)，在大数据量的情况下这样的分页基本上是没有用的

使用分页插件步奏

1. 导入pom依赖

<dependency>
      <groupId>com.github.pagehelper</groupId>
      <artifactId>pagehelper</artifactId>
      <version>5.1.2</version>
    </dependency>

2. Mybatis.cfg.xml配置拦截器

必须遵循dtd约束，不然会报错

<!ELEMENT configuration (properties?, settings?, typeAliases?, typeHandlers?, objectFactory?, objectWrapperFactory?, reflectorFactory?, plugins?, environments?, databaseIdProvider?, mappers?)>

<plugins>
    <!-- 配置分页插件PageHelper, 4.0.0以后的版本支持自动识别使用的数据库 -->
    <plugin interceptor="com.github.pagehelper.PageInterceptor">
    </plugin>
</plugins>

3. 使用PageHelper进行分页
   BookMapper.xml

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

BookMapper.java

//select * from t__mvc_book where bname like '%圣墟%' limit 20,10
    List<Map> listPager(Map map);

BookServiceImpl.java

@Override
    public List<Map> listPager(Map map, PageBean pageBean) {
        if (pageBean!=null && pageBean.isPagination()){
            PageHelper.startPage(pageBean.getPage(),pageBean.getRows());
        }
        List<Map> list = bookMapper.listPager(map);
        if (pageBean!=null && pageBean.isPagination()){
            PageInfo pageInfo=new PageInfo(list);
            System.out.println("当前页：" + pageInfo.getPageNum());
            System.out.println("显示当前内容数量：" + pageInfo.getSize());
            System.out.println("符合条件的总记录数：" + pageInfo.getTotal());
            pageBean.setTotal(pageInfo.getTotal()+"");
        }
        return list;
    }

4. 处理分页结果

@Test
    public void listPager(){
       Map map=new HashMap();
       map.put("bname","%圣墟%");
        PageBean pageBean=new PageBean();
//        pageBean.setPagination(false);
        pageBean.setPage(3);
        for (Map m : this.bookService.listPager(map, pageBean)) {
            System.out.println(m);
        }
    }

特殊字符处理

(>)
<(<)
&(&)
空格( )
<![CDATA[ <= ]]>

BookMapper.xml

<select id="list6" resultType="com.xfz.model.Book" parameterType="com.xfz.model.BookVo">
    <!--select * from t_mvc_book where <![CDATA[ price> #{min} and price < #{max} ]]>-->
    select * from t_mvc_book where price &gt; #{min} and price &lt; #{max}
</select>

BookMapper.java

List<Book> list6(BookVo bookVo);

BookServiceImpl.java

@Override
    public List<Book> list6(BookVo bookVo) {
        return bookMapper.list6(bookVo);
    }