根据一级分类Id获取专辑标签（内连接，一对多）

原创于 2024-09-23 11:42:03 发布 · 747 阅读

14 ·

CC 4.0 BY-SA版权

文章标签：

#数据库

MySQL高级篇专栏收录该内容

79 篇文章

订阅专栏

文章目录

- - base_attribute
  - base_attribute_value
1、BaseAttribute
2、BaseAttributeValue
3、BaseCategoryApiController --》findAttribute()
4、BaseCategoryServiceImpl --》findAttribute()
5、BaseAttributeMapper
6、BaseAttributeMapper.xml

当选择完专辑分类之后，就会发送请求获取专辑标签：

在这里插入图片描述
鼠标点击选择标签，效果如下：1对多

对应表：base_attribute、base_attribute_value

base_attribute

mysql> select * from base_attribute;
+----+--------------+-----------------+---------------------+---------------------+------------+
| id | category1_id | attribute_name  | create_time         | update_time         | is_deleted |
+----+--------------+-----------------+---------------------+---------------------+------------+
|  1 |            2 | 有声书分类      | 2023-03-20 15:39:12 | 2023-03-20 15:39:12 |          0 |
|  2 |            2 | 播讲形式        | 2023-03-20 15:39:12 | 2023-03-20 15:39:12 |          0 |
|  3 |            2 | 是否付费        | 2023-03-20 15:39:12 | 2023-03-20 15:39:12 |          0 |
|  4 |            2 | 是否完本        | 2023-03-20 15:39:12 | 2023-03-20 15:39:12 |          0 |
|  5 |            2 | 是否新品        | 2023-03-20 15:39:12 | 2023-03-20 15:39:12 |          0 |
|  6 |           10 | 是否付费        | 2023-03-20 15:53:23 | 2023-03-20 15:53:23 |          0 |
|  7 |           10 | 是否完本        | 2023-03-20 15:53:23 | 2023-03-20 15:53:23 |          0 |
|  8 |           11 | 是否付费        | 2023-03-20 15:53:23 | 2023-03-20 15:58:02 |          0 |
|  9 |           11 | 是否完本        | 2023-03-20 15:58:02 | 2023-03-20 15:58:02 |          0 |
| 10 |            8 | 创作团体        | 2023-03-20 15:58:02 | 2023-03-20 15:58:02 |          0 |
| 11 |            8 | 是否付费        | 2023-03-20 15:58:02 | 2023-03-20 15:58:02 |          0 |
| 12 |            8 | 是否完结        | 2023-03-20 15:58:02 | 2023-03-20 15:58:02 |          0 |
| 13 |            6 | 是否付费        | 2023-03-20 16:04:13 | 2023-03-20 16:04:13 |          0 |
| 14 |            6 | 是否完结        | 2023-03-20 16:04:13 | 2023-03-20 16:04:13 |          0 |
| 15 |            5 | 适合年龄        | 2023-03-20 16:04:13 | 2023-03-20 16:04:13 |          0 |
| 16 |            5 | 是否完结        | 2023-03-20 16:04:13 | 2023-03-20 16:04:13 |          0 |
| 17 |            5 | 是否付费        | 2023-03-20 16:04:13 | 2023-03-20 16:04:13 |          0 |
| 18 |           12 | 是否付费        | 2023-03-20 16:04:13 | 2023-03-20 16:04:13 |          0 |
| 19 |           12 | 是否完结        | 2023-03-20 16:04:13 | 2023-03-20 16:04:13 |          0 |
+----+--------------+-----------------+---------------------+---------------------+------------+
19 rows in set (0.00 sec)

base_attribute_value

mysql> select * from base_attribute_value;
+----+--------------+--------------+---------------------+---------------------+------------+
| id | attribute_id | value_name   | create_time         | update_time         | is_deleted |
+----+--------------+--------------+---------------------+---------------------+------------+
|  1 |            1 | 男频小说     | 2023-03-20 16:15:34 | 2023-03-20 16:15:34 |          0 |
|  2 |            1 | 女频小说     | 2023-03-20 16:15:34 | 2023-03-20 16:15:34 |          0 |
|  3 |            2 | 多人         | 2023-03-20 16:15:34 | 2023-03-20 16:15:34 |          0 |
|  4 |            2 | 双人         | 2023-03-20 16:15:34 | 2023-03-20 16:15:34 |          0 |
|  5 |            2 | 单人         | 2023-03-20 16:15:34 | 2023-03-20 16:15:34 |          0 |
|  6 |            3 | 付费         | 2023-03-20 16:15:34 | 2023-03-20 16:15:34 |          0 |
|  7 |            3 | 免费         | 2023-03-20 16:15:34 | 2023-03-20 16:15:34 |          0 |
|  8 |            4 | 完结         | 2023-03-20 16:15:34 | 2023-03-20 16:15:34 |          0 |
|  9 |            4 | 连载中       | 2023-03-20 16:15:34 | 2023-03-20 16:15:34 |          0 |
| 10 |            5 | 新品         | 2023-03-20 16:15:34 | 2023-03-20 16:15:34 |          0 |
| 11 |            5 | 非新品       | 2023-03-20 16:15:34 | 2023-03-20 16:15:34 |          0 |
| 12 |            6 | 付费         | 2023-03-20 16:15:34 | 2023-03-20 16:15:34 |          0 |
| 13 |            6 | 免费         | 2023-03-20 16:15:34 | 2023-03-20 16:15:34 |          0 |
| 14 |            7 | 完结         | 2023-03-20 16:15:34 | 2023-03-20 16:15:34 |          0 |
| 15 |            7 | 连载中       | 2023-03-20 16:15:34 | 2023-03-20 16:15:34 |          0 |
| 16 |            8 | 付费         | 2023-03-20 16:15:34 | 2023-03-20 16:15:34 |          0 |
| 17 |            8 | 免费         | 2023-03-20 16:15:34 | 2023-03-20 16:15:34 |          0 |
| 18 |            9 | 完结         | 2023-03-20 16:15:34 | 2023-03-20 16:15:34 |          0 |
| 19 |            9 | 连载中       | 2023-03-20 16:15:34 | 2023-03-20 16:15:34 |          0 |
| 20 |           10 | 德云社       | 2023-03-20 16:15:34 | 2023-03-20 16:15:34 |          0 |
| 21 |           10 | 其他         | 2023-03-20 16:15:34 | 2023-03-20 16:15:34 |          0 |
| 22 |           11 | 付费         | 2023-03-20 16:15:34 | 2023-03-20 16:15:34 |          0 |
| 23 |           11 | 免费         | 2023-03-20 16:15:34 | 2023-03-20 16:15:34 |          0 |
| 24 |           12 | 完结         | 2023-03-20 16:15:34 | 2023-03-20 16:15:34 |          0 |
| 25 |           12 | 连载中       | 2023-03-20 16:15:34 | 2023-03-20 16:15:34 |          0 |
| 26 |           13 | 付费         | 2023-03-20 16:15:34 | 2023-03-20 16:15:34 |          0 |
| 27 |           13 | 免费         | 2023-03-20 16:15:34 | 2023-03-20 16:15:34 |          0 |
| 28 |           14 | 完结         | 2023-03-20 16:15:34 | 2023-03-20 16:15:34 |          0 |
| 29 |           14 | 连载中       | 2023-03-20 16:15:34 | 2023-03-20 16:15:34 |          0 |
| 30 |           15 | 0-3岁        | 2023-03-20 16:15:34 | 2023-03-20 16:15:34 |          0 |
| 31 |           15 | 3-6岁        | 2023-03-20 16:15:34 | 2023-03-20 16:15:34 |          0 |
| 32 |           15 | 6岁以上      | 2023-03-20 16:15:34 | 2023-03-20 16:15:34 |          0 |
| 33 |           16 | 完结         | 2023-03-20 16:15:34 | 2023-03-20 16:15:34 |          0 |
| 34 |           16 | 连载中       | 2023-03-20 16:15:34 | 2023-03-20 16:15:34 |          0 |
| 35 |           17 | 付费         | 2023-03-20 16:15:34 | 2023-03-20 16:15:34 |          0 |
| 36 |           17 | 免费         | 2023-03-20 16:15:34 | 2023-03-20 16:15:34 |          0 |
| 37 |           18 | 付费         | 2023-03-20 16:15:34 | 2023-03-20 16:15:34 |          0 |
| 38 |           18 | 免费         | 2023-03-20 16:15:34 | 2023-03-20 16:15:34 |          0 |
| 39 |           19 | 完结         | 2023-03-20 16:15:34 | 2023-03-20 16:15:34 |          0 |
| 40 |           19 | 连载中       | 2023-03-20 16:15:34 | 2023-03-20 16:15:34 |          0 |
+----+--------------+--------------+---------------------+---------------------+------------+
40 rows in set (0.00 sec)

请求路径：/api/album/category/findAttribute/{categoryId}

请求参数：占位符参数

请求方式：get

返回结果集：

{
    "code": 200,
    "message": "成功",
    "data": [
        {
            "id": 1,
            "createTime": null,
            "category1Id": 2,
            "attributeName": "有声书分类",
            "attributeValueList": [
                {
                    "id": 1,
                    "createTime": null,
                    "attributeId": 1,
                    "valueName": "男频小说"
                },
                {
                    "id": 2,
                    "createTime": null,
                    "attributeId": 1,
                    "valueName": "女频小说"
                }
            ]
        },
        {
            "id": 2,
            "createTime": null,
            "category1Id": 2,
            "attributeName": "播讲形式",
            "attributeValueList": [
                {
                    "id": 3,
                    "createTime": null,
                    "attributeId": 2,
                    "valueName": "多人"
                },
                {
                    "id": 4,
                    "createTime": null,
                    "attributeId": 2,
                    "valueName": "双人"
                },
                {
                    "id": 5,
                    "createTime": null,
                    "attributeId": 2,
                    "valueName": "单人"
                }
            ]
        },
        {
            "id": 3,
            "createTime": null,
            "category1Id": 2,
            "attributeName": "是否付费",
            "attributeValueList": [
                {
                    "id": 6,
                    "createTime": null,
                    "attributeId": 3,
                    "valueName": "付费"
                },
                {
                    "id": 7,
                    "createTime": null,
                    "attributeId": 3,
                    "valueName": "免费"
                }
            ]
        },
        {
            "id": 4,
            "createTime": null,
            "category1Id": 2,
            "attributeName": "是否完本",
            "attributeValueList": [
                {
                    "id": 8,
                    "createTime": null,
                    "attributeId": 4,
                    "valueName": "完结"
                },
                {
                    "id": 9,
                    "createTime": null,
                    "attributeId": 4,
                    "valueName": "连载中"
                }
            ]
        },
        {
            "id": 5,
            "createTime": null,
            "category1Id": 2,
            "attributeName": "是否新品",
            "attributeValueList": [
                {
                    "id": 10,
                    "createTime": null,
                    "attributeId": 5,
                    "valueName": "新品"
                },
                {
                    "id": 11,
                    "createTime": null,
                    "attributeId": 5,
                    "valueName": "非新品"
                }
            ]
        }
    ]
}

1、BaseAttribute

package com.atguigu.tingshu.model.album;

import com.atguigu.tingshu.model.base.BaseEntity;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableName;
import io.swagger.v3.oas.annotations.media.Schema;
import lombok.Data;

import java.util.List;

@Data
@Schema(description = "BaseAttribute")
@TableName("base_attribute")
public class BaseAttribute extends BaseEntity {

	private static final long serialVersionUID = 1L;

	@Schema(description = "一级分类id")
	@TableField("category1_id")
	private Long category1Id;

	@Schema(description = "属性显示名称")
	@TableField("attribute_name")
	private String attributeName;

	@TableField(exist = false)
	private List<BaseAttributeValue> attributeValueList;

}

2、BaseAttributeValue

package com.atguigu.tingshu.model.album;

import com.atguigu.tingshu.model.base.BaseEntity;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableName;
import io.swagger.v3.oas.annotations.media.Schema;
import lombok.Data;

@Data
@Schema(description = "BaseAttributeValue")
@TableName("base_attribute_value")
public class BaseAttributeValue extends BaseEntity {

	private static final long serialVersionUID = 1L;

	@Schema(description = "属性id")
	@TableField("attribute_id")
	private Long attributeId;

	@Schema(description = "属性值名称")
	@TableField("value_name")
	private String valueName;

}

3、BaseCategoryApiController --》findAttribute()

@Tag(name = "分类管理")
@RestController
@RequestMapping(value="/api/album/category")
@SuppressWarnings({"unchecked", "rawtypes"})
public class BaseCategoryApiController {
	/**
	 * 根据一级分类ID查询属性
	 *
	 * 该方法用于从数据库中查询与给定的一级分类ID相关的所有属性它通过HTTP GET请求的URL中提取一级分类ID，
	 * 然后调用业务服务来获取相应的属性列表最后，它返回一个包含查询到的属性列表的Result对象
	 *
	 * @param category1Id 从URL中获取的一级分类ID，用于查询特定分类的属性
	 * @return 返回一个Result对象，其中包含一个属性列表，这些属性与给定的一级分类ID相关
	 */
	@GetMapping("findAttribute/{category1Id}")
	public Result<List<BaseAttribute>> findAttribute(@PathVariable("category1Id") Long category1Id){
	    // 调用业务服务根据一级分类ID查询属性
	    List<BaseAttribute> baseAttributes = this.baseCategoryService.findAttribute(category1Id);

	    // 返回查询到的属性列表的Result对象
	    return Result.ok(baseAttributes);
	}
}

4、BaseCategoryServiceImpl --》findAttribute()

@Service
@SuppressWarnings({"unchecked", "rawtypes"})
public class BaseCategoryServiceImpl extends ServiceImpl<BaseCategory1Mapper, BaseCategory1> implements BaseCategoryService {
    @Override
    public List<BaseAttribute> findAttribute(Long category1Id) {

        return this.baseAttributeMapper.findAttribute(category1Id);
    }
}

5、BaseAttributeMapper

package com.atguigu.tingshu.album.mapper;

import com.atguigu.tingshu.model.album.BaseAttribute;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;

import java.util.List;

@Mapper
public interface BaseAttributeMapper extends BaseMapper<BaseAttribute> {


    List<BaseAttribute> findAttribute(Long category1Id);
}

6、BaseAttributeMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//ibatis.apache.org//DTD Mapper 3.0//EN"
"http://ibatis.apache.org/dtd/ibatis-3-mapper.dtd">
<mapper namespace="com.atguigu.tingshu.album.mapper.BaseAttributeMapper">

    <resultMap id="attributeMap" type="com.atguigu.tingshu.model.album.BaseAttribute" autoMapping="true">
        <id column="id" property="id"></id>
        <collection property="attributeValueList" javaType="List" ofType="com.atguigu.tingshu.model.album.BaseAttributeValue" autoMapping="true">
            <id column="bav_id" property="id"/>
        </collection>
    </resultMap>

    <select id="findAttribute" resultMap="attributeMap">
        select ba.id, ba.category1_id, ba.attribute_name, bav.id bav_id, bav.attribute_id, bav.value_name from base_attribute ba
           inner join base_attribute_value bav on ba.id=bav.attribute_id
        where ba.category1_id=#{category1Id} and ba.is_deleted=0 and bav.is_deleted=0
    </select>
</mapper>

select * from base_attribute ba 
inner join base_attribute_value bav on ba.id=bav.attribute_id 
where ba.category1_id=2 and ba.is_deleted=0 and bav.is_deleted=0;

mysql> select * from base_attribute ba inner join base_attribute_value bav on ba.id=bav.attribute_id where ba.category1_id=2 and ba.is_deleted=0 and bav.is_deleted=0;
+----+--------------+-----------------+---------------------+---------------------+------------+----+--------------+--------------+---------------------+---------------------+------------+
| id | category1_id | attribute_name  | create_time         | update_time         | is_deleted | id | attribute_id | value_name   | create_time         | update_time         | is_deleted |
+----+--------------+-----------------+---------------------+---------------------+------------+----+--------------+--------------+---------------------+---------------------+------------+
|  1 |            2 | 有声书分类      | 2023-03-20 15:39:12 | 2023-03-20 15:39:12 |          0 |  1 |            1 | 男频小说     | 2023-03-20 16:15:34 | 2023-03-20 16:15:34 |          0 |
|  1 |            2 | 有声书分类      | 2023-03-20 15:39:12 | 2023-03-20 15:39:12 |          0 |  2 |            1 | 女频小说     | 2023-03-20 16:15:34 | 2023-03-20 16:15:34 |          0 |
|  2 |            2 | 播讲形式        | 2023-03-20 15:39:12 | 2023-03-20 15:39:12 |          0 |  3 |            2 | 多人         | 2023-03-20 16:15:34 | 2023-03-20 16:15:34 |          0 |
|  2 |            2 | 播讲形式        | 2023-03-20 15:39:12 | 2023-03-20 15:39:12 |          0 |  4 |            2 | 双人         | 2023-03-20 16:15:34 | 2023-03-20 16:15:34 |          0 |
|  2 |            2 | 播讲形式        | 2023-03-20 15:39:12 | 2023-03-20 15:39:12 |          0 |  5 |            2 | 单人         | 2023-03-20 16:15:34 | 2023-03-20 16:15:34 |          0 |
|  3 |            2 | 是否付费        | 2023-03-20 15:39:12 | 2023-03-20 15:39:12 |          0 |  6 |            3 | 付费         | 2023-03-20 16:15:34 | 2023-03-20 16:15:34 |          0 |
|  3 |            2 | 是否付费        | 2023-03-20 15:39:12 | 2023-03-20 15:39:12 |          0 |  7 |            3 | 免费         | 2023-03-20 16:15:34 | 2023-03-20 16:15:34 |          0 |
|  4 |            2 | 是否完本        | 2023-03-20 15:39:12 | 2023-03-20 15:39:12 |          0 |  8 |            4 | 完结         | 2023-03-20 16:15:34 | 2023-03-20 16:15:34 |          0 |
|  4 |            2 | 是否完本        | 2023-03-20 15:39:12 | 2023-03-20 15:39:12 |          0 |  9 |            4 | 连载中       | 2023-03-20 16:15:34 | 2023-03-20 16:15:34 |          0 |
|  5 |            2 | 是否新品        | 2023-03-20 15:39:12 | 2023-03-20 15:39:12 |          0 | 10 |            5 | 新品         | 2023-03-20 16:15:34 | 2023-03-20 16:15:34 |          0 |
|  5 |            2 | 是否新品        | 2023-03-20 15:39:12 | 2023-03-20 15:39:12 |          0 | 11 |            5 | 非新品       | 2023-03-20 16:15:34 | 2023-03-20 16:15:34 |          0 |
+----+--------------+-----------------+---------------------+---------------------+------------+----+--------------+--------------+---------------------+---------------------+------------+
11 rows in set (0.00 sec)