万能动态数据库连接工具类

0、写在前面的话

        今天在工作中遇到一个需求，是这样的，需要连接很多的数据库（我这里300多个），去每个库执行一段sql，然后把结果进行处理

        如果手工用数据库去连接那就太LOW了，而且300个诶大哥好辛苦，所以我的思路是写一个循环读取文件中的数据库ip地址，端口，用户名，密码，动态的去连接数据库，拿回返回结果再处理

         现在把我的这个工具分享给大家，大神不用看了，只是jdbc的简单连接使用

1、使用场景

    需要动态连接到很多数据库执行相同或则不同的业务

    我只写了mysql和sql server的连接，后续使用可以直接扩展

2、废话不多说，直接看代码

package com.test.db;import java.sql.*;import java.util.ArrayList;import java.util.List;/** * Careate by chenjin at 2017-09-25 * 动态连接数据库工具类 * </br> * <p> * 主要针对动态连接数据库 */public class DBHelper {    //数据库连接    private static Connection conn = null;    //数据库连接配置    private static DbPro dbPro = null;    private DBHelper() {        System.out.println(DBHelper.class.getName() + "该类不能初始化！！！");    }    /**     * 获取连接数据库拼接的URL     *     * @return     */    private static String getUrl() {        //判断IP和端口是否是空的        if (dbPro == null || dbPro.getIp() == null || "".equals(dbPro.getIp())                || dbPro.getPort() == null || "".equals(dbPro.getPort())) {            System.out.println("请初始化IP和端口！！！");            return null;        }        String urlStart;        if (dbPro.getSqlTyle() == SQL_TYPE.SQL_SERVER) {            urlStart = "jdbc:sqlserver://" + dbPro.getIp() + ":" + dbPro.getPort() + ";DatabaseName=" + dbPro.getDataBaseName();        } else if (dbPro.getSqlTyle() == SQL_TYPE.MYSQL) {            urlStart = "jdbc:mysql://" + dbPro.getIp() + ":" + dbPro.getPort() + "/" + dbPro.getDataBaseName();        } else {            System.out.println("未知数据库类型!!!");            urlStart = null;        }        return urlStart;    }    /**     * 初始化连接     *     * @param dbPro     * @return     */    public static Connection initConn(DbPro dbPro) {        try {            if (dbPro == null) {                new RuntimeException("初始化参数dbPro不能为空！！");                return null;            }            DBHelper.dbPro = dbPro;            //加载对应驱动            Class.forName(dbPro.getDriver());            //超时时间            DriverManager.setLoginTimeout(dbPro.getLoginTimeOutSeconds());            String url = getUrl();            if (url == null || "".equals(url)) {                return null;            }            conn = DriverManager.getConnection(url, dbPro.getUserName(), dbPro.getPassWord());            System.out.println("数据库初始化连接成功！！！");        } catch (ClassNotFoundException e) {            System.out.println("数据库初始化连接【失败】");            e.printStackTrace();            closeConn();        } catch (SQLException e) {            System.out.println("数据库初始化连接【失败】");            e.printStackTrace();            closeConn();        } finally {        }        return conn;    }    public static void closeConn() {        if (conn != null) {            try {                conn.close();                System.out.println("关闭成功！！！");            } catch (SQLException e) {                e.printStackTrace();                System.out.println("连接关闭失败！！！" + e.getMessage());            }        } else {            System.out.println("连接为空,无需关闭！！！");        }    }    /**     * 执行SQL查询     *     * @param sql     * @return     */    public static List<List<String>> exec(String sql) {        if (sql == null || sql.equals("")) {            System.out.println("sql为空，请先设置执行的SQL语句");            return null;        }        List<List<String>> allDatas = new ArrayList<List<String>>();        try {            if (conn == null) {                System.out.println("conn == null");                return null;            }            PreparedStatement preparedStatement = conn.prepareStatement(sql);            ResultSet resultSet = preparedStatement.executeQuery();            //获取到数据列数            int columnCount = resultSet.getMetaData().getColumnCount();            //column  列            //row     行            while (resultSet.next()) {                List<String> rows = new ArrayList<>();                for (int i = 1; i <= columnCount; i++) {                    if (dbPro.isPrint()) {                        if (i < columnCount) {                            System.out.print(resultSet.getString(i) + "\t");                        } else {                            System.out.print(resultSet.getString(i));                        }                    }                    rows.add(resultSet.getString(i));                }                if (dbPro.isPrint()) {                    System.out.println();                }                allDatas.add(rows);            }            System.out.println("SQL 执行完成！！！" + sql);        } catch (SQLException e) {            e.printStackTrace();        }        return allDatas;    }    /**     * 执行SQL查询     *     * @return     */    public static List<List<String>> exec() {        if (dbPro.getSql() == null || dbPro.getSql().equals("")) {            System.out.println("dbPro.getSql()为空，请先设置执行的SQL 语句");            return null;        }        return exec(dbPro.getSql());    }    /**     * 数据库配置类     */    static class DbPro {        private String ip = null;        private String port = null;        private String driver = null;        private String userName = null;        private String passWord = null;        private String sql = null;        private String dataBaseName = null;        private SQL_TYPE sqlTyle = null;        private int loginTimeOutSeconds = 0;        private boolean isPrint = false;        public boolean isPrint() {            return isPrint;        }        public void setPrint(boolean print) {            isPrint = print;        }        public int getLoginTimeOutSeconds() {            return loginTimeOutSeconds;        }        public void setLoginTimeOutSeconds(int loginTimeOutSeconds) {            this.loginTimeOutSeconds = loginTimeOutSeconds;        }        public SQL_TYPE getSqlTyle() {            return sqlTyle;        }        public void setSqlTyle(SQL_TYPE sqlTyle) {            this.sqlTyle = sqlTyle;        }        public String getIp() {            return ip;        }        public void setIp(String ip) {            this.ip = ip;        }        public String getPort() {            return port;        }        public void setPort(String port) {            this.port = port;        }        public String getDriver() {            return driver;        }        public void setDriver(String driver) {            this.driver = driver;        }        public String getUserName() {            return userName;        }        public void setUserName(String userName) {            this.userName = userName;        }        public String getPassWord() {            return passWord;        }        public void setPassWord(String passWord) {            this.passWord = passWord;        }        public String getSql() {            return sql;        }        public void setSql(String sql) {            this.sql = sql;        }        public String getDataBaseName() {            return dataBaseName;        }        public void setDataBaseName(String dataBaseName) {            this.dataBaseName = dataBaseName;        }    }    /**     * 数据库类型枚举     */    enum SQL_TYPE {        MYSQL, SQL_SERVER    }}