MyBatis SpringBoot integration

The thing is, JPA is simple to use but has been writing SQL for over a year. I decided to use MyBatis secretly in the new project until I found out that our platform framework didn’t allow it. Write down what you find so you can refer to it next time.

Rely on

The first step in use is to find dependencies. MyBatis officially provides an integrated Starter for SpringBoot; Pagehelper is also used when using MyBatis to reduce the inconvenience of pagination. The datetime type is used in the database, and a typehandler related package is introduced to take advantage of java8’s new date and time classes. As follows:

        <! -- mybatis starter -->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.0.1</version>
        </dependency>
        <! -- page helper -->
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper</artifactId>
            <version>5.1.11</version>
        </dependency>
        <! -- type handler -->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis-typehandlers-jsr310</artifactId>
            <version>1.0.2</version>
        </dependency>
Copy the code

In version, for some reason only SpringBoot2.0.x can be used, so starter is version 2.0.1. The other two dependent versions are the latest ones.

usage

Once you’ve introduced dependencies, you can start writing code.

First come a set of three workers: new entity, DAO, service, controller, config packages. Annotated writing became very popular as SpringBoot became widely used. So instead of writing mapper in the Resources directory, annotate @mapperscan (“xx.xx.dao”) in the startup class, or write @mapper on each map in the DAO layer.

Write SQL

Then write XxxMapper interface in dao package, ordinary SQL can use @SELECT ‘ ‘@INSERT’ ‘@update’ ‘@delete and other annotations, detailed description view official documentation. Dynamic SQL uses @insertProvider @updateProvider @deleteProvider @selectProvider annotations:

// type writes the class name. Method writes the method name
@SelectProvider(type = ParkingDynamicSqlProvider.class, method = "findAllSql")
Copy the code

Such as:

public class ParkingDynamicSqlProvider  {

    public String findAllSql(final Integer projectId, final String name, final Integer parent, final String sort) {
        return new SQL() {{
            SELECT("p.id, p.name, p.total_space_num, p.fixed_space_num, p.temporary_space_num, p.reserved_space_num, p.parent, p.project_id");
            FROM("pm_parking p");
            WHERE("project_id = #{projectId}");
            if(name ! =null) {
                WHERE("p.name = #{name}");
            }
            if(parent ! =null) {
                WHERE("p.parent = #{parent}"); } ORDER_BY(sort); }}.toString(); }}Copy the code

The resulting mapping uses the @Results annotation to match the column name in the database to the entity variable name.

    @Results({
            @Result(property = "totalSpaceNum", column = "total_space_num")})Copy the code

paging

For some reason, my interface restricts the use of start (initial primary key ID) and limit (number of rows) in front end request for paging, and the return of total (total query) and result list in back end response. So I only focus on RowBounds(PageRowBounds). All other paging methods are page number +limit.

The DAO layer paging query method is written in a non-paging manner. You simply add PageRowBounds instances (offset, limit, count=true) to the dao layer parameter and use paging.

PageHelper official usage document

Type processing

Mysql dateTime createDatetime; mysql dateTime createDatetime; mysql dateTime createDatetime; .