java使用drui作为数据库连接池
1,创建一个web项目:
2,在src下面创建一个数据库的属性文件以及drui的基本配置:druidconfig.properties,内容如下
#\u9A71\u52A8\u8DEF\u5F84
driverClassName=com.mysql.jdbc.Driver
#\u6570\u636E\u5E93\u8FDE\u63A5\u5730\u5740
jdbcUrl=jdbc:mysql://x.x.x.x:3306/x
#\u6570\u636E\u5E93\u7528\u6237\u540D
username=xxxxx
#\u6570\u636E\u5E93\u5BC6\u7801
password=xxxxx
#
#filters=stat,log4j
#\u6700\u5927\u8FDE\u63A5\u6C60\u6570\u91CF
maxActive=20
#\u521D\u59CB\u5316\u65F6\u5EFA\u7ACB\u7269\u7406\u8FDE\u63A5\u7684\u4E2A\u6570
initialSize=4
#\u83B7\u53D6\u8FDE\u63A5\u65F6\u6700\u5927\u7B49\u5F85\u65F6\u95F4\uFF0C\u5355\u4F4D\u6BEB\u79D2
maxWait=60000
#\u6700\u5C0F\u8FDE\u63A5\u6C60\u6570\u91CF
minIdle=4
#1) Destroy\u7EBF\u7A0B\u4F1A\u68C0\u6D4B\u8FDE\u63A5\u7684\u95F4\u9694\u65F6\u95F4 2) testWhileIdle\u7684\u5224\u65AD\u4F9D\u636E
timeBetweenEvictionRunsMillis=60000
#Destory\u7EBF\u7A0B\u4E2D\u5982\u679C\u68C0\u6D4B\u5230\u5F53\u524D\u8FDE\u63A5\u7684\u6700\u540E\u6D3B\u8DC3\u65F6\u95F4\u548C\u5F53\u524D\u65F6\u95F4\u7684\u5DEE\u503C\u5927\u4E8EminEvictableIdleTimeMillis\uFF0C\u5219\u5173\u95ED\u5F53\u524D\u8FDE\u63A5\u3002
minEvictableIdleTimeMillis=300000
#\u5EFA\u8BAE\u914D\u7F6E\u4E3Atrue\uFF0C\u4E0D\u5F71\u54CD\u6027\u80FD\uFF0C\u5E76\u4E14\u4FDD\u8BC1\u5B89\u5168\u6027\u3002\u7533\u8BF7\u8FDE\u63A5\u7684\u65F6\u5019\u68C0\u6D4B\uFF0C\u5982\u679C\u7A7A\u95F2\u65F6\u95F4\u5927\u4E8EtimeBetweenEvictionRunsMillis\uFF0C\u6267\u884CvalidationQuery\u68C0\u6D4B\u8FDE\u63A5\u662F\u5426\u6709\u6548\u3002
testWhileIdle=true
#\u7533\u8BF7\u8FDE\u63A5\u65F6\u6267\u884CvalidationQuery\u68C0\u6D4B\u8FDE\u63A5\u662F\u5426\u6709\u6548\uFF0C\u505A\u4E86\u8FD9\u4E2A\u914D\u7F6E\u4F1A\u964D\u4F4E\u6027\u80FD
testOnBorrow=false
#\u5F52\u8FD8\u8FDE\u63A5\u65F6\u6267\u884CvalidationQuery\u68C0\u6D4B\u8FDE\u63A5\u662F\u5426\u6709\u6548\uFF0C\u505A\u4E86\u8FD9\u4E2A\u914D\u7F6E\u4F1A\u964D\u4F4E\u6027\u80FD
testOnReturn=false
#\u662F\u5426\u7F13\u5B58preparedStatement\uFF0C\u4E5F\u5C31\u662FPSCache\u3002PSCache\u5BF9\u652F\u6301\u6E38\u6807\u7684\u6570\u636E\u5E93\u6027\u80FD\u63D0\u5347\u5DE8\u5927\uFF0C\u6BD4\u5982\u8BF4oracle
poolPreparedStatements=true
#\u8981\u542F\u7528PSCache\uFF0C\u5FC5\u987B\u914D\u7F6E\u5927\u4E8E0\uFF0C\u5F53\u5927\u4E8E0\u65F6\uFF0CpoolPreparedStatements\u81EA\u52A8\u89E6\u53D1\u4FEE\u6539\u4E3Atrue\u3002 \u5728Druid\u4E2D\uFF0C\u4E0D\u4F1A\u5B58\u5728Oracle\u4E0BPSCache\u5360\u7528\u5185\u5B58\u8FC7\u591A\u7684\u95EE\u9898\uFF0C\u53EF\u4EE5\u628A\u8FD9\u4E2A\u6570\u503C\u914D\u7F6E\u5927\u4E00\u4E9B\uFF0C\u6BD4\u5982\u8BF4100
maxOpenPreparedStatements=20
#log4j.appender.stdout=log4j.appender.stdout=org.apache.log4j.ConsoleAppender
3,在src目录下创建一个日志配置文件log4j.properties,内容如下:
log4j.logger.druid.sql=warn,stdout
log4j.logger.druid.sql.DataSource=warn,stdout
log4j.logger.druid.sql.Connection=warn,stdout
log4j.logger.druid.sql.Statement=warn,stdout
log4j.logger.druid.sql.ResultSet=warn,stdout
4,在lib目录下导入jar包
5,创建CastUtil类,
package com.szkingdom.db;
/**
* Created by jack on 2015/12/26.
* 转型操作工具类
*/
public class CastUtil {
/*
* 转为String型
* */
public static String castString(Object obj) {
return CastUtil.castString(obj, "");
}
/*
* 转为String型(提供默认值)
* */
public static String castString(Object obj, String defaultValue) {
return obj != null ? String.valueOf(obj) : defaultValue;
}
/*
* 转为double型
* */
public static double castDouble(Object obj) {
return castDouble(obj, (double)0);
}
/*
* 转为double型(提供默认值)
* */
public static double castDouble(Object obj, Double defaultValue) {
double doubleValue = defaultValue;
if (obj != null) {
String strValue = castString(obj);
if (StringUtil.isNotEmpty(strValue)) {
try {
doubleValue = Double.parseDouble(strValue);
} catch (NumberFormatException e) {
defaultValue = defaultValue;
}
}
}
return doubleValue;
}
/*
* 转为long型
* */
public static long castLong(Object obj) {
return castLong(obj, 0);
}
/*
* 转为long型(提供默认值)
* */
public static long castLong(Object obj, long defaultValue) {
long longValue = defaultValue;
if (obj != null) {
String strValue = castString(obj);
if (StringUtil.isNotEmpty(strValue)) {
try {
longValue = Long.parseLong(strValue);
}catch (NumberFormatException e){
longValue=defaultValue;
}
}
}
return longValue;
}
/*
* 转为int型
* */
public static int castInt(Object obj){
return castInt(obj,0);
}
/*
* 转为int型(提供默值)
* */
public static int castInt(Object obj,int defaultValue){
int intValue=defaultValue;
if (obj!=null){
String strValue=castString(obj);
if(StringUtil.isNotEmpty(strValue)){
try {
intValue=Integer.parseInt(strValue);
}catch (NumberFormatException e){
intValue=defaultValue;
}
}
}
return intValue;
}
/*
* 转为boolean型
* */
public static boolean castBoolean(Object obj){
return castBoolean(obj,false);
}
/*
* 转为boolean型(提供默认值)
* */
public static boolean castBoolean(Object obj,boolean defaultValue){
boolean booleanValue=defaultValue;
if(obj!=null){
booleanValue=Boolean.parseBoolean(castString(obj));
}
return booleanValue;
}
}
6,创建PropsUtil类加载属性文件
package com.szkingdom.db;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.Properties;
/**
* Created by jack on 2015/12/26.
* 属性文件工具类
*/
public class PropsUtil {
//private static final Logger LOGGER = LoggerFactory.getLogger(PropsUtil.class);
/*
* 加载属性文件
*
* */
public static Properties loadProps(String fileName) {
Properties properties = null;
InputStream inputStream = null;
try {
inputStream = Thread.currentThread().getContextClassLoader().getResourceAsStream(fileName);
if (inputStream == null) {
throw new FileNotFoundException(fileName + " file is not found!");
}
properties = new Properties();
properties.load(inputStream);
} catch (IOException e) {
//LOGGER.error("load properties file failure", e);
System.out.println("load properties file failure:"+e);
} finally {
if (inputStream != null) {
try {
inputStream.close();
} catch (IOException e) {
//LOGGER.error("close input stream failure", e);
System.out.println("close input stream failure:"+e);
}
}
}
return properties;
}
/*
* 获取字符型属性(默认为空字符串)
*
* */
public static String getString(Properties props, String key) {
return getString(props, key, "");
}
/*
* 获取字符型属性(可指定默认值)
* */
public static String getString(Properties props, String key, String
defaultValue) {
String value = defaultValue;
if (props.containsKey(key)) {
value = props.getProperty(key);
}
return value;
}
/*
* 获取数值类型属性(默认为0)
* */
public static int getInt(Properties props, String key) {
return getInt(props, key, 0);
}
/*
* 获取数值类型属性(可指定默认值)
* */
public static int getInt(Properties props, String key, int defaultValue) {
int value = defaultValue;
if (props.containsKey(key)) {
value = CastUtil.castInt(props.getProperty(key));
}
return value;
}
/*
* 获取布尔型属性(默认值为false)
* */
public static boolean getBoolean(Properties props, String key) {
return getBoolean(props, key, false);
}
/*
* 获取布尔型属性(可指定默认值)
* */
public static boolean getBoolean(Properties props, String key, Boolean defaultValue) {
boolean value = defaultValue;
if (props.containsKey(key)) {
value = CastUtil.castBoolean(props.getProperty(key));
}
return value;
}
}
7,创建StringUtil类,进行字符串判断
package com.szkingdom.db;
/**
* Created by jack on 2015/12/26.
* 字符串工具类
*/
public class StringUtil {
/*
* 判断字符串是否为空
* */
public static boolean isEmpty(String str){
if(str != null){
str=str.trim();
}
//return StringUtils.isEmpty(str);
return "".equals(str);
}
/*
* 判断字符串是否非空
* */
public static boolean isNotEmpty(String str){
return !isEmpty(str);
}
}
8,最后的重头戏,创建数据库帮助类DataSourceUtil,建立数据库连接池
package com.szkingdom.db;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Properties;
import javax.sql.DataSource;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.alibaba.druid.support.logging.Log;
public class DataSourceUtil {
// public static Logger logger = null;
public static DruidDataSource dataSource = null;
// 保证一个线程一个Connection,线程安全
private static final ThreadLocal<Connection> CONNECTION_HOLDER;
// 静态代码块
static {
// logger = Logger.getLogger(DataSourceUtil.class.getName());
// 初始化ThreadLocal变量
CONNECTION_HOLDER = new ThreadLocal<Connection>();
dataSource = getDruidDataSource();
System.out.println("获取DruidDataSource");
}
// 获取DruidDataSource
public static DruidDataSource getDruidDataSource() {
// http://www.cnblogs.com/JavaSubin/p/5294721.html
// http://www.cnblogs.com/nima/p/5718331.html
// 获取DruidDataSource的属性配置文件
// https://github.com/alibaba/druid/wiki/%E9%85%8D%E7%BD%AE_LogFilter
// https://github.com/alibaba/druid/wiki/DruidDataSource%E9%85%8D%E7%BD%AE%E5%B1%9E%E6%80%A7%E5%88%97%E8%A1%A8
Properties properties = PropsUtil.loadProps("druidconfig.properties");
// 创建DruidDataSource
DruidDataSource dataSource = new DruidDataSource();
// 对DruidDataSource设置属性值
dataSource
.setDriverClassName(properties.getProperty("driverClassName"));
dataSource.setUrl(properties.getProperty("jdbcUrl"));
dataSource.setUsername(properties.getProperty("username"));
dataSource.setPassword(properties.getProperty("password"));
dataSource.setMaxActive(Integer.parseInt(properties
.getProperty("maxActive")));
dataSource.setInitialSize(Integer.parseInt(properties
.getProperty("initialSize")));
dataSource
.setMaxWait(Long.parseLong(properties.getProperty("maxWait")));
dataSource.setMinIdle(Integer.parseInt(properties
.getProperty("minIdle")));
dataSource.setTimeBetweenEvictionRunsMillis(Long.parseLong(properties
.getProperty("timeBetweenEvictionRunsMillis")));
dataSource.setMinEvictableIdleTimeMillis(Long.parseLong(properties
.getProperty("minEvictableIdleTimeMillis")));
dataSource.setTestWhileIdle(Boolean.parseBoolean(properties
.getProperty("testWhileIdle")));
dataSource.setTestOnBorrow(Boolean.parseBoolean(properties
.getProperty("testOnBorrow")));
dataSource.setTestOnReturn(Boolean.parseBoolean(properties
.getProperty("testOnReturn")));
dataSource.setPoolPreparedStatements(Boolean.parseBoolean(properties
.getProperty("poolPreparedStatements")));
dataSource.setMaxOpenPreparedStatements(Integer.parseInt(properties
.getProperty("maxOpenPreparedStatements")));
System.out.println("完成设置DruidDataSource参数");
return dataSource;
}
// 获取数据库连接
public static Connection getConnection() {
// 定义连接变量
// Connection connection = null;
//
Connection connection = CONNECTION_HOLDER.get();
//boolean flag = false;
/*if (connection != null) {
try {
flag = connection.isClosed();
System.out.println("80---connection is closed!!!");
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
}*/
if (connection == null ) {
try {
System.out.println("通过CONNECTION_HOLDER.get()方法获取连接为null,下面通过数据库连接池获取连接");
// 获取连接
connection = dataSource.getConnection();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
System.out.println("获取Connection连接失败.!!!");
} finally {
CONNECTION_HOLDER.set(connection);
}
}
return connection;
}
public static void update(int thlsh, String ltnr) {
Connection connection = getConnection();
if (connection == null) {
System.out.println("获取Connection连接失败,不进行update,直接返回!!!");
return;
}
PreparedStatement pstmt = null;
System.out.println("update开始!");
int ltlsh = 0;
String sql = "update message set CONTENT = ? where id=?";
System.out.println("更新的sql语句为:sql->" + sql);
try {
pstmt = connection.prepareStatement(sql);
pstmt.setString(1, "this is dbcp2 test 3333333");
pstmt.setInt(2, 6);
if (pstmt.executeUpdate() > 0) {
// System.out.println("更新id=1的数据成功!");
System.out.println("更新thlsh=" + thlsh + "的聊天内容数据成功!\n聊天内容为:"
+ ltnr);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
// e.printStackTrace();
System.out.println("更新数据异常connection=" + connection);
System.out.println("update t_wx_ltnrk failure:" + e);
throw new RuntimeException(e);
} finally {
CONNECTION_HOLDER.remove();
if (pstmt != null) {
try {
pstmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
public static void main(String arg[]) {
System.out.println("11111111");
Connection connection = null;
try {
for (int i = 0; i < 120; i++) {
System.out.println("获取连接开始i=" + i);
connection = DataSourceUtil.dataSource.getConnection();
System.out.println("获取连接结束i=" + i);
connection.close();
System.out.println("dataSource=" + dataSource);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
// System.out.println("获取连接失败Connection:"+dataSource);
}
// System.out.println("dataSource=" + dataSource);
}
}
9,然后利用数据库连接的帮助类,获取连接进行数据库的操作