Mybatis-Plus简单查询和分页

约定不等于承诺〃 2021-07-24 17:58 577阅读 0赞

**目录**

一 简单查询

1 通过多个id批量查询

2 简单的条件查询

二 分页

1 分页插件

1.1 添加分页插件

1.2 测试selectPage分页

2 返回指定的列

--------------------

# 一 简单查询 #

## 1 通过多个id批量查询   ##

/**
    * 功能描述：通过多个id批量查询
    *
    * @author cakin
    * @date 2020/11/14
    */
    @Test
    public void testSelectBatchIds() {
        List<User> users = userMapper.selectBatchIds(Arrays.asList(1, 2, 3));
        users.forEach(System.out::println);
    }

测试结果如下：

Creating a new SqlSession
    SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@6f7e336b] was not registered for synchronization because synchronization is not active
    2020-11-14 17:48:27.250  INFO 27520 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...
    2020-11-14 17:48:27.639  INFO 27520 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Start completed.
    JDBC Connection [HikariProxyConnection@992955027 wrapping com.mysql.cj.jdbc.ConnectionImpl@213bd3d5] will not be managed by Spring
    ==>  Preparing: SELECT id,name,age,email,create_time,update_time FROM user WHERE id IN ( ? , ? , ? )
    ==> Parameters: 1(Integer), 2(Integer), 3(Integer)
    <==    Columns: id, name, age, email, create_time, update_time
    <==        Row: 1, Annie, 28, test1@baomidou.com, null, null
    <==        Row: 2, Jack, 20, test2@baomidou.com, null, null
    <==        Row: 3, Tom, 28, test3@baomidou.com, null, null
    <==      Total: 3
    Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@6f7e336b]
    User(id=1, name=Annie, age=28, email=test1@baomidou.com, createTime=null, updateTime=null)
    User(id=2, name=Jack, age=20, email=test2@baomidou.com, createTime=null, updateTime=null)
    User(id=3, name=Tom, age=28, email=test3@baomidou.com, createTime=null, updateTime=null)

## 2 简单的条件查询 ##

通过map封装查询条件

> 注意：map中的key对应数据库中表的列名。如：数据表字段 user\_id，实体类对应的属性是 userId，这时map的key需要填写user\_id

/**
    * 功能描述：通过map封装查询条件
    *
    * @author cakin
    * @date 2020/11/14
    * @description: map中的key对应数据库中的列名。如：数据库user_id，实体类是userId，这时map的key需要填写user_id
    */
    @Test
    public void testSelectByMap() {
        HashMap<String, Object> map = new HashMap<>();
        // key 是数据表的字段，不是实体类的属性
        map.put("name", "cakin");
        map.put("age", 18);
        List<User> users = userMapper.selectByMap(map);
        users.forEach(System.out::println);
    }

测试结果

Creating a new SqlSession
    SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@47547132] was not registered for synchronization because synchronization is not active
    2020-11-14 18:04:08.827  INFO 29468 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...
    2020-11-14 18:04:09.063  INFO 29468 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Start completed.
    JDBC Connection [HikariProxyConnection@662045021 wrapping com.mysql.cj.jdbc.ConnectionImpl@b174a73] will not be managed by Spring
    ==>  Preparing: SELECT id,name,age,email,create_time,update_time FROM user WHERE name = ? AND age = ?
    ==> Parameters: cakin(String), 18(Integer)
    <==    Columns: id, name, age, email, create_time, update_time
    <==        Row: 1327533614031368193, cakin, 18, 798103175@qq.com, 2020-11-14 16:47:33, 2020-11-14 16:47:33
    <==      Total: 1
    Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@47547132]
    User(id=1327533614031368193, name=cakin, age=18, email=798103175@qq.com, createTime=Sat Nov 14 16:47:33 CST 2020, updateTime=Sat Nov 14 16:47:33 CST 2020, deleted=0)

# 二 分页 #

## 1 分页插件 ##

MyBatis Plus自带分页插件，只要简单的配置即可实现分页功能

### 1.1 添加分页插件 ###

配置类中添加@Bean配置

/**
    * 分页插件
    */
    @Bean
    public PaginationInterceptor paginationInterceptor() {
        return new PaginationInterceptor();
    }

### 1.2 测试selectPage分页 ###

测试：最终通过page对象获取相关数据

/**
    * 功能描述：分页查询
    *
    * @author cakin
    * @date 2020/11/14
    */
    @Test
    public void testSelectPage() {
        // 第1个参数：查询第几页 第2个参数：每页几条数据
        Page<User> page = new Page<>(1, 5);
        // 第1个参数：分页参数 第2个参数：条件查询包装参数
        Page<User> pageParam = userMapper.selectPage(page, null);
        
        // 下面两条语句等价，查询结果相同
        // List<User> records = page.getRecords();
        List<User> records = pageParam.getRecords();
    
        records.forEach(System.out::println);
        System.out.println(pageParam.getPages()); // 总页数
        System.out.println(pageParam.getTotal()); // 总记录数
        System.out.println(pageParam.getCurrent()); // 当前页码
        System.out.println(pageParam.getSize()); // 每页记录数
        System.out.println(pageParam.hasNext()); // 是否有下一页
        System.out.println(pageParam.hasPrevious()); // 是否有上一页
    }

测试结果

Creating a new SqlSession
    SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@5e1218b4] was not registered for synchronization because synchronization is not active
    2020-11-14 18:12:44.471  INFO 28752 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...
    2020-11-14 18:12:44.675  INFO 28752 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Start completed.
    JDBC Connection [HikariProxyConnection@1260012504 wrapping com.mysql.cj.jdbc.ConnectionImpl@6fa69af7] will not be managed by Spring
    JsqlParserCountOptimize sql=SELECT  id,name,age,email,create_time,update_time FROM user
    ==>  Preparing: SELECT COUNT(1) FROM user
    ==> Parameters:
    <==    Columns: COUNT(1)
    <==        Row: 8
    ==>  Preparing: SELECT id,name,age,email,create_time,update_time FROM user LIMIT ?,?
    ==> Parameters: 0(Long), 5(Long)
    <==    Columns: id, name, age, email, create_time, update_time
    <==        Row: 1, Annie, 28, test1@baomidou.com, null, null
    <==        Row: 2, Jack, 20, test2@baomidou.com, null, null
    <==        Row: 3, Tom, 28, test3@baomidou.com, null, null
    <==        Row: 4, Sandy, 21, test4@baomidou.com, null, null
    <==        Row: 1244892079889334273, Annie, 28, 55317332@qq.com, 2020-03-31 15:39:35, 2020-11-14 16:38:00
    <==      Total: 5
    Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@5e1218b4]
    User(id=1, name=Annie, age=28, email=test1@baomidou.com, createTime=null, updateTime=null)
    User(id=2, name=Jack, age=20, email=test2@baomidou.com, createTime=null, updateTime=null)
    User(id=3, name=Tom, age=28, email=test3@baomidou.com, createTime=null, updateTime=null)
    User(id=4, name=Sandy, age=21, email=test4@baomidou.com, createTime=null, updateTime=null)
    User(id=1244892079889334273, name=Annie, age=28, email=55317332@qq.com, createTime=Tue Mar 31 15:39:35 CST 2020, updateTime=Sat Nov 14 16:38:00 CST 2020)
    2
    8
    1
    5
    true
    false

## 2 返回指定的列 ##

当指定了特定的查询列时，希望分页结果列表只返回被查询的列，而不是很多null值

测试selectMapsPage分页：结果集是Map

/**
    * 功能描述：返回指定的列
    *
    * @author cakin
    * @date 2020/11/14
    */
    @Test
    public void testSelectMapsPage() {
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        // 返回指定列，这里是数据库的字段，而不是实体类的属性
        queryWrapper.select("id", "name");
        Page<Map<String, Object>> page = new Page<>(1, 5);
        // 第1个参数：分页参数 第2个参数：条件查询包装参数——指定了返回哪些列
        Page<Map<String, Object>> pageParam = userMapper.selectMapsPage(page, queryWrapper);
        List<Map<String, Object>> records = pageParam.getRecords();
        records.forEach(System.out::println);
        System.out.println(pageParam.getPages()); // 总页数
        System.out.println(pageParam.getTotal()); // 总记录数
        System.out.println(pageParam.getCurrent()); // 当前页码
        System.out.println(pageParam.getSize()); // 每页记录数
        System.out.println(pageParam.hasNext()); // 是否有下一页
        System.out.println(pageParam.hasPrevious()); // 是否有上一页
    }

测试结果

Creating a new SqlSession
    SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@4e61e4c2] was not registered for synchronization because synchronization is not active
    2020-11-14 18:25:46.031  INFO 25680 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...
    2020-11-14 18:25:46.387  INFO 25680 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Start completed.
    JDBC Connection [HikariProxyConnection@1031479068 wrapping com.mysql.cj.jdbc.ConnectionImpl@51ce6f85] will not be managed by Spring
    JsqlParserCountOptimize sql=SELECT  
    id,name
      FROM user
    ==>  Preparing: SELECT COUNT(1) FROM user
    ==> Parameters:
    <==    Columns: COUNT(1)
    <==        Row: 8
    ==>  Preparing: SELECT id,name FROM user LIMIT ?,?
    ==> Parameters: 0(Long), 5(Long)
    <==    Columns: id, name
    <==        Row: 1, Annie
    <==        Row: 2, Jack
    <==        Row: 3, Tom
    <==        Row: 4, Sandy
    <==        Row: 1244892079889334273, Annie
    <==      Total: 5
    Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@4e61e4c2]
    {name=Annie, id=1}
    {name=Jack, id=2}
    {name=Tom, id=3}
    {name=Sandy, id=4}
    {name=Annie, id=1244892079889334273}
    2
    8
    1
    5
    true
    false
    
    2020-11-14 18:25:46.634  INFO 25680 --- [extShutdownHook] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown initiated...
    2020-11-14 18:25:46.651  INFO 25680 --- [extShutdownHook] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown completed.
    
    Process finished with exit code 0