mysql分頁插件springboot_Springboot內建mybatis通用Mapper與分頁插件PageHelper

插件介紹

通用 Mapper 是一個可以實作任意 MyBatis 通用方法的架構，項目提供了正常的增删改查操作以及 Example 相關的單表操作。通用 Mapper 是為了解決 MyBatis 使用中 90% 的基本操作，PageHelper則提供通用的分頁查詢功能，使用它們可以很友善的進行開發，可以節省開發人員大量的時間。

項目結構

導入依賴

1

3

4 tk.mybatis

5 mapper-spring-boot-starter

6 2.1.5

7

8

10

11 com.github.pagehelper

12 pagehelper-spring-boot-starter

13 1.2.10

14

15

16

17 mysql

18 mysql-connector-java

19

20

21

22 org.springframework.boot

23 spring-boot-starter-web

24

屬性配置

在 application.properties 檔案中分别添加上資料庫、Mybatis、通用Mapper、PageHelper的屬性配置

spring.datasource.url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&useSSL=false

spring.datasource.username=root

spring.datasource.password=123456

# 如果想看到mybatis日志需要做如下配置

logging.level.com.carry=DEBUG

######### Mybatis 自身配置 ##########

mybatis.mapper-locations=classpath:com/carry/mapper*.xml

10

11 true

12

13

14

15

16 org.springframework.boot

17 spring-boot-maven-plugin

18

19

20

具體編碼

1、表結構

建立一張 user表

CREATE TABLE `user` (

`id` int(8) NOT NULL AUTO_INCREMENT COMMENT '主鍵自增',

`username` varchar(50) NOT NULL COMMENT '使用者名',

`password` varchar(50) NOT NULL COMMENT '密碼',

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='使用者表';

2、實體類

1 packagecom.carry.entity;2

3 importjava.io.Serializable;4

5 importjavax.persistence.GeneratedValue;6 importjavax.persistence.GenerationType;7 importjavax.persistence.Id;8 importjavax.persistence.Table;9

10 @Table(name = "user")11 public class User implementsSerializable {12

13 private static final long serialVersionUID = -8057591359892731452L;14

15 @Id16 @GeneratedValue(strategy =GenerationType.IDENTITY)17 privateLong id;18 privateString username;19 privateString password;20 publicLong getId() {21 returnid;22 }23 public voidsetId(Long id) {24 this.id =id;25 }26 publicString getUsername() {27 returnusername;28 }29 public voidsetUsername(String username) {30 this.username =username;31 }32 publicString getPassword() {33 returnpassword;34 }35 public voidsetPassword(String password) {36 this.password =password;37 }38

39

40 publicUser() {}41 publicUser(String username, String password) {42 super();43 this.username =username;44 this.password =password;45 }46 }

3、持久層

packagecom.carry.mapper;importcom.carry.entity.User;importtk.mybatis.mapper.common.Mapper;

public interface UserMapper extends Mapper{

intcountByUsername(String username);

}

4、映射檔案UserMapper.xml

1 <?xml version="1.0" encoding="UTF-8"?>

2

3

4

5

6 SELECT count(1) FROM user WHERE username = #{username}7

8

5、啟動類

importorg.springframework.boot.SpringApplication;importorg.springframework.boot.autoconfigure.SpringBootApplication;importtk.mybatis.spring.annotation.MapperScan;

@SpringBootApplication

@MapperScan(basePackages= "com.carry.mapper")public classSpringbootMapperPagehelperApplication {public static void main(String[] args) {

SpringApplication.run(SpringbootMapperPagehelperApplication.class, args);

}

}

測試

完成資料通路層接口後，編寫一個junit測試類來檢驗代碼的正确性。

1 packagecom.carry;2

3 importorg.junit.Test;4 importorg.junit.runner.RunWith;5 importorg.slf4j.Logger;6 importorg.slf4j.LoggerFactory;7 importorg.springframework.beans.factory.annotation.Autowired;8 importorg.springframework.boot.test.context.SpringBootTest;9 importorg.springframework.test.context.junit4.SpringRunner;10

11 importcom.carry.entity.User;12 importcom.carry.mapper.UserMapper;13 importcom.github.pagehelper.PageHelper;14 importcom.github.pagehelper.PageInfo;15

16 @RunWith(SpringRunner.class)17 @SpringBootTest18 public classSpringbootMapperPagehelperApplicationTests {19

20 private final Logger logger = LoggerFactory.getLogger(this.getClass());21

22 @Autowired23 privateUserMapper userMapper;24

25 @Test26 public voidtest() {27 try{28 final User user1 = new User("u1", "p1");29 final User user2 = new User("u1", "p2");30 final User user3 = new User("u3", "p3");31 userMapper.insertSelective(user1);32 logger.info("[user1回寫主鍵] - [{}]", user1.getId());33 userMapper.insertSelective(user2);34 logger.info("[user2回寫主鍵] - [{}]", user2.getId());35 userMapper.insertSelective(user3);36 logger.info("[user3回寫主鍵] - [{}]", user3.getId());37 final int count = userMapper.countByUsername("u1");38 logger.info("[調用自己寫的SQL] - [{}]", count);39

40 //TODO 模拟分頁

41 for (int i = 0; i < 20; i++) {42 userMapper.insertSelective(new User("u" + i, "p" +i));43 }44 //TODO 分頁 + 排序 this.userMapper.selectAll() 這一句就是我們需要寫的查詢，有了這兩款插件無縫切換各種資料庫

45 final PageInfo pageInfo = PageHelper.startPage(1, 10).setOrderBy("id desc")46 .doSelectPageInfo(() -> this.userMapper.selectAll());47 logger.info("[lambda寫法] - [分頁資訊] - [{}]", pageInfo.toString());48

49 PageHelper.startPage(1, 10).setOrderBy("id desc");50 final PageInfo userPageInfo = new PageInfo<>(this.userMapper.selectAll());51 logger.info("[普通寫法] - [{}]", userPageInfo);52 } catch(Exception e) {53 logger.error(e.getMessage());54 }55 }56

57 }