歡迎訪問我的GitHub
https://github.com/zq2599/blog_demos
內容:所有原創文章分類彙總及配套原始碼,涉及Java、Docker、Kubernetes、DevOPS等;
本文是《MyBatis初級實戰》系列的第二篇,通過前文我們知道了如何在SpringBoot中整合MyBatis,本篇就一起來練習基本功:增刪改查;
本篇概覽
本篇要練習的內容如下:
- 單表的增刪改查
- 批量新增
- 聯表查詢
全文由以下部分組成:
- 新建工程
- 增加啟動類
- 增加swagger的配置類,工程包含了swagger,以便稍後在瀏覽器上驗證
- 增加配置檔案
- 增加實體類
- 增加mapper配置檔案
- 增加mapper介面
- 增加service,呼叫mapper介面
- 增加controller,呼叫service服務
- 編寫單元測試用例
- 驗證
原始碼下載
- 如果您不想編碼,可以在GitHub下載所有原始碼,地址和連結資訊如下表所示(https://github.com/zq2599/blog_demos):
名稱 | 連結 | 備註 |
---|---|---|
專案主頁 | https://github.com/zq2599/blog_demos | 該專案在GitHub上的主頁 |
git倉庫地址(https) | https://github.com/zq2599/blog_demos.git | 該專案原始碼的倉庫地址,https協議 |
git倉庫地址(ssh) | git@github.com:zq2599/blog_demos.git | 該專案原始碼的倉庫地址,ssh協議 |
- 這個git專案中有多個資料夾,本章的應用在mybatis資料夾下,如下圖紅框所示:
開發
- 本文的實戰使用的資料庫和表結構與前文《MyBatis初級實戰之一:Spring Boot整合》一模一樣;
- 前文《MyBatis初級實戰之一:Spring Boot整合》新建了父工程mybatis,本文繼續在此工程中新增子工程,名為curd,整個子工程檔案結構如下:
3. 修改父工程mybatis的pom.xml,在dependencyManagement節點下新增兩個dependency節點,如下所示,這麼做是為了統一管理依賴庫的版本:
<!-- swagger-ui -->
<dependency>
<groupId>io.springfox</groupId>
<artifactId>springfox-swagger-ui</artifactId>
<version>2.5.0</version>
</dependency>
<dependency>
<groupId>com.google.code.gson</groupId>
<artifactId>gson</artifactId>
<version>2.8.6</version>
</dependency>
- 名為curd子工程,其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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>com.bolingcavalry</groupId>
<artifactId>mybatis</artifactId>
<version>1.0-SNAPSHOT</version>
<relativePath>../pom.xml</relativePath>
</parent>
<groupId>com.bolingcavalry</groupId>
<artifactId>curd</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>curd</name>
<description>Demo project for Mybatis CURD in 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>
</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>
<exclusions>
<exclusion>
<groupId>org.junit.vintage</groupId>
<artifactId>junit-vintage-engine</artifactId>
</exclusion>
</exclusions>
</dependency>
<dependency>
<groupId>io.springfox</groupId>
<artifactId>springfox-swagger2</artifactId>
</dependency>
<!-- swagger-ui -->
<dependency>
<groupId>io.springfox</groupId>
<artifactId>springfox-swagger-ui</artifactId>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>com.google.code.gson</groupId>
<artifactId>gson</artifactId>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
- 增加啟動類,注意要用MapperScan註釋來指定mapper介面程式碼的包路徑:
package com.bolingcavalry.curd;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
@MapperScan("com.bolingcavalry.curd.mapper")
public class CurdApplication {
public static void main(String[] args) {
SpringApplication.run(CurdApplication.class, args);
}
}
- 本次實戰用到了swagger,這樣可以很方便的通過瀏覽器向各個controller介面傳送請求,以下是配置類:
package com.bolingcavalry.curd;
import springfox.documentation.service.Contact;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import springfox.documentation.builders.ApiInfoBuilder;
import springfox.documentation.builders.PathSelectors;
import springfox.documentation.builders.RequestHandlerSelectors;
import springfox.documentation.service.ApiInfo;
import springfox.documentation.service.Tag;
import springfox.documentation.spi.DocumentationType;
import springfox.documentation.spring.web.plugins.Docket;
import springfox.documentation.swagger2.annotations.EnableSwagger2;
@Configuration
@EnableSwagger2
public class SwaggerConfig {
@Bean
public Docket createRestApi() {
return new Docket(DocumentationType.SWAGGER_2)
.apiInfo(apiInfo())
.tags(new Tag("UserController", "使用者服務"), new Tag("LogController", "日誌服務"))
.select()
// 當前包路徑
.apis(RequestHandlerSelectors.basePackage("com.bolingcavalry.curd.controller"))
.paths(PathSelectors.any())
.build();
}
//構建 api文件的詳細資訊函式,注意這裡的註解引用的是哪個
private ApiInfo apiInfo() {
return new ApiInfoBuilder()
//頁面標題
.title("MyBatis CURD操作")
//建立人
.contact(new Contact("程式設計師欣宸", "https://github.com/zq2599/blog_demos", "zq2599@gmail.com"))
//版本號
.version("1.0")
//描述
.description("API 描述")
.build();
}
}
- application.yml內容如下:
server:
port: 8080
spring:
# 資料來源
datasource:
username: root
password: 123456
url: jdbc:mysql://192.168.50.43:3306/mybatis?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC
driver-class-name: com.mysql.cj.jdbc.Driver
# mybatis配置
mybatis:
# 配置檔案所在位置
config-location: classpath:mybatis-config.xml
# 對映檔案所在位置
mapper-locations: classpath:mappers/*Mapper.xml
# 日誌配置
logging:
level:
root: INFO
com:
bolingcavalry:
curd:
mapper: debug
- 增加user表的實體類User.java,裡面帶有swagger的註解,方便在swagger頁面展示:
package com.bolingcavalry.curd.entity;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
@ApiModel(description = "使用者實體類")
public class User {
@ApiModelProperty(value = "使用者ID")
private Integer id;
@ApiModelProperty(value = "使用者名稱", required = true)
private String name;
@ApiModelProperty(value = "使用者地址", required = false)
private Integer age;
@Override
public String toString() {
return "User{" +
"id=" + id +
", name='" + name + '\'' +
", age=" + age +
'}';
}
// 省去get和set方法,請您自行補齊
}
- 增加log表的實體類Log.java,裡面帶有swagger的註解,方便在swagger頁面展示:
package com.bolingcavalry.curd.entity;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import java.sql.Date;
/**
* @Description: 實體類
* @author: willzhao E-mail: zq2599@gmail.com
* @date: 2020/8/4 8:24
*/
@ApiModel(description = "日誌實體類")
public class Log {
@ApiModelProperty(value = "日誌ID")
private Integer id;
@ApiModelProperty(value = "使用者ID")
private Integer userId;
@ApiModelProperty(value = "日誌內容")
private String action;
@ApiModelProperty(value = "建立時間")
private Date createTime;
@Override
public String toString() {
return "Log{" +
"id=" + id +
", userId=" + userId +
", action='" + action + '\'' +
", createTime=" + createTime +
'}';
}
// 省去get和set方法,請您自行補齊
}
- 為聯表查詢的結果準備一個bean,名為LogExtend.java,繼承自Log.java,自己只有個userName欄位,對應聯表查詢user表的name欄位:
package com.bolingcavalry.curd.entity;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
@ApiModel(description = "日誌實體類(含使用者表的欄位)")
public class LogExtend extends Log {
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
@ApiModelProperty(value = "使用者名稱")
private String userName;
@Override
public String toString() {
return "LogExtend{" +
"id=" + getId() +
", userId=" + getUserId() +
", userName='" + getUserName() + '\'' +
", action='" + getAction() + '\'' +
", createTime=" + getCreateTime() +
'}';
}
}
- 增加user表的mapper對映檔案,可見都是些很簡單sql,要注意的是批量新增的節點,這裡面用到了foreach語法,可以通過集合動態生成sql:
<?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.bolingcavalry.curd.mapper.UserMapper">
<select id="sel" parameterType="int" resultType="user">
select * from user where id = #{id}
</select>
<!--新增單條記錄-->
<insert id="insertWithFields" useGeneratedKeys="true" keyProperty="id">
insert into user (id, name, age) values (#{id}, #{name}, #{age})
</insert>
<!--批量新增-->
<insert id="insertBatch" useGeneratedKeys="true" keyProperty="id">
insert into user (id, name, age)
values
<foreach collection="users" item="user" separator=",">
(#{user.id}, #{user.name}, #{user.age})
</foreach>
</insert>
<!--按照名稱查詢-->
<select id="findByName" parameterType="String" resultType="user">
select id, name, age from user where name like concat('%', #{name}, '%')
</select>
<!--刪除指定資料-->
<delete id="delete">
delete from user where id= #{id}
</delete>
<!--刪除所有資料-->
<delete id="clearAll">
delete from user
</delete>
<!--更新-->
<update id="update">
update user set name = #{name}, age = #{age} where id = #{id}
</update>
<!--獲取總數-->
<select id="totalCount" resultType="java.lang.Integer">
select count(*) from user
</select>
</mapper>
- 增加log表的mapper對映檔案,如下所示,請關注聯表操作selExtend,其結果是logExtendResultMap:
<?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.bolingcavalry.curd.mapper.LogMapper">
<resultMap id="logExtendResultMap" type="logExtend">
<id property="id" column="id"/>
<result column="user_id" jdbcType="INTEGER" property="userId"/>
<result column="action" jdbcType="VARCHAR" property="action"/>
<result column="create_time" jdbcType="TIMESTAMP" property="createTime"/>
<result column="user_name" jdbcType="TIMESTAMP" property="userName"/>
</resultMap>
<!--新增單條記錄-->
<insert id="insertWithFields" useGeneratedKeys="true" keyProperty="id" parameterType="log">
insert into log (id, user_id, action, create_time) values (#{id}, #{userId}, #{action}, #{createTime})
</insert>
<select id="selExtend" parameterType="int" resultMap="logExtendResultMap">
select l.id as id,
l.user_id as user_id,
l.action as action,
l.create_time as create_time,
u.name as user_name
from log as l
left join user as u
on l.user_id = u.id
where l.id = #{id}
</select>
</mapper>
- 增加使用者表的mapper介面類UserMapper.java ,對應著對映檔案中的sql節點的id:
package com.bolingcavalry.curd.mapper;
import com.bolingcavalry.curd.entity.LogExtend;
import com.bolingcavalry.curd.entity.User;
import org.springframework.stereotype.Repository;
import java.util.List;
@Repository
public interface UserMapper {
User sel(int id);
int insertWithFields(User user);
int insertBatch(List<User> users);
int clearAll();
List<User> findByName(String name);
int update(User user);
int delete(int id);
int totalCount();
LogExtend selExtend(int id);
}
- 增加日誌表的mapper介面類LogMapper.java,對應著對映檔案中的sql節點的id:
package com.bolingcavalry.curd.mapper;
import com.bolingcavalry.curd.entity.Log;
import com.bolingcavalry.curd.entity.LogExtend;
import org.springframework.stereotype.Repository;
@Repository
public interface LogMapper {
Log sel(int id);
LogExtend selExtend(int id);
int insertWithFields(Log log);
}
- mapper介面完成後就是service層,先寫user表的service,如下所示,可見都是對mapper介面的呼叫:
package com.bolingcavalry.curd.service;
import com.bolingcavalry.curd.entity.User;
import com.bolingcavalry.curd.mapper.UserMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
@Service
public class UserService {
@Autowired
UserMapper userMapper;
public User sel(int id) {
return userMapper.sel(id);
}
public User insertWithFields(User user) {
userMapper.insertWithFields(user);
return user;
}
public List<User> insertBatch(List<User> users) {
userMapper.insertBatch(users);
return users;
}
public int clearAll() {
return userMapper.clearAll();
}
public List<User> findByName(String name) {
return userMapper.findByName(name);
}
public int update(User user) {
return userMapper.update(user);
}
public int delete(int id) {
return userMapper.delete(id);
}
public int totalCount() {
return userMapper.totalCount();
}
}
- 還有log表的service:
package com.bolingcavalry.curd.service;
import com.bolingcavalry.curd.entity.Log;
import com.bolingcavalry.curd.entity.LogExtend;
import com.bolingcavalry.curd.entity.User;
import com.bolingcavalry.curd.mapper.LogMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
@Service
public class LogService {
@Autowired
LogMapper logMapper;
public Log sel(int id){
return logMapper.sel(id);
}
public LogExtend selExtend(int id) {
return logMapper.selExtend(id);
}
public Log insertWithFields(Log log) {
logMapper.insertWithFields(log);
return log;
}
}
- 最後是controller層了,由於使用了swagger,導致controller相對上一篇略微複雜(多了些註解):
package com.bolingcavalry.curd.controller;
import com.bolingcavalry.curd.entity.User;
import com.bolingcavalry.curd.service.UserService;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiImplicitParam;
import io.swagger.annotations.ApiOperation;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import java.util.ArrayList;
import java.util.List;
@RestController
@RequestMapping("/user")
@Api(tags = {"UserController"})
public class UserController {
@Autowired
private UserService userService;
@ApiOperation(value = "新增user記錄", notes="新增user記錄")
@RequestMapping(value = "/insertwithfields",method = RequestMethod.PUT)
public User create(@RequestBody User user) {
return userService.insertWithFields(user);
}
@ApiOperation(value = "批量新增user記錄", notes="批量新增user記錄")
@RequestMapping(value = "/insertbatch", method = RequestMethod.PUT)
public List<User> insertBatch(@RequestBody List<User> users) {
return userService.insertBatch(users);
}
@ApiOperation(value = "刪除指定ID的user記錄", notes="刪除指定ID的user記錄")
@ApiImplicitParam(name = "id", value = "使用者ID", paramType = "path", required = true, dataType = "Integer")
@RequestMapping(value = "/{id}", method = RequestMethod.DELETE)
public int delete(@PathVariable int id){
return userService.delete(id);
}
@ApiOperation(value = "刪除user表所有資料", notes="刪除user表所有資料")
@RequestMapping(value = "/clearall", method = RequestMethod.DELETE)
public int clearAll(){
return userService.clearAll();
}
@ApiOperation(value = "根據ID修改user記錄", notes="根據ID修改user記錄")
@RequestMapping(value = "/update", method = RequestMethod.POST)
public int update(@RequestBody User user){
return userService.update(user);
}
@ApiOperation(value = "根據名稱模糊查詢所有user記錄", notes="根據名稱模糊查詢所有user記錄")
@ApiImplicitParam(name = "name", value = "使用者名稱", paramType = "path", required = true, dataType = "String")
@RequestMapping(value = "/findbyname/{name}", method = RequestMethod.GET)
public List<User> findByName(@PathVariable("name") String name){
return userService.findByName(name);
}
@ApiOperation(value = "根據ID查詢user記錄", notes="根據ID查詢user記錄")
@ApiImplicitParam(name = "id", value = "使用者ID", paramType = "path", required = true, dataType = "Integer")
@RequestMapping(value = "/{id}", method = RequestMethod.GET)
public User GetUser(@PathVariable int id){
return userService.sel(id);
}
@ApiOperation(value = "獲取總數", notes="獲取總數")
@RequestMapping(value = "/totalcount", method = RequestMethod.GET)
public int totalcount(){
return userService.totalCount();
}
}
- log的controller如下:
package com.bolingcavalry.curd.controller;
import com.bolingcavalry.curd.entity.Log;
import com.bolingcavalry.curd.entity.LogExtend;
import com.bolingcavalry.curd.service.LogService;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiImplicitParam;
import io.swagger.annotations.ApiOperation;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
@RestController
@RequestMapping("/log")
@Api(tags = {"LogController"})
public class LogController {
@Autowired
private LogService logService;
@ApiOperation(value = "根據ID查詢日誌記錄", notes="根據ID查詢日誌記錄")
@ApiImplicitParam(name = "id", value = "日誌ID", paramType = "path", required = true, dataType = "Integer")
@RequestMapping(value = "/{id}", method = RequestMethod.GET)
public LogExtend logExtend(@PathVariable int id){
return logService.selExtend(id);
}
@ApiOperation(value = "新增日誌記錄", notes="新增日誌記錄")
@RequestMapping(value = "/insertwithfields",method = RequestMethod.PUT)
public Log create(@RequestBody Log log) {
return logService.insertWithFields(log);
}
}
- 最後是一段單元測試的程式碼,我們們試試通過junit進行自測,如下所示,可見一共測試了三個controller介面:先新增,再查詢,最後刪除,要注意的是MockMvc的用法,以及jsonPath方法的用法,還有就是通過Order註解控制執行順序(一定要新增TestMethodOrder註解,否則Order註解不生效):
package com.bolingcavalry.curd.controller;
import com.bolingcavalry.curd.entity.User;
import com.google.gson.Gson;
import com.google.gson.JsonArray;
import com.google.gson.JsonParser;
import org.junit.Ignore;
import org.junit.jupiter.api.*;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.autoconfigure.web.servlet.AutoConfigureMockMvc;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.http.MediaType;
import org.springframework.test.context.junit4.SpringRunner;
import org.springframework.test.web.servlet.MockMvc;
import org.springframework.test.web.servlet.request.MockMvcRequestBuilders;
import java.util.List;
import java.util.UUID;
import static org.hamcrest.Matchers.hasSize;
import static org.hamcrest.Matchers.is;
import static org.hamcrest.core.IsEqual.equalTo;
import static org.springframework.test.web.servlet.result.MockMvcResultHandlers.print;
import static org.springframework.test.web.servlet.result.MockMvcResultMatchers.*;
@RunWith(SpringRunner.class)
@SpringBootTest
@AutoConfigureMockMvc
@TestMethodOrder(MethodOrderer.OrderAnnotation.class)
class UserControllerTest {
@Autowired
private MockMvc mvc;
// user表的name欄位,這裡為了保證測試時新增和刪除的記錄是同一條,用UUID作為使用者名稱
static String testName;
@BeforeAll
static void init() {
testName = UUID.randomUUID().toString().replaceAll("-","");;
}
@Test
@Order(1)
void insertWithFields() throws Exception {
String jsonStr = "{\"name\": \"" + testName + "\", \"age\": 10}";
mvc.perform(
MockMvcRequestBuilders.put("/user/insertwithfields")
.contentType(MediaType.APPLICATION_JSON)
.content(jsonStr)
.accept(MediaType.APPLICATION_JSON))
.andExpect(status().isOk())
.andExpect(jsonPath("$.name", is(testName)))
.andDo(print())
.andReturn()
.getResponse()
.getContentAsString();
}
@Test
@Order(2)
void findByName() throws Exception {
mvc.perform(MockMvcRequestBuilders.get("/user/findbyname/"+ testName).accept(MediaType.APPLICATION_JSON))
.andExpect(status().isOk())
.andExpect(jsonPath("$", hasSize(1)))
.andDo(print());
}
@Test
@Order(3)
void delete() throws Exception {
// 先根據名稱查出記錄
String responseString = mvc.perform(MockMvcRequestBuilders.get("/user/findbyname/"+ testName).accept(MediaType.APPLICATION_JSON))
.andExpect(status().isOk())
.andExpect(jsonPath("$", hasSize(1)))
.andDo(print())
.andReturn()
.getResponse()
.getContentAsString();
// 反序列化得到陣列
JsonArray jsonArray = JsonParser.parseString(responseString).getAsJsonArray();
// 反序列化得到user例項
User user = new Gson().fromJson(jsonArray.get(0), User.class);
// 執行刪除
mvc.perform(MockMvcRequestBuilders.delete("/user/"+ user.getId()).accept(MediaType.APPLICATION_JSON))
.andExpect(status().isOk())
.andExpect(content().string(equalTo("1")))
.andDo(print());
}
}
- 至此編碼結束,開始驗證上述功能;
單元測試驗證
- IDEA開啟UserControllerTest.java,點選下圖紅框中的圖示即可開始執行單元測試:
2. 單元測試完成後IDEA會給出結果,如下圖,紅框右側可以檢視詳細的測試過程資料:
- 篇幅所限,這隻有少量的單元測試用例,接下來用swagger來驗證每個介面;
swagger驗證web介面
- 如下圖,啟動CurdApplication類:
2. 瀏覽器訪問:http://localhost:8080/swagger-ui.html ,即可開啟swagger頁面,如下圖:
3. 先試試新增的介面,操作如下圖:
- 點選了上圖紅框3的Try it out!按鈕後,響應資訊如下圖,可見操作成功:
5. 限於篇幅,其他介面的測試就不逐一列出了,請您自行驗證;
- 至此,MyBatis的基本增刪改查和簡單的聯表操作的實戰就完成了,接下來我們們會繼續探索MyBatis的基本操作;
你不孤單,欣宸原創一路相伴
歡迎關注公眾號:程式設計師欣宸
微信搜尋「程式設計師欣宸」,我是欣宸,期待與您一同暢遊Java世界...
https://github.com/zq2599/blog_demos