SpringBoot 2.4 integrates with the Mybatis linked database

This article assumes a good knowledge of Spring Boot and REST apis. It will help you integrate MyBatis into the Spring-Boot project and develop some examples of interacting with the MySQL database. By the end of this tutorial, you will have all the capabilities you need to create basic interactions with relational databases (including transactions and pool size limits) and unit test interactions with MySQL databases.

For this tutorial, we’ll start by creating a basic instance of Spring Boot

Quick Instance creation

Go to start.spring. IO/as shown below

Click GENERATE to GENERATE a zip extract and import the IDEA tool

Of course we also need to create a MySQL database to run on your MacOS/Linux/ Win etc

• Install mysql 5.7.28-Winx64 for Windows
• Install Mysql5.7.20 for Linux
• Cross-platform one-click startup of common MySQL/Redis/Rabbit environments

Initialize our database

resources/init.sqlThe script will initialize the database structure and data for you:

CREATE DATABASE IF NOT EXISTS `spring_boot` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

USE `spring_boot`;

DROP TABLE IF EXISTS `mybatis_test`;

CREATE TABLE `mybatis_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(255) NOT NULL,
  `en_name` varchar(255) NOT NULL.PRIMARY KEY (`id`)
);

INSERT INTO `mybatis_test` VALUES (1.'Goyes'.'Yes.Guo'), (2.'Gamora'.'Mola.Guo');
Copy the code

Create the myBatis_test entity

@Data
public class MybatisTest implements Serializable {
    
    private static final long serialVersionUID = 8897018268303812871L;

    private Long id;

    private String firstName;

    private String enName;
}
Copy the code

Integrate MyBatis and configure Spring data sources

First, let’s add some needed dependencies

		<dependency>
			<groupId>org.projectlombok</groupId>
			<artifactId>lombok</artifactId>
			<optional>true</optional>
		</dependency>
		<dependency>
			<groupId>org.mybatis.spring.boot</groupId>
			<artifactId>mybatis-spring-boot-starter</artifactId>
			<version>2.1.4</version>
		</dependency>
		<! -- Database -->
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>8.0.14</version>
		</dependency>
Copy the code

Lombok dependencies save you extra getters or equals methods. The Mybatis – spring-boot-Starter dependency adds MyBatis to your project. The mysql-connector-java dependency provides the driver required to communicate with the mysql database. If other databases are used, additional dependencies are required. Next, we need to configure our data source (in application.properties), which will allow us to easily configure transaction management, pool size and connection timeouts and many other things with minimal configuration:

spring.datasource.url=jdbc:mysql://alibaba.com:3306/spring_boot?useSSL=false
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.hikari.maximum-pool-size=10
Copy the code

The server address in the code changes itself locally. That’s all we need to connect to the database at run time, now let’s learn how to create MyBatis Mappers.

Create Mappers

Mappers is the interface that allows us to map SQL queries to Java methods. There are two types of mappings:

• Annotation-driven (simplest)
• XML (better for complex and/or dynamic queries)

We’ll learn about both in this article, but let’s start with a simpler annotation-driven mapping.

Annotation-driven mapper

Create the following TestMapper.java interface that defines two query mappings and one insert mapping:

@Mapper
public interface TestMapper {
    
    @Select("SELECT id, first_name as firstName, en_name as enName FROM mybatis_test WHERE id = #{id}")
    MybatisTest selectOne(long id);

    @Select("SELECT id, first_name as firstName, en_name as enName FROM mybatis_test")
    List<MybatisTest> findAll(a);

    @Insert("INSERT INTO mybatis_test (id, first_name, en_name) VALUES (#{id}, #{firstName}, #{enName})")
    // Set the object ID to the ID generated in the database
    @Options(useGeneratedKeys = true, keyColumn = "id", keyProperty = "id")
    void insertMybatisTest(MybatisTest mybatisTest);
}
Copy the code

The @mapper annotation at the top of the class tells MyBatis that the interface is a Mapper. The @SELECT and @INSERT annotations above the method signature indicate the type of query we are mapping. MyBatis provides different Options depending on the type of query (as shown in the @options annotation above insertMybatisTest()), but the big difference between @SELECT and @insert is that @SELECT returns one or more records, while @INSERT returns only the number of rows affected.

The structure of each method is very simple and will look familiar to anyone familiar with Hibernate: the query in the annotation is bound to a Java method. Now you can automatically connect the mapper to another class to invoke the query database. So let’s try it out.

Create the business interface and its implementation classes

TestService.java

public interface TestService {

    MybatisTest getMybatisTestById(Long id);

    void createMybatisTest(final MybatisTest mybatisTest);

    List<MybatisTest> getMybatisTests(a);
}
Copy the code

TestServiceImpl.java

@Service
public class TestServiceImpl implements TestService {


    private TestMapper testMapper;

    @Autowired
    public void setTestMapper(TestMapper testMapper) {
        this.testMapper = testMapper;
    }

    @Override
    public MybatisTest getMybatisTestById(Long id) {
        return testMapper.selectOne(id);
    }

    @Override
    public List<MybatisTest> getMybatisTests(a) {
        return testMapper.findAll();
    }

    @Override
    public void createMybatisTest(final MybatisTest mybatisTest) { testMapper.insertMybatisTest(mybatisTest); }}Copy the code

The control layer is used to invoke business interfaces

Tips: Writing controllers for the control layer requires importing the Spring-boot-starter-Web dependency into PUM.xml

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>
Copy the code

TestController.java

@RequestMapping("/test")
@RestController
public class TestController {

    private TestService testService;

    @Autowired
    public void setTestService(TestService testService) {
        this.testService = testService;
    }

    @GetMapping("getById/{id}")
    public MybatisTest getClient(@PathVariable("id") final Long id) {
        return testService.getMybatisTestById(id);
    }

    @PostMapping("add")
    public ResponseBean createClient(@RequestBody MybatisTest mybatisTest) {
        testService.createMybatisTest(mybatisTest);
        returnResponseBean.ok(); }}Copy the code

The enclosed sponseBean. Java

@Data
public class ResponseBean {
    private Integer status;

    private String msg;

    private Object data;

    public static ResponseBean build(a) {
        return new ResponseBean();
    }

    public static ResponseBean ok(a) {
        return new ResponseBean(200."success".null);
    }

    public static ResponseBean ok(String msg) {
        return new ResponseBean(200, msg, null);
    }

    public static ResponseBean ok(String msg, Object obj) {
        return new ResponseBean(200, msg, obj);
    }

    public static ResponseBean error(String msg) {
        return new ResponseBean(500, msg, null);
    }

    public static ResponseBean error(String msg, Object obj) {
        return new ResponseBean(500, msg, obj);
    }

    private ResponseBean(a) {}private ResponseBean(Integer status, String msg, Object obj) {
        this.status = status;
        this.msg = msg;
        this.data = obj; }}Copy the code

Now we can start the project and test it

The test interface

Postman is a very good test tools, we visit http://localhost:8080/test/getById/1 now, you should see the following results:

{
    "id": 1."firstName": "Guyers"."enName": "Yes.Guo"
}
Copy the code

Let’s test the create method. Use the following JSON to send a POST request to http://localhost:8080/test/add:

{
    "id": 3."firstName": "Dog rabbit"."enName": "Rabbit.Dog"
}
Copy the code

You should get an HTTP 200 response. To check that the data was created correctly, try GET access againhttp://localhost:8080/test/getById/3, you should see the following access results to prove that the data has been added:

XML mapper

Now that we’ve introduced an annotation-driven mapper, we’ll implement a more complex mapper using XML. This enables us to query data based on the user name. File path should be resources/mapper/TestMapper. XML is shown below:

      
<! DOCTYPEmapper PUBLIC "- / / mybatis.org//DTD Mapper / 3.0 / EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.github.demo.mapper.TestMapper">

    <select id="findByFirstName" resultType="MybatisTest" parameterType="java.lang.String">
        SELECT
            id,
            first_name as firstName,
            en_name as enName
        FROM mybatis_test
        WHERE first_name = #{value}
    </select>

</mapper>
Copy the code

Things to note: In line 3, we tell MyBatis which mapper interface will open this query. In the SELECT tag, the resultType attribute tells MyBatis to return the result as a MybatisTest entity object, while parameterType tells it that the input parameter is a string. Now we must tell MyBatis where to find the XML mapper, so add it to application.properties:

Mybatis. Mapper-locations = classpath*:/mybatis/*.xml Mybatis. Type-aliases -package= com.github.demo. Model # tells Mybatis to look for our entity class in the correct packageCopy the code

The next step is to return to the XXXMapper interface and create a method name that matches the query in our XML mapper. Add the following to testMapper.java:

List<MybatisTest> findByFirstName(String value);
Copy the code

The name of the method should correspond to the ID attribute of the query we wrote in the XML mapper. Again, it should take the String argument, and since many firstNames may have the same name (that is, the first_name column in the database is not UNIQUE), we should make it return a List. You can now execute this query just like any other method by calling findByFirstName. I added it to the business layer as well as the control layer so that you can test it through HTTP POST

Transaction configuration

Spring already has excellent transaction management that we can take advantage of with minimal effort on MyBatis. Simply add the following annotations to your Application class:

@EnableTransactionManagement
Copy the code

That’s it for now. Of course, like all Spring features, there are many configuration options for transaction management, but the data source configuration we wrote at the beginning of this article is sufficient to provide Spring with a way to create transactions at the method level. Where we want to use them, to make methods Transactional, we simply add the @Transactional annotation to them. Today, Spring’s default data source implementation is Hikari. You can find more information about the project on their Github, where you can find many customization options.