mybatis 多个中间表查询映射

最近项目用到中间表，则遇到如何联查映射的问题，之前一直都是一个表头，多个明细或者一对一这样的关系，没遇到这样的问题，所以趁机找了下资料解决了这个问题。

表结构设计如下：

主表：

CREATE TABLE `process_line_detail` (
  `id` char(32) NOT NULL COMMENT 'ID',
  `serial_no` int(11) NOT NULL COMMENT '序号',
  `process_id` char(32) DEFAULT '' COMMENT '工序id'   
)

中间表：

REATE TABLE `process_line_detail_device` (
  `process_line_detail_id` char(32) NOT NULL COMMENT '工序线详情id',
  `device_id` char(32) NOT NULL COMMENT '设备id'
) 

CREATE TABLE `process_line_detail_mould` (
  `process_line_detail_id` char(32) NOT NULL COMMENT '工序线详情id',
  `mould_id` char(32) NOT NULL COMMENT '模具id'
)

中间表关联表：

CREATE TABLE `device` (
  `id` char(32) NOT NULL COMMENT 'ID',
  `name` varchar(32) NOT NULL COMMENT '名称'
)

CREATE TABLE `mould` (
  `id` char(32) NOT NULL COMMENT 'ID',
  `code` varchar(64) NOT NULL COMMENT '编码',
  `serial_no` varchar(32) DEFAULT '' COMMENT '序列号',
  `name` varchar(32) DEFAULT '' COMMENT '模具名称',
  `model` varchar(64) DEFAULT '' COMMENT '模具型号'
)

一对一的关联表：

CREATE TABLE `process` (
  `id` char(32) NOT NULL COMMENT 'ID',
  `name` varchar(64) DEFAULT '' COMMENT '工序名称'
)

实体类则不需要关联表，只需要在主表中添加关联表的集合或者单个对象即可（简写，省略 get，set）：

public class ProcessLineDetail{
    private String id;
    private String serialNo;        // 序号
    private Process process;        // 工序
    private List<Device> deviceList = Collections.emptyList(); 
    private List<Mould> mouldList = Collections.emptyList(); 

}

public class Device  {
    private String name;        // 名称
    private String id;        // id
}    

public class Mould {
    private String id;
    private String code;        // 编码
    private String serialNo;        // 序列号
    private String name;        // 模具名称
    private String model;        // 模具型号
}

public class Process  {
    private String id;        // id
    private String name;        // 工序名称

mybatis映射文件如下（简写，没包含具体实体的路径）：

<sql id="baseColumns">
        a.id,
        a.serial_no,
        a.process_id,
        process.name AS "process_name",
        device.name AS "device_name",
        device.id AS "device_id",
        mould.id AS "mould_id",
        mould.name AS "mould_name"
    </sql>

<resultMap id="AllResultMap" type="ProcessLineDetail">
        <id property="id" column="id"  />
        <result property="serialNo" column="serial_no" />
        <association property="process" column="process_id" javaType="Process">
            <id column="process_id" property="id" />
            <result column="process_name" property="name" />
        </association>
        <collection property="deviceList"  ofType="Device" >
            <id column="device_id" property="id"  />
            <result  column="device_name"  property="name"/>
        </collection>
        <collection property="mouldList" ofType="Mould">
            <id column="mould_id" property="id" />
            <result column="mould_name" property="name" />
        </collection>
    </resultMap>

<sql id="allJoins">
        left join process_line_detail_device detailDevice on detailDevice.process_line_detail_id = a.id
        left join device device on device.id = detailDevice.device_id
        left join process_line_detail_mould detailMould on detailMould.process_line_detail_id = a.id
        left join mould mould on mould.id = detailMould.mould_id
        left join process process ON process.id = a.process_id
    </sql>

<select id="get" resultMap="AllResultMap" >
        SELECT 
            <include refid="baseColumns"/>
        FROM process_line_detail a
        <include refid="allJoins"/>
        WHERE a.id = #{id}
    </select>

如此即可，mybaits会自动将各数据集合一一对应上。

转载于:https://www.cnblogs.com/suiyueqiannian/p/10833118.html