5

合并多行查询的结果

业务需求

商家可以根据商品类目分类,每位商家可以有多个商品类目,商品类目又跟运营类目有一种关联规则。此处暂且不说运营类目。根据业务,商家信息是一张表,商家与商品类目有一张关联表,商品类目有一张表,权且使用三张表。

其他不相干字段不展示
  • 商家表(user)
uid username mobile
1 张三 12345678901
2 李四 12345600002
  • 商品类目表(item_category)
icid name status parentid
1 服装 0 0
2 茶具 0 0
3 食品 0 0
  • 商家与商品类目(user_item_cate_rel)
uid icid
1 1
1 2
2 1
2 3

如果我们需要在前端页面展示每个商家的商品类目,那么我们返回的结果中就需要每条商家数据中包含所属商品的类目。实现方式有很多种:1.一条sql语句可以实现,使用group_concat函数并根据uid分组2.先查询商家信息,再根据商家uid查询user_item_cate_rel和item_category表,再将结果在model中组合3.用mybatis的resultMap。此处只看1和3的实现方式。

  • 展示需求:
# 商家ID 商家名 手机号码 商品类目 操作
1 1 张三 12345678901 服装,茶具 删除 修改
2 2 李四 12345600002 服装,食品 删除 修改

group_caoncat实现

SELECT u.*,group_concat(ic.name) categories FROM user 
LEFT JOIN user_item_cate_rel uicr
ON u.uid = uicr.uid
LEFT JOIN item_category ic
ON uicr.icid = ic.icid
WHERE ... # 查询条件
GROUP BY u.uid

大概就这样,可以把类目直接返回给前端,毫无疑问,这个实现是最快的。但是,当数据量大的时候,并且是查询列表的时候,全表扫描,就会出现慢查询。

巧妙使用Mybatis resultMap

创建User和Category POJO。User除了user表的字段作为属性外,外加一个private List<Category> categories属性。Category的字段分别为:icidname

mapper的xml中:

  • 先定义一个id为user的resultMap:
<resultMap id="user" type="User" autoMapping="true">
    <collection property="categories" ofType="Category" select="getCategory" column="uid" javaType="ArrayList">
        <id property="icid" column="icid"/>
        <result property="name" column="name"/>
    </collection>
</resultMap>
  • 实现select属性对应方法:此处select的id为上面id为user的resultMap中collection的select元素,参数#{uid}为column元素指定的字段。
<select id="getCategory" resultType="Category">
    SELECT uicr.icid,ic.name FROM user_item_cate_rel uicr
    LEFT JOIN item_category ic
    ON uicr.icid = ic.icid
    WHERE uicr.uid = #{uid}
</select>
  • 获取指定条数的数据:mybatis可以根据传入的参数组装sql语句。
<select id="getUsers" parameterType="UserSearch" resultMap="user">
    SELECT u.* FROM user u LEFT JOIN user_item_cate_rel uicr 
    ON u.uid = uicr.uid
    WHERE 1=1
    <if test="uid != null"> AND u.uid = #{uid} </if>
    <if test="username != null"> AND u.username LIKE concat('%',#{username}, '%')</if>
    <if test="mobile != null"> AND u.mobile = #{mobile}</if>
    <if test="icid != null"> AND uicr.icid = #{icid}</if>
    ..... // 各种查询条件
    LIMIT #{page},#{offset}
</select>

上面查询中,UserSearch是查询条件POJO,可以根据需要自定义过滤那些字段。其中有一个属性是icid,就是根据传入的一个商品类目查询商家信息。比如,要查询icid=1的商家,那么我们返回的数据不仅仅是icid=1的,还应该是icid=1商家的其他icid信息。

那么查询结果应该为:

[
    {
        "uid": 1,
        "username": "张三",
        "mobile": "12345678901",
        "categories": [
            {
                "icid": 1,
                "name": "服装"
            },
            {
                "icid": 2,
                "name": "茶具"
            }
        ]
    },
    {
        "uid": 2,
        "username": "李四",
        "mobile": "12345600002",
        "categories": [
            {
                "icid": 1,
                "name": "服装"
            },
            {
                "icid": 3,
                "name": "服装"
            }
        ]
    }
]
此处需要注意一点,resultMap的select元素调用getCategory方法时,自动映射的属性名和列名应该保持一致,或者列表必须是数据库表中的字段名,而不是别名。

Joyven
630 声望34 粉丝

不学无(用之)术,不安于现状,总想鼓捣点什么,或者总想尝试一些什么新鲜事物。