mybatis中association和collection的column传入多个参数问题

最新推荐文章于 2025-05-07 15:18:42 发布

飞奔的大土豆

最新推荐文章于 2025-05-07 15:18:42 发布

阅读量1.5k

点赞数

分类专栏： mybits 文章标签： mybatis collection column 查询条件参数传下级 tree table

mybits 专栏收录该内容

10 篇文章

订阅专栏

本文介绍在MyBatis中如何使用association和collection标签来处理一对一和一对多的关系，并通过column属性传递多个参数值到嵌套查询语句中。特别展示了如何构造虚拟列并将其作为查询条件。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

mybatis中association和collection的column传入多个参数值

项目中在使用association和collection实现一对一和一对多关系时需要对关系中结果集进行筛选，如果使用懒加载模式，即联合使用select标签时，主sql和关系映射里的sql是分开的，查询参数传递成为问题。

mybatis文档:

property	description
column	数据库的列名或者列标签别名。与传递给resultSet.getString(columnName)的参数名称相同。注意：在处理组合键时，您可以使用column=“{prop1=col1,prop2=col2}”这样的语法，设置多个列名传入到嵌套查询语句。这就会把prop1和prop2设置到目标嵌套选择语句的参数对象中。

<resultMap id="findCountryCityAddressMap" type="map">
    <result property="country" column="country"/>
    <collection property="cityList"
                column="{cityId=city_id,adr=addressCol, dis=districtCol}" //adr作为第二个sql查询条件key,即prop1属性
                ofType="map"                                              //addressCol即为虚拟列名
                javaType="java.util.List" select="selectAddressByCityId"/>
</resultMap>

<resultMap id="selectAddressByCityIdMap" type="map">
    <result property="city" column="city"/>
    <collection property="addressList" column="city" ofType="map" javaType="java.util.List">
        <result property="address" column="address"/>
        <result property="district" column="district"/>
    </collection>
</resultMap>

<select id="findCountryCityAddress" resultMap="findCountryCityAddressMap">
    SELECT
        ct.country,
        ci.city_id,
        IFNULL(#{addressQuery},'') addressCol, //为传入查询条件,构造虚拟列，虚拟列为查询条件参数值
        IFNULL(#{districtQuery},'') districtCol
    FROM
        country ct
    LEFT JOIN city ci ON ct.country_id = ci.country_id
    ORDER BY ct.country_id
</select>

<select id="selectAddressByCityId" parameterType="java.util.Map" resultMap="selectAddressByCityIdMap">
    SELECT
        ci.city,
        ads.address,
      ads.district
    FROM
        (
            SELECT
                city,
                city_id
            FROM
                city ci
            WHERE
                ci.city_id = #{cityId}
        ) ci
    LEFT JOIN address ads ON ads.city_id = ci.city_id
    <where>
        <if test="adr!=null and adr!=''">
            and ads.address RegExp #{adr}
        </if>
        <if test="dis!=null and dis!=''">
            ads.district Regexp #{dis}
        </if>
    </where>

</select>

测试文件:

@Test
public void findCountryCityAddressTest() throws JsonProcessingException {
    Map<String,Object> param = new HashMap<>();
    param.put("addressQuery","1168");
    List<Map<String, Object>> rs = countryManager.findCountryCityAddress(param);
    ObjectMapper mapper = new ObjectMapper();
    ObjectWriter writer = mapper.writerWithDefaultPrettyPrinter();
    System.out.println(writer.writeValueAsString(rs));
}

测试结果:

[
    {
        "country": "Afghanistan",
        "cityList": [{
                "city": "Kabul",
                "addressList": [{
                        "address": "1168 Najafabad Parkway",
                        "district": "Kabol"
                    }
                ]
            }
        ],
        "city_id": 251
    },
    {
        "country": "Algeria",
        "cityList": [],
        "city_id": 59
    }
]

可以看到，确实将查询条件通过column参数传入到第二个sql中，并执行成功

ps: ------tree table

vue:

<el-table-column label="机构名称" prop="orgName" min-width="150">
<template slot-scope="scope">
						<p :style="`margin-left: ${scope.row.__level * 20}px;margin-top:0;margin-bottom:0`">
							<i @click="toggleFoldingStatus(scope.row)" class="permission_toggleFold" :class="toggleFoldingClass(scope.row)"></i>
							<span class="c-click" @click="handleViewSyorg(scope.row)">{{ scope.row.orgName }}</span>
						</p>
</template>
</el-table-column>

sql:

(#{syuserId}) as syuserId就可以别sql转化未属性

	      <!--tree table 返回结果集Map -->
    <resultMap id="treeTable_ResultMap" type="com.alex.hainan.oas.base.entity.OrgTreeTableBO">
        <result column="ORG_ID" jdbcType="VARCHAR" property="orgId" />
        <result column="pid" jdbcType="VARCHAR" property="pid" />
        <result column="syuserId" jdbcType="VARCHAR" property="syuserId" />
    <collection property="children" ofType="com.alex.hainan.oas.base.entity.OrgTreeTableBO" javaType="java.util.List"
                column="{pid=pid,syuserId=syuserId}" select="getChildSyorgList">
    </collection>
    </resultMap>
     <!--alex企业tree table init显示接口 查询列表 -->
  <select id="query" resultMap="treeTable_ResultMap"  parameterType="java.util.Map">
    select
		o.org_id pid, o.org_id, o.org_name,
		o.parent_org_id,
        ### (#{syuserId}) as syuserId
        IFNULL(#{syuserId},'')  as syuserId
	from alex.syorg o where o.del_flg=0
      and o.parent_org_id is null or o.parent_org_id =''
	 <if test="syuserId !=null and syuserId !=''">
			AND o.org_id  IN (select u.org_id  from alex.syuser_syorg u where
			u.syuser_id = #{syuserId,jdbcType=VARCHAR} and u.del_flg=0)
	</if>
  </select>
    <select id="getChildSyorgList" resultMap="treeTable_ResultMap"  parameterType="java.util.Map">
    SELECT
      o.org_id pid, o.org_id,  o.org_name,
      IFNULL(#{syuserId},'')  as syuserId
    FROM
        alex.syorg o
    LEFT JOIN  alex.review registRv ON (o.review_seq = registRv.review_seq AND o.del_flg =0 AND registRv.apply_type = '1')
     LEFT  JOIN alex.review platRv ON (o.review_seq = platRv.review_seq AND o.del_flg = 0 AND platRv.apply_type = '2')
    LEFT JOIN ( select  e.org_id,  f.batch_code as batch_code
                from  alex.org_batch_data e 
				INNER JOIN alex.batch_data f ON  (e.batch_seq = f.batch_seq AND f.del_flg = 0   AND f.batch_type = '1')
                where e.del_flg = 0
              )as ef ON o.org_id = ef.org_id
    WHERE
        o.del_flg = 0
      <if test="pid !=null and pid !=''">
        and o.parent_org_id =#{pid,jdbcType=VARCHAR}
      </if>
      <if test="syuserId !=null and syuserId !=''">
        AND o.org_id  IN (select u.org_id  from alex.syuser_syorg u where
        u.syuser_id = #{syuserId,jdbcType=VARCHAR} and u.del_flg=0)
      </if>
  </select>

js：

method:{
    formatConversion(parent, children, index = 0, family = [], elderIdentity = 'x') {
			// children如果长度等于0，则代表已经到了最低层
			// let page = (this.startPage - 1) * 10
			if (children.length > 0) {
				children.map((x, i) => {
					// 设置 __level 标志位 用于展示区分层级
					Vue.set(x, '__level', index);
					// 设置 __family 为家族关系 为所有父级，包含本身在内
					Vue.set(x, '__family', [...family, elderIdentity + '_' + i]);
					// 本身的唯一标识  可以理解为个人的身份证咯 一定唯一。
					Vue.set(x, '__identity', elderIdentity + '_' + i);
					parent.push(x);
					// 如果仍有子集，则进行递归
					if (x.children.length > 0) this.formatConversion(parent, x.children, index + 1, [...family, elderIdentity + '_' + i], elderIdentity + '_' + i);
				});
			}
			return parent;
		}
}