springboot+mybatis实现增、删、改、查等操作（超级详细）

项目的基本结构

一、多表查询（一对一、多对一）

1、配置文件信息

application.yml文件中配置数据库四要素，连接数据库
application.yml

spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/mybatis_dev?&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai
    username: root
    password: "个人数据库密码（纯数字密码要用双引号）"

application.properties

#下面这些内容是为了让MyBatis映射
#指定Mybatis的Mapper文件
mybatis.mapper-locations=classpath:mapper/*xml
#指定Mybatis的实体目录
mybatis.type-aliases-package=com.example.mybatis_dev.mybatis.entity
#应用服务 WEB 访问端口
server.port=8080

注： application.properties文件在创建项目时，可以自动生成，但里面的信息一定要根据个人实际情况设置值(尤其是mapper文件所在目录，端口号)
本人遇坑 在指定Mybatis的Mapper文件时，自动生成mappers/*xml，而实际是mapper/*xml

2、项目中所需依赖

pom.xml

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-thymeleaf</artifactId>
        </dependency>
        <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>2.2.2</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.23</version>
        </dependency>
        <dependency>
            <groupId>com.mysql</groupId>
            <artifactId>mysql-connector-j</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <!--        json解析-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.76</version>
        </dependency>
<!--        分页查询依赖-->
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper-spring-boot-starter</artifactId>
            <version>1.4.7</version>
        </dependency>
    </dependencies>

注：在使用分页查询依赖时，应注意其依赖的版本

3、数据库表构建

1. 新建数据库

2. 新建数据库表格
   account表

CREATE TABLE `account`  (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '账户表',
  `uid` int NULL DEFAULT NULL COMMENT '用户id外键',
  `money` double NULL DEFAULT NULL COMMENT '账户余额',
  `delete_ll` int NULL DEFAULT NULL COMMENT '逻辑删除（1：未删除，0：删除）',
  `view_count` int NULL DEFAULT NULL COMMENT '登录次数',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 9 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

user表

CREATE TABLE `user`  (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '用户信息表',
  `username` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '用户姓名',
  `sex` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '用户性别',
  `birthday` datetime NULL DEFAULT NULL COMMENT '用户出生日期',
  `address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '用户户籍所在地',
  `delete_ll` int NULL DEFAULT NULL COMMENT '逻辑删除（0：删除，1：未删除）',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

注：在account表中有user表的外键(uid)

4、实体类

4.1.1 user实体类

user.java

package com.example.mybatis_dev.entity;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.util.Date;

/**
 * ClassName:User
 * Package:IntelliJ IDEA
 *
 * @Create 2024/1/12 21:44
 */

/**
 * 用户实体类
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
public class User {
    private Integer id;
    private String userName;
    private String sex;
    private Date birthday;
    private String address;
    private Integer deleteLl;
}

4.1.2 account实体类

account.java

package com.example.mybatis_dev.entity;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

/**
 * ClassName:Account
 * Package:IntelliJ IDEA
 *
 * @Create 2024/1/12 21:46
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Account {
    private Integer id;
    private Double money;
    private Integer deleteLl;
    private Integer viewCount;//登录次数
//    用户外键
    private User user;
}

注意：在account实体类中有对应user实体类类型的属性（user）,重点！！！

5、mapper层

accountMapper.java

package com.example.mybatis_dev.mapper;

import com.example.mybatis_dev.entity.Account;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

import java.util.List;

/**
 * ClassName:AccountMapper
 * Package:IntelliJ IDEA
 *
 * @Create 2024/1/12 21:59
 */
@Mapper
public interface AccountMapper {
    /**
     * 添加
     * @param account
     * @return
     */
    Boolean addAccount(Account account);

    /**
     * 删除
     * @param id
     * @return
     */
    Boolean deleteAccount(int id);

    /**
     * 逻辑删除
     * @param id
     * @return
     */
    Boolean logicDelete(int id);

    /**
     * 修改
     * @param account
     * @return
     */
    Boolean updateAccount(Account account);

    /**
     * 根据id查询账户
     * @param id
     * @return
     */
    Account selectById(int id);
    /**
     * 查询所有
     * @return
     */
    List<Account> selectAllAccount();

    /**
     * 多条件查询
     * @param id
     * @param uid
     * @param money
     * @return
     */
    List<Account> selectByCons(@Param("id") int id,@Param("uid") int uid,@Param("money") double money);

    /**
     * 根据普通字段搜索多条数据
     * @param money
     * @return
     */
    List<Account> selectByMoney(@Param("money") double money);

    /**
     * 查看次数
     * @param account
     * @return
     */
    Integer loginCount(Account account);
}

6、mapper层配置文件xxxMapper.xml

accountMapper.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.mybatis_dev.mapper.AccountMapper">
    <resultMap id="account" type="com.example.mybatis_dev.entity.Account">
        <result column="id" property="id"/>
        <result column="money" property="money"/>
        <result column="delete_ll" property="deleteLl"/>
        <result column="view_count" property="viewCount"/>
        <association property="user" javaType="com.example.mybatis_dev.entity.User">
            <id column="id" property="id"/>
            <result column="username" property="userName"/>
            <result column="sex" property="sex"/>
            <result column="birthday" property="birthday"/>
            <result column="address" property="address"/>
            <result column="delete_ll" property="deleteLl"/>
        </association>
    </resultMap>
    <!--    添加-->
    <insert id="addAccount">
        insert into account(id, money, uid, delete_ll, view_count)
        values (#{id}, #{money}, #{user.id}, 1, 1)
    </insert>

    <!--    删除-->
    <delete id="deleteAccount">
        delete
        from account
        where id = #{id}
    </delete>
    <!--    逻辑删除-->
    <update id="logicDelete">
        update account
        set delete_ll=0
        where id = #{id}
    </update>

    <!--    修改-->
    <update id="updateAccount">
        update account
        <set>
            <if test="user!=null and user!=''">uid=#{user.id},</if>
            <if test="money!=null and money!=''">money=#{money},</if>
            <if test="deleteLl!=null and deleteLl!=''">delete_ll=#{deleteLl},</if>
        </set>
        where id=#{id}
    </update>

    <!--    根据id查询账户-->
    <select id="selectById" resultMap="account">
        select *
        from account
                 left join user on user.id = account.uid
        where account.id = #{id}
    </select>

    <!--查询所有-->
    <select id="selectAllAccount" resultMap="account">
        select *
        from account
                 left join user on user.id = account.uid
        where account.delete_ll = 1
    </select>

    <!--    多条件查询-->
    <select id="selectByCons" resultMap="account">
        select * from account
        left join user on user.id = account.uid
        <where>
            <if test="id!=null and id!=''">
                and account.id=#{id}
            </if>
            <if test="uid!=null and uid!=''">
                and account.uid=#{uid}
            </if>
            <if test="money!=null and money!=''">
                and account.money=#{money}
            </if>
            and account.delete_ll=1
        </where>
    </select>

    <!--    根据普通字段查询多条数据-->
    <select id="selectByMoney" resultMap="account">
        select *
        from account
                 left join user on user.id = account.uid
        where account.money = #{money}
    </select>

    <!--    浏览量-->
    <update id="loginCount">
        update account
        set view_count=view_count + 1
        where id = #{id}
    </update>
</mapper>

注意：数据库表和实体类的映射，重点！！！

7、service层

7.1.1 service接口

AccountService.java

package com.example.mybatis_dev.service;

import com.example.mybatis_dev.controller.dto.RequestParamDto;
import com.example.mybatis_dev.entity.Account;
import com.github.pagehelper.PageInfo;

import java.util.List;

/**
 * ClassName:AccountService
 * Package:IntelliJ IDEA
 *
 * @Create 2024/1/12 22:05
 */
public interface AccountService {
    /**
     * 添加
     * @param account
     * @return
     */
    Boolean insertAccount(Account account);

    /**
     * 简单删除
     * @param id
     * @return
     */
    Boolean deleteAccount(int id);

    /**
     * 逻辑删除
     * @param id
     * @return
     */
    Boolean logicDeleteAccount(int id);

    /**
     * 修改
     * @param account
     * @return
     */
    Boolean updateAccount(Account account);

    /**
     * 根据id查询账户
     * @param id
     * @return
     */
    Account selectAccountById(int id);
    /**
     * 查询所有
     * @return
     */
    List<Account> selectAll();

    /**
     * 多条件查询

     * @return
     */
    List<Account> selectAccountByCons(RequestParamDto requestParamDto);

    /**
     * 根据普通字段搜索多条数据
     * @param money
     * @return
     */
    List<Account> selectByMoney(double money);

    /**
     * 分页查询
     * @return
     */
    PageInfo<Account> selectByPage(int currentPage, int pageSize);

    /**
     * 查看账户次数
     * @param account
     * @return
     */
    Integer loginCount(Account account);
}

7.1.2 service接口实现类

AccountServiceImpl.java

package com.example.mybatis_dev.service.impl;

import com.example.mybatis_dev.controller.dto.RequestParamDto;
import com.example.mybatis_dev.entity.Account;
import com.example.mybatis_dev.mapper.AccountMapper;
import com.example.mybatis_dev.service.AccountService;
import com.github.pagehelper.Page;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

/**
 * ClassName:AccountServiceImpl
 * Package:IntelliJ IDEA
 *
 * @Create 2024/1/12 22:05
 */
@Service
public class AccountServiceImpl implements AccountService {
    @Autowired
    AccountMapper accountMapper;

    @Override
    public Boolean insertAccount(Account account) {
        return accountMapper.addAccount(account);
    }

    @Override
    public Boolean deleteAccount(int id) {
        return accountMapper.deleteAccount(id);
    }

    @Override
    public Boolean logicDeleteAccount(int id) {
        return accountMapper.logicDelete(id);
    }

    @Override
    public Boolean updateAccount(Account account) {
        return accountMapper.updateAccount(account);
    }

    @Override
    public Account selectAccountById(int id) {
        return accountMapper.selectById(id);
    }

    @Override
    public List<Account> selectAll() {
        return accountMapper.selectAllAccount();
    }

    @Override
    public List<Account> selectAccountByCons(RequestParamDto requestParamDto) {
        return accountMapper.selectByCons(requestParamDto.getId(),requestParamDto.getUid(),requestParamDto.getMoney());
    }

    @Override
    public List<Account> selectByMoney(double money) {
        return accountMapper.selectByMoney(money);
    }

    @Override
    public PageInfo<Account> selectByPage(int currentPage, int pageSize) {
        Page<Account> page= PageHelper.startPage(currentPage,pageSize);
        accountMapper.selectAllAccount();
        return page.toPageInfo();
    }

    @Override
    public Integer loginCount(Account account) {
        return accountMapper.loginCount(account);
    }
}

8、controller层

LimitOutput

package com.example.mybatis_dev.controller.dto;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

/**
 * ClassName:LimitOutput
 * Package:IntelliJ IDEA
 *
 * @Create 2024/1/13 20:12
 */

/**
 * 限定输出数据
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
public class LimitOutput {
    private Integer uid;
    private Double money;
}

RequestParamDto.java

package com.example.mybatis_dev.controller.dto;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

/**
 * ClassName:requestParamDto
 * Package:IntelliJ IDEA
 *
 * @Create 2024/1/13 11:11
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
public class RequestParamDto {
    private Integer id;
    private Integer uid;
    private Double money;
}

AccountController.java

package com.example.mybatis_dev.controller;

import com.example.mybatis_dev.controller.dto.LimitOutput;
import com.example.mybatis_dev.controller.dto.RequestParamDto;
import com.example.mybatis_dev.entity.Account;
import com.example.mybatis_dev.service.AccountService;
import com.example.mybatis_dev.util.Page;
import com.example.mybatis_dev.util.R;
import com.github.pagehelper.PageInfo;
import lombok.extern.slf4j.Slf4j;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.ArrayList;
import java.util.List;

/**
 * ClassName:AccountController
 * Package:IntelliJ IDEA
 *
 * @Create 2024/1/12 22:07
 */
@RestController
@RequestMapping("/mybatis")
@Slf4j
public class AccountController {
    private final Logger logger = LoggerFactory.getLogger(AccountController.class);
    @Autowired
    AccountService accountService;

    /**
     * 添加
     *
     * @param account
     * @return
     */
    @PostMapping("/insert")
    public R insertAccount(@RequestBody Account account) {
        Boolean flag = accountService.insertAccount(account);
        return R.ok(flag);
    }

    /**
     * 简单删除
     *
     * @param account
     * @return
     */
    @PostMapping("/delete")
    public R deleteAccount(@RequestBody Account account) {
        Boolean flag = accountService.deleteAccount(account.getId());
        return R.ok(flag);
    }

    /**
     * 逻辑删除
     *
     * @param account
     * @return
     */
    @PostMapping("/logicDelete")
    public R logicDeleteAccount(@RequestBody Account account) {
        Boolean flag = accountService.logicDeleteAccount(account.getId());
        return R.ok(flag);
    }

    /**
     * 修改+新增操作
     *
     * @param account
     * @return
     */
    @PostMapping("/update")
    public R updateAccount(@RequestBody Account account) {
//        1.判断是否存在
        Account account1 = accountService.selectAccountById(account.getId());
        if (account1 == null || account1.equals("")) {
            Boolean flag = accountService.insertAccount(account);
            return R.ok("该账户不存在，实现新增操作");
        } else {
            Boolean flag = accountService.updateAccount(account);
            return R.ok(flag);
        }
    }

    /**
     * 查询所有
     *
     * @return
     */
    @PostMapping("/selectAll")
    public R<List<Account>> selectAll() {
        List<Account> accounts = accountService.selectAll();

        if (accounts.isEmpty()) {
            return R.fail("没有数据");
        } else {
            logger.info(accounts.toString());
            return R.ok(accounts);
        }
    }

    /**
     * 根据id查询
     *
     * @param account
     * @return
     */
    @PostMapping("/selectById")
    public R selectById(@RequestBody Account account) {
        Account account1 = accountService.selectAccountById(account.getId());
        return R.ok(account1);
    }

    /**
     * 多条件查询
     *
     * @param
     * @return
     */
    @PostMapping("/selectByCons")
    public R<List<Account>> selectByCons(@RequestBody RequestParamDto requestParamDto) {
        List<Account> accounts = accountService.selectAccountByCons(requestParamDto);

        if (accounts == null) {
            return R.fail("没有数据");
        } else {
            logger.info(accounts.toString());
            return R.ok(accounts);
        }
    }

    /**
     * 根据普通字段搜索多条数据（批量搜索）
     *
     * @param requestParamDto
     * @return
     */
    @PostMapping("/selectByMoney")
    public R<List<Account>> selectByMoney(@RequestBody RequestParamDto requestParamDto) {
        List<Account> accounts = accountService.selectByMoney(requestParamDto.getMoney());
        if (accounts == null || accounts.isEmpty()) {
            return R.fail("没有数据");
        } else {
            logger.info(accounts.toString());
            return R.ok(accounts);
        }
    }

    /**
     * 分页查询
     * @param page
     * @return
     */
    @PostMapping("/selectByPage")
    public R selectByPage(@RequestBody Page page) {
        PageInfo<Account> accounts = accountService.selectByPage(page.getCurrentPage(), page.getPageSize());
        if (accounts == null) {
            return R.fail("没有数据");
        } else {
            logger.info(accounts.toString());
            return R.ok(accounts);
        }
    }

    /**
     * 查看账户次数
     * @param account
     * @return
     */
    @PostMapping("/logincount")
    public R loginCount(@RequestBody Account account){
        Integer loginCount=accountService.loginCount(account);
        return R.ok(loginCount);
    }

    /**
     * 限定输出数据
     * @return
     */
    @PostMapping("/limitoutput")
    public R limitOut(){
        List<Account> accounts=accountService.selectAll();
        //存放指定输出数据的数据集合
        List<LimitOutput> limitOutputs=new ArrayList<>();
        if(accounts==null){
            return R.fail("没有数据");
        }
        else{
            for(Account account:accounts){
                //限定输出数据的单个数据
                LimitOutput limitOutput1=new LimitOutput();

                limitOutput1.setUid(account.getUser().getId());
                limitOutput1.setMoney(account.getMoney());

                limitOutputs.add(limitOutput1);
            }
            return R.ok(limitOutputs);
        }
    }
}

9、工具类

Page.java

package com.example.mybatis_dev.util;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

/**
 * ClassName:Page
 * Package:IntelliJ IDEA
 *
 * @Create 2024/1/13 19:35
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Page {
    private Integer currentPage;
    private Integer pageSize;
}

R.java

package com.example.mybatis_dev.util;

import java.io.Serializable;

public class R<T> implements Serializable {
    /**
     * 成功
     */
    public static final int SUCCESS = 200;
    /**
     * 失败
     */
    public static final int FAIL = 500;
    private static final long serialVersionUID = 1L;
    private int code;

    private String msg;

    private T data;

    public static <T> R<T> ok() {
        return restResult(null, SUCCESS, "操作成功");
    }

    public static <T> R<T> ok(T data) {
        return restResult(data, SUCCESS, "操作成功");
    }

    public static <T> R<T> ok(T data, String msg) {
        return restResult(data, SUCCESS, msg);
    }

    public static <T> R<T> fail() {
        return restResult(null, FAIL, "操作失败");
    }

    public static <T> R<T> fail(String msg) {
        return restResult(null, FAIL, msg);
    }

    public static <T> R<T> fail(T data) {
        return restResult(data, FAIL, "操作失败");
    }

    public static <T> R<T> fail(T data, String msg) {
        return restResult(data, FAIL, msg);
    }

    public static <T> R<T> fail(int code, String msg) {
        return restResult(null, code, msg);
    }

    private static <T> R<T> restResult(T data, int code, String msg) {
        R<T> apiResult = new R<>();
        apiResult.setCode(code);
        apiResult.setData(data);
        apiResult.setMsg(msg);
        return apiResult;
    }

    public static <T> Boolean isError(R<T> ret) {
        return !isSuccess(ret);
    }

    public static <T> Boolean isSuccess(R<T> ret) {
        return R.SUCCESS == ret.getCode();
    }

    public int getCode() {
        return code;
    }

    public void setCode(int code) {
        this.code = code;
    }

    public String getMsg() {
        return msg;
    }

    public void setMsg(String msg) {
        this.msg = msg;
    }

    public T getData() {
        return data;
    }

    public void setData(T data) {
        this.data = data;
    }
}

二、多表查询（一对多）

1.entity

同上

2.mapper

UserMapper.java

package com.example.mybatis_dev.mapper;

import com.example.mybatis_dev.entity.User;
import org.apache.ibatis.annotations.Mapper;

import java.util.List;

@Mapper
public interface UserMapper {
    /**
     * 查询所有用户
     * @return
     */
    List<User> findAllUser();
}

3.mapper配置类

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.mybatis_dev.mapper.UserMapper">
    <resultMap id="user" type="com.example.mybatis_dev.entity.User">
        <id property="id" column="id"/>
        <result property="userName" column="username"/>
        <result property="sex" column="sex"/>
        <result property="address" column="address"/>

        <collection property="accounts" ofType="com.example.mybatis_dev.entity.Account">
            <id column="id" property="id"/>
            <result column="uid" property="uid"/>
            <result column="money" property="money"/>
            <result column="delete_ll" property="deleteLl"/>
            <result column="view_count" property="viewCount"/>
        </collection>
    </resultMap>
<!--查询所有-->
    <select id="findAllUser" resultMap="user">
        select *
        from user
        left outer join account on user.id = account.uid
        where user.delete_ll = 1
    </select>
</mapper>

4.service

userService.java

package com.example.mybatis_dev.service;

import com.example.mybatis_dev.entity.User;

import java.util.List;

public interface UserService {
    /**
     * 查询所有
     * @return
     */
    List<User> selectAllUser();
}

userServiceImpl.java
```java
package com.example.mybatis_dev.service.impl;

import com.example.mybatis_dev.entity.User;
import com.example.mybatis_dev.mapper.UserMapper;
import com.example.mybatis_dev.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class UserServiceImpl implements UserService {
    @Autowired
    UserMapper userMapper;
    @Override
    public List<User> selectAllUser() {
        return userMapper.findAllUser();
    }
}

5.controller

userController.java

package com.example.mybatis_dev.controller;

import com.example.mybatis_dev.entity.User;
import com.example.mybatis_dev.service.UserService;
import com.example.mybatis_dev.utils.R;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RestController
@RequestMapping("/user")
public class UserController {
    private Logger logger= LoggerFactory.getLogger(UserController.class);
    /**
     * 查询
     */
    @Autowired
    UserService userService;
    @PostMapping("/selectAll")
    public R selectAll(){
        List<User> users = userService.selectAllUser();
        System.out.println(users);
        if(users.isEmpty()){
            return R.fail("没有数据");
        }
        else{
            logger.info(users.toString());
            return R.ok(users);
        }
    }
}

三、多表查询（多对多）

user_role表

CREATE TABLE `user_role`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `uid` int NULL DEFAULT NULL COMMENT '用户id',
  `rid` int NULL DEFAULT NULL COMMENT '角色id',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

1.entity

role.java

package com.example.mybatis_dev.entity;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;

import java.util.List;

@Data
@AllArgsConstructor
@NoArgsConstructor
@ToString
public class Role {
    private Integer roleId;
    private String roleName;
    private String RoleDesc;

    private List<User> users;
}

2. mapper

roleMapper.java

package com.example.mybatis_dev.mapper;

import com.example.mybatis_dev.entity.Role;
import org.apache.ibatis.annotations.Mapper;

import java.util.List;

@Mapper
public interface RoleMapper {
    List<Role> findAllRole();
}

3.mapper配置类

<?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.mybatis_dev.mapper.RoleMapper">
    <resultMap id="roleMap" type="com.example.mybatis_dev.entity.Role">
        <id column="roleId" property="roleId"/>
        <result property="roleName" column="role_name"/>
        <result property="roleDesc" column="role_desc"/>
        <collection property="users" ofType="com.example.mybatis_dev.entity.User">
            <id column="id" property="id"/>
            <result column="username" property="userName"/>
            <result column="address" property="address"/>
            <result column="sex" property="sex"/>
            <result column="birthday" property="birthday"/>
        </collection>
    </resultMap>

    <select id="findAllRole" resultMap="roleMap">
        select *
        from role r
                 left outer join user_role ur on r.id = ur.rid
                 left outer join user on user.id = ur.uid
    </select>
</mapper>

左外连接

4.service

roleService.java

package com.example.mybatis_dev.service;

import com.example.mybatis_dev.entity.Role;

import java.util.List;

public interface RoleService {
    List<Role> findRole();
}

roleServiceImpl.java

package com.example.mybatis_dev.service.impl;

import com.example.mybatis_dev.entity.Role;
import com.example.mybatis_dev.mapper.RoleMapper;
import com.example.mybatis_dev.service.RoleService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class RoleServiceImpl implements RoleService {
    @Autowired
    RoleMapper roleMapper;
    @Override
    public List<Role> findRole() {
        return roleMapper.findAllRole();
    }
}

5.controller

roleController.java

package com.example.mybatis_dev.controller;

import com.example.mybatis_dev.entity.Role;
import com.example.mybatis_dev.service.RoleService;
import com.example.mybatis_dev.utils.R;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RestController
@RequestMapping("/role")
public class RoleController {
    private Logger logger= LoggerFactory.getLogger(RoleController.class);
    @Autowired
    RoleService roleService;
    @PostMapping("/selectAll")
    public R selectAllRole(){
        List<Role> role = roleService.findRole();
        logger.info(role.toString());
        return R.ok(role);
    }
}