Spring Boot实战：整合MyBatis连接数据库

0. 开发环境

IDE：IntelliJ IDEA 2017.1 x64

jdk：1.8.0_91

Spring Boot：2.1.1.RELEASE

Maven：3.3.9

1. 引入依赖

org.mybatis.spring.boot

mybatis-spring-boot-starter

1.3.2

mysql

mysql-connector-java

8.0.15

runtime

这里，如果使用的 MySQL 是 8.X版本的话，mysql-connector-java 版本必须是 8.0以上，否则会报错：

java.sql.SQLException:Unabletoloadauthenticationplugin'caching_sha2_password'

2. 引入数据源

application.yml 增加如下配置信息

spring:

datasource:

url: jdbc:mysql://127.0.0.1:3306/test?serverTimezone=GMT%2B8

username: root

password: root

driver-class-name: com.mysql.cj.jdbc.Driver

到这里，Spring Boot就可以访问数据库了，我们来做下测试。

3. 测试

3.1 新建数据库表

CREATESCHEMA `test` ;

CREATETABLE`test`.`test` (

`id`INTNOTNULLAUTO_INCREMENT,

`name`VARCHAR(45)NOTNULL,

PRIMARY KEY (`id`));

INSERTINTO`test`.`test` (`name`)VALUES('张三');

INSERTINTO`test`.`test` (`name`)VALUES('李四');

INSERTINTO`test`.`test` (`name`)VALUES('王五');

INSERTINTO`test`.`test` (`name`)VALUES('周六');

3.2 创建实体

3.3 DAO层

packagecn.wbnull.springbootdemo.dao;

importcn.wbnull.springbootdemo.model.TestModel;

importorg.apache.ibatis.annotations.Insert;

importorg.apache.ibatis.annotations.Mapper;

importorg.apache.ibatis.annotations.Param;

importorg.apache.ibatis.annotations.Select;

importjava.util.List;

@Mapper

publicinterfaceTestMapper{

@Select("select id,name from test")

Listselect();

@Insert("insert into test(name) values(#)")

intinsert(@Param("name")Stringname);

}

3.4 Service层

packagecn.wbnull.springbootdemo.service;

importcn.wbnull.springbootdemo.dao.TestMapper;

importcn.wbnull.springbootdemo.model.TestModel;

importorg.springframework.beans.factory.annotation.Autowired;

importorg.springframework.stereotype.Service;

importjava.util.List;

@Service

publicclassMySQLTestService{

@Autowired

privateTestMappertestMapper;//这里会有报错，不用管

publicListselect() {

returntestMapper.select();

}

publicintinsert(Stringname) {

returntestMapper.insert(name);

}

}

3.5 Controller层

packagecn.wbnull.springbootdemo.controller;

importcn.wbnull.springbootdemo.model.TestModel;

importcn.wbnull.springbootdemo.service.MySQLTestService;

importorg.springframework.beans.factory.annotation.Autowired;

importorg.springframework.context.annotation.Scope;

importorg.springframework.web.bind.annotation.PostMapping;

importorg.springframework.web.bind.annotation.RequestMapping;

importorg.springframework.web.bind.annotation.RequestParam;

importorg.springframework.web.bind.annotation.RestController;

importjava.util.List;

@RestController

@Scope("prototype")

@RequestMapping("/mysql/test")

publicclassMySQLTestController{

@Autowired

privateMySQLTestServicemySQLTestService;

@PostMapping(value="/select")

publicListselect()throwsException{

returnmySQLTestService.select();

}

@PostMapping(value="/insert")

publicintinsert(@RequestParam(value="name")Stringname)throwsException{

returnmySQLTestService.insert(name);

}

}

3.6 测试

使用Postman进行测试，输出结果如下3.6.1 select

3.6.2 insert

Postman接收到输出为1，表示成功，我们去查下数据库

数据成功插入。

4. MyBatis映射文件

以上，我们访问数据库时，SQL语句还是写死在代码里面，处理起来并没有很方便。通常，我们把SQL语句与代码分离，使用MyBatis映射文件方式来访问数据库。

4.1 MyBatis配置

我们在resources文件夹下新建mapper文件夹，用于存放MyBatis映射文件

application.yml 增加MyBatis配置信息

mybatis:

mapper-locations: classpath:mapper/*.xml #对应mapper映射xml文件所在路径

type-aliases-package: cn.wbnull.springbootdemo.model #对应实体类路径

完整配置如下，注意spring与mybatis都是根节点

spring:

datasource:

url: jdbc:mysql://127.0.0.1:3306/test?serverTimezone=GMT%2B8

username: root

password: root

driver-class-name: com.mysql.cj.jdbc.Driver

#

mybatis:

mapper-locations: classpath:mapper/*.xml #对应mapper映射xml文件所在路径

type-aliases-package: cn.wbnull.springbootdemo.model #对应实体类路径

4.2 DAO层

我们创建一个新的DAO类

packagecn.wbnull.springbootdemo.dao;

importcn.wbnull.springbootdemo.model.TestModel;

importjava.util.List;

publicinterfaceTestMapperV2{

Listselect();

intinsert(TestModeltestModel);

}

4.3 创建MyBatis映射文件

resources/mapper下新建映射文件TestMapper.xml

id, name

select

from test

insert into test (id, name

)

values (#, #

)

这里着重注意这几点1、 namespace 一定要对应自己dao包所在的包路径，对应我们需要匹配的dao层类3、对应数据库表的字段4、select from test

4.4 Service层

创建一个新的Service类

packagecn.wbnull.springbootdemo.service;

importcn.wbnull.springbootdemo.dao.TestMapperV2;

importcn.wbnull.springbootdemo.model.TestModel;

importorg.springframework.beans.factory.annotation.Autowired;

importorg.springframework.stereotype.Service;

importjava.util.List;

@Service

publicclassMySQLTestServiceV2{

@Autowired

privateTestMapperV2testMapperV2;//这里会有报错，不用管

publicListselect() {

returntestMapperV2.select();

}

publicintinsert(intid,Stringname) {

TestModeltestModel=newTestModel();

testModel.setId(id);

testModel.setName(name);

returntestMapperV2.insert(testModel);

}

}

4.5 Controller层

创建一个新的Controller类

packagecn.wbnull.springbootdemo.controller;

importcn.wbnull.springbootdemo.model.TestModel;

importcn.wbnull.springbootdemo.service.MySQLTestServiceV2;

importorg.springframework.beans.factory.annotation.Autowired;

importorg.springframework.context.annotation.Scope;

importorg.springframework.web.bind.annotation.PostMapping;

importorg.springframework.web.bind.annotation.RequestMapping;

importorg.springframework.web.bind.annotation.RequestParam;

importorg.springframework.web.bind.annotation.RestController;

importjava.util.List;

@RestController

@Scope("prototype")

@RequestMapping("/mysql/test")

publicclassMySQLTestControllerV2{

@Autowired

privateMySQLTestServiceV2mySQLTestServiceV2;

@PostMapping(value="/selectV2")

publicListselect()throwsException{

returnmySQLTestServiceV2.select();

}

@PostMapping(value="/insertV2")

publicintinsert(@RequestParam(value="id")intid,

@RequestParam(value="name")Stringname)throwsException{

returnmySQLTestServiceV2.insert(id,name);

}

}

4.6 项目启动类

增加@MapperScan("cn.wbnull.springbootdemo.dao")，对应DAO层的包名

packagecn.wbnull.springbootdemo;

importorg.mybatis.spring.annotation.MapperScan;

importorg.springframework.boot.SpringApplication;

importorg.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication

@MapperScan("cn.wbnull.springbootdemo.dao")

publicclassSpringBootDemoApplication{

publicstaticvoidmain(String[]args) {

SpringApplication.run(SpringBootDemoApplication.class,args);

}

}

4.7 测试

4.7.1 select

4.7.2 insert

Postman接收到输出为1，表示成功，我们去查下数据库

数据成功插入

截至这里，Spring Boot已成功整合MyBatis并连接上了数据库，且测试正常。

但我们实际开发过程中，如果存在大量数据库表，那我们就需要创建大量的实体类、DAO层、映射文件，工作量较大。我们使用mybatis generator来自动生成代码。

5. Generator

5.1 新建数据库表

我们先新建一个数据库表，便于一会自动生成代码使用

CREATETABLE`test`.`testv2` (

`id`INTNOTNULLAUTO_INCREMENT,

`username`VARCHAR(45)NOTNULL,

`password`VARCHAR(45)NOTNULL,

PRIMARY KEY (`id`));

5.2 Generator配置文件

在resources文件夹下新建generator文件夹，generator下新建generatorConfig.xml

PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"

"http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">

location="D:\Program Files\Maven\repository\mysql\mysql-connector-java\8.0.15\mysql-connector-java-8.0.15.jar"/>

connectionURL="jdbc:mysql://127.0.0.1:3306/test?serverTimezone=GMT%2B8"

userId="root"

password="root">

targetProject="src/main/java">

targetProject="src/main/resources">

targetPackage="cn.wbnull.springbootdemo.dao"

targetProject="src/main/java">

enableCountByExample="false"enableUpdateByExample="false"

enableDeleteByExample="false"enableSelectByExample="false"selectByExampleQueryId="false"/>

5.3 引入依赖

org.mybatis.generator

mybatis-generator-maven-plugin

1.3.2

$/src/main/resources/generator/generatorConfig.xml

true

true

5.4 Generator配置

1、Run --> Edit Configurations...

2、增加Maven配置

3、增加Generator配置 (mybatis-generator:generate -e)，OK

4、运行

生成代码如下。这里我们运行成功后，同一个表，不要运行多次，否则mapper映射文件中会生成多次的代码。

packagecn.wbnull.springbootdemo.model;

publicclassTestV2{

privateIntegerid;

privateStringusername;

privateStringpassword;

publicIntegergetId() {

returnid;

}

publicvoidsetId(Integerid) {

this.id=id;

}

publicStringgetUsername() {

returnusername;

}

publicvoidsetUsername(Stringusername) {

this.username=username==null?null:username.trim();

}

publicStringgetPassword() {

returnpassword;

}

publicvoidsetPassword(Stringpassword) {

this.password=password==null?null:password.trim();

}

}

packagecn.wbnull.springbootdemo.dao;

importcn.wbnull.springbootdemo.model.TestV2;

publicinterfaceTestV2Mapper{

intinsert(TestV2record);

intinsertSelective(TestV2record);

}

insert into testv2 (id, username, password

)

values (#, #, #

)

insert into testv2

id,

username,

password,

#,

#,

#,

5.5 测试

5.5.1 Service层

packagecn.wbnull.springbootdemo.service;

importcn.wbnull.springbootdemo.dao.TestV2Mapper;

importcn.wbnull.springbootdemo.model.TestV2;

importorg.springframework.beans.factory.annotation.Autowired;

importorg.springframework.stereotype.Service;

@Service

publicclassMySQLTestV2Service{

@Autowired

privateTestV2MappertestV2Mapper;//这里会有报错，不用管

publicintinsert(intid,Stringusername,Stringpassword) {

TestV2testV2=newTestV2();

testV2.setId(id);

testV2.setUsername(username);

testV2.setPassword(password);

returntestV2Mapper.insert(testV2);

}

}

5.5.1 Controller层

packagecn.wbnull.springbootdemo.controller;

importcn.wbnull.springbootdemo.service.MySQLTestV2Service;

importorg.springframework.beans.factory.annotation.Autowired;

importorg.springframework.context.annotation.Scope;

importorg.springframework.web.bind.annotation.PostMapping;

importorg.springframework.web.bind.annotation.RequestMapping;

importorg.springframework.web.bind.annotation.RequestParam;

importorg.springframework.web.bind.annotation.RestController;

@RestController

@Scope("prototype")

@RequestMapping("/mysql/testv2")

publicclassMySQLTestV2Controller{

@Autowired

privateMySQLTestV2ServicemySQLTestV2Service;

@PostMapping(value="/insert")

publicintinsert(@RequestParam(value="id")intid,

@RequestParam(value="username")Stringusername,

@RequestParam(value="password")Stringpassword

)throwsException{

returnmySQLTestV2Service.insert(id,username,password);

}

}

5.5.2 insert

Postman接收到输出为1，表示成功，我们去查下数据库

数据成功插入

这样，对于大量的数据库表，我们就可以使用Generator来生成基本的代码，然后自己再添加其他所需要的代码即可。

GitHub：https://github.com/dkbnull/SpringBootDemo