项目里面用的MyBatis-Plus这个工具,刚开始各种不好使,熟悉了以后感觉还是很强大的
http://mp.baomidou.com/guide/quick-start.html
按官方的教程写了个demo,不过数据库我换成了mysql,里面还有些问题得注意,
先打开mysql准备数据
现有一张 User
表,其表结构如下:
id | name | age | |
---|---|---|---|
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 |
其对应的数据库 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上面测试
@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
}
这种方法感觉好一点