【JAVA】java-jdbc远程数据库连接，执行CURD

import org.springframework.stereotype.Component;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 数据库连接和关闭公共类
 */
@Component
public class DBUtil {

    public static Connection getConn(String DRIVER,String URL,String USER,String PWD) {
        setting();
        System.out.println("ConnectionConnection==>" + URL + "====" + USER + "====" + PWD + "====" + DRIVER);
        Connection conn = null;
        try {
            // 注册驱动
            Class.forName(DRIVER);
            // 获得连接
            conn = DriverManager.getConnection(URL, USER, PWD);
            System.out.println("连接成功");
        } catch (Exception e) {
            e.printStackTrace();
        }
        return conn;
    }

    public static void setting() {

    }
    // 关闭连接
    public static void closeConn(Connection conn) {
        if (conn != null) {
            try {
                conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

    // 关闭执行对象
    public static void closeStatement(Statement stmt) {
        if (stmt != null) {
            try {
                stmt.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

    // 关闭结果集
    public static void closeResultSet(ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 查询SQL
     * @param sql
     * @param datasourceConfig
     * @return
     * @throws SQLException
     */
    public static List<Map<String, String>> getValue(String sql, Map<String,String> cConfig) {
        String DRIVER="";
        String URL="";
        String address=cConfig.get("address");
        String port=cConfig.get("port");
        String dbName=cConfig.get("dbName");
        String type=cConfig.get("type");
        String USER=cConfig.get("USER");
        String PWD=cConfig.get("PWD");
        //mysql
        if(type.equals("mysql")){
            //启动驱动
            DRIVER = "com.mysql.cj.jdbc.Driver";
            //设置连接路径
            URL = "jdbc:mysql://"+address+":"+port+"/"+dbName+"?serverTimezone=UTC&characterEncoding=utf8";
            //sqlserver
        }else if(type.equals("sqlserver")){
            DRIVER = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
            URL = "jdbc:sqlserver://"+address+":"+port+";DatabaseName="+dbName;
            //oracle
        }else if(type.equals("oracle")){
            DRIVER = "oracle.jdbc.driver.OracleDriver";
            URL = "jdbc:Oracle:thin:@"+address+":"+port+":"+dbName;
        }



        List<Map<String, String>> list = new ArrayList<>();
        Connection conn = getConn(DRIVER,URL,USER,PWD);
        Statement stmt = null;
        ResultSet rs = null;
        try {
            stmt = conn.createStatement();
            // 执行查询
            System.out.println("执行查询:" + sql);
            rs = stmt.executeQuery(sql);

            //获取字段
            ResultSetMetaData rsmd = rs.getMetaData();
            String[] fields = new String[rsmd.getColumnCount()];

            for (int i = 1; i < rsmd.getColumnCount() + 1; i++) {
                //字段名转小写toLowerCase()
                fields[i - 1] = rsmd.getColumnLabel(i).toLowerCase();
            }
            while (rs.next()) {
                HashMap<String, String> map = new HashMap<>();
                for (String field : fields) {
                    map.put(field, rs.getString(field));
                }
                list.add(map);
            }
            closeResultSet(rs);
            closeStatement(stmt);
            closeConn(conn);
            return list;
        } catch (SQLException e) {
            throw new RuntimeException("sql语句中字段或数据库表不存在",e);
        }
    }
    /**
     * 新增、修改、删除sql
     * @param sql
     * @param datasourceConfig
     * @return
     * @throws SQLException
     */
    public static int changeData(String sql, Map<String,String> cConfig) throws SQLException {
        String DRIVER="";
        String URL="";
        String address=cConfig.get("address");
        String port=cConfig.get("port");
        String dbName=cConfig.get("dbName");
        String type=cConfig.get("type");
        String USER=cConfig.get("USER");
        String PWD=cConfig.get("PWD");

        if(type.equals("mysql")){//mysql
            DRIVER = "com.mysql.cj.jdbc.Driver";	//启动驱动
            URL = "jdbc:mysql://"+address+":"+port+"/"+dbName+"?serverTimezone=UTC&characterEncoding=utf8";	//设置连接路径
        }else if(type.equals("sqlserver")){//sqlserver
            DRIVER = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
            URL = "jdbc:sqlserver://"+address+":"+port+";DatabaseName="+dbName;
        }else if(type.equals("oracle")){//oracle
            DRIVER = "oracle.jdbc.driver.OracleDriver";	//启动驱动
            URL = "jdbc:Oracle:thin:@"+address+":"+port+":"+dbName;	//设置连接路径
        }
        //logger.info("执行变更:" + sql);
        // TODO Auto-generated method stub
        Connection con = getConn(DRIVER,URL,USER,PWD);
        //创建PreparedStatement对象用来执行sql语句
        PreparedStatement per = con.prepareStatement(sql);
        //执行sql语句，executeUpdate()获取影响的行数并返回
        int rows = per.executeUpdate();
        //返回执行影响的行数
        return rows;
    }
}