networkResource的 resultMap
<resultMap id="NetworkResultMap" type="com.chinamobile.epic.zebra.model.NetworkResource">
<id column="id" property="id" jdbcType="CHAR" />
<result column="name" property="name" jdbcType="VARCHAR" />
<result column="admin" property="admin" jdbcType="BIT" />
<result column="shared" property="shared" jdbcType="BIT" />
<result column="type" property="type" jdbcType="VARCHAR" />
<result column="organization_id" property="organizationId"
jdbcType="CHAR" />
<result column="external" property="external" jdbcType="BIT" />
<result column="segmentation_id" property="segmentationId"
jdbcType="VARCHAR" />
<result column="status" property="status" jdbcType="VARCHAR" />
<result column="physical_name" property="physicalName"
jdbcType="VARCHAR" />
<result column="user_id" property="userId" jdbcType="CHAR" />
<result column="pool_id" property="poolId" jdbcType="CHAR" />
<result column="order_id" property="orderId" jdbcType="CHAR" />
<result column="deleted" property="deleted" jdbcType="BIT" />
<result column="business_id" jdbcType="CHAR" property="businessId" />
<result column="created_at" property="createdAt" jdbcType="TIMESTAMP" />
<result column="updated_at" property="updatedAt" jdbcType="TIMESTAMP" />
<result column="region_name" property="regionName" jdbcType="VARCHAR" />
<result column="variety" property="variety" jdbcType="INTEGER" />
<result column="domain" property="domain" jdbcType="CHAR" />
<result column="d_name" property="domainName" jdbcType="CHAR" />
<result column="vpc_id" property="vpcId" jdbcType="CHAR" />
<result column="is_classic" property="isClassic" jdbcType="TINYINT" />
<result column="mtu" property="mtu" jdbcType="VARCHAR" />
<result column="vlan_transparent" property="vlanTransparent"
jdbcType="BIT" />
<result column="qos_policy_id" property="qosPolicyId" jdbcType="CHAR" />
<result column="ip_version" property="ipVersion" jdbcType="INTEGER" />
<result column="description" property="description" jdbcType="VARCHAR" />
<collection property="labels"
ofType="com.chinamobile.epic.zebra.model.Label">
<id column="l_id" property="id" jdbcType="CHAR" />
<result column="l_name" property="name" jdbcType="VARCHAR" />
</collection>
</resultMap>
子网的resultMap
<resultMap id="SubnetResultMap"
type="com.chinamobile.epic.zebra.model.SubnetResource">
<id column="s_id" property="id" jdbcType="CHAR" />
<result column="s_name" property="name" jdbcType="VARCHAR" />
<result column="s_enable_dhcp" property="enableDhcp" jdbcType="BIT" />
<result column="s_network_id" property="networkId" jdbcType="CHAR" />
<result column="s_organization_id" property="organizationId"
jdbcType="CHAR" />
<result column="s_dns_names" property="dnsNames" jdbcType="VARCHAR" />
<result column="s_allocation_pools" property="allocationPools"
jdbcType="VARCHAR" />
<result column="s_gateway" property="gateway" jdbcType="VARCHAR" />
<result column="s_ip_version" property="ipVersion" jdbcType="INTEGER" />
<result column="s_cidr" property="cidr" jdbcType="VARCHAR" />
<result column="s_user_id" property="userId" jdbcType="CHAR" />
<result column="s_pool_id" property="poolId" jdbcType="CHAR" />
<result column="s_order_id" property="orderId" jdbcType="CHAR" />
<result column="s_deleted" property="deleted" jdbcType="BIT" />
<result column="s_created_at" property="createdAt" jdbcType="TIMESTAMP" />
<result column="s_updated_at" property="updatedAt" jdbcType="TIMESTAMP" />
<result column="s_business_id" property="businessId" jdbcType="CHAR" />
<result column="s_region_name" property="regionName" jdbcType="VARCHAR" />
<result column="s_ip_count" property="ipCount" jdbcType="INTEGER" />
<result column="s_ipv6_address_mode" property="ipv6AddressMode"
jdbcType="VARCHAR" />
<result column="s_ipv6_ra_mode" property="ipv6RaMode" jdbcType="VARCHAR" />
</resultMap>
网络与子网的1：N关系的resultMap
<resultMap id="DetailResultMap" type="com.chinamobile.epic.zebra.model.NetworkResource" extends="NetworkResultMap">
<collection property="subnetResources" column="id" resultMap="SubnetResultMap" />
</resultMap>
sql查询
<sql id="network_Column_List">
n.id, n.name, n.admin, n.shared, n.type, n.organization_id,
n.external,
n.segmentation_id, n.deleted,
n.status, n.physical_name,
n.user_id, n.pool_id, n.order_id,
n.business_id,
n.created_at, n.updated_at, n.region_name, n.variety, n.domain,
n.vpc_id, n.is_classic,
n.mtu, n.vlan_transparent,
n.qos_policy_id,n.ip_version,n.description
</sql>
<sql id="subnet_Column_List">
s.id s_id, s.name s_name, s.enable_dhcp s_enable_dhcp, s.dns_names
s_dns_names, s.allocation_pools s_allocation_pools, s.gateway
s_gateway, s.ip_version s_ip_version, TRIM(s.cidr) s_cidr
</sql>
具体的sql
<select id="selectWith" resultMap="DetailResultMap"
parameterType="java.lang.String">
select
<include refid="network_Column_List" />
,
<include refid="subnet_Column_List" />
,
<include refid="Base_Column_Label" />
from networks n
left join subnets s on n.id = s.network_id
left join
crab.organizations o on o.id = n.organization_id
left join
label_relations lr ON lr.resource_id = n.id AND lr.deleted = false
left join labels l ON l.id = lr.label_id AND l.deleted = false
<where>
<if test="poolId != null ">
n.pool_id = #{poolId, jdbcType=CHAR}
</if>
<if test="orgDomainId != null ">
AND o.domain_id = #{orgDomainId, jdbcType=CHAR}
</if>
<if test="ipVersion != null">
AND s.ip_version = #{ipVersion, jdbcType=INTEGER}
</if>
<if test="type != null ">
AND n.type = #{type, jdbcType=VARCHAR}
</if>
<if test="domainId != null ">
AND n.domain = #{domainId, jdbcType=CHAR}
</if>
<if test="organizationId != null ">
AND (n.organization_id = #{organizationId, jdbcType=CHAR}
or n.organization_id is null)
</if>
<if test="external != null ">
AND n.external = #{external, jdbcType=BIT}
</if>
<if test="shared != null ">
AND n.shared = #{shared, jdbcType=BIT}
</if>
<if test="businessId != null">
AND (n.business_id=#{businessId,jdbcType=CHAR} or
n.business_id is null)
</if>
<if test="variety != null ">
AND n.variety = #{variety, jdbcType=INTEGER}
</if>
<if test="labelId != null">
AND l.id = #{labelId,jdbcType=CHAR}
</if>
AND n.deleted = false
</where>
</select>