Springboot series (12) using Mybatis integrated PageHelper paging plug-in and mapper plug-in

Be serious about writing, not clickbait.

The article is available at Github.com/niumoo/Java… And program ape Alang’s blog, point attention, don’t get lost.

preface

In the eleventh article of Springboot series (using Mybatis (automatic generation plug-in) to access the database), the experiment of Springboot combined with Mybatis and Mybatis-generator plug-in development process, In fact, for Mybatis, there are many excellent convenient plug-ins, such as the general Mapper generation plug-in to demonstrate and paging plug-in.

Database preparation

Since it is the persistence layer framework, prepare a data table for the experiment operation, this time using the springboot.book data table from the mysql database used in the previous experiment.

The uncreated table book can be created in the springboot library of the mysql database for demonstration purposes.

CREATE TABLE `book` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `author` varchar(255) DEFAULT NULL COMMENT 'Book author',
  `name` varchar(255) DEFAULT NULL COMMENT 'Book Name',
  `price` float NOT NULL COMMENT 'Book prices',
  `create_time` datetime NOT NULL COMMENT 'Creation time',
  `description` varchar(255) DEFAULT NULL COMMENT 'Book Description'.PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8;
Copy the code

Add test data.

INSERT INTO 'springboot'. 'book' (' id ', 'author ',' name ', 'price ',' create_time ', 'description') VALUES (2, 'springboot ',' description ') 12, '2018-09-01 10:10:12', 'is a full-length wuxia novel created by writer Jin Yong '); INSERT INTO 'springboot'. 'book' (' id ', 'author ',' name ', 'price ',' create_time ', 'description') VALUES (3, 'springboot ',' description ') 'Romance of The Three Kingdoms ', 22, '2018-09-01 10:10:16',' is a long historical novel created by the writer Luo Guanzhong '); INSERT INTO 'springboot'. 'book' (' id ', 'author ',' name ', 'price ',' create_time ', 'description') VALUES (4, 'springboot ',' description ') 17, '2018-09-01 10:10:19', 'it is a full-length novel created by wu Cheng 'en '); INSERT INTO `springboot`.`book`(`id`, `author`, `name`, `price`, `create_time`, `description`) VALUES (5, 'Jin Yong 1535767819284',' Laugh the Ao Jiang Hu 1535767819284', 43, '2018-09-01 10:10:19', 'is a full-length martial arts novel created by writer Jin Yong 1535767819284'); INSERT INTO `springboot`.`book`(`id`, `author`, `name`, `price`, `create_time`, `description`) VALUES (6, 'Jin Yong 1535767819679',' Laugh the Ao Jiang Hu 1535767819679', 24, '2018-09-01 10:10:20', 'is a full-length martial arts novel created by writer Jin Yong 1535767819679'); INSERT INTO `springboot`.`book`(`id`, `author`, `name`, `price`, `create_time`, `description`) VALUES (7, 'Luo Guanzhong 1535769035138',' Romance of The Three Kingdoms 1535769035138', 20, '2018-09-01 10:30:35', 'is a novel created by Luo Guanzhong 1535769035138'); INSERT INTO `springboot`.`book`(`id`, `author`, `name`, `price`, `create_time`, `description`) VALUES (9, 'Jin Yong 1535783613226',' Laugh the Ao Jiang Hu 1535783613226', 30, '2018-09-01 14:33:33', 'is a full-length martial arts novel created by writer Jin Yong 1535783613226'); INSERT INTO `springboot`.`book`(`id`, `author`, `name`, `price`, `create_time`, `description`) VALUES (10, 'Jin Yong 1535783618455',' Laugh the Ao Jiang Hu 1535783618455', 30, '2018-09-01 14:33:38', 'is a full-length martial arts novel created by writer Jin Yong 1535783618455'); INSERT INTO `springboot`.`book`(`id`, `author`, `name`, `price`, `create_time`, `description`) VALUES (11, 'Jin Yong 1535783620634',' Laugh the Ao Jiang Hu 1535783620634', 30, '2018-09-01 14:33:41', 'is a full-length martial arts novel created by writer Jin Yong 1535783620634'); INSERT INTO `springboot`.`book`(`id`, `author`, `name`, `price`, `create_time`, `description`) VALUES (12, 'Jin Yong 1535783672457',' Laugh the Ao Jiang Hu 1535783672457', 30, '2018-09-01 14:34:32', 'is a full-length martial arts novel created by writer Jin Yong 1535783672457'); INSERT INTO `springboot`.`book`(`id`, `author`, `name`, `price`, `create_time`, `description`) VALUES (13, 'Jin Yong 1535783673664',' Laugh the Ao Jiang Hu 1535783673664', 30, '2018-09-01 14:34:34', 'is a full-length martial arts novel created by writer Jin Yong 1535783673664'); INSERT INTO `springboot`.`book`(`id`, `author`, `name`, `price`, `create_time`, `description`) VALUES (14, 'Jin Yong 1535783939262',' Laughing ao Jiang Hu 1535783939262', 30, '2018-09-01 14:38:59', 'is a full-length martial arts novel created by writer Jin Yong 1535783939262');Copy the code

Introduction of depend on

In addition to creating Springboot project, maven dependency is introduced, mainly myBastis core dependency, Mybatis Mapper automatic generation plug-in, paging plug-in, general mapper plug-in. See Article 9 of this series for the Druid data source section of dependencies.

 <dependencies>
        <! -- Spring Boot Web Development integration -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
            <exclusions>
                <exclusion>
                    <artifactId>spring-boot-starter-json</artifactId>
                    <groupId>org.springframework.boot</groupId>
                </exclusion>
            </exclusions>
        </dependency>

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

        <! -- Ali Fastjson -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.47</version>
        </dependency>

        <! -- Lombok tools -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>

        <! The springBoot file handler will be displayed when the springboot file is configured.
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-configuration-processor</artifactId>
            <optional>true</optional>
        </dependency>

        <! Add database link -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>

        <! -- Druid data source -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.10</version>
        </dependency>

        <! -- mybatis -->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.2</version>
        </dependency>

        <! Mybatis mapper plugin -->
        <dependency>
            <groupId>org.mybatis.generator</groupId>
            <artifactId>mybatis-generator-core</artifactId>
            <version>1.3.7</version>
            <scope>compile</scope>
            <optional>true</optional>
        </dependency>

        <! -- Mybatis PageHelper plugin -->
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper-spring-boot-starter</artifactId>
            <version>1.2.10</version>
        </dependency>
        <dependency>
            <groupId>tk.mybatis</groupId>
            <artifactId>mapper-spring-boot-starter</artifactId>
            <version>2.1.5</version>
        </dependency>

        <! -- Mybatis Mapper -->
        <dependency>
            <groupId>tk.mybatis</groupId>
            <artifactId>mapper</artifactId>
            <version>4.0.4</version>
        </dependency>

    </dependencies>
Copy the code

Briefly explain the role of a few uncommon dependencies.

1. Mybatis -generator-core is used to automatically generate model, Mapper interface and Mapper XML.
2. Pagehelper-spring-boot-starter is used for paging
3. Mapper is used to enhance the function of adding, deleting, modifying, and querying. It integrates many common operations.

Increase the configuration

Configuration of the Druid data source is not explained anymore. See Article 9 of this series. In the configuration, the project code, data source information, mapper location of DurID data source and Mybatis and package path of myBatis mapping alias are mainly configured. There is also the PageHelper paging plug-in section.

# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
# service startup port number
server.port=8080
spring.profiles.active=dev
# code
server.tomcat.uri-encoding=utf-8
spring.http.encoding.force=true
spring.http.encoding.charset=UTF-8
spring.http.encoding.enabled=true
# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
spring.datasource.url=JDBC: mysql: / / 127.0.0.1:3306 / springboot? characterEncoding=utf-8&serverTimezone=GMT%2B8
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.username=root
spring.datasource.password=123
# Use druid data source
spring.datasource.type:com.alibaba.druid.pool.DruidDataSource
spring.datasource.initialSize:5
spring.datasource.minIdle:5
spring.datasource.maxActive:20
spring.datasource.maxWait:60000
spring.datasource.timeBetweenEvictionRunsMillis:60000
spring.datasource.minEvictableIdleTimeMillis:300000
spring.datasource.validationQuery:SELECT 1 FROM DUAL
spring.datasource.testWhileIdle:true
spring.datasource.testOnBorrow:false
spring.datasource.testOnReturn:false
spring.datasource.poolPreparedStatements:true
spring.datasource.filters:stat
spring.datasource.maxPoolPreparedStatementPerConnectionSize:20
spring.datasource.useGlobalDataSourceStat:true
spring.datasource.connectionProperties:druid.stat.mergeSql=true; druid.stat.slowSqlMillis=500
# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
# mybatis
mybatis.mapper-locations=classpath:mapper/*.xml
mybatis.type-aliases-package=net.codingme.boot.domain
# # # # # # # # # # # # # # mybatis configuration page # # # # # # # # # # # # # # # # # # # # # # #
Load the jar package included in the include.
restart.include.mapper=/mapper-[\\w-\\.]+jar
restart.include.pagehelper=/pagehelper-[\\w-\\.]+jar
# mappers separate multiple interfaces by commas
mapper.mappers=net.codingme.boot.util.MybatisMapper
mapper.not-empty=false
mapper.identity=MYSQL
# pagehelper
pagehelper.helperDialect=mysql
pagehelper.reasonable=true
pagehelper.supportMethodsArguments=true
pagehelper.params=count=countSql
Mybatis SQL log
logging.level.net.codingme.boot.domain.mapper=debug
Copy the code

Some notes.

1. mapper.mappers=net.codingme.boot.util.MybatisMapperUse to include a self-written Mapper.
2. restart.includeHot deployment
3. logging.level.net.codingme.boot.domain.mapper=debugMybatis SQL Mybatis SQL Mybatis SQL

General Mapper

Automatic generation was also demonstrated in the last article, which is a common method of generation, but this time we will introduce a common Mapper to generate more concise code.

To help you understand, take a look at the final structure of the project.

Automatic build configuration

Automatic general-purpose interface generation is a two-step process. The first step is to write the build configuration file. The comments have been added and you can look directly at the code.

      
<! DOCTYPEgeneratorConfiguration
        PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
        "http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">

<generatorConfiguration>
    <context id="MysqlContext" targetRuntime="MyBatis3Simple" defaultModelType="flat">
        <property name="beginningDelimiter" value="`"/>
        <property name="endingDelimiter" value="`"/>
        <property name="javaFileEncoding" value="UTF-8"/>
        <! -- whether to use the annotation tool provided by the universal Mapper, default true, so that the generated code will include the annotation of the field (currently only mysql and Oracle support) -->
        <property name="useMapperCommentGenerator" value="true"/>

        <plugin type="tk.mybatis.mapper.generator.MapperPlugin">
            <! -- Mapper interface automatically inherits this interface -->
            <property name="mappers" value="tk.mybatis.mapper.common.MySqlMapper"/>
        </plugin>

        <plugin type="tk.mybatis.mapper.generator.MapperPlugin">
            <! -- Mapper interface automatically inherits this interface -->
            <property name="mappers" value="tk.mybatis.mapper.common.Mapper"/>
            <! -- case sensitive, default false -->
            <property name="caseSensitive" value="true"/>
        </plugin>

        <jdbcConnection driverClass="com.mysql.cj.jdbc.Driver"
                        connectionURL="JDBC: mysql: / / 127.0.0.1:3306 / springboot? characterEncoding=utf-8&amp;serverTimezone=GMT%2B8&amp;nullCatalogMeansCurrent=true"
                        userId="root"
                        password="123">
        </jdbcConnection>

        <! -- PoJOs generated in package -->
        <javaModelGenerator targetPackage="net.codingme.boot.domain" targetProject="src/main/java"/>

        <! Mapper -->
        <sqlMapGenerator targetPackage="mapper" targetProject="src/main/resources"/>

        <! Mapper -->
        <javaClientGenerator targetPackage="net.codingme.boot.domain.mapper" targetProject="src/main/java"
                             type="XMLMAPPER"/>

        <! To generate those tables (change tableName and domainObjectName) -->
        <table tableName="book" domainObjectName="Book"/>

    </context>
</generatorConfiguration>
Copy the code

Unlike last time, two plugins have been added, and the resulting Mapper interface automatically inherits these classes.

Auto-generated code

Once the configuration file is written, you need to write a generator that loads the configuration file and runs it to generate the relevant entity classes, Mapper interfaces, and Mapper XML.

import org.mybatis.generator.api.MyBatisGenerator;
import org.mybatis.generator.config.Configuration;
import org.mybatis.generator.config.xml.ConfigurationParser;
import org.mybatis.generator.internal.DefaultShellCallback;
import java.io.File;
import java.util.ArrayList;
/** * <p> * Reverse generation of Mybatis Generator class **@Author niujinpeng
 */
public class MybatisGenerator {

    public void generator(a) throws Exception {
        ArrayList<String> warnings = new ArrayList<>();
        boolean overwrite = true;
        // Specify the project configuration file you want to project
        File configFile = new File("generatorConfig.xml");
        System.out.println(configFile.getAbsolutePath());
        ConfigurationParser cp = new ConfigurationParser(warnings);
        Configuration config = cp.parseConfiguration(configFile);
        DefaultShellCallback callback = new DefaultShellCallback(overwrite);
        MyBatisGenerator myBatisGenerator = new MyBatisGenerator(config, callback, warnings);
        myBatisGenerator.generate(null);
        for(String warning : warnings) { System.out.println(warning); }}public static void main(String[] args) throws Exception {
        MybatisGenerator mybatisGenerator = newMybatisGenerator(); mybatisGenerator.generator(); }}Copy the code

Automatic result generation

After running the above program, book.java is automatically generated.

@Table(name = "book")
@ToString // Manually added toString annotations
public class Book {
    @Id
    private Integer id;

    /** * Book author */
    private String author;

    /** * Book name */
    private String name;

    /** * Book price */
    private Float price;

    // Omit the auto-generated code below
Copy the code

General Mapper

The above program also automatically generates the BookMapper interface and inherits the configured MySqlMapper and Mapper interfaces.

import net.codingme.boot.domain.Book;
import org.springframework.stereotype.Repository;
import tk.mybatis.mapper.common.Mapper;
import tk.mybatis.mapper.common.MySqlMapper;

@Repository
public interface BookMapper extends MySqlMapper<Book>, Mapper<Book> {}Copy the code

Many common operations are implemented in these two interfaces.

After the build is complete, add a MapperScan annotation to the Springboot launcher to specify the mapper location to scan.

@tk.mybatis.spring.annotation.MapperScan(basePackages = "net.codingme.boot.domain.mapper")
@SpringBootApplication
public class BootApplication {

    public static void main(String[] args) { SpringApplication.run(BootApplication.class, args); }}Copy the code

Unit testing and paging testing

Write BookMapperTest unit tests to test BookMapper’s methods.

package net.codingme.boot.domain.mapper;

import com.github.pagehelper.Page;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import net.codingme.boot.domain.Book;
import org.junit.Assert;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import java.util.List;

@RunWith(SpringRunner.class)
@SpringBootTest
public class BookMapperTest {

    @Autowired
    private BookMapper bookMapper;

    @Test
    public void testSelectOne(a) {
        Book book = new Book();
        book.setId(2);
        Book selectOne = bookMapper.selectOne(book);
        Assert.assertNotNull(selectOne);
        System.out.println(selectOne);
    }

    @Test
    public void testSelectByPrimaryKey(a) {
        Book book = bookMapper.selectByPrimaryKey(2);
        Assert.assertNotNull(book);
        System.out.println(book);
    }

    /** ** paging test */
    @Test
    public void testSelectPageInfo(a) {
        PageHelper.startPage(2.3);
        List<Book> bookList = bookMapper.selectAll();
        Assert.assertNotNull(bookList);
        System.out.println("Query quantity:" + bookList.size());
        PageInfo<Book> pageInfo = PageInfo.of(bookList);
        System.out.println("Total quantity:" + pageInfo.getTotal());
        System.out.println("Total pages:" + pageInfo.getPages());
        System.out.println("Page size:" + pageInfo.getPageSize());
        System.out.println("What page:" + pageInfo.getPageNum());
        System.out.println("Current quantity:" + pageInfo.getSize());
    }

    /** ** paging test */
    @Test
    public void testSelectPage(a) {
        PageHelper.startPage(2.3);
        List<Book> bookList = bookMapper.selectAll();
        Assert.assertNotNull(bookList);
        System.out.println("Query quantity:" + bookList.size());
        System.out.println("Total quantity:" + ((Page)bookList).getTotal());
        System.out.println("Total pages:" + ((Page)bookList).getPages());
        System.out.println("What page:"+ ((Page)bookList).getPageNum()); }}Copy the code

As you can see from the code, the implementation of pagination is mainly PageHelper Settings, and the first query after PageHelper is set will be paginated. Examples like the one above query the second page, three times per page.

PageHelper.startPage(2.3);
List<Book> bookList = bookMapper.selectAll();
Copy the code

The data type returned by using the paging plug-in is a Page class. The total number of pages is already returned. If you want to use the paging plug-in, you need to cast the data type and then fetch it.

1 / / way
PageInfo<Book> pageInfo = PageInfo.of(bookList);
System.out.println("Total quantity:" + pageInfo.getTotal());
System.out.println("Total pages:" + pageInfo.getPages());
System.out.println("Page size:" + pageInfo.getPageSize());
System.out.println("What page:" + pageInfo.getPageNum());
System.out.println("Current quantity:" + pageInfo.getSize());

2 / / way
System.out.println("Query quantity:" + bookList.size());
System.out.println("Total quantity:" + ((Page)bookList).getTotal());
System.out.println("Total pages:" + ((Page)bookList).getPages());
System.out.println("What page:" + ((Page)bookList).getPageNum());
Copy the code

Run the BookMapperTest class to test all the unit tests.

See the output of a paging query (testSelectPageInfo) when the unit tests all pass.

The 2019-03-08 16:07:52. 26764-226 the DEBUG [main] N.C.B.D.M.B ookMapper. SelectAll_COUNT: = = > Preparing: SELECT count (0) FROM the book the 2019-03-08 16:07:52. 26764-227 the DEBUG [main] N.C.B.D.M.B ookMapper. SelectAll_COUNT: = = > Parameters: the 2019-03-08 16:07:52. 26764-229 the DEBUG [main] N.C.B.D.M.B ookMapper. SelectAll_COUNT: < = = Total: 1 the 16:07:52 2019-03-08. 26764-231 the DEBUG [main] N.C.B.D.M apper. BookMapper. SelectAll: = = > Preparing: SELECT id,author,name,price,create_time,description FROM book LIMIT ? ,? The 2019-03-08 16:07:52. 26764-233 the DEBUG [main] N.C.B.D.M apper. BookMapper. SelectAll: = = > the Parameters: 3 (Integer) and 3 (Integer) 16:07:52 2019-03-08. 26764-236 the DEBUG [main] N.C.B.D.M apper. BookMapper. SelectAll: < = = Total: 3 Query Quantity: 3 Total Number of pages: 12 Total number of pages: 4 Size: 3 Page: 2 Current Quantity: 3Copy the code

Take another look at the output of a normal query (testSelectByPrimaryKey).

The 2019-03-08 16:07:52. 26764-241 the DEBUG [main] N.C.B.D.M.B ookMapper. SelectByPrimaryKey: = = > Preparing: SELECT id,author,name,price,create_time,description FROM book WHERE id = ? The 2019-03-08 16:07:52. 26764-242 the DEBUG [main] N.C.B.D.M.B ookMapper. SelectByPrimaryKey: = = > the Parameters: 2 (Integer) 16:07:52 2019-03-08. 26764-244 the DEBUG [main] N.C.B.D.M.B ookMapper. SelectByPrimaryKey: < = = Total: 1 Book(id=2, author= name, price=12.0, createTime=Sat Sep 01 10:10:12 GMT+08:00 2018, Description = a full-length martial arts novel by writer Jin Yong)Copy the code

Github Spring Boot – Mybatis

For additional information on these plug-ins, check out the official documentation.

1. How do I use paging plug-ins
2. Mapper plug-in
3. MyBatis Generator

After < >

Hello world 🙂

I am a lang, lang of the moon, a technical tool person who moves bricks every day. Personal website: www.wdbyte.com If you want to subscribe, you can follow the public account of “Procedural Ape Alang”, or the blog of procedural ape Alang, or add me on wechat (WN8398).

This article has also been compiled at GitHub.com/niumoo/Java… Welcome Star.