子查询 的子查询
结果
{
"code": 200,
"message": "成功",
"data": {
"records": [
{
"id": 1,
"uid": "123",
"name": "123",
"description": "123",
"slaveUnitKeys": [
{
"id": 1,
"uid": "12312312",
"slaveUnitTypeId": 1,
"parentUnitId": 0,
"name": "12312",
"code": "111",
"defaultFunc": false,
"description": "12312",
"subSlaveUnitKeys": []
},
{
"id": 2,
"uid": "111",
"slaveUnitTypeId": 1,
"parentUnitId": 0,
"name": "222",
"code": "222",
"defaultFunc": false,
"subSlaveUnitKeys": [
{
"id": 3,
"uid": "333",
"slaveUnitTypeId": 1,
"parentUnitId": 2,
"name": "333",
"code": "333",
"defaultFunc": false
}
]
}
]
}
],
"total": 1,
"size": 100,
"current": 1,
"searchCount": true,
"pages": 1
}
}
<?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.xxxx.modules.software.devices.setting.repository.dao.SoftwareDevSlaveUnitTypeMapper">
<!-- 通用查询映射结果 -->
<resultMap id="BaseResultMap" type="com.xxxx.modules.software.devices.setting.repository.bean.DevSlaveUnitType">
<id column="id" property="id"/>
<result column="uid" property="uid"/>
<result column="name" property="name"/>
<result column="description" property="description"/>
<!-- <result column="create_by" property="createBy"/>-->
<!-- <result column="create_time" property="createTime"/>-->
<!-- <result column="last_update_by" property="lastUpdateBy"/>-->
<!-- <result column="last_update_time" property="lastUpdateTime"/>-->
</resultMap>
<!-- DevSlaveType -> TSoftwareDevSlaveTypeDTO -->
<resultMap id="DevSlaveTypeDTOResultMap"
type="com.xxxx.modules.software.device.setting.dto.TSoftwareDevSlaveUnitTypeDTO">
<id column="id" property="id" jdbcType="BIGINT"/>
<result column="uid" property="uid"/>
<result column="name" property="name"/>
<result column="description" property="description"/>
<!-- <result column="create_by" property="createBy"/>-->
<!-- <result column="create_time" property="createTime"/>-->
<!-- <result column="last_update_by" property="lastUpdateBy"/>-->
<!-- <result column="last_update_time" property="lastUpdateTime"/>-->
</resultMap>
<resultMap id="DevSlaveTypeDTOExResultMap" type="com.xxxx.modules.software.device.setting.dto.TSoftwareDevSlaveUnitTypeDTO">
<id column="id" property="id" jdbcType="BIGINT"/>
<result column="uid" property="uid"/>
<result column="name" property="name"/>
<result column="description" property="description"/>
<!--分机列表-->
<collection property="slaveUnitKeys" column="id" select="findRelatedDevSlaveUnitKey" ofType="com.xxxx.modules.software.device.setting.dto.TSoftwareDevSlaveUnitKeyDTO">
<id column="id" property="id"/>
<result column="uid" property="uid"/>
<result column="slave_unit_type_id" property="slaveUnitTypeId"/>
<result column="parent_unit_id" property="parentUnitId"/>
<result column="name" property="name"/>
<result column="code" property="code"/>
<result column="default_func" property="defaultFunc"/>
<result column="func" property="func"/>
<result column="description" property="description"/>
<!--子分机列表-->
<collection property="subSlaveUnitKeys" column="id" select="fetchByParentUnitId" ofType="com.xxx.modules.software.device.setting.dto.TSoftwareDevSlaveUnitKeyDTO">
<id column="id" property="id"/>
<result column="uid" property="uid"/>
<result column="slave_unit_type_id" property="slaveUnitTypeId"/>
<result column="parent_unit_id" property="parentUnitId"/>
<result column="name" property="name"/>
<result column="code" property="code"/>
<result column="default_func" property="defaultFunc"/>
<result column="func" property="func"/>
<result column="description" property="description"/>
</collection>
</collection>
</resultMap>
<resultMap id="DevSlaveTypeDTOEx1ResultMap" type="com.xxxx.modules.software.device.setting.dto.TSoftwareDevSlaveUnitKeyDTO">
<!--分机列表-->
<id column="id" property="id"/>
<result column="uid" property="uid"/>
<result column="slave_unit_type_id" property="slaveUnitTypeId"/>
<result column="parent_unit_id" property="parentUnitId"/>
<result column="name" property="name"/>
<result column="code" property="code"/>
<result column="default_func" property="defaultFunc"/>
<result column="func" property="func"/>
<result column="description" property="description"/>
<!--子分机列表-->
<collection property="subSlaveUnitKeys" column="id" select="fetchByParentUnitId" ofType="com.xxxx.modules.software.device.setting.dto.TSoftwareDevSlaveUnitKeyDTO">
<id column="id" property="id"/>
<result column="uid" property="uid"/>
<result column="slave_unit_type_id" property="slaveUnitTypeId"/>
<result column="parent_unit_id" property="parentUnitId"/>
<result column="name" property="name"/>
<result column="code" property="code"/>
<result column="default_func" property="defaultFunc"/>
<result column="func" property="func"/>
<result column="description" property="description"/>
</collection>
</resultMap>
<resultMap id="DevSlaveUnitKeyDTOResultMap" type="com.xxxx.modules.software.device.setting.dto.TSoftwareDevSlaveUnitKeyDTO">
<id column="id" property="id" jdbcType="BIGINT"/>
<result column="uid" property="uid"/>
<result column="slave_unit_type_id" property="slaveUnitTypeId"/>
<result column="parent_unit_id" property="parentUnitId"/>
<result column="name" property="name"/>
<result column="code" property="code"/>
<result column="default_func" property="defaultFunc"/>
<result column="func" property="func"/>
<result column="image_enable" property="imageEnable"/>
<result column="image_path" property="imagePath"/>
<result column="description" property="description"/>
</resultMap>
<select id="findRelatedDevSlaveUnitKey" resultMap="DevSlaveTypeDTOEx1ResultMap">
SELECT
<include refid="Dev_Slave_Unit_Key_List"></include>
from dev_slave_unit_key
WHERE slave_unit_type_id = #{id} and parent_unit_id = 0
ORDER BY id
</select>
<select id="fetchByParentUnitId" resultMap="DevSlaveUnitKeyDTOResultMap">
SELECT
<include refid="Dev_Slave_Unit_Key_List"></include>
from dev_slave_unit_key
where parent_unit_id = #{id}
ORDER BY id
</select>
<sql id="Dev_Slave_Unit_Key_List">
id, uid, slave_unit_type_id, parent_unit_id, name, code, default_func, func, description, create_by, create_time, last_update_by, last_update_time
</sql>
<!-- 通用查询结果列 -->
<sql id="Base_Column_List">
id, uid, name, description, create_by, create_time, last_update_by, last_update_time
</sql>
<sql id="where_clause">
<where>
1=1
<if test="query.keyWords != null and query.keyWords != ''">
And (a.name like '%' #{query.keyWords, jdbcType=VARCHAR} '%'
)
</if>
<if test="query.id != null">
AND (a.id = #{query.id})
</if>
<if test="query.uid != null and query.uid != ''">
And (a.uid like '%' #{query.uid, jdbcType=VARCHAR} '%')
</if>
<if test="query.name != null and query.name != ''">
And (a.name like '%' #{query.name, jdbcType=VARCHAR} '%')
</if>
</where>
</sql>
<!-- 使用自增ID -->
<!-- <insert id="insertDevSlaveType" useGeneratedKeys="true" keyProperty="id"-->
<!-- parameterType="com.xxxx.modules.software.devices.setting.repository.bean.DevSlaveUnitType">-->
<!-- INSERT INTO dev_slave_unit_type-->
<!-- (id,uid, name, create_by,create_time, last_update_by,last_update_time)-->
<!-- VALUES-->
<!-- (#{id},#{uid},#{name},#{createBy},#{createTime},#{lastUpdateBy},#{lastUpdateTime});-->
<!-- </insert>-->
<!--删除 -->
<delete id="deleteDevSlaveTypeById">
DELETE FROM dev_slave_unit_type WHERE id = #{id}
</delete>
<select id="selectDevSlaveTypeDTO"
parameterType="com.xxxxx.modules.software.device.setting.query.TSoftwareDevSlaveUnitTypeQueryDTO"
resultMap="DevSlaveTypeDTOResultMap">
SELECT
<include refid="Base_Column_List"></include>
from dev_slave_unit_type a
<include refid="where_clause"></include>
ORDER BY a.id desc
</select>
<select id="selectDevSlaveTypeDTOEx"
parameterType="com.xxxx.modules.software.device.setting.query.TSoftwareDevSlaveUnitTypeQueryDTO"
resultMap="DevSlaveTypeDTOExResultMap">
SELECT
<include refid="Base_Column_List"></include>
from dev_slave_unit_type a
<include refid="where_clause"></include>
ORDER BY a.id desc
</select>
<sql id="insert_column_list">
(name,uid, create_by, last_update_by)
</sql>
<sql id="foreach_sql">
<foreach collection="list" index="index" item="item" separator=",">
(#{item.name}, {item.uid},#{item.createBy}, #{item.lastUpdateBy})
</foreach>
</sql>
<!-- 批量插入分机单元类型,忽略重复数据,将返回插入id -->
<insert id="insertBatchSlaveType" useGeneratedKeys="true" keyProperty="id">
INSERT ignore INTO dev_slave_unit_type
<include refid="insert_column_list"></include>
VALUES
<include refid="foreach_sql"></include>
</insert>
<!-- 批量插入分机单元类型,更新重复数据,将不会返回插入/更新id -->
<insert id="insertOrUpdateBatchSlaveType">
INSERT INTO dev_slave_unit_type
<include refid="insert_column_list"></include>
VALUES
<include refid="foreach_sql"></include>
ON DUPLICATE KEY UPDATE
name = values(name),uid = values(uid), create_by = values(create_by), last_update_by = values(last_update_by)
</insert>
</mapper>
TSoftwareDevSlaveUnitTypeDTO 实体
import com.fasterxml.jackson.annotation.JsonInclude;
import lombok.Getter;
import lombok.Setter;
import java.io.Serializable;
import java.time.LocalDateTime;
import java.util.List;
/**
* <p>
* 分机类型
* </p>
*
* @author yws
* @since 2022.8.3
*/
@Getter
@Setter
public class TSoftwareDevSlaveUnitTypeDTO implements Serializable {
private static final long serialVersionUID = 1L;
private Long id;
/**
* uid,用于导入导出
*/
private String uid;
/**
* 功能名称
*/
private String name;
/**
* 描述
*/
private String description;
/**
* 关联分机
*/
private List<TSoftwareDevSlaveUnitKeyDTO> slaveUnitKeys;
}
package com.taiden.modules.software.device.setting.dto;
import com.fasterxml.jackson.annotation.JsonInclude;
import lombok.Getter;
import lombok.Setter;
import java.io.Serializable;
import java.util.List;
/**
* <p>
* 分机类型
* </p>
*
* @author yws
* @since 2022.8.3
*/
@Getter
@Setter
public class TSoftwareDevSlaveUnitKeyDTO implements Serializable {
private static final long serialVersionUID = 1L;
private Long id;
/**
* uid,用于导入导出
*/
private String uid;
/**
* 分机型号ID
*/
private Long slaveUnitTypeId;
/**
* 分机型号ID
*/
private Long parentUnitId;
/**
* 分机型号名称
*/
private String name;
/**
* 分机指令
*/
private String code;
/**
* 默认功能
*/
private Boolean defaultFunc;
/**
* 分机可选功能
*/
private String func;
/**
* 是否显示图片
*/
private Boolean imageEnable;
/**
* 图片路径
*/
private String imagePath;
/**
* 说明
*/
private String description;
/**
* 派生分机
*/
private List<TSoftwareDevSlaveUnitKeyDTO> subSlaveUnitKeys;
}
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.experimental.Accessors;
import java.io.Serializable;
import java.time.LocalDateTime;
/**
* <p>
* 分机类型
* </p>
*
* @author yws
* @since 2022-09-15
*/
@Data
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
public class DevSlaveUnitType implements Serializable {
private static final long serialVersionUID = 1L;
/**
* ID
*/
@TableId(value = "id", type = IdType.AUTO)
private Long id;
/**
* uid,用于导入导出
*/
private String uid;
/**
* 功能名称
*/
private String name;
/**
* 说明
*/
private String description;
/**
* 创建人
*/
private String createBy;
/**
* 创建时间
*/
private LocalDateTime createTime;
/**
* 更新人
*/
private String lastUpdateBy;
/**
* 更新时间
*/
private LocalDateTime lastUpdateTime;
/**
* 通过CSV文件生成对应实例
*/
public DevSlaveUnitType(String[] args) {
// id、创建时间和更新时间为空
this(null, args[1], args[2], args[3], null, args[5], null, null);
}
public DevSlaveUnitType() {
}
public DevSlaveUnitType(Long id, String uid, String name, String createBy, LocalDateTime createTime, String lastUpdateBy, LocalDateTime lastUpdateTime, String description) {
this.id = id;
this.uid = uid;
this.name = name;
this.description = description;
this.createBy = createBy;
this.createTime = createTime;
this.lastUpdateBy = lastUpdateBy;
this.lastUpdateTime = lastUpdateTime;
}
}