SpringBoot整合mybatis实现增删改查、分页查询

丨康有为丨

已于 2022-12-20 20:04:33 修改

阅读量991

点赞数 6

分类专栏： SpringBoot 文章标签： mybatis spring boot java

于 2022-12-20 19:27:01 首次发布

本文链接：https://blog.csdn.net/KangYouWei6/article/details/128388629

版权

SpringBoot 专栏收录该内容

10 篇文章 1 订阅

订阅专栏

前提：

先搭建出最基本的SpringBoot项目

SpringBoot框架快速入门搭建Hello World，请点击下面链接：https://blog.csdn.net/KangYouWei6/article/details/127018638

一、建立数据库

/*
 Navicat Premium Data Transfer

 Source Server         : 本地数据库
 Source Server Type    : MySQL
 Source Server Version : 50649
 Source Host           : localhost:3306
 Source Schema         : springboot

 Target Server Type    : MySQL
 Target Server Version : 50649
 File Encoding         : 65001

 Date: 20/12/2022 18:15:34
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `createtime` datetime(0) DEFAULT CURRENT_TIMESTAMP,
  `updatetime` datetime(0) DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP(0),
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = MyISAM AUTO_INCREMENT = 17 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES (1, '海贼王', '路飞', 11, '2022-12-20 10:13:37', '2022-12-20 10:20:20');
INSERT INTO `user` VALUES (2, '剑豪', '索隆', 11, '2022-12-20 10:13:37', '2022-12-20 10:20:23');
INSERT INTO `user` VALUES (3, '航海士', '娜美', 11, '2022-12-20 10:20:59', '2022-12-20 10:20:59');
INSERT INTO `user` VALUES (4, '色厨师', '山治', 11, '2022-12-20 10:21:55', '2022-12-20 10:22:33');

SET FOREIGN_KEY_CHECKS = 1;

二、建立下图目录与文件

pom.xml

其中配置了SpringMVC、mybatis、mysql、lombok、pageHelper

将下面你的项目所缺少的依赖加入到 <dependencies>标签中即可

    <dependencies>
        <!--web开发的起步 依赖SpringMvc-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <!--整合mybatis和mysql-->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.1</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>

        <!--整合lombok-->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>

        <!--pageHelper分页插件-->
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper-spring-boot-starter</artifactId>
            <version>1.3.0</version>
        </dependency>

    </dependencies>

application.yaml

server:
  port: 8081
spring:
  datasource:
    url: jdbc:mysql://localhost:3306/springboot?useUnicode=true&characterEncoding=utf-8&serverTimezone=CTT
    username: root
    password: 123456789
    driver-class-name: com.mysql.cj.jdbc.Driver
mybatis:
  mapper-locations: classpath:mapper/*.xml  #对应mapper映射xml文件所在路径
  type-aliases-package: com.kyw.entity  #对应实体类entity层路径
logging.level.com.kyw.mapper: debug #mybatis配置日志文件，选择自己的mapper层路径
pagehelper:
  helperDialect: mysql
  reasonable: true # 修改默认值

实体类 entity层：User

package com.kyw.entity;

import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.Data;

import java.util.Date;

@Data
public class User {
    private Long id;
    private String name;
    private int age;
    private String username;
    @JsonFormat(pattern = "yyyy-MM-dd", timezone = "GMT+8")//统一时间格式
    private Date createtime;
    @JsonFormat(pattern = "yyyy-MM-dd", timezone = "GMT+8")//统一时间格式
    private Date updatetime;
}

mapper层：UserMapper

package com.kyw.mapper;
import com.kyw.entity.User;
import java.util.List;

public interface UserMapper {
    //findAll方法
    List<User> findAll();


    //增加
    int  save(User user);

    //删除
    int  deleteById(Integer id);

    //修改
    int  updateById(User user);

    //查询全部
    List<User> list();

    //根据id查询
    User getById(Integer id);

}

service层：UserService

package com.kyw.service;
import com.kyw.entity.User;
import java.util.List;

public interface UserService {
    List<User> findAll();

    //增加
    int  save(User user);

    //删除
    int  deleteById(Integer id);

    //修改
    int  update(User user);

    //查询全部
    List<User> list();

    //根据id查询
    User getById(Integer id);

    //分页查询
    List<User> findPage(Integer pageNo,Integer pageSize);


}

impl层：UserServiceImpl

package com.kyw.service.impl;
import com.github.pagehelper.PageHelper;
import com.kyw.entity.User;
import com.kyw.mapper.UserMapper;
import com.kyw.service.UserService;
import org.springframework.stereotype.Service;
import javax.annotation.Resource;
import java.util.List;

@Service
public class UserServiceImpl implements UserService {
    //    @Autowired  Autowired和Resource都可以
    @Resource
    private UserMapper userMapper;

    @Override
    public List<User> findAll() {
        return userMapper.findAll();
    }

    @Override
    //增
    public int  save(User user) {
       return userMapper.save(user);
    }

    @Override
    //删
    public int  deleteById(Integer id) {
       return userMapper.deleteById(id);
    }

    @Override
    //改
    public int  update(User user) {
       return userMapper.updateById(user);
    }

    @Override
    //查询
    public List<User> list() {
        return userMapper.list();
    }

    @Override
    //根据id查询
    public User getById(Integer id) {
        return userMapper.getById(id);
    }


    //分页查询
    @Override
    public List<User> findPage(Integer pageNo, Integer pageSize) {
        PageHelper.startPage(pageNo,pageSize);
        return userMapper.list();//这里的list方法是查询全部数据的方法
    }

}

xml层：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">
<!--namespace选择mapper层（dao）层下的文件-->
<!--如果按住ctrl键能点击就说明写对了-->
<mapper namespace="com.kyw.mapper.UserMapper">
    <!-- type里面选择entity层下面的文件-->
    <!--如果按住ctrl键能点击就说明写对了-->
    <resultMap id="BaseResultMap" type="com.kyw.entity.User">
        <!--这里对应数据库字段，和字段的类型-->
        <id column="id" jdbcType="BIGINT" property="id" />
        <result column="name" jdbcType="VARCHAR" property="name" />
        <result column="age" jdbcType="BIGINT" property="age" />
    </resultMap>
    <!--公用的数据库字段-->
    <sql id="Base_Column_List">
        id,name,age
    </sql>


    <!--增加-->
    <insert id="save">
        insert into user(name, username, age)
        values(#{name}, #{username}, #{age})
    </insert>

    <!--修改-->
    <update id="updateById">
        update user set username = #{username},name = #{name}, age = #{age}
                         where id = #{id}
    </update>

    <!--删除-->
    <delete id="deleteById">
        delete from user where id = #{id}
    </delete>

    <!--查询全部-->
    <select id="list" resultType="com.kyw.entity.User">
        select * from user
    </select>

    <!--根据id查询-->
    <select id="getById" resultType="com.kyw.entity.User">
        select * from user where id = #{id}
    </select>


</mapper>

设置统一返回值结果类Result

1．设计统一的返回值结果类型便于前端开发读取数据

2．返回值结果类型可以根据需求自行设定，没有固定格式

3．返回值结果模型类用于后端与前端进行数据格式统一，也称为前后端数据协议

package com.kyw.common;
import lombok.Data;

/**
 * 封装通用返回类
 */
@Data
public class Result {
    //定义两个常量，成功的code是200，失败的是-1
    //前端可以根据 后端反馈的code来得知操作是否成功
    private static final String SUCCESS_CODE = "200";
    private static final String ERROR_CODE = "-1";

    private String code;//code：接口的响应结果
    private Object data;//data：数据
    private String msg;//msg：存放错误信息

    //无参数的成功方法，只返回成功代码“200”
    public static Result success() {
        Result result = new Result();
        result.setCode(SUCCESS_CODE);
        return result;
    }

    //有参数的成功方法，返回成功代码“200” 和 数据data
    public static Result success(Object data) {
        Result result = new Result();
        result.setCode(SUCCESS_CODE);
        result.setData(data);
        return result;
    }

    //失败的方法，返回自定义错误信息 和 错误代码“-1”
    public static Result error(String msg) {
        Result result = new Result();
        result.setCode(ERROR_CODE);
        result.setMsg(msg);
        return result;
    }

    //失败的方法，返回自定义错误信息 和 自定义错误代码
    public static Result error(String code, String msg) {
        Result result = new Result();
        result.setCode(code);
        result.setMsg(msg);
        return result;
    }

}

controller控制层：UserController

package com.kyw.controller;
import com.kyw.common.Result;
import com.kyw.entity.User;
import com.kyw.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import java.util.List;

@RestController
@RequestMapping("/user")//这是Controller路径
public class UserController {

    @Autowired
    private UserService userService;

    @RequestMapping("/findAll")//这是方法路径
    public List<User> findAll() {
        return userService.findAll();
    }

    //查
    @GetMapping("/list")
    public Result list() {
        List<User> users = userService.list();
        return Result.success(users);
    }

    //增加
    @PostMapping("/save")
    public Result save(@RequestBody User user) {
        int num = userService.save(user);//增删改的sql执行之后都会有一个int类型的返回值，表示的意思是这个操作影响的行数。
        if (num == 1){//如果有一条数据受影响 那就说明成功了
            return Result.success();
        }else {
            return Result.error("添加数据失败~~~");
        }
    }
    //删
    /**
     * 加了@PathVariable：它是一个占位符，为后面的参数id在URL中占一个位置
     * "/delete/{id}"：在写路径时，就可以直接将id写在后面
     *  例如：http://localhost:8081/user/delete/8，而不是delete?id=8
     */
    @DeleteMapping("/delete/{id}")
    public Result delete(@PathVariable Integer id) {
        int num = userService.deleteById(id);//增删改的sql执行之后都会有一个int类型的返回值，表示的意思是这个操作影响的行数。
        if (num == 1){//如果有一条数据受影响 那就说明成功了
            return Result.success();
        }else {
            return Result.error("删除数据失败~~~");
        }
    }

    //改
    @PutMapping("/update")
    public Result update(@RequestBody User user) {
        int num = userService.update(user);//增删改的sql执行之后都会有一个int类型的返回值，表示的意思是这个操作影响的行数。
        if (num == 1){//如果有一条数据受影响 那就说明成功了
            return Result.success();
        }else {
            return Result.error("修改数据失败~~~");
        }

    }

    //根据id查询
    //加了PathVariable注解，所以可以直接http://localhost:8081/user/1
    @GetMapping("/{id}")
    public Result getById(@PathVariable Integer id) {
        User user = userService.getById(id);
        return Result.success(user);
    }

    //分页查询
    @RequestMapping("findPage")
    public Result findPage(int pageNo,Integer pageSize){
        return Result.success(userService.findPage(pageNo,pageSize));
    }
}

启动类启动：StartApplication

package com.kyw;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

/**
 * 引导类。SpringBoot项目的入口
 */
@SpringBootApplication
// 启动类扫描mapper层
@MapperScan("com.kyw.mapper")
public class StartApplication {
    public static void main(String[] args) {
        SpringApplication.run(StartApplication.class, args);
    }
}

全部代码写完，开始连接测试！

测试

因为没有写前端页面，我们用浏览器和PostMan软件来链接测试

查询全部

http://localhost:8081/user/list

返回结果：

{

    "code": "200",

    "data": [

        {

            "id": 1,

            "name": "路飞",

            "age": 11,

            "username": "海贼王",

            "createtime": "2022-12-20",

            "updatetime": "2022-12-20"

        },

        {

            "id": 2,

            "name": "索隆",

            "age": 11,

            "username": "剑豪",

            "createtime": "2022-12-20",

            "updatetime": "2022-12-20"

        },

        {

            "id": 3,

            "name": "娜美",

            "age": 11,

            "username": "航海士",

            "createtime": "2022-12-20",

            "updatetime": "2022-12-20"

        },

        {

            "id": 4,

            "name": "山治",

            "age": 11,

            "username": "色厨师",

            "createtime": "2022-12-20",

            "updatetime": "2022-12-20"

        },

        {

            "id": 17,

            "name": "lll",

            "age": 10,

            "username": "kkk",

            "createtime": "2022-12-20",

            "updatetime": "2022-12-20"

        }

    ],

    "msg": null

}

增加数据

http://localhost:8081/user/save

因为增加的接口写的是PostMapping，所以我们用PostMan软件来模拟前端发送的post请求

返回结果：

删除数据

http://localhost:8081/user/delete/19

因为我们写的是DeleteMapping，所以也用PostMan来模拟

修改数据

http://localhost:8081/user/update

因为我们写的是PutMapping，所以也用PostMan来模拟

根据id查询

localhost:8081/user/1

返回结果

{

    "code": "200",

    "data": {

        "id": 1,

        "name": "路飞",

        "age": 11,

        "username": "海贼王",

        "createtime": "2022-12-20",

        "updatetime": "2022-12-20"

    },

    "msg": null

}

分页查询

localhost:8081/user/findPage?pageNo=1&pageSize=2

返回结果

{

    "code": "200",

    "data": [

        {

            "id": 1,

            "name": "路飞",

            "age": 11,

            "username": "海贼王",

            "createtime": "2022-12-20",

            "updatetime": "2022-12-20"

        },

        {

            "id": 2,

            "name": "索隆",

            "age": 11,

            "username": "剑豪",

            "createtime": "2022-12-20",

            "updatetime": "2022-12-20"

        }

    ],

    "msg": null

}