Spring Boot+MyBatis+Maven+MySQL基于XML实现增删改查功能

① 现有一个user表，包含以下字段：username, password, salt, email, type, status, activation_code, header_url, create_time。
② 实现增删改查功能。
③ 采用 Spring Boot 和 MyBatis框架实现。

src.main.java.com.nowcoder.community.entity.User.java
实体类User.java
类中的私有成员变量与数据库中user表中的字段对应。

package com.nowcoder.community.entity;

import java.util.Date;

public class User {

    private int id;
    private String username;
    private String password;
    private String salt;
    private String email;
    private int type;
    private int status;
    private String activationCode;
    private String headerUrl;
    private Date createTime;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getSalt() {
        return salt;
    }

    public void setSalt(String salt) {
        this.salt = salt;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public int getType() {
        return type;
    }

    public void setType(int type) {
        this.type = type;
    }

    public int getStatus() {
        return status;
    }

    public void setStatus(int status) {
        this.status = status;
    }

    public String getActivationCode() {
        return activationCode;
    }

    public void setActivationCode(String activationCode) {
        this.activationCode = activationCode;
    }

    public String getHeaderUrl() {
        return headerUrl;
    }

    public void setHeaderUrl(String headerUrl) {
        this.headerUrl = headerUrl;
    }

    public Date getCreateTime() {
        return createTime;
    }

    public void setCreateTime(Date createTime) {
        this.createTime = createTime;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + ''' +
                ", password='" + password + ''' +
                ", salt='" + salt + ''' +
                ", email='" + email + ''' +
                ", type=" + type +
                ", status=" + status +
                ", activationCode='" + activationCode + ''' +
                ", headerUrl='" + headerUrl + ''' +
                ", createTime=" + createTime +
                '}';
    }
}

src.main.java.com.nowcoder.community.dao.UserMapper.java
接口类UserMapper.java

package com.nowcoder.community.dao;

import com.nowcoder.community.entity.User;
import org.apache.ibatis.annotations.Mapper;

@Mapper
public interface UserMapper {

    User selectById(int id);

    User selectByName(String username);

    User selectByEmail(String email);

    int insertUser(User user);

    int updateStatus(int id, int status);

    int updateHeader(int id, String headerUrl);

    int updatePassword(int id, String password);

}

src.main.resources.mapper.user-mapper.xml
user-mapper.xml
xml 文件包含增删改查等 sql 语句：

    
        username, password, salt, email, type, status, activation_code, header_url, create_time
    

    
        id, username, password, salt, email, type, status, activation_code, header_url, create_time
    

    
        select 
        from user
        where id = #{id}
    

    
        select 
        from user
        where username = #{username}
    

    
        select 
        from user
        where email = #{email}
    

    
        insert into user ()
        values(#{username}, #{password}, #{salt}, #{email}, #{type}, #{status}, #{activationCode}, #{headerUrl}, #{createTime})
    

    
        update user set status = #{status} where id = #{id}
    

    
        update user set header_url = #{headerUrl} where id = #{id}
    

    
        update user set password = #{password} where id = #{id}
    

其中，
① 代码可以实现字段的复用。
② #{字段}可以自动替换为指定的值
③ 区别：${}是拼接符，传入的内容将拼接在sql中且不进行jdbc类型的转换
#{}是占位符，可以设置值并自动进行java和jdbc类型转换

src.resources.application.properties
application.properties

# ServerProperties
server.port=8080
server.servlet.context-path=/community

# ThymeleafProperties
spring.thymeleaf.cache=false

# DataSourceProperties
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/community?characterEncoding=utf-8&useSSL=false&serverTimezone=Hongkong
spring.datasource.username=xxxx
spring.datasource.password=xxxxxx
spring.datasource.type=com.zaxxer.hikari.HikariDataSource
spring.datasource.hikari.maximum-pool-size=15
spring.datasource.hikari.minimum-idle=5
spring.datasource.hikari.idle-timeout=30000

# MybatisProperties
mybatis.mapper-locations=classpath:mapper/*.xml
mybatis.type-aliases-package=com.nowcoder.community.entity
mybatis.configuration.useGeneratedKeys=true
mybatis.configuration.mapUnderscoreToCamelCase=true

# logger
logging.level.com.nowcoder.community=debug

其中，mybatis.configuration.mapUnderscoreToCamelCase=true的作用是将带有下划线的表字段映射为驼峰格式的实体类属性。

src.test.java.com.nowcoder.community.MapperTests.java
MapperTests.java

package com.nowcoder.community;

import com.nowcoder.community.dao.UserMapper;
import com.nowcoder.community.entity.User;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.ContextConfiguration;

import java.util.Date;

@SpringBootTest
@ContextConfiguration(classes = CommunityApplication.class)
public class MapperTests {

    @Autowired
    private UserMapper userMapper;

    @Test
    public void testSelectUser() {
        User user = userMapper.selectById(101);
        System.out.println(user);

        user = userMapper.selectByName("liubei");
        System.out.println(user);

        user = userMapper.selectByEmail("nowcoder101@sina.com");
        System.out.println(user);

    }

    @Test
    public void testInsertUser() {
        User user = new User();
        user.setUsername("test");
        user.setPassword("123456");
        user.setSalt("abc");
        user.setEmail("test@qq.com");
        user.setHeaderUrl("http://www.nowcoder.com/101.png");
        user.setCreateTime(new Date());

        int rows = userMapper.insertUser(user);
        System.out.println(rows);
        System.out.println(user.getId());
    }

    @Test
    public void updataUser() {
        int rows = userMapper.updateStatus(150, 1);
        System.out.println(rows);

        rows = userMapper.updateHeader(150, "http://www.nowcoder.com/102.png");
        System.out.println(rows);

        rows = userMapper.updatePassword(150, "newpsw");
        System.out.println(rows);
    }
}

MyBatis核心组件：
① SqlSessionFactory：用于创建 SqlSession 的工厂类
② SqlSession：Mybatis 的核心，用于向数据库执行 SQL
③ 主配置文件：XML 配置，可以对 MyBatis 的底层行为进行详细配置
④ Mapper接口：即DAO接口，在 MyBatis 中习惯称之为Mapper
⑤ Mapper映射器：用于编写 SQL 并将 SQL 和实体类映射的组件，采用 XML、注解均可实现。