目录
- 工具类代码
- 测试类
- 如何使用
- 测试结果
- 结尾
在工作中做统计时需要将sql查询结果行转列，因此上网查找方法，最终找到了大佬水中加点糖的博客，参考了他的工具类代码。
参考链接：
由于自身业务需求是固定列有多个，因此在他的代码上做了改进，本文给出改进后的代码及使用方法。
工具类代码
package com.jxz.jxzmes.common.util;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.LinkedHashSet;
import java.util.List;
import java.util.Set;
/**
* 行转列工具类
*/
public class RowConvertColUtil {
private static Set<Object> headerSet;
private static Set<Object> fixedColumnSet;
private RowConvertColUtil() {
}
public static class ConvertData {
private Set<Object> headerSet;
private Set<Object> fixedColumnSet;
private List<List<Object>> dataList;
public ConvertData(List<List<Object>> dataList, Set<Object> headerSet, Set<Object> fixedColumnSet) {
this.headerSet = headerSet;
this.fixedColumnSet = fixedColumnSet;
this.dataList = dataList;
}
public Set<Object> getHeaderSet() {
return headerSet;
}
public void setHeaderSet(Set<Object> headerSet) {
this.headerSet = headerSet;
}
public Set<Object> getfixedColumnSet() {
return fixedColumnSet;
}
public void setfixedColumnSet(Set<Object> fixedColumnSet) {
this.fixedColumnSet = fixedColumnSet;
}
public List<List<Object>> getDataList() {
return dataList;
}
public void setDataList(List<List<Object>> dataList) {
this.dataList = dataList;
}
}
/**
* 行转列,返回ConvertData
*
* @param orignalList 要行转列的List
* @param headerName 要行转列的字段
* @param fixedColumn 固定列字段
* @param valueFiedName 行转列字段对应值列的字段名
* @param needHeader 是否返回表头
* @param fixedColumnName 固定列字段名称数组
* @param nullValue 定义空值补数
* @return ConvertData
*/
public static synchronized ConvertData doConvertReturnObj(List orignalList, String headerName, String[] fixedColumn
, String valueFiedName, boolean needHeader,String[] fixedColumnName,String nullValue) throws Exception {
List<List<Object>> lists = doConvert(orignalList, headerName, fixedColumn, valueFiedName, needHeader,fixedColumnName,nullValue);
return new ConvertData(lists, headerSet, fixedColumnSet);
}
/**
* 行转列,返回转换后的list
*
* @param orignalList 要行转列的List
* @param headerName 要行转列的字段
* @param fixedColumn 固定列字段
* @param valueFiedName 行转列字段对应值列的字段名
* @param needHeader 是否返回表头
* @param fixedColumnName 固定列字段名称数组
* @param nullValue 定义空值补数
*/
public static synchronized List<List<Object>> doConvert(List orignalList, String headerName, String[] fixedColumn
, String valueFiedName, boolean needHeader,String[] fixedColumnName,String nullValue) throws Exception {
headerSet = new LinkedHashSet<>();
fixedColumnSet = new LinkedHashSet<>();
//resultList:首行名称list
List<List<Object>> resultList = new ArrayList<>();
getHeaderfixedColumnSet(orignalList, headerName, fixedColumn);
if (needHeader) {
List<Object> headerList = new ArrayList<>();
//填充进header
for(String value:fixedColumnName) {
headerList.add(value);
}
headerList.addAll(headerSet);
resultList.add(headerList);
}
for (Object fixedColumnItem : fixedColumnSet) {
//colList：数据list
List<Object> colList = new ArrayList<>();
//名称
for(String ColNameItem:fixedColumnItem.toString().split("\\|")) {
colList.add(ColNameItem);
}
for (Object headerItem : headerSet) {
boolean flag = true;
for (Object orignalObjectItem : orignalList) {
Field fixedColumnField = null;
Field headerField = orignalObjectItem.getClass().getDeclaredField(headerName);
headerField.setAccessible(true);
fixedColumnField = orignalObjectItem.getClass().getDeclaredField(fixedColumn[0]);
fixedColumnField.setAccessible(true);
Field valueField = orignalObjectItem.getClass().getDeclaredField(valueFiedName);
valueField.setAccessible(true);
if (headerItem.equals(headerField.get(orignalObjectItem))) {
if (fixedColumnItem.toString().split("\\|")[0].equals(fixedColumnField.get(orignalObjectItem))) {
colList.add(valueField.get(orignalObjectItem));
flag = false;
break;
}
}
}
if (flag) {
colList.add(nullValue);
}
}
resultList.add(colList);
}
return resultList;
}
private static void getHeaderfixedColumnSet(List orignalList, String headerName, String[] fixedColumn) {
try {
for (Object item : orignalList) {
Field headerField = item.getClass().getDeclaredField(headerName);
headerField.setAccessible(true);
headerSet.add(headerField.get(item));
StringBuffer sBuffer = new StringBuffer();
int len = 1;
for(String name:fixedColumn) {
Field fixedColumnField = item.getClass().getDeclaredField(name);
fixedColumnField.setAccessible(true);
sBuffer.append(fixedColumnField.get(item));
if(len<fixedColumn.length) {
sBuffer.append("|");
}
len++;
}
fixedColumnSet.add(sBuffer.toString());
}
} catch (NoSuchFieldException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
}
}
}测试类
package com.jxz.jxzmes.common.util;
import java.util.*;
import org.springframework.stereotype.Controller;
import com.alibaba.fastjson.JSON;
import com.jxz.jxzmes.common.util.RowConvertColUtil.ConvertData;
/**
* 行转列测试类
*/
@Controller
public class RowConvertCol {
private static class ScoreInfo {
private String stuName;
private String sex;
private Integer age;
private String subjectName;
private Integer value;
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public String getStuName() {
return stuName;
}
public String getSubjectName() {
return subjectName;
}
public Integer getValue() {
return value;
}
public ScoreInfo(String stuName, String sex, Integer age, String subjectName, Integer value) {
this.stuName = stuName;
this.sex = sex;
this.age = age;
this.subjectName = subjectName;
this.value = value;
}
@Override
public String toString() {
return "ScoreInfo{" +
"stuName='" + stuName + '\'' +
", sex='" + sex + '\'' +
", age='" + age + '\'' +
", subjectName='" + subjectName + '\'' +
", value=" + value +
'}';
}
}
public static void main(String[] args) throws Exception {
func2();
//testDTO();
}
public static void testDTO(){
}
private static void func2() throws Exception {
List<ScoreInfo> scoreInfoList = new ArrayList<>();
scoreInfoList.add(new ScoreInfo("张三", "男",15, "语文", 61));
scoreInfoList.add(new ScoreInfo("张三", "男",15, "数学", 78));
scoreInfoList.add(new ScoreInfo("张三", "男",15, "英语", 93));
scoreInfoList.add(new ScoreInfo("李四", "男",14, "语文", 70));
scoreInfoList.add(new ScoreInfo("李四", "男",14, "数学", 86));
scoreInfoList.add(new ScoreInfo("李四", "男",14, "英语", 72));
scoreInfoList.add(new ScoreInfo("王五", "男",15, "语文", 66));
scoreInfoList.add(new ScoreInfo("赵六", "男",16, "语文", 91));
scoreInfoList.add(new ScoreInfo("王五", "男",15, "数学", 88));
scoreInfoList.add(new ScoreInfo("赵六", "男",16, "数学", 63));
scoreInfoList.add(new ScoreInfo("王五", "男",15, "英语", 93));
scoreInfoList.add(new ScoreInfo("赵六", "男",16, "英语", 58));
scoreInfoList.add(new ScoreInfo("王七", "女",15, "英语", 65));
scoreInfoList.add(new ScoreInfo("王七", "女",15, "数学", 91));
for (ScoreInfo scoreInfo : scoreInfoList) {
System.out.println(scoreInfo.toString());
}
System.out.println("-------------------");
//doConvertReturnObj(要行转列的List,要行转列的字段,固定列字段数组,行转列对应值列的字段,是否返回表头,固定列字段名称数组,定义空值补数)
String[] fixedColumn = {"stuName","sex","age"};
String[] fixedColumnName = {"姓名","性别","年龄"};
ConvertData lists = RowConvertColUtil.doConvertReturnObj(scoreInfoList, "subjectName", fixedColumn, "value", true,fixedColumnName,"0");
for (List<Object> list : lists.getDataList()) {
System.out.println(list.toString());
}
List<TestDTO> dtoList = new ArrayList<>();
TestDTO dto=null;
System.out.println("lists.getDataList().size():"+lists.getDataList().size());
System.out.println("-------------------");
System.out.println(lists.getfixedColumnSet());
System.out.println("lists.getFirstColSet().size():"+lists.getfixedColumnSet().size());
System.out.println(lists.getHeaderSet());
System.out.println("lists.getHeaderSet().size():"+lists.getHeaderSet().size());
System.out.println("-------------------");
}
}如何使用
根据自身需要，调用工具类中的doConvertReturnObj或doConvert方法。
方法传参如下：
(要行转列的List,要行转列的字段,固定列字段数组,行转列对应值列的字段,是否返回表头,固定列字段名称数组,定义空值补数)
测试结果
转换前：
转换后：
其中：
stuName，sex，age为固定列。
subjectName为需要行转列的字段。
value为行转列对应值列的字段。
结尾
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
