hikaricp mysql_HikariCP 个人实例

pom依赖

com.zaxxer

HikariCP

2.7.2

配置文件

spring.mysql.devcenter.datasource.url=jdbc:mysql://XXX.XX.XX.XX:XXXX/XXXXXXXXXXXX?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true

spring.mysql.devcenter.datasource.username=XXXXXX

spring.mysql.devcenter.datasource.password=XXXXXX

spring.mysql.devcenter.datasource.driverClassName=com.mysql.jdbc.Driver

java实现

package com.hikbigscreen.common;

import com.zaxxer.hikari.HikariConfig;

import com.zaxxer.hikari.HikariDataSource;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

import java.io.IOException;

import java.io.InputStream;

import java.sql.Connection;

import java.sql.SQLException;

import java.util.Properties;

public class HikariPoolManager {

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

private static final String DB_CONFIG_FILE = "/application-jdbc.properties";

// 数据库服务器addr

private static String db_url = null;

// 数据库登录用户名

private static String db_username = null;

// 数据库登录密码

private static String db_password = null;

// 数据库驱动

private static String driverClassName = null;

// 数据库连接数

private static short db_max_conn = 0;

// 数据库连接端口

private static short db_port = 0;

// 数据库名称

private static String db_name = null;

private static HikariDataSource dataSource;

private static HikariPoolManager hikariPoolManager;

/**

* 单例模式

*

* @return

*/

public static HikariPoolManager getInstance() {

if (hikariPoolManager == null) hikariPoolManager = new HikariPoolManager();

return hikariPoolManager;

}

/**

* 启动

*

* @throws IOException

* @throws SQLException

*/

public static void start() throws IOException, SQLException {

logger.info("连接池初始化Start!!!");

Properties properties = new Properties();

InputStream in = HikariPoolManager.class.getClass().getResourceAsStream(DB_CONFIG_FILE);

properties.load(in);

db_url = String.valueOf(properties.getProperty("spring.mysql.devcenter.datasource.url"));

db_username = String.valueOf(properties.getProperty("spring.mysql.devcenter.datasource.username"));

db_password = String.valueOf(properties.getProperty("spring.mysql.devcenter.datasource.password"));

driverClassName = String.valueOf(properties.getProperty("spring.mysql.devcenter.datasource.driverClassName"));

if (db_url == null || db_url.length() == 0) {

logger.error("配置的数据库ip地址错误!");

System.exit(0);

}

HikariConfig config = new HikariConfig();

config.setJdbcUrl(db_url);

config.setUsername(db_username);

config.setPassword(db_password);

config.setDriverClassName(driverClassName);

config.addDataSourceProperty("cachePrepStmts", "true");

config.addDataSourceProperty("prepStmtCacheSize", "250");

config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");

// 设置连接超时为8小时

config.setConnectionTimeout(8 * 60 * 60);

dataSource = new HikariDataSource(config);

logger.info("连接池初始化End!!!");

}

/**

* 打开连接

*

* @return

* @throws SQLException

*/

public synchronized static Connection getConnection() throws SQLException {

try {

return dataSource.getConnection();

} catch (SQLException e) {

logger.error("error:" + e.getMessage());

dataSource.close();

return null;

}

}

/**

* 关闭连接

*

* @return

* @throws SQLException

*/

public boolean stop() throws SQLException {

dataSource.close();

return true;

}

}

package com.hikbigscreen.common;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

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

*

* @author AdiandianquanquanA

*/

@Component

public class CommonComponent {

protected Logger logger = LoggerFactory.getLogger(this.getClass());

/**

* key位填类型,value位填传参的值

*

* @param sql

* @param param

* @return

*/

// 获取JDBC的数据,并返回list数据,每条信息放在Map中

public List> getJdbcData(String sql, Object[] param) {

Connection conn = null;

PreparedStatement pstmt = null;

List> list = new ArrayList>();

try {

conn = HikariPoolManager.getConnection();

pstmt = conn.prepareStatement(sql);

if (param != null) {

if (param.length > 0) {

for (int i = 0; i < param.length; i++) {

pstmt.setObject(i + 1, param[i]);

}

}

}

ResultSet rs = pstmt.executeQuery();

// 获取列信息

ResultSetMetaData columns = rs.getMetaData();

// 列数量

int columnNum = columns.getColumnCount();

while (rs.next()) {

Map resultMap = new HashMap();

// 将结果返回成Map，key为列表名，value为该字段的值

for (int j = 1; j <= columnNum; j++) {

resultMap.put(columns.getColumnName(j), rs.getString(j));

}

list.add(resultMap);

}

return list;

} catch (Exception e) {

logger.error("sql: " + sql);

logger.error("Exception: " + e.getMessage(), e);

return list;

} finally {

try {

if (conn != null) {

conn.close();

conn = null;

}

if (pstmt != null) {

pstmt.close();

}

} catch (SQLException e) {

logger.error("sql: " + sql);

logger.error("SQLException: " + e.getMessage(), e);

}

}

}

}

package com.hikbigscreen;

import com.hzcominfo.hik.hikbigscreen.common.HikariPoolManager;

import org.springframework.boot.SpringApplication;

import org.springframework.boot.autoconfigure.SpringBootApplication;

import org.springframework.boot.autoconfigure.solr.SolrAutoConfiguration;

import org.springframework.boot.web.support.SpringBootServletInitializer;

import java.io.IOException;

import java.sql.SQLException;

@SpringBootApplication(exclude = {SolrAutoConfiguration.class})

public class Application extends SpringBootServletInitializer {

public static void main(String[] args) throws IOException, SQLException {

SpringApplication app = new SpringApplication(Application.class);

app.setWebEnvironment(true);

HikariPoolManager.start();

app.run(args);

}

}