C3P0连接池的三种实现方式

原创 2016年06月01日 11:05:31

用到的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();
			}
		}
	}
}

用xml文件设置参数:

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);
		}
	}
}
版权声明:本文为博主原创文章,未经博主允许不得转载。

相关文章推荐

JDBC连接数据库的四种方式:DriverManager,DataSource,DBCP,C3P0

DBC连接数据库的方式 DataSource比DriverManager区别 DriverManager DataSource DBCP C3P0

Spring配置数据源的三种方式 (dbcp,c3p0,jndi).

[html] view plaincopy 1,使用org.springframework.jdbc.datasource.DriverManagerDataSource...

C3p0数据源ComboPooledDataSource的close方法

关于c3p0不想说什么了,以前研究过,但是现在给忘了,于是乎遇到了一些问题,主要关于ComboPooledDataSource 的close方法 对于数据源ComboPooledDataSource...

C3P0连接池配置

C3P0是一个开源的JDBC连接池。 在Spring中,C3P0的一些配置,介绍如下(只列了一部分,不是全部)

C3P0连接池详解及配置

我也来续一下:数据库连接是一个耗费大量资源且相当慢的操作,所以为了提高性能和连接速度,诞生了连接池这样的概念。在多用户并发操作过程中,连接池尤为重要。它是将那些已连接的数据库连接存放在一个容器里(连接...

Java中使用C3P0连接池

1、c3p0.properties c3p0.driverClass=org.postgresql.Driver c3p0.jdbcUrl=jdbc:postgresql://192.168.20...
  • dinphi
  • dinphi
  • 2014-02-11 23:25
  • 12509

C3P0连接池的配置与使用

1、下载c3p0-0.9.1.2.jar 下载地址:http://download.csdn.net/detail/chunxiaqiudong5/9661922 2、添加配置文件c3p0...

论数据库连接池的必要性及c3p0的详解

1.JDBC数据库连接池的必要性 在使用开发基于数据库的web程序时,传统的模式基本是按以下步骤:   在主程序(如servlet、beans)中建立数据库连接。 进行sql操作 断开数据库连接。 ...

数据库连接池的作用及c3p0的详解

1.JDBC数据库连接池的必要性 在使用开发基于数据库的web程序时,传统的模式基本是按以下步骤:   在主程序(如servlet、beans)中建立数据库连接。 进行sql操作 ...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:深度学习:神经网络中的前向传播和反向传播算法推导
举报原因:
原因补充:

(最多只允许输入30个字)