在Mybatis的collection标签中获取以,分隔的id字符串
在Mybatis的collection标签中获取以,分隔的id字符串
有的时候我们把一个表的id以逗号（,）分隔的字符串形式放在另一个表里表示一种包含关系，当我们要查询出我们所需要的全部内容时，会在resultMap标签中使用collection标签来获取这样的一个集合。
我们以门店以及门店提供的服务来进行一个介绍
这是一个门店表，service_ids是一家门店包含的所有的服务id
Java实体类为
/**
* 服务商门店
*/
@NoArgsConstructor
@Data
public class Store {
private Long id;
private String name;
private Address address;
private String cityName;
private List<Service> serviceList;
private Double avgStar;
//服务的数量
private Integer numService;
}
服务的数据表
Java实体类如下
/**
* 商家服务
*/
@NoArgsConstructor
@AllArgsConstructor
@Data
public class Service {
private Long id;
private String name;
private Price price;
private String topUrls;
private String details;
private List<Evaluate> evaluateList;
public Service deepClone() {
Input input = null;
try {
Kryo kryo = new Kryo();
ByteArrayOutputStream stream = new ByteArrayOutputStream();
Output output = new Output(stream);
kryo.writeObject(output, this);
output.close();
// System.out.println(Arrays.toString(stream.toByteArray()));
input = new Input(new ByteArrayInputStream(stream.toByteArray()));
return kryo.readObject(input,Service.class);
}finally {
input.close();
}
}
}另外我们还需要一个用来接引索引的表sequence，只有一个主键字段seq,里面放入尽可能多的从1开始的数字
Mybatis dao如下
@Mapper
public interface StoreDao {
List<Store> findStoreByCity(String city);
@Update("update store set service_ids=concat(service_ids,concat(',',#{serviceId})) where id=#{storeId}")
int addServiceToStore(ParamId paramId);
}这里我们主要看的是findStoreByCity方法
映射文件如下
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.cloud.serviceprovider.dao.StoreDao">
<resultMap id="store_map" type="com.cloud.model.serviceprovider.Store">
<id property="id" column="id" />
<result property="name" column="store_name" />
<result property="cityName" column="city_name" />
<association property="address" javaType="com.cloud.model.serviceprovider.Address">
<id property="name" column="address_name" />
<result property="longitude" column="address_longitude" />
<result property="latitude" column="address_latitude" />
</association>
<collection property="serviceList" javaType="java.util.List" column="service_ids"
ofType="com.cloud.model.serviceprovider.Service"
select="findServiceByIds">
</collection>
</resultMap>
<resultMap id="service_Map" type="com.cloud.model.serviceprovider.Service">
<id column="id" property="id" />
<result column="name" property="name" />
<result column="top_urls" property="topUrls" />
<result column="details" property="details" />
<association property="price" javaType="com.cloud.model.serviceprovider.Price">
<id column="normal_price" property="normalPrice" />
<result column="seckill_price" property="secKillPrice" />
</association>
</resultMap>
<select id="findServiceByIds" parameterType="java.lang.String" resultMap="service_Map" resultType="java.util.List">
select id,name,normal_price,seckill_price,top_urls,details from service
<where>
id in (SELECT DISTINCT
SUBSTRING_INDEX(
SUBSTRING_INDEX(#{service_ids}, ',', seq),
',' ,- 1
)
FROM sequence
where seq BETWEEN 1
AND (
SELECT
1 + LENGTH(#{service_ids}) - LENGTH(replace(#{service_ids}, ',', ''))
))
</where>
</select>
<select id="findStoreByCity" parameterType="java.lang.String" resultMap="store_map">
select id,store_name,city_name,address_name,
address_longitude,address_latitude,service_ids
from store
<where>
city_name=#{city}
</where>
</select>
<select id="findStoreById" parameterType="java.lang.Long" resultMap="store_map"
resultType="com.cloud.model.serviceprovider.Store">
select id,store_name,city_name,address_name,
address_longitude,address_latitude,service_ids
from store
<where>
id=#{id}
</where>
</select>
</mapper>我们重点来看的是
<collection property="serviceList" javaType="java.util.List" column="service_ids"
ofType="com.cloud.model.serviceprovider.Service"
select="findServiceByIds">
</collection><select id="findServiceByIds" parameterType="java.lang.String" resultMap="service_Map" resultType="java.util.List">
select id,name,normal_price,seckill_price,top_urls,details from service
<where>
id in (SELECT DISTINCT
SUBSTRING_INDEX(
SUBSTRING_INDEX(#{service_ids}, ',', seq),
',' ,- 1
)
FROM sequence
where seq BETWEEN 1
AND (
SELECT
1 + LENGTH(#{service_ids}) - LENGTH(replace(#{service_ids}, ',', ''))
))
</where>
</select>这里需要说明的是如果写成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": "<html><body><img src='http://123.234.123.12'></body></html>", "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": "<html><body><img src='http://123.234.123.12'></body></html>", "id": 2456268364314575869, "name": "小保养", "price": { "normalPrice": 100, "secKillPrice": 88 }, "topUrls": "http://123.234.123.12,http://234.123.343.21" } ] } ], "msg": "操作成功" }