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