MyBatis多表间级联查询

115 阅读 0 评论 76 点赞

我是靠谱客的博主认真香菇，最近开发中收集的这篇文章主要介绍MyBatis多表间级联查询，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

例一、

1.角色分类表

create table t_role_line
(
id bigint(11) not null auto_increment comment 'id',
role_line_type integer comment '角色线条类型（1一般，2行政）',
role_line varchar(20) comment '角色线条名称',
status integer not null default 1 comment '状态（1有效，0删除）',
created_at datetime not null default CURRENT_TIMESTAMP comment '创建时间', deleted_at datetime comment '删除时间' );

2. 角色表

create table t_role
(
id bigint(11) not null auto_increment comment 'id',
role_code varchar(20) comment '角色编码',
role_name varchar(200) comment '角色名称',
role_line_id bigint comment '角色线条ID',
role_line_type integer comment '角色线条类型(1一般，2行政）',
qualifications varchar(1000) comment '角色需要的资质编码（JSON数组串 ）',
status integer not null default 1 comment '状态（1有效，0删除）',
created_at datetime not null default CURRENT_TIMESTAMP comment '创建时间',
deleted_at datetime comment '删除时间',
last_update_at datetime default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP comment '上次操作时间',
last_update_by varchar(50) comment '操作人',
last_update_byname varchar(50) comment '操作人姓名'
);

3. MyBatis 的DAO 或Mapper文件

3.1. RoleLineDAO.xml

<mapper namespace="com.xxx.xxx.dao.RoleLineDAO">

<resultMap id="ofRolesResultMap" type="com.xxx.xxx.entity.RoleLine">
<id column="id" jdbcType="BIGINT" property="id" />
<result column="role_line_type" jdbcType="INTEGER" property="roleLineType" />
<result column="role_line" jdbcType="VARCHAR" property="roleLine" />
<result column="status" jdbcType="INTEGER" property="status" />
<result column="created_at" jdbcType="TIMESTAMP" property="createdAt" />
<result column="deleted_at" jdbcType="TIMESTAMP" property="deletedAt" />
<collection property="roles" javaType="java.util.ArrayList" column="id"
ofType="com.xxx.xxx.entity.Role" select="com.xxx.xxx.dao.RoleDAO.selectByRoleLineId"/>
</resultMap>

<sql id="Base_Column_List">
id, role_line_type, role_line, status, created_at, deleted_at
</sql>

<select id="selectLineAndRolesByRoleType" resultMap="ofRolesResultMap">
select 
<include refid="Base_Column_List" />
from t_role_line t 
where t.status=1 and t.role_line_type = #{rolelinetype,jdbcType=INTEGER}
</select>

</mapper>

3.2.RoleDAO.xml

<mapper namespace="com.xxx.xxx.dao.RoleDAO">
<resultMap id="BaseResultMap" type="com.xxx.xxx.entity.Role">
<id column="id" jdbcType="BIGINT" property="id" />
<result column="role_code" jdbcType="VARCHAR" property="roleCode" />
<result column="role_name" jdbcType="VARCHAR" property="roleName" />
<result column="role_line_id" jdbcType="BIGINT" property="roleLineId" />
<result column="role_line_type" jdbcType="INTEGER" property="roleLineType" />
<result column="qualifications" jdbcType="VARCHAR" property="qualifications" />
<result column="status" jdbcType="INTEGER" property="status" />
<result column="created_at" jdbcType="TIMESTAMP" property="createdAt" />
<result column="deleted_at" jdbcType="TIMESTAMP" property="deletedAt" />
<result column="last_update_at" jdbcType="TIMESTAMP" property="lastUpdateAt" />
<result column="last_update_by" jdbcType="VARCHAR" property="lastUpdateBy" />
<result column="last_update_byname" jdbcType="VARCHAR" property="lastUpdateByname" />
</resultMap>
<sql id="Base_Column_List">
id, role_code, role_name, role_line_id, role_line_type, qualifications, status, created_at, 
deleted_at, last_update_at, last_update_by, last_update_byname
</sql>

<select id="selectByRoleLineId" resultMap="BaseResultMap">
select 
<include refid="Base_Column_List" />
from t_role t 
where t.status=1 and t.role_line_id = #{roleLineId,jdbcType=BIGINT}
</select>

</mapper>

4.实体类

4.1 Role

public class Role {
private Long id;

private String roleCode;

private String roleName;

private Long roleLineId;

private Integer roleLineType;

private String qualifications;

private Integer status;

private Date createdAt;

private Date deletedAt;

private Date lastUpdateAt;

private String lastUpdateBy;

private String lastUpdateByname;

public Long getId() {
return id;
}

public void setId(Long id) {
this.id = id;
}

public String getRoleCode() {
return roleCode;
}

public void setRoleCode(String roleCode) {
this.roleCode = roleCode == null ? null : roleCode.trim();
}

public String getRoleName() {
return roleName;
}

public void setRoleName(String roleName) {
this.roleName = roleName == null ? null : roleName.trim();
}
public Long getRoleLineId() {
return roleLineId;
}

public void setRoleLineId(Long roleLineId) {
this.roleLineId = roleLineId;
}

public Integer getRoleLineType() {
return roleLineType;
}

public void setRoleLineType(Integer roleLineType) {
this.roleLineType = roleLineType;
}

public String getQualifications() {
return qualifications;
}

public void setQualifications(String qualifications) {
this.qualifications = qualifications == null ? null : qualifications.trim();
}

public Integer getStatus() {
return status;
}

public void setStatus(Integer status) {
this.status = status;
}

public Date getCreatedAt() {
return createdAt;
}

public void setCreatedAt(Date createdAt) {
this.createdAt = createdAt;
}

public Date getDeletedAt() {
return deletedAt;
}

public void setDeletedAt(Date deletedAt) {
this.deletedAt = deletedAt;
}

public Date getLastUpdateAt() {
return lastUpdateAt;
}

public void setLastUpdateAt(Date lastUpdateAt) {
this.lastUpdateAt = lastUpdateAt;
}

public String getLastUpdateBy() {
return lastUpdateBy;
}

public void setLastUpdateBy(String lastUpdateBy) {
this.lastUpdateBy = lastUpdateBy == null ? null : lastUpdateBy.trim();
}

public String getLastUpdateByname() {
return lastUpdateByname;
}

public void setLastUpdateByname(String lastUpdateByname) {
this.lastUpdateByname = lastUpdateByname == null ? null : lastUpdateByname.trim();
}
}

4.2.RoleLine

public class RoleLine {
private Long id;

private Integer roleLineType;

private String roleLine;

private Integer status;

private Date createdAt;

private Date deletedAt;

private List<Role> roles;

public Long getId() {
return id;
}

public void setId(Long id) {
this.id = id;
}

public Integer getRoleLineType() {
return roleLineType;
}

public void setRoleLineType(Integer roleLineType) {
this.roleLineType = roleLineType == null ? null : roleLineType.intValue();
}

public String getRoleLine() {
return roleLine;
}

public void setRoleLine(String roleLine) {
this.roleLine = roleLine == null ? null : roleLine.trim();
}

public Integer getStatus() {
return status;
}

public void setStatus(Integer status) {
this.status = status;
}

public Date getCreatedAt() {
return createdAt;
}

public void setCreatedAt(Date createdAt) {
this.createdAt = createdAt;
}

public Date getDeletedAt() {
return deletedAt;
}

public void setDeletedAt(Date deletedAt) {
this.deletedAt = deletedAt;
}

public List<Role> getRoles() {
return roles;
}

public void setRoles(List<Role> roles) {
this.roles = roles;
}
}

5.DAO类

5.1.RoleDAO 类

@Repository
public interface RoleDAO {

List<Role> selectByRoleLineId(@Param("roleLineId")Long roleLineId);

}

5.2.RoleLineDAO类

@Repository
public interface RoleLineDAO {

List<RoleLine> selectLineAndRolesByRoleType(@Param("rolelinetype")Integer rolelinetype);

}

例二、根据多字段级联

1.建表

create table t_network_clazz
(
   id                   bigint(11) not null auto_increment comment 'id',
   service_line         bigint(11) comment '业务线',
   org_type_code        national varchar(20) not null comment '组织类型编码',
   org_type_name        varchar(200) comment '组织类型名称',
   parent_id            bigint(11) not null comment '上级数据ID(顶级ID=0)',
   parent_org_type_code national varchar(20) comment '上级组织类型编码',
   parent_org_type_name varchar(200) comment '上级组织类型名称',
   ref_service_line     national bigint(11) comment '参照业务线',
   follow_type          integer not null comment '订阅状态(1 follow , 0 unfollow)',
   sync_all             integer not null comment '全量同步(1是，0否）',
   level_code           integer comment '层级',
   status               integer not null default 1 comment '状态（1有效，0删除）',
   created_at           datetime not null default CURRENT_TIMESTAMP comment '创建时间',
   deleted_at           datetime comment '删除时间'
);

create table t_service_line
(
   code                 bigint(11) not null auto_increment comment '编码',
   name                 national varchar(200) not null comment '名称',
   abbreviation         varchar(100) comment '简称',
   describes            national varchar(200) comment '描述',
   ref_type             integer not null comment '参考类型(1 新建，2 参照已有)',
   ref_service_line     varchar(20) comment '参考业务线',
   follow_flag          integer comment '是否有follow(1是，0否)',
   created_at           datetime not null comment '创建时间',
   created_by           national varchar(20) comment '创建人',
   status               integer not null default 1 comment '状态（1有效，0删除）',
   deleted_at           datetime comment '删除时间',
   deleted_by           national varchar(50) comment '删除人',
   last_update_at       datetime comment '上次操作时间',
   last_update_by       varchar(20) comment '操作人',
   last_update_byname   varchar(200) comment '操作人姓名'
);

create table t_service_line_org_type
(
   id                   bigint(11) not null auto_increment comment 'id',
   service_line         bigint(11) not null comment '业务线',
   org_type_code        national varchar(20) not null comment '服务类型编码',
   org_type_name        varchar(200) comment '服务类型名称',
   has_asset            integer not null comment '是否关联资产(1是，0否）',
   asset_types          varchar(2000) comment '关联资产类型
            format:
            [{
              "code":"xxxx"
            }]',
   status               integer not null default 1 comment '状态(1有效，0删除）',
   created_at           datetime not null default CURRENT_TIMESTAMP comment '创建时间',
   deleted_at           datetime comment '删除时间',
   last_update_at       datetime default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP comment '上次操作时间',
   last_update_by       varchar(20) comment '操作人',
   last_update_byname   varchar(200) comment '操作人姓名'
);

2. MyBatis的DAO或Mapper.xml

2.1.ServiceLineOrgTypeDAO.xml

<?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.xxx.xxx.dao.ServiceLineOrgTypeDAO">
<resultMap id="associateResultMap" type="com.xxx.xxx.entity.ServiceLineOrgType">
    <id column="id" jdbcType="BIGINT" property="id" />
    <result column="service_line" jdbcType="BIGINT" property="serviceLine" />
    <result column="org_type_code" jdbcType="VARCHAR" property="orgTypeCode" />
    <result column="org_type_name" jdbcType="VARCHAR" property="orgTypeName" />
    <result column="has_asset" jdbcType="INTEGER" property="hasAsset" />
    <result column="asset_types" jdbcType="VARCHAR" property="assetTypes" />
    <result column="status" jdbcType="INTEGER" property="status" />
    <result column="created_at" jdbcType="TIMESTAMP" property="createdAt" />
    <result column="deleted_at" jdbcType="TIMESTAMP" property="deletedAt" />
    <result column="last_update_at" jdbcType="TIMESTAMP" property="lastUpdateAt" />
    <result column="last_update_by" jdbcType="VARCHAR" property="lastUpdateBy" />
    <result column="last_update_byname" jdbcType="VARCHAR" property="lastUpdateByname" />
    <association property="serviceLineObj" column="service_line" javaType="com.xxx.xxx.entity.ServiceLine"
            select="com.xxx.xxx.dao.ServiceLineDAO.selectByPrimaryKey"/>
    <collection property="networkClazzs" javaType="java.util.ArrayList" column="{serviceLine=service_line,orgTypeCode=org_type_code}"
            ofType="com.xxx.xxx.entity.NetworkClazz" select="com.xxx.xxx.dao.NetworkClazzDAO.selectByServiceLineAndOrgTypeCode"/>
  </resultMap>
  <sql id="Base_Column_List">
    id, service_line, org_type_code, org_type_name, has_asset, asset_types, 
    status, created_at, deleted_at, last_update_at, last_update_by, last_update_byname
  </sql>
<select id="findListByPageCount" resultType="java.lang.Long">
      select 
     count(t.id) c 
     from t_service_line_org_type t 
     where 1=1 and t.status=1  
     <if test="serviceLineCodes != null and serviceLineCodes.size > 0 ">
      and t.service_line in  
      <foreach collection="serviceLineCodes" item="item" index="index" open="(" separator="," close=")">
       #{item}
      </foreach>
     </if>
     <if test="orgTypeCodes != null and orgTypeCodes.size > 0 ">
      and t.org_type_code in 
       <foreach collection="orgTypeCodes" item="item" index="index" open="(" separator="," close=")">
        #{item}
       </foreach>
     </if>  
  </select>
  <select id="findListByPage" resultMap="associateResultMap">
      select 
    <include refid="Base_Column_List" />
     from t_service_line_org_type t 
     where 1=1 and t.status=1  
     <if test="serviceLineCodes != null and serviceLineCodes.size > 0 ">
      and t.service_line in  
      <foreach collection="serviceLineCodes" item="item" index="index" open="(" separator="," close=")">
       #{item}
      </foreach>
     </if>
     <if test="orgTypeCodes != null and orgTypeCodes.size > 0 ">
      and t.org_type_code in 
       <foreach collection="orgTypeCodes" item="item" index="index" open="(" separator="," close=")">
        #{item}
       </foreach>
     </if>
     order by last_update_at desc limit #{offset},#{pagesize} 
  </select>
  <select id="selectById" resultMap="associateResultMap">
      select 
    <include refid="Base_Column_List" />
     from t_service_line_org_type t 
     where t.status=1 and t.id = #{id,jdbcType=BIGINT} 
  </select>

</mapper>

2.2.ServiceLineDAO.xml

<?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.xxx.xxx.dao.ServiceLineDAO">
  <resultMap id="BaseResultMap" type="com.xxx.xxx.entity.ServiceLine">
    <id column="code" jdbcType="BIGINT" property="code" />
    <result column="name" jdbcType="VARCHAR" property="name" />
    <result column="abbreviation" jdbcType="VARCHAR" property="abbreviation" />
    <result column="describes" jdbcType="VARCHAR" property="describes" />
    <result column="ref_type" jdbcType="INTEGER" property="refType" />
    <result column="ref_service_line" jdbcType="VARCHAR" property="refServiceLine" />
    <result column="follow_flag" jdbcType="INTEGER" property="followFlag" />
    <result column="created_at" jdbcType="TIMESTAMP" property="createdAt" />
    <result column="created_by" jdbcType="VARCHAR" property="createdBy" />
    <result column="status" jdbcType="INTEGER" property="status" />
    <result column="deleted_at" jdbcType="TIMESTAMP" property="deletedAt" />
    <result column="deleted_by" jdbcType="VARCHAR" property="deletedBy" />
    <result column="last_update_at" jdbcType="TIMESTAMP" property="lastUpdateAt" />
    <result column="last_update_by" jdbcType="VARCHAR" property="lastUpdateBy" />
    <result column="last_update_byname" jdbcType="VARCHAR" property="lastUpdateByname" />
    
    <result column="org_type_codes" jdbcType="VARCHAR" property="orgTypeCodes" />
    <result column="org_type_names" jdbcType="VARCHAR" property="orgTypeNames" />
  </resultMap>
  <sql id="Base_Column_List">
    code, name, abbreviation, describes, ref_type, ref_service_line, follow_flag, created_at, created_by, 
    status, deleted_at, deleted_by, last_update_at, last_update_by, last_update_byname
  </sql>
  <select id="selectByPrimaryKey" parameterType="java.lang.String" resultMap="BaseResultMap">
    select 
    <include refid="Base_Column_List" />
    from t_service_line
    where code = #{code,jdbcType=BIGINT}
  </select>
</mapper>

2.3.NetworkClazzDAO.xml

<?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.xxx.xxx.dao.NetworkClazzDAO">
  <resultMap id="BaseResultMap" type="com.xxx.xxx.entity.NetworkClazz">
    <id column="id" jdbcType="BIGINT" property="id" />
    <result column="service_line" jdbcType="BIGINT" property="serviceLine" />
    <result column="org_type_code" jdbcType="VARCHAR" property="orgTypeCode" />
    <result column="org_type_name" jdbcType="VARCHAR" property="orgTypeName" />
    <result column="parent_id" jdbcType="BIGINT" property="parentId" />
    <result column="parent_org_type_code" jdbcType="VARCHAR" property="parentOrgTypeCode" />
    <result column="parent_org_type_name" jdbcType="VARCHAR" property="parentOrgTypeName" />
    <result column="level_code" jdbcType="INTEGER" property="levelCode" />
    <result column="ref_service_line" jdbcType="VARCHAR" property="refServiceLine" />
    <result column="follow_type" jdbcType="INTEGER" property="followType" />
    <result column="sync_all" jdbcType="INTEGER" property="syncAll" />    
    <result column="status" jdbcType="INTEGER" property="status" />
    <result column="created_at" jdbcType="TIMESTAMP" property="createdAt" />
    <result column="deleted_at" jdbcType="TIMESTAMP" property="deletedAt" />
  </resultMap>
  <sql id="Base_Column_List">
    id, service_line, org_type_code, org_type_name, parent_id, parent_org_type_code, 
    parent_org_type_name, level_code, ref_service_line, follow_type, sync_all, status, created_at, deleted_at
  </sql>
<select id="selectByServiceLineAndOrgTypeCode" resultMap="BaseResultMap">
    select 
    <include refid="Base_Column_List" />
    from t_network_clazz t 
    where t.status=1 and t.service_line=#{serviceLine} 
    and t.org_type_code=#{orgTypeCode} 
  </select>
</mapper>

3.实体类

3.1.ServiceLineOrgType

  1 package com.xxx.xxx.entity;
  2 
  3 import java.io.Serializable;
  4 import java.util.Date;
  5 import java.util.List;
  6 
  7 public class ServiceLineOrgType implements Serializable{
  8     /**
  9      * 
 10      */
 11     private static final long serialVersionUID = 1L;
 12 
 13     private Long id;
 14 
 15     private Long serviceLine;
 16 
 17     private String orgType;
 18 
 19     private String orgTypeCode;
 20 
 21     private String orgTypeName;
 22 
 23     private Integer hasAsset;
 24 
 25     private String assetTypes;
 26 
 27     private Integer status;
 28 
 29     private Date createdAt;
 30 
 31     private Date deletedAt;
 32 
 33     private Date lastUpdateAt;
 34 
 35     private String lastUpdateBy;
 36 
 37     private String lastUpdateByname;
 38     
 39     private ServiceLine serviceLineObj;
 40     
 41     private List<NetworkClazz> networkClazzs;
 42 
 43     public Long getId() {
 44         return id;
 45     }
 46 
 47     public void setId(Long id) {
 48         this.id = id;
 49     }
 50 
 51     public Long getServiceLine() {
 52         return serviceLine;
 53     }
 54 
 55     public void setServiceLine(Long serviceLine) {
 56         this.serviceLine = serviceLine;
 57     }
 58 
 59     public String getOrgType() {
 60         return orgType;
 61     }
 62 
 63     public void setOrgType(String orgType) {
 64         this.orgType = orgType == null ? null : orgType.trim();
 65     }
 66 
 67     public String getOrgTypeCode() {
 68         return orgTypeCode;
 69     }
 70 
 71     public void setOrgTypeCode(String orgTypeCode) {
 72         this.orgTypeCode = orgTypeCode == null ? null : orgTypeCode.trim();
 73     }
 74 
 75     public String getOrgTypeName() {
 76         return orgTypeName;
 77     }
 78 
 79     public void setOrgTypeName(String orgTypeName) {
 80         this.orgTypeName = orgTypeName == null ? null : orgTypeName.trim();
 81     }
 82 
 83     public Integer getHasAsset() {
 84         return hasAsset;
 85     }
 86 
 87     public void setHasAsset(Integer hasAsset) {
 88         this.hasAsset = hasAsset;
 89     }
 90 
 91     public String getAssetTypes() {
 92         return assetTypes;
 93     }
 94 
 95     public void setAssetTypes(String assetTypes) {
 96         this.assetTypes = assetTypes == null ? null : assetTypes.trim();
 97     }
 98 
 99     public Integer getStatus() {
100         return status;
101     }
102 
103     public void setStatus(Integer status) {
104         this.status = status;
105     }
106 
107     public Date getCreatedAt() {
108         return createdAt;
109     }
110 
111     public void setCreatedAt(Date createdAt) {
112         this.createdAt = createdAt;
113     }
114 
115     public Date getDeletedAt() {
116         return deletedAt;
117     }
118 
119     public void setDeletedAt(Date deletedAt) {
120         this.deletedAt = deletedAt;
121     }
122 
123     public Date getLastUpdateAt() {
124         return lastUpdateAt;
125     }
126 
127     public void setLastUpdateAt(Date lastUpdateAt) {
128         this.lastUpdateAt = lastUpdateAt;
129     }
130 
131     public String getLastUpdateBy() {
132         return lastUpdateBy;
133     }
134 
135     public void setLastUpdateBy(String lastUpdateBy) {
136         this.lastUpdateBy = lastUpdateBy == null ? null : lastUpdateBy.trim();
137     }
138 
139     public String getLastUpdateByname() {
140         return lastUpdateByname;
141     }
142 
143     public void setLastUpdateByname(String lastUpdateByname) {
144         this.lastUpdateByname = lastUpdateByname == null ? null : lastUpdateByname.trim();
145     }
146 
147     public ServiceLine getServiceLineObj() {
148         return serviceLineObj;
149     }
150 
151     public void setServiceLineObj(ServiceLine serviceLineObj) {
152         this.serviceLineObj = serviceLineObj;
153     }
154 
155     public List<NetworkClazz> getNetworkClazzs() {
156         return networkClazzs;
157     }
158 
159     public void setNetworkClazzs(List<NetworkClazz> networkClazzs) {
160         this.networkClazzs = networkClazzs;
161     }
162 }

View Code

3.2.ServiceLine

package com.xxx.xxx.entity;

import java.util.Date;

public class ServiceLine {
    private Long code;

    private String name;
    
    private String abbreviation;

    private String describes;

    private Integer refType;

    private String refServiceLine;

    private Integer followFlag;

    private Date createdAt;

    private String createdBy;

    private Integer status;

    private Date deletedAt;

    private String deletedBy;

    private Date lastUpdateAt;

    private String lastUpdateBy;

    private String lastUpdateByname;
    
    private String orgTypeCodes;
    
    private String orgTypeNames; 

    public Long getCode() {
        return code;
    }

    public void setCode(Long code) {
        this.code = code;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name == null ? null : name.trim();
    }

    public String getAbbreviation() {
        return abbreviation;
    }

    public void setAbbreviation(String abbreviation) {
        this.abbreviation = abbreviation == null ? null : abbreviation.trim();
    }

    public String getDescribes() {
        return describes;
    }

    public void setDescribes(String describes) {
        this.describes = describes == null ? null : describes.trim();
    }

    public Integer getRefType() {
        return refType;
    }

    public void setRefType(Integer refType) {
        this.refType = refType;
    }

    public String getRefServiceLine() {
        return refServiceLine;
    }

    public void setRefServiceLine(String refServiceLine) {
        this.refServiceLine = refServiceLine == null ? null : refServiceLine.trim();
    }

    public Integer getFollowFlag() {
        return followFlag;
    }

    public void setFollowFlag(Integer followFlag) {
        this.followFlag = followFlag;
    }

    public Date getCreatedAt() {
        return createdAt;
    }

    public void setCreatedAt(Date createdAt) {
        this.createdAt = createdAt;
    }

    public String getCreatedBy() {
        return createdBy;
    }

    public void setCreatedBy(String createdBy) {
        this.createdBy = createdBy == null ? null : createdBy.trim();
    }

    public Integer getStatus() {
        return status;
    }

    public void setStatus(Integer status) {
        this.status = status;
    }

    public Date getDeletedAt() {
        return deletedAt;
    }

    public void setDeletedAt(Date deletedAt) {
        this.deletedAt = deletedAt;
    }

    public String getDeletedBy() {
        return deletedBy;
    }

    public void setDeletedBy(String deletedBy) {
        this.deletedBy = deletedBy == null ? null : deletedBy.trim();
    }

    public Date getLastUpdateAt() {
        return lastUpdateAt;
    }

    public void setLastUpdateAt(Date lastUpdateAt) {
        this.lastUpdateAt = lastUpdateAt;
    }

    public String getLastUpdateBy() {
        return lastUpdateBy;
    }

    public void setLastUpdateBy(String lastUpdateBy) {
        this.lastUpdateBy = lastUpdateBy == null ? null : lastUpdateBy.trim();
    }

    public String getLastUpdateByname() {
        return lastUpdateByname;
    }

    public void setLastUpdateByname(String lastUpdateByname) {
        this.lastUpdateByname = lastUpdateByname == null ? null : lastUpdateByname.trim();
    }

    public String getOrgTypeCodes() {
        return orgTypeCodes;
    }

    public void setOrgTypeCodes(String orgTypeCodes) {
        this.orgTypeCodes = orgTypeCodes == null ? null : orgTypeCodes.trim();
    }

    public String getOrgTypeNames() {
        return orgTypeNames;
    }

    public void setOrgTypeNames(String orgTypeNames) {
        this.orgTypeNames = orgTypeNames == null ? null : orgTypeNames.trim();
    }
    
}

View Code

3.3.NetworkClazz

package com.xxx.xxx.entity;

import java.util.Date;

public class NetworkClazz {
    private Long id;

    private Long serviceLine;

    private String orgTypeCode;

    private String orgTypeName;

    private Long parentId;

    private String parentOrgTypeCode;

    private String parentOrgTypeName;

    private Integer levelCode;
    
    private String refServiceLine;
    
    private Integer followType;

    private Integer syncAll;

    private Integer status;

    private Date createdAt;

    private Date deletedAt;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public Long getServiceLine() {
        return serviceLine;
    }

    public void setServiceLine(Long serviceLine) {
        this.serviceLine = serviceLine;
    }

    public String getOrgTypeCode() {
        return orgTypeCode;
    }

    public void setOrgTypeCode(String orgTypeCode) {
        this.orgTypeCode = orgTypeCode == null ? null : orgTypeCode.trim();
    }

    public String getOrgTypeName() {
        return orgTypeName;
    }

    public void setOrgTypeName(String orgTypeName) {
        this.orgTypeName = orgTypeName == null ? null : orgTypeName.trim();
    }

    public Long getParentId() {
        return parentId;
    }

    public void setParentId(Long parentId) {
        this.parentId = parentId;
    }

    public String getParentOrgTypeCode() {
        return parentOrgTypeCode;
    }

    public void setParentOrgTypeCode(String parentOrgTypeCode) {
        this.parentOrgTypeCode = parentOrgTypeCode == null ? null : parentOrgTypeCode.trim();
    }

    public String getParentOrgTypeName() {
        return parentOrgTypeName;
    }

    public void setParentOrgTypeName(String parentOrgTypeName) {
        this.parentOrgTypeName = parentOrgTypeName == null ? null : parentOrgTypeName.trim();
    }

    public Integer getLevelCode() {
        return levelCode;
    }

    public void setLevelCode(Integer levelCode) {
        this.levelCode = levelCode;
    }
    
    public String getRefServiceLine() {
        return refServiceLine;
    }

    public void setRefServiceLine(String refServiceLine) {
        this.refServiceLine = refServiceLine == null ? null : refServiceLine.trim();
    }
    
    public Integer getFollowType() {
        return followType;
    }

    public void setFollowType(Integer followType) {
        this.followType = followType;
    }

    public Integer getSyncAll() {
        return syncAll;
    }

    public void setSyncAll(Integer syncAll) {
        this.syncAll = syncAll;
    }
    
    public Integer getStatus() {
        return status;
    }

    public void setStatus(Integer status) {
        this.status = status;
    }

    public Date getCreatedAt() {
        return createdAt;
    }

    public void setCreatedAt(Date createdAt) {
        this.createdAt = createdAt;
    }

    public Date getDeletedAt() {
        return deletedAt;
    }

    public void setDeletedAt(Date deletedAt) {
        this.deletedAt = deletedAt;
    }
}

View Code

4.DAO类

4.1.ServiceLineOrgTypeDAO

 1 package com.xxx.xxx.dao;
 2 
 3 import java.util.List;
 4 
 5 import org.apache.ibatis.annotations.Param;
 6 import org.springframework.stereotype.Repository;
 7 
 8 import com.xxx.xxx.entity.ServiceLineOrgType;
 9 
10 @Repository
11 public interface ServiceLineOrgTypeDAO {
12     /*List<ServiceLineOrgType> selectListByPage(@Param("orgTypeCodes") String orgTypeCode,@Param("serviceLineCodes")String serviceLineCodes, RowBounds rowBounds);*/
13     Long findListByPageCount(@Param("orgTypeCodes") List<String> orgTypeCode,@Param("serviceLineCodes")List<String> serviceLineCodes);
14     List<ServiceLineOrgType> findListByPage(@Param("orgTypeCodes") List<String> orgTypeCode,@Param("serviceLineCodes")List<String> serviceLineCodes,@Param("offset")Long offset,@Param("pagesize")Integer pagesize);
15     ServiceLineOrgType selectById(Long id);
16     
17 }

View Code

4.2.ServiceLineDAO

 1 package com.xxx.xxx.dao;
 2 
 3 import com.xxx.xxx.entity.ServiceLine;
 4 
 5 import org.springframework.stereotype.Repository;
 6 
 7 @Repository
 8 public interface ServiceLineDAO {
 9 
10     ServiceLine selectByPrimaryKey(Long code);
11 }

View Code

4.3.NetworkClazzDAO

 1 package com.xxx.xxx.dao;
 2 
 3 import com.xxx.xxx.entity.NetworkClazz;
 4 
 5 import java.util.List;
 6 
 7 import org.apache.ibatis.annotations.Param;
 8 import org.springframework.stereotype.Repository;
 9 
10 @Repository
11 public interface NetworkClazzDAO {
12     List<NetworkClazz> selectByServiceLineAndOrgTypeCode(@Param("serviceLine")Long serviceLine,@Param("orgTypeCode")String orgTypeCode);
13    
14 }

View Code

5.BIZ层或Service层代码样例

5.1.ServiceLineOrgtypeBiz

  1 package com.xxx.xxx.biz;
  2 
  3 import java.util.ArrayList;
  4 import java.util.Arrays;
  5 import java.util.HashMap;
  6 import java.util.HashSet;
  7 import java.util.List;
  8 import java.util.Map;
  9 import java.util.Set;
 10 
 11 import org.apache.commons.lang3.StringUtils;
 12 import org.slf4j.Logger;
 13 import org.slf4j.LoggerFactory;
 14 import org.springframework.beans.factory.annotation.Autowired;
 15 import org.springframework.stereotype.Service;
 16 
 17 import com.xxx.xxx.dao.NetworkClazzDAO;
 18 import com.xxx.xxx.dao.ServiceLineOrgTypeDAO;
 19 import com.xxx.xxx.dao.UsergroupUserDAO;
 20 import com.xxx.xxx.dto.PaginationDTO;
 21 import com.xxx.xxx.dto.ServiceLineOrgTypeDTO;
 22 import com.xxx.xxx.dto.UserDTO;
 23 import com.xxx.xxx.entity.NetworkClazz;
 24 import com.xxx.xxx.entity.ServiceLineOrgType;
 25 import com.xxx.xxx.entity.Usergroup;
 26 import com.xxx.xxx.entity.UsergroupAuthority;
 27 import com.xxx.xxx.entity.UsergroupUser;
 28 import com.xxx.xxx.transfer.ServiceLineOrgTypeTransfer;
 29 import com.xxx.xxx.vo.OrgTypeVO;
 30 
 31 /**
 32  * 服务类型管理service业务逻辑处理层
 33  * @author v-mengjf
 34  * @since 2019-05-16
 35  * @version 1.0
 36  */
 37 @Service
 38 public class ServiceLineOrgtypeBiz {
 39     private static Logger logger = LoggerFactory.getLogger(ServiceLineOrgtypeBiz.class);
 40     @Autowired
 41     private NetworkClazzDAO networkClazzDAO;
 42     @Autowired
 43     private ServiceLineOrgTypeDAO serviceLineOrgTypeDAO;
 44     @Autowired
 45     private UsergroupUserDAO usergroupUserDAO;
 46     
 47     
 48     public Map<String,Object> listByPage(Integer pageno,Integer pagesize,String orgTypeCode,String serviceLine) {
 49         /*logger.debug("invoke method listByPage(),orgTypeCode="+orgTypeCode+",serviceLine="+serviceLine);*/
 50         if (pageno <= 0) {
 51             pageno = 1;
 52         }
 53         if (pagesize <= 0) {
 54             pagesize = 50;
 55         }
 56         
 57         String[] orgTypeCodeArr = StringUtils.isNotBlank(orgTypeCode)?(StringUtils.contains(orgTypeCode, ",")?StringUtils.split(orgTypeCode, ","):new String[] {orgTypeCode}):null;
 58         String[] serviceLineArr = StringUtils.isNotBlank(serviceLine)?(StringUtils.contains(serviceLine, ",")?StringUtils.split(serviceLine, ","):new String[] {serviceLine}):null;
 59         List<String> orgTypeCodes = null;
 60         List<String> serviceLines = null;
 61         
 62         if(orgTypeCodeArr!=null) {
 63             orgTypeCodes = Arrays.asList(orgTypeCodeArr);
 64         }
 65         if(serviceLineArr!=null) {
 66             serviceLines = Arrays.asList(serviceLineArr);
 67         }
 68         Long offset = (long) ((pageno-1)*pagesize);
 69         /*RowBounds rowBounds = new RowBounds((pageno-1)*pagesize, pagesize);*/
 70         
 71         List<ServiceLineOrgType> serviceLineOrgTypeList = null;
 72         Long totalRow = 0L;
 73         try {
 74             /*serviceLineOrgTypeList = serviceLineOrgTypeDAO.selectListByPage(orgTypeCode, serviceLine, rowBounds);*/
 75             totalRow = serviceLineOrgTypeDAO.findListByPageCount(orgTypeCodes, serviceLines);
 76             serviceLineOrgTypeList = serviceLineOrgTypeDAO.findListByPage(orgTypeCodes, serviceLines, offset,pagesize);
 77         } catch (Exception e) {
 78             logger.error("分页查询服务范围类型出现异常", e);
 79             e.printStackTrace();
 80             throw e;
 81         }
 82         /*Integer totalRow = CountHelper.getTotalRow();*/
 83         Integer pages = totalRow.intValue()/pagesize;
 84         if(pages*pagesize<totalRow) {
 85             pages++;
 86         }
 87         
 88         List<ServiceLineOrgTypeDTO> dataList = new ArrayList<ServiceLineOrgTypeDTO>();
 89         if(serviceLineOrgTypeList!=null && serviceLineOrgTypeList.size()>0) {
 90             for(ServiceLineOrgType orgType : serviceLineOrgTypeList) {
 91                 List<NetworkClazz> ofNetworkClazzs = orgType.getNetworkClazzs();
 92                 List<NetworkClazz> ofParentNetworkClazzs = null;
 93                 if(ofNetworkClazzs!=null && ofNetworkClazzs.size()>0) {
 94                     ofParentNetworkClazzs = networkClazzDAO.selectByServiceLineAndNetworkClazzs(orgType.getServiceLine(), ofNetworkClazzs);
 95                 }
 96                 List<NetworkClazz> ofChildrenNetworkClazzs = networkClazzDAO.selectByServiceLineAndParentOrgTypeCode(orgType.getServiceLine(), orgType.getOrgTypeCode());
 97                 
 98                 dataList.add(ServiceLineOrgTypeTransfer.transfer(orgType, ofParentNetworkClazzs, ofChildrenNetworkClazzs));
 99             }
100         }
101         PaginationDTO paginationDTO = new PaginationDTO();
102         paginationDTO.setPage(pageno);
103         paginationDTO.setLimit(pagesize);
104         paginationDTO.setTotalPages(pages);
105         paginationDTO.setTotalRecords(totalRow.intValue());
106         Map<String,Object> result = new HashMap<String,Object>();
107         result.put("pagination", paginationDTO);
108         result.put("orgTypes", dataList);
109         
110         return result;
111     }
112     
113 }

View Code

6.Junit Test

6.1.ServiceLineOrgtypeBIZTest

 1 package com.xxx.xxx.biz;
 2 
 3 import java.util.ArrayList;
 4 import java.util.List;
 5 import java.util.Map;
 6 
 7 import org.junit.Test;
 8 import org.springframework.beans.factory.annotation.Autowired;
 9 
10 
11 import org.slf4j.Logger;
12 import org.slf4j.LoggerFactory;
13 import org.springframework.test.context.ContextConfiguration;
14 import org.springframework.test.context.junit4.AbstractJUnit4SpringContextTests;
15 
16 @ContextConfiguration(locations = { "classpath:test_config.xml", "classpath:test_cache.xml" })
17 public class ServiceLineOrgtypeBIZTest extends AbstractJUnit4SpringContextTests {
18 protected final Logger logger = LoggerFactory.getLogger(getClass());
19 
20     @Autowired
21     private ServiceLineOrgtypeBiz serviceLineOrgtypeBiz;
22     @Test
23     public void testListByPage() {
24         Map<String,Object> result = serviceLineOrgtypeBiz.listByPage(1, 50, null, "200,300");
25         logger.info(result.toString());
26     }
27 
28 }