Spring Boot（九）：springboot结合mybatis实现增删改查

一、前言

最近在跟着视频做码匠社区项目，视频中连接H2数据库，但是我这个社区版的IDEA无法集成H2，暂时还未找到解决办法。

因此想到了之前练习的连接MySQL，为了能继续下去，只能先这样了，以后找到解决办法再改成H2吧！

二、项目架构

pom.xml

1<?xml version="1.0" encoding="UTF-8"?>
2<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
3	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
4	<modelVersion>4.0.0</modelVersion>
5	<parent>
6		<groupId>org.springframework.boot</groupId>
7		<artifactId>spring-boot-starter-parent</artifactId>
8		<version>2.2.4.RELEASE</version>
9		<relativePath/>
10	</parent>
11	<groupId>life.majiang.community</groupId>
12	<artifactId>community</artifactId>
13	<version>0.0.1-SNAPSHOT</version>
14	<name>community</name>
15	<description>community</description>
16
17	<properties>
18		<java.version>1.8</java.version>
19	</properties>
20
21	<dependencies>
22		<dependency>
23			<groupId>org.springframework.boot</groupId>
24			<artifactId>spring-boot-starter-web</artifactId>
25		</dependency>
26
27		<dependency>
28			<groupId>org.springframework.boot</groupId>
29			<artifactId>spring-boot-starter-thymeleaf</artifactId>
30		</dependency>
31
32		<!-- https://mvnrepository.com/artifact/org.springframework/spring-jdbc -->
33		<dependency>
34			<groupId>org.springframework</groupId>
35			<artifactId>spring-jdbc</artifactId>
36			<version>5.2.3.RELEASE</version>
37		</dependency>
38
39
40		<!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
41		<dependency>
42			<groupId>org.mybatis</groupId>
43			<artifactId>mybatis</artifactId>
44			<version>3.5.4</version>
45		</dependency>
46
47		<dependency>
48			<groupId>org.mybatis.spring.boot</groupId>
49			<artifactId>mybatis-spring-boot-starter</artifactId>
50			<version>1.3.2</version>
51		</dependency>
52
53		<dependency>
54			<groupId>mysql</groupId>
55			<artifactId>mysql-connector-java</artifactId>
56			<version>8.0.11</version>
57		</dependency>
58
59		<dependency>
60			<groupId>org.springframework.boot</groupId>
61			<artifactId>spring-boot-starter-test</artifactId>
62			<scope>test</scope>
63			<exclusions>
64				<exclusion>
65					<groupId>org.junit.vintage</groupId>
66					<artifactId>junit-vintage-engine</artifactId>
67				</exclusion>
68			</exclusions>
69		</dependency>
70	</dependencies>
71
72	<build>
73		<plugins>
74			<plugin>
75				<groupId>org.springframework.boot</groupId>
76				<artifactId>spring-boot-maven-plugin</artifactId>
77			</plugin>
78		</plugins>
79	</build>
80
81</project>

三、springboot集成MySQL

如果不做更改，test connection不会成功。

四、注解版实现增删改查

我习惯从controller开写

1package life.majiang.community.controller;
2
3import life.majiang.community.bean.User;
4import life.majiang.community.mapper.UserMapper;
5import org.springframework.beans.factory.annotation.Autowired;
6import org.springframework.stereotype.Controller;
7import org.springframework.web.bind.annotation.GetMapping;
8import org.springframework.web.bind.annotation.RequestParam;
9
10@Controller
11public class UserController {
12    @Autowired
13    UserMapper userMapper;
14    @Autowired
15    User user;
16
17    @GetMapping("/getUserById")
18    public String getUserById(@RequestParam(name="id") String id){
19        System.out.println(userMapper.getUserById(id).name);
20        return "index";
21    }
22
23    @GetMapping("/getUsers")
24    public String getUsers(){
25        return "index";
26    }
27
28    @GetMapping("/insertUser")
29    public String insertUser(@RequestParam(name="id") String id,@RequestParam(name="name") String name,@RequestParam(name="password") String password){
30        user.setId(id);
31        user.setName(name);
32        user.setPassword(password);
33        userMapper.insertUser(user);
34        return "index";
35    }
36}

UserMapper

1package life.majiang.community.mapper;
2
3import life.majiang.community.bean.User;
4import org.apache.ibatis.annotations.Insert;
5import org.apache.ibatis.annotations.Select;
6
7public interface UserMapper {
8    @Select("select * from user where id=#{id}")
9    public User getUserById(String id);
10
11    @Insert("insert into user(ID,name,password) values (#{id},#{name},#{password})")
12    public void insertUser(User user);
13
14    @Select("select * from user")
15    public User getUsers();
16}

测试

五、配置实现增删改查

1package life.majiang.community.controller;
2
3import life.majiang.community.bean.User;
4import life.majiang.community.mapper.UserMapper;
5import org.springframework.beans.factory.annotation.Autowired;
6import org.springframework.stereotype.Controller;
7import org.springframework.web.bind.annotation.GetMapping;
8import org.springframework.web.bind.annotation.RequestParam;
9
10@Controller
11public class UserController {
12    @Autowired
13    UserMapper userMapper;
14    @Autowired
15    User user;
16
17    @GetMapping("/getUserById2")
18    public String getUserById2(@RequestParam(name="id") String id){
19        System.out.println(userMapper.getUserById2(id).name);
20        return "index";
21    }
22
23    @GetMapping("/insertUser2")
24    public String insertUser2(@RequestParam(name="id") String id,@RequestParam(name="name") String name,@RequestParam(name="password") String password){
25        user.setId(id);
26        user.setName(name);
27        user.setPassword(password);
28        userMapper.insertUser2(user);
29        return "index";
30    }
31
32    @GetMapping("/deleteUserById")
33    public String deleteUserById(@RequestParam(name="id") String id){
34        userMapper.deleteUserById(id);
35        return "index";
36    }
37}

UserMapper

1package life.majiang.community.mapper;
2
3import life.majiang.community.bean.User;
4import org.apache.ibatis.annotations.Insert;
5import org.apache.ibatis.annotations.Select;
6
7public interface UserMapper {
8    public User getUserById2(String id);
9    public void insertUser2(User user);
10    public void deleteUserById(String id);
11}

UserMapper.xml

1<?xml version="1.0" encoding="UTF-8" ?>
2<!DOCTYPE mapper
3        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
4        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
5<mapper namespace="life.majiang.community.mapper.UserMapper">
6    <select id="getUserById2" resultType="life.majiang.community.bean.User">
7        SELECT * FROM user WHERE id=#{id};
8    </select>
9   <insert id="insertUser2">
10      insert into user(ID,name,password) values (#{id},#{name},#{password});
11   </insert>
12    <delete id="deleteUserById">
13        delete from user where id=#{id};
14    </delete>
15</mapper>

application.yml

测试

六、总结

springboot通过mybatis实现增删改查还是挺简单的，但也有很多问题需要注意，比如@MapperScan，application.yml的配置，驼峰的设置等等。