初学proxool

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

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

proxool.xml配置文件：

<?xml version="1.0" encoding="UTF-8"?><!--the proxool configuration can be embedded within your ownapplication'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;// constructorprivate 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) {// fieldsField[] fields = UserInfo.class.getDeclaredFields();Field field = null;// for eachfor (int i = 0; i < fields.length; i++) {field = fields[i];String name = field.getName();Object value = null;Class type = field.getType();// try catchtry {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 infoUserInfo 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 sqlrs = pstmt.executeQuery();while (rs.next()) {userInfo = new UserInfo();Field field = null;for (int i = 0; i < fields.length; i++) {field = fields[i];// get field namefieldName = field.getName();// get field typetype = field.getType();// accessiblefield.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