MyBatis初級實戰之二：增刪改查

本文是《MyBatis初級實戰》系列的第二篇，前文《 》我們知道了如何在SpringBoot中集成MyBatis，本篇就一起來練習基本功：增刪改查；

本篇概覽

本篇要練習的內容如下：

1. 單表的增刪改查
2. 批量新增
3. 聯表查詢

全文由以下部分組成：

1. 新建工程
2. 增加啟動類
3. 增加swagger的配置類，工程包含了swagger，以便稍後在瀏覽器上驗證
4. 增加配置文件
5. 增加實體類
6. 增加mapper配置文件
7. 增加mapper接口
8. 增加service，調用mapper接口
9. 增加controller，調用service服務
10. 編寫單元測試用例
11. 驗證

源碼下載

如果您不想編碼，可以在GitHub下載所有源碼，地址和連結信息如下表所示(
https://github.com/zq2599/blog_demos)：

• 這個git項目中有多個文件夾，《MyBatis初級實戰》系列的源碼在mybatis文件夾下，如下圖紅框所示：

開發

• 本文的實戰使用的資料庫和表結構與前文《 》一模一樣；
• 前文《 》新建了父工程mybatis，本文繼續在此工程中新增子工程，名為curd，整個子工程文件結構如下：

• 修改父工程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=&34; encoding=&34;?><project xmlns=&34; xmlns:xsi=&34; xsi:schemaLocation=&34;> <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(&34;)public class CurdApplication { public static void main(String[] args) { SpringApplication.run(CurdApplication.class, args); }}

• 本次實戰用到了swagger，這樣可以很方便的通過瀏覽器向各個controller接口發送請求，以下是swagger配置類：

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@EnableSwagger2public class SwaggerConfig { @Bean public Docket createRestApi() { return new Docket(DocumentationType.SWAGGER_2) .apiInfo(apiInfo()) .tags(new Tag(&34;, &34;), new Tag(&34;, &34;)) .select() // 當前包路徑 .apis(RequestHandlerSelectors.basePackage(&34;)) .paths(PathSelectors.any()) .build(); } //構建 api文檔的詳細信息函數,注意這裡的註解引用的是哪個 private ApiInfo apiInfo() { return new ApiInfoBuilder() //頁面標題 .title(&34;) //創建人 .contact(new Contact(&34;, &34;, &34;)) //版本號 .version(&34;) //描述 .description(&34;) .build(); }}

• application.yml內容如下：

server: port: 8080spring: mybatis配置mybatis: 映射文件所在位置 mapper-locations: classpath:mappers/*Mapper.xml34;用戶實體類&34;用戶ID&34;用戶名&34;用戶地址&34;User{&34;id=&34;, name=&34; + name + &39;&34;, age=&39;}&34;日誌實體類&34;日誌ID&34;用戶ID&34;日誌內容&34;創建時間&34;Log{&34;id=&34;, userId=&34;, action=&34; + action + &39;&34;, createTime=&39;}&34;日誌實體類(含用戶表的欄位)&34;用戶名&34;LogExtend{&34;id=&34;, userId=&34;, userName=&34; + getUserName() + &39;&34;, action=&34; + getAction() + &39;&34;, createTime=&39;}&34;1.0&34;UTF-8&34;-//mybatis.org//DTD Mapper 3.0//EN&34;http://mybatis.org/dtd/mybatis-3-mapper.dtd&34;com.bolingcavalry.curd.mapper.UserMapper&34;sel&34;int&34;user&{id} </select> <!--新增單條記錄--> <insert id=&34; useGeneratedKeys=&34; keyProperty=&34;> insert into user (id, name, age) values ({name}, 34;insertBatch&34;true&34;id&34;users&34;user&34;,&{user.id}, {user.age}) </foreach> </insert> <!--按照名稱查找--> <select id=&34; parameterType=&34; resultType=&34;> select id, name, age from user where name like concat(&39;, 39;%&34;delete&{id} </delete> <!--刪除所有數據--> <delete id=&34;> delete from user </delete> <!--更新--> <update id=&34;> update user set name = {age} where id = 34;totalCount&34;java.lang.Integer&34;1.0&34;UTF-8&34;-//mybatis.org//DTD Mapper 3.0//EN&34;http://mybatis.org/dtd/mybatis-3-mapper.dtd&34;com.bolingcavalry.curd.mapper.LogMapper&34;logExtendResultMap&34;logExtend&34;id&34;id&34;user_id&34;INTEGER&34;userId&34;action&34;VARCHAR&34;action&34;create_time&34;TIMESTAMP&34;createTime&34;user_name&34;TIMESTAMP&34;userName&34;insertWithFields&34;true&34;id&34;log&{id}, {action}, 34;selExtend&34;int&34;logExtendResultMap&{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;@Repositorypublic 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;@Repositorypublic 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;@Servicepublic 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;@Servicepublic 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(&34;)@Api(tags = {&34;})public class UserController { @Autowired private UserService userService; @ApiOperation(value = &34;, notes=&34;) @RequestMapping(value = &34;,method = RequestMethod.PUT) public User create(@RequestBody User user) { return userService.insertWithFields(user); } @ApiOperation(value = &34;, notes=&34;) @RequestMapping(value = &34;, method = RequestMethod.PUT) public List<User> insertBatch(@RequestBody List<User> users) { return userService.insertBatch(users); } @ApiOperation(value = &34;, notes=&34;) @ApiImplicitParam(name = &34;, value = &34;, paramType = &34;, required = true, dataType = &34;) @RequestMapping(value = &34;, method = RequestMethod.DELETE) public int delete(@PathVariable int id){ return userService.delete(id); } @ApiOperation(value = &34;, notes=&34;) @RequestMapping(value = &34;, method = RequestMethod.DELETE) public int clearAll(){ return userService.clearAll(); } @ApiOperation(value = &34;, notes=&34;) @RequestMapping(value = &34;, method = RequestMethod.POST) public int update(@RequestBody User user){ return userService.update(user); } @ApiOperation(value = &34;, notes=&34;) @ApiImplicitParam(name = &34;, value = &34;, paramType = &34;, required = true, dataType = &34;) @RequestMapping(value = &34;, method = RequestMethod.GET) public List<User> findByName(@PathVariable(&34;) String name){ return userService.findByName(name); } @ApiOperation(value = &34;, notes=&34;) @ApiImplicitParam(name = &34;, value = &34;, paramType = &34;, required = true, dataType = &34;) @RequestMapping(value = &34;, method = RequestMethod.GET) public User GetUser(@PathVariable int id){ return userService.sel(id); } @ApiOperation(value = &34;, notes=&34;) @RequestMapping(value = &34;, 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(&34;)@Api(tags = {&34;})public class LogController { @Autowired private LogService logService; @ApiOperation(value = &34;, notes=&34;) @ApiImplicitParam(name = &34;, value = &34;, paramType = &34;, required = true, dataType = &34;) @RequestMapping(value = &34;, method = RequestMethod.GET) public LogExtend logExtend(@PathVariable int id){ return logService.selExtend(id); } @ApiOperation(value = &34;, notes=&34;) @RequestMapping(value = &34;,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(&34;,&34;);; } @Test @Order(1) void insertWithFields() throws Exception { String jsonStr = &34;name\&34;&34;\&34;age\&34;; mvc.perform( MockMvcRequestBuilders.put(&34;) .contentType(MediaType.APPLICATION_JSON) .content(jsonStr) .accept(MediaType.APPLICATION_JSON)) .andExpect(status().isOk()) .andExpect(jsonPath(&34;, is(testName))) .andDo(print()) .andReturn() .getResponse() .getContentAsString(); } @Test @Order(2) void findByName() throws Exception { mvc.perform(MockMvcRequestBuilders.get(&34;+ testName).accept(MediaType.APPLICATION_JSON)) .andExpect(status().isOk()) .andExpect(jsonPath(&34;, hasSize(1))) .andDo(print()); } @Test @Order(3) void delete() throws Exception { // 先根據名稱查出記錄 String responseString = mvc.perform(MockMvcRequestBuilders.get(&34;+ testName).accept(MediaType.APPLICATION_JSON)) .andExpect(status().isOk()) .andExpect(jsonPath(&34;, 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(&34;+ user.getId()).accept(MediaType.APPLICATION_JSON)) .andExpect(status().isOk()) .andExpect(content().string(equalTo(&34;))) .andDo(print()); }}

• 至此編碼結束，開始驗證上述功能；

單元測試驗證

• IDEA打開UserControllerTest.java，點擊下圖紅框中的圖標即可開始執行單元測試：

• 單元測試完成後IDEA會給出結果，如下圖，紅框右側可以查看詳細的測試過程數據：

• 篇幅所限，這隻有少量的單元測試用例，接下來用swagger來驗證每個接口；

swagger驗證web接口

• 如下圖，啟動CurdApplication類：

• 瀏覽器訪問：http://localhost:8080/swagger-ui.html ，即可打開swagger頁面，如下圖：

• 先試試新增的接口，操作如下圖：

• 點擊了上圖紅框3的Try it out!按鈕後，響應信息如下圖，可見操作成功：

• 限於篇幅，其他接口的測試就不逐一列出了，請您自行驗證；

至此，MyBatis的基本增刪改查和簡單的聯表操作的實戰就完成了，接下來的文章咱們會繼續探索MyBatis的基本操作；

歡迎關注我的公眾號：程式設計師欣宸