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

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

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

mybatis文档:

property

description

column

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

column="{cityId=city_id,adr=addressCol, dis=districtCol}" //adr作为第二个sql查询条件key,即prop1属性

ofType="map" //addressCol即为虚拟列名

javaType="java.util.List" select="selectAddressByCityId"/>

column="{cityId=city_id,adr=addressCol, dis=districtCol}" //adr作为第二个sql查询条件key,即prop1属性

ofType="map" //addressCol即为虚拟列名

javaType="java.util.List" select="selectAddressByCityId"/>

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

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

and ads.address RegExp #{adr}

ads.district Regexp #{dis}

测试文件:

@Test

public void findCountryCityAddressTest() throws jsonProcessingException {

Map param = new HashMap<>();

param.put("addressQuery","1168");

List<Map> 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中，并执行成功

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。