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

2019-11-26 08:30:55

字体：大中小

来源：转载

供稿：网友

项目中在使用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>

测试文件:

@Testpublic 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中，并执行成功

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持武林网。

上一篇：Spring Boot整合Spring Data JPA过程解析

下一篇：Java 最优二叉树的哈夫曼算法的简单实现