SpringBoot入门建站全系列（三）Mybatis操作数据库

作者: 逍遥天扬 | 来源:发表于2019-04-30 18:20 被阅读8次

SpringBoot入门建站全系列（三）Mybatis操作数据库

SpringBoot操作数据库有多种方式，如

JDBC直接操作:太古老了，没人愿意这样玩

Mybatis插件：比较时髦，比较适合sql复杂，或者对性能要求高的应用，因为sql都是自己写的。

Spring-data-jpa：使用hibernate作为实现，基本上不需要写sql，因为sql都是统计的，总是会产生多余的查询，性能上相对而言会低，但不绝对，影响性能的因素是多种的，这里说的性能是从最终的查询的sql来对比的，毕竟生成的sql没有经过深思熟虑写出来的性能好。

JdbcTemplate：spring在jdbc上面做了深层次的封装，使用spring的注入功能，可以把DataSource注册到JdbcTemplate之中。Spring-data-jpa引入的时候，JdbcTemplate必然会被引入的。

当然还有其他中间件，主流使用的就是Mybatis和Spring-data-jpa。索引本篇先讲Mybatis。

品茗IT-SpringBoot专题-同步发布

品茗IT 提供在线支持：

一键快速构建Spring项目工具

一键快速构建SpringBoot项目工具

一键快速构建SpringCloud项目工具

一站式Springboot项目生成

Mysql一键生成Mybatis注解Mapper

一、引入依赖

需要同时引入数据库的connector和数据源datasource，当然也可以使用mybatis自己实现的数据源，但是还是以第三方数据源最好，毕竟经过大家的认可。

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>1.3.2</version>
</dependency>
<dependency>
    <groupId>org.apache.commons</groupId>
    <artifactId>commons-dbcp2</artifactId>
</dependency>

完整的依赖如下所示：

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>cn.pomit</groupId>
    <artifactId>testboot</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <packaging>jar</packaging>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.0.4.RELEASE</version>
        <relativePath /> <!-- lookup parent from repository -->
    </parent>
    <name>testboot</name>
    <url>http://maven.apache.org</url>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-dbcp2</artifactId>
        </dependency>
    </dependencies>
    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>
</project>

二、配置数据库连接信息

spring.datasource.driverClassName = com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/boot?useUnicode=true&characterEncoding=utf8&serverTimezone=UTC
spring.datasource.username=cff
spring.datasource.password=123456

spring.datasource.type=org.apache.commons.dbcp2.BasicDataSource
spring.datasource.dbcp2.max-wait-millis=60000
spring.datasource.dbcp2.min-idle=20
spring.datasource.dbcp2.initial-size=2
spring.datasource.dbcp2.validation-query=SELECT 1
spring.datasource.dbcp2.connection-properties=characterEncoding=utf8
spring.datasource.dbcp2.validation-query=SELECT 1
spring.datasource.dbcp2.test-while-idle=true
spring.datasource.dbcp2.test-on-borrow=true
spring.datasource.dbcp2.test-on-return=false

mybatis.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl

三、使用Mybatis的Mapper

3.1 表与Java实体

假设我们有一张这个表user_role ：

在这里插入图片描述

实体：

import java.io.Serializable;

public class UserRole implements Serializable {
    private static final long serialVersionUID = 1L;

    private Integer id;

    private String role;

    private String userName;

    private String phone;

    public String getUserName() {
        return userName;
    }

    public void setUserName(String userName) {
        this.userName = userName;
    }

    public UserRole() {
    }

    public Integer getId() {
        return id;
    }

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

    public String getRole() {
        return this.role;
    }

    public void setRole(String role) {
        this.role = role;
    }

    @Override
    public String toString() {
        return "UserRole [id=" + id + ", role=" + role + ", userName=" + userName + "]";
    }

    public String getPhone() {
        return phone;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }

}

3.2 使用注解方式写sql

import java.util.List;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Options;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import cn.pomit.testboot.domain.UserRole;

@Mapper
public interface UserRoleMapper {
    @Insert({"<script> ",
        "INSERT INTO user_role",
        "( phone,",
        "userName ,",
        "role",
         ") ",
        " values ",
         "( #{phone},",
         "#{userName},",
         "#{role}",
        " ) ",
         "</script>"})
    @Options(useGeneratedKeys = true, keyProperty = "id", keyColumn = "id")
    int saveTest(UserRole userRole);
    
    @Select({
        "<script>",
            "SELECT ",
            "id as id,",
            "userName as userName,",
            "role as role",
            "FROM user_role",
       "</script>"})
    List<UserRole> selectAll();
    
    @Update({
        "<script>",
        " update user_role set",
        " phone = #{phone, jdbcType=VARCHAR}",
        " where id=#{id}",
        "</script>"
    })
    int update(@Param("id") Integer id, @Param("phone") String phone);
    
    @Delete({
        "<script>",
        " delete from user_role",
        " where id=#{id}",
        "</script>"
    })
    int delete(@Param("id") Integer id);
}

其中，插入操作中的语句：

@Options(useGeneratedKeys = true, keyProperty = "id", keyColumn = "id")

是用来回显ID的，方便保存后回查。

3.3 使用xml方式写sql

使用xml方式写sql，需要先在SpringBoot读取的配置文件（可以放在环境相关的配置文件中，也可以直接放在application.properties文件）中加入：

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

这条配置指定了xml的配置在classpath下的mapper文件夹下。

首先建立一个mapper接口：

import java.util.List;

import org.apache.ibatis.annotations.Mapper;

import cn.pomit.testboot.domain.UserRole;

@Mapper
public interface UserRoleInfoMapper {
    int saveTest(UserRole userRole);
    
    List<UserRole> selectAll();
    
    int update(Integer id, String phone);

    int delete(Integer id);
}

在配置文件mapper文件夹下建立UserRoleInfoMapper.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="cn.pomit.testboot.mapper.UserRoleInfoMapper" >
    <resultMap id="BaseResultMap" type="cn.pomit.testboot.domain.UserRole" >
        <id property="id" column="id" />
        <result column="role" jdbcType="VARCHAR" property="role" />
        <result column="userName" jdbcType="VARCHAR" property="userName" />
        <result column="phone" jdbcType="VARCHAR" property="phone" />
    </resultMap>
    
    <select id="selectAll" resultMap="BaseResultMap">
      SELECT * FROM user_role
    </select>
    
    <insert id="saveTest" useGeneratedKeys="true" keyProperty="id">
        INSERT INTO user_role( phone,userName ,role )  values  ( #{phone}, #{userName}, #{role} )
    </insert>
    
    <update id="update">
      update user_role set
        phone = #{param2}
      where id = #{param1}
    </update>

    <delete id="delete" parameterType="java.lang.Integer">
      delete from user_role where id = #{id}
    </delete>
</mapper>

这里，多参数传递的时候，刚开始用#{0}，#{1}这种形式取值，出现：

nested exception is org.apache.ibatis.binding.BindingException: Parameter '1' not found. Available parameters are [arg1, arg0, param1, param2]

这种异常，可能是版本升级，取参方式改变了，那就按照它说的换成param或者arg就行，如果怕出问题，就直接在接口的参数前加上@Param注解，xml中以名称来取变量即可。

四、service逻辑调用Mapper。

UserRoleService：

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import cn.pomit.testboot.domain.UserRole;
import cn.pomit.testboot.mapper.UserRoleInfoMapper;

@Service
public class UserRoleService {
    @Autowired
    UserRoleInfoMapper userRoleMapper;

    // @Autowired
    // UserRoleMapper userRoleMapper;

    public List<UserRole> selectAll() {
        return userRoleMapper.selectAll();
    }

    public int saveTest(UserRole userRole) {
        return userRoleMapper.saveTest(userRole);
    }

    public int update(Integer id, String phone) {
        return userRoleMapper.update(id, phone);
    }

    public int delete(Integer id) {
        return userRoleMapper.delete(id);
    }
}

五、开放接口调用service

MybatisRest:

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import cn.pomit.testboot.domain.UserRole;
import cn.pomit.testboot.service.UserRoleService;

@RestController
@RequestMapping("/db")
public class MybatisRest {
    @Autowired
    UserRoleService userRoleService;

    @RequestMapping(value = "/query")
    public List<UserRole> query() {
        return userRoleService.selectAll();
    }
    
    @RequestMapping(value = "/save")
    public Object save() {
        UserRole userRole = new UserRole();
        userRole.setRole("TEST");
        userRole.setUserName("TEST");
        userRole.setPhone("3424234");
        return userRoleService.saveTest(userRole );
    }
    
    @RequestMapping(value = "/update")
    public Object update() {
        return userRoleService.update(4,"454");
    }
    
    @RequestMapping(value = "/delete")
    public Object delete() {
        return userRoleService.delete(4);
    }
    
}

喜欢这篇文章么，喜欢就加入我们一起讨论SpringBoot技术吧!

品茗IT技术交流群

SpringBoot入门建站全系列（三）Mybatis操作数据库