【Mybatis实例】实现动态表头数据（动态行专列）、对动态表头字段排序

逃离我推掉我的手 2024-04-01 06:00 70阅读 0赞

#### 目录 ####

*   *  动态表头（表头排序）
     *   *  业务说明
         *  实现
         *   *  1 \*\*Service代码实现\*\*
             *  2 \*\*动态表头获取\*\*
             *  3 \*\*表格内容获取\*\*
             *  4 \*\*动态表头排序\*\*
             *  5 \*\*执行结果\*\*
     *  表DDL

### 动态表头（表头排序） ###

#### 业务说明 ####

现在有三张表，每个表的数据都是动态变化的：科目表（subjects）、用户表（users）、分数表（score）

由于业务需要，现在要将科目信息作为表头，用户作为表的行数据，每行显示的是用户对应科目的分数，并且要支持对表头的每个科目和总分进行排序。

内容如下图：  
![在这里插入图片描述][50aa731084b346b4a776814c21133d58.png]

#### 实现 ####

一般来说，动态表头都可以通过实现行专列来实现。

行专列SQL示例：

SELECT 
    	u.`name`, 
      MAX(CASE su.`name` WHEN 'java' THEN s.score END ) `java`,
      MAX(CASE su.`name` WHEN 'C' THEN s.score END ) `C`,
      MAX(CASE su.`name` WHEN 'C#' THEN s.score END ) `C#`,
      MAX(CASE su.`name` WHEN 'Python' THEN s.score END ) `Python`
    FROM users u 
    LEFT JOIN score s ON u.id = s.user_id
    LEFT JOIN subjects su ON su.id = s.subject_id
    GROUP BY u.`name`;

但是这么做有个缺点，当科目数据是动态的时候，那么上面行专列的SQL就失效了。因此，行转列的这一部分数据也得改成动态数据才行。

##### 1 **Service代码实现** #####

依照行专列的这个思想，我们可以先查询所有的科目数据，然后将科目数据作为行专列的参数，然后来查询表格内容。

public StatDTO table() {
        
        StatQuery query = new StatQuery();
        StatDTO statDTO = new StatDTO();
        // 查询所有科目（表头）
        Map<Integer, Subjects> subjectsMap = subjectsMapper.queryAll();
        statDTO.setHeaders(subjectsMap);
        // 设置表头参数，用作表头的行专列
        query.setSubjects(new ArrayList<>(subjectsMap.values()));
        // 表格内容数据查询
        List<Map<String, Object>> contents = usersMapper.query(query);
        statDTO.setContents(contents);
        return statDTO;
    }

##### 2 **动态表头获取** #####

将表头转为map，主要是方便前端处理表头和数据的映射关系，这里的Map的键将作为表格内容映射的依据。

// subjectsMapper#mapper
    @MapKey("id")
    Map<Integer, Subjects> queryAll();
    
    // subjectsMapper#xml
    <select id="queryAll" resultType="mybatisreading.domain.Subjects">
        select
          id, name
        from test.subjects
    </select>

实体数据：

@Data
    public class Subjects implements Serializable {
        
        private Integer id;
        private String name;
        // 用于设置一个字符串类型的对应关系，如果ID本身就是字符串（UUID），这一步可以省略
        public String getSubjectId() {
        
            return "subject" + id;
        }
    }

##### 3 **表格内容获取** #####

> 动态行专列的注意点:
> 
>  *  注意“**$**”与“**\#**”的使用区别
>  *  在select中设置别名不能使用数字，因此需要将科目ID转为一个字符串。如果id是字符串则这一点忽略
>  *  为了保证排序结果的有序性，这里返回的结果使用LinkedHashMap

获取完整表格内容的源码如下：

# usersMapper#mapper
    List<Map<String, Object>> query(StatQuery query);
    
    # usersMapper#xml
    <select id="query" resultType="java.util.LinkedHashMap">
        SELECT *
        FROM
        (
            SELECT
            u.`name`,
            <foreach item="item" index="index" collection="subjects" separator=",">
                MAX(case su.id when #{item.id} then s.score else 0 end) AS ${item.subjectId}
            </foreach>
                ,
            (
                <foreach item="item" index="index" collection="subjects" separator="+">
                    MAX(case su.id when #{item.id} then s.score else 0 end)
                </foreach>
            ) total
            FROM users u
            LEFT JOIN score s ON u.id = s.user_id
            LEFT JOIN subjects su ON su.id = s.subject_id
            GROUP BY u.`name`
        ) r
        <if test="subjectId != null">
            <choose>
                <when test='sort != null and sort == "1"'>
                    ORDER BY ${subjectId}
                </when>
                <otherwise>
                    ORDER BY ${subjectId} DESC
                </otherwise>
            </choose>
        </if>
    </select>

StatQuery查询参数内容如下：

@Data
    public class StatQuery {
        
        /**
         * 科目ID 这里要使用string 数字无法作为表头名
         */
        private String subjectId;
        /**
         * 0 DESC  1 AES
         */
        private Integer sort;
        private List<Subjects> subjects;
    }

##### 4 **动态表头排序** #####

代码在3中已经展示过了。动态表头的排序，相对来说比较简单，只需要使用重名命后的列名进行排序即可：

<if test="subjectId != null">
            <choose>
                <when test='sort != null and sort == "1"'>
                    ORDER BY ${subjectId}
                </when>
                <otherwise>
                    ORDER BY ${subjectId} DESC
                </otherwise>
            </choose>
        </if>

##### 5 **执行结果** #####

{
        
      "headers": {
        
        "1": {
        
          "id": 1,
          "name": "java",
          "subjectId": "subject1"
        },
        "2": {
        
          "id": 2,
          "name": "C",
          "subjectId": "subject2"
        },
        "3": {
        
          "id": 3,
          "name": "C#",
          "subjectId": "subject3"
        },
        "4": {
        
          "id": 4,
          "name": "Python",
          "subjectId": "subject4"
        }
      },
      "contents": [
        {
        
          "name": "沸羊羊",
          "subject1": 76,
          "subject2": 32,
          "subject3": 67,
          "subject4": 12,
          "total": 187
        },
        {
        
          "name": "懒羊羊",
          "subject1": 42,
          "subject2": 27,
          "subject3": 99,
          "subject4": 12,
          "total": 180
        },
        {
        
          "name": "喜羊羊",
          "subject1": 89,
          "subject2": 98,
          "subject3": 90,
          "subject4": 97,
          "total": 374
        }
      ]
    }

--------------------

### 表DDL ###

CREATE TABLE `subjects` (
      `id` int NOT NULL,
      `name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '科目',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
    
    
    CREATE TABLE `users` (
      `id` int NOT NULL,
      `name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
    
    
    CREATE TABLE `score` (
      `id` int NOT NULL,
      `user_id` int DEFAULT NULL,
      `subject_id` int DEFAULT NULL,
      `score` tinyint DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--------------------

源码地址: [github][]

[50aa731084b346b4a776814c21133d58.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/05cc8400c9e644eab6466531bf6a0ccd.png
[github]: https://github.com/VsLegend/mybatis-reading