Spring Boot 技术探索

Spring Boot makes it easy to create stand-alone, production-grade Spring based Applications that you can "just run".

14、Spring Boot使用MyBatis操作数据库

平台环境:

 

名称

版本号

Mac OS X

10.14.5

JDK

1.8.0_201

Apache Maven

3.6.0

IntelliJ IDEA

2019.1 (Ultimate Edition)

Spring Boot

2.1.6.RELEASE

MySQL

8.0.13 库名叫test1

 

  MyBatis 是一款优秀的持久层框架,它支持定制化 SQL、存储过程以及高级映射。MyBatis 避免了几乎所有的 JDBC 代码和手动设置参数以及获取结果集。由于直接编写SQL操作数据库,因此具有极大的灵活性。

 

一、配置过程

1、启动MySQL数据库,建表:

CREATE TABLE
    Employee
    (
        ID bigint NOT NULL AUTO_INCREMENT,
        user_name VARCHAR(100),
        pass_word VARCHAR(100),
        login_time DATETIME,
        remark VARCHAR(500),
        PRIMARY KEY (ID)
    )
    ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

2、pom.xml中添加相关依赖

<?xml version="1.0" encoding="UTF-8"?>
<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>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.1.6.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.example</groupId>
    <artifactId>demo</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>demo</name>
    <description>Demo project for Spring Boot</description>

    <properties>
        <java.version>1.8</java.version>
    </properties>

    <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>
            <version>2.1.0</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

 

3、application.properties中添加配置

# Spring Config
spring.datasource.driverClassName = com.mysql.cj.jdbc.Driver
spring.datasource.url = jdbc:mysql://localhost:3306/test1?serverTimezone=Asia/Shanghai&characterEncoding=utf-8
spring.datasource.username = root
spring.datasource.password = 12345678

# MyBatis Config
logging.level.com.example.MyBatisDemo.mapper=debug
#开启驼峰命名转换
#mybatis.configuration.map-underscore-to-camel-case=true
#开启null值返回
#mybatis.configuration.callSettersOnNulls=true

这里需要注意:

  • 从MySQL5.X以后的版本(不包括5.X)开始,数据库连接驱动使用com.mysql.cj.jdbc.Driver
  • 旧版本的数据库连接驱动为com.mysql.jdbc.Driver
  • 使用新的数据库驱动时要加上时区serverTimezone=Asia/Shanghai,如果不加则默认为格林威治标准时间。
  • 打印日志配置分为logging.level.加mapper包的位置两部分组成。

 

4、编写与数据库表对应的Entity类

package com.example.demo.dao.entity;

import java.io.Serializable;
import java.util.Date;

public class EmployeeEntity implements Serializable
{
    private static final long serialVersionUID = 1L;
    private Long id;
    private String userName;
    private String password;
    private Date loginTime;
    private String remark;

    public Long getId()
    {
        return id;
    }

    public void setId(Long 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 Date getLoginTime()
    {
        return loginTime;
    }

    public void setLoginTime(Date loginTime)
    {
        this.loginTime = loginTime;
    }

    public String getRemark()
    {
        return remark;
    }

    public void setRemark(String remark)
    {
        this.remark = remark;
    }
}

 

5、编写Mapper(交由Spring托管的Java接口,增删改查操作都写到这里)

一般来说,一个数据库表的操作,对应一个Mapper。但把多张表的操作都写到一个Mapper里也不会报错,只是不够规范。

package com.example.demo.dao.mapper;

import java.util.LinkedHashMap;
import java.util.List;

import com.example.demo.dao.entity.EmployeeEntity;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.ResultMap;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

public interface EmployeeMapper
{
    // #{}中的名称要和Entity类中的属性名一致
    @Insert("INSERT INTO Employee(user_name, pass_word, login_time, remark) VALUES(#{userName}, #{password}, #{loginTime}, #{remark})")
    void insert(EmployeeEntity employeeEntity);

    // 由于数据库字段名和Entity中的属性不完全一致,因此这里需要编写映射字段。
    @Select("SELECT * FROM Employee WHERE user_name = '#{userName}'")
    @Results(id = "EmployeeResults", value = {@Result(column = "user_name", property = "userName"), @Result(column = "pass_word", property = "password"), @Result(column = "login_time", property = "loginTime")})
    // remark的映射就不用写了,因为数据库列名称和Entity类字段完全一样。
    EmployeeEntity getEmployeeByUserName(String userName);

    // 直接使用定义好的@Results
    @Select("SELECT * FROM Employee")
    @ResultMap("EmployeeResults")
    List<EmployeeEntity> getAll();

    // 不用Entity类接收返回值(省略写@Results的麻烦)
    @Select("SELECT * FROM Employee")
    List<LinkedHashMap<String, Object>> getAll_NoEntity();

    // 注意这里用$号(相当于拼接字符串)
    @Update("UPDATE Employee SET remark='${remark}' WHERE user_name = '${userName}'")
    void update(@Param("userName") String userName, @Param("remark") String remark);

    // 注意这里用#号(相当于?号传参)
    @Delete("DELETE FROM Employee WHERE ID=#{id}")
    void delete(Long id);

    @Delete("DELETE FROM Employee WHERE user_name = '${userName}'")
    void deleteByUserName(@Param("userName") String userName);
}

 

6、新建配置类,加入对自定义Mapper的扫描注解

package com.example.demo.config;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.context.annotation.Configuration;

@Configuration
@MapperScan("com.example.demo.dao.mapper")// 这个也可以直接写到Spring Boot的启动类上
public class AppConfig
{
}

 

二、测试

新建测试类:

package com.example.demo;

import java.util.Date;
import java.util.LinkedHashMap;
import java.util.List;

import com.example.demo.dao.mapper.EmployeeMapper;
import com.example.demo.dao.entity.EmployeeEntity;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import javax.annotation.Resource;

@RunWith(SpringRunner.class)
@SpringBootTest
public class MyBatisTest
{
    @Resource
    EmployeeMapper employeeMapper;

    @Test
    public void TestInsert()
    {
        EmployeeEntity employeeEntity = new EmployeeEntity();
        employeeEntity.setUserName("wang");
        employeeEntity.setPassword("123456");
        employeeEntity.setLoginTime(new Date());
        employeeEntity.setRemark("备注XXX");
        employeeMapper.insert(employeeEntity);

        EmployeeEntity employeeEntity2 = new EmployeeEntity();
        employeeEntity2.setUserName("wang2");
        employeeEntity2.setPassword("888888");
        employeeEntity2.setLoginTime(new Date());
        employeeEntity2.setRemark("备注YYY");
        employeeMapper.insert(employeeEntity2);
    }

    @Test
    public void testSelect()
    {
        List<EmployeeEntity> employeeEntitys = employeeMapper.getAll();
        for (EmployeeEntity employeeEntity : employeeEntitys)
        {
            System.out.println(employeeEntity.getUserName());
            System.out.println(employeeEntity.getPassword());
            System.out.println(employeeEntity.getLoginTime());
            System.out.println(employeeEntity.getRemark());
            System.out.println("--------------");
        }
    }

    @Test
    public void testSelectNoEntity()
    {
        List<LinkedHashMap<String, Object>> employeeEntitys = employeeMapper.getAll_NoEntity();
        for (LinkedHashMap<String, Object> hashMap : employeeEntitys)
        {
            for (String key : hashMap.keySet())
            {
                System.out.println(key + ":" + hashMap.get(key));
            }
            System.out.println("--------------");
        }
    }

    @Test
    public void testUpdate() throws Exception
    {
        employeeMapper.update("wang2", "KKKKKKKKK");
    }

    @Test
    public void testDelete() throws Exception
    {
        employeeMapper.deleteByUserName("wang");
    }

}

 

补充1:

在Mapper类中,由于数据库字段名和Entity中的属性不完全一致,因此需要编写映射字段@Results(……),比较繁琐。但是如果命名规则是有规律的话,这里有简化的方法。规则:对于如果数据库字段是下划线分隔,Entity类中的字段是驼峰命名的导致无法匹配的情况,则可以开启MyBatis的驼峰命名转换功能。例如:

数据库字段

Entity类

user_name

userName

pass_word

password

login_time

loginTime

开启方法:

在application.properties中添加以下配置

mybatis.configuration.map-underscore-to-camel-case=true

 

补充2:

在测试方法testSelectNoEntity()中,如果遇到有字段的值是null的情况,查询结果会自动缺少这一列。这种情况会导致同一个查询由于数据的原因导致返回的列数不一致的问题。解决办法:

在application.properties中添加以下配置

mybatis.configuration.callSettersOnNulls=true

 

补充3:

为什么MySQL从5.7版本一下子变成8版本了?

The first question people are likely to ask: If the most recent version of MySQL was a 5.x release, why's this one 8.0? For one, version 6.0 was canned as part of the transition to "more frequent and timely releases of the MySQL Server." Version 7 was reserved for the clustering version of MySQL. That leaves 8.x as the next major version number for the common use-case version of MySQL.

更多信息,可查看原文:https://www.infoworld.com/article/3119258/database/mysql-80-more-unicode-less-hassle.html

 

 

Bootstrap Thumbnail Second
MySQL

MySQL is the world's most popular open source database.

GO

Bootstrap Thumbnail Third
算法基础

本书介绍了什么是计算机算法,如何描述它们,以及如何来评估它们。

GO