JDBC实现多数据连接数据库，工具类

深碍√TFBOYSˉ_ 2023-01-02 07:30 171阅读 0赞

### JDBC实现多数据连接数据库 工具类 ###

*   *  说明
     *  借鉴
     *  需要的包(pom)
     *  工具类
     *   *  JDBCUtil
         *  SqlUtil

## 说明 ##

目前支持的数据库有mysql、Oracle、sqlserver、PostgreSql、达梦、sqlite、h2、access；版本兼容问题没有一一去测试，没安装这么多数据库，mysql、sqlserver、PostgreSql测试可用。

## 借鉴 ##

1.[使用JDBC获取各数据库的Meta信息][JDBC_Meta]；  
2.关系型数据库同步工具[xJavaFxTool][]的插件集合；

## 需要的包(pom) ##

<dependency>
                <groupId>cn.hutool</groupId>
                <artifactId>hutool-all</artifactId>
                <version>5.5.6</version>
            </dependency>
    
            <dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>druid</artifactId>
                <version>1.1.16</version>
            </dependency>
    
            
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <version>5.1.47</version>
            </dependency>
    
            
            <dependency>
                <groupId>cn.easyproject</groupId>
                <artifactId>ojdbc7</artifactId>
                <version>12.1.0.2.0</version>
            </dependency>
    
            
            <dependency>
                <groupId>com.microsoft.sqlserver</groupId>
                <artifactId>sqljdbc4</artifactId>
                <version>4.0</version>
            </dependency>
            <dependency>
                <groupId>net.sourceforge.jtds</groupId>
                <artifactId>jtds</artifactId>
                <version>1.3.1</version>
            </dependency>
    
            <dependency>
                <groupId>org.postgresql</groupId>
                <artifactId>postgresql</artifactId>
                <scope>runtime</scope>
            </dependency>
    
            
            <dependency>
                <groupId>com.h2database</groupId>
                <artifactId>h2</artifactId>
                <version>1.4.200</version>
            </dependency>
    
            
            <dependency>
                <groupId>org.xerial</groupId>
                <artifactId>sqlite-jdbc</artifactId>
                <version>3.28.0</version>
            </dependency>
    
            <dependency>
                <groupId>net.sf.ucanaccess</groupId>
                <artifactId>ucanaccess</artifactId>
                <version>4.0.4</version>
            </dependency>

## 工具类 ##

### JDBCUtil ###

import com.alibaba.druid.pool.DruidDataSource;
    import com.alibaba.druid.util.JdbcUtils;
    import javafx.scene.control.CheckBox;
    import javafx.scene.control.CheckBoxTreeItem;
    import javafx.scene.control.TreeView;
    import javafx.scene.layout.HBox;
    import org.apache.commons.lang.StringUtils;
    import org.h2.table.TableType;
    import org.springframework.jdbc.core.JdbcTemplate;
    
    import javax.sql.DataSource;
    import java.io.Closeable;
    import java.sql.*;
    import java.util.*;
    
    /**
     * 数据库类型转换url 
     * @author wnm
     * @date 2020/12/29
     */
    public class JDBCUtil {
        
    
        public static String getDbDefaultPort(String DB_TYPE) {
        
            if ("sqlserverold".equalsIgnoreCase(DB_TYPE)) {
        
                DB_TYPE = "sqlserver";
            } else if ("oracleSid".equalsIgnoreCase(DB_TYPE)) {
        
                DB_TYPE = "oracle";
            }
            if ("sqlserver".equalsIgnoreCase(DB_TYPE)) {
        
                return "1433";
            } else if ("oracle".equalsIgnoreCase(DB_TYPE)) {
        
                return "1521";
            } else if ("mysql".equalsIgnoreCase(DB_TYPE)) {
        
                return "3306";
            } else if ("postgresql".equalsIgnoreCase(DB_TYPE)) {
        
                return "5432";
            } else if ("dm".equalsIgnoreCase(DB_TYPE)) {
        
                return "5236";
            } else if ("h2Server".equalsIgnoreCase(DB_TYPE)) {
        
                return "9092";
            }
            return null;
        }
    
        /**
         * 获取jdbc连接地址
         */
        public static String getJdbcUrl(String jdbcUrl, String DB_TYPE, String dbIp, String dbPort, String dbName) {
        
            if (StringUtils.isNotEmpty(jdbcUrl)) {
        
                return jdbcUrl;
            }
            if ("sqlserver".equalsIgnoreCase(DB_TYPE)) {
        
                //jdbc:sqlserver://localhost:3433;DatabaseName=dbname
                jdbcUrl = String.format("jdbc:sqlserver://%s:%s;DatabaseName=%s", dbIp, dbPort, dbName);
            } else if ("oracle".equalsIgnoreCase(DB_TYPE)) {
        
                //jdbc:oracle:thin:@[HOST_NAME]:PORT:[DATABASE_NAME]
                jdbcUrl = String.format("jdbc:oracle:thin:@%s:%s/%s", dbIp, dbPort, dbName);
            } else if ("oracleSid".equalsIgnoreCase(DB_TYPE)) {
        
                //jdbc:oracle:thin:@[HOST_NAME]:PORT:[DATABASE_NAME]
                jdbcUrl = String.format("jdbc:oracle:thin:@%s:%s:%s", dbIp, dbPort, dbName);
            } else if ("mysql".equalsIgnoreCase(DB_TYPE)) {
        
                //jdbc:mysql://bad_ip:3306/database
                jdbcUrl = String.format("jdbc:mysql://%s:%s/%s", dbIp, dbPort, dbName);
            } else if ("postgresql".equalsIgnoreCase(DB_TYPE)) {
        
                jdbcUrl = String.format("jdbc:postgresql://%s:%s/%s", dbIp, dbPort, dbName);
            } else if ("sqlserverold".equalsIgnoreCase(DB_TYPE)) {
        
                jdbcUrl = String.format("jdbc:jtds:sqlserver://%s:%s/%s", dbIp, dbPort, dbName);
            } else if ("dm".equalsIgnoreCase(DB_TYPE)) {
        
                jdbcUrl = String.format("jdbc:dm://%s:%s/%s", dbIp, dbPort, dbName);
            } else if ("sqlite".equalsIgnoreCase(DB_TYPE)) {
        
                jdbcUrl = String.format("jdbc:sqlite:%s", dbName);
            } else if ("h2Embedded".equalsIgnoreCase(DB_TYPE)) {
        
                jdbcUrl = String.format("jdbc:h2:%s", dbName);
            } else if ("h2Server".equalsIgnoreCase(DB_TYPE)) {
        
                jdbcUrl = String.format("jdbc:h2:tcp://%s:%s/%s", dbIp, dbPort, dbName);
            } else if ("access".equalsIgnoreCase(DB_TYPE)) {
        
                jdbcUrl = String.format("jdbc:ucanaccess://%s", dbName);
            }
            System.out.print("\n解析出jdbcUrl: " + jdbcUrl);
            return jdbcUrl;
        }
    
        public static String convertDatabaseCharsetType(String userName, String schema, String type) {
        
            if ("sqlserverold".equalsIgnoreCase(type)) {
        
                type = "sqlserver";
            } else if ("oracleSid".equalsIgnoreCase(type)) {
        
                type = "oracle";
            }
            String dbUser;
            if ("oracle".equals(type)) {
        
                dbUser = StringUtils.defaultIfBlank(schema, userName).toUpperCase();
            } else if ("postgresql".equals(type)) {
        
                dbUser = StringUtils.defaultIfBlank(schema, "public");
            } else if ("mysql".equals(type)) {
        
                dbUser = null;
            } else if ("sqlserver".equals(type)) {
        
                dbUser = StringUtils.defaultIfBlank(schema, "dbo");
            } else if ("db2".equals(type)) {
        
                dbUser = StringUtils.defaultIfBlank(schema, userName).toUpperCase();
            } else if ("h2Server".equals(type)) {
        
                dbUser = StringUtils.defaultIfBlank(schema, "PUBLIC");
            } else {
        
                dbUser = StringUtils.defaultIfBlank(schema, userName);
            }
            return dbUser;
        }
    
        /**
         * 获取ResultSet
         * @author wnm
         * @date 2021/1/3
         * @param dataSource, sql
         * @return java.sql.ResultSet
         */
        public static ResultSet getResultSetBySql(DataSource dataSource,String sql) {
        
            try {
        
                final ResultSet[] rs = new ResultSet[1];
                // 使用jdbc原生方法获取
                JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
                jdbcTemplate.query(sql, r->{
        
                    rs[0] =r;
                });
                return rs[0];
            } catch (Throwable e) {
        
                // 获取表失败！
                return null;
            }
        }
    
        /**
         * 将一个未处理的ResultSet解析为Map列表.
         * @param rs
         * @return
         */
        public static List<Map<String, Object>> parseResultSetToMapList(ResultSet rs) {
        
            //
            List<Map<String, Object>> result = new ArrayList<>();
            //
            if (null == rs) {
        
                return null;
            }
            //
            try {
        
                while (rs.next()) {
        
                    //
                    Map<String, Object> map = parseResultSetToMap(rs);
                    //
                    if (null != map) {
        
                        result.add(map);
                    }
                }
            } catch (SQLException e) {
        
                e.printStackTrace();
            }
            //
            return result;
        }
    
        /**
         * 解析ResultSet的单条记录,不进行 ResultSet 的next移动处理
         * @param rs
         * @return
         */
        private static Map<String, Object> parseResultSetToMap(ResultSet rs) {
        
            //
            if (null == rs) {
        
                return null;
            }
            //
            Map<String, Object> map = new HashMap<String, Object>();
            //
            try {
        
                ResultSetMetaData meta = rs.getMetaData();
                //
                int colNum = meta.getColumnCount();
                //
                for (int i = 1; i <= colNum; i++) {
        
                    // 列名
                    String name = meta.getColumnLabel(i); // i+1
                    Object value = rs.getObject(i);
                    // 加入属性
                    map.put(name, value);
                }
            } catch (SQLException e) {
        
                e.printStackTrace();
            }
            //
            return map;
        }
    
       /**
         * 关闭Connection
         */
        public static void close(Connection conn) {
        
            if(conn!=null) {
        
                try {
        
                    conn.close();
                    conn = null;
                } catch (SQLException e) {
        
                    e.printStackTrace();
                }
            }
        }
        
       /**
         * 关闭Statement
         */
        public static void close(Statement stmt) {
        
            if(stmt!=null) {
        
                try {
        
                    stmt.close();
                    stmt = null;
                } catch (SQLException e) {
        
                    e.printStackTrace();
                }
            }
        }
        
        /**
         * 关闭ResultSet
         */
        public static void close(ResultSet rs) {
        
            if(rs!=null) {
        
                try {
        
                    rs.close();
                    rs = null;
                } catch (SQLException e) {
        
                    e.printStackTrace();
                }
            }
        }
    }

### SqlUtil ###

import cn.hutool.db.meta.Column;
    import cn.hutool.db.meta.MetaUtil;
    import cn.hutool.db.meta.Table;
    import cn.hutool.db.meta.TableType;
    import com.alibaba.druid.pool.DruidDataSource;
    import com.alibaba.druid.util.JdbcUtils;
    import org.springframework.jdbc.core.JdbcTemplate;
    import org.springframework.jdbc.datasource.DriverManagerDataSource;
    import org.springframework.jdbc.datasource.SimpleDriverDataSource;
    
    import javax.sql.DataSource;
    import java.io.Closeable;
    import java.sql.*;
    import java.util.*;
    
    public class SqlUtil {
        
    
        /**
         * 连接数据库
         * @author wnm
         * @date 2020/12/30
         * @param dbType, dbIp, dbPort, dbName, dbUserName, dbUserPassword
         * @return void
         */
        public static void connectAction(String dbType, String dbIp, String dbPort, String dbName, String dbUserName, String dbUserPassword) throws Exception {
        
            DataSource dataSource = null;
            try {
        
                dataSource = getDataSource(dbType, dbIp, dbPort, dbName, dbUserName, dbUserPassword,null,"Druid");
                List<String> getTableNames=getTableNames(dataSource,dbType,dbUserName);
                System.out.print("\n表名：");
                System.out.print(getTableNames);
                System.out.print("\n");
                List<String> getTableFields=getTableFields(dataSource,getTableNames.get(0));
                System.out.print("\n表字段：");
                System.out.print(getTableFields);
                System.out.print("\n");
                List<Map<String, Object>> getTableData=getTableData(dataSource,getTableNames.get(0));
                System.out.print("\n表数据：");
                System.out.print(getTableData);
                System.out.print("\n");
            } finally {
        
                if (dataSource instanceof DruidDataSource) {
        
                    JdbcUtils.close((Closeable) dataSource);
                }
            }
        }
    
        /**
         * 获取数据源
         * @author wnm
         * @date 2020/12/29
         * @param dbType, dbIp, dbPort, dbName, dbUserName, dbUserPassword, jdbcUrl, dataSourceType]
         * @return javax.sql.DataSource
         */
        public static DataSource getDataSource(String dbType, String dbIp, String dbPort, String dbName, String dbUserName, String dbUserPassword, String jdbcUrl, String dataSourceType) {
        
            DataSource dataSource = null;
            if ("Druid".equals(dataSourceType)) {
        
                dataSource = getDruidDataSource(dbType, dbIp, dbPort, dbName, dbUserName, dbUserPassword, jdbcUrl);
            } else if ("Driver".equals(dataSourceType)) {
        
                dataSource = new DriverManagerDataSource(JDBCUtil.getJdbcUrl(jdbcUrl, dbType, dbIp, dbPort, dbName), dbUserName, dbUserPassword);
            } else if ("Simple".equals(dataSourceType)) {
        
                dataSource = getSimpleDataSource(dbType, dbIp, dbPort, dbName, dbUserName, dbUserPassword, jdbcUrl);
            }
            return dataSource;
        }
    
        /**
         *获取DruidDataSource
         * @author wnm
         * @date 2020/12/29
         * @param dbType, dbIp, dbPort, dbName, dbUserName, dbUserPassword, jdbcUrl]
         * @return com.alibaba.druid.pool.DruidDataSource
         */
        public static DruidDataSource getDruidDataSource(String dbType, String dbIp, String dbPort, String dbName, String dbUserName, String dbUserPassword, String jdbcUrl) {
        
            DruidDataSource dataSource = new DruidDataSource();
            try {
        
                String url= JDBCUtil.getJdbcUrl(jdbcUrl, dbType, dbIp, dbPort, dbName);
                DriverManager.getConnection(url,dbUserName,dbUserPassword);
                dataSource.setUrl(url);
                if ("access".equals(dbType)) {
        
                    dataSource.setDriverClassName("net.ucanaccess.jdbc.UcanaccessDriver");
                }
                dataSource.setUsername(dbUserName);
                dataSource.setPassword(dbUserPassword);
                dataSource.setTestWhileIdle(false);
                dataSource.setFailFast(true);
                return dataSource;
            }
            catch (SQLException e){
        
                String m=e.getMessage();
                System.out.print("\n错误："+m);
                return dataSource;
            }
    
        }
    
        /**
         *获取SimpleDataSource
         * @author wnm
         * @date 2020/12/29
         * @param dbType, dbIp, dbPort, dbName, dbUserName, dbUserPassword, jdbcUrl]
         * @return org.springframework.jdbc.datasource.SimpleDriverDataSource
         */
        public static SimpleDriverDataSource getSimpleDataSource(String dbType, String dbIp, String dbPort, String dbName, String dbUserName, String dbUserPassword, String jdbcUrl) {
        
            jdbcUrl = JDBCUtil.getJdbcUrl(jdbcUrl, dbType, dbIp, dbPort, dbName);
            String driverClass = null;
            Driver driver=null;
            try {
        
                if ("access".equals(dbType)) {
        
                    driverClass = "net.ucanaccess.jdbc.UcanaccessDriver";
                } else {
        
                    driverClass = JdbcUtils.getDriverClassName(jdbcUrl);
                }
                driver=JdbcUtils.createDriver(driverClass);
            } catch (SQLException e) {
        
                e.printStackTrace();
            }
            SimpleDriverDataSource dataSource = new SimpleDriverDataSource(driver,jdbcUrl, dbUserName, dbUserPassword);
            return dataSource;
        }
    
        public static List<String> showSqlServerTables(DataSource dataSource, String dbType) throws SQLException {
        
            List<String> tables = new ArrayList<>();
            String sql = "";
            if ("sqlserver".equals(dbType)) {
        
                sql = "select c.name from sys.objects c where c.type='u'";
            } else if ("sqlserverold".equals(dbType)) {
        
                sql = "select c.name from sysobjects c where c.type='u'";
            } else if ("access".equals(dbType)) {
        
                sql = "select table_name from information_schema.tables";
            } else {
        
                return tables;
            }
            tables = new JdbcTemplate(dataSource).queryForList(sql, String.class);
            return tables;
        }
    
        /**
         * 获取数据库的表名
         * @author wnm
         * @date 2020/12/30
         * @param dbType, dbIp, dbPort, dbName, dbUserName, dbUserPassword
         * @return void
         */
        public static List<String> getTableNames(DataSource dataSource,String dbType, String dbUserName) throws SQLException{
        
            List<String> tableNames;
            try {
        
                    tableNames = MetaUtil.getTables(dataSource, JDBCUtil.convertDatabaseCharsetType(dbUserName, "", dbType), TableType.valueOf("TABLE"));
                    return tableNames;
                } catch (Throwable e) {
        
                    if ("sqlserver".equals(dbType) || "sqlserverold".equals(dbType) || "access".equals(dbType)) {
        
                        tableNames = SqlUtil.showSqlServerTables(dataSource, dbType);
                    } else {
        
                        if ("oracleSid".equals(dbType)) {
        
                            dbType = "oracle";
                        }
                        Connection connection = dataSource.getConnection();
                        try {
        
                            tableNames = JdbcUtils.showTables(connection, dbType);
                        } finally {
        
                            JdbcUtils.close(connection);
                        }
                    }
                    return tableNames;
                }
        }
    
        /**
         * 获取表字段信息
         * @author wnm
         * @date 2020/12/30
         * @param dataSource, tableName
         * @return cn.hutool.db.meta.Table
         */
        public static List<String> getTableFields(DataSource dataSource,String tableName) {
        
            Table table = new Table(tableName);
            List<String> fields=new ArrayList<>();
            try {
        
                table = MetaUtil.getTableMeta(dataSource, tableName);
                for (Column column : table.getColumns()) {
        
                    fields.add(column.getName());
                }
                return fields;
            } catch (Throwable e) {
        
                // 获取表结构失败！使用jdbc原生方法获取
                String querySql = String.format("select * from %s where 1=2", tableName);
                ResultSet rs = JDBCUtil.getResultSetBySql(dataSource, querySql);
                try {
        
                    assert rs != null;
                    ResultSetMetaData rsMetaData = rs.getMetaData();
                    for (int i = 1, len = rsMetaData.getColumnCount(); i <= len; i++) {
        
                        Column column = new Column();
                        column.setName(rsMetaData.getColumnName(i));
                        column.setType(rsMetaData.getColumnType(i));
                        column.setSize(rsMetaData.getColumnDisplaySize(i));
                        column.setComment(rsMetaData.getColumnLabel(i));
                        table.setColumn(column);
                    }
                    for (Column column : table.getColumns()) {
        
                        fields.add(column.getName());
                    }
                    return fields;
                } catch (Throwable ee) {
        
                    return null;
                }
            }
        }
    
        /**
         * 获取指定表格数据
         * @author wnm
         * @date 2021/1/3
         * @param dataSource, tableName
         * @return java.sql.ResultSet
         */
        public static List<Map<String, Object>> getTableData(DataSource dataSource,String tableName) {
        
            final List<Map<String, Object>>[] rsMap = new ArrayList[1];
            try {
        
    
                // 使用jdbc原生方法获取
                JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
                jdbcTemplate.query("select * from "+tableName, rs->{
        
                    rsMap[0]= JDBCUtil.parseResultSetToMapList(rs);
                });
                return rsMap[0];
            } catch (Throwable e) {
        
                // 获取表失败！
                return null;
            }
        }
    
    }

[JDBC_Meta]: https://blog.csdn.net/renfufei/article/details/39316751/
[xJavaFxTool]: https://gitee.com/xwintop/xJavaFxTool