Java-RuoYi动态切换据库连接

Java-RuoYi动态切换据库连接

动态：

一、动态通过部门或公司切换数据库连接

二、动态返回对象数据或数据条数

import com.ruoyi.common.core.domain.entity.SysDept;
import com.ruoyi.common.utils.SecurityUtils;
import org.springframework.cglib.beans.BeanMap;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class JdbcUtil {
    //APP泛型
    public static <T> List<T> getConnectionTAPP(Object sql,SysDept sysDept, Class<T> cla){
        System.out.println("SQLT1:"+sql);
        sql = changeSQL(sysDept.getDataUrl(),sql);
        System.out.println("SQLT2:"+sql);
        List<T> list = new ArrayList<T>();
        Connection connection = null;
        CallableStatement callableStatement = null;
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            connection = DriverManager.getConnection(sysDept.getDataUrl(),sysDept.getDataName(),sysDept.getDataPassword());
            //编写存储过程语句
            String sqlStr = sql.toString();
            //设置入参
            callableStatement = connection.prepareCall(sqlStr);
            //执行
            callableStatement.executeQuery();
            //获取查询结果
            ResultSet rs = callableStatement.getResultSet();
            //处理数据
            if (null != rs) {
                ResultSetMetaData metaData = rs.getMetaData(); // 获得列的结果
                List<String> colNameList = new ArrayList<String>();
                int cols_len = metaData.getColumnCount(); // 获取总的列数
                for (int i = 0; i < cols_len; i++) {
                    colNameList.add(metaData.getColumnName(i + 1));
                }
                while (rs.next()) {
                    Map<String, Object> mapTMP = new HashMap<String, Object>();
                    for (int i = 0; i < cols_len; i++) {
                        String key = colNameList.get(i);
                        Object value = rs.getObject(colNameList.get(i));
                        mapTMP.put(key, value);
                    }
                    Object instance = cla.newInstance();
                    BeanMap beanMap = BeanMap.create(instance);
                    beanMap.putAll(mapTMP);
                    list.add(cla.cast(instance));
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    //新增和编辑
    public static int getConnectionSaveAndUpdateApp(Object sql,SysDept sysDept){
        System.out.println("SQLSaveAndUpdate1:"+sql);
        sql = changeSQL(sysDept.getDataUrl(),sql);
        System.out.println("SQLSaveAndUpdate2:"+sql);
        int count = 0;
        Connection connection = null;
        CallableStatement callableStatement = null;
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            connection = DriverManager.getConnection(sysDept.getDataUrl(),sysDept.getDataName(),sysDept.getDataPassword());
            //编写存储过程语句
            String sqlStr = sql.toString();
            //设置入参
            callableStatement = connection.prepareCall(sqlStr);
            //执行
            count = callableStatement.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return count;
    }

    //总条数
    public static Long getConnectionCountAPP(Object sql,SysDept sysDept){
        System.out.println("SQLCount1:"+sql);
        sql = changeSQL(sysDept.getDataUrl(),sql);
        System.out.println("SQLCount2:"+sql);
        Long count = 0L;
        Connection connection = null;
        CallableStatement callableStatement = null;
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            connection = DriverManager.getConnection(sysDept.getDataUrl(),sysDept.getDataName(),sysDept.getDataPassword());
            //编写存储过程语句
            String sqlStr = sql.toString();
            //设置入参
            callableStatement = connection.prepareCall(sqlStr);
            //执行
            callableStatement.executeQuery();
            //获取查询结果
            ResultSet rs = callableStatement.getResultSet();
            //处理数据
            if (null != rs) {
                ResultSetMetaData metaData = rs.getMetaData(); // 获得列的结果
                List<String> colNameList = new ArrayList<String>();
                int cols_len = metaData.getColumnCount(); // 获取总的列数
                for (int i = 0; i < cols_len; i++) {
                    colNameList.add(metaData.getColumnName(i + 1));
                }
                while (rs.next()) {
                    Object value = rs.getString(colNameList.get(0));
                    count = Long.parseLong(value.toString());
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return count;
    }

    //sql
    public static List<Map<String, Object>> getConnectionMap(Object sql){
        SysDept sysDept = SecurityUtils.getLoginUser().getUser().getDept();
        System.out.println("SQL1:"+sql);
        sql = changeSQL(sysDept.getDataUrl(),sql);
        System.out.println("SQL2:"+sql);
        List<Map<String, Object>> mapObjList = new ArrayList<>();
        Connection connection = null;
        CallableStatement callableStatement = null;
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            connection = DriverManager.getConnection(sysDept.getDataUrl(),sysDept.getDataName(),sysDept.getDataPassword());
            //编写存储过程语句
            String sqlStr = sql.toString();
            //设置入参
            callableStatement = connection.prepareCall(sqlStr);
            //执行
            callableStatement.executeQuery();
            //获取查询结果
            ResultSet rs = callableStatement.getResultSet();
            //处理数据
            if (null != rs) {

                ResultSetMetaData metaData = rs.getMetaData(); // 获得列的结果
                List<String> colNameList = new ArrayList<String>();
                int cols_len = metaData.getColumnCount(); // 获取总的列数
                for (int i = 0; i < cols_len; i++) {
                    colNameList.add(metaData.getColumnName(i + 1));
                }
                while (rs.next()) {
                    Map<String, Object> mapTMP = new HashMap<String, Object>();
                    for (int i = 0; i < cols_len; i++) {
                        String key = colNameList.get(i);
                        Object value = rs.getString(colNameList.get(i));
                        mapTMP.put(key, value);
                    }
                    mapObjList.add(mapTMP);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return mapObjList;
    }

    //泛型
    public static <T> List<T> getConnectionT(Object sql, Class<T> cla){
        SysDept sysDept = SecurityUtils.getLoginUser().getUser().getDept();
        System.out.println("SQLT1:"+sql);
        sql = changeSQL(sysDept.getDataUrl(),sql);
        System.out.println("SQLT2:"+sql);
        List<T> list = new ArrayList<T>();
        Connection connection = null;
        CallableStatement callableStatement = null;
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            connection = DriverManager.getConnection(sysDept.getDataUrl(),sysDept.getDataName(),sysDept.getDataPassword());
            //编写存储过程语句
            String sqlStr = sql.toString();
            //设置入参
            callableStatement = connection.prepareCall(sqlStr);
            //执行
            callableStatement.executeQuery();
            //获取查询结果
            ResultSet rs = callableStatement.getResultSet();
            //处理数据
            if (null != rs) {
                ResultSetMetaData metaData = rs.getMetaData(); // 获得列的结果
                List<String> colNameList = new ArrayList<String>();
                int cols_len = metaData.getColumnCount(); // 获取总的列数
                for (int i = 0; i < cols_len; i++) {
                    colNameList.add(metaData.getColumnName(i + 1));
                }
                while (rs.next()) {
                    Map<String, Object> mapTMP = new HashMap<String, Object>();
                    for (int i = 0; i < cols_len; i++) {
                        String key = colNameList.get(i);
                        Object value = rs.getObject(colNameList.get(i));
                        mapTMP.put(key, value);
                    }
                    Object instance = cla.newInstance();
                    BeanMap beanMap = BeanMap.create(instance);
                    beanMap.putAll(mapTMP);
                    list.add(cla.cast(instance));
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    //总条数
    public static Long getConnectionCount(Object sql){
        SysDept sysDept = SecurityUtils.getLoginUser().getUser().getDept();
        System.out.println("SQLCount1:"+sql);
        sql = changeSQL(sysDept.getDataUrl(),sql);
        System.out.println("SQLCount2:"+sql);
        Long count = 0L;
        Connection connection = null;
        CallableStatement callableStatement = null;
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            connection = DriverManager.getConnection(sysDept.getDataUrl(),sysDept.getDataName(),sysDept.getDataPassword());
            //编写存储过程语句
            String sqlStr = sql.toString();
            //设置入参
            callableStatement = connection.prepareCall(sqlStr);
            //执行
            callableStatement.executeQuery();
            //获取查询结果
            ResultSet rs = callableStatement.getResultSet();
            //处理数据
            if (null != rs) {
                ResultSetMetaData metaData = rs.getMetaData(); // 获得列的结果
                List<String> colNameList = new ArrayList<String>();
                int cols_len = metaData.getColumnCount(); // 获取总的列数
                for (int i = 0; i < cols_len; i++) {
                    colNameList.add(metaData.getColumnName(i + 1));
                }
                while (rs.next()) {
                    Object value = rs.getString(colNameList.get(0));
                    count = Long.parseLong(value.toString());
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return count;
    }

    //新增和编辑
    public static int getConnectionSaveAndUpdate(Object sql){
        SysDept sysDept = SecurityUtils.getLoginUser().getUser().getDept();
        System.out.println("SQLSaveAndUpdate1:"+sql);
        sql = changeSQL(sysDept.getDataUrl(),sql);
        System.out.println("SQLSaveAndUpdate2:"+sql);
        int count = 0;
        Connection connection = null;
        CallableStatement callableStatement = null;
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            connection = DriverManager.getConnection(sysDept.getDataUrl(),sysDept.getDataName(),sysDept.getDataPassword());
            //编写存储过程语句
            String sqlStr = sql.toString();
            //设置入参
            callableStatement = connection.prepareCall(sqlStr);
            //执行
            count = callableStatement.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return count;
    }

    //删除
    public static int getConnectionDelete(Object sql){
        SysDept sysDept = SecurityUtils.getLoginUser().getUser().getDept();
        System.out.println("SQLCount1:"+sql);
        sql = changeSQL(sysDept.getDataUrl(),sql);
        System.out.println("SQLCount2:"+sql);
        int count = 0;
        Connection connection = null;
        CallableStatement callableStatement = null;
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            connection = DriverManager.getConnection(sysDept.getDataUrl(),sysDept.getDataName(),sysDept.getDataPassword());
            //编写存储过程语句
            String sqlStr = sql.toString();
            //设置入参
            callableStatement = connection.prepareCall(sqlStr);
            //执行
            count = callableStatement.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return count;
    }

    //指定SQL中的表
    public static Object changeSQL(String url,Object sql){
        String targetStr = url.substring(url.indexOf("DatabaseName=")+13,url.length());
        String sqlOrg = sql.toString();
        sqlOrg = sqlOrg.replaceAll("XXXX.dbo",targetStr+".dbo");
        sql = sqlOrg;
        return sql;
    }
}