欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

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

程序员文章站 2024-03-20 08:50:22
...

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

引用jar包

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

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

工程目录结构:

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


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>



运行结果:


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

相关标签: c3p0