SpringBoot(十一) ----SpringBoot结合mybatis实现增删改查

我们可以使用SpringBoot，将SpringBoot与mybatis结合起来，实现对数据库的增删改查的操作。

接下来举个例子：

第一步，首先创建一个数据库test，并创建表tb_user，向tb_user中插入几条数据。

create database test;
-- ----------------------------
-- Table structure for tb_user
-- ----------------------------
use test;
DROP TABLE IF EXISTS `tb_user`;
CREATE TABLE `tb_user` (
  `id` int(11) NOT NULL,
  `username` varchar(255) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
-- ----------------------------
-- Records of tb_user
-- ----------------------------
INSERT INTO `tb_user` VALUES ('1', 'laowang', '112233');
INSERT INTO `tb_user` VALUES ('2', 'laoli', '123456');

第二步，构建我们的springboot项目，项目结构如下，

从项目的结构中，我们可以看到我们需要构建的项目文件，springboot启动类DemoApplication.java、控制层类UserController.java、实体类User.java、DAO层UserMapper.java接口文件、Service层UserService.java、mapping文件夹下UserMapper.xml映射文件、静态资源文件success.html和我们SpringBoot的application.yml配置文件、Maven配置文件pom.xml。

第三步，构建pom.xml文件,文件内容如下：

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>
 
    <groupId>com.example</groupId>
    <artifactId>demo</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <packaging>jar</packaging>
 
    <name>demo</name>
    <description>Demo project for Spring Boot</description>
 
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.0.5.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
 
    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <java.version>1.8</java.version>
    </properties>
 
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <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>1.3.2</version>
        </dependency>
 
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.15</version>
        </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>

这里核心引入与mybatis和mysql数据库相关的jar

<dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.15</version>
</dependency> 

第四步，配置application.yml数据库文件

server:
  port: 8080
 
spring:
  datasource:
    username: root
    password: 123456
    url: jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC
    driver-class-name: com.mysql.jdbc.Driver
 
mybatis:
  mapper-locations: classpath:mapping/*Mapper.xml
  type-aliases-package: com.example.entity
 
#showSql
logging:
  level:
    com:
      example:
        mapper : debug

第五步，构建controller、service、Dao、Data层文件内容

UserController.java

package com.example.controller;
 
import com.example.entity.User;
import com.example.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
 
/**
 * @Author:zk
 * @Date: 2020/4/20 0026
 * @Time: 14:42
 */
 
@RestController
@RequestMapping("/testBoot")
public class UserController {
 
    @Autowired
    private UserService userService;
 
    @RequestMapping("getUser/{id}")
    public String GetUser(@PathVariable int id){
        return userService.Sel(id).toString();
    }
     
    @RequestMapping("findAll")
    public String findAll(){
        return userService.SelAll().toString();
    }
     
    @RequestMapping("insert/id={id}&username={username}&password={password}")
    public String insert(User user){
        userService.insert(user);
        return "success";
    }
     
    @RequestMapping("delete/id={id}")
    public String delete(User user){
        userService.delete(user);
        return "success";
    }
     
    @RequestMapping("update/id={id}&username={username}&password={password}")
    public String update(User user){
        userService.update(user);
        return "success";
    }
}

User.java

package com.example.entity;
 
/**
 * @Author:zk
 * @Date: 2020/4/20 0026
 * @Time: 14:42
 */
public class User {
    private Integer id;
    private String username;
    private String password;
    
 
    public Integer getId() {
        return id;
    }
 
    public void setId(Integer 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;
    }
 
    @Override
    public String toString() {
        return "User [id=" + id + ", username=" + username + ", password=" + password + "]";
    }
     
     
}

UserMapper.java

package com.example.mapper;
 
import com.example.entity.User;
 
import java.util.List;
 
import org.springframework.stereotype.Repository;
 
/**
 * @Author:zk
 * @Date: 2020/4/20 0026
 * @Time: 14:42
 */
@Repository
public interface UserMapper {
 
    User Sel(int id);
 
    List<User> SelAll();
 
    int insert(User user);
 
    int deleteById(User user);
 
    int updateById(User user);
}

UserMapper.xml

<?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.example.mapper.UserMapper">
 
    <resultMap id="BaseResultMap" type="com.example.entity.User">
        <result column="id" jdbcType="INTEGER" property="id" />
        <result column="userName" jdbcType="VARCHAR" property="userName" />
        <result column="passWord" jdbcType="VARCHAR" property="passWord" />
    </resultMap>
 
    <select id="Sel" resultType="com.example.entity.User">
        select * from tb_user where id = #{id}
    </select>
 
    <select id="SelAll" resultType="com.example.entity.User">
        select * from tb_user
    </select>
 
    <insert id="insert" parameterType="com.example.entity.User">
        INSERT INTO tb_user
        (
        id,username,password
        )
        VALUES (
        #{id},
        #{username, jdbcType=VARCHAR},
        #{password, jdbcType=VARCHAR}
        )
    </insert>
 
    <delete id="deleteById">
        Delete from tb_user
        where id=#{id}
    </delete>
    <update id="updateById" parameterType="com.example.entity.User">
        Update tb_user
        SET
        id = #{id},
        username = #{username},
        password = #{password}
        where id = #{id}
    </update>
</mapper>

UserService.java

package com.example.service;
 
import com.example.entity.User;
import com.example.mapper.UserMapper;
 
import java.util.List;
 
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
 
/**
 * @Author:zk
 * @Date: 2020/4/20 0026
 * @Time: 14:42
 */
@Service
public class UserService {
    @Autowired
    UserMapper userMapper;
    public User Sel(int id){
        return userMapper.Sel(id);
    }
    public List<User> SelAll() {
        // TODO Auto-generated method stub
        return userMapper.SelAll();
    }
    public int insert(User user) {
        // TODO Auto-generated method stub
        return userMapper.insert(user);
    }
    public int delete(User user) {
        // TODO Auto-generated method stub
        return userMapper.deleteById(user);
    }
    public int update(User user) {
        // TODO Auto-generated method stub
        return userMapper.updateById(user);
    }
}

构建success.html静态资源文件

success.html

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
success
</body>
</html>

最后一步，设置启动类

DemoApplication.java

package com.example;
 
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
 
@MapperScan("com.example.mapper") //扫描的mapper
@SpringBootApplication
public class DemoApplication {
 
    public static void main(String[] args) {
        SpringApplication.run(DemoApplication.class, args);
    }
}