不废话Spring Boot（四）整合Mybatis

简介

上一节学习了在Spring Boot里通过JPA方式访问数据库，除此之外，还有一种以编写SQL为核心的方式（上一节中有详细介绍），就是通过整合Mybatis来操作数据库，今天再来学习一下在Spring Boot整合Mybatis，Mybatis框架已经很流行了，就不介绍太多了。

整合Mybatis

首先在POM文件里添加Mybatis的依赖

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

然后加入MySQL驱动依赖

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
</dependency>

参考上一节代码内容，配置数据库连接信息

spring.datasource.url=jdbc:mysql://localhost:3306/springbootstudy?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.username=root
spring.datasource.password=123456

spring.jpa.properties.hibernate.hbm2ddl.auto=update
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL5InnoDBDialect
spring.jpa.show-sql=true

以上，整合Mybatis的工作就可以了。

无配置文件版本

接下来我们创建数据库表，和上一节一样

CREATE TABLE user (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `addr` varchar(255) DEFAULT NULL,
  `company` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

对应实体类 

package org.dothwinds.springbootstudy.model;

import java.io.Serializable;

public class User implements Serializable {
    private Long id;
    private String name;
    private String company;
    private String addr;

    public Long getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getCompany() {
        return company;
    }

    public void setCompany(String company) {
        this.company = company;
    }

    public String getAddr() {
        return addr;
    }

    public void setAddr(String addr) {
        this.addr = addr;
    }
}

把对应的数据库及实体类都创建完了，接下来编写代码操作数据表。不管哪种方式，SQL都是不可缺少的，Mybatis本身就鼓励写SQL而不是像Hibernate自动生成。编写Mapper接口，这块是核心内容，SQL处理都在这里，编写增删改查功能

package org.dothwinds.springbootstudy.dao;

import org.apache.ibatis.annotations.*;
import org.dothwinds.springbootstudy.model.User;

@Mapper
public interface UserMapper {
    @Insert("insert into user(name, addr, company) values(#{name}, #{addr}, #{company})")
    int add(@Param("name") String name, @Param("addr") String addr, @Param("company") String company);

    @Update("update user set name = #{name}, addr = #{addr}, company=#{company} where id = #{id}")
    int update(@Param("name") String name, @Param("addr") String addr,@Param("company") String company , @Param("id") long  id);

    @Delete("delete from user where id = #{id}")
    int delete(@Param("id") long  id);

    @Select("select * from user where id = #{id}")
    User findUserById(@Param("id") int id);
}

编写UserService，只写了增加和删除功能

package org.dothwinds.springbootstudy.service;

import org.dothwinds.springbootstudy.dao.UserMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

@Service
public class UserService {

    @Autowired
    private UserMapper userMapper;

    public int add(String name, String addr, String company) {
        return userMapper.add(name, addr, company);
    }

    public int delete(long id) {
        return userMapper.delete(id);
    }

}

编写UserController

package org.dothwinds.springbootstudy.controller;

import org.dothwinds.springbootstudy.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

@RestController
public class UserController {

    @Autowired
    private UserService userService;

    @GetMapping("/addUser")
    public String addUser(@RequestParam(value = "name") String name, @RequestParam(value = "addr") String addr, @RequestParam(value = "company") String company){
        int result = userService.add(name, addr, company);
        if (result == 1) {
            return "success";
        } else {
            return "fail";
        }
    }

    @GetMapping("/deleteUser")
    public String deleteUser(@RequestParam(value = "id") long id){
        int result = userService.delete(id);
        if (result == 1) {
            return "success";
        } else {
            return "fail";
        }
    }
}

然后启动服务进行测试返回成功，数据库里也存在。

在测试一下删除也返回成功，数据库里也被删除掉了

 

配置文件版本 

 先指定配置文件都放在哪里，在application.properties里加入配置文件存放位置

mybatis.config-location=classpath:mybatis/mybatis-config.xml
mybatis.mapper-locations=classpath:mybatis/mapper/*.xml

修改mybatis-config，指定java类型别名，也可以加入一些基础配置，比如数据源之类等等

<?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="VARCHAR" type="java.lang.String" />
    </typeAliases>
</configuration>

接下来就是把相关SQL操作放到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="org.dothwinds.springbootstudy.dao.UserMapper" >
    <resultMap id="BaseResultMap" type="org.dothwinds.springbootstudy.model.User" >
        <id column="id" property="id" jdbcType="BIGINT" />
        <result column="name" property="name" jdbcType="VARCHAR" />
        <result column="addr" property="addr" jdbcType="VARCHAR" />
        <result column="company" property="company" javaType="VARCHAR"/>
    </resultMap>

    <sql id="Base_Column_List" >
        id, name, addr, company
    </sql>

    <insert id="add" parameterType="java.lang.String" >
        INSERT INTO
        user
        (name,addr,company)
        VALUES
        (#{name}, #{addr}, #{company})
    </insert>


    <delete id="delete" parameterType="java.lang.Long">
        DELETE FROM
        user
        WHERE
        id =#{id}
    </delete>
</mapper>

DAO接口变简单了，只有对应的调用方法了

package org.dothwinds.springbootstudy.dao;

import org.apache.ibatis.annotations.Param;

public interface UserMapper {
    /*
    无配置注解方式的代码
    @Insert("insert into user(name, addr, company) values(#{name}, #{addr}, #{company})")
    int add(@Param("name") String name, @Param("addr") String addr, @Param("company") String company);

    @Update("update user set name = #{name}, addr = #{addr}, company=#{company} where id = #{id}")
    int update(@Param("name") String name, @Param("addr") String addr,@Param("company") String company , @Param("id") long  id);

    @Delete("delete from user where id = #{id}")
    int delete(@Param("id") long  id);

    @Select("select * from user where id = #{id}")
    User findUserById(@Param("id") int id);
     */

    //配置文件版本
    int add(@Param("name") String name, @Param("addr") String addr, @Param("company") String company );

    int delete(@Param("id") long  id);
}

其余的Controller和Service都不用做变化，测试都没问题

一些需要注意的地方

SQL写到配置文件里，需要配置的内容比较多，大概注意几点：

1，在application.properties文件里，要写明mybatis config和mapper xml的文件路径，比如：

mybatis.config-location=classpath:mybatis/mybatis-config.xml
mybatis.mapper-locations=classpath:mybatis/mapper/*.xml

2， 别忘记写 @MapperScan(basePackages = {"org.dothwinds.springbootstudy.dao"}) ，其作用为指定要变成实现类的接口所在的包，然后包下面的所有接口在编译之后都会生成相应的实现类，也就是需要找到DAO在哪

3，mapper xml文件和具体的DAO要对应好，重点看mapper xml里的namespace指定，如下：

<mapper namespace="org.dothwinds.springbootstudy.dao.UserMapper" >

代码地址：码云