SpringBoot maven 项目 JDBC 连接多源数据库

此方法支持 mysql，oracle，postgre，sqlserver等等

基本逻辑：1. 加载驱动; 2. 通过DriverManager传入数据库地址，用户名，密码，获取本地数据库连接

1. pom文件下添加依赖

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>${mysql-driver.version}</version>
    <scope>runtime</scope>
</dependency>

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <scope>runtime</scope>
</dependency>

<!-- oracle -->
<dependency>
    <groupId>com.oracle.database.jdbc</groupId>
    <artifactId>ojdbc8</artifactId>
    <version>21.1.0.0</version>
</dependency>

<dependency>
    <groupId>com.microsoft.sqlserver</groupId>
    <artifactId>mssql-jdbc</artifactId>
    <version>6.4.0.jre8</version>
</dependency>

2. 新建Connect基础类，其中提供select和insert方法。

public class JDBCConn {
    private Connection conn = null;
    private String driver;
    private String user;
    private String password;
    private String url;

    public JDBCConn(String driver, String user, String password, String url) {
        this.driver = driver;
        this.user = user;
        this.password = password;
        this.url = url;
        this.getConnection();
    }

    private void getConnection() {
        try {
            //加载驱动
            Class.forName(driver);
            //获取本地数据库
            conn = DriverManager.getConnection(url, user, password);
            System.out.println("数据库连接成功");
        } catch (Exception e) {
            System.out.println("获取数据库连接对象时产生的异常" + e);
        }
    }

    //关闭数据库连接
    public void closeAll(PreparedStatement pst) {
        if (pst != null) {
            try {
                pst.close();
                System.out.println("关闭数据库连接");
            } catch (SQLException e) {
                e.printStackTrace();
                System.out.println("PreparedStatement对象关闭数据库的异常" + e);
            }
        }
        if (conn != null) {
            try {
                conn.close();
                System.out.println("关闭数据库连接");
            } catch (Exception e) {
                System.out.println("数据库连接对象的异常" + e);
            }
        }
    }

    public Connection getConn() {
        return conn;
    }

    public String getDriver() {
        return driver;
    }

    public void setDriver(String driver) {
        this.driver = driver;
    }

    public String getUser() {
        return user;
    }

    public void setUser(String user) {
        this.user = user;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getUrl() {
        return url;
    }

    public void setUrl(String url) {
        this.url = url;
    }


    public List<Map<String, String>> selectAll(String tableName, String where) {
        List<Map<String, String>> result = new ArrayList<>();
        try {
            Statement statement = conn.createStatement();
            String sql = "select * from " + tableName + " " + where;
            ResultSet rs = statement.executeQuery(sql);//执行sql语句
            ResultSetMetaData rsmd = rs.getMetaData();//获取属性名
            String[] keys = new String[0];
            if (rsmd != null) {
                int count = rsmd.getColumnCount();
                keys = new String[count];
                for (int i = 1; i <= count; i++) {
                    keys[i - 1] = rsmd.getColumnName(i);
                }
            }
            System.out.println();
            while (rs.next()) {
                Map<String, String> temp = new HashMap<>();
                for (String key : keys) {
                    temp.put(key.toLowerCase(), rs.getString(key));
                }
                result.add(temp);
            }
            rs.close();
            statement.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }

    public void insertList(String tableName, List<Map<String, String>> params) {
        ResultSetMetaData rsmd = null;
        StringBuilder keySb = new StringBuilder();
        StringBuilder valueSb = new StringBuilder();
        try {
            Statement sqlStatement = conn.createStatement();
            String sql = "select * from " + tableName;
            ResultSet rs = sqlStatement.executeQuery(sql);
            rsmd = rs.getMetaData();
            List<String> keys = new ArrayList<>();
            if (rsmd != null) {
                int count = rsmd.getColumnCount();
                for (int i = 1; i <= count; i++) {
                    keys.add(rsmd.getColumnName(i));
                }
            }
            keys.forEach(key -> keySb.append(key).append(","));
            params.forEach(temp -> {
                StringBuilder sb2 = new StringBuilder("('");
                for (int i = 0; i < keys.size(); i++) {
                    String value = temp.get(keys.get(i));
                    if (null == value && Character.isUpperCase(keys.get(i).charAt(0))) {
                        value = temp.get(keys.get(i).toLowerCase());
                    }
                    if (null == value && Character.isLowerCase(keys.get(i).charAt(0))) {
                        value = temp.get(keys.get(i).toUpperCase());
                    }
                    sb2.append(value);
                    if (i == keys.size() - 1) {
                        sb2.append("'),");
                    } else {
                        sb2.append("','");
                    }
                }
                valueSb.append(sb2);
            });
//            INSERT [INTO] table_or_view [(column_list)] VALUES data_values
            String insertSql = "insert into " + tableName + "(" + keySb.substring(0, keySb.length() - 1) + ") values " + valueSb.substring(0, valueSb.length() - 1) + "";//SQL语句
            System.out.println(insertSql);
            sqlStatement.executeUpdate(insertSql);
            System.out.println();
            rs.close();
            sqlStatement.close();
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("Execute failed !");
        }
    }
}

封装的数据库链接我就直接上图了

main函数测试

public static void main(String[] args) {
    JDBCConn mySqlConn = JDBCService.getMySqlJDBCConn();
    List<Map<String, String>> ads_tops = mySqlConn.selectAll("ads_top", "");
    System.out.println(ads_tops);
}

结果：