欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

关于MySQL数据表的联合查询传到MyBatis对应实体类的理解

程序员文章站 2022-05-04 16:26:29
...

关于MySQL数据表的联合查询传到MyBatis对应实体类的理解

  • SQL查询某个父节点下的子节点
SELECT
	m1.*, m2.id AS id2,
	m2.url AS urL2,
	m2.path AS path2,
	m2.component AS component2,
	m2.`name` AS name2,
	m2.iconCls AS iconCls2,
	m2.keepAlive AS keepAlive2,
	m2.requireAuth AS requireAuth2,
	m2.parentId AS parentId2,
	m2.enabled AS enabled2
FROM
	menu AS m1,
	menu AS m2
WHERE
	m1.id = m2.parentId
AND m1.id = 2
  • 查询的结果
id url path component name iconCls keepAlive requireAuth parentId enabled id2 url2 path2 component2 name2 iconCls2 keepAlive2 requireAuth2 parentId2 enabled2
2 / /home Home 员工资料 fa fa-user-circle-o 1 1 1 7 /employee/basic/** /emp/basic EmpBasic 基本资料 1 2 1
2 / /home Home 员工资料 fa fa-user-circle-o 1 1 1 8 /employee/advanced/** /emp/adv EmpAdv 高级资料 1 2 0
  • 实体类
package com.fern.vhr.model;

import java.util.List;

public class Menu {
    private Integer id;

    private String url;

    private String path;

    private String component;

    private String name;

    private String iconcls;
    
    private Integer parentid;

    private Boolean enabled;
    
	private Meta meta;

    private List<Menu> children;
    
    //getter...
    //setter...
    
}
  • MyBastis的mapper.xml
<resultMap id="BaseResultMap" type="com.fern.vhr.model.Menu" >
    <id column="id" property="id" jdbcType="INTEGER" />
    <result column="url" property="url" jdbcType="VARCHAR" />
    <result column="path" property="path" jdbcType="VARCHAR" />
    <result column="component" property="component" jdbcType="VARCHAR" />
    <result column="name" property="name" jdbcType="VARCHAR" />
    <result column="iconCls" property="iconcls" jdbcType="VARCHAR" />
    <result column="parentId" property="parentid" jdbcType="INTEGER" />
    <result column="enabled" property="enabled" jdbcType="BIT" />
    <association property="meta" javaType="com.fern.vhr.model.Meta">
      <result column="keepAlive" property="keepalive" jdbcType="BIT" />
      <result column="requireAuth" property="requireauth" jdbcType="BIT" />
    </association>
    <!--实体类中集合  property属性名  javaType集合类型   ofType泛型-->
    <collection property="children" javaType="java.util.List" ofType="com.fern.vhr.model.Menu">
      <id column="id2" property="id" jdbcType="INTEGER" />
      <result column="url2" property="url" jdbcType="VARCHAR" />
      <result column="path2" property="path" jdbcType="VARCHAR" />
      <result column="component2" property="component" jdbcType="VARCHAR" />
      <result column="name2" property="name" jdbcType="VARCHAR" />
      <result column="iconCls2" property="iconcls" jdbcType="VARCHAR" />
      <result column="parentId2" property="parentid" jdbcType="INTEGER" />
      <result column="enabled2" property="enabled" jdbcType="BIT" />
      <association property="meta" javaType="com.fern.vhr.model.Meta">
        <result column="keepAlive2" property="keepalive" jdbcType="BIT" />
        <result column="requireAuth2" property="requireauth" jdbcType="BIT" />
      </association>
    </collection>
  </resultMap>

	<select id="getMenuByhrId" resultMap="BaseResultMap" parameterType="java.lang.Integer">
   SELECT m1.*,m2.id as id2,m2.url as urL2,m2.path as path2,m2.component as component2,m2.`name`as name2,m2.iconCls as iconCls2,m2.keepAlive as keepAlive2,m2.requireAuth as requireAuth2,m2.parentId as parentId2,m2.enabled as enabled2 from menu as m1,menu as m2 WHERE m1.id=m2.parentId
and m1.id=#{id}
  </select>

  • 结果
{
    "id": 2,
    "url": "/",
    "path": "/home",
    "component": "Home",
    "name": "员工资料",
    "iconcls": "fa fa-user-circle-o",
    "meta": {
        "keepalive": null,
        "requireauth": true
    },
    "children": [
        {
            "id": 7,
            "url": "/employee/basic/**",
            "path": "/emp/basic",
            "component": "EmpBasic",
            "name": "基本资料",
            "iconcls": null,
            "meta": {
                "keepalive": null,
                "requireauth": true
            },
            "children": null,
            "parentid": 2,
            "enabled": true
        },
        {
            "id": 8,
            "url": "/employee/advanced/**",
            "path": "/emp/adv",
            "component": "EmpAdv",
            "name": "高级资料",
            "iconcls": null,
            "meta": {
                "keepalive": null,
                "requireauth": true
            },
            "children": null,
            "parentid": 2,
            "enabled": false
        }
    ],
    "parentid": 1,
    "enabled": true
}
  • 按我写之前的想法,应该是联合查询出来两行员工资料,按理查询出来的json数据也应该有两个员工资料,但结果显然打了我的脸。
  • 因此我估计应该是mybatis将联合查询出来的多余重复数据自动去除了

关于MySQL数据表的联合查询传到MyBatis对应实体类的理解

相关标签: 总结