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会自动将各数据集合一一对应上。

巴特西

mybatis 多个中间表查询映射

最新文章

热门文章