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

创始人

2024-04-27 06:59:14

0次

前提：

先搭建出最基本的SpringBoot项目

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

一、建立数据库

/*Navicat Premium Data TransferSource Server         : 本地数据库Source Server Type    : MySQLSource Server Version : 50649Source Host           : localhost:3306Source Schema         : springbootTarget Server Type    : MySQLTarget Server Version : 50649File Encoding         : 65001Date: 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

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

    org.springframework.bootspring-boot-starter-weborg.mybatis.spring.bootmybatis-spring-boot-starter2.1.1mysqlmysql-connector-javaruntimeorg.projectlomboklomboktruecom.github.pagehelperpagehelper-spring-boot-starter1.3.0

application.yaml

server:port: 8081
spring:datasource:url: jdbc:mysql://localhost:3306/springboot?useUnicode=true&characterEncoding=utf-8&serverTimezone=CTTusername: rootpassword: 123456789driver-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: mysqlreasonable: 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 findAll();//增加int  save(User user);//删除int  deleteById(Integer id);//修改int  updateById(User user);//查询全部List 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 findAll();//增加int  save(User user);//删除int  deleteById(Integer id);//修改int  update(User user);//查询全部List list();//根据id查询User getById(Integer id);//分页查询List 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都可以@Resourceprivate UserMapper userMapper;@Overridepublic List 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 list() {return userMapper.list();}@Override//根据id查询public User getById(Integer id) {return userMapper.getById(id);}//分页查询@Overridepublic List findPage(Integer pageNo, Integer pageSize) {PageHelper.startPage(pageNo,pageSize);return userMapper.list();//这里的list方法是查询全部数据的方法}}

xml层：UserMapper.xml





id,name,ageinsert into user(name, username, age)values(#{name}, #{username}, #{age})update user set username = #{username},name = #{name}, age = #{age}where id = #{id}delete from user where id = #{id}

设置统一返回值结果类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” 和 数据datapublic 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 {@Autowiredprivate UserService userService;@RequestMapping("/findAll")//这是方法路径public List findAll() {return userService.findAll();}//查@GetMapping("/list")public Result list() {List 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