open-JSD-10802/src/main/java/com/fr/plugin/db/procedure/dialectcreator/MyOracleDialectFetchStoreProcedureParameterExecutor.java

package com.fr.plugin.db.procedure.dialectcreator;

import com.fr.base.StoreProcedureParameter;
import com.fr.data.core.db.DBUtils;
import com.fr.data.core.db.dialect.Dialect;
import com.fr.data.core.db.dialect.base.key.fetchspp.parameter.FetchStoreProcedureParameterUtils;
import com.fr.data.core.db.dialect.base.key.fetchspp.parameter.OracleDialectFetchStoreProcedureParameterExecutor;
import com.fr.general.ComparatorUtils;
import com.fr.log.FineLoggerFactory;
import com.fr.stable.StringUtils;
import org.jetbrains.annotations.NotNull;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class MyOracleDialectFetchStoreProcedureParameterExecutor extends OracleDialectFetchStoreProcedureParameterExecutor {

    @Override
    public StoreProcedureParameter[] execute(Connection conn, String name, String parameterDefaultValue, Dialect current) {
        String query = this.getProcedureParametersSql(name);
        StoreProcedureParameter[] parameters = this.getStoreProcedureParameters(conn, parameterDefaultValue, query);
        if (parameters.length == 0) {
            String queryOther = this.getProcedureParametersSqlByOtherUser(conn, name);
            if (StringUtils.isNotEmpty(queryOther)) {
                parameters = this.getStoreProcedureParameters(conn, parameterDefaultValue, queryOther);
            }
        }

        return parameters;
    }


    private int sql2ColumnTypeForProcedure(String type) {
        if (type == null) {
            return 12;
        } else if (type.toUpperCase().indexOf("CHAR") <= -1 && type.toUpperCase().indexOf("BIT") <= -1) {
            if (type.toUpperCase().indexOf("INT") > -1) {
                return 4;
            } else if (isDecimalType(type)) {
                return 3;
            } else if (type.toUpperCase().indexOf("DATE") > -1) {
                return 91;
            } else {
                return type.toUpperCase().indexOf("BOOLEAN") > -1 ? 16 : 12;
            }
        } else {
            return 12;
        }
    }


    private static boolean isDecimalType(String type) {
        return type.toUpperCase().indexOf("DECIMAL") > -1 || type.toUpperCase().indexOf("NUMERIC") > -1 || type.toUpperCase().indexOf("DOUBLE") > -1 || type.toUpperCase().indexOf("FLOAT") > -1 || type.toUpperCase().indexOf("NUMBER") > -1;
    }

    private @NotNull StoreProcedureParameter[] getStoreProcedureParameters(Connection conn, String parameterDefaultValue, String query) {
        List<StoreProcedureParameter> list = new ArrayList();
        Statement stat = null;
        ResultSet resultSet = null;

        StoreProcedureParameter[] var8;
        try {
            stat = conn.createStatement();
            FineLoggerFactory.getLogger().info("query:"+query);
            resultSet = stat.executeQuery(query);
            int i = 0;

            while(resultSet.next()) {
                ++i;
                String parameterName = resultSet.getString("argument_name");
                String DataType = resultSet.getString("Data_type");
                String inOut = resultSet.getString("in_out");
                String plsType = resultSet.getString("pls_type");
                int level = resultSet.getInt("DATA_LEVEL");

                FineLoggerFactory.getLogger().info("parameterName:"+parameterName+",DataType:"+DataType+",inOut:"+inOut+",plsType:"+plsType+",level:"+level);

                if (level <= 0) {
                    DataType = DataType == null ? "" : DataType;
                    int type;
                    if (DataType.toUpperCase().contains("CURSOR")) {
                        type = -10;
                    } else {
                        type = sql2ColumnTypeForProcedure(plsType);
                    }

                    if(parameterName != null){
                        StoreProcedureParameter tempPara;
                        if (DataType.toUpperCase().contains("VARCHAR") && StringUtils.isNotEmpty(parameterDefaultValue)) {
                            tempPara = new StoreProcedureParameter(parameterName == null ? "storeParameter" + i : parameterName, parameterDefaultValue, sql2ParameterType(inOut), type);
                        } else {
                            tempPara = new StoreProcedureParameter(parameterName == null ? "storeParameter" + i : parameterName, sql2ParameterType(inOut), type);
                        }

                        list.add(tempPara);
                    }

                }
            }

            var8 = (StoreProcedureParameter[])list.toArray(new StoreProcedureParameter[0]);
            return var8;
        } catch (SQLException var18) {
            FineLoggerFactory.getLogger().error(var18.getMessage(), var18);
            var8 = (StoreProcedureParameter[])list.toArray(new StoreProcedureParameter[0]);
        } finally {
            DBUtils.close(resultSet);
            DBUtils.close(stat);
        }

        return var8;
    }

    private int sql2ParameterType(String inOut) {
        if (ComparatorUtils.equals(inOut.toUpperCase(), "OUT")) {
            return 2147483645;
        } else if (ComparatorUtils.equals(inOut.toUpperCase(), "IN")) {
            return 2147483646;
        } else {
            return ComparatorUtils.equals(inOut.toUpperCase(), "IN/OUT") ? 2147483644 : 2147483646;
        }
    }

    private String getProcedureParametersSql(String fullProcedureName) {
        String[] nameAddress = fullProcedureName.split("\\.");
        String procedureName;
        if (nameAddress.length == 3) {
            String packageName = nameAddress[1];
            procedureName = nameAddress[2];
            return "select argument_name, Data_type, in_out, pls_type, DATA_LEVEL from sys.user_arguments WHERE OBJECT_NAME='" + procedureName + "' and PACKAGE_NAME='" + packageName + "' order by position";
        } else {
            procedureName = nameAddress[nameAddress.length - 1];
            return "select argument_name, Data_type, in_out, pls_type, DATA_LEVEL from sys.user_arguments WHERE OBJECT_NAME='" + procedureName + "' and PACKAGE_NAME is null order by position";
        }
    }

    private String getProcedureParametersSqlByOtherUser(Connection conn, String fullProcedureName) {
        String[] nameAddress = fullProcedureName.split("\\.");
        Statement stat = null;
        ResultSet queryResultSet = null;
        String query = "";

        String owner;
        try {
            stat = conn.createStatement();
            String ownerQuery = this.getOwner(nameAddress[nameAddress.length - 1]);
            queryResultSet = stat.executeQuery(ownerQuery);

            for(owner = ""; queryResultSet.next(); owner = queryResultSet.getString("OWNER")) {
            }

            query = this.getProcedureParametersSql(owner, nameAddress);
            String var9 = query;
            return var9;
        } catch (SQLException var13) {
            FineLoggerFactory.getLogger().debug("oracle procedure parameters:{}", new Object[]{query});
            owner = query;
        } finally {
            DBUtils.closeResultSet(queryResultSet);
            DBUtils.closeStatement(stat);
        }

        return owner;
    }

    private String getProcedureParametersSql(String owner, String[] nameAddress) {
        String procedureName;
        if (nameAddress.length == 3) {
            String packageName = nameAddress[1];
            procedureName = nameAddress[2];
            return "select argument_name, Data_type, in_out, pls_type, DATA_LEVEL FROM all_arguments WHERE OWNER = '" + owner + "' and OBJECT_NAME='" + procedureName + "' and PACKAGE_NAME='" + packageName + "' order by position";
        } else {
            procedureName = nameAddress[nameAddress.length - 1];
            return "select argument_name, Data_type, in_out, pls_type, DATA_LEVEL FROM all_arguments WHERE OWNER = '" + owner + "' and OBJECT_NAME='" + procedureName + "' and PACKAGE_NAME is null order by position";
        }
    }

    private String getOwner(String procedureName) {
        return "SELECT OWNER FROM all_objects WHERE OBJECT_TYPE = 'PROCEDURE' AND OBJECT_NAME = '" + procedureName + "'";
    }

}