springBoot学习笔记（2.1）—— 整合mybatis之一对一，一对多查询

一、准备工作

上篇文章已经介绍了如何引入jar和整合mybatis此处就不在多做赘述。

1. 创建实体类和表

1.1创建用户类

@Data
public class User {
    /**
     * 主键id
     */
    private  Long  id;
    /**
     * 用户名
     */
    private String userName;
    /**
     * 身份证号码
     */
    private String idCardNumber;
    /**
     * 手机号码
     */
    private String phoneNumber;
    /**
     * 部门id
     */
    private Long deptId;
    /**
     * 部门名称
     */
    private String deptName;
    /**
     * 部门类
     */
    private Dept dept;
}

1.2 创建用户表

CREATE TABLE `user`(
    id BIGINT (15) NOT NULL AUTO_INCREMENT COMMENT '主键id',
    user_name VARCHAR (50) NOT NULL DEFAULT '' COMMENT '用户名',
    idCardNumber VARCHAR (50) NOT NULL DEFAULT '' COMMENT '身份证号码',
    phone_number VARCHAR (50) NOT NULL DEFAULT '' COMMENT '手机号码',
    dept_id BIGINT (15) NOT NULL DEFAULT -1 COMMENT '部门id',
    PRIMARY KEY (id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT 'user';
alter table user modify id int auto_increment;

注意设置表格为自动增长，不然在后文中插入数据可能会报错。

1.3 创建部门类

@Data
public class Dept {
    /**
     * 主键id
     */
    private Long  id;
    /**
     * 部门名称
     */
    private String deptName;
    /**
     * 父类id
     */
    private String parentId;
    /**
     * 排序码
     */
    private String orderCode;
    /**
     * 是否开启使用(0否,1是)
     */
    private Integer isStart;
    /**
     * 说明
     */
    private String description;
    /**
     * 地址
     */
    private String address;
    /**
     * 人员数据
     */
    private List<User> userList;
    /**
     * 人员名字集合
     */
    private List<String> userNameList;
}

1.4 创建部门表

CREATE TABLE dept(
    id BIGINT (15) NOT NULL AUTO_INCREMENT COMMENT '主键id',
    dept_name VARCHAR (50) NOT NULL DEFAULT '' COMMENT '部门名称',
    parent_id VARCHAR (50) NOT NULL DEFAULT '' COMMENT '父类id',
    order_code VARCHAR (50) NOT NULL DEFAULT '' COMMENT '排序码',
    is_start INT (11) NOT NULL DEFAULT 0 COMMENT '是否开启使用(0否,1是)',
    description VARCHAR (50) NOT NULL DEFAULT '' COMMENT '说明',
    address VARCHAR (50) NOT NULL DEFAULT '' COMMENT '地址',
    PRIMARY KEY (id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT 'dept';

至于表格数据就不在此处说明，用户可以用自己的数据。

二、代码详情

1. mybatis一对一映射

1.1 Dao接口

@Mapper
public interface UserDao {
    /***
     * description: 查询用户具体信息,mybatis中一对一样例
     * version: 1.0 ->
     * date: 2021/12/29 10:32
     * author: xiaYZ
     * iteration: 迭代说明
     * @param userId 用户id
     * @return java.lang.String
     */
     User findUserData(Long userId);
}

1.2 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.springbootmybatis.dao.UserDao">
    <resultMap id="userMap" type="com.example.springbootmybatis.entity.User">
        <id property="id" column="id"/>
        <result property="deptId" column="dept_id"/>
        <result property="phoneNumber" column="phone_number"/>
        <result property="idCardNumber" column="idCardNumber"/>
        <result property="userName" column="user_name"/>
        <result property="deptName" column="dept_name"/>
        <!--mybatis一对一映射-->
        <association property="dept" javaType="com.example.springbootmybatis.entity.Dept">
            <id property="id" column="dept_id"/>
            <result property="deptName" column="dept_name"/>
            <result property="address" column="address"/>
            <result property="parentId" column="parent_id"/>
            <result property="description" column="description"/>
            <result property="isStart" column="is_start"/>
            <result property="orderCode" column="order_code"/>
        </association>
    </resultMap>
    <select id="findUserData" resultMap="userMap">
       select u.id, user_name, idCardNumber, phone_number, dept_id,
        dept_name, parent_id, order_code, is_start, description, address
       from user u
       left join dept d on u.dept_id = d.id
       where u.id = #{userId}
    </select>

1.3 Controller和Service层代码

@RestController
@RequestMapping("userController")
public class UserController {
    @Resource
    UserService userService;
    /***
     * description: 查询用户具体信息,mybatis中一对一样例
     * version: 1.0 ->
     * date: 2021/12/29 10:32
     * author: xiaYZ
     * iteration: 迭代说明
     * @param userId
     * @return java.lang.String
     */
    @GetMapping("findUserData")
    public String findUserData(Long userId){
        User user = new User();
        try{
            user = userService.findUserData(userId);
        }catch (Exception e){
            e.printStackTrace();
            return "查询用户详细数据";
        }
        return user.toString();
    }

@Service
public class UserService {
    @Resource
    UserDao userDao;
    public User findUserData(Long userId){
        return userDao.findUserData(userId);
    }
}

1.4 运行截图

springBoot学习笔记（2.1）—— 整合mybatis之一对一，一对多查询 - 图1

2. mybatis一对多映射

2.1 Dao接口

@Mapper
public interface DeptDao {
    /**
     * description: 通过部门id查询部门数据
     * version: 1.0
     * date: 2021/12/30 15:10
     * author: xiaYZ
     * iteration: 迭代说明
     * @param deptId 部门id
     * @return
     */
    Dept findDeptById(Long deptId);
}

2.1 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.springbootmybatis.dao.DeptDao">
    <resultMap id="deptMap" type="com.example.springbootmybatis.entity.Dept">
        <id column="id" property="id"/>
        <result column="dept_name" property="deptName"/>
        <result column="parent_id" property="parentId"/>
        <result column="order_code" property="orderCode"/>
        <result column="is_start" property="isStart"/>
        <result column="description" property="description"/>
        <result column="address" property="address"/>
        <!-- mybatis中一对多映射-->
        <collection property="userList" ofType="com.example.springbootmybatis.entity.User">
            <id column="userId" property="id"/>
            <result column="user_name" property="userName"/>
            <result column="idCardNumber" property="idCardNumber"/>
            <result column="phone_number" property="phoneNumber"/>
            <result column="dept_id" property="deptId"/>
        </collection>
      <!--一对多时List<String>映射方法--->
       <collection property="userNameList" ofType="java.lang.String">
            <constructor>
                 <arg column="user_name"/>
              </constructor>
      </collection>
    </resultMap>
    <select id="findDeptById" resultMap="deptMap">
       select d.id, dept_name, parent_id,order_code, is_start, description, address,
        u.id userId, user_name, idCardNumber, phone_number, dept_id
       from dept d
       left join user u on u.dept_id = d.id
       where d.id = #{deptId}
    </select>
</mapper>

2.3 Controller和Service类

@RestController
@RequestMapping("deptController")
public class DeptController {
    @Resource
    DeptService deptService;
    /***
     * description: 通过部门id查询部门数据
     * version: 1.0 ->
     * date: 2021/12/30 15:24
     * author: xiaYZ
     * iteration: 迭代说明
     * @param deptId 部门id
     * @return java.lang.String
     */
    @GetMapping("findDeptById")
    public String findDeptById(Long deptId){
        Dept dept = new Dept();
        try{
           dept = deptService.findDeptById(deptId);
           return dept.toString();
        }catch (Exception e){
            e.printStackTrace();
            return "查询部门数据错误";
        }
    }
}

@Service
public class DeptService {
    @Resource
    DeptDao deptDao;
    public Dept findDeptById(Long deptId){
       return deptDao.findDeptById(deptId);
    }
}

2.4运行截图

springBoot学习笔记（2.1）—— 整合mybatis之一对一，一对多查询 - 图2

3 mybatis新增修改删除操作

3.1 Dao接口类

   /**
      * description: 通过id删除用户操作
      * version: 1.0
      * date: 2021/12/29 15:54
      * author: xiaYZ
      * iteration: 迭代说明
      * @param userId
      * @return 
      */
    int deleteUserById(Long userId);
    /**
     * description: 新增用户操作
     * version: 1.0
     * date: 2021/12/29 15:56
     * author: xiaYZ
     * iteration: 迭代说明
     * @param user
     * @return 
     */
    int insertUser(User user);
    /**
     * description: 修改用户操作
     * version: 1.0
     * date: 2021/12/29 15:57
     * author: xiaYZ
     * iteration: 迭代说明
     * @param user
     * @return
     */
    int updateUser(User user);

3.2 Mapper文件类

 <delete id="deleteUserById">
        delete
        from user
        where id = #{userId}
    </delete>
    <insert id="insertUser" useGeneratedKeys="true" keyProperty="id">
        insert into user(id, user_name, idCardNumber, phone_number, dept_id)
        values (#{id}, #{userName}, #{idCardNumber}, #{phoneNumber}, #{deptId,jdbcType=BIGINT})
    </insert>
    <update id="updateUser">
        update user
        <set>
            <if test="userName != null and userName != ''">
                user_name = #{userName},
            </if>
            <if test="idCardNumber != null and idCardNumber != ''">
                idCardNumber = #{idCardNumber},
            </if>
            <if test="phoneNumber != null and phoneNumber != ''">
                phone_number = #{phoneNumber},
            </if>
            <if test="deptId != null">
                dept_id = #{deptId}
            </if>
        </set>
        where id = #{id}
    </update>

3.3 Service服务层类

  public int  deleteUserById(Long userId){
        return userDao.deleteUserById(userId);
    }
    public int insertUser(User user){
        return userDao.insertUser(user);
    }
    public int updateUser(User user){
        return userDao.updateUser(user);
    }

3.4 Controller控制层类

  public int  deleteUserById(Long userId){
        return userDao.deleteUserById(userId);
    }
    public int insertUser(User user){
        return userDao.insertUser(user);
    }
    public int updateUser(User user){
        return userDao.updateUser(user);
    }

项目截图就不一一在此展示了

总结

mybatis中一对一映射使用association标签。
2. mybatis中一对多映射使用collection标签。
注意：List 的映射方式
3. 数据库中新增操作时可以使用insert标签中useGenerateKeys=”true” keyProperty=”id”来获取新增数据的id

项目源码