入门客AI创业平台(我带你入门，你带我飞行)
博文笔记

C3P0连接池的三种实现方式

创建时间：2016-06-01 投稿人： 浏览次数：17187

用到的jar包：

                 mysql-connector-java-5.1.30-bin.jar

                 c3p0-0.9.5.2.jar

                 mchange-commons-java-0.2.11.jar


C3P0Inner.java 

package com.song.c3p0.db.inner;

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

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class C3P0Inner {
	
	private static ComboPooledDataSource ds;
	
	//静态初始化块进行初始化
	static{
		try {
			ds = new ComboPooledDataSource();//创建连接池实例
			
			ds.setDriverClass("com.mysql.jdbc.Driver");//设置连接池连接数据库所需的驱动
			
			ds.setJdbcUrl("jdbc:mysql://localhost:3306/cloudhospital");//设置连接数据库的URL
			
			ds.setUser("root");//设置连接数据库的用户名
			
			ds.setPassword("admin");//设置连接数据库的密码
			
			ds.setMaxPoolSize(40);//设置连接池的最大连接数
			
			ds.setMinPoolSize(2);//设置连接池的最小连接数
			
			ds.setInitialPoolSize(10);//设置连接池的初始连接数
			
			ds.setMaxStatements(100);//设置连接池的缓存Statement的最大数			
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	//获取与指定数据库的连接
	public static ComboPooledDataSource getInstance(){
		return ds;
	} 

	//从连接池返回一个连接
	public static Connection getConnection(){
		Connection conn = null;
		try {
			conn = ds.getConnection();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return conn;
	}
	
	//释放资源
	public static void realeaseResource(ResultSet rs,PreparedStatement ps,Connection conn){
		if(null != rs){
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		if(null != ps){
			try {
				ps.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		try {
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}

C3P0InnerMain.java

package com.song.c3p0.db.inner;

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

public class C3P0InnerMain {

	public PreparedStatement setStatement(Connection conn,String sql){
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return ps;
	}
	
	public PreparedStatement setParameter(PreparedStatement ps,Object...values){
		try {
			if(null != values){
				for(int i=1;i<=values.length;i++){
					ps.setObject(i, values[i-1]);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return ps;
	}
	
	public static void main(String[] args) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = C3P0Inner.getConnection();
			
			C3P0InnerMain c3P0InnerMain = new C3P0InnerMain();
			
			String sql = "SELECT * FROM tb_user WHERE ID < ? ";
			
			ps = c3P0InnerMain.setStatement(conn, sql);
			ps = c3P0InnerMain.setParameter(ps, new Object[]{20});
			
			rs = ps.executeQuery();
			
			while(rs.next()){
				Object ob = rs.getObject(1);
				Object name = rs.getObject(4);
				System.out.println("ID: " + ob + ",NAME:" + name);
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			//释放资源
			C3P0Inner.realeaseResource(rs, ps, conn);
		}
	}
}

1.从配置文件中逐条获取参数

config.properties

DriverClass = com.mysql.jdbc.Driver
JdbcUrl = jdbc:mysql://localhost:3306/cloudhospital
User = root
Password = admin
MaxPoolSize = 20
MinPoolSize = 2
InitialPoolSize = 5
MaxStatements = 30
MaxIdleTime = 100

C3P0Properties.java

package com.song.c3p0.db.properties;

import java.sql.Connection;
import java.util.Properties;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class C3P0Properties {

	private ComboPooledDataSource cpds;
	
	private static C3P0Properties c3P0Properties;
	
	static{
		c3P0Properties = new C3P0Properties();
	}

	public C3P0Properties() {
		try {
			cpds = new ComboPooledDataSource();
			
			//加载配置文件
			Properties props = new Properties();
			props.load(C3P0Properties.class.getClassLoader().getResourceAsStream("config.properties"));
			
			cpds.setDriverClass(props.getProperty("DriverClass"));
			cpds.setJdbcUrl(props.getProperty("JdbcUrl"));
			cpds.setUser(props.getProperty("User"));
			cpds.setPassword(props.getProperty("Password"));
			
			cpds.setMaxPoolSize(Integer.parseInt(props.getProperty("MaxPoolSize")));
			cpds.setMinPoolSize(Integer.parseInt(props.getProperty("MinPoolSize")));
			cpds.setInitialPoolSize(Integer.parseInt(props.getProperty("InitialPoolSize")));
			cpds.setMaxStatements(Integer.parseInt(props.getProperty("MaxStatements")));
			cpds.setMaxIdleTime(Integer.parseInt(props.getProperty("MaxIdleTime")));
			
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public static C3P0Properties getInstance(){
		return c3P0Properties;
	}
	
	public Connection getConnection(){
		Connection conn = null;
		try {
			conn = cpds.getConnection();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return conn;
	}
}

C3POPropertiesMain.java

package com.song.c3p0.db.properties;

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

public class C3POPropertiesMain {

	public PreparedStatement getPrepareStatement(Connection conn,String sql){
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return ps;
	}
	
	public PreparedStatement setPrepareStatementParameter(PreparedStatement ps,Object... values){
		try {
			if (null != values) {
				for (int i = 1; i <= values.length; i++) {
					ps.setObject(i, values[i - 1]);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return ps;
	}
	
	//释放资源
	public static void realeaseResource(ResultSet rs,PreparedStatement ps,Connection conn){
		if(null != rs){
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		if(null != ps){
			try {
				ps.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		try {
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public static void main(String[] args) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = C3P0Properties.getInstance().getConnection();
			
			String sql = "SELECT * FROM tb_user WHERE ID < ? ";
			
			C3POPropertiesMain c3p0Instance = new C3POPropertiesMain();
			
			ps = c3p0Instance.getPrepareStatement(conn, sql);
			c3p0Instance.setPrepareStatementParameter(ps, new Object[]{20});
			
			rs = ps.executeQuery();
			
			while(rs.next()){
				Object obj1 = rs.getObject(1);
				Object obj4 = rs.getObject(4);
				
				System.out.println("ID: " + obj1 + ",NAME: " + obj4);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			//释放资源
			C3POPropertiesMain.realeaseResource(rs, ps, conn);
		}
	}
}

2.不用显式的从配置文件中获取参数

c3p0.properties

c3p0.driverClass = com.mysql.jdbc.Driver
c3p0.jdbcUrl = jdbc:mysql://localhost:3306/cloudhospital
c3p0.user = root
c3p0.password = admin
c3p0.maxPoolSize = 20
c3p0.minPoolSize = 3
c3p0.maxStatements = 30
c3p0.maxIdleTime = 150

C3P0PropertiesSimplify.java

package com.song.c3p0.db.properties;

import java.sql.Connection;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class C3P0PropertiesSimplify {
	
	private static C3P0PropertiesSimplify c3P0PropertiesSimplify;
	
	private ComboPooledDataSource cpds;
	
	static{
		c3P0PropertiesSimplify = new C3P0PropertiesSimplify();
	}

	public C3P0PropertiesSimplify() {
		cpds = new ComboPooledDataSource();
	}
	
	public static C3P0PropertiesSimplify getInstance(){
		return c3P0PropertiesSimplify;
	}
	
	public Connection getConnection(){
		Connection conn = null;
		try {
			conn = cpds.getConnection();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return conn;
	}
}

C3P0PropertiesSimplifyMain.java

package com.song.c3p0.db.properties;

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

public class C3P0PropertiesSimplifyMain {

	public PreparedStatement createStatement(Connection conn,String sql){
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return ps;
	}
	
	public PreparedStatement setParameters(PreparedStatement ps,Object...values){
		try {
			if(null != values){
				for(int i=1;i<=values.length;i++){
					ps.setObject(i, values[i-1]);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return ps;
	}
	
	public static void main(String[] args) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = C3P0PropertiesSimplify.getInstance().getConnection();
			
			C3P0PropertiesSimplifyMain c3p0Instance = new C3P0PropertiesSimplifyMain();
			String sql = "SELECT * FROM tb_user WHERE ID < ? ";
			ps = c3p0Instance.createStatement(conn, sql);
			ps = c3p0Instance.setParameters(ps, new Object[]{20});
			
			rs = ps.executeQuery();
			while(rs.next()){
				Object obj1 = rs.getObject(1);
				Object obj4 = rs.getObject(4);
				
				System.out.println("ID: " + obj1 + ",Name: " + obj4);
			}
		} catch (Exception e) {
		}finally{
			try {
				if(null != rs){
					try {
						rs.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
				
				if(null != ps){
					try {
						ps.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
				
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			} catch (Exception e2) {
				e2.printStackTrace();
			}
		}
	}
}

c3p0-config.xml
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
	<named-config name="myc3p0xml">
		<property name="driverClass">com.mysql.jdbc.Driver</property>
		<property name="jdbcUrl">jdbc:mysql://localhost:3306/cloudhospital</property>
		<property name="user">root</property>
		<property name="password">admin</property>
		<property name="maxPoolSize">20</property>
		<property name="minPoolSize">3</property>
		<property name="maxStatements">30</property>
		<property name="maxIdleTime">150</property>
	</named-config>
</c3p0-config>

C3P0XmlSimplify.java 
package com.song.c3p0.db.xml;

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

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class C3P0XmlSimplify {
	
	private static C3P0XmlSimplify c3P0XmlSimplify;

	private ComboPooledDataSource cpds;
	
	static{
		c3P0XmlSimplify = new C3P0XmlSimplify();
	}

	public C3P0XmlSimplify() {
		cpds = new ComboPooledDataSource("myc3p0xml");
	}
	
	public static C3P0XmlSimplify getInstance(){
		return c3P0XmlSimplify;
	}
	
	public Connection getConnection(){
		Connection conn = null;
		try {
			conn = cpds.getConnection();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return conn;
	}
	
	public static PreparedStatement setStatement(Connection conn, String sql){
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return ps;
	}
	
	
	public static PreparedStatement setSQLParameters(PreparedStatement ps,Object...values){
		try {
			if (null != values) {
				for (int i = 1; i <= values.length; i++) {
					ps.setObject(i, values[i-1]);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return ps;
	}
	
	public static void releaseSources(Connection conn, PreparedStatement ps, ResultSet rs){
		if(null != rs){
			try {
				rs.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		
		if(null != ps){
			try {
				ps.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		
		if(null != conn){
			try {
				conn.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}
}

C3P0XmlSimplifyMain.java 
package com.song.c3p0.db.xml;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class C3P0XmlSimplifyMain {

	public static void main(String[] args) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = C3P0XmlSimplify.getInstance().getConnection();
			
			String sql = " SELECT * FROM tb_user WHERE ID < ? ";
			ps = C3P0XmlSimplify.setStatement(conn, sql);
			ps = C3P0XmlSimplify.setSQLParameters(ps, new Object[]{20});
			
			rs = ps.executeQuery();
			
			while(rs.next()){
				Object obj1 = rs.getObject(1);
				Object obj4 = rs.getObject(4);
				
				System.out.println("ID: " + obj1 + ",Name: " + obj4);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			C3P0XmlSimplify.releaseSources(conn, ps, rs);
		}
	}
}
声明：该文观点仅代表作者本人，入门客AI创业平台信息发布平台仅提供信息存储空间服务，如有疑问请联系rumenke@qq.com。
  • 上一篇：没有了
  • 下一篇：没有了
未上传头像

copyright © 2008-2019 入门客AI创业平台 版权所有 备案号：湘ICP备2023012770号