mysql jdbc druid_JDBC使用Druid作为连接池

1. jdbc.properties

driverClassName=com.mysql.jdbc.Driver

url=jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=UTF-8

username=admin

password=admin

filters=stat

initialSize=100

maxActive=300

maxWait=60000

timeBetweenEvictionRunsMillis=60000

minEvictableIdleTimeMillis=300000

validationQuery=SELECT 1

testWhileIdle=true

testOnBorrow=false

testOnReturn=false

poolPreparedStatements=false

rewriteBatchedStatements=true

maxPoolPreparedStatementPerConnectionSize=200

2. 常量类

package conf;

public class SysConstants {

/** jdbc配置文件路径(src/main/resources) **/

public static final String JDBC_CONF_FILE = "jdbc.properties";

}

3. Jdbc使用Druid管理连接

package jdbc;

import java.io.IOException;

import java.sql.Connection;

import java.sql.SQLException;

import java.util.Properties;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

import com.alibaba.druid.pool.DruidDataSource;

import com.alibaba.druid.pool.DruidDataSourceFactory;

import conf.SysConstants;

@Deprecated

public class JdbcConnectionPool {

public static Logger logger = LoggerFactory.getLogger(JdbcConnectionPool.class);

private static ThreadLocal connectionThreadLocal = new ThreadLocal();

private static DruidDataSource druidDataSource = null;

static {

Properties properties = loadPropertiesFile(SysConstants.JDBC_CONF_FILE);

try {

druidDataSource = (DruidDataSource) DruidDataSourceFactory.createDataSource(properties);

} catch (Exception e) {

logger.error("[JDBC Exception] --> "

+ "Failed to configured the Druid DataSource, the exceprion message is:" + e.getMessage());

}

}

public static Connection getConnection() {

Connection connection = connectionThreadLocal.get();

try {

if(null == connection){

connection = druidDataSource.getConnection();

connectionThreadLocal.set(connection);

}

} catch (SQLException e) {

logger.error("[JDBC Exception] --> "

+ "Failed to create a connection, the exceprion message is:" + e.getMessage());

}

return connection;

}

public static void closeConnection() {

Connection connection = connectionThreadLocal.get();

if(null != connection){

try {

connection.close();

connectionThreadLocal.remove();

} catch (SQLException e) {

logger.error("[JDBC Exception] --> "

+ "Failed to close the DruidPooledConnection, the exceprion message is:" + e.getMessage());

}

}

}

public static void startTransaction() {

Connection conn=connectionThreadLocal.get();

try{

if(conn==null){

conn=getConnection();

connectionThreadLocal.set(conn);

}

conn.setAutoCommit(false);

}catch(Exception e){

logger.error("[JDBC Exception] --> "

+ "Failed to start the transaction, the exceprion message is:" + e.getMessage());

}

}

public static void commit(){

try{

Connection conn=connectionThreadLocal.get();

if(null!=conn){

conn.commit();

}

}catch(Exception e){

logger.error("[JDBC Exception] --> "

+ "Failed to commit the transaction, the exceprion message is:" + e.getMessage());

}

}

public static void rollback(){

try{

Connection conn=connectionThreadLocal.get();

if(conn!=null){

conn.rollback();

connectionThreadLocal.remove();

}

}catch(Exception e){

logger.error("[JDBC Exception] --> "

+ "Failed to rollback the transaction, the exceprion message is:" + e.getMessage());

}

}

private static Properties loadPropertiesFile(String fullFile) {

if (null == fullFile || fullFile.equals("")) {

throw new IllegalArgumentException(

"Properties file path can not be null" + fullFile);

}

Properties prop = new Properties();

try {

prop.load(JdbcConnectionPool.class.getClassLoader().getResourceAsStream(fullFile));

} catch (IOException e) {

logger.error("[Properties Exception] --> "

+ "Can not load jdbc properties, the exceprion message is:" + e.getMessage());

}

return prop;

}

}

4. CRUD

package jdbc;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.ResultSetMetaData;

import java.sql.SQLException;

import java.util.List;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

import com.alibaba.fastjson.JSONArray;

import com.alibaba.fastjson.JSONObject;

@Deprecated

public class JdbcTemplate {

public static Logger logger = LoggerFactory.getLogger(JdbcTemplate.class);

private static JdbcTemplate jdbcTemplate = null;

private JdbcTemplate(){ }

public static JdbcTemplate getInstance() {

if(jdbcTemplate == null)

jdbcTemplate = new JdbcTemplate();

return jdbcTemplate;

}

public boolean insert(String sql){

boolean f = false;

PreparedStatement prep = null;

Connection conn = null;

try {

conn = JdbcConnectionPool.getConnection();

prep = conn.prepareStatement(sql);

f = prep.execute();

} catch (SQLException e) {

logger.error("[JDBC Exception] --> "

+ "Can not insert, the exceprion message is:" + e.getMessage());

} finally {

try {

if(null != prep)

prep.close();

} catch (SQLException e) {

logger.error("[JDBC Exception] --> "

+ "Failed to close connection, the exceprion message is:" + e.getMessage());

}

}

return f;

}

public void insert(String sql, List> data){

PreparedStatement prep = null;

Connection conn = null;

try {

conn = JdbcConnectionPool.getConnection();

prep = conn.prepareStatement(sql);

JdbcConnectionPool.startTransaction();

for(int i=0; i

List l = data.get(i);

for(int j=0; j

prep.setObject(j+1, l.get(j));

}

prep.addBatch();

}

prep.executeBatch();

prep.clearBatch();

JdbcConnectionPool.commit();

} catch (SQLException e) {

logger.error("[JDBC Exception] --> "

+ "Can not insert, the exceprion message is:" + e.getMessage());

} finally {

try {

if(null != prep)

prep.close();

JdbcConnectionPool.closeConnection();

} catch (SQLException e) {

logger.error("[JDBC Exception] --> "

+ "Failed to close connection, the exceprion message is:" + e.getMessage());

}

}

return;

}

public String select(String sql){

String r = "";

ResultSet rs=null;

PreparedStatement prep = null;

Connection conn = null;

try {

conn = JdbcConnectionPool.getConnection();

prep = conn.prepareStatement(sql);

rs = prep.executeQuery();

r = ResultSetToJson(rs);

} catch (SQLException e) {

logger.error("[JDBC Exception] --> "

+ "Can not select, the exceprion message is:" + e.getMessage());

} finally {

try {

if(null != rs)

rs.close();

if(null != prep)

prep.close();

JdbcConnectionPool.closeConnection();

} catch (SQLException e) {

logger.error("[JDBC Exception] --> "

+ "Failed to close connection, the exceprion message is:" + e.getMessage());

}

}

return r;

}

public static String ResultSetToJson(ResultSet rs){

JSONArray array = new JSONArray();

try {

ResultSetMetaData metaData = rs.getMetaData();

int columnCount = metaData.getColumnCount();

while (rs.next()) {

JSONObject jsonObj = new JSONObject();

for (int i = 1; i <= columnCount; i++) {

String columnName =metaData.getColumnLabel(i);

String value = rs.getString(columnName);

jsonObj.put(columnName, value);

}

array.add(jsonObj);

}

} catch (SQLException e) {

logger.error("[ResultSetToJson Exception] --> "

+ "Failed to covert ResultSet Data to Json, the exceprion message is:" + e.getMessage());

}

return array.toString();

}

}

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值