数据库连接工具类

我是标题

有点用好像完全又没有用的工具类

java代码

package com.lydata.handfulcode.common.utils;

import cn.hutool.core.convert.Convert;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Arrays;

/**
 * @author: aQ
 * @program: workspace
 * @description: 数据库链接工具类
 * @company date: 海葵 2021-05-08 14:24
 **/
public class DataBaseConnectionUtil {

    private static final Logger logger = LoggerFactory.getLogger(DataBaseConnectionUtil.class);

    /**
     * 创建ORACLE连接信息
     *
     * @param linkType
     * @param ip
     * @param port
     * @param dbname
     * @param username
     * @param password
     * @param autoCommit
     * @return java.sql.Connection
     * @author: aQ
     * @date 2021/5/8 15:17
     **/
    public static Connection initOracle(String linkType, String ip, String port, String dbname, String username, String password, boolean autoCommit) {
        String jdbcUrl = "";
        if ("Basic".equals(linkType)) {
            // oracle数据库用户名需要带上C##开头
            jdbcUrl = "jdbc:oracle:thin:@" + ip + ":" + port + "/" + dbname;
        } else if ("TNS".equals(linkType)) {
            jdbcUrl = "jdbc:oracle:thin:@ " + ip;
        } else {
            return null;
        }
        String jdbcDriver = "oracle.jdbc.driver.OracleDriver";
        return intiConnection(jdbcDriver, jdbcUrl, username, password, autoCommit);
    }

    public static Connection initOracle(String linkType, String ip, String port, String dbname, String username, String password) {
        return initOracle(linkType, ip, port, dbname, username, password, Boolean.TRUE);
    }

    /**
     * 创建MYSQL连接信息
     *
     * @param ip
     * @param port
     * @param dbname
     * @param username
     * @param password
     * @param autoCommit
     * @return java.sql.Connection
     * @author: aQ
     * @date 2021/5/8 15:11
     **/
    public static Connection initMysql(String ip, String port, String dbname, String username, String password, boolean autoCommit) {
        String jdbcDriver = "com.mysql.jdbc.Driver";
        String jdbcUrl = "jdbc:mysql://" + ip + ":" + port + "/" + dbname + "?useUnicode=true&characterEncoding=utf-8&rewriteBatchedStatements=true";
        return intiConnection(jdbcDriver, jdbcUrl, username, password, autoCommit);
    }

    public static Connection initMysql(String ip, String port, String dbname, String username, String password) {
        return initMysql(ip, port, dbname, username, password, Boolean.TRUE);
    }

    /**
     * 创建SQLServer连接信息
     *
     * @param ip
     * @param port
     * @param dbname
     * @param username
     * @param password
     * @param autoCommit
     * @return java.sql.Connection
     * @author: aQ
     * @date 2021/5/8 15:12
     **/
    public static Connection initSQLServer(String ip, String port, String dbname, String username, String password, boolean autoCommit) {
        String jdbcDriver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
        String jdbcUrl = "jdbc:sqlserver://" + ip + ":" + port + ";DatabaseName=" + dbname;
        return intiConnection(jdbcDriver, jdbcUrl, username, password, autoCommit);
    }

    public static Connection initSQLServer(String ip, String port, String dbname, String username, String password) {
        return initSQLServer(ip, port, dbname, username, password, Boolean.TRUE);
    }

    /**
     * 创建HANA连接信息
     *
     * @param ip
     * @param port
     * @param dbname
     * @param username
     * @param password
     * @param autoCommit
     * @return java.sql.Connection
     * @author: aQ
     * @date 2021/5/8 16:29
     **/
    public static Connection initHana(String ip, String port, String dbname, String username, String password, boolean autoCommit) {
        String jdbcDriver = "com.sap.db.jdbc.Driver";
        String jdbcUrl = "jdbc:sap://" + ip + ":" + port + "/databaseName=" + dbname;
        return intiConnection(jdbcDriver, jdbcUrl, username, password, autoCommit);
    }

    public static Connection initHana(String ip, String port, String dbname, String username, String password) {
        return initHana(ip, port, dbname, username, password, Boolean.TRUE);
    }

    /**
     * 根据传入的数据源类型，创建Connection连接信息
     *
     * @param jdbcDriver
     * @param jdbcUrl
     * @param username
     * @param password
     * @param autoCommit
     * @return java.sql.Connection
     * @author: aQ
     * @date 2021/5/8 14:56
     **/
    public static Connection intiConnection(String jdbcDriver, String jdbcUrl, String username, String password, boolean autoCommit) {
        Connection conn;
        try {
            Class.forName(jdbcDriver);
            // 连接数据库
            conn = DriverManager.getConnection(jdbcUrl, username, password);
            // 设置是否自动提交
            conn.setAutoCommit(autoCommit);
        } catch (Exception se) {
            logger.error("连接失败", se);
            return null;
        }
        return conn;
    }

    /**
     * 关闭数据库
     *
     * @param conn
     * @return void
     * @author: aQ
     * @date 2021/5/8 14:51
     **/
    public static void closeDB(Connection conn) {
        try {
            if (conn != null) {
                conn.close();
            }
        } catch (Exception ee) {
            logger.error("数据源关闭失败", ee);
        }
    }

    public static void main(String[] args) throws SQLException {
//        Connection connection = initHana("192.168.3.107", "30015", "DBO", "DBO", "HaiKui1234",false);
//        Connection connection = initSQLServer("192.168.3.164", "1433", "fdi", "sa", "hk123456",false);
//        Connection connection = initOracle("Basic", "192.168.3.104", "1521", "ORCL", "HK", "1a2b3c4D",false);
        Connection connection = initMysql("192.168.3.105", "3306", "lydata_merchant_base", "root", "Lyhdata@mysql105", false);
        System.out.println(connection);
//        String sql = "select * from user_info";
//        PreparedStatement ps = connection.prepareStatement(sql);
//        ResultSet rs = ps.executeQuery();
//        if (rs.next()) {
//            do {
//                String text = "id:" + rs.getInt("id") + ",name:" + rs.getString("name");
//                System.out.println(text);
//            } while (rs.next());
//        }

//        String sql = "INSERT INTO fdi_job_3 (id,merchant_id,job_name) VALUES (?,?,?)";
//        String sql = "UPDATE fdi_job_3 SET remark = 1 WHERE id = ? and merchant_id = ? and job_name = ?";
        String sql = "DELETE FROM fdi_job_3 WHERE id = ? and merchant_id = ? and job_name = ?";
        int count = 1;
        // 执行sql
        PreparedStatement ps2 = connection.prepareStatement(sql);
        for (int i = 1; i <= 101; i++) {
            ps2.setLong(1, Convert.toLong(i));
            ps2.setLong(2, 1);
            ps2.setString(3, "aaa" + i);
            // 添加批处理
            ps2.addBatch();
            if (count % 10 == 0 || count == 101) {
                int[] executeBatch = ps2.executeBatch();
                System.out.println(Arrays.toString(executeBatch));
                long executeBatchCount = Arrays.stream(executeBatch).filter(x -> x == 1).count();
                System.out.println(executeBatchCount);
                ps2.clearBatch();
                connection.commit();
            }
            count++;
        }
        closeDB(connection);
    }
}

pom引入

<dependency>
	<groupId>mysql</groupId>
	<artifactId>mysql-connector-java</artifactId>
	<version>8.0.13</version>
</dependency>
<dependency>
	<groupId>com.oracle.database.jdbc</groupId>
	<artifactId>ojdbc6</artifactId>
	<version>11.2.0.4</version>
</dependency>
<dependency>
	<groupId>com.microsoft.sqlserver</groupId>
	<artifactId>mssql-jdbc</artifactId>
	<version>9.2.1.jre8</version>
</dependency>
<dependency>
	<groupId>com.sap.cloud.db.jdbc</groupId>
	<artifactId>ngdbc</artifactId>
	<version>2.8.14</version>
</dependency>