SpringBoot使用Hikari构建数据库操作工具

 用于程序需要根据配置不同操作不同数据库的场景。技术使用SpringBoot自带数据库连接线程池框架Hikari

import com.google.common.collect.Lists;
import com.highjet.index.modules.dto.DatabaseDto;
import com.zaxxer.hikari.HikariDataSource;
import lombok.extern.slf4j.Slf4j;


import javax.sql.DataSource;
import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStreamReader;
import java.nio.charset.StandardCharsets;
import java.sql.*;
import java.util.*;


@Slf4j
public class SqlUtils {

	/**
	 * 获取数据源
	 *
	 * @param jdbcUrl /
	 * @param userName /
	 * @param password /
	 * @return DataSource
	 */
	private static DataSource getDataSource(String jdbcUrl, String userName, String password) {
		HikariDataSource hikariDataSource = new HikariDataSource();
		String className;
		try {
			className = DriverManager.getDriver(jdbcUrl.trim()).getClass().getName();
		} catch (SQLException e) {
			throw new RuntimeException("Get class name error: =" + jdbcUrl);
		}
		if (className!=null) {
			DataTypeEnum dataTypeEnum = DataTypeEnum.urlOf(jdbcUrl);
			if (null == dataTypeEnum) {
				throw new RuntimeException("Not supported data type: jdbcUrl=" + jdbcUrl);
			}
			hikariDataSource.setDriverClassName(dataTypeEnum.getDriver());
		} else {
			hikariDataSource.setDriverClassName(className);
		}

		hikariDataSource.setJdbcUrl(jdbcUrl);
		hikariDataSource.setUsername(userName);
		hikariDataSource.setPassword(password);
		//连接只读数据库时配置为true， 保证安全
		//hikariDataSource.setReadOnly(true);
		//等待连接池分配连接的最大时长（毫秒），超过这个时长还没可用的连接则发生SQLException
		hikariDataSource.setConnectionTimeout(1000*60*3);
		//一个连接的生命时长（毫秒），超时而且没被使用则被释放
		hikariDataSource.setMaxLifetime(1000*60*2);
		// 配置初始化大小、最小、最大
		//连接池中允许的最大连接数。缺省值：10；推荐的公式：((core_count * 2) + effective_spindle_count)
		hikariDataSource.setMaximumPoolSize(1);
		hikariDataSource.setMinimumIdle(1);
		// 如果链接出现异常则直接判定为失败而不是一直重试

		return hikariDataSource;
	}


	private static Connection getConnection(String jdbcUrl, String userName, String password) {
		DataSource dataSource = getDataSource(jdbcUrl, userName, password);
		Connection connection = null;
		try {
			connection = dataSource.getConnection();
		} catch (Exception ignored) {}
		try {
			int timeOut = 5;
			if (null == connection || connection.isClosed() || !connection.isValid(timeOut)) {
				log.info("connection is closed or invalid, retry get connection!");
				connection = dataSource.getConnection();
			}
		} catch (Exception e) {
			log.error("create connection error, jdbcUrl: {}", jdbcUrl);
			throw new RuntimeException("create connection error, jdbcUrl: " + jdbcUrl);
		} finally {
			//close(connection);
		}
		return connection;
	}

	private static void releaseConnection(Connection connection) {
		if (null != connection) {
			try {
				connection.close();
			} catch (Exception e) {
				log.error(e.getMessage(),e);
				log.error("connection close error：" + e.getMessage());
			}
		}
	}

	/**
	 * 测试连接
	 * @param jdbcUrl
	 * @param userName
	 * @param password
	 * @return
	 */
	public static boolean testConnection(String jdbcUrl, String userName, String password) {
		Connection connection = null;
		try {
			connection = getConnection(jdbcUrl, userName, password);
			if (null != connection) {
				return true;
			}
		} catch (Exception e) {
			log.info("Get connection failed:" + e.getMessage());
		} finally {
			releaseConnection(connection);
		}
		return false;
	}


	/**
	 * 执行SQL查询
	 * @param jdbcUrl
	 * @param userName
	 * @param password
	 * @param sqlScript
	 * @return
	 */
	public static List<Map<String, Object>>  executeQuerySqlScript(String jdbcUrl, String userName, String password, String sqlScript) {
		Connection connection = getConnection(jdbcUrl, userName, password);
		List<Map<String, Object>> list = null;
		try {
			list = executeQuery(connection, sqlScript);
		} catch (Exception e) {
			log.error("sql脚本执行发生异常:{}",e.getMessage());

		}finally {
			releaseConnection(connection);
		}
		return list;
	}

	public static List<Map<String, Object>>  executeQuery(Connection connection, String  sql) {
		Statement st = null;
		List<Map<String, Object>> list =null;
		try {
			st = connection.createStatement();
			ResultSet resultSet = st.executeQuery(sql);
			list = resultSetTransferToList(resultSet);
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close(st);
		}
		return list;
	}

	/**将ResultSet对象转换为List对象
	 * @param rs ResultSet对象
	 * @return list
	 */
	private  static List<Map<String, Object>> resultSetTransferToList(ResultSet rs){
		try{
			ResultSetMetaData md = rs.getMetaData();
			List<Map<String, Object>> list= new ArrayList<>();
			int num = md.getColumnCount();
			while (rs.next()) {
				HashMap<String, Object> map = new HashMap<>();
				for (int i = 1; i <= num; i++) {
					map.put(md.getColumnName(i), rs.getObject(i));
				}
				list.add(map);
			}
			return list;
		}catch (SQLException e){
			e.printStackTrace();
			return null;
		}
	}
	/**
	 * 批量执行sql
	 * @param connection /
	 * @param sqlList /
	 */
	public static void batchExecute(Connection connection, List<String> sqlList) {
		Statement st = null;
		try {
			st = connection.createStatement();
			for (String sql : sqlList) {
				if (sql.endsWith(";")) {
					sql = sql.substring(0, sql.length() - 1);
				}
				st.addBatch(sql);
			}
			st.executeBatch();
		} catch (SQLException throwables) {
			throwables.printStackTrace();
		} finally {
			close(st);
		}
	}

	public static void close(AutoCloseable closeable) {
		if (null != closeable) {
			try {
				closeable.close();
			} catch (Exception e) {
				// 静默关闭
			}
		}
	}
	public static String executeFile(String jdbcUrl, String userName, String password, File sqlFile) {
		Connection connection = getConnection(jdbcUrl, userName, password);
		try {
			batchExecute(connection, readSqlList(sqlFile));
		} catch (Exception e) {
			log.error("sql脚本执行发生异常:{}",e.getMessage());
			return e.getMessage();
		}finally {
			releaseConnection(connection);
		}
		return "success";
	}
	/**
	 * 将文件中的sql语句以；为单位读取到列表中
	 * @param sqlFile /
	 * @return /
	 * @throws Exception e
	 */
	private static List<String> readSqlList(File sqlFile) throws Exception {
		List<String> sqlList = Lists.newArrayList();
		StringBuilder sb = new StringBuilder();
		try (BufferedReader reader = new BufferedReader(new InputStreamReader(
				new FileInputStream(sqlFile), StandardCharsets.UTF_8))) {
			String tmp;
			while ((tmp = reader.readLine()) != null) {
				log.info("line:{}", tmp);
				if (tmp.endsWith(";")) {
					sb.append(tmp);
					sqlList.add(sb.toString());
					sb.delete(0, sb.length());
				} else {
					sb.append(tmp);
				}
			}
			if (!"".endsWith(sb.toString().trim())) {
				sqlList.add(sb.toString());
			}
		}

		return sqlList;
	}

	public static void main(String[] args) {
		DatabaseDto database = new DatabaseDto();
		database.setJdbcUrl("jdbc:mysql://localhost:33062/a_whatever?useUnicode=true&characterEncoding=utf8&serverTimezone=UTC");
		database.setName("x");
		database.setUserName("root");
		database.setPwd("123456");
		boolean b = SqlUtils.testConnection(database.getJdbcUrl(), database.getUserName(), database.getPwd());
		System.out.println(b);
	}
}

import lombok.extern.slf4j.Slf4j;

/**
 * @author /
 */
@Slf4j
@SuppressWarnings({"unchecked","all"})
public enum DataTypeEnum {

    /** mysql */
    MYSQL("mysql", "mysql", "com.mysql.cj.jdbc.Driver", "`", "`", "'", "'"),

    /** oracle */
    ORACLE("oracle", "oracle", "oracle.jdbc.driver.OracleDriver", "\"", "\"", "\"", "\""),

    /** sql server */
    SQLSERVER("sqlserver", "sqlserver", "com.microsoft.sqlserver.jdbc.SQLServerDriver", "\"", "\"", "\"", "\""),

    /** h2 */
    H2("h2", "h2", "org.h2.Driver", "`", "`", "\"", "\""),

    /** phoenix */
    PHOENIX("phoenix", "hbase phoenix", "org.apache.phoenix.jdbc.PhoenixDriver", "", "", "\"", "\""),

    /** mongo */
    MONGODB("mongo", "mongodb", "mongodb.jdbc.MongoDriver", "`", "`", "\"", "\""),

    /** sql4es */
    ELASTICSEARCH("sql4es", "elasticsearch", "nl.anchormen.sql4es.jdbc.ESDriver", "", "", "'", "'"),

    /** presto */
    PRESTO("presto", "presto", "com.facebook.presto.jdbc.PrestoDriver", "", "", "\"", "\""),

    /** moonbox */
    MOONBOX("moonbox", "moonbox", "moonbox.jdbc.MbDriver", "`", "`", "`", "`"),

    /** cassandra */
    CASSANDRA("cassandra", "cassandra", "com.github.adejanovski.cassandra.jdbc.CassandraDriver", "", "", "'", "'"),

    /** click house */
    CLICKHOUSE("clickhouse", "clickhouse", "ru.yandex.clickhouse.ClickHouseDriver", "", "", "\"", "\""),

    /** kylin */
    KYLIN("kylin", "kylin", "org.apache.kylin.jdbc.Driver", "\"", "\"", "\"", "\""),

    /** vertica */
    VERTICA("vertica", "vertica", "com.vertica.jdbc.Driver", "", "", "'", "'"),

    /** sap */
    HANA("sap", "sap hana", "com.sap.db.jdbc.Driver", "", "", "'", "'"),

    /** impala */
    IMPALA("impala", "impala", "com.cloudera.impala.jdbc41.Driver", "", "", "'", "'");

    private String feature;
    private String desc;
    private String driver;
    private String keywordPrefix;
    private String keywordSuffix;
    private String aliasPrefix;
    private String aliasSuffix;

    private static final String JDBC_URL_PREFIX = "jdbc:";

    DataTypeEnum(String feature, String desc, String driver, String keywordPrefix, String keywordSuffix, String aliasPrefix, String aliasSuffix) {
        this.feature = feature;
        this.desc = desc;
        this.driver = driver;
        this.keywordPrefix = keywordPrefix;
        this.keywordSuffix = keywordSuffix;
        this.aliasPrefix = aliasPrefix;
        this.aliasSuffix = aliasSuffix;
    }

    public static DataTypeEnum urlOf(String jdbcUrl) {
        String url = jdbcUrl.toLowerCase().trim();
        for (DataTypeEnum dataTypeEnum : values()) {
            if (url.startsWith(JDBC_URL_PREFIX + dataTypeEnum.feature)) {
                try {
                    Class<?> aClass = Class.forName(dataTypeEnum.getDriver());
                    if (null == aClass) {
                        throw new RuntimeException("Unable to get driver instance for jdbcUrl: " + jdbcUrl);
                    }
                } catch (ClassNotFoundException e) {
                    throw new RuntimeException("Unable to get driver instance: " + jdbcUrl);
                }
                return dataTypeEnum;
            }
        }
        return null;
    }

    public String getFeature() {
        return feature;
    }

    public String getDesc() {
        return desc;
    }

    public String getDriver() {
        return driver;
    }

    public String getKeywordPrefix() {
        return keywordPrefix;
    }

    public String getKeywordSuffix() {
        return keywordSuffix;
    }

    public String getAliasPrefix() {
        return aliasPrefix;
    }

    public String getAliasSuffix() {
        return aliasSuffix;
    }
}