Springboot+MybatisPlus实现增删改查和分页和连表查询

最新推荐文章于 2024-06-04 16:10:49 发布

漱石枕流-

最新推荐文章于 2024-06-04 16:10:49 发布

阅读量1.1k

点赞数 3

文章标签： java spring boot

本文链接：https://blog.youkuaiyun.com/Demon_666/article/details/121673876

版权

Springboot MybatisPlus 分页查询联表查询 CRUD

关键词由优快云通过智能技术生成

Springboot+MybatisPlus实现增删改查和分页和连表查询

项目目录

在这里插入图片描述
sql语句

#user表
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `uid` bigint(20) NOT NULL COMMENT '主键ID',
  `name` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '姓名',
  `age` int(11) NULL DEFAULT NULL COMMENT '年龄',
  `email` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '邮箱',
  `department_id` bigint(20) NULL DEFAULT NULL COMMENT '外键(部门表)',
  PRIMARY KEY (`uid`) USING BTREE,
  INDEX `department_id`(`department_id`) USING BTREE,
  CONSTRAINT `user_ibfk_1` FOREIGN KEY (`department_id`) REFERENCES `department` (`did`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES (1, 'Jone', 18, 'test1@baomidou.com', 1001);
INSERT INTO `user` VALUES (2, 'Jack', 20, 'test2@baomidou.com', 1001);
INSERT INTO `user` VALUES (3, 'Tom', 28, 'test3@baomidou.com', 1002);
INSERT INTO `user` VALUES (4, 'Sandy', 21, 'test4@baomidou.com', 1002);
INSERT INTO `user` VALUES (5, 'Jone2', 18, 'test111@baomidou.com', 1003);
INSERT INTO `user` VALUES (6, 'Jack2', 20, 'test222@baomidou.com', 1003);
INSERT INTO `user` VALUES (7, 'Tom2', 28, 'test333@baomidou.com', 1001);
INSERT INTO `user` VALUES (8, 'Sandy2', 21, 'test444@baomidou.com', 1001);
INSERT INTO `user` VALUES (9, 'Billie2', 24, 'test555@baomidou.com', 1002);
INSERT INTO `user` VALUES (10, 'Jone3', 48, 'test122@baomidou.com', 1002);
INSERT INTO `user` VALUES (11, 'Jack4', 40, 'test244@baomidou.com', 1002);
INSERT INTO `user` VALUES (12, 'Tom5', 38, 'test355@baomidou.com', 1002);
INSERT INTO `user` VALUES (13, 'Sandy3', 51, 'test466@baomidou.com', 1003);
INSERT INTO `user` VALUES (14, 'Billie4', 21, 'test5777@baomidou.com', 1003);
INSERT INTO `user` VALUES (1465947360045182977, '库里', 3, '3333333@qq.com', 1001);
INSERT INTO `user` VALUES (1465947691877543938, '詹姆斯', 36, '222222222@qq.com', 1003);
INSERT INTO `user` VALUES (1466597588570107906, 'Jack2', 36, '222222222@qq.com', 1001);
INSERT INTO `user` VALUES (1466597640294264833, 'Jack2', 46, '333333333332@qq.com', 1002);
INSERT INTO `user` VALUES (1466597686251253762, 'Jack2', 12, '4444444@qq.com', 1003);

SET FOREIGN_KEY_CHECKS = 1;



#department表
DROP TABLE IF EXISTS `department`;
CREATE TABLE `department`  (
  `did` bigint(20) NOT NULL COMMENT '部门ID',
  `departmentName` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '部门名',
  `departmentNum` int(11) NULL DEFAULT NULL COMMENT '部门人数',
  PRIMARY KEY (`did`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of department
-- ----------------------------
INSERT INTO `department` VALUES (1001, '技术部', 18);
INSERT INTO `department` VALUES (1002, '财务部', 5);
INSERT INTO `department` VALUES (1003, '市场部', 1);

SET FOREIGN_KEY_CHECKS = 1;

添加依赖:pom.xml

<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
        <scope>test</scope>
    </dependency>
    <!--mysql-->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
    </dependency>
    <!--lombok-->
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
    </dependency>
    <!--mybatis-plus-->
    <dependency>
        <groupId>com.baomidou</groupId>
        <artifactId>mybatis-plus-boot-starter</artifactId>
        <version>3.0.5</version>
    </dependency>
    <!-- 模板引擎 -->
    <dependency>
        <groupId>org.apache.velocity</groupId>
        <artifactId>velocity-engine-core</artifactId>
        <version>2.0</version>
    </dependency>
    <!-- 引入Druid依赖，阿里巴巴所提供的数据源 -->
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>druid</artifactId>
        <version>1.0.29</version>
    </dependency>
</dependencies>

配置文件application.properties

# mysql 5 驱动不同  com.mysql.jdbc.Driver
# mysql 8 驱动不同 com.mysql.cj.jdbc.Driver . 需要增加时区的配置 serverTimezone=GMT%2B8
spring.datasource.username=root
spring.datasource.password=123456
spring.datasource.url=jdbc:mysql://localhost:3306/mybatis_plus?useSSL=false&useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT%2B8
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

创建一个MybatisPlus分页的拦截器MybatisPlusConfig，如果不创建，MybatisPlus就不会在sql上增加分页的语句

@Configuration
@ConditionalOnClass(value = {PaginationInterceptor.class})
public class MybatisPlusConfig {
    @Bean
    public PaginationInterceptor paginationInterceptor() {
        PaginationInterceptor paginationInterceptor = new PaginationInterceptor();
        return paginationInterceptor;
    }
}

建立实体类User

@TableName("user")
public class User {
    private Long id;
    private String name;
    private Integer age;
    private String email;
    //getter and setter
}

mapper层，建立UserMapper继承BaseMapper

@Repository//代表持久层
@Mapper
public interface UserMapper extends BaseMapper<User> {}

修改启动类MybatisPlusApplication，添加@MapperScan注解

@MapperScan("com.wyx.mapper")
@SpringBootApplication
public class MybatisPlusApplication {

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

service层

public interface UserService {
    //增
    Integer add(User user);

    //删
    Integer delete(Long id);

    //改
    Integer update(User user);

    //查
    User select(Long id);

    //分页
    public IPage<User> selectPage(long page, long size);
}

建立serviceImpl

@Service
public class UserServiceImpl implements UserService {
    @Autowired
    UserMapper userMapper;
    
    @Override
    public Integer add(User user) {
        return userMapper.insert(user);
    }

    @Override
    public Integer delete(Long id) {
        return userMapper.deleteById(id);
    }

    @Override
    public Integer update(User user) {
        return userMapper.updateById(user);
    }

    @Override
    public User select(Long id) {
        return userMapper.selectById(id);
    }

    @Override
    public IPage<User> selectPage(long page, long size) {
        IPage<User> iPage = userMapper.selectPage(new Page<>(page,size),null);
        return iPage;
    }
}

controller层

@Controller
@RequestMapping("/wyx")
public class UserController {
    @Autowired
    UserService userService;
    /**
     * 增加
     */
    @RequestMapping("/add")
    @ResponseBody
    public Integer addUser(@RequestBody User user){
        return userService.add(user);
    }

    /**
     * 删除
     */
    @RequestMapping("/delete")
    @ResponseBody
    public Integer deleteUser(@RequestBody User user){
        return userService.delete(user.getId());
    }

    /**
     * 修改
     */
    @RequestMapping("/update")
    @ResponseBody
    public Integer updateUser(@RequestBody User user){
        return userService.update(user);
    }

    /**
     * 查询
     */
    @RequestMapping("/select")
    @ResponseBody
    public User selectUser(@RequestBody User user){
        return userService.select(user.getId());
    }

    /**
     * 分页
     * @param page
     */
    @RequestMapping("/page")
    @ResponseBody
    public List<User> getUserPage(@Param("page") long page){
        long size = 5;
        IPage<User> ipage =userService.selectPage(page,size);
        return ipage.getRecords();
    }
}

调用apipost接口测试

查
http://127.0.0.1:8080/wyx/select
{
	"id": 2
}

增
http://127.0.0.1:8080/wyx/add
{
    "name": "詹姆斯",
    "age": 36,
    "email": "222222222@qq.com"
}

改
http://127.0.0.1:8080/wyx/update
{
    "id": 3,
    "name": "库里",
    "age": 3,
    "email": "3333333@qq.com"
}

删
http://127.0.0.1:8080/wyx/delete
{
    "id": 5
}

分页
http://127.0.0.1:8080/wyx/page
page   2

分页:
在这里插入图片描述
在控制台打印日志:执行的sql语句

在application.properties中加

logging.level.com.wyx.mapper=debug
mybatis.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl

在这里插入图片描述

联表查询接口编写

创建两张有关联的表

在这里插入图片描述

Mapper层

UserMapper新增一个接口

@Mapper
public interface UserMapper extends BaseMapper<User> {
    //联表查询用户和他们的所属部门
    List<User> selectByUid(@Param("uid") Long uid);
}

在resources目录下新建mapper目录,创建UserMapper.xml并编写sql语句

<?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.wyx.mapper.UserMapper">
    <select id="selectByUid" resultType="java.util.Map">
        select u.name, u.age, d.departmentName
        from user u
        left join department d
        on u.department_id = d.did
        where u.uid =#{uid}
    </select>
</mapper>

Service层

UserService新增接口

//联表查询
List selectByUid(Long uid);

实现类UserServiceImpl新增重写方法

@Override
public List selectByUid(Long uid) {
    return userMapper.selectByUid(uid);
}

Controller层

UserController新增一个方法

/**
 * 联表查询
 */
@RequestMapping("/union")
@ResponseBody
public List selectByUid(@RequestBody User user){
    return userService.selectByUid(user.getUid());
}