初学proxool

菜鸟一枚，学习proxool配置管理数据库连接。

新建了一个application程序测试proxool数据库连接。

proxool.xml配置文件：

<?xml version="1.0" encoding="UTF-8"?>
	<!--
		the proxool configuration can be embedded within your own
		application's. Anything outside the "proxool" tag is ignored.
	-->
<something-else-entirely>
	<proxool>
		<alias>dbname</alias> <!--数据源的别名-->
		<driver-url>jdbc:oracle:thin:@127.0.0.1:1521:orcl</driver-url><!--url连接串-->
		<driver-class>oracle.jdbc.driver.OracleDriver</driver-class> <!--驱动类-->
		<driver-properties>
			<property name="user" value="zen" /> <!--用户名-->
			<property name="password" value="sa" /><!--密码-->
		</driver-properties> 
		<!--最大连接数(默认5个),超过了这个连接数,再有请求时,就排在队列中等候,最大的等待请求数由maximum-new-connections决定 -->
		<maximum-connection-count>100</maximum-connection-count> 
		<!--最小连接数(默认2个)-->
		<minimum-connection-count>10</minimum-connection-count> 
		<!--proxool自动侦察各个连接状态的时间间隔(毫秒),侦察到空闲的连接就马上回收,超时的销毁 默认30秒-->
		<house-keeping-sleep-time>90000</house-keeping-sleep-time>
		<!--没有空闲连接可以分配而在队列中等候的最大请求数,超过这个请求数的用户连接就不会被接受-->
		<maximum-new-connections>10</maximum-new-connections> 
		<!--最少保持的空闲连接数(默认2个)-->
		<prototype-count>5</prototype-count> 
		<!--在使用之前测试-->
		<test-before-use>true</test-before-use>
		<!--用于保持连接的测试语句 -->
		<house-keeping-test-sql>select sysdate from dual</house-keeping-test-sql>
	</proxool>
</something-else-entirely> 

oracle数据库中，测试表userinfo对应的bean：userinfo.java

package cn.proxool;

import java.util.Date;

/**
 * @author xxx
 *
 */
public class UserInfo {
	 private int userid;
	   private String name;
	   private String userPwd;
	   private Date birthday;

	   public UserInfo(int id)
	   {
	       this.userid=id;
	   }
	    public UserInfo(int userid, String userName, String userPwd, Date birthday)
	    {
	        super();
	        this.userid = userid;
	        this.name = userName;
	        this.userPwd = userPwd;
	        this.birthday = birthday;
	    }
	    //构造器
	    public UserInfo()
	    {
	    }
		public int getUserid() {
			return userid;
		}
		public void setUserid(int userid) {
			this.userid = userid;
		}
		public String getUserName() {
			return name;
		}
		public void setUserName(String userName) {
			this.name = userName;
		}
		public String getUserPwd() {
			return userPwd;
		}
		public void setUserPwd(String userPwd) {
			this.userPwd = userPwd;
		}
		public Date getBirthday() {
			return birthday;
		}
		public void setBirthday(Date birthday) {
			this.birthday = birthday;
		}
	    
}

数据库连接控制类:SqlTool.java

package cn.proxool;

import java.sql.*;
import org.logicalcobwebs.proxool.configuration.JAXPConfigurator;

public final class SqlTool{

 private SqlTool(){
 }
 
 private static void init(){
  try {
   JAXPConfigurator.configure("src/proxool.xml",false);// proxool.xml所在目录
     }
     catch (Exception ex) {
      ex.printStackTrace();
     }
 }
 /**
     * get Connection
     */
 public static Connection getConnection(){
  Connection conn=null;
  try{
   init();
   conn=DriverManager.getConnection("proxool.dbname");
  }
  catch(Exception e){
   conn=null;
  }
  return conn;
 }
 
 /**
     * closeConnection
     */
 public static void closeConnection(Connection conn){
  try{
   if((conn!=null)&&(!conn.isClosed())){
    conn.close();
   }
  }
  catch(SQLException e){
   System.out.println("close Connection error!");
  }
 }
 
 /**
     * closePreparedStatement
     */
 public static void closePreparedStatement(PreparedStatement pstmt){
  try{
   if(pstmt!=null){
    pstmt.close();
   }
  }
  catch(SQLException e){
   System.out.println("close PreparedStatement error!");
  }
 }
 
 /**
     * closeResultSet
     */
 public static void closeResultSet(ResultSet rs){
  try {
   if(rs!=null){
    rs.close();
   }
     }
     catch (Exception ex) {
      System.out.println("close ResultSet 异常!");
     }
 }
 
 
 /**
     * 测试main
     */
 public static void main(String[] args)throws Exception{
  java.sql.Connection conn=SqlTool.getConnection();
  System.out.println (conn==null?"NULL":"NOT NULL");
 }
}

数据库操作类：UserTool.java

package cn.proxool;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;

import org.apache.log4j.Logger;

public class UserTool {
	/** log */
	private static Logger logger = Logger.getLogger(UserTool.class);
	private static UserTool instance;

	// constructor
	private UserTool() {
	}

	/**
	 * get instance
	 * 
	 * @return the single instance
	 */
	public static UserTool getInstance() {
		if (instance == null) {
			instance = new UserTool();
		}
		return instance;
	}

	/**
	 * print the user info
	 * 
	 * @param userInfo
	 * @throws IllegalAccessException
	 * @throws IllegalArgumentException
	 */
	@SuppressWarnings("unchecked")
	public void printUserInfo(UserInfo userInfo) {
		// fields
		Field[] fields = UserInfo.class.getDeclaredFields();

		Field field = null;
		// for each
		for (int i = 0; i < fields.length; i++) {
			field = fields[i];
			String name = field.getName();
			Object value = null;
			Class type = field.getType();
			// try catch
			try {
				field.setAccessible(true);
				if (type == int.class) {
					value = (int) field.getInt(userInfo);
				} else if (type == String.class) {
					value = (String) field.get(userInfo);
				} else if (type == Date.class) {
					value = (Date) field.get(userInfo);
				}
				logger.info(name + "->" + value);
			} catch (IllegalArgumentException e) {
				e.printStackTrace();
			} catch (IllegalAccessException e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * find User Info By Id
	 * @param id
	 * @return UserInfo
	 */
	@SuppressWarnings("unchecked")
	public UserInfo findUserInfoById(int id) {//该方法这样写，源于数据库字段和对应bean的字段是一样的
		Connection connection = SqlTool.getConnection();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		if (null == connection) {
			logger.warn("connection is null!");
			return null;
		}
		// user info
		UserInfo userInfo = null;
		Field[] fields = UserInfo.class.getDeclaredFields();
		Class type = null;
		String fieldName = null;
		try {
			pstmt = connection
					.prepareStatement("select * from userinfo where USERID=?");
			pstmt.setInt(1, id);
			// execute sql
			rs = pstmt.executeQuery();

			while (rs.next()) {
				userInfo = new UserInfo();
				Field field = null;
				for (int i = 0; i < fields.length; i++) {
					field = fields[i];
					// get field name
					fieldName = field.getName();
					// get field type
					type = field.getType();
					// accessible
					field.setAccessible(true);
					try {
						if (type == int.class) {
							field.setInt(userInfo, rs.getInt(fieldName));
						} else if (type == String.class) {
							field.set(userInfo,rs.getString(fieldName));
						}else if(type == Date.class){
							field.set(userInfo, rs.getDate(fieldName));
						}
					} catch (Exception e) {
						e.printStackTrace();
					}
				}

			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			SqlTool.closeResultSet(rs);
			SqlTool.closePreparedStatement(pstmt);
			SqlTool.closeConnection(connection);
		}

		return userInfo;
	}

	public static void main(String[] args) {
		logger.info("UserTool start...");
		UserTool tool = UserTool.getInstance();
		UserInfo userInfo = tool.findUserInfoById(1);
		tool.printUserInfo(userInfo);
		logger.info("-----> end");
	}
}

所需jar包：proxool-0.9.1.jar,log4j.jar,commons-logging.jar,commons-pool.jar,proxool-cglib.jar,ojdbc14.jar