如何将数据库某列的值（如日期）作为表格的列名

如何将数据库某列的值（如日期）作为表格的列名

需求：前端需要展示如下的二维表格，其中表格的日期是数据库表里data的值，每行不同的值是表格里的字段值，也就是需要将表里的数据行转列，列转行，本来想着通过sql和mybatis映射完成转换，但是搜了一下没有找到自己想要的方法，只好通过java代码来完成转换。


前端代码：

<template>
<div>
<el-form :model="searchForm" ref="searchForm" size="small" :inline="true" label-width="68px">
<el-form-item label="日期" prop="date">
<el-date-picker
v-model="searchForm.date"
type="daterange"
range-separator="至"
start-placeholder="开始日期"
end-placeholder="结束日期"
/>
</el-form-item>
<el-form-item>
<el-button type="primary" icon="el-icon-search" size="mini" @click="query">搜索</el-button>
<el-button type="info" icon="el-icon-download" size="mini" @click="upload">导出</el-button>
<el-button icon="el-icon-refresh" size="mini" @click="resetQuery">重置</el-button>
</el-form-item>
</el-form>

<el-table :data="usdDataList" border stripe v-loading="loading">
<el-table-column prop="date" label="日期" width="155" align="center">
<el-table-column prop="d0" label="项目USD" width="155" align="center"></el-table-column>
</el-table-column>
<el-table-column
v-for="(item,index) in listLabel"
:key="index"
:prop="item.prop"
:label="item.label"
/>
</el-table>
<el-table :data="ureDataList" border stripe v-loading="loading">
<el-table-column prop="date" label="日期" width="155" align="center">
<el-table-column prop="d0" label="项目EUR" width="155" align="center"></el-table-column>
</el-table-column>
<el-table-column
v-for="(item,index) in listLabel"
:key="index"
:prop="item.prop"
:label="item.label"
/>
</el-table>
</div>
</template>
<script>
import Api from "@/constants/Api.js";
import { getDate, getNextDate, parseTime } from "@/utils/utils";
export default {
name: "zmqldxcsb",
data() {
return {
listLabel: [],
usdDataList: [],
ureDataList: [],
searchForm: {
date: [getDate(0), getDate(30)]
},
loading: false
};
},
created() {
this.query();
},
methods: {
resetQuery() {
this.searchForm = {
date: [getDate(0), getDate(30)]
};
},
query() {
this.loading = true;
this.$axios({
method: "post",
url: Api.freeTradMobility,
data: {
startTime: parseTime(this.searchForm.date[0], "{y}{m}{d}"),
endTime: parseTime(this.searchForm.date[1], "{y}{m}{d}")
}
})
.then(res => {
this.listLabel = res.labelList;
this.usdDataList = res.dataMap["USD"];
this.ureDataList = res.dataMap["EUR"];
this.loading = false;
})
.catch(error => {
console.log(error.message);
this.$message.error("查询数据出错！");
this.loading = false;
});
}
};
</script>
<style scoped>
/* ::v-deep .el-table thead.is-group th {
background: none;
padding: 0px;
} */
::v-deep .el-table thead.is-group tr:first-of-type th:first-of-type {
border-bottom: none;
}
::v-deep .el-table thead.is-group tr:first-of-type th:first-of-type div.cell {
text-align: right;
}
::v-deep .el-table thead.is-group tr:last-of-type th:first-of-type div.cell {
text-align: left;
}
::v-deep .el-table thead.is-group tr:first-of-type th:first-of-type:before {
content: "";
position: absolute;
width: 1px;
height: 100px;
top: 0;
left: 0;
background-color: grey;
opacity: 0.2;
display: block;
transform: rotate(-43deg);
transform: rotate(-70deg);
-webkit-transform-origin: top;
transform-origin: top;
}
::v-deep .el-table thead.is-group tr:last-of-type th:first-of-type:before {
content: "";
position: absolute;
width: 1px;
height: 100px;
bottom: 0;
right: 0;
background-color: grey;
opacity: 0.2;
display: block;
transform: rotate(-45deg);
transform: rotate(-70deg);
-webkit-transform-origin: bottom;
transform-origin: bottom;
}
::v-deep .el-table thead.is-group th {
height: 27.4px;
}
</style>

每个表格的代码：

<el-table :data="usdDataList" border stripe v-loading="loading">
<el-table-column prop="date" label="日期" width="155" align="center">
<el-table-column prop="d0" label="项目USD" width="155" align="center"></el-table-column>
</el-table-column>
<el-table-column
v-for="(item,index) in listLabel"
:key="index"
:prop="item.prop"
:label="item.label"
/>
</el-table>

下面是需要的数据格式，其中日期是动态的，所以日期和每行的数据是分开的，日期的数据格式：

"labelList": [
{
"label": "20231024",
"prop": "d20231024"
},
{
"label": "20231025",
"prop": "d20231025"
},
{
"label": "20231026",
"prop": "d20231026"
},
{
"label": "20231027",
"prop": "d20231027"
},
{
"label": "20231028",
"prop": "d20231028"
}
],

label就是每列展示的日期值，prop就是每行绑定的数据。

表格数据的格式：

[
{
"d20231028": "10",
"d0": "日出余额",
"d20231027": "10",
"d20231026": "10"
},
{
"d20231028": "10",
"d0": "总行2036",
"d20231027": "10",
"d20231026": "10"
},
{
"d20231028": "10",
"d0": "即远期",
"d20231027": "10",
"d20231026": "10"
},
{
"d20231028": "10",
"d0": "掉期",
"d20231027": "10",
"d20231026": "10"
},
{
"d20231028": "10",
"d0": "拆借",
"d20231027": "10",
"d20231026": "10"
},
{
"d20231028": "10",
"d0": "回购",
"d20231027": "10",
"d20231026": "10"
},
{
"d20231028": "10",
"d0": "TRS",
"d20231027": "10",
"d20231026": "10"
},
{
"d20231028": "10",
"d0": "债券到期",
"d20231027": "10",
"d20231026": "10"
},
{
"d20231028": "10",
"d0": "债券投资",
"d20231027": "10",
"d20231026": "10"
},
{
"d20231028": "10",
"d0": "客户进出款（合计）",
"d20231027": "10",
"d20231026": "10"
},
{
"d20231028": "vdvdsvd",
"d0": "备注",
"d20231027": "vdvdsvd",
"d20231026": "vdvdsvd"
},
{
"d20231028": "10",
"d0": "日终余额",
"d20231027": "10",
"d20231026": "10"
}
]

其中d0就是表格代码第三行的prop="d0"绑定的值，其余的就是每个日期绑定的值。
针对这两种格式，我们在java代码中就是需要把查到的数据进行组装。

java代码如下。

@Autowired
private DaoSupport daoSupport;

//方法入口
public FrnFreeTradMobilityOut query(FrnFreeTradMobilityIn input) {
//处理时间
String startDate = input.getStartTime();
String endDate = input.getEndTime();
//生成日期的集合
List<Label> labels = createdLabelList(startDate, endDate);

FrnFreeTradMobilityOut out = new FrnFreeTradMobilityOut();
out.setLabelList(labels);
Map<String, Object> queryMap = new HashMap<>();
queryMap.put("startDate", startDate);
queryMap.put("endDate", endDate);

//查询表格数据
List<FrnFreetradMobilityPo> list = daoSupport.selectList(FrnFreetradMobilityPo.class.getName() + ".select", queryMap);

//组装数据，因为我是多个表格，所以采用map形式，key为每个表格的名称，值为表格数据，如果只有一个表格，可以用返回list
Map<String, List<Map<String, String>>> dataMap = createdDataMap(list);
out.setDataMap(dataMap);
return out;
}


private Map<String, List<Map<String, String>>> createdDataMap(List<FrnFreetradMobilityPo> list) {
Map<String, List<Map<String, String>>> result = new HashMap<>();
//循环处理每条记录
for (int i = 0; i < list.size(); i++) {
FrnFreetradMobilityPo frnFreetradMobilityPo = list.get(i);
String project = frnFreetradMobilityPo.getProject();

//判断当前数据所在的表格是否在map中，不存在就初始化
if (!result.containsKey(project)) {
initDataMap(result, project);
}
List<Map<String, String>> dataList = result.get(project);
//如下map是每行的数据，rcyeMap对应的是日出余额行的数据，其余的同理
Map<String, String> rcyeMap = dataList.get(0);
Map<String, String> zhMap = dataList.get(1);
Map<String, String> jyqMap = dataList.get(2);
Map<String, String> dqiMap = dataList.get(3);
Map<String, String> cjieMap = dataList.get(4);
Map<String, String> hgouMap = dataList.get(5);
Map<String, String> TRSMap = dataList.get(6);
Map<String, String> zqdqMap = dataList.get(7);
Map<String, String> zqtzMap = dataList.get(8);
Map<String, String> khjckMap = dataList.get(9);
Map<String, String> bzMap = dataList.get(10);
Map<String, String> rzyeMap = dataList.get(11);

String key = "d" + frnFreetradMobilityPo.getDate();
rcyeMap.put(key, frnFreetradMobilityPo.getRcye());
zhMap.put(key, frnFreetradMobilityPo.getZh());
jyqMap.put(key, frnFreetradMobilityPo.getJyq());
dqiMap.put(key, frnFreetradMobilityPo.getDqi());
cjieMap.put(key, frnFreetradMobilityPo.getCjie());
hgouMap.put(key, frnFreetradMobilityPo.getHgou());
TRSMap.put(key, frnFreetradMobilityPo.getTrs());
zqdqMap.put(key, frnFreetradMobilityPo.getZqdq());
zqtzMap.put(key, frnFreetradMobilityPo.getZqtz());
khjckMap.put(key, frnFreetradMobilityPo.getKhjck());
bzMap.put(key, frnFreetradMobilityPo.getBz());
rzyeMap.put(key, frnFreetradMobilityPo.getRzye());

//eur的表比别的多一列，这时进行特殊处理
if (FrnConstans.EUR.equals(project)) {
Map<String, String> xxzdMap = dataList.get(12);
xxzdMap.put(key, frnFreetradMobilityPo.getXzzd());
}
result.put(list.get(i).getProject(), dataList);
}
if (result.containsKey("EUR")) {
result.get("EUR").add(1, result.get("EUR").get(12));
result.get("EUR").remove(13);
}

return result;
}

//每行数据的初始化
private void initDataMap(Map<String, List<Map<String, String>>> result, String project) {
List date = new ArrayList<>();
Map<String, String> rcyeMap = new HashMap<>();
Map<String, String> zhMap = new HashMap<>();
Map<String, String> jyqMap = new HashMap<>();
Map<String, String> dqiMap = new HashMap<>();
Map<String, String> cjieMap = new HashMap<>();
Map<String, String> hgouMap = new HashMap<>();
Map<String, String> TRSMap = new HashMap<>();
Map<String, String> zqdqMap = new HashMap<>();
Map<String, String> zqtzMap = new HashMap<>();
Map<String, String> khjckMap = new HashMap<>();
Map<String, String> bzMap = new HashMap<>();
Map<String, String> rzyeMap = new HashMap<>();

rcyeMap.put("d0", "日出余额");
zhMap.put("d0", "总行2036");
jyqMap.put("d0", "即远期");
dqiMap.put("d0", "掉期");
cjieMap.put("d0", "拆借");
hgouMap.put("d0", "回购");
TRSMap.put("d0", "TRS");
zqdqMap.put("d0", "债券到期");
zqtzMap.put("d0", "债券投资");
khjckMap.put("d0", "客户进出款（合计）");
bzMap.put("d0", "备注");
rzyeMap.put("d0", "日终余额");

date.add(rcyeMap);
date.add(zhMap);
date.add(jyqMap);
date.add(dqiMap);
date.add(cjieMap);
date.add(hgouMap);
date.add(TRSMap);
date.add(zqdqMap);
date.add(zqtzMap);
date.add(khjckMap);
date.add(bzMap);
date.add(rzyeMap);

//eur表的特殊处理
if (FrnConstans.EUR.equals(project)) {
Map<String, String> xxzdMap = new HashMap<>();
xxzdMap.put("d0", "线下账单");
date.add(xxzdMap);
}
result.put(project, date);
}

//创建日期的数据，Label类的代码在最后
private List<Label> createdLabelList(String startDate, String endDate) {
List<Label> labelList = new ArrayList<>();
LocalDate startTime = LocalDate.of(Integer.parseInt(startDate.substring(0, 4)),Integer.parseInt(startDate.substring(4, 6)), Integer.parseInt(startDate.substring(6, 8)));
LocalDate endTime = LocalDate.of(Integer.parseInt(endDate.substring(0, 4)), Integer.parseInt(endDate.substring(4, 6)), Integer.parseInt(endDate.substring(6, 8)));
long between = ChronoUnit.DAYS.between(startTime, endTime);
for (int i = 0; i <= between; i++) {
LocalDate date = startTime.plusDays(i);
Label label = new Label(date.toString().replace("-", ""), "d" + (date.toString().replace("-", "")));
labelList.add(label);
}
return labelList;
}

@Data
@AllArgsConstructor
public class Label {
private String label;
private String prop;
}

以上就是前后端所有的代码了，如果您有更好的实现方法，欢迎您分享您的方法。