java从model中取出id,在Mybatis的collection标签中获取以,分隔的id字符串

有的时候我们把一个表的id以逗号(,)分隔的字符串形式放在另一个表里表示一种包含关系，当我们要查询出我们所需要的全部内容时，会在resultMap标签中使用collection标签来获取这样的一个集合。

我们以门店以及门店提供的服务来进行一个介绍

这是一个门店表，service_ids是一家门店包含的所有的服务id

Java实体类为

/*** 服务商门店*/@NoArgsConstructor@Datapublic classStore {

privateLong id;privateString name;privateAddress address;privateString cityName;privateList serviceList;privateDouble avgStar;//服务的数量privateInteger numService;}

服务的数据表

Java实体类如下

/*** 商家服务*/@NoArgsConstructor@AllArgsConstructor@Datapublic classService {

privateLong id;privateString name;privatePrice price;privateString topUrls;privateString details;privateList evaluateList;

publicService deepClone() {

Input input = null;try{

Kryo kryo = newKryo();ByteArrayOutputStream stream = newByteArrayOutputStream();Output output = newOutput(stream);kryo.writeObject(output, this);output.close();// System.out.println(Arrays.toString(stream.toByteArray()));input = newInput(newByteArrayInputStream(stream.toByteArray()));returnkryo.readObject(input,Service.class);}finally{

input.close();}

}

}

Mybatis dao如下

@Mapperpublic interfaceStoreDao {

List findStoreByCity(String city);@Update("update store set service_ids=concat(service_ids,concat(',',#{serviceId})) where id=#{storeId}")

intaddServiceToStore(ParamId paramId);}

这里我们主要看的是findStoreByCity方法

映射文件如下

<?xml version="1.0"encoding="UTF-8"?>-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd">select id,name,normal_price,seckill_price,top_urls,details from service

id in (SELECT DISTINCT

SUBSTRING_INDEX(

SUBSTRING_INDEX(#{service_ids}, ',', id),

',' ,- 1

)

FROM service)

select id,store_name,city_name,address_name,

address_longitude,address_latitude,service_ids

from store

city_name=#{city}

我们重点来看的是

select id,name,normal_price,seckill_price,top_urls,details from service

id in (SELECT DISTINCT

SUBSTRING_INDEX(

SUBSTRING_INDEX(#{service_ids}, ',', id),

',' ,- 1

)

FROM service)

这里需要说明的是如果写成id in (#{service_ids})是取不出我们所希望的集合的，因为#{service_ids}只是一个字符串，翻译过来的语句例为id in ('1,2,3')之类的语句，所以需要将它解析成id in (1,2,3)，substring_index的作用可以自行查询。

最终在controller中查出来的结果如下

{

"code": 200,

"data": [

{

"address": {

"distance": 11444.8137,

"latitude": 256.2342133234,

"longitude": 135.3454234,

"name": "三润汽修厂"

},

"avgStar": 5,

"cityName": "广州",

"id": 1,

"name": "三润汽修厂",

"serviceList": [

{

"details": "sdfadfsdfdadsdf",

"id": 1,

"name": "人工洗车",

"price": {

"normalPrice": 50,

"secKillPrice": 45

},

"topUrls": "http://123.456.789"

},

{

"details": "ddsadfasdehgfjh",

"id": 2,

"name": "换轮胎",

"price": {

"normalPrice": 300,

"secKillPrice": 250

},

"topUrls": "http://123.456.789"

},

{

          "details": "

",

"id": 2455928998547424253,

"name": "大保养",

"price": {

"normalPrice": 50,

"secKillPrice": 45

},

"topUrls": "http://123.234.123.12,http://234.123.343.21"

}

]

},

{

"address": {

"distance": 18577.1862,

"latitude": 348.23423234,

"longitude": 168.2344234,

"name": "驰加京海店"

},

"avgStar": null,

"cityName": "广州",

"id": 2,

"name": "驰加京海店",

"serviceList": [

{

"details": "sdfadfsdfdadsdf",

"id": 1,

"name": "人工洗车",

"price": {

"normalPrice": 50,

"secKillPrice": 45

},

"topUrls": "http://123.456.789"

},

{

"details": "ddsadfasdehgfjh",

"id": 2,

"name": "换轮胎",

"price": {

"normalPrice": 300,

"secKillPrice": 250

},

"topUrls": "http://123.456.789"

},

{

          "details": "

",

"id": 2456268364314575869,

"name": "小保养",

"price": {

"normalPrice": 100,

"secKillPrice": 88

},

"topUrls": "http://123.234.123.12,http://234.123.343.21"

}

]

}

],

"msg": "操作成功"

}