MyBatis N+1 paging mode, a new MyBatis paging mode

Posted by Jasp182 on Mon, 24 Jan 2022 19:28:17 +0100

In the world of MyBatis, the native paging is based on RowBounds. Although it can achieve the purpose of paging, it is still not so convenient to use Later, the developer added the streaming query method. Although it can solve some problems, it still doesn't feel like the desired paging method On the other hand, Java programmers have given many excellent paging schemes, such as Mybatis-PageHelper The world is wonderful because it is different. Today brings a new way of paging I hope to bring users a new way to use api

mybatis-nosugar

Thanks to jdk1 8, the new paging mode can be used

Paging Api

Api definition supports up to five parameters, and more than five can use type cast Or expand it yourself
Support the query method defined by the original Mapper

Page<T> page = mapper.selectPagePX(Page<T>, mapper::Of the original query method lambda quote, Original parameter list...);

Simple example

Page<Movie> page = mapper.selectPageP2(new PageImpl<>(2, 5), mapper::findByLocationAndScoreGreaterThan, "US", 9.0);

Mode of use

Introduce Maven dependency

<dependency>
    <groupId>com.nosugarice</groupId>
    <artifactId>mybatis-nosugar-spring</artifactId>
    <version>${nosugar.version}</version>
</dependency>

Based on the original configuration of mybatis spring, replace factoryBean with MybatisMapperFactoryBean in NoSugar

@MapperScan(basePackages = {"com.xxx"}, factoryBean = MybatisMapperFactoryBean.class)
public class MyBatisConfiguration {
}

Inherit interface basemapper < T, ID >

public interface MovieMapper extends BaseMapper<Movie, Integer> {
}

Sample data

Entity class

@Table(name = "movie")
public class Movie implements Serializable {

    private static final long serialVersionUID = -8793166370987026047L;

    /** Primary key */
    @Id
    @Column(name = "id", nullable = false)
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    /** name */
    @Column(name = "name", nullable = false)
    private String name;

    /** region */
    @Column(name = "location", nullable = false)
    private String location;

    /** duration */
    @Column(name = "length", nullable = false)
    private Integer length;

    /** Issue date */
    @Column(name = "release_date", nullable = false)
    private LocalDate releaseDate;

    /** Ranking */
    @Column(name = "ordinal", nullable = false)
    private Integer ordinal;

    /** score */
    @Column(name = "score", nullable = false)
    private Double score;

    /** category */
    @Column(name = "category", nullable = false)
    private String category;

    ...
}

Mapper interface

public interface MovieMapper extends BaseMapper<Movie, Integer> {

    List<Movie> findByCategoryIsNotNull();

    List<Movie> findByLocation(String location);

    List<Movie> findByLocationAndScoreGreaterThan(String location, Double score);

    List<Movie> findByLocationAndScoreGreaterThanAndCategoryContains(String location, Double score, String category);

}

Database initialization SQL

DROP TABLE movie IF EXISTS;
CREATE TABLE movie
(
    id           INTEGER GENERATED BY DEFAULT AS IDENTITY,
    name         VARCHAR(100) NOT NULL,
    location     VARCHAR(100) NOT NULL,
    length       INTEGER      NOT NULL,
    release_date DATE         NOT NULL,
    ordinal      INTEGER      NOT NULL,
    score        DOUBLE       NOT NULL,
    category     VARCHAR(20)  NULL,
    PRIMARY KEY (id)
);

INSERT INTO movie VALUES (0, 'The Shawshank Redemption', 'US', 142, '1994-09-23', 1, 9.7, 'plot');
INSERT INTO movie VALUES (1, 'Farewell to my concubine', 'CHN', 171, '1993-07-26', 2, 9.6, 'plot');
INSERT INTO movie VALUES (2, 'Forrest Gump ', 'US', 142, '1994-06-23', 3, 9.5, 'plot');
INSERT INTO movie VALUES (3, 'Beautiful life ', 'IT', 116, '1997-12-20', 4, 9.6, '');
INSERT INTO movie VALUES (4, 'Spirited away ', 'JP', 125, '2001-07-20', 5, 9.4, 'Fantasy');
INSERT INTO movie VALUES (5, 'Titanic ', 'US', 194, '1997-12-19', 6, 9.4, 'love');
INSERT INTO movie VALUES (6, 'Léon', 'FR', 133, '1994-09-14', 7, 9.4, 'Crime');
INSERT INTO movie VALUES (7, 'Interstellar crossing', 'US', 169, '2014-11-12', 8, 9.4, 'science fiction');
INSERT INTO movie VALUES (8, 'Infernal Affairs', 'CHN', 101, '2003-09-05', 9, 9.3, 'Crime');
INSERT INTO movie VALUES (9, 'Zootopia', 'US', 109, '2016-03-04', 10, 9.2, 'comedy');
INSERT INTO movie VALUES (10, 'Wall-E', 'US', 98, '2008-06-27', 11, 9.3, 'animation');
INSERT INTO movie VALUES (11, 'Roman Holiday', 'US', 118, '1953-08-20', 12, 9.1, 'love');
INSERT INTO movie VALUES (12, 'Talk about the wedding of the great sage of the journey to the West', 'CHN', 95, '1995-02-04', 13, 9.2, 'plot');
INSERT INTO movie VALUES (13, 'Moonlight treasure box of Dahua journey to the West', 'CHN', 87, '1995-01-21', 14, 9.0, 'comedy');
INSERT INTO movie VALUES (14, 'Lord of the rings 3', 'US', 201, '2003-12-17', 15, 9.3, null);

Paging example based on 0 parameters

Page<Movie> page = mapper.selectPageP0(new PageImpl<>(2, 5), mapper::findByCategoryIsNotNull);

MovieMapper.adapterCount   : ==>  Preparing: SELECT COUNT(*) FROM movie WHERE category IS NOT NULL
MovieMapper.adapterCount   : ==> Parameters: 
MovieMapper.adapterCount   : <==      Total: 1
MovieMapper.adapterPage    : ==>  Preparing: SELECT id AS "id", name AS "name", location AS "location", length AS "length", release_date AS "releaseDate", ordinal AS "ordinal", score AS "score", category AS "category" FROM movie WHERE category IS NOT NULL OFFSET 5 LIMIT 5
MovieMapper.adapterPage    : ==> Parameters: 
MovieMapper.adapterPage    : <==      Total: 5

Paging example based on three parameters

Page<Movie> page = mapper.selectPageP3(new PageImpl<>(2, 2), mapper::findByLocationAndScoreGreaterThanAndCategoryContains, "US", 9.0, "feeling");

MovieMapper.adapterCount   : ==>  Preparing: SELECT COUNT(*) FROM movie WHERE location = ? AND score > ? AND category LIKE ?
MovieMapper.adapterCount   : ==> Parameters: US(String), 9.0(Double), %feeling%(String)
MovieMapper.adapterCount   : <==      Total: 1
MovieMapper.adapterPage    : ==>  Preparing: SELECT id AS "id", name AS "name", location AS "location", length AS "length", release_date AS "releaseDate", ordinal AS "ordinal", score AS "score", category AS "category" FROM movie WHERE location = ? AND score > ? AND category LIKE ? OFFSET 2 LIMIT 2
MovieMapper.adapterPage    : ==> Parameters: US(String), 9.0(Double), %feeling%(String)
MovieMapper.adapterPage    : <==      Total: 2

New Count query

Based on SelectMapper#adapterCount

Open Count query on the original query method

Example

long count=mapper.countP3(mapper::findByLocationAndScoreGreaterThanAndCategoryContains, "US", 9.0, "feeling");

be careful

Except for the parameter referenced by the first method, other parameter types of the called method should correspond to the original method

New exists query

Based on SelectMapper#adapterExists

Open the Exists query on the original query method

Example

Optional<Integer> optional = mapper.existsP3(mapper::findByLocationAndScoreGreaterThanAndCategoryContains, "US", 9.0, "feeling");
Assertions.assertTrue(optional.isPresent());

Insufficient

The SQL optimization of paging statements is only a simple process without too much optimization. Users can introduce other libraries to parse SQL analysis and optimize Count statements semantically Implement dialect#optimizementcountsql to optimize This piece is for the user

NoSugar is more than that

Sugar free formula
Opening a large number of interfaces gives developers great freedom to adapt according to their own programs You don't have to be the same
The performance is very silky. Most functions surpass the dynamic label. The more parameters, the more obvious the performance improvement
It is easy to use, does not affect the original project, does not need to modify the original Mybatis class declaration, and does not reconstruct any Mybatis basic configuration class. It can be started by adding an attribute configuration
Seamlessly enhance the existing Mybatis project (+ function), even if the current project is using other Mybatis frameworks, it can still be enhanced
Some functions, such as paging, Count query and JPA query according to the method name, can be selected separately
Basic addition, deletion, modification and query
Conditional construction
Primary key policy on insertion
Batch enhanced mode
New general paging mode without plug-ins
New general Count query method
Soft delete
Optimistic lock
Dynamic table name
Easier to use value processor
Jpa type: query and delete according to the method name

Topics: Java Mybatis

Programmer Think

MyBatis N+1 paging mode, a new MyBatis paging mode

Paging Api

Simple example

Mode of use

Sample data

Paging example based on 0 parameters

Paging example based on three parameters

New Count query

Based on SelectMapper#adapterCount

Example

be careful

New exists query

Based on SelectMapper#adapterExists

Example

Insufficient

NoSugar is more than that

Hot Topics