【从零开始学Spring Boot】-12.Spring Boot Mybatis操作数据库

1.简介

1.1 概述

The MyBatis-Spring-Boot-Starter help you build quickly MyBatis applications on top of the Spring Boot.

By using this module you will achieve:

• Build standalone applications
• Reduce the boilerplate to almost zero
• Less XML configuration

MyBatis-Spring-Boot-Starter可帮助您在Spring Boot之上快速构建MyBatis应用程序。

通过使用此模块，您将实现：

• 构建独立的应用程序
• 将样板减少到几乎为零
• 减少XML配置

1.2 特点

As you may already know, to use MyBatis with Spring you need at least an SqlSessionFactory and at least one mapper interface.

MyBatis-Spring-Boot-Starter will:

• Autodetect an existing DataSource
• Will create and register an instance of a SqlSessionFactory passing that DataSource as an input using the SqlSessionFactoryBean
• Will create and register an instance of a SqlSessionTemplate got out of the SqlSessionFactory
• Auto-scan your mappers, link them to the SqlSessionTemplate and register them to Spring context so they can be injected into your beans

MyBatis-Spring-Boot-Starter 将会：

• 自动检测已有的 DataSource
• 将创建并注册一个 SqlSessionFactory 实例，并使用 SqlSessionFactoryBean 将该 DataSource 作为输入
• 将创建并注册一个从 SqlSessionFactory 中获取的 SqlSessionTemplate 实例
• 自动扫描您的映射器，将它们链接到 SqlSessionTemplate 并将它们注册到 Spring 上下文中，以便可以将它们注入到您的 bean 中

2.演示环境

1. JDK 1.8.0_201
2. Spring Boot 2.2.0.RELEASE
3. 构建工具（apache maven 3.6.3）
4. 开发工具（IntelliJ IDEA ）

3.演示代码

3.1 代码说明

spring boot 集成 mybatis，使用了 xml 和注解 两种配置方式。实现了单表的增删改查。

3.2 代码结构

3.3 maven 依赖

<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>
    </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
    </dependency>
</dependencies>

3.4 配置文件

application.properties

mybatis.config-location=classpath:mybatis/mybatis-config.xml
mybatis.mapper-locations=classpath:mybatis/mapper/*.xml
mybatis.type-aliases-package=com.soulballad.usage.springboot

spring.datasource.url=jdbc:mysql://172.16.11.125:3306/test?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&useSSL=true
spring.datasource.username=root
spring.datasource.password=123456
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

mybatis-config.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <typeAliases>
        <typeAlias alias="Integer" type="java.lang.Integer"/>
        <typeAlias alias="Long" type="java.lang.Long"/>
        <typeAlias alias="HashMap" type="java.util.HashMap"/>
        <typeAlias alias="LinkedHashMap" type="java.util.LinkedHashMap"/>
        <typeAlias alias="ArrayList" type="java.util.ArrayList"/>
        <typeAlias alias="LinkedList" type="java.util.LinkedList"/>
    </typeAliases>
</configuration>

UserMapper.xml

<?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.soulballad.usage.springboot.mapper.UserMapper">

    <resultMap id="baseResultMap" type="com.soulballad.usage.springboot.model.UserModel">
        <id column="id" property="id" jdbcType="BIGINT"/>
        <result column="name" property="name" jdbcType="VARCHAR"/>
        <result column="age" property="age" javaType="INTEGER"/>
        <result column="birthday" property="birthday" jdbcType="VARCHAR"/>
        <result column="address" property="address" jdbcType="VARCHAR"/>
        <result column="phone" property="phone" jdbcType="VARCHAR"/>
    </resultMap>

    <sql id="Base_Column_List">
        id, `name`, age, birthday, address, phone
    </sql>

    <select id="findUserByName" parameterType="java.lang.String" resultMap="baseResultMap">
        SELECT
        <include refid="Base_Column_List"/>
        FROM t_user
        WHERE `name` like concat('%', #{name}, '%')
    </select>

    <select id="findUserByPhone" parameterType="java.lang.String" resultMap="baseResultMap">
        SELECT
        <include refid="Base_Column_List"/>
        FROM t_user
        WHERE phone = ${phone}
    </select>


    <update id="updateByName" parameterType="com.soulballad.usage.springboot.model.UserModel">
        UPDATE t_user SET phone = #{phone} WHERE `name` = #{name}
    </update>

    <delete id="deleteByName" parameterType="java.lang.String">
       DELETE FROM t_user WHERE `name` = #{name}
    </delete>

</mapper>

3.5 java代码

UserModel.java

public class UserModel implements Serializable {
    private Long id;
    private String name;
    private Integer age;
    private String birthday;
    private String address;
    private String phone;

    public UserModel() {}

    public UserModel(String name, Integer age, String birthday, String address, String phone) {
        this.name = name;
        this.age = age;
        this.birthday = birthday;
        this.address = address;
        this.phone = phone;
    }

       // get&set&toString
}

UserMapper.java

@Repository
public interface UserMapper {

    @Select("SELECT id, `name`, age, birthday, address, phone FROM t_user")
    @Results({
            @Result(property = "id", column = "id"),
            @Result(property = "name", column = "name"),
            @Result(property = "age", column = "age"),
            @Result(property = "birthday", column = "birthday"),
            @Result(property = "address", column = "address"),
            @Result(property = "phone", column = "phone")
    })
    List<UserModel> findAll();

    UserModel findUserByName(String name);

//    Page<UserModel> findByPage(SpringDataWebProperties.Pageable pageable);

    List<UserModel> findUserByPhone(@Param("phone") String phone);

    int updateByName(UserModel userModel);

    int deleteByName(@Param("name") String name);

    @Insert("INSERT INTO t_user(`name`, age, birthday, address, phone) VALUES(#{name}, #{age}, #{birthday}, #{address}, #{phone})")
    int insertUser(UserModel userModel);
}

UserService.java

public interface UserService {

    /**
     * 查询所有数据
     * @return user
     */
    List<UserModel> selectList();

    /**
     * 根据名称查询
     * @param name name
     * @return user
     */
    UserModel findUserByName(String name);

    /**
     * 根据电话查询
     * @param phone 电话
     * @return user
     */
    List<UserModel> findUserByPhone(String phone);

    /**
     * 根据名称更新电话
     * @param phone 电话
     * @param name 名称
     * @return 影响行数
     */
    UserModel updateByName(String phone, String name);

    /**
     * 根据名称删除
     * @param name 名称
     * @return 影响行数
     */
    UserModel deleteByName(String name);

    /**
     * 新增
     * @param user user
     * @return user
     */
    UserModel add(UserModel user);
}

UserServiceImpl.java

@Service
public class UserServiceImpl implements UserService {

    @Autowired
    private UserMapper userMapper;

    @Override
    public List<UserModel> selectList() {
        return userMapper.findAll();
    }

    @Override
    public UserModel findUserByName(String name) {
        return userMapper.findUserByName(name);
    }

    @Override
    public List<UserModel> findUserByPhone(String phone) {
        return userMapper.findUserByPhone(phone);
    }

    @Override
    public UserModel updateByName(String phone, String name) {
        UserModel userModel = new UserModel();
        userModel.setPhone(phone);
        userModel.setName(name);
        userMapper.updateByName(userModel);
        return findUserByName(name);
    }

    @Override
    public UserModel deleteByName(String name) {
        UserModel user = findUserByName(name);
        userMapper.deleteByName(name);
        return user;
    }

    @Override
    public UserModel add(UserModel user) {
        userMapper.insertUser(user);
        return findUserByName(user.getName());
    }
}

UserController.java

@RestController
@RequestMapping(value = "/user")
public class UserController {

    @Autowired
    private UserService userService;

    @GetMapping(value = "/list")
    public List<UserModel> list() {
        return userService.selectList();
    }

    @GetMapping(value = "/findByName/{name}")
    public UserModel findByName(@PathVariable String name) {
        return userService.findUserByName(name);
    }

    @GetMapping(value = "/findByPhone/{phone}")
    public List<UserModel> findByPhone(@PathVariable String phone) {
        return userService.findUserByPhone(phone);
    }

    @PostMapping(value = "/add")
    public UserModel add(@RequestBody UserModel user) {
        return userService.add(user);
    }

    @PutMapping(value = "/updateByName")
    public UserModel updateByName(@RequestBody UserModel user) {
        return userService.updateByName(user.getPhone(), user.getName());
    }

    @DeleteMapping(value = "/deleteByName/{name}")
    public UserModel deleteByName(@PathVariable String name) {
        return userService.deleteByName(name);
    }
}

3.6 git 地址

spring-boot/spring-boot-06-jdbc/spring-boot-mybatis

4.效果展示

启动 SpringBootMybatisApplication.main 方法，在 spring-boot-mybatis.http 访问下列地址，观察输出信息是否符合预期。

查询用户列表（所有）

### GET /user/list
GET http://localhost:8080/user/list
Accept: application/json

根据用户名查询

### GET /user/findByName/{name}
GET http://localhost:8080/user/findByName/lisi
Accept: application/json

根据手机号查询

### GET /user/findByPhone/{phone}
GET http://localhost:8080/user/findByPhone/13666666666
Accept: application/json

添加用户

### POST user/add
POST http://localhost:8080/user/add
Content-Type: application/json

{
  "name": "abcd12",
  "age": 33,
  "birthday": "1987-07-20",
  "address": "washington",
  "phone": "15666666666"
}

更新用户信息

### PUT /user/updateByName
PUT http://localhost:8080/user/updateByName
Content-Type: application/json

{
  "name": "zhangsan",
  "phone": "13456789012"
}

删除用户

### DELETE /user/deleteByName/{name}
DELETE http://localhost:8080/user/deleteByName/zhangsan
Content-Type: application/json

5.参考

1. Mybatis官网