c3p0 数据库连接池配置封装使用

1. c3p0 数据库连接池配置封装使用

引用jar包

  

工程目录结构：

package com.feng.test.c3p0;



import com.mchange.v2.c3p0.ComboPooledDataSource;


import java.sql.Connection;  
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;  
import java.util.UUID;
  
  

public class c3p0Demo2 {
	public static void main(String[] args) {
		try {
			DBUtil_BO dbBo = new DBUtil_BO();
			dbBo.conn=C3p0Utils.getConnection();//取用一个连接
			String sql = "select * from kfk_server";
			dbBo.st=dbBo.conn.prepareStatement(sql);//预处理sql语句
			//此时dbBo对象已经封装了一个数据库连接以及要执行的操作   		
			DBUtils.executeQuery(dbBo);//通过数据库操作类来执行这个操作封装类，结果封装回这个操作封装类
			//从dbBo类提取操作结果
			if (dbBo.rs.next()){
			    String str =dbBo.rs.getString("ZOOKEEPER_CONNECT");
			    System.out.println("--------------------------"+str);
			}		
			//结果集遍历完了，手动释放连接回连接池
			DBUtils.realseSource(dbBo);
		} catch (Exception e) {
			e.printStackTrace();
		}		
	}	
}


class C3p0Utils {
    static org.apache.log4j.Logger logger=org.apache.log4j.Logger.getLogger(C3p0Utils.class.getName());
    
    //通过标识名来创建相应连接池
    static ComboPooledDataSource dataSource=new ComboPooledDataSource("c3p0");
    //从连接池中取用一个连接
    public static Connection getConnection() throws Exception{
        try {
            return dataSource.getConnection();
            
        } catch (Exception e) {
            logger.error("Exception in C3p0Utils!", e);            
            throw new Exception("数据库连接出错!",e);
        }
    }    
    //释放连接回连接池
     public static void close(Connection conn,PreparedStatement pst,ResultSet rs) throws Exception{  
            if(rs!=null){  
                try {  
                    rs.close();  
                } catch (SQLException e) {  
                    logger.error("Exception in C3p0Utils!", e);
                    throw new Exception("数据库连接出错!",e);       
                }  
            }  
            if(pst!=null){  
                try {  
                    pst.close();  
                } catch (SQLException e) {  
                    logger.error("Exception in C3p0Utils!", e);
                    throw new Exception("数据库连接出错!",e);  
                }  
            }  
      
            if(conn!=null){  
                try {  
                    conn.close();  
                } catch (SQLException e) {  
                    logger.error("Exception in C3p0Utils!", e);
                    throw new Exception("数据库连接出错!",e);   
                }  
            }  
        }  
}
 
class DBUtil_BO {   
    public Connection conn = null;
    public PreparedStatement st = null;
    public ResultSet rs = null;
    public DBUtil_BO() {
        super();
    }    
}

class DBUtils {
    static org.apache.log4j.Logger logger=org.apache.log4j.Logger.getLogger(DBUtils.class.getName());
    
    
    private static void realseSource( Connection _conn, PreparedStatement _st,ResultSet _rs){    	
        try {
			C3p0Utils.close(_conn,_st,_rs);
		} catch (Exception e) {			
			logger.error("",e);
		}           
    }

       public static void realseSource(DBUtil_BO _vo){    
           if(_vo!=null){
               realseSource(_vo.conn, _vo.st, _vo.rs);
           }        
       }
       //注意：查询操作完成后，因为还需提取结果集中信息，所以仍保持连接，在结果集使用完后才通过DBUtils.realseSource()手动释放连接
       public static void executeQuery(DBUtil_BO vo)
       {        
           try{
               vo.rs = vo.st.executeQuery();
           }catch (SQLException e){            
               realseSource(vo);
               String uuid=UUID.randomUUID().toString();
               logger.error("UUID:"+uuid+", SQL语法有误: ",e);
           }    
       }
       
      //而update操作完成后就可以直接释放连接了，所以在方法末尾直接调用了realseSourse()
       public static  void executeUpdate(DBUtil_BO vo)
       {

           Connection conn = vo.conn;
           PreparedStatement st = vo.st;
           try {
               st.executeUpdate();
           } catch (SQLException e) {
               realseSource(conn, st, null);        
               String uuid=UUID.randomUUID().toString();
               logger.error("UUID:"+uuid+", SQL语法有误: ",e);               
           }
           realseSource(conn, st,null );                

       }
}

<!-- <?xml version="1.0" encoding="UTF-8"?> -->
<c3p0-config>
  <default-config>
    <property name="automaticTestTable">con_test</property>
    <property name="checkoutTimeout">30000</property>
    <property name="idleConnectionTestPeriod">30</property>
    <property name="initialPoolSize">10</property>
    <property name="maxIdleTime">30</property>
    <property name="maxPoolSize">100</property>
    <property name="minPoolSize">10</property>
    <property name="maxStatements">200</property>

    <user-overrides user="test-user">
      <property name="maxPoolSize">10</property>
      <property name="minPoolSize">1</property>
      <property name="maxStatements">0</property>
    </user-overrides>

  </default-config>

  <!-- This app is massive! -->
  <named-config name="c3p0"> 
  	<!-- 指定连接数据源基本属性 -->
    <property name="user">monitor3</property>
    <property name="password">mnt2t3!jsdx</property>
    <property name="driverClass">oracle.jdbc.driver.OracleDriver</property>
    <property name="jdbcUrl">jdbc:oracle:thin:@(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1621)) (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.2)(PORT = 1621)) (LOAD_BALANCE = off) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = bondb_jf) (FAILOVER_MODE = (TYPE = SELECT) (METHOD = BASIC) (RETRIES = 30) (DELAY = 20) ) ) )</property>
    
    
  
    <property name="acquireIncrement">50</property>
    <property name="initialPoolSize">100</property>
    <property name="minPoolSize">50</property>
    <property name="maxPoolSize">1000</property>

    <!-- intergalactoApp adopts a different approach to configuring statement caching -->
    <property name="maxStatements">0</property> 
    <property name="maxStatementsPerConnection">5</property>

    <!-- he's important, but there's only one of him -->
    <user-overrides user="master-of-the-universe"> 
      <property name="acquireIncrement">1</property>
      <property name="initialPoolSize">1</property>
      <property name="minPoolSize">1</property>
      <property name="maxPoolSize">5</property>
      <property name="maxStatementsPerConnection">50</property>
    </user-overrides>
  </named-config>
</c3p0-config>

运行结果：