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

 
 



 
 
  
  
 



 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> 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
 }
]