溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
項目中在使用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中，并執行成功
以上就是本文的全部內容，希望對大家的學習有所幫助，也希望大家多多支持億速云。
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
