mybatis动态SQL之foreach、模糊查询、分页

前言

之前我们讲过了mybatis的sql语句是由我们手动编写的，但是我们的mybatis逆向工程生成的xxMapper.xml中会自动生成一部分动态的sql

什么叫做动态SQL？

根据用户提供的参数，动态决定查询语句依赖的查询条件或SQL语句的内容。

这篇博客主要讲述mybatis的动态SQL以及其中的分页。

测试mybatis的自生动态sql

首先我们根据service类创建一个测试类

勾上setUp和tearDown，再选勾上自己要测试的方法

将测试类准备好：

package com.zengjing.service;

import com.zengjing.mapper.BookMapper;
import com.zengjing.model.Book;
import com.zengjing.service.impl.BookServiceImpl;
import com.zengjing.util.SessionUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

/**
 * @author zengjing
 * @site https://blog.youkuaiyun.com/JiangHu_Java
 * @create  2020-10-21 14:38
 */
public class IBookServiceTest {

    private IBookService bookService;
    private SqlSession sqlSession;


    @Test
    public void deleteByPrimaryKey() {
        this.bookService.deleteByPrimaryKey(12);
    }

    @Test
    public void updateByPrimaryKey() {
        Book book=new Book();
        book.setBookId(12);
        book.setBookName("新神雕侠侣5555");
        book.setBookPrice((float) 38);
        book.setBookBrief("略ovo");
        this.bookService.updateByPrimaryKey(book);
    }

    @Test
    public void insert() {
        Book book=new Book();
        book.setBookId(13);
        book.setBookName("测试1");
        book.setBookPrice((float) 14);
        book.setBookBrief("略");
        this.bookService.insert(book);
    }


    @Test
    public void selectByPrimaryKey() {
        Book book = this.bookService.selectByPrimaryKey(1);
        System.out.println(book);
    }


    @Before
    public void setUp() throws Exception {
        BookServiceImpl bookService=new BookServiceImpl();
        sqlSession = SessionUtil.openSession();
        BookMapper mapper = sqlSession.getMapper(BookMapper.class);
        bookService.setBookMapper(mapper);
        this.bookService=bookService;
    }

    @After
    public void tearDown() throws Exception {
        sqlSession.commit();
        sqlSession.close();
    }

}

再看看生成的xxMapper.xml中自动生成的动态sql：

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.zengjing.mapper.BookMapper" >
  <resultMap id="BaseResultMap" type="com.zengjing.model.Book" >
    <constructor >
      <idArg column="book_id" jdbcType="INTEGER" javaType="java.lang.Integer" />
      <arg column="book_name" jdbcType="VARCHAR" javaType="java.lang.String" />
      <arg column="book_price" jdbcType="REAL" javaType="java.lang.Float" />
      <arg column="book_brief" jdbcType="VARCHAR" javaType="java.lang.String" />
    </constructor>
  </resultMap>
  <sql id="Base_Column_List" >
    book_id, book_name, book_price, book_brief
  </sql>
  <select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Integer" >
    select 
    <include refid="Base_Column_List" />
    from t_book
    where book_id = #{bookId,jdbcType=INTEGER}
  </select>
  <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer" >
    delete from t_book
    where book_id = #{bookId,jdbcType=INTEGER}
  </delete>
  <insert id="insert" parameterType="com.zengjing.model.Book" >
    insert into t_book (book_id, book_name, book_price, 
      book_brief)
    values (#{bookId,jdbcType=INTEGER}, #{bookName,jdbcType=VARCHAR}, #{bookPrice,jdbcType=REAL}, 
      #{bookBrief,jdbcType=VARCHAR})
  </insert>
  <insert id="insertSelective" parameterType="com.zengjing.model.Book" >
    insert into t_book
    <trim prefix="(" suffix=")" suffixOverrides="," >
      <if test="bookId != null" >
        book_id,
      </if>
      <if test="bookName != null" >
        book_name,
      </if>
      <if test="bookPrice != null" >
        book_price,
      </if>
      <if test="bookBrief != null" >
        book_brief,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides="," >
      <if test="bookId != null" >
        #{bookId,jdbcType=INTEGER},
      </if>
      <if test="bookName != null" >
        #{bookName,jdbcType=VARCHAR},
      </if>
      <if test="bookPrice != null" >
        #{bookPrice,jdbcType=REAL},
      </if>
      <if test="bookBrief != null" >
        #{bookBrief,jdbcType=VARCHAR},
      </if>
    </trim>
  </insert>
  <update id="updateByPrimaryKeySelective" parameterType="com.zengjing.model.Book" >
    update t_book
    <set >
      <if test="bookName != null" >
        book_name = #{bookName,jdbcType=VARCHAR},
      </if>
      <if test="bookPrice != null" >
        book_price = #{bookPrice,jdbcType=REAL},
      </if>
      <if test="bookBrief != null" >
        book_brief = #{bookBrief,jdbcType=VARCHAR},
      </if>
    </set>
    where book_id = #{bookId,jdbcType=INTEGER}
  </update>
  <update id="updateByPrimaryKey" parameterType="com.zengjing.model.Book" >
    update t_book
    set book_name = #{bookName,jdbcType=VARCHAR},
      book_price = #{bookPrice,jdbcType=REAL},
      book_brief = #{bookBrief,jdbcType=VARCHAR}
    where book_id = #{bookId,jdbcType=INTEGER}
  </update>
</mapper>

我们首先找到我们熟悉的标签：select，insert，update，delete，可以看到这四个标签都是有的，为什么会有?其实这个映射文件里的这些标签都是根据对应的mapper接口里的方法生成的。

这些sql的标签的id都是mapper接口里方法的方法名 ，带的参数参数名也是一致的，如果是带的对象的话，则会使用id标签来对对象的属性一个一个判断要执行的字段是哪些：

package com.zengjing.mapper;

import com.zengjing.model.Book;

public interface BookMapper {
    int deleteByPrimaryKey(Integer bookId);

    int insert(Book record);

    int insertSelective(Book record);

    Book selectByPrimaryKey(Integer bookId);

    int updateByPrimaryKeySelective(Book record);

    int updateByPrimaryKey(Book record);
}

我们以此来看这些标签，select：

  <resultMap id="BaseResultMap" type="com.zengjing.model.Book" >
    <constructor >
      <idArg column="book_id" jdbcType="INTEGER" javaType="java.lang.Integer" />
      <arg column="book_name" jdbcType="VARCHAR" javaType="java.lang.String" />
      <arg column="book_price" jdbcType="REAL" javaType="java.lang.Float" />
      <arg column="book_brief" jdbcType="VARCHAR" javaType="java.lang.String" />
    </constructor>
  </resultMap>
  
  <sql id="Base_Column_List" >
    book_id, book_name, book_price, book_brief
  </sql>
  
  <select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Integer" >
    select 
    <include refid="Base_Column_List" />
    from t_book
    where book_id = #{bookId,jdbcType=INTEGER}
  </select>

其中resultMap是用来存放我们查询的结果集的，sql标签存放了我们查询的字段。

测试：

查询结果：

insert：

 <insert id="insertSelective" parameterType="com.zengjing.model.Book" >
    insert into t_book
    <trim prefix="(" suffix=")" suffixOverrides="," >
      <if test="bookId != null" >
        book_id,
      </if>
      <if test="bookName != null" >
        book_name,
      </if>
      <if test="bookPrice != null" >
        book_price,
      </if>
      <if test="bookBrief != null" >
        book_brief,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides="," >
      <if test="bookId != null" >
        #{bookId,jdbcType=INTEGER},
      </if>
      <if test="bookName != null" >
        #{bookName,jdbcType=VARCHAR},
      </if>
      <if test="bookPrice != null" >
        #{bookPrice,jdbcType=REAL},
      </if>
      <if test="bookBrief != null" >
        #{bookBrief,jdbcType=VARCHAR},
      </if>
    </trim>
  </insert>

其中使用了trim来进行括号以及最后的逗号的去除，测试：

update测试：

delect测试：

注意，测试前需要增加一段pom.xml中的配置文件：

  <resources>
      <!--解决mybatis-generator-maven-plugin运行时没有将XxxMapper.xml文件放入target文件夹的问题-->
      <resource>
        <directory>src/main/java</directory>
        <includes>
          <include>**/*.xml</include>
        </includes>
      </resource>
      <!--解决mybatis-generator-maven-plugin运行时没有将jdbc.properites文件放入target文件夹的问题-->
      <resource>
        <directory>src/main/resources</directory>
        <includes>
          <include>jdbc.properties</include>
          <include>*.xml</include>
        </includes>
      </resource>
    </resources>

添加位置，build标签中：

foreach标签

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

我们首先在mapper.java类中增加要的方法：

    /**
     * 加Param注解是因为形参需要在mapper.xml文件中使用
     * @param BookIds
     * @return
     */
    List<Book> selectBooksIn(@Param("BookIds") List BookIds);

然后我们在mapper.xml文件中增加foreach标签：

resultType是用来定义一个返回结果集对象的全限定名或者别名。如果接收参数是一个集合，那么这里定义的就是集合中可以包含的类型，而并不是集合本身。

parameterType是用来定义传递参数的类型。

collection是用来定义传递参数也就是数据源的名称，item是循环集合中的对象别名，open是用来开头的；close是加在结束的，separator是每次循环间加的。

  <!--foreach标签使用-->
  <select id="selectBooksIn" resultType="com.zengjing.model.Book" parameterType="java.util.List" >
    select * from t_book where bookId in
    <foreach collection="BookIds" item="bid" open="(" close=")" separator=",">
      #{bid}
    </foreach>
  </select>

然后我们在service类中增加相应的方法：

IBookService增加：

List<Book> selectBooksIn(List BookIds);

实现类BookServiceImpl增加：

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

然后我们测试：

    @Test
    public void selectBooksIn(){
        List list=new ArrayList();
        list.add(1);
        list.add(3);
        list.add(11);
        List<Book> books=this.bookService.selectBooksIn(list);
        System.out.println(books.size());
        for (Book b:books
             ) {
            System.out.println(b);
        }
    }

遇到的小bug

博主在测试foreach标签时，出现了一个bug：明明查到了结果，但是集合当中的对象显示为空，这是因为博主之前在数据库当中字段命名的方法是加了下划线，类似book_id，但是mybatis逆向工程生成的实体类字段名是bookId，导致两边字段不一致，我用一个book的集合来装的时候，他识别不出来是实体类的book，就变成了null，其实是有结果的。

模糊查询

模糊查询一共有三种拼接sql语句的方法：

#{…}

#会在前后加单引号[']

${…}

$则直接插入值

concat

拼接函数

我们一般都会采用#，因为$有sql注入的危险，而concat又过于麻烦。

我们依次测试：

首先准备好工具类：

package com.zengjing.util;

/**
 * @author zengjing
 * @site https://blog.youkuaiyun.com/JiangHu_Java
 * @create  2020-10-26 19:56
 */
public class StringUtils {
    public static String toLikeStr(String str){
        return "%"+str+"%";
    }
}

mapper.java中增加方法：

    List<Book> selectBookLike1(@Param("bookName") String bookName);
    List<Book> selectBookLike2(@Param("bookName") String bookName);
    List<Book> selectBookLike3(@Param("bookName") String bookName);

mapper.xml中的事务，具体差异就是体现在这里，由#插入的值直接写参数名就行了，而$需要加单引号，且前面也说了sql注入的危险，不懂的可以去博主上篇博客看看，而concat确实比较前两者稍显麻烦

  <!--模糊查询的三种拼接sql语句的方法-->
  <select id="selectBookLike1"  resultType="com.zengjing.model.Book" parameterType="java.lang.String">
      select * from t_book where bookName like #{bookName}
  </select>

  <select id="selectBookLike2"  resultType="com.zengjing.model.Book" parameterType="java.lang.String">
      select * from t_book where bookName like '${bookName}'
  </select>

  <select id="selectBookLike3"  resultType="com.zengjing.model.Book" parameterType="java.lang.String">
      select * from t_book where bookName like concat(concat('%',#{bookName}),'%')
  </select>

IBookService增加：

 	List<Book> selectBookLike1(String bookName);
    List<Book> selectBookLike2(String bookName);
    List<Book> selectBookLike3(String bookName);

实现类BookServiceImpl增加：

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

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

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

IBookServiceTest测试代码：

@Test
    public void selectBookLike() {
        String bname = "的";
        //第一种
        //List<Book> books =this.bookService.selectBookLike1(StringUtils.toLikeStr(bname));
        //第二种
        //List<Book> books =this.bookService.selectBookLike2(StringUtils.toLikeStr(bname));
        //第三种
        List<Book> books =this.bookService.selectBookLike3(bname);
        for (Book b : books) {
            System.out.println(b);
        }
    }

测试结果：

分页

其实mybatis中是自带分页的，那么为什么要将分页特意做一遍？

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

意思就是，mybatis中的分页是将数据库的数据全都查询并保存到内存当中，然后我们需要的时候根据页数将数据取出来，那么当数据过多的时候，不仅查询时间慢，而且那么多的数据保存到内存当中可能导致的问题也会有很多，所以我们需要将分页特意做一遍。

首先导入依赖

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

在mybatis.cfg.xml的运行环境前配置

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

Bookmapper.java中增加分页的方法

List<Map> listPager(Map map);

BookMapper.xml中增加标签

  <select id="listPager" resultType="java.util.Map">
      select * from t_book where bookName like #{bookName}
  </select>

IBookService增加方法

List<Map> listPager(Map map, PageBean pageBean);

IBookService的实现类BookServiceImpl增加方法，分页最主要的代码处理就在这里，就是PageHelper处理了分页的请求，我们先测试成功的结果

 @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.getTotal());
            System.out.println("当前页："+pageInfo.getPageNum());
            System.out.println("页大小："+pageInfo.getPageSize());
            pageBean.setTotal(pageInfo.getTotal()+"");
            System.out.println("总页数：" + pageBean.getMaxPage());
        }
        return list;
    }

测试代码：

    @Test
    public void listPager() {
        Map map=new HashMap();
        map.put("bookName","%%");

        PageBean pageBean =new PageBean();
        pageBean.setPage(1);
        List<Map> list=this.bookService.listPager(map,pageBean);
        for (Map m:list
             ) {
            System.out.println(m);
        }


    }

测试结果：

假如我们将BookServiceImpl的listPager中的PageHelper处理那一行代码注释掉再运行

测试结果，可以看到传递到数据库的sql语句没有了后面的limit