【基于JDBC数据源工具类】

package com.bobandata.model.relation.tool.common.utils;

/**
 * @author:ChenGuoAo
 * @createDate:2022/12/6
 * @description: 数据源工具类
 */
@Slf4j
@Component
public class DataSourceUtil {

    @Value("${datasourceUrl}")
    private String datasourceUrl;

    @Autowired
    private DataSourceMapper dataSourceMapper;


    public List<Map<String, String>> execute(String dataSourceId, String sql) {
        DataSourceConfig dataSourceConfig = dataSourceMapper.getById(dataSourceId);
        if (dataSourceConfig == null){
            throw new RuntimeException("数据源不存在！！");
        }
        List<Map<String, String>> data = new ArrayList<>();
        try {
            // 转义字符去掉
            String sqlStr = StringEscapeUtils.unescapeJava(sql.trim());
           /* // 转小写
            String lowerStr = StringUtils.lowerCase(sqlStr);*/
            // 多空格变单空格
            String lastStr = replaceWhiteSpace(sqlStr);

            JSONObject jsonObject = JSONObject.parseObject(dataSourceConfig.getAttribute());
            String schemaName = String.valueOf(jsonObject.get("databases"));
            // 连接数据库
            Connection connection = DBUtil.getConnection(Long.parseLong(dataSourceId), datasourceUrl,schemaName , dataSourceConfig);
            if (connection == null) {
                throw new RuntimeException("连接数据库失败");
            }
            Statement statement = connection.createStatement();

            // 判断语句类型
            if (lastStr.startsWith("select") || lastStr.startsWith("SELECT") ) {
                ResultSet resultSet = statement.executeQuery(lastStr);
                int columnCount = resultSet.getMetaData().getColumnCount();

                while (resultSet.next()) {
                    Map<String, String> map = new LinkedHashMap<>();
                    for (int i = 1; i <= columnCount; i++) {
                        String columnName = resultSet.getMetaData().getColumnName(i);
                        String value = resultSet.getString(i);
                        map.put(columnName, value);
                    }
                    data.add(map);
                }
            } else { // DDL
                Integer i = statement.executeUpdate(lastStr);
                if (i == null) {
                    throw new RuntimeException("SQL执行失败!");
                }
            }
        } catch (Exception e) {
            log.error(e.getMessage(), e);
        }
        return data;
    }


    /**
     * 多空格变单空格
     */
    private static String replaceWhiteSpace(String original) {
        StringBuilder sb = new StringBuilder();
        //标记是否是第一个空格
        boolean isFirstSpace = false;
        char c;
        for (int i = 0; i < original.length(); i++) {
            c = original.charAt(i);
            //遇到空格字符时,先判断是不是第一个空格字符
            if (c == ' ' || c == '\t') {
                if (!isFirstSpace) {
                    sb.append(c);
                    isFirstSpace = true;
                }
            } else {//遇到非空格字符时
                sb.append(c);
                isFirstSpace = false;
            }
        }
        return sb.toString();
    }


}

public class DBUtil {
    public DBUtil() {
    }

    public static Connection connect(String driverName, String url, String user, String password) {
        Connection conn = null;

        try {
            Class.forName(driverName);
            conn = DriverManager.getConnection(url, user, password);
            System.out.println("连接成功");
        } catch (ClassNotFoundException var6) {
            var6.printStackTrace();
        } catch (SQLException var7) {
            var7.printStackTrace();
        }

        return conn;
    }

    public static Connection getConnection(long dataSourceId, String httpUrl, String schemaName) {
        String jsonStr = "";

        try {
            jsonStr = HttpClientUtils.doGet(httpUrl + dataSourceId);
        } catch (Exception var11) {
            var11.printStackTrace();
        }

        if (jsonStr.equals("{}")) {
            return null;
        } else {
            Map<String, String> maps = (Map)JSON.parse(jsonStr);
            String username = (String)maps.get("userName");
            String password = (String)maps.get("password");
            String url = (String)maps.get("connectUrl");
            if (url.contains("mysql") || url.contains("kingbase")) {
                url = url.substring(0, url.lastIndexOf("/") + 1);
            }

            if (url.contains("dm") || url.contains("oracle")) {
                url = url.substring(0, url.lastIndexOf(":") + 1);
            }

            url = url + schemaName + "?useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&useSSL=false";
            String driverName = (String)maps.get("driver");
            Connection connection = connect(driverName, url, username, password);
            return connection;
        }
    }

    public static Connection getConnection(long dataSourceId, String httpUrl, String schemaName, DataSourceConfig dataSourceConfig) {
        String type = dataSourceConfig.getType();
        String version = dataSourceConfig.getVersion();
        if (type.equals("dm") && version.equals("7.0")) {
            type = "dm7";
        }

        if (type.equals("dm") && version.equals("6.0")) {
            type = "dm6";
        }

        String jsonStr = "";

        try {
            Map map = (Map)JSON.parse(dataSourceConfig.getAttribute());
            jsonStr = HttpClientUtils.doGet(httpUrl + dataSourceId);
            if (!jsonStr.equals("{}")) {
                Map<String, String> maps = (Map)JSON.parse(jsonStr);
                String username = (String)maps.get("userName");
                String password = (String)maps.get("password");
                String url = "";
                String driverName = DataSourceType.getDriverName(type);
                if (type.equals("Oracle")) {
                    url = url + DataSourceType.getUrl(type) + map.get("host") + ":" + map.get("port") + DataSourceType.getSign(type) + schemaName;
                } else if (type.equals("kb")) {
                    url = (String)maps.get("connectUrl");
                } else {
                    url = url + DataSourceType.getUrl(type) + map.get("host") + ":" + map.get("port") + DataSourceType.getSign(type) + schemaName + "?useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&useSSL=false&useOldAliasMetadataBehavior=true";
                }

                Connection connection = connect(driverName, url, username, password);
                return connection;
            } else {
                return null;
            }
        } catch (Exception var15) {
            var15.printStackTrace();
            return null;
        }
    }

    public static void main(String[] args) {
        connect("oracle.jdbc.driver.OracleDriver", "jdbc:oracle:thin:@10.172.246.198:1521:orcl", "system", "Bobandata123");
    }
}