jnpf-common-v6x/jnpf-boot-common/jnpf-common-database/target/classes/jnpf/database/util/ConnUtil.java

package jnpf.database.util;

import cn.hutool.core.util.ReflectUtil;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.proxy.jdbc.ConnectionProxy;
import jnpf.database.model.dto.PrepSqlDTO;
import jnpf.database.model.entity.DbLinkEntity;
import jnpf.database.model.interfaces.DbSourceOrDbLink;
import jnpf.database.source.DbBase;
import jnpf.database.source.impl.DbOracle;
import jnpf.exception.DataException;
import jnpf.util.StringUtil;
import jnpf.util.TenantHolder;
import lombok.extern.slf4j.Slf4j;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Objects;
import java.util.Properties;

/**
 * Connection数据连接相关工具类
 *
 * @author JNPF开发平台组
 * @version V3.2.0
 * @copyright 引迈信息技术有限公司
 * @date 2021/10/6
 */
@Slf4j
public class ConnUtil {


    public static Connection getConnOrDefault(DbSourceOrDbLink dbSourceOrDbLink) throws DataException {
        if(dbSourceOrDbLink == null){
            dbSourceOrDbLink = DynamicDataSourceUtil.getDataSourceUtil();
        }
        return PrepSqlDTO.getConn(dbSourceOrDbLink.init());
    }

    public static Connection getConn(DbSourceOrDbLink dbSourceOrDbLink) throws DataException {
        return PrepSqlDTO.getConn(dbSourceOrDbLink.init());
    }

    public static Connection getConn(DbSourceOrDbLink dbSourceOrDbLink, String dbName) throws DataException {
        return PrepSqlDTO.getConn(dbSourceOrDbLink.init(dbName));
    }

    public static Connection getConn(String userName, String password, String url) throws DataException {
        DbLinkEntity dbLinkEntity = new DbLinkEntity();
        dbLinkEntity.setUserName(userName);
        dbLinkEntity.setPassword(password);
        dbLinkEntity.setUrl(url);
        dbLinkEntity.setDbType(DbTypeUtil.getDb(url).getJnpfDbEncode());
        return PrepSqlDTO.getConn(dbLinkEntity);
    }

    /**
     * 连接Connection
     */
    @Deprecated
    private static Connection getConnection(DbSourceOrDbLink dbSourceOrDbLink) throws DataException {
        return getConnection(dbSourceOrDbLink, null);
    }
    /**
     * 指定库名（多租户）
     */
    @Deprecated
    private static Connection getConnection(DbSourceOrDbLink dataSourceUtil, String dbName) throws DataException {
        DbLinkEntity dbLinkEntity = dataSourceUtil.init();
        // Oracle特殊连接
        if(DbTypeUtil.checkOracle(dbLinkEntity)){ return getOracleConn(dbLinkEntity); }
        return getConnection(dbLinkEntity.getAutoUsername(), dbLinkEntity.getAutoPassword(), getUrl(dbLinkEntity));
    }

    /**
     * 基础连接方式
     */
    @Deprecated
    private static Connection getConnection(String userName, String password, String url) throws DataException {
        DbBase db = DbTypeUtil.getDb(url);
        return ConnCommon.createConn(db.getDriver(), userName, password, url);
    }

    /* ==================================================== */

    /**
     * oracle特殊连接方式
     */
    private static Connection getOracleConn(DbLinkEntity dsd) throws DataException {
        DbOracle dbOracle = new DbOracle();
        return dbOracle.getOracleConn(dsd, getUrl(dsd));
    }

    /*==============获取数据连接URL==============*/

    public static String getUrl(DbSourceOrDbLink dbSourceOrDbLink) {
        return getUrl(dbSourceOrDbLink, null);
    }

    /**
     * 指定库名（多租户）
     *
     * @param dbSourceOrDbLink 数据源
     * @param dbName           数据库名
     * @return url连接
     */
    public static String getUrl(DbSourceOrDbLink dbSourceOrDbLink, String dbName) {
        return DbBase.BaseCommon.getDbBaseConnUrl(dbSourceOrDbLink, dbName);
    }

    public static void switchConnectionSchema(Connection conn) throws SQLException {
        if(TenantDataSourceUtil.isMultiTenancy() && DynamicDataSourceUtil.isPrimaryDataSoure() && TenantHolder.getLocalTenantCache() != null) {
            String schema = TenantDataSourceUtil.getTenantDbName();
            if (StringUtil.isNotEmpty(schema)) {
                Connection tmpConnection = getRealConnection(conn);
                //多租户模式， Schema模式下降连接切换至租户库
                //判断数据库类型
                try {
                    switch (DynamicDataSourceUtil.getPrimaryDbType()) {
                        case DbBase.SQL_SERVER:
                        case DbBase.MYSQL:
                            if(!Objects.equals(tmpConnection.getCatalog(), schema)) {
                                tmpConnection.setCatalog(schema);
                            }
                            break;
                        case DbBase.POSTGRE_SQL:
                            //POSTGRE转小写
                            schema = schema.toLowerCase();
                            if(!Objects.equals(tmpConnection.getSchema(), schema)) {
                                tmpConnection.setSchema(schema);
                            }
                            break;
                        case DbBase.ORACLE:
                            //Oracle转大写
                            schema = schema.toUpperCase();
                            if(!Objects.equals(tmpConnection.getSchema(), schema)) {
                                tmpConnection.setSchema(schema);
                            }
                            break;
                        case DbBase.KINGBASE_ES:
                        case DbBase.DM:
                        default:
                            if(!Objects.equals(tmpConnection.getSchema(), schema)) {
                                tmpConnection.setSchema(schema);
                            }
                    }
                }catch (Exception e){
                    //切库失败 连接不可用 需要先手动关闭链接
                    conn.close();
                    String url = "";
                    if(tmpConnection instanceof ConnectionProxy){
                        try {
                            url = ((ConnectionProxy) tmpConnection).getDirectDataSource().getUrl();
                        }catch (Exception ee){}
                    }
                    log.error("切库失败, 租户：{}, URL: {}, Msg: {}", TenantHolder.getDatasourceId(), url, e.getMessage());
                    throw e;
                }
            }
        }
    }


    public static String getConnectionDbName(Connection conn) throws SQLException {
        Connection tmpConnection = getRealConnection(conn);
        String dbName;
        String dbCode = DbTypeUtil.getDb(tmpConnection.getMetaData().getURL()).getJnpfDbEncode();
        //多租户模式， Schema模式下降连接切换至租户库
        //判断数据库类型
        switch (dbCode) {
            case DbBase.ORACLE://DBName:Schema
            case DbBase.DM://DBName:Schema
                dbName = tmpConnection.getSchema();
                break;
            case DbBase.SQL_SERVER://DBName:CataLog
            case DbBase.MYSQL://DBName:CataLog
            case DbBase.POSTGRE_SQL://DBName:CataLog, Schema:Schema
            case DbBase.KINGBASE_ES://DBName:CataLog, Schema:Schema
            default:
                dbName = tmpConnection.getCatalog();
        }
        return dbName;
    }

    public static String getConnectionSchema(Connection conn) throws SQLException {
        Connection tmpConnection = getRealConnection(conn);
        String dbName;
        String dbCode = DbTypeUtil.getDb(tmpConnection.getMetaData().getURL()).getJnpfDbEncode();
        //多租户模式， Schema模式下降连接切换至租户库
        //判断数据库类型
        switch (dbCode) {
            case DbBase.ORACLE://DBName:Schema
            case DbBase.DM://DBName:Schema
            case DbBase.POSTGRE_SQL://DBName:CataLog, Schema:Schema
            case DbBase.KINGBASE_ES://DBName:CataLog, Schema:Schema
            case DbBase.SQL_SERVER://DBName:CataLog
                dbName = tmpConnection.getSchema();
                break;
            case DbBase.MYSQL://DBName:CataLog
            default:
                dbName = tmpConnection.getCatalog();
        }
        return dbName;
    }

    /**
     * 获取包装连接中的数据连接
     * @param connection
     * @return
     */
    public static Connection getRealConnection(Connection connection){
        Connection tmpConnection = connection;
        //Druid连接包装只允许Mysql切换Schema
        for (int i = 0; i < 6; i++) {
            //最大6次避免有不知名的数据源自己获取自己无限循环
            try{
                tmpConnection = ReflectUtil.invoke(tmpConnection, "getConnection");
            }catch (Exception e){
                break;
            }
        }
        return tmpConnection;
    }

    /**
     * 获取DruidDataSource
     */
    public static DruidDataSource getDruidDataSource(DataSourceUtil dataSourceUtil) throws DataException {
        DruidDataSource druidDataSource = new DruidDataSource();
        druidDataSource.setUsername(dataSourceUtil.getUserName());
        druidDataSource.setPassword(dataSourceUtil.getPassword());
        //jdbc-url
        druidDataSource.setUrl(ConnUtil.getUrl(dataSourceUtil));
        //数据库驱动
        druidDataSource.setDriverClassName(DbTypeUtil.getDb(dataSourceUtil).getDriver());
        return druidDataSource;
    }



    /**
     * 以下为ConnUtil上面非显性的公开方法
     * 让调用者只关注getConn方法而不造成干扰
     */
    public static class ConnCommon {

        /**
         * （基础）获取数据连接
         *
         * @param driver   驱动
         * @param userName 用户
         * @param password 密码
         * @param url      url
         * @return 数据库连接
         * @throws DataException ignore
         */
        public static Connection createConn(String driver, String userName, String password, String url) throws DataException {
            try {
                Class.forName(driver);
                return DriverManager.getConnection(url, userName, password);
            } catch (Exception e) {
                e.printStackTrace();
                throw DataException.errorLink(e.getMessage());
            }
        }


        public static Connection createConnByProp(String driver, String userName, String password, String url, Properties conProps) throws DataException {
            try {
                conProps.put("user", userName);
                conProps.put("password", password);
                Class.forName(driver);
                return DriverManager.getConnection(url, conProps);
            } catch (Exception e) {
                throw new DataException(e.getMessage());
            }
        }

        /**
         * 开启数据库获取表注解连接
         *
         * @param dbSourceOrDbLink 数据源对象
         * @return ignore
         */
        public static Connection getConnRemarks(DataSourceUtil dbSourceOrDbLink) throws DataException {
            Properties props = new Properties();
            //设置可以获取remarks信息
            props.setProperty("remarks", "true");
            props.setProperty("remarksReporting", "true");
            //设置可以获取tables remarks信息
            props.setProperty("useInformationSchema", "true");
            return createConnByProp(DbTypeUtil.getDb(getUrl(dbSourceOrDbLink)).getDriver(), dbSourceOrDbLink.getUserName(), dbSourceOrDbLink.getPassword(),
                    getUrl(dbSourceOrDbLink), props);
        }

    }

}