在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": "操作成功"
}