MyBatis多表查询——resultType——resultMap

使用多表查询，就得将两个实体类放在一块：

package com.quxiao.mybatis.pojo;

public class textAndUser {
    private Text text;
    private User user;

    @Override
    public String toString() {
        String str=user.getId()+" "+user.getName()+" "+user.getAge()+" "+user.getGender()+" "+user.getEmail()+" "+text.getTime();
        return str;
    }
}

这是我自己想的一个办法，然后来说说我们这次的两个多表查询的案例：

既然是多表查询，就得关联多个表，所以sql就和以前有一些不同了，而且要使用别名的方式将每个字段给定别名，别名为类名.属性

    <select id="selectTextAndUser" resultType="com.quxiao.mybatis.pojo.textAndUser">
        select u.id "user.id", u.name "user.name", u.gender "user.gender", 
        t.time "text.time"
        from user u
                 join text t on u.id = t.id
        where t.id = #{id};
    </select>

 返回值直接为多个实体类的关联类：selectTextAndUser

 然后另一种办法就是将所有的列一一对应：

    <select id="selectTextAndUser" resultMap="userAndtextMap">
        select u.id, u.name, u.gender, t.time
        from user u
                 join text t on u.id = t.id
        where t.id = #{id};
    </select>
    <resultMap id="userAndtextMap" type="com.quxiao.mybatis.pojo.textAndUser">
        <result property="user.id" column="id"></result>
        <result property="user.name" column="name"></result>
        <result property="user.age" column="age"></result>
        <result property="user.gender" column="gender"></result>
        <result property="user.email" column="email"></result>
        <result property="text.time" column="time"></result>
    </resultMap>

这里有几点要注意一下：

property：表示接下来你在sql中将要使用的字段的key

column： 则可以理解为value，属于数据库的字段名，必须相同！

一对多关系：

 

非常经典的绑定关系展示，现在都是先查询父级，然后通过父级id查询子级。

其实可以通过mybatis的一对多来直接查询所有的数据：

<?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.stock1Server.mapper.UserMapper">
    <resultMap id="getUserMap" type="com.stock1Server.pojo.vo.UserVO">
        <result property="id" column="id"/>
        <result property="userName" column="user_name"/>
        <result property="userCode" column="user_code"/>
        <collection property="workIds" javaType="ArrayList" ofType="com.stock1Server.entity.WorktableEntity">
            <result property="i1" column="i1"/>
            <result property="i2" column="i2"/>
            <result property="i3" column="i3"/>
            <result property="i4" column="i4"/>
        </collection>
    </resultMap>

    <select id="getUser" resultMap="getUserMap">
        select user.*,
               worktable.*
        from user
                 left join worktable on worktable.user_id = user.id
    </select>
</mapper>

返回就是这样：

[
  {
    "id": 14,
    "userName": "aaa",
    "userCode": "111",
    "workIds": [
      {
        "i1": 2,
        "i2": "多了一个表",
        "i3": "2024-02-21T14:48:27.000+00:00",
        "i4": 1,
        "data": "dsaaa",
        "userId": 14
      },
      {
        "i1": 1,
        "i2": "20212904",
        "i3": "2024-02-20T14:48:27.000+00:00",
        "i4": 1,
        "data": "dsaaa",
        "userId": 14
      }
    ]
  },
  {
    "id": 15,
    "userName": "11",
    "userCode": "er",
    "workIds": []
  },
  {
    "id": 16,
    "userName": "11",
    "userCode": "aaa",
    "workIds": []
  }
]