Mybatis嵌套查询——一对多

mybatis的嵌套查询为两个表之间的关联提供了支持，通过嵌套查询可以轻松达到一种查询A的同时把B也关联查询出来，数据库中也不用创建外键。话不多说，直接上教程。

首先需要确定两个表的关系，我这里的例子是用户表，订单表。用户对订单是一对多的关系。

表字段

图1 订单表字段

图2 用户表字段

实体类

建立完表后，我们接下来编写实体类

public class User implements Serializable {
    private Integer user_id;
    private String nickname;
    private String phonenumber ;

    public Integer getUser_id() {
        return user_id;
    }

    public void setUser_id(Integer user_id) {
        this.user_id = user_id;
    }

  public Getter and Setter(){
	..................
	}
}

用户实体类(User)

public class Order implements Serializable {
  private Integer orderId;

  private Integer userId;

  private Integer pId;

  private String createtime;

  private String price;

  private String remarks;

  private User user;

  public Getter and Setter(){
	..................
	}
}

订单实体类(Order)，这里需要注意的是在属性中加入了user，后面会通过该对象获取到查询的user信息。

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.biyesheji.schoolka.mapper.UserMapper">
  <resultMap id="BaseResultMap" type="com.biyesheji.schoolka.bean.User">
    <result column="user_id" jdbcType="INTEGER" property="user_id" />
    <result column="nickname" jdbcType="VARCHAR" property="nickname" />
    <result column="phonenumber" jdbcType="VARCHAR" property="phonenumber" />
  </resultMap>
  <select id="selectAll" resultMap="BaseResultMap">
    select user_id, nickname, phonenumber
    from user
  </select>
  
  <select id="selectById" resultMap="BaseResultMap" parameterType="java.lang.Integer">
    SELECT * FROM user
    WHERE user_id= #{id}
  </select>
</mapper>

UserMappeer.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.biyesheji.schoolka.mapper.OrderMapper">
    <resultMap id="BaseResultMap" type="com.biyesheji.schoolka.bean.Order">
        <result column="order_id" jdbcType="INTEGER" property="orderId"/>
        <result column="user_id" jdbcType="INTEGER" property="userId"/>
        <result column="p_id" jdbcType="INTEGER" property="pId"/>
        <result column="createtime" jdbcType="VARCHAR" property="createtime"/>
        <result column="price" jdbcType="VARCHAR" property="price"/>
        <result column="remarks" jdbcType="VARCHAR" property="remarks"/>
        <!--
        property:在对应实体类中的属性名称，这里是user
        column:在表中要关联的字段名，这里是user_id
        select:填入要调用的sql语句id(可以填入其她命名空间中的id，例如com.biyesheji.schoolka.mapper.UserMapper.selectById)
        -->
        <association property="user" column="user_id" select="getUser"
                     javaType="com.biyesheji.schoolka.bean.User">
        </association>
    </resultMap>
    <select id="selectAll" resultMap="BaseResultMap">
    select order_id, user_id, p_id, createtime, price, remarks
    from `order`
  </select>
    <select id="getUser" parameterType="java.lang.Integer"
            resultMap="com.biyesheji.schoolka.mapper.UserMapper.BaseResultMap">
    SELECT * FROM user WHERE user_id=#{user_id}
  </select>
    <select id="selectByUser" parameterType="java.lang.Integer" resultMap="BaseResultMap">
    SELECT * FROM `order` WHERE user_id=#{id}
  </select>
</mapper>

OrderMapper.xml中要关联查询出来的对象user，单独使用标签，在标签的select属性有两种填法，当时我第一次用的是按照上文的的填法，但是一直报错，所以我用了第二种，按照“命名空间+id”的方式填入，测试成功运行。如下图

<association property="user" column="user_id" select="com.biyesheji.schoolka.mapper.UserMapper.selectById"
                     javaType="com.biyesheji.schoolka.bean.User">
        </association>

测试

如有问题可在下方留言，感谢阅读。