JAVA行转列工具类(参考水中加点糖的博客做了改进)
目录
- 工具类代码
- 测试类
- 如何使用
- 测试结果
- 结尾
在工作中做统计时需要将sql查询结果行转列,因此上网查找方法,最终找到了大佬水中加点糖的博客,参考了他的工具类代码。
参考链接:https://blog.csdn.net/puhaiyang/article/details/105292195
由于自身业务需求是固定列有多个,因此在他的代码上做了改进,本文给出改进后的代码及使用方法。
工具类代码
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;/** * 行转列测试类 */@Controllerpublic 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为行转列对应值列的字段。
结尾
欢迎私信或评论交流,一起进步。