springBoot和MyBatis-Plus快速开发及分页实战过程

项目里面用的MyBatis-Plus这个工具,刚开始各种不好使,熟悉了以后感觉还是很强大的

http://mp.baomidou.com/guide/quick-start.html

按官方的教程写了个demo,不过数据库我换成了mysql,里面还有些问题得注意,

 先打开mysql准备数据

现有一张 User 表,其表结构如下:

idnameageemail
1Jone18test1@baomidou.com
2Jack20test2@baomidou.com
3Tom28test3@baomidou.com
4Sandy21test4@baomidou.com
5Billie24test5@baomidou.com

其对应的数据库 Schema 脚本如下:

DROP TABLE IF EXISTS user;

CREATE TABLE user
(
	id BIGINT(20) NOT NULL COMMENT '主键ID',
	name VARCHAR(30) NULL DEFAULT NULL COMMENT '姓名',
	age INT(11) NULL DEFAULT NULL COMMENT '年龄',
	email VARCHAR(50) NULL DEFAULT NULL COMMENT '邮箱',
	PRIMARY KEY (id)
);

其对应的数据库 Data 脚本如下:

DELETE FROM user;

INSERT INTO user (id, name, age, email) VALUES
(1, 'Jone', 18, 'test1@baomidou.com'),
(2, 'Jack', 20, 'test2@baomidou.com'),
(3, 'Tom', 28, 'test3@baomidou.com'),
(4, 'Sandy', 21, 'test4@baomidou.com'),
(5, 'Billie', 24, 'test5@baomidou.com');

 

使用idea自带的 Spring Initializr 快速初始化一个 Spring Boot 工程

pom文件 

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.whale.luo</groupId>
    <artifactId>mybatis-plus</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <packaging>jar</packaging>

    <name>mybatis-plus</name>
    <description>Demo project for Spring Boot</description>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.0.3.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>

        <!--idea需要安装lombok插件-->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.0.6</version>
        </dependency>

        <!--mysql-->
        <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.13</version>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

 application.yml

# DataSource Config
spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT
    username: root
    password: 12345678

实体

@Data
public class User {
    private Long id;
    private String name;
    private Integer age;
    private String email;
}

 注意数据库中的表名默认和实体有着对应关系的,比如
 实体User  对应表USER
 实体UserAddress对应表USER_ADDRESS  
 即表名默认是实体名称每个大小写之间加下划线

当然也可以不使用它这种默认的规则,但需要加@TableName注解 ,比如

@Data
@TableName("USER")
public class UserVo {
    private Long id;
    private String name;
    private Integer age;
    private String email;
}

因此,这两个实体是一样的

我们选择第一个使用吧

jdbc测试数据

 

@RestController
@SpringBootApplication
@MapperScan("com.whale.luo.mybatisplus")
public class MybatisPlusApplication {

    public static void main(String[] args) {
        SpringApplication.run(MybatisPlusApplication.class, args);
    }

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @RequestMapping("/getUsers")
    public List<Map<String, Object>> getDbType(){
        String sql = "select * from USER";
        List<Map<String, Object>> list =  jdbcTemplate.queryForList(sql);
        for (Map<String, Object> map : list) {
            Set<Map.Entry<String, Object>> entries = map.entrySet( );
            if(entries != null) {
                Iterator<Map.Entry<String, Object>> iterator = entries.iterator( );
                while(iterator.hasNext( )) {
                    Map.Entry<String, Object> entry =(Map.Entry<String, Object>) iterator.next( );
                    Object key = entry.getKey( );
                    Object value = entry.getValue();
                    System.out.println(key+":"+value);
                }
            }
        }
        return list;
    }

}

 运行,访问地址http://localhost:8080/getUsers

ok

接下我们就试试用这个插件实现上面的功能 

 

先创建UserMapper 

我们应该继承BaseMappe<T>接口,其中接口中的T应该为我们对应的实体User,不能为其他的,这个插件会自动将根据这个实体去找到它关联的表。

@Component
public interface UserMapper extends BaseMapper<User> {

}

创建接口IUserService及其实现类

在接口中我们也可以声明自己的方法

public interface IUserService extends IService<User> {
    
    List<User> getAll(User user);
    
}

 实现类

@Service("iUserService")
public class IUserServiceImpl extends ServiceImpl<UserMapper,User> implements IUserService {
    @Override
    public List<User> getAll(User user) {
        return this.baseMapper.selectList(null);
    }
}

最后的Controller如下

@RestController
@SpringBootApplication
@MapperScan("com.whale.luo.mybatisplus")
public class MybatisPlusApplication {

    public static void main(String[] args) {
        SpringApplication.run(MybatisPlusApplication.class, args);
    }

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Autowired
    private IUserService iUserService;

    @RequestMapping("/getUsers")
    public List<Map<String, Object>> getDbType(){
        String sql = "select * from USER";
        List<Map<String, Object>> list =  jdbcTemplate.queryForList(sql);
        for (Map<String, Object> map : list) {
            Set<Map.Entry<String, Object>> entries = map.entrySet( );
            if(entries != null) {
                Iterator<Map.Entry<String, Object>> iterator = entries.iterator( );
                while(iterator.hasNext( )) {
                    Map.Entry<String, Object> entry =(Map.Entry<String, Object>) iterator.next( );
                    Object key = entry.getKey( );
                    Object value = entry.getValue();
                    System.out.println(key+":"+value);
                }
            }
        }
        return list;
    }

    @RequestMapping("/getUserList")
    public List<User> getUserList(){
         User user = new User();
        List<User> all = iUserService.getAll(user);
        //List<User> list = iUserService.list(); 也可以用这个方法返回
        return all;
    }

}

 

运行http://localhost:8080/getUserList

最后注意:一般我们的实体是不能出现在controller层的,所以我们应当在service层把实体转换成vo再返到前台 

流程就是这样,从controller中调服务,再从服务中调mapper 

过程中我把service注解加到了接口上,一下子走弯了,这个注解应该加到它的实现类上

 

内置分页 

http://mp.baomidou.com/guide/page.html

直接贴代码

@RestController
@SpringBootApplication
@MapperScan("com.whale.luo.mybatisplus")
public class MybatisPlusApplication {

    public static void main(String[] args) {
        SpringApplication.run(MybatisPlusApplication.class, args);
    }

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

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Autowired
    private IUserService iUserService;

    @RequestMapping("/getUsers")
    public List<Map<String, Object>> getDbType(){
        String sql = "select * from USER";
        List<Map<String, Object>> list =  jdbcTemplate.queryForList(sql);
        for (Map<String, Object> map : list) {
            Set<Map.Entry<String, Object>> entries = map.entrySet( );
            if(entries != null) {
                Iterator<Map.Entry<String, Object>> iterator = entries.iterator( );
                while(iterator.hasNext( )) {
                    Map.Entry<String, Object> entry =(Map.Entry<String, Object>) iterator.next( );
                    Object key = entry.getKey( );
                    Object value = entry.getValue();
                    System.out.println(key+":"+value);
                }
            }
        }
        return list;
    }

    @RequestMapping("/getUserList")
    public List<User> getUserList(){
         User user = new User();
        List<User> all = iUserService.getAll(user);
        //List<User> list = iUserService.list(); 也可以用这个方法返回
        return all;
    }

     @RequestMapping("/selectUserPage")
    public IPage<User> selectUserPage(){
        //Page<User> page = new Page<>(0,0);
        // {"records":[],"total":4,"size":0,"current":1,"searchCount":true,"pages":0}
        // 可以看出"size":0,"current":1,"pages":0 即 一共有0页,每页为0条数据,当前页为第一页

        //Page<User> page = new Page<>(0,1);
        //{"records":[{"id":2,"name":"Jack","age":20,"email":"test2@baomidou.com"}],"total":4,"size":1,"current":1,"searchCount":true,"pages":4}
        //"size":1,"current":1"pages":4   即 一共有4页,每页为1条数据,当前页为第一页

        Page<User> page = new Page<>(1,1);
        //{"records":[{"id":2,"name":"Jack","age":20,"email":"test2@baomidou.com"}],"total":4,"size":1,"current":1,"searchCount":true,"pages":4}
        //可见返回的数据与上条数据一样
        
        //总结new Page<>(1,1);需要传递两个参数,
        // 第一个为current,为当前的页数,默认是从1开始,如果设置为0,它后台是默认请求第一页
        //第二个参数为size ,为每页显示多少条数据,如果为0,则每页为0条数据,所以一般这个值要>=1
        
        IPage<User> userIPage = iUserService.selectUserPage(page);
        return userIPage;
    }

}

分页的时候我们要传一个page参数

总结new Page<>(1,1);需要传递两个参数,
 第一个为current,为当前的页数,默认是从1开始,如果设置为0,它后台是默认请求第一页
第二个参数为size ,为每页显示多少条数据,如果为0,则每页为0条数据,所以一般这个值要>=1

为了测试方便,我直接把page写死了,实际中应该由http请求获得当前页 

public interface IUserService extends IService<User> {

    List<User> getAll(User user);

    IPage<User>  selectUserPage(Page<User> page);

}
@Service("iUserService")
public class IUserServiceImpl extends ServiceImpl<UserMapper,User> implements IUserService {
    @Override
    public List<User> getAll(User user) {
        return this.baseMapper.selectList(null);
    }

    public IPage<User> selectUserPage(Page<User> page) {
        return this.baseMapper.selectPage(page,null);
    }
}

 

运行页面访问 http://localhost:8080/selectUserPage

分页插件 Mybatis-PageHelper   

这个插件适用于mybatis,也适用于 mybatis-plus ,因此,下面的操作都是通用的

 https://github.com/pagehelper/Mybatis-PageHelper

https://github.com/pagehelper/Mybatis-PageHelper/blob/master/wikis/zh/HowToUse.md使用 *

https://github.com/pagehelper/Mybatis-PageHelper/blob/master/wikis/zh/HowToUse.md 集成springBoot*

https://www.cnblogs.com/ljdblog/p/6725094.html 

 上面加*号的需要重点看,问题都可在上面找到答案

 

我的过程如下,还是基于上面接着写

1 pom文件加依赖

       <!--pagehelper-->
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper-spring-boot-starter</artifactId>
            <version>1.2.3</version>
        </dependency>

 2、修改配置文件application

server:
  port: 8080

spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT
    username: root
    password: 12345678
mybatis:
  mapper-locations: classpath:mapping/*Mapper.xml
  type-aliases-package: com.whale.luo.mybatisdemo.po

#pagehelper
pagehelper:
    helperDialect: mysql
    reasonable: true
    supportMethodsArguments: true
    params: count=countSql

 3.1、测试1

还是在springBoot的启动类 MybatisPlusApplication上面测试

https://github.com/pagehelper/Mybatis-PageHelper/blob/master/wikis/zh/HowToUse.md#2-pagehelperstartpage-静态方法调用

   @RequestMapping("/selectUserPage1")
    public List<User> selectUserPage1(){
        //获取第1页,2条内容,默认查询总数count
        PageHelper.startPage(1, 2);
        //紧跟着的第一个select方法会被分页
        List<User> list = iUserService.getAll(null);
        return list;
    }

 访问http://localhost:8080/selectUserPage1

返回两条数据 

[{"id":2,"name":"Jack","age":20,"email":"test2@baomidou.com"},{"id":3,"name":"Tom","age":28,"email":"test3@baomidou.com"}]

3.2 pageInfo测试2

 

   @RequestMapping("/selectUserPage2")
    public PageInfo selectUserPage2(){

        //获取第1页,10条内容,默认查询总数count
        PageHelper.startPage(1, 1);
        //紧跟着的第一个select方法会被分页
        List<User> list = iUserService.getAll(null);
        //用PageInfo对结果进行包装
        PageInfo pageInfo = new PageInfo(list);
        return pageInfo;
    }

 运行 http://localhost:8080/selectUserPage2

 确实获得了一条数据,但还有好多其他东西

如下

{
    "pageNum":1,   
当前页
    "pageSize":1,     每页显示多少条数据
    "size":1,
    "startRow":1,
    "endRow":1,"
    total":4,
    "pages":4,         
一共有4页
    "list":[                 返回的list数据
            {"id":2,"name":"Jack","age":20,"email":"test2@baomidou.com"}
            ],
    "prePage":0,
    "nextPage":2,     
下一页
    "isFirstPage":true,   
    "isLastPage":false,
    "hasPreviousPage":false,  
    "hasNextPage":true,
    "navigatePages":8,
    "navigatepageNums":[1,2,3,4],
    "navigateFirstPage":1,
    "navigateLastPage":4,
    "lastPage":4,      
    "firstPage":1
}

这种方法感觉好一点 

  • 3
    点赞
  • 13
    收藏
    觉得还不错? 一键收藏
  • 2
    评论
您好!关于您的问题,我可以帮您解答。针对springboot整合mybatis-plus实现多表分页查询实现,可以按照以下步骤进行: 1.在pom.xml文件中添加Mybatis-Plus和Pagehelper的依赖,如下: ``` <!-- Mybatis-Plus依赖 --> <dependency> <groupId>com.baomidou</groupId> <artifactId>mybatis-plus-boot-starter</artifactId> <version>3.0.5</version> </dependency> <!-- Pagehelper依赖 --> <dependency> <groupId>com.github.pagehelper</groupId> <artifactId>pagehelper-spring-boot-starter</artifactId> <version>1.2.13</version> </dependency> ``` 2.在Mybatis-Plus的配置文件中,指定分页插件。如下: ``` @Configuration @MapperScan("com.example.mapper") public class MybatisPlusConfig { @Bean public PaginationInterceptor paginationInterceptor() { return new PaginationInterceptor(); } } ``` 3.编写Mapper和对应的Mapper.xml文件,进行多表联合查询,并在Mapper接口方法上添加分页参数。如下: ``` //在Mapper接口方法上添加分页参数 public interface UserMapper extends BaseMapper<User> { List<User> selectUserPage(Page<User> page); } <!-- 在Mapper.xml中编写多表联合查询SQL语句 --> <select id="selectUserPage" resultMap="BaseResultMap"> select u.*, r.role_name from user u left join user_role ur on u.id = ur.user_id left join role r on ur.role_id = r.id <where> <if test="username != null and username != ''"> and u.username like concat('%',#{username},'%') </if> </where> </select> ``` 4.在Controller层中,接受分页参数并返回分页结果。如下: ``` @RestController public class UserController { @Autowired private UserMapper userMapper; @GetMapping("/users") public Page<User> selectUserPage(@RequestParam(defaultValue = "1") Integer page, @RequestParam(defaultValue = "10") Integer size, String username) { Page<User> p = new Page<>(page, size); p = userMapper.selectUserPage(p, username); return p; } } ``` 以上就是整合Mybatis-Plus和Pagehelper实现多表分页查询的具体步骤,希望能对您有所帮助!如果您有其他问题,欢迎继续提问。

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值