SpringBoot (4) : Use MyBatis to access the database

MyBatis is an excellent persistence layer framework that supports customized SQL, stored procedures, and advanced mapping. It avoids almost all of the JDBC code and manual parameter setting and result set fetching. Because MyBatis can configure and map native information using simple XML or annotations, the interface and Java’s POJOs (Plain Old Java Objects) are mapped to records in the database.

Write table structure

Since MyBatis cannot automatically generate table structures from Java code like JPA, we need to create them manually. Create the resources/db/schema. SQL file and fill in the following

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
    `id`         bigint(20) AUTO_INCREMENT NOT NULL.`first_name` varchar(255) DEFAULT NULL.`last_name`  varchar(255) DEFAULT NULL
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;
Copy the code

Introduction of depend on

The MyBatis dependency file is not in the SpringBoot automatic dependency configuration, so you need to manually specify the version number here:

<dependencys>
    <dependency>
        <groupId>org.mybatis.spring.boot</groupId>
        <artifactId>mybatis-spring-boot-starter</artifactId>
        <version>2.1.3</version>
    </dependency>
    <dependency>
        <groupId>com.h2database</groupId>
        <artifactId>h2</artifactId>
    </dependency>
</dependencys>
Copy the code

Configuring a Data Source

Schema and Initialization -mode will automatically read and execute the resources/db/schema. SQL file.

spring:
  datasource:
    url: jdbc:h2:mem:test; MODE=MYSQL
    driver-class-name: org.h2.Driver
    schema: classpath:db/schema.sql
    initialization-mode: always
Copy the code

Writing a query interface

Start by creating an entity class User whose attributes should correspond to fields in the database.

@Data
@NoArgsConstructor
@AllArgsConstructor
public class User {
    private Long id;
    private String firstName;
    private String lastName;
}
Copy the code

Create the query interface UserMapper and the corresponding resources/mybatis/user_mapper.xml

public interface UserMapper {
    List<User> findByFirstName(@Param("firstName") String firstName);
}
Copy the code

      
<! DOCTYPEmapper PUBLIC "- / / mybatis.org//DTD Mapper / 3.0 / EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="spring.boot.mybatis.mapper.UserMapper">
    <resultMap id="User" type="spring.boot.mybatis.module.User">
        <id column="id" property="id" javaType="java.lang.Long" jdbcType="INTEGER"/>
        <result column="first_name" property="firstName" javaType="java.lang.String" jdbcType="VARCHAR"/>
        <result column="last_name" property="lastName" javaType="java.lang.String" jdbcType="VARCHAR"/>
    </resultMap>
    <select id="findByFirstName" resultMap="User">
        select *
        from user
        where first_name = #{firstName}
    </select>
</mapper>
Copy the code

Add the configuration to specify the path where mapper. XML resides

mybatis:
  mapper-locations: classpath:mybatis/*.xml
Copy the code

Add annotations to the startup class and scan the Mapper interface

@MapperScan("spring.boot.mybatis.*")
Copy the code

Although MyBatis was originally designed as an XML-driven ORM framework, its configuration information is based on XML, but from MyBatis3 it is based on the powerful Java language configuration API. Support for using annotations to configure SQL and the mapping between query results and entities. So let’s take a look at using annotations to use MyBatis.

// Insert a piece of user data
@Insert("insert into user(id, first_name, last_name) values(#{id}, #{firstName}, #{lastName})")
int save(User user);

// Query all user data
@Select("select * from user")
@Results({ @Result(property = "firstName", column = "first_name", javaType = String.class), @Result(property = "lastName", column = "last_name", javaType = String.class), })
List<User> findAll(a);
Copy the code

Create an implementation class CommandLineRunner to test the query interface:

@Slf4j
@Component
public class UserRunner implements CommandLineRunner {
    @Resource
    private UserMapper userMapper;
    @Override
    public void run(String... args) {
        int changeRow = userMapper.save(new User(1L."Vincent"."Jiang"));
        log.info("Change rows: {}", changeRow);
        log.info("User: {}", userMapper.findAll());
        log.info("User: {}", userMapper.findByFirstName("Vincent")); }}Copy the code

Reference documentation

• Juejin. Cn/post / 684490…
• Developer.ibm.com/zh/tutorial…