preface

In the database development of a project, it is inevitable to use the persistence layer framework. The current mainstream persistence layer frameworks include Spring Data, Hibernate, MyBatis, etc. MyBatis is used here.

In this paper, the integration of MyBatis and the implementation of a simple add, delete, change and search. In the process of using, point out the deficiencies of MyBatis and the project, and think of solutions.

If you are familiar with the use of MyBaits, you can skip to summary.

The specific implementation

Maven rely on

<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-jdbc</artifactId>
    </dependency>

    <! -- mybatis -->
    <dependency>
        <groupId>org.mybatis.spring.boot</groupId>
        <artifactId>mybatis-spring-boot-starter</artifactId>
        <version>2.1.1</version>
    </dependency>

    <! -- mysql -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <scope>runtime</scope>
    </dependency>
</dependencies>
Copy the code

Parameter configuration

spring:
  datasource:
    driver-class-name: com.mysql.jdbc.Driver
    url: jdbc:mysql://xxxxxxxx:3306/framework? useUnicode=true&characterEncoding=utf-8
    username: xxxxxxxx
    password: xxxxxxxx
mybatis:
  mapper-locations: classpath:sql-mappers/*.xml
  configuration:
    map-underscore-to-camel-case: true
Copy the code

  • Mybatis. Mapper-locations Specifies the location of a mapper file

  • Mybatis. Configuration. The map – the underscore – to – camel hump – case for open function

    Database column name: create_time Entity class attribute: createTime

The database table

DROP TABLE IF EXISTS `sys_user`;
CREATE TABLE `sys_user` (
	`id` INT (11) NOT NULL AUTO_INCREMENT,
	`account` VARCHAR (40) NOT NULL COMMENT 'Username'.`password` VARCHAR (255) NOT NULL COMMENT 'password'.`nickname` VARCHAR (60) DEFAULT NULL COMMENT 'nickname'.`email` VARCHAR (40) DEFAULT NULL COMMENT 'email'.`phone` VARCHAR (11) DEFAULT NULL COMMENT 'phone'.`create_time` datetime DEFAULT NULL COMMENT 'Creation time'.`create_user` VARCHAR (11) DEFAULT NULL COMMENT 'Founder'.`modify_time` datetime DEFAULT NULL COMMENT 'Modification time'.`modify_user` VARCHAR (11) DEFAULT NULL COMMENT 'Modifier',
	PRIMARY KEY (`id`))ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8mb4;
Copy the code

Save path: classpath: resources/ SQLS /sys.sql

Entity class

@Getter
@Setter
public class User extends Common {

    private Long id;

    @NotNull(message = "User account cannot be empty.")
    @Size(min = 6, max = 11, message = "Account must be 6-11 characters long")
    private String account;

    @NotNull(message = "User password cannot be empty")
    @Size(min = 6, max = 11, message = "Password must be 6-16 characters long")
    private String password;

    @Size(max = 40, message = "User nicknames can be no longer than 40 characters.")
    private String nickname;

    @Email(message = "Email format is incorrect")
    private String email;

    @Phone(message = "Mobile phone number format is incorrect")
    private String phone;
}
Copy the code

Mapper 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">

<mapper namespace="com.zhuqc.framework.dao.UserDao">

    <select id="getUser" resultType="com.zhuqc.framework.entity.User">
        select * from sys_user where id = #{id, jdbcType = NUMERIC}
    </select>

    <insert id="addUser" parameterType="com.zhuqc.framework.entity.User">
        insert into sys_user
          (account,
           password,
           nickname,
           email,
           phone,
           create_user,
           create_time,
           modify_user,
           modify_time)
        values
          (#{account, jdbcType = VARCHAR},
           #{password, jdbcType = VARCHAR},
           #{nickname, jdbcType = VARCHAR},
           #{email, jdbcType = VARCHAR},
           #{phone, jdbcType = VARCHAR},
           #{createUser, jdbcType = VARCHAR},
           #{createTime, jdbcType = TIMESTAMP},
           #{modifyUser, jdbcType = VARCHAR},
           #{modifyTime, jdbcType = TIMESTAMP})
    </insert>

    <delete id="deleteUser">
        delete from sys_user
         where id = #{id, jdbcType = NUMERIC}
    </delete>

    <update id="updateUser" parameterType="com.zhuqc.framework.entity.User">
        update sys_user
           set nickname       = #{nickname, jdbcType = VARCHAR},
               email          = #{email, jdbcType = VARCHAR},
               phone          = #{phone, jdbcType = VARCHAR},
               modify_user    = #{modifyUser, jdbcType = VARCHAR},
               modify_time    = #{modifyTime, jdbcType = TIMESTAMP}
         where id = #{id, jdbcType = NUMERIC}
    </update>

</mapper>
Copy the code

The persistence layer UserDao

@Mapper
public interface UserDao {

    User getUser(@Param("id") Long id);

    int addUser(User user);

    int deleteUser(@Param("id") Long id);

    int updateUser(User user);
}
Copy the code

Mapper files need a one-to-one mapping with Dao classes

  • The namespace in the Mapper file corresponds to the full path of the DAO interface
  • The ID of the statement in the Mapper file corresponds to the method name in the DAO interface
  • ParameterType in statement in the Mapper file corresponds to the input parameterType of the DAO interface
  • The resultType of statement in the Mapper file corresponds to the return type of the DAO interface method

The service layer UserService

public interface UserService {

    User getUser(Long id);

    int addUser(User user);

    int deleteUser(Long id);

    int updateUser(User user);
}
Copy the code
@Service
@Transactional
public class UserServiceImpl implements UserService {

    @Autowired
    private UserDao userDao;

    @Override
    public User getUser(Long id) {
        return userDao.getUser(id);
    }

    @Override
    public int addUser(User user) {
        return userDao.addUser(user);
    }

    @Override
    public int deleteUser(Long id) {
        return userDao.deleteUser(id);
    }

    @Override
    public int updateUser(User user) {
        returnuserDao.updateUser(user); }}Copy the code

Control layer UserController

@RestController
@RequestMapping("/user")
public class UserController extends BaseController {

    @Autowired
    private UserService userService;

    @GetMapping("/{id}")
    public ApiResult getUser(@PathVariable("id") Long id) {
        return ApiResult.success(userService.getUser(id));
    }

    @PostMapping("/add")
    public ApiResult addUser(@RequestBody @Valid User user) {
        setCreateInfo(user);
        return ApiResult.success(userService.addUser(user));
    }

    @DeleteMapping("/{id}")
    public ApiResult deleteUser(@PathVariable("id") Long id) {
        return ApiResult.success(userService.deleteUser(id));
    }

    @PutMapping("/{id}")
    public ApiResult updateUser(@RequestBody @Valid User user) {
        setModifyInfo(user);
        returnApiResult.success(userService.updateUser(user)); }}Copy the code

After writing, visit Swagger address to test the interface as follows:

conclusion

So far, the successful integration of MyBatis and the realization of a simple add, delete, change and check.

However, it is easy to find some problems in the process of using:

  1. Each entity needs to write add, delete, change and check SQL, which feels like repetitive work
  2. Without SQL monitoring, the running status of SQL cannot be counted
  3. Without paging plug-ins, paging queries are more troublesome
  4. Without unit testing, interface testing is a hassle

Solutions to the above problems:

  1. Use code generator and integrate MyBatis- Plus.
  2. Use Ali data source Druid
  3. Integrate the paging plug-in PageHelper
  4. The unit test calls the Controller interface through the MockMvc class

The solution will be implemented in a later article.

Above, thanks for reading. If it helps, just click a “like”!

The source code

Github.com/zhuqianchan…

Review past

  • Build backend frameworks from scratch – keep updating