一、前言
oracle和mysql的group by 分组功能大家应该清楚，那如何使用java实现同样的功能呢
比如下面这个表
我们需要按id分组，求最大age和math累计成绩
我们的sql应该这样写
select id,max(age),sum(math) from student group by id;
得到的数据是这样的
那java应该怎么样实现呢？
二、java实现过程
group by分组实现
public static Map<String, Object> groupBy(Map<String, Object> map, String[] groupFuns) { // List list = new ArrayList(groupFuns.length); if (groupFuns.length<1){ return map; } Map<String, Object> result = new HashMap<>(); for (int i = 0, length = groupFuns.length; i < length; i++) { String field = groupFuns[i]; if (!map.containsKey(field)) { System.out.println("map中未有【" + field + "】字段"); //log.error("map中未有【{}】字段",field); continue; } Object value = map.get(field); if (value == null || StringUtils.isBlank(value.toString())) { result.put(field, null); } else { result.put(field, value); } } return result; }
调用分组函数
public static List<Map<String, Object>> partitionGroupBy(List<Map<String, Object>> dataList, String[] groupFuns, String[][] combiFuns) { Map<Map<String, Object>, List<Map<String, Object>>> groupByResultMap = dataList.stream().collect(Collectors.groupingBy(o -> groupBy(o, groupFuns))); if (MapUtils.isEmpty(groupByResultMap)){ return new ArrayList<>(); } List<Map<String, Object>> result = new ArrayList<>(groupByResultMap.size()); for (Map.Entry<Map<String, Object>, List<Map<String, Object>>> entry : groupByResultMap.entrySet()) { Map<String, Object> key = entry.getKey(); List<Map<String, Object>> valueList = entry.getValue(); // {{"max","advancedate"},{"max","vc_code"},{"sum","cashdivirmb"}}; Map<String, Object> resultMap = new HashMap<>(16); for (int i = 0, length = combiFuns.length; i < length; i++) { String[] combiFun = combiFuns[i]; // 组合函数名称 String combiFunName = combiFun[0]; // 字段名称 String combiFunField = combiFun[1]; // 别名 String otherName = StringUtils.isBlank(combiFun[2]) ? combiFunField : combiFun[2]; switch (combiFunName.toLowerCase()) { case "null": resultMap.put(otherName, null); break; case "field": // 原始字段值，从分组的key里面取值 resultMap.put(otherName, key.get(combiFunField)); break; case "spel": resultMap.putAll(combiFunSpel(resultMap, combiFunField, otherName,combiFun[3])); break; case "max": resultMap.putAll(combiFunMax(valueList, combiFunField, otherName)); break; case "min": resultMap.putAll(combiFunMin(valueList, combiFunField, otherName)); break; case "sum": resultMap.putAll(combiFunSum(valueList, combiFunField,otherName)); break; default: System.out.println("不存在组合函数【" + combiFunName + "," + combiFunField + "】，请自己实现或者更改成已经有的组合函数"); //log.error("不存在组合函数【{},{}】，请自己实现或者更改成已经有的组合函数",combiFunName,combiFunField); } } // List<Map<String, Object>> combiFunFilterList = new ArrayList<>(); // combiFunFilterList.add(resultMap); result.add(resultMap); } return result; }
实现max，sum，min，spel表达式等自定义函数
private static Map<String, Object> combiFunMax(List<Map<String, Object>> list, String combiFunField, String otherName) { BigDecimal maxValue = null; for (Map<String, Object> map : list) { if (!map.containsKey(combiFunField)) { System.out.println("map中未有【" + combiFunField + "】字段"); //log.error("map中未有【{}】字段", combiFunField); continue; } Object tempValueObj = map.get(combiFunField); if (tempValueObj == null || StringUtils.isBlank(tempValueObj.toString())) { continue; } BigDecimal tempValue = ComUtils.nvl(map.get(combiFunField), BigDecimal.ZERO); if (maxValue == null || tempValue.compareTo(maxValue) > 0) { maxValue = tempValue; } } Map<String, Object> resultMap = new HashMap<>(); resultMap.put(otherName, maxValue); return resultMap; } private static Map<String, Object> combiFunMin(List<Map<String, Object>> list, String combiFunField, String otherName) { BigDecimal minValue = null; for (Map<String, Object> map : list) { if (!map.containsKey(combiFunField)) { System.out.println("map中未有【" + combiFunField + "】字段"); //log.error("map中未有【{}】字段", combiFunField); continue; } Object tempValueObj = map.get(combiFunField); if (tempValueObj == null || StringUtils.isBlank(tempValueObj.toString())) { continue; } BigDecimal tempValue = ComUtils.nvl(map.get(combiFunField), BigDecimal.ZERO); if (minValue == null || tempValue.compareTo(minValue) < 0) { minValue = tempValue; } } Map<String, Object> resultMap = new HashMap<>(); resultMap.put(otherName, minValue); return resultMap; } private static SpelCommonUtil spelCommonUtil=new SpelCommonUtil(); private static Map<String, Object> combiFunSpel(Map<String,Object> map, String combiFunField, String otherName,String spelFun) { // spel ExpressionParser parser = new SpelExpressionParser(); Expression exp = parser.parseExpression(spelFun); StandardEvaluationContext context = new StandardEvaluationContext(); context.setRootObject(spelCommonUtil); Map<String, Object> resultMap = new HashMap<>(); context.setVariable("a",map); Object value = exp.getValue(context); resultMap.put(otherName,value); return resultMap; } private static Map<String, Object> combiFunSum(List<Map<String, Object>> list, String combiFunField,String otherName) { BigDecimal sumValue = null; for (Map<String, Object> map : list) { if (!map.containsKey(combiFunField)) { System.out.println("map中未有【" + combiFunField + "】字段"); //log.error("map中未有【{}】字段", combiFunField); continue; } Object tempValueObj = map.get(combiFunField); if (tempValueObj == null || StringUtils.isBlank(tempValueObj.toString())) { continue; } BigDecimal tempValue = ComUtils.nvl(map.get(combiFunField), BigDecimal.ZERO); sumValue = tempValue.add(sumValue == null ? BigDecimal.ZERO : sumValue); } Map<String, Object> resultMap = new HashMap<>(); resultMap.put(otherName, sumValue); return resultMap; }
测试类调用
public static void main(String[] args) { Map<String, Object> map1 = new HashMap<String, Object>() {{ put("id", 10); put("name", "map1"); put("age", "20"); put("math", "92.5"); }}; Map<String, Object> map2 = new HashMap<String, Object>() {{ put("id", 103); put("name", "map2"); put("age", ""); put("math", "84"); }}; Map<String, Object> map3 = new HashMap<String, Object>() {{ put("id", 102); put("name", "map3"); put("age", "5"); put("math", "20"); }}; Map<String, Object> map4 = new HashMap<String, Object>() {{ put("id", 102); put("name", "map4"); put("age", "6"); put("math", "1"); }}; Map<String, Object> map5 = new HashMap<String, Object>() {{ put("id", 103); put("name", "map5"); put("age", null); put("math", "63"); }}; Map<String, Object> map6 = new HashMap<String, Object>() {{ put("id", 103); put("name", "map6"); put("age", 5); put("math", ""); }}; Map<String, Object> map7 = new HashMap<String, Object>() {{ put("id", 10); put("name", "map7"); put("age", "20"); put("math", ""); }}; List<Map<String, Object>> list = new ArrayList<Map<String, Object>>() {{ add(map1); add(map2); add(map3); add(map4); add(map5); add(map6); add(map7); }}; String[] groupFuns = {"id"}; //String[] groupFuns = {"id","age"}; // 组合函数，取的字段，别名(如果为空就以取的字段为key)，特殊处理函数 //String[][] combiFuns = {{"min", "age", "", ""}, {"field", "id", "", ""}, {"sum", "math", "", ""},{"spel", "age", "spel", "decode(#a.get(\"age\"),5,\"优秀\",\"不优秀\")"}}; // String[][] combiFuns = {{"min", "age", "", ""}, {"field", "id", "", ""}, {"sum", "math", "", ""}}; String[][] combiFuns = {{"max", "age", "", ""},{"max", "math", "", ""}}; List<Map<String, Object>> maps = partitionGroupBy(list, groupFuns, combiFuns); System.out.println(maps); System.out.println(JSON.toJSONString(maps)); }
执行测试结果
发现和数据库执行的一致，符合要求
复杂情况casewhen函数
那要是还是按id，age分组，sum(math)>60为及格，否则为不及格应该怎么写呢？
sql语句应该为
select id,max(age),sum(math),case when sum(math)>60 then '及格' else '不及格' end as pingjia from student group by id,age;
执行结果
java应该怎么实现呢，这里就需要用到spel表达式，不了解的朋友们可以去找一下资料，这里就不赘述了
这里就得加spel常用的一些表达式，具体请看文末的，spelCommonUtil.class
java测试
分组和函数配置如下
public static void main(String[] args) { Map<String, Object> map1 = new HashMap<String, Object>() {{ put("id", 10); put("name", "map1"); put("age", "20"); put("math", "92.5"); }}; Map<String, Object> map2 = new HashMap<String, Object>() {{ put("id", 103); put("name", "map2"); put("age", ""); put("math", "84"); }}; Map<String, Object> map3 = new HashMap<String, Object>() {{ put("id", 102); put("name", "map3"); put("age", "5"); put("math", "20"); }}; Map<String, Object> map4 = new HashMap<String, Object>() {{ put("id", 102); put("name", "map4"); put("age", "6"); put("math", "1"); }}; Map<String, Object> map5 = new HashMap<String, Object>() {{ put("id", 103); put("name", "map5"); put("age", null); put("math", "63"); }}; Map<String, Object> map6 = new HashMap<String, Object>() {{ put("id", 103); put("name", "map6"); put("age", 5); put("math", ""); }}; Map<String, Object> map7 = new HashMap<String, Object>() {{ put("id", 10); put("name", "map7"); put("age", "20"); put("math", ""); }}; List<Map<String, Object>> list = new ArrayList<Map<String, Object>>() {{ add(map1); add(map2); add(map3); add(map4); add(map5); add(map6); add(map7); }}; //String[] groupFuns = {"id"}; String[] groupFuns = {"id","age"}; // 组合函数，取的字段，别名(如果为空就以取的字段为key)，特殊处理函数 //String[][] combiFuns = {{"min", "age", "", ""}, {"field", "id", "", ""}, {"sum", "math", "", ""},{"spel", "age", "spel", "decode(#a.get(\"age\"),5,\"优秀\",\"不优秀\")"}}; // String[][] combiFuns = {{"min", "age", "", ""}, {"field", "id", "", ""}, {"sum", "math", "", ""}}; //String[][] combiFuns = {{"field", "id", "", ""},{"max", "age", "", ""},{"max", "math", "", ""}}; //String[][] combiFuns = {{"field", "id", "", ""},{"max", "age", "", ""},{"sum", "math", "", ""}}; String[][] combiFuns = {{"field", "id", "", ""},{"max", "age", "", ""},{"sum", "math", "", ""},{"spel","math","pingjia","caseWhen(\"不及格\",#a.get(\"math\")>60,\"及格\")"}}; List<Map<String, Object>> maps = partitionGroupBy(list, groupFuns, combiFuns); System.out.println(maps); System.out.println(JSON.toJSONString(maps)); }
执行结果
结果和sql执行一致，符合要求，
优化代码
综上所述，代码符合要求，但是有一种情况该方法就无法实现，比如我们需要求按id和age分组，同时满足max(age)>5 和 sum(math)>60 的，上述代码就无法实现，因为这里出现了分组函数和case when函数同时出现的情况
我们将在另外的代码中优化，具体请看下期
三、完整代码
import com.alibaba.fastjson.JSON; import org.apache.commons.collections.MapUtils; import org.apache.commons.lang3.StringUtils; import org.springframework.expression.Expression; import org.springframework.expression.ExpressionParser; import org.springframework.expression.spel.standard.SpelExpressionParser; import org.springframework.expression.spel.support.StandardEvaluationContext; import java.math.BigDecimal; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import java.util.stream.Collectors; public class Test14 { public static void main(String[] args) { Map<String, Object> map1 = new HashMap<String, Object>() {{ put("id", 10); put("name", "map1"); put("age", "20"); put("math", "92.5"); }}; Map<String, Object> map2 = new HashMap<String, Object>() {{ put("id", 103); put("name", "map2"); put("age", ""); put("math", "84"); }}; Map<String, Object> map3 = new HashMap<String, Object>() {{ put("id", 102); put("name", "map3"); put("age", "5"); put("math", "20"); }}; Map<String, Object> map4 = new HashMap<String, Object>() {{ put("id", 102); put("name", "map4"); put("age", "6"); put("math", "1"); }}; Map<String, Object> map5 = new HashMap<String, Object>() {{ put("id", 103); put("name", "map5"); put("age", null); put("math", "63"); }}; Map<String, Object> map6 = new HashMap<String, Object>() {{ put("id", 103); put("name", "map6"); put("age", 5); put("math", ""); }}; Map<String, Object> map7 = new HashMap<String, Object>() {{ put("id", 10); put("name", "map7"); put("age", "20"); put("math", ""); }}; List<Map<String, Object>> list = new ArrayList<Map<String, Object>>() {{ add(map1); add(map2); add(map3); add(map4); add(map5); add(map6); add(map7); }}; //String[] groupFuns = {"id"}; String[] groupFuns = {"id","age"}; // 组合函数，取的字段，别名(如果为空就以取的字段为key)，特殊处理函数 //String[][] combiFuns = {{"min", "age", "", ""}, {"field", "id", "", ""}, {"sum", "math", "", ""},{"spel", "age", "spel", "decode(#a.get(\"age\"),5,\"优秀\",\"不优秀\")"}}; // String[][] combiFuns = {{"min", "age", "", ""}, {"field", "id", "", ""}, {"sum", "math", "", ""}}; //String[][] combiFuns = {{"field", "id", "", ""},{"max", "age", "", ""},{"max", "math", "", ""}}; //String[][] combiFuns = {{"field", "id", "", ""},{"max", "age", "", ""},{"sum", "math", "", ""}}; String[][] combiFuns = {{"field", "id", "", ""},{"max", "age", "", ""},{"sum", "math", "", ""},{"spel","math","pingjia","caseWhen(\"不及格\",#a.get(\"math\")>60,\"及格\")"}}; List<Map<String, Object>> maps = partitionGroupBy(list, groupFuns, combiFuns); System.out.println(maps); System.out.println(JSON.toJSONString(maps)); } public static List<Map<String, Object>> partitionGroupBy(List<Map<String, Object>> dataList, String[] groupFuns, String[][] combiFuns) { Map<Map<String, Object>, List<Map<String, Object>>> groupByResultMap = dataList.stream().collect(Collectors.groupingBy(o -> groupBy(o, groupFuns))); if (MapUtils.isEmpty(groupByResultMap)){ return new ArrayList<>(); } List<Map<String, Object>> result = new ArrayList<>(groupByResultMap.size()); for (Map.Entry<Map<String, Object>, List<Map<String, Object>>> entry : groupByResultMap.entrySet()) { Map<String, Object> key = entry.getKey(); List<Map<String, Object>> valueList = entry.getValue(); // {{"max","advancedate"},{"max","vc_code"},{"sum","cashdivirmb"}}; Map<String, Object> resultMap = new HashMap<>(16); for (int i = 0, length = combiFuns.length; i < length; i++) { String[] combiFun = combiFuns[i]; // 组合函数名称 String combiFunName = combiFun[0]; // 字段名称 String combiFunField = combiFun[1]; // 别名 String otherName = StringUtils.isBlank(combiFun[2]) ? combiFunField : combiFun[2]; switch (combiFunName.toLowerCase()) { case "null": resultMap.put(otherName, null); break; case "field": // 原始字段值，从分组的key里面取值 resultMap.put(otherName, key.get(combiFunField)); break; case "spel": resultMap.putAll(combiFunSpel(resultMap, combiFunField, otherName,combiFun[3])); break; case "max": resultMap.putAll(combiFunMax(valueList, combiFunField, otherName)); break; case "min": resultMap.putAll(combiFunMin(valueList, combiFunField, otherName)); break; case "sum": resultMap.putAll(combiFunSum(valueList, combiFunField,otherName)); break; default: System.out.println("不存在组合函数【" + combiFunName + "," + combiFunField + "】，请自己实现或者更改成已经有的组合函数"); //log.error("不存在组合函数【{},{}】，请自己实现或者更改成已经有的组合函数",combiFunName,combiFunField); } } // List<Map<String, Object>> combiFunFilterList = new ArrayList<>(); // combiFunFilterList.add(resultMap); result.add(resultMap); } return result; } public static Map<String, Object> groupBy(Map<String, Object> map, String[] groupFuns) { // List list = new ArrayList(groupFuns.length); if (groupFuns.length<1){ return map; } Map<String, Object> result = new HashMap<>(); for (int i = 0, length = groupFuns.length; i < length; i++) { String field = groupFuns[i]; if (!map.containsKey(field)) { System.out.println("map中未有【" + field + "】字段"); //log.error("map中未有【{}】字段",field); continue; } Object value = map.get(field); if (value == null || StringUtils.isBlank(value.toString())) { result.put(field, null); } else { result.put(field, value); } } return result; } private static Map<String, Object> combiFunMax(List<Map<String, Object>> list, String combiFunField, String otherName) { BigDecimal maxValue = null; for (Map<String, Object> map : list) { if (!map.containsKey(combiFunField)) { System.out.println("map中未有【" + combiFunField + "】字段"); //log.error("map中未有【{}】字段", combiFunField); continue; } Object tempValueObj = map.get(combiFunField); if (tempValueObj == null || StringUtils.isBlank(tempValueObj.toString())) { continue; } BigDecimal tempValue = ComUtils.nvl(map.get(combiFunField), BigDecimal.ZERO); if (maxValue == null || tempValue.compareTo(maxValue) > 0) { maxValue = tempValue; } } Map<String, Object> resultMap = new HashMap<>(); resultMap.put(otherName, maxValue); return resultMap; } private static Map<String, Object> combiFunMin(List<Map<String, Object>> list, String combiFunField, String otherName) { BigDecimal minValue = null; for (Map<String, Object> map : list) { if (!map.containsKey(combiFunField)) { System.out.println("map中未有【" + combiFunField + "】字段"); //log.error("map中未有【{}】字段", combiFunField); continue; } Object tempValueObj = map.get(combiFunField); if (tempValueObj == null || StringUtils.isBlank(tempValueObj.toString())) { continue; } BigDecimal tempValue = ComUtils.nvl(map.get(combiFunField), BigDecimal.ZERO); if (minValue == null || tempValue.compareTo(minValue) < 0) { minValue = tempValue; } } Map<String, Object> resultMap = new HashMap<>(); resultMap.put(otherName, minValue); return resultMap; } private static SpelCommonUtil spelCommonUtil=new SpelCommonUtil(); private static Map<String, Object> combiFunSpel(Map<String,Object> map, String combiFunField, String otherName,String spelFun) { // spel ExpressionParser parser = new SpelExpressionParser(); Expression exp = parser.parseExpression(spelFun); StandardEvaluationContext context = new StandardEvaluationContext(); context.setRootObject(spelCommonUtil); Map<String, Object> resultMap = new HashMap<>(); context.setVariable("a",map); Object value = exp.getValue(context); resultMap.put(otherName,value); return resultMap; } private static Map<String, Object> combiFunSum(List<Map<String, Object>> list, String combiFunField,String otherName) { BigDecimal sumValue = null; for (Map<String, Object> map : list) { if (!map.containsKey(combiFunField)) { System.out.println("map中未有【" + combiFunField + "】字段"); //log.error("map中未有【{}】字段", combiFunField); continue; } Object tempValueObj = map.get(combiFunField); if (tempValueObj == null || StringUtils.isBlank(tempValueObj.toString())) { continue; } BigDecimal tempValue = ComUtils.nvl(map.get(combiFunField), BigDecimal.ZERO); sumValue = tempValue.add(sumValue == null ? BigDecimal.ZERO : sumValue); } Map<String, Object> resultMap = new HashMap<>(); resultMap.put(otherName, sumValue); return resultMap; } }
ComUtils.class
import org.apache.commons.collections.CollectionUtils; import org.apache.commons.collections.MapUtils; import org.apache.commons.lang3.StringUtils; import org.springframework.expression.Expression; import org.springframework.expression.ExpressionParser; import org.springframework.expression.spel.standard.SpelExpressionParser; import org.springframework.expression.spel.support.StandardEvaluationContext; import java.math.BigDecimal; import java.text.SimpleDateFormat; import java.util.*; import java.util.stream.Collectors; public class ComUtils { /** * SpelCommonUtil */ private static SpelCommonUtil spelCommonUtil = new SpelCommonUtil(); /** * nvl转换函数 * * @param obj1 * @param obj2 * @return */ public static BigDecimal nvl(Object obj1, BigDecimal obj2) { if (obj1 == null) { return obj2; } if (StringUtils.isBlank(obj1.toString())) { return obj2; } return new BigDecimal(obj1.toString()); } /** * nvl转换函数 * * @param obj1 * @param obj2 * @return */ public static String nvl(Object obj1, String obj2) { if (obj1 == null) { return obj2; } if (StringUtils.isBlank(obj1.toString())) { return obj2; } return obj1.toString(); } }
SpelCommonUtil.class
package com.zygxsq.test.util; import org.apache.commons.lang3.ObjectUtils; import org.apache.commons.lang3.StringUtils; import java.math.BigDecimal; import java.text.DateFormat; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Arrays; import java.util.Date; import java.util.List; import java.util.UUID; public class SpelCommonUtil { private static ThreadLocal<DateFormat> threadLocal = new ThreadLocal<DateFormat>() { protected DateFormat initialValue() { return new SimpleDateFormat("yyyyMMdd"); } }; /** * 日期格式转换为Integer * @date 2022-10-21 17:06 * @param date * @return java.lang.Integer **/ public static Integer dateToInt(Date date){ if (null==date){ return null; } return Integer.valueOf( threadLocal.get().format(date)); } /** * 相除 * 修正原因：保留精度 默认保留小数位 10位 * 入参格式：division(#a.get('xxx'),100,4)-》表示除以100 保留4位 * 注意点： * 精度具体值 来源于具体表的具体字段的数据定义 * @param numerator * @param denominator * @return */ public static BigDecimal division(Object numerator , Object... values){ if (null ==numerator){ return null; } if (null==values || values.length<=0){ return null; } Object denominator = values[0]; if (denominator == null){ return null; } BigDecimal numeratorDouble=new BigDecimal(numerator.toString()); BigDecimal denominatorDouble=new BigDecimal(denominator.toString()); if (denominatorDouble.doubleValue()==0){ return null; } // 默认保留 10位 int scale = 10; // 传入精度时 替换 if (values.length>1){ scale = values[1] != null? Integer.valueOf(values[1].toString()) :scale; } return numeratorDouble.divide(denominatorDouble,scale,BigDecimal.ROUND_HALF_UP); } /** * 相乘 * 修改原因：精度的问题 * @param numerator * @param denominator * @return */ public static BigDecimal multiply(Object numerator , Object denominator){ if (null ==numerator||null==denominator){ return null; } BigDecimal numeratorBigDecimal=new BigDecimal(numerator.toString()); BigDecimal denominatorBigDecimal=new BigDecimal(denominator.toString()); return numeratorBigDecimal.multiply(denominatorBigDecimal); } /** * 相减 * @param num1 被减数 * @param num2 减数 * @return */ public static BigDecimal subtract(Object num1, Object num2){ if (null == num1 || null == num2){ return null; } BigDecimal bigDecimal1 = new BigDecimal(num1.toString()); BigDecimal bigDecimal2 = new BigDecimal(num2.toString()); return bigDecimal1.subtract(bigDecimal2); } /** * 获取当前日期 * * @date 2022-10-21 17:10 * @return java.util.Date **/ public static Date now(){ return new Date(); } /** * 三目运算 * @param obj1 * @param obj2 * @return */ public static Object nvl(Object obj1 , Object obj2){ return null == obj1?obj2:obj1; } /** * 三目运算 * @param obj1 * @param obj2 * @return */ public static Object nvl2(Object obj1 , Object obj2, Object obj3){ return null == obj1?obj3:obj2; } /** * 字符串截取，对应Oracle substr 例如：substr(#t.get("dm"), 1, 4) * @param obj * @param index * @return */ public static String substr(Object obj, Integer... index) { StringBuilder result = new StringBuilder(); if (null == obj) { return result.toString(); } String str = obj.toString(); // 针对 从后面截取 if (index.length == 1) { // 如截取 -2 if (index[0] < 0 && Math.abs(index[0]) <= str.length()) { return str.substring(str.length()+ index[0]); } // 超过 返回空字符串 if (index[0] < 0 && Math.abs(index[0]) > str.length()) { return ""; } } if (index.length==2){ if (index[0] == 0 || index[0] == 1){ if (index[0]+index[1]-1>str.length()){ result.append(str.substring(0,str.length())); }else{ result.append(str.substring(0,index[0]+index[1]-1)); } }else{ if (index[0]+index[1]-1>str.length()){ result.append(str.substring(index[0]-1,str.length())); }else{ result.append(str.substring(index[0]-1,index[0]+index[1]-1)); } } }else if (index.length==1){ if (index[0] == 0 || index[0] == 1){ result.append(str.substring(0)); }else{ result.append(str.substring(index[0]-1)); } }else{ result.append(str); } return result.toString(); } /** * 字符串拼接 对应Oracle || 例如：append("2",#a.get("id")) * @param strs * @return */ public static String append(Object... strs){ StringBuilder result = new StringBuilder(); for (int i=0;i<strs.length;i++){ if (null != strs[i]){ result.append(strs[i]); } } return result.toString(); } /** * 条件判断 例：caseWhen(默认值,条件1,值1,条件2,值2) 例如：caseWhen(0,in(#a.get("companynature"),"(172,420)"),1) * @param defaultValue 默认值 * @param conditions 条件 * @return */ public static Object caseWhen(Object defaultValue, Object... conditions){ Object result = defaultValue; for (int i=0;i<conditions.length;){ if ("true".equals(conditions[i].toString())){ result = conditions[i+1]; break; } i=i+2; } return result; } /** * oracle 的decode函数，不支持value为null，有为null请使用caseWhen() * 例：decode(#a.get("companynature"),1,1,19,2,20,3,null) * @param value * @param conditions * @return */ public static Object decode(Object value, Object... conditions) { if (conditions == null) { throw new BatchException("decode值不能为空"); } int three = 3; int one = 1; if (conditions.length < three || (conditions.length & one) == 0) { throw new BatchException("decode值不符合要求"); } Object result = conditions[conditions.length - 1]; if (value == null) { return result; } for (int i = 0; i < conditions.length; ) { if (conditions[i] == null) { throw new BatchException("decode中的比较值不能为null"); } if (value != null && conditions[i] != null) { if (StringUtils.equals(value.toString(), conditions[i].toString())) { result = conditions[i + 1]; break; } } else if (value == null && conditions[i] == null) { result = conditions[i + 1]; break; } i = i + 2; } return result; } /** * * @param obj in(#a.get("companynature"),"172,420")) * @param condition * @return */ public static boolean in(Object obj, String condition){ if (StringUtils.isBlank(condition) || obj == null){ return false; } String[] values = condition.split(","); List<String> valueList = new ArrayList(Arrays.asList(values)); return valueList.contains(obj); } /** 对应数值类型 * @param obj in(#a.get("l_market"),1,2)) * @param condition * @return */ public static boolean in(Object obj, Integer... condition) { if (condition.length == 0 || obj == null) { return false; } if (obj instanceof BigDecimal || obj instanceof Double) { obj = ((Number) obj).intValue(); } List<Integer> valueList = new ArrayList(Arrays.asList(condition)); return valueList.contains(obj); } /** * 不在 -> true * @param obj inNot(#a.get("companynature"),"172,420")) * @param condition * @return */ public static boolean inNot(Object obj, String condition){ if (StringUtils.isBlank(condition) || obj == null){ return false; } String[] values = condition.split(","); List<String> valueList = new ArrayList(Arrays.asList(values)); return !valueList.contains(obj); } /** * 对应数据库左like，例如：%11 * @param obj * @param condition * @return */ public static boolean likeOnLeft(Object obj,String condition){ if (null == obj){ return false; } return obj.toString().endsWith(condition); } /** * 对应数据库右like，例如：11% likeOnRight(#a.get("chiname"),"中华人民共和国") * @param obj * @param condition * @return */ public static boolean likeOnRight(Object obj,String condition){ if (null == obj){ return false; } return obj.toString().startsWith(condition); } /** * 对应数据库like，例如：11%22 * @param obj * @param condition * @return */ public static boolean likeOnCenter(Object obj,String condition){ if (null == obj){ return false; } return obj.toString().contains(condition); } /** * 对应数据库to_char,当需要转换的类型为日期格式，可以传第二个参数，例如"yyyyMMdd"，默认"yyyyMMdd" * @param obj * @return */ public static String toString(Object... obj){ String defaultFormat = "yyyyMMdd"; if (obj.length == 1){ if (null == obj[0]){ return null; }else{ return obj[0].toString(); } }else if (obj.length == 2){ if (null == obj[0]) { return null; } if (obj[0] instanceof Date){ if (ObjectUtils.isNotEmpty(obj[1])){ SimpleDateFormat sdf; // 容错 避免因为大小写的问题 if (defaultFormat.equalsIgnoreCase(obj[1].toString())) { sdf = new SimpleDateFormat(defaultFormat); } else { sdf = new SimpleDateFormat(obj[1].toString()); } return sdf.format(obj[0]); } else { SimpleDateFormat sdf = new SimpleDateFormat(defaultFormat); return sdf.format(obj[0]); } }else{ return obj[0].toString(); } }else{ return null; } } /** * 对应数据库to_char,当需要转换的类型为日期格式，可以传第二个参数，例如"yyyyMMdd"，默认"yyyyMMdd" * @param obj * @return */ public static String toChar(Object... obj){ return toString(obj); } /** * 对应数据库to_number * @param obj * @return */ public static Integer toNumber(Object obj){ if (ObjectUtils.isEmpty(obj)){ return null; } if (obj instanceof String){ return Integer.valueOf(obj.toString()); } return null; } /** * 系统参数校验 * * @date 2022-12-01 15:26 * @param paramKey * @param paramValue * @return boolean **/ public static boolean readtsysparameter(Integer paramKey,String paramValue){ String param= LoadDataConfigCache.getTetlparameterByKey(paramKey); if (StringUtils.isBlank(param)){ return false; } if (param.equals(paramValue)){ return true; } return false; } /** * 对应Oracle instr 判断str1中是否包含str2 * @param str1 * @param str2 * @return */ public static boolean contains(String str1, String str2){ if (StringUtils.isEmpty(str1)){ return false; } return str1.contains(str2); } /** * 去空格 例如：trim(#a.get("businessmajor")) * @param str * @return */ public static String trim(Object str){ String result = ""; if (null == str){ return result; } if (str instanceof String){ result = str.toString().trim(); } return result; } /** * 对应Oracle replace * @param str * @param str1 * @param str2 * @return */ public static String replace(String str, String str1, String str2){ return str.replace(str1,str2); } /** * 对应Oracle upper * @param str * @return */ public static String upper(String str){ return str.toUpperCase(); } /** * 对应Oracle lower * @param str * @return */ public static String lower(String str){ return str.toLowerCase(); } /** * 对应Oracle = * @param obj1 * @param obj2 * @return */ public static boolean equal(Object obj1, Object obj2){ if (null == obj1){ return false; } return obj1.equals(obj2); } /** 不相等 * 对应Oracle != 和 <> * @param obj1 * @param obj2 * @return */ public static boolean equalNot(Object obj1, Object obj2){ if (null == obj1){ return false; } return !obj1.equals(obj2); } /** * 对应Oracle round * @param number * @param decimals * @return */ public static BigDecimal round(Object number, int decimals){ BigDecimal result = new BigDecimal(0); if (number instanceof Double){ result = new BigDecimal((double) number); } else if (number instanceof Integer){ result = new BigDecimal((int) number); } else if (number instanceof String){ result = new BigDecimal((String) number); } else if (number instanceof BigDecimal){ result=(BigDecimal) number; } return result.setScale(decimals, BigDecimal.ROUND_HALF_UP); } /** * oracle中sys_guid() * @return */ public static String guid(){ return UUID.randomUUID().toString().replaceAll("-",""); } }
