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

动态表头（表头排序）

业务说明

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

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

内容如下图：

实现

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

行专列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