基于springboot+mybatis实现数据库CRUD

这里使用IDEA基于springboot+mybatis实现对数据库的增删改查。

新建一个spring boot 项目

 pom文件引入相关依赖

   <dependencies>

<!--        spring boot test 相关-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>

        <!--        mybatis相关-->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.2.0</version>
        </dependency>
        <!--        mysql相关-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>

        <!--        JDBC相关-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>

<!--        spring web 相关-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

    </dependencies>

项目结构

在com.example.springdemo包下分别创建包controller、entity、mapper、service。在resources下创建mapping文件夹。具体的代码结构如下图所示：

代码实现

新建数据库database，并在该数据库下建表user，包含id（int），name（varchar），password（varchar），phoneNum（varchar），插入几组数据。

在entity文件下新建User.java，变量名与数据库字段名一致

package com.example.springdemo.entity;

public class User {
    private Integer id;
    private String name;
    private String password;
    private String phoneNum;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getPhoneNum() {
        return phoneNum;
    }

    public void setPhoneNum(String phoneNum) {
        this.phoneNum = phoneNum;
    }
    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", password='" + password + '\'' +
                ", phoneNum='" + phoneNum + '\'' +
                '}';
    }
}

在mapper包下新建UserMapper接口

package com.example.springdemo.mapper;

import com.example.springdemo.entity.User;

import org.apache.ibatis.annotations.Mapper;
import org.springframework.stereotype.Repository;

import java.util.List;
@Mapper
@Repository
public interface UserMapper {

    User getUserInfo(int id);
    int save(User user);
    int update(User user);
    int deleteById(int id);
    List<User> selectAll();

}

在service包下新建实现类UserService.java

package com.example.springdemo.service;
import com.example.springdemo.entity.User;
import com.example.springdemo.mapper.UserMapper;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class UserService {
    @Autowired
    private UserMapper userMapper;
    public User getUserInfo(int id){
        return userMapper.getUserInfo(id);
    }
    public int deleteById(int id){
        return userMapper.deleteById(id);
    }
    public int Update(User user){
        return userMapper.update(user);
    }
    public User save(User user){
        int save = userMapper.save(user);
        return user;
    }
    public List<User> selectAll(){
        return userMapper.selectAll();
    }

}

在controller包下新建访问类UserController.java

package com.example.springdemo.controller;


import com.example.springdemo.entity.User;
import com.example.springdemo.service.UserService;
import com.example.springdemo.service.GoodService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import java.util.List;

//@RequestMapping设置的是URL的路径 在controller类上面的  就是接在端口号后面的
@RestController
@RequestMapping("/testBoot")
public class UserController {

    @Autowired
    private UserService userService;

    @Autowired
    private GoodService goodService;
    //

    @GetMapping("/findGoodByID")
    public String findGoodByID(int goodID){
        return goodService.findByID(goodID);
    }

    @GetMapping("/selectAllGood")
    public List selectAllGood(){
        return goodService.selectAllGood();
    }
    @RequestMapping(value = "getUser/{id}",method = RequestMethod.GET)
    public String GetUser(@PathVariable int id) {
        return userService.getUserInfo(id).toString();
    }


    @RequestMapping(value = "/delete", method = RequestMethod.GET)
    public String delete(int id){
        int result = userService.deleteById(id);
        if(result>=1){
            return "delete success";
        }
        return "delete false";

    }

    //
    @RequestMapping(value = "/update", method = RequestMethod.POST)
    public String update(User user) {
        int result = userService.Update(user);
        if (result >= 1) {
            return "update success";
        } else {
            return "update false";
        }
    }
    //

    @RequestMapping(value = "/insert", method = RequestMethod.POST)
    public User insert(User user) {
        return userService.save(user);
    }

    @RequestMapping("/selectAll")


    @ResponseBody
    public List<User> ListUser() {
        return userService.selectAll();
    }

}

在src/main/resources/mapping文件夹下新建UserMapper的映射文件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.springdemo.mapper.UserMapper">

    <resultMap id="BaseResultMap" type="com.example.springdemo.entity.User">
        <result column="id" jdbcType="INTEGER" property="id" />
        <result column="name" jdbcType="VARCHAR" property="username" />
        <result column="password" jdbcType="VARCHAR" property="password" />
        <result column="phoneNum" jdbcType="VARCHAR" property="phoneNum" />
    </resultMap>
    <!--查询用户信息-->
    <select id="getUserInfo" resultType="com.example.springdemo.entity.User">
        select * from user where id = #{id}
    </select>
    <!--删除用户信息-->
    <delete id="deleteById" parameterType="int">
        delete from user where id=#{id}
    </delete>
    <!--返回所有用户信息-->
    <select id="selectAll"  resultType="com.example.springdemo.entity.User">
        select * from user
    </select>

    <!--增加用户信息-->
    <insert id="save" parameterType="com.example.springdemo.entity.User" >
        insert into user
        <trim prefix="(" suffix=")" suffixOverrides="," >
            <if test="id != null" >
                id,
            </if>
            <if test="name != null" >
                name,
            </if>
            <if test="password != null" >
                password,
            </if>

            <if test=" phoneNum != null" >
                phoneNum,
            </if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides="," >
            <if test="id != null" >
                #{id,jdbcType=INTEGER},
            </if>
            <if test="name != null" >
                #{name,jdbcType=VARCHAR},
            </if>
            <if test="password != null" >
                #{password,jdbcType=VARCHAR},
            </if>
            <if test="phoneNum != null" >
                #{phoneNum,jdbcType=VARCHAR},
            </if>
        </trim>
    </insert>

    <!--根据id更改用户信息-->
    <update id="update" parameterType="com.example.springdemo.entity.User">
        update user
        <set >
            <if test="name != null" >
                name = #{name,jdbcType=VARCHAR},
            </if>
            <if test="password != null" >
                password = #{password,jdbcType=VARCHAR},
            </if>
            <if test="phoneNum != null" >
                phoneNum = #{phoneNum,jdbcType=VARCHAR},
            </if>
        </set>
        where id = #{id,jdbcType=INTEGER}
    </update>
</mapper>

启动入口类添加注解

@SpringBootApplication
@EnableEurekaClient
@EnableFeignClients
public class SpringdemoApplication {

    public static void main(String[] args) {
        SpringApplication.run(SpringdemoApplication.class, args);
    }

}

使用postman工具进行测试

增：

 

 删：

 

改：

 

 

查：