In the previous section Spring Data JPA In, it mainly introduces how Spring Data JPA connects to the database, and how to add, delete, modify and query the data. In this section, we will integrate MyBatis, another commonly used Java persistence layer framework, with Spring Boot.

Considering the wide application of MyBatis, MyBatis will be used as the main Java persistence layer framework here. Readers interested in MyBatis Plus can refer to this section and its official website https://mybatis.plus/ And its Spring Boot starter, mybatis plus boot starter.

This section will take the user information table as an example to realize MyBatis connecting to SQL database and reading and writing data. It is mainly divided into the following parts:

• Dependency introduction of MyBatis
• MyBatis connects to MySQL
• MyBatis entity class
• MyBatis write, update, delete, query data
• MyBatis multi query, paging query
• MyBatis uses Mapper.xml

create database spring;

1. What is MyBatis

As mentioned in the previous section, Spring Data JPA only needs to write the method name of the interface according to the rules to directly perform the corresponding SQL operation. The code is clear at a glance, and developers hardly need to jump to the Repository interface to understand the purpose of this method.

Compared with the Spring Data JPA framework, which almost doesn't need to write SQL, MyBatis is that developers can write SQL flexibly, but the trouble is a pile of Mapper.xml and other configuration files in the project. Even through MyBatis code generator, automatic generation of entity classes and related configuration files reduces the work of developers, but sometimes changing a table field may result in the need to modify several XML and Java codes at the same time, so that developers often switch between XML configuration files and Java codes.

Later, MyBatis did a lot of upgrade optimization, which can reduce the relevant configuration files by using annotations. At the beginning What is Spring Boot As mentioned in, a major feature of Spring Boot is AutoConfiguration, which provides almost zero configuration out of the box capabilities for many third-party development libraries, such as MyBatis. MyBatis is Spring Boot starter out of the box enables Spring Boot to integrate MyBatis and achieve almost zero configuration development. MyBatis Spring Boot starter supports the traditional Mapper.xml configuration method and almost no configuration annotation method. This section mainly uses annotation, Mapper.xml will be mentioned at the end of the article.

MyBatis integration with Spring Boot Official GitHub Warehouse https://github.com/mybatis/spring-boot-starter MyBatis Spring-Boot-Starter will help you use MyBatis with Spring Boot.

2. Configuration of MyBatis

For the new project 03 SQL MyBatis, please check MyBatis and MySQL dependency. Note that the Spring Boot version is 2.1.X.

MyBatis Yes Spring Boot Version support

• master(2.1.x) : MyBatis 3.5+, MyBatis-Spring 2.0+(2.0.3+ recommended), Java 8+ and Spring Boot 2.1+
• 2.0.x : MyBatis 3.5+, MyBatis-Spring 2.0+, Java 8+ and Spring Boot 2.0/2.1.
• 1.3.x : MyBatis 3.4+, MyBatis-Spring 1.3+, Java 6+ and Spring Boot 1.5

2.1. Paging plug-in Pagehelper

For the paging of MyBatis, in this section, it is implemented through Pagehelper.

Gradle depends on

dependencies {
	implementation 'org.springframework.boot:spring-boot-starter-web'
	implementation 'org.mybatis.spring.boot:mybatis-spring-boot-starter:2.1.1'
	implementation 'com.github.pagehelper:pagehelper-spring-boot-starter:1.2.13'
	compileOnly 'org.projectlombok:lombok'
	runtimeOnly 'mysql:mysql-connector-java'
	annotationProcessor 'org.projectlombok:lombok'
	testImplementation('org.springframework.boot:spring-boot-starter-test') {
		exclude group: 'org.junit.vintage', module: 'junit-vintage-engine'
	}
}

Maven dependence

  <dependencies>
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    <dependency>
      <groupId>org.mybatis.spring.boot</groupId>
      <artifactId>mybatis-spring-boot-starter</artifactId>
      <version>2.1.1</version>
    </dependency>
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <scope>runtime</scope>
    </dependency>
    <dependency>
      <groupId>org.projectlombok</groupId>
      <artifactId>lombok</artifactId>
      <optional>true</optional>
    </dependency>
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-test</artifactId>
      <scope>test</scope>
      <exclusions>
        <exclusion>
          <groupId>org.junit.vintage</groupId>
          <artifactId>junit-vintage-engine</artifactId>
        </exclusion>
      </exclusions>
    </dependency>
    <!-- https://mvnrepository.com/artifact/com.github.pagehelper/pagehelper-spring-boot-starter -->
    <dependency>
      <groupId>com.github.pagehelper</groupId>
      <artifactId>pagehelper-spring-boot-starter</artifactId>
      <version>1.2.13</version>
    </dependency>
  </dependencies>

2.2. MySQL, MyBatis, Pagehelper configuration

Edit the / src/main/resources/application.properties file and write the following contents. For the way of using annotation, there is no other configuration besides this. As for how MyBatis connects to the database, how to manage the connection, how Mapper class maps to the table, all these are handed to MyBatis spring boot starter:

# Database URL, user name, password, JDBC Driver to change the database only need to change these information
# MySQL 8 needs to specify serverTimezone to connect successfully
spring.datasource.url=jdbc:mysql://localhost:3306/spring?useUnicode=true&characterEncoding=utf8&serverTimezone=UTC
spring.datasource.password=xiaoxian
spring.datasource.username=root
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
# MyBatis hump naming conversion
mybatis.configuration.map-underscore-to-camel-case=true
# Specify the address of the Mapper file
mybatis.mapper-locations=classpath:mapper/*.xml
## pagehelper
pagehelper.helperDialect=mysql
pagehelper.reasonable=true
pagehelper.supportMethodsArguments=true
pagehelper.params=count=countSql

In the Spring Boot startup class * Application, add @ MapperScan("org.xian.boot.mapper") and automatically scan the mapper class under org.xian.boot.mapper. With this annotation, you don't need to add any more mapper classes @Mapper annotation

@SpringBootApplication
@MapperScan("org.xian.boot.mapper")
public class BootApplication {
    public static void main(String[] args) {
        SpringApplication.run(BootApplication.class, args);
    }
}

2.3. General message class MyResponse

MyResponse: general message return class. Whether the operation of adding, deleting and modifying is successful and the class of information return:

@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class MyResponse implements Serializable {
    private static final long serialVersionUID = -2L;
    private String status;
    private String message;
}

3. Start using MyBatis

The project is still divided into three layers, but the data persistence layer framework is changed to MyBatis.

< img SRC = "https://gitee.com/ylooq/image-repository/raw/master/image2020/20200308195133. PNG" ALT = "project structure" style = "zoom: 50%;" / >

API interface layer: provides a RESTful API interface, which is an interface for external interaction of the system. Interface service layer: the main logic part of the application. It is not recommended to write application logic in API interface layer. Data persistence layer: write the corresponding MyBatis Mapper interface to realize the interaction with MySQL database.

In this section, the following RESTful API interfaces will be implemented:

• /api/user/insert: insert a piece of data
• /api/user/select: query a piece of data
• /api/user/update: update a piece of data
• /api/user/delete: delete a piece of data
• /api/user/selectAll: browse all data
• /api/user/selectPage: paging browsing

3.1 data table structure and Mapper entity class

The table structure of the last section is the same:

SQL statement

-- MySQL
create table sys_user
(
	user_id bigint auto_increment,
	username varchar(18) not null,
	nickname varchar(36) not null,
	user_age tinyint null,
	user_sex varchar(2) null,
	constraint sys_user_pk
		primary key (user_id)
);

Mapper entity class: create a new package with the name of entity. Create a new SysUser class under entity:

@Data
public class SysUser implements Serializable {
    private static final long serialVersionUID = 4522943071576672084L;

    private Long userId;

    @NotEmpty(message = "User name cannot be empty")
    @Pattern(regexp = "^[a-zA-Z0-9]{3,16}$", message = "User name needs 3 to 16 digits in English,number")
    private String username;

    @NotEmpty(message = "User nickname cannot be empty")
    private String nickname;

    @Range(min=0, max=100,message = "Age needs to be between 0 and 100")
    private Integer userAge;

    private String userSex;
}

@NotEmpty(message = ''): cannot be empty, message means a prompt message when it is null or the character length is 0.

@Pattern: regular expression, for example, you can use to verify whether the user name and password meet the specification.

@Range: Specifies the maximum and minimum values, for example, the maximum score is 100.

3.2 Mapper interface in annotation mode

Create a new mapper package and a new SysUserMapper interface. Unlike JPA, you need to write SQL by annotation or mapper.xml. The method at the end of OnXml is to use mapper.xml to specify SQL, which will be mentioned later. It is the same as the method without the OnXml suffix.

public interface SysUserMapper {
    /** Insert a record into SYS user
     * @param sysUser User information
     */
    @Insert("Insert Into sys_user(username, nickname, user_age, user_sex) " +
            "Values(#{username}, #{nickname}, #{userAge}, #{userSex})")
    @Options(useGeneratedKeys = true, keyProperty = "userId")
    void insert(SysUser sysUser);
    void insertOnXml(SysUser sysUser);

    /** Query user information according to user ID
     * @param userId User ID
     * @return User information
     */
    @Select("Select user_id,username, nickname, user_age, user_sex From sys_user Where user_id=#{userId}")
    @Results({
            @Result(property = "userId", column = "user_id"),
            @Result(property = "userAge", column = "user_age"),
            @Result(property = "userSex", column = "user_sex")
    })
    SysUser selectByUserId(Long userId);
    SysUser selectByUserIdOnXml(Long userId);

    /** Update user nickname, user age and user gender information according to user name
     * @param sysUser User information
     */
    @Update("Update sys_user Set nickname=#{nickname}, user_age=#{userAge}, user_sex=#{userSex} Where username=#{username}")
    void update(SysUser sysUser);
    void updateOnXml(SysUser sysUser);

    /** Delete user information according to user ID
     * @param userId User ID
     */
    @Delete("Delete From sys_user where user_id=#{userId}")
    void delete(Long userId);
    void deleteOnXml(Long userId);

    /** Browse all user information
     * @return All user information
     */
    @Select("Select * From sys_user")
    List<SysUser> selectAll();
    List<SysUser> selectAllOnXml();
}

Code resolution:

@Insert, @ select, @ Update, @Delete annotate the insert, select, update, delete statements of SQL respectively.

MyBatis uses {param} as its SQL input parameter. The param name should be the same as your Java variable name. There is no difference between other parts of SQL and standard SQL statements.

If the Mapper interface method passes in a class, there is no need to manually use the Getter method to assign values to the incoming parameters of SQL. MyBatis automatically assigns values based on the member variable names in the class.

@Results if the MyBatis hump naming conversion is not enabled, or some fields do not conform to the hump naming conversion rules, such as: the field name in the database is user_sex, while the member variable in the Java class is sex, you need to map manually in this way.

@Result (property = user ID, column = user? ID), property specifies the member variable name of the Java class, and column specifies the field name of the database.

3.3 Service of interface Service layer

Add Package of service and SysUserService:

@Service
public class SysUserService {
    @Resource
    private SysUserMapper sysUserMapper;

    /** Save a record
     * @param sysUser User information
     * @return Save results
     */
    public MyResponse insert(SysUser sysUser) {
        try {
            sysUserMapper.insert(sysUser);
            return new MyResponse("success", "New success");
        } catch (Exception e) {
            return new MyResponse("error", e.getMessage());
        }
    }

    /** Query a record based on user ID
     * @param userId User ID
     * @return User information
     */
    public SysUser select(Long userId) {
        return sysUserMapper.selectByUserIdOnXml(userId);
    }

    /** Update user age, gender and nickname information according to user name
     * @param sysUser User information
     * @return Result
     */
    public MyResponse update(SysUser sysUser) {
        try {
            sysUserMapper.update(sysUser);
            return new MyResponse("success", "Update success");
        } catch (Exception e) {
            return new MyResponse("error", e.getMessage());
        }
    }

    /** Delete user information according to user ID
     * @param userId User ID
     * @return Operation result
     */
    public MyResponse delete(Long userId) {
        try {
            sysUserMapper.delete(userId);
            return new MyResponse("success", "Delete successful");
        } catch (Exception e) {
            return new MyResponse("error", e.getMessage());
        }
    }

    /** Browse all user information
     * @return All user information
     */
    public List<SysUser> selectAll() {
        return sysUserMapper.selectAll();
    }

     /** Tabbed Browsing
     * @return One page user information
     */
    public PageInfo<SysUser> selectPage(int page,int size) {
        // The subsequent queries performed by PageHelper will be paged automatically
        PageHelper.startPage(page, size);
        PageHelper.orderBy("user_id DESC");
        return PageInfo.of(sysUserMapper.selectAllOnXml());
    }
}

Code resolution:

@Service: define a Bean, and the annotated class will be automatically registered to the Spring container.

@Resource: relative to @ Autowired annotation, Bean is automatically assembled.

PageHelper.startPage(page, size); after calling this method, the subsequent query will automatically use paging mode.

PageInfo.of(sysUserMapper.selectAllOnXml()); it is used to pack the information returned from a query with PageInfo.

3.4 API interface layer

Add the Package of controller and SysUserController:

@RestController
@RequestMapping(value = "/api/user")
public class SysUserController {
    @Resource
    private SysUserService sysUserService;

    @PostMapping(value = "/insert")
    public MyResponse insert(@RequestBody SysUser sysUser) {
        return sysUserService.insert(sysUser);
    }

    @PostMapping(value = "select")
    public SysUser select(@RequestBody Long userId) {
        return sysUserService.select(userId);
    }

    @PostMapping(value = "/update")
    public MyResponse update(@RequestBody SysUser sysUser) {
        return sysUserService.update(sysUser);
    }

    @PostMapping(value = "delete")
    public MyResponse delete(@RequestBody Long userId) {
        return sysUserService.delete(userId);
    }

    @GetMapping("selectAll")
    public List<SysUser> selectAll() {
        return sysUserService.selectAll();
    }

    @GetMapping("selectPage")
    public PageInfo<SysUser> selectPage(@RequestParam(defaultValue = "0") Integer page,
                                        @RequestParam(defaultValue = "3") Integer size) {
        return sysUserService.selectPage(page, size);
    }
}

3.5 check operation effect

Run the project and use Postman to access the RESTful API interface:

• /api/user/insert: insert a piece of data
• /api/user/select: query a piece of data
• /api/user/update: update a piece of data
• /api/user/delete: delete a piece of data
• /api/user/selectAll: browse all data
• /api/user/selectPage: paging browsing

3.6. Use Mapper.xml to customize SQL statements

The way to specify the Mapper interface by annotation above, and the way to customize SQL by Mapper.xml has also been greatly optimized in Spring Boot. You only need to specify the location of Mapper.xml file in resources/application.properties, and other configurations will be automatically completed by the starter of MyBatis.

In Section 3.2 Mapper interface, there is a method with OnXml suffix under each method that uses annotation to customize SQL. If this method is called directly in SysUserService, an error will be reported. Before using these methods, we also need to customize SQL fields in Mapper.xml file:

Create a new resources/mapper/SysUserMapper.xml file:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!-- Appoint Mapper class -->
<mapper namespace="org.xian.boot.mapper.SysUserMapper">
  <!-- Return the field name and field type of the result, and the member variable name of the corresponding class. You can define multiple id Different values resultMap -->
  <resultMap id="BaseResultMap" type="org.xian.boot.entity.SysUser">
    <id column="user_id" jdbcType="BIGINT" property="userId"/>
    <result column="username" jdbcType="VARCHAR" property="username"/>
    <result column="nickname" jdbcType="VARCHAR" property="nickname"/>
    <result column="user_age" jdbcType="TINYINT" property="userAge"/>
    <result column="user_sex" jdbcType="VARCHAR" property="userSex"/>
  </resultMap>
  <!-- SQL General part of statement -->
  <sql id="SysColumn">
    user_id, username, nickname, user_age, user_sex
  </sql>
  <!-- Id by Mapper The member variable name of the class, resultMap Specify the return acceptance class -->
  <select id="selectAllOnXml" resultMap="BaseResultMap">
    select
    <!-- Use Include Include generic SQL Part -->
    <include refid="SysColumn"/>
    from sys_user
  </select>

  <select id="selectByUserIdOnXml" parameterType="java.lang.Long" resultMap="BaseResultMap">
    select
    <include refid="SysColumn"/>
    from sys_user Where user_id=#{userId}
  </select>
  <!-- parameterType Specify the incoming parameter type -->
  <insert id="insertOnXml" parameterType="org.xian.boot.entity.SysUser">
    Insert Into sys_user(username, nickname, user_age, user_sex)
    Values (#{username}, #{nickname}, #{userAge}, #{userSex})
  </insert>
  <!-- See source code warehouse 03 for complete code-sql-mybatis Of resources/mapper/SysUserMapper.xml -->
</mapper>

The specific code parsing has been annotated in the source code, similar to the annotation method, but the custom SQL has been moved to the XML file. Change SysUserService to call the OnXml suffix Mapper interface method. Rerun the project, access the corresponding API, and see how the results and annotations differ.

Epilogue

In Spring Boot, MyBatis is integrated. Its starter, MyBatis Spring Boot starter, automatically completes many configurations, whether using annotation or Mapper.xml.

In addition, MyBatis Generator supports automatic generation of Mapper interface, Mapper.xml, and entity classes. Interested readers can search and learn by themselves.

In the next section, we will integrate RocketMQ with Spring Boot 2.X, and slightly delay the NoSQL part.

Reference and extended reading:

https://mybatis.org/mybatis-3/zh/index.html

https://github.com/mybatis/spring-boot-starter