首页 > 数据库 >java实现oracle和mysql的group by分组功能|同时具备max()/min()/sum()/case when 函数等功能

java实现oracle和mysql的group by分组功能|同时具备max()/min()/sum()/case when 函数等功能

时间：2023-02-04 12:01:32浏览次数：39  
标签：case Map return String min new put java null


一、前言

oracle和mysql的group by 分组功能大家应该清楚，那如何使用java实现同样的功能呢

比如下面这个表


id

name

age

math

English

10

yujianlin

20

92.5


103

ww


84


102


5

20


102


6

1


103



63


103


5



10


20




我们需要按id分组，求最大age和math累计成绩

我们的sql应该这样写

select id,max(age),sum(math)
from student
group by id;

得到的数据是这样的



java实现oracle和mysql的group by分组功能|同时具备max()/min()/sum()/case when 函数等功能_List


那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));
    }

执行测试结果



java实现oracle和mysql的group by分组功能|同时具备max()/min()/sum()/case when 函数等功能_List_02


发现和数据库执行的一致，符合要求

复杂情况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实现oracle和mysql的group by分组功能|同时具备max()/min()/sum()/case when 函数等功能_Powered by 金山文档_03


java应该怎么实现呢，这里就需要用到spel表达式，不了解的朋友们可以去找一下资料，这里就不赘述了

这里就得加spel常用的一些表达式，具体请看文末的，spelCommonUtil.class

java测试

分组和函数配置如下

String[] groupFuns = {"id","age"};
String[][] combiFuns = {{"field", "id", "", ""},{"max", "age", "", ""},{"sum", "math", "", ""},{"spel","math","pingjia","caseWhen(\"不及格\",#a.get(\"math\")>60,\"及格\")"}};
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));
    }

执行结果



java实现oracle和mysql的group by分组功能|同时具备max()/min()/sum()/case when 函数等功能_字段_04


结果和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("-","");
    }

}

标签：case,Map,return,String,min,new,put,java,null
From： https://blog.51cto.com/u_15430531/6037097

相关文章

  • 三.第一个Java程序
    三.第一个Java程序1.安装Java(1)下载大家可以去官网(oracle.com)找到自己电脑对应的版本进行下载.当然因为一些国际上的问题，下载可能会有一些慢.这里我已经为大家准备好......
  • 四.如何学习Java
    四.如何学习Java1.学习建议(1)多写代码、多总结、多交流(2)第一周以后，尽量不要抄代码(3)写笔记，找一个好的笔记工具（比如typora）(4)分享、提问、思考2.博客的重要性我......
  • JavaScript高级第01天笔记-cnblog
    JavaScript高级第01天笔记1.面向过程与面向对象1.1面向过程面向过程就是分析出解决问题所需要的步骤，然后用函数把这些步骤一步一步实现，使用的时候再一个一个的依次调用......
  • JavaScript高级第02天笔记-cnblog
    JavaScript高级第02天笔记1.构造函数和原型1.1对象的三种创建方式--复习字面量方式varobj={};new关键字varobj=newObject();构造函数方式function......
  • JavaScript高级第03天笔记-cnblog
    JavaScript高级第03天笔记1.函数的定义和调用1.1函数的定义方式方式1函数声明方式function关键字(命名函数)functionfn(){}方式2函数表达式(匿名函数)var......
  • java中的回调函数
    什么是回调函数？回调函数就是一个被作为参数传递的函数为什么有回调函数？当程序需要运行时灵活性的时候需要引入回调函数如：1.调用者不知道具体事件发生时需要调用的具体函数......
  • Windows设置右键以管理员身份打开CMD(right click open command prompt runas adminis
    打开注册表编辑器win+r输入regedit打开注册表编辑器找到计算机\HKEY_CLASSES_ROOT\Directory\Background\shell新建runas项选中shell然后右键新建项，命名为runas选......
  • Java程序设计基础复习题
    Java程序设计基础1-6一、Java语言概述1.Java语言有哪些特点？简单易学。Java去掉了C/C++语言支持的单个不易理解和掌握的数据类型（指针pointer、联合体unions、结构体stru......
  • Java底层 - JVM 工具
    Java底层-JVM工具一、JVisualVM JVisualVM是JDK自带的性能检测工具，路径在%JAVA_HOME%/bin下面。mac下启动：直接使用jvisualvm命令即可打开界面。windows下启动：......
  • 关于#java#的问题：为什么hello world就报错了
    提问： 刚开始学java，为什么helloworld就报错了？求解疑。      解答： 你的eclipse创建的项目有问题，连libary都没有，这就是一个文件，它是无法运行的，需要依......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99