随笔- 95 文章- 2 评论- 1 阅读- 23万

mybatis sql查子list

public class Masterxx implements Serializable {
    private String id;
 
    private String serialNumber;
 
    private String name;
 
    private String rootId;
 
    private String parentId;
 
    private Integer level;
 
    private Integer isDelete;
 
    private String remark;
 
    private List serviceCapabilitiesList;

　　xml

<resultMap id="BaseResultMap1" type="com.xinlianpu.master.entity.MasterServiceCapabilities">
    <id column="id" property="id" jdbcType="VARCHAR" />
    <result column="serial_number" property="serialNumber" jdbcType="VARCHAR" />
    <result column="name" property="name" jdbcType="VARCHAR" />
    <result column="root_id" property="rootId" jdbcType="VARCHAR" />
    <result column="parent_id" property="parentId" jdbcType="VARCHAR" />
    <result column="level" property="level" jdbcType="INTEGER" />
    <result column="is_delete" property="isDelete" jdbcType="INTEGER" />
    <result column="remark" property="remark" jdbcType="VARCHAR" />
    <collection property="serviceCapabilitiesList" ofType="com.xinlianpu.master.entity.MasterServiceCapabilities">
        <id column="t2_id" property="id" jdbcType="VARCHAR" />
        <result column="t2_serial_number" property="serialNumber" jdbcType="VARCHAR" />
        <result column="t2_name" property="name" jdbcType="VARCHAR" />
        <result column="t2_root_id" property="rootId" jdbcType="VARCHAR" />
        <result column="t2_parent_id" property="parentId" jdbcType="VARCHAR" />
        <result column="t2_level" property="level" jdbcType="INTEGER" />
        <result column="t2_is_delete" property="isDelete" jdbcType="INTEGER" />
        <result column="t2_remark" property="remark" jdbcType="VARCHAR" />
    collection>
resultMap>

<select id="getServiceList" resultMap="BaseResultMap1">
    SELECT
    t.id,
    t2.id t2_id,
    t.serial_number,
    t.name,
    t.root_id,
    t.parent_id,
    t.level,
    t.is_delete,
    t.remark,
 
    t2.id t2_id,
    t2.serial_number t2_serial_number,
    t2.name t2_name,
    t2.root_id t2_root_id,
    t2.parent_id t2_parent_id,
    t2.level level,
    t2.is_delete t2_is_delete,
    t2.remark t2_remark
    FROM
    master_service_capabilities t
    LEFT JOIN master_service_capabilities t2 ON t2.parent_id = t.id
    WHERE t.level='1'
select>

结果是：

[{
    "serialNumber": "A1",
    "level": 1,
    "rootId": "156291319422610000",
    "isDelete": 0,
    "name": "品牌战略",
    "remark": "",
    "id": "156291319527510001",
    "serviceCapabilitiesList": [{
        "serialNumber": "A11",
        "rootId": "156291319422610000",
        "isDelete": 0,
        "name": "品牌诊断",
        "remark": "通过对品牌的过去、现在、未来的洞察，了解品牌的市场竞争力及其健康程度。",
        "id": "156291319529310002",
        "parentId": "156291319527510001"
    },
    {
        "serialNumber": "A12",
        "rootId": "156291319422610000",
        "isDelete": 0,
        "name": "品牌架构梳理",
        "remark": "通过对企业所拥有的品牌进行梳理，建立清晰高效的品牌关系，实现对品牌资源的整合与有效利用。",
        "id": "156291319530810003",
        "parentId": "156291319527510001"
    },
    {
        "serialNumber": "A13",
        "rootId": "156291319422610000",
        "isDelete": 0,
        "name": "品牌命名与定义",
        "remark": "提炼品牌最高效、最准确、最打动人的核心价值，以精准的用词进行命名，并赋予其明确的品牌定位和价值观。",
        "id": "156291319532310004",
        "parentId": "156291319527510001"
    },
    {
        "serialNumber": "A14",
        "rootId": "156291319422610000",
        "isDelete": 0,
        "name": "品牌营销传播策略制定",
        "remark": "通过对传播主题进行规划，对品牌理念、个性、价值进行充分表达，在目标受众心目中建立企业所期望的品牌联想。",
        "id": "156291319533810005",
        "parentId": "156291319527510001"
    },
    {
        "serialNumber": "A15",
        "rootId": "156291319422610000",
        "isDelete": 0,
        "name": "品牌顾问",
        "remark": "以专属顾问的形式参与到客户的品牌发展过程中，为客户提供动态的、最符合当前需要的品牌顾问服务。",
        "id": "156291319535410006",
        "parentId": "156291319527510001"
    }],
    "parentId": "156291319422610000"
}]