java使用drui作为数据库连接池

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,然后利用数据库连接的帮助类,获取连接进行数据库的操作





  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值