Spring Boot 2.X -SQL Database (MyBatis)

I am Xiaoxian, a non-slash youth who focuses on big data and distributed technology. I love Coding, reading, photography and life more!

Source code repository: github.com/zhshuixian/…

In the previous section “Actual SQL Database (Spring Data JPA)”, mainly introduces how to connect the Spring Data JPA to the database, and realize the operation of adding, deleting, changing and checking Data. This section will integrate MyBatis, another commonly used Java persistence layer framework, into actual Spring Boot.

Considering that MyBatis is widely used, MyBatis will be used as the main Java persistence layer framework here. Readers interested in MyBatis Plus can refer to the content in this section. Refer to its website mybatis. Plus/and its Spring Boot starter mybatis-plus-boot-starter.

This section will take the user information table as an example to practice MyBatis to connect SQL database and read and write data, mainly divided into the following several parts:

MyBatis dependency introduction
MyBatis connection MySQL
MyBatis entity class
MyBatis write, update, delete, query data
MyBatis multi – pen query, paging query
MyBatis uses mapper. XML

MySQL is used here. If you want to use other databases such as PostgreSQL, just change the corresponding dependencies and specify the Driver package. You need to have MySQL or other SQL databases installed in advance.

Refer to the article under Linux install MySQL 8: blog.csdn.net/u010974701/…

After installation, run the following command:
create database spring;
Copy the code

1. What is MyBatis

MyBatis official website: mybatis.org/mybatis-3/z… MyBatis is an excellent persistence layer framework that supports customized SQL, stored procedures, and advanced mapping. MyBatis avoids almost all of the JDBC code and manual setting of parameters and fetching result sets. MyBatis can use simple XML or annotations to configure and map native types, interfaces, and Java’s Plain Old Java Objects (POJOs) to records in the database.

As mentioned in the previous section, Spring Data JPA can perform SQL operations directly by writing the method names of the interface in accordance with the rules. The code is so straightforward that developers hardly need to jump to the Repository interface to see what the method does.

Compared to Spring Data JPA, which almost does not need to write SQL, MyBatis allows developers to write SQL flexibly, but the trouble is a pile of configuration files such as mapper.xml in the project. Even though MyBatis code generator automatically generates entity classes and associated configuration files to reduce the developer’s work, sometimes changing a table field can result in several SIMULTANEOUS changes to XML and Java code, causing the developer to switch frequently between XML configuration files and Java code.

Since then, MyBatis has done a lot of upgrade optimization to reduce the relevant configuration files by using annotations. As mentioned in the introduction “What is Spring Boot”, one of Spring Boot’s major features is AutoConfiguration, which provides almost zero configuration out of the box capability for many third-party development libraries, such as MyBatis. And MyBatis out-of-the-box Starter is MyBatis -spring-boot-starter, which makes spring boot integrate MyBatis and can achieve almost zero configuration development. Mybatis -spring-boot-starter supports the traditional mapper. XML configuration method. Support for annotations with little configuration. This section mainly uses annotations, which mapper.xml will mention at the end of the article.

MyBatis Integration with Spring Boot github.com/mybatis/spr… MyBatis spring-boot Starter will help you use MyBatis with Spring Boot.

2. Configuration of MyBatis

New project 03-SQL-mybatis, remember to check mybatis, MySQL dependencies, note that Spring Boot version should be 2.1.X.

MyBatis supports Spring Boot versions

Master (2.1.x) : MyBatis 3.5+, MyBatis-Spring 2.0+(2.0.3+ recommended), Java 8+ and Spring Boot 2.1+

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 plugin Pagehelper

Pagination in MyBatis is implemented in this section through Pagehelper.

Gradle rely on

dependencies { implementation 'org.springframework.boot:spring-boot-starter-web' implementation 'org. Mybatis. Spring. The boot: mybatis - spring - the boot - starter: 2.1.1' implementation 'com. Making. Pagehelper: pagehelper - spring - the 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' } }Copy the code

Maven rely on

  <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>
Copy the code

2.2 MySQL, MyBatis, Pagehelper configuration

Edit/SRC/main/resources/application properties file, write the following content, for the way using annotations, besides that no other configuration, as for MyBatis how to connect to the database, how to manage the connection, Mybatis -spring-boot-starter: mybatis-spring-boot-starter:

You only need to change the database URL, username, password, and JDBC Driver
MySQL 8 needs to specify serverTimezone to connect to MySQLspring.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
Mapper file address
mybatis.mapper-locations=classpath:mapper/*.xml
## pagehelper
pagehelper.helperDialect=mysql
pagehelper.reasonable=true
pagehelper.supportMethodsArguments=true
pagehelper.params=count=countSql
Copy the code

Add @mapperscan (“org.xian.boot.mapper”) to the Spring Boot Boot class *Application to automatically scan the mapper class under org.xian.boot.mapper. No need to add @mapper annotations to all Mapper classes

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

2.3. General message class MyResponse

MyResponse: general message return class, add, delete, modify the success of the operation and information return class:

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

3. Start using MyBatis

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

API Interface layer: Provides RESTful API interfaces for system external interaction. Interface service layer: The main logical part of an application. It is not recommended to write application logic on the API interface layer. Data persistence layer: write corresponding MyBatis Mapper interface to realize interaction with MySQL database.

In this section, the following RESTful apis are implemented:

/ API /user/insert: Inserts a data
/ API /user/select: queries a data
/ API /user/update: Updates a data
/ API /user/delete: deletes a data
/ API /user/selectAll: Browse all data
/ API /user/selectPage: paging browsing

3.1. Data table structures and Mapper entity classes

The table structure is the same as in the previous section:

The field name	The field type	note
user_id	bigint	Primary key, increment
username	varchar(18)	User name, not null unique
nickname	varchar(36)	User nickname, non-empty
user_age	tinyint	The user’s age
user_sex	varchar(2)	User’s gender

The 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)
);
Copy the code

Mapper Entity class: Create a package named 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 dec} {$", message = "User name must be 3 to 16 digits in English, numerals")
    private String username;

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

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

    private String userSex;
}
Copy the code

@notempty (message = “”) : cannot be empty. Message indicates null or a message with a character length of 0.

@pattern: regular expressions, for example, that you can use to verify that a user name or password conforms to the specification.

@range: specifies maximum and minimum values, such as a maximum score of 100.

3.2. Annotated Mapper interface

Create a mapper package and create a SysUserMapper interface. Unlike JPA, you need to customize your SQL using annotations or mapper.xml. The OnXml ending method is to specify SQL using mapper.xml, which is described later, and is equivalent to the method without the OnXml suffix.

public interface SysUserMapper {
    /** Insert a record * to sys_user@paramSysUser 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 based on user ID *@paramUserId userId *@returnUser 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 name, age, gender *@paramSysUser 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 based on user ID *@paramUserId userId */
    @Delete("Delete From sys_user where user_id=#{userId}")
    void delete(Long userId);
    void deleteOnXml(Long userId);

    /** View all user information *@returnAll user information */
    @Select("Select * From sys_user")
    List<SysUser> selectAll(a);
    List<SysUser> selectAllOnXml(a);
}
Copy the code

Code parsing:

@insert, @SELECT, @update, @delete annotate SQL Insert, Select, Update, Delete statements respectively.

MyBatis SQL is passed in with #{param}, param should be the same as your Java variable name. The rest of the SQL is no different from a standard SQL statement.

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

@results If MyBatis camel name conversion is not enabled or some fields do not comply with camel name conversion rules, for example, the field name in the database is user_sex but the member variable in the Java class is sex, you need to manually map the fields.

@result (property = “userId”, column = “user_id”),property specifies the Java class member variable name, column specifies the database field name.

3.3 interface Service layer Service

Added SysUserService to Package of Service:

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

    /** Save a record *@paramSysUser User information *@returnSave the result */
    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 the user ID *@paramUserId userId *@returnUser information */
    public SysUser select(Long userId) {
        return sysUserMapper.selectByUserIdOnXml(userId);
    }

    /** Update user age, gender, and nickname based on user name *@paramSysUser User information *@returnResults the * /
    public MyResponse update(SysUser sysUser) {
        try {
            sysUserMapper.update(sysUser);
            return new MyResponse("success"."Update successful");
        } catch (Exception e) {
            return new MyResponse("error", e.getMessage()); }}/** Delete user information based on user ID *@paramUserId userId *@returnResult */
    public MyResponse delete(Long userId) {
        try {
            sysUserMapper.delete(userId);
            return new MyResponse("success"."Deleted successfully");
        } catch (Exception e) {
            return new MyResponse("error", e.getMessage()); }}/** View all user information *@returnAll user information */
    public List<SysUser> selectAll(a) {
        return sysUserMapper.selectAll();
    }

     /** Paging browse *@returnOne page of user information */
    public PageInfo<SysUser> selectPage(int page,int size) {
        // PageHelper automatically paginates subsequent queries
        PageHelper.startPage(page, size);
        PageHelper.orderBy("user_id DESC");
        returnPageInfo.of(sysUserMapper.selectAllOnXml()); }}Copy the code

Code parsing:

@service: Defines a Bean. The annotated class is automatically registered with the Spring container.

@Resource: Automatic assembly of beans as opposed to the @autowired annotation.

PageHelper.startPage(page, size); When this method is called, subsequent queries automatically use paging mode.

PageInfo) of (sysUserMapper selectAllOnXml ()); The information returned by a query on a page is packaged using PageInfo.

3.4. API interface Layer

Added 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(a) {
        return sysUserService.selectAll();
    }

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

3.5. View the running effect

To run the project, use Postman to access the RESTful API:

/ API /user/insert: Inserts a data
/ API /user/select: queries a data
/ API /user/update: Updates a data
/ API /user/delete: deletes a data
/ API /user/selectAll: Browse all data
/ API /user/selectPage: paging browsing

3.6. Customize SQL statements using mapper. XML

The Mapper interface is annotated and the mapper. XML custom SQL is optimized in Spring Boot. You only need to in the resources/application. The properties specified Mapper. The location of the XML file, other configuration by MyBatis Starter (Starter) automatically.

In section 3.2 of the Mapper interface, under each method that uses annotations to customize SQL, there is a method with the OnXml suffix that will return an error if called directly from SysUserService. Before using these methods, we also need to customize the SQL fields in the mapper.xml file:

New resources/mapper/SysUserMapper. XML file:

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

      
<! Mapper class -->
<mapper namespace="org.xian.boot.mapper.SysUserMapper">
  <! ResultMap = resultMap = resultMap = resultMap = 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>
  <! -- Common part of SQL statement -->
  <sql id="SysColumn">
    user_id, username, nickname, user_age, user_sex
  </sql>
  <! ResultMap specifies the name of the member variable of the Mapper class.
  <select id="selectAllOnXml" resultMap="BaseResultMap">
    select
    <! -- Use Include to Include common SQL parts -->
    <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 Specifies the type of the incoming parameter -->
  <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>
  <! - complete code to see the source code warehouse resources of 03 - SQL - mybatis mapper/SysUserMapper. XML - >
</mapper>
Copy the code

The specific code parsing is already annotated in the source code, similar to annotations, but with the custom SQL moved into the XML file. Change SysUserService to call the OnXml suffix Mapper interface method. Re-run the project, access the corresponding API, and see how the results and annotations differ.

Afterword.

Integrating MyBatis with Spring Boot, MyBatis – Spring-boot-starter automatically does a lot of configuration, either using annotations or mapper.xml, which is very concise.

In addition, MyBatis Generator supports automatic generation of Mapper interface, mapper. XML, entity class, interested readers can search for their own.

In the next section, we will integrate RocketMQ with Live Spring Boot 2.X, with a slight delay for NoSQL.

Reference and extended reading:

Mybatis.org/mybatis-3/z…

Github.com/mybatis/spr…

mo4tech.com (Moment For Technology) is a global community with thousands techies from across the global hang out!Passionate technologists, be it gadget freaks, tech enthusiasts, coders, technopreneurs, or CIOs, you would find them all here.

Spring Boot 2.X –SQL Database (MyBatis)

1. What is MyBatis

2. Configuration of MyBatis

2.1. Paging plugin Pagehelper

2.2 MySQL, MyBatis, Pagehelper configuration

2.3. General message class MyResponse

3. Start using MyBatis

3.1. Data table structures and Mapper entity classes

3.2. Annotated Mapper interface

3.3 interface Service layer Service

3.4. API interface Layer

3.5. View the running effect

3.6. Customize SQL statements using mapper. XML

Afterword.

Spring Boot 2.X –SQL Database (MyBatis)

1. What is MyBatis

2. Configuration of MyBatis

2.1. Paging plugin Pagehelper

2.2 MySQL, MyBatis, Pagehelper configuration

2.3. General message class MyResponse

3. Start using MyBatis

3.1. Data table structures and Mapper entity classes

3.2. Annotated Mapper interface

3.3 interface Service layer Service

3.4. API interface Layer

3.5. View the running effect

3.6. Customize SQL statements using mapper. XML

Afterword.

Related Posts

PHP uses bitwise operations to implement four operations

New features that MySQL can use

How to guarantee the security of token