proxool数据库连接池用法

        今天给大家介绍一种新的数据连接池实现方式——proxool数据库连接池，这是一个健壮、易用的连接池。以下通过一个Demo说明一下怎样使用：

        项目结构例如以下：

        DBLink.java文件里的代码：

package com.ghj.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class DBLink {
	
	private Connection connection;
	private PreparedStatement preparedStatement;
	private ResultSet resultSet;

	static{
		try {
			Class.forName("org.logicalcobwebs.proxool.ProxoolDriver");	//载入驱动 
		}catch (ClassNotFoundException e) {
			System.out.println("载入驱动失败！");
			e.printStackTrace();
		}
	}
	
	private Connection getConn(){
		try {
			if(connection==null||connection.isClosed()){
				connection = DriverManager.getConnection("proxool.mysqlsource");	//获取连接 
			}
		} catch (SQLException e) {
			System.out.println("获取连接失败。");
			e.printStackTrace();
		}
		return connection;
	}
	
	public List<Map<String, Object>> select(String sql, Object [] params){
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		try{
			connection = this.getConn();
			preparedStatement = connection.prepareStatement(sql);
			if(params != null){
				for(int i =0; i<params.length; i++){
					preparedStatement.setObject(i+1, params[i]);//逐一给问号赋值
				}
			}
			resultSet = preparedStatement.executeQuery();
			ResultSetMetaData rsmd = resultSet.getMetaData();
			int columnCount = rsmd.getColumnCount();
			while(resultSet.next()){
				Map<String, Object> map = new HashMap<String, Object>();
				for(int i =1; i<=columnCount;i++){
					String columnName = rsmd.getColumnName(i);
					Object obj = null;
					if(rsmd.getColumnTypeName(i).equals("DATE")){
						obj = resultSet.getString(columnName);
					}else{
						obj = resultSet.getObject(columnName);
					}
					map.put(columnName, obj);
				}
				list.add(map);
			}
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			close();
		}
		return list;
	}
	
	private void close(){
		try {
			if(resultSet != null){
				resultSet.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			if(preparedStatement != null){
				preparedStatement.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}

        proxool.xml文件里的代码：

<?xml version="1.0" encoding="UTF-8"?>
<proxool-config>
	<proxool>
		<alias>mysqlsource</alias>
		<driver-url>jdbc:mysql://localhost:3306/test</driver-url>
		<driver-class>com.mysql.jdbc.Driver</driver-class>
		<driver-properties>
			<property name="user" value="root" />
			<property name="password" value="root" />
		</driver-properties>
		<!-- 保留线程处于睡眠状态的最长时间 -->
		<house-keeping-sleep-time>60000</house-keeping-sleep-time>
		<maximum-connection-count>30</maximum-connection-count>
		<minimum-connection-count>10</minimum-connection-count>
		<!-- 连接池中可用的连接数量（空暇状态） -->
		<prototype-count>5</prototype-count>
		<maximum-new-connections>1</maximum-new-connections>
		<simultaneous-build-throttle>1</simultaneous-build-throttle>
		<!-- 连接池使用状态 -->
		<statistics>30s,10m,1d</statistics>
		<!-- 连接池日志类型 -->
		<statistics-log-level>ERROR</statistics-log-level>
		<!-- 假设为true,那么每一个被运行的SQL语句将会在运行期被log记录(DEBUG LEVEL) -->
		<trace>true</trace>
		
		<house-keeping-test-sql>select CURRENT_DATE</house-keeping-test-sql>
	</proxool>
</proxool-config>

        web.xml文件里的代码：

<?xml version="1.0" encoding="UTF-8"?>
<web-app version="3.0" xmlns="http://java.sun.com/xml/ns/javaee"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://java.sun.com/xml/ns/javaee 
	http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd">

	<!-- 连接池 -->
	<servlet>
		<servlet-name>ServletConfigurator</servlet-name>
		<servlet-class>org.logicalcobwebs.proxool.configuration.ServletConfigurator</servlet-class>
		<init-param>
			<param-name>xmlFile</param-name>
			<param-value>WEB-INF/proxool.xml</param-value>
		</init-param>
		<load-on-startup>1</load-on-startup>
	</servlet>
	
	<welcome-file-list>
		<welcome-file>index.jsp</welcome-file>
	</welcome-file-list>
</web-app>

        index.jsp文件里的代码：

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@page import="com.ghj.util.DBLink,java.util.List,java.util.Map"%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
	<head>
		<title>首页</title>
	</head>

	<body>
		<table width="100%" border="1px">
			<tr>
				<th>序号	</th><th>班级名称</th><th>开班日期</th><th>结束日期</th><th>状态</th><th>操作</th>
			</tr>
			<%
				String sql = "SELECT id, class_name, begin_date, end_date, state FROM class ";
				DBLink db = new DBLink();
				List<Map<String, Object>> list = db.select(sql, null);
				int index = 0;
				for (Map<String, Object> map : list) {
					index = index + 1;
					int id = Integer.parseInt(map.get("id").toString());
					String className = map.get("class_name").toString();
					String beginDate = map.get("begin_date").toString();
					String endDate = map.get("end_date").toString();
					int state = Integer.parseInt(map.get("state").toString());
					String stateName = (state == 0 ? "未启用" : "启用");
			%>
			<tr align="center">
				<td><%=index%></td><td><%=className%></td><td><%=beginDate%></td><td><%=endDate%></td><td><%=stateName%></td><td>【<a href="###" onclick="">改动</a>】【<a href="###" onclick="">查看</a>】【<a href="###" onclick="">删除</a>】</td>
			</tr>
			<%
				}
			%>
		</table>
	</body>
</html>

        【0分下载演示样例】