原生JDBC实现CRUD（使用数据库c3p0连接池）

使用到的相关jar包 点击打开链接

配置文件 res/c3p0.properties

driverClass=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/db
username=root
password=root
#driver=oracle.jdbc.driver.OracleDriver
#url=jdbc:oracle:thin:127.0.0.1:1521:orcl
#username=scott
#password=tiger

#c3p0数据库连接池相关配置
#最大连接数#
c3p0.maxPoolSize=50
#最小连接数#
c3p0.minPoolSize=5
#初始化连接数#
c3p0.initialPoolSize=5
#最大空闲时间#
c3p0.maxIdleTime=200
#超时时间#
c3p0.checkoutTimeout=10000
#一次获取的连接数#
c3p0.acquireIncrement=5
#最大的PreparedStatement的数量#
c3p0.max_statements=0

package util;
import java.io.BufferedInputStream;  
import java.io.FileInputStream;  
import java.io.IOException;  
import java.io.InputStream;  
import java.sql.Connection;  
import java.sql.PreparedStatement;  
import java.sql.ResultSet;  
import java.sql.SQLException;  
import java.sql.Statement;  
import java.util.PropertyResourceBundle;  
import java.util.ResourceBundle;
import javax.sql.rowset.CachedRowSet;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.sun.rowset.CachedRowSetImpl;

public class DBUtil {  
    
	private static ComboPooledDataSource dsOfMySql = null; //数据源
      
    static {  
        InputStream inputStream = null;  
        try {  
        	String proFilePath = System.getProperty("user.dir") +"\\res\\c3p0.properties";    
        	
            inputStream = new BufferedInputStream(new FileInputStream(proFilePath));  
            ResourceBundle bundle = new PropertyResourceBundle(inputStream);    
            
            //初始化DataSource对象
			dsOfMySql = new ComboPooledDataSource();
			
			dsOfMySql.setDriverClass(bundle.getString("driverClass"));
			dsOfMySql.setJdbcUrl(bundle.getString("url"));
			dsOfMySql.setUser(bundle.getString("username"));
			dsOfMySql.setPassword(bundle.getString("password"));
			
			dsOfMySql.setMaxPoolSize(Integer.parseInt(bundle.getString("c3p0.maxPoolSize")));//最大连接数
			dsOfMySql.setMinPoolSize(Integer.parseInt(bundle.getString("c3p0.minPoolSize")));//最小连接数
			dsOfMySql.setInitialPoolSize(Integer.parseInt(bundle.getString("c3p0.initialPoolSize")));//初始化连接数
			dsOfMySql.setMaxIdleTime(Integer.parseInt(bundle.getString("c3p0.maxIdleTime"))); //最大空闲时间
			dsOfMySql.setCheckoutTimeout(Integer.parseInt(bundle.getString("c3p0.checkoutTimeout"))); //超时时间 
			dsOfMySql.setAcquireIncrement(Integer.parseInt(bundle.getString("c3p0.acquireIncrement")));//一次获取的连接数
			dsOfMySql.setMaxStatements(Integer.parseInt(bundle.getString("c3p0.max_statements"))); //最大的PreparedStatement的数量
			
        } catch (Exception e) {  
            e.printStackTrace();  
        } finally{  
            if(inputStream!=null){  
                try {  
                    inputStream.close();  
                } catch (IOException e) {  
                    e.printStackTrace();  
                }  
            }  
        }  
    }  
      
    /** 
     * 获取连接 
     */  
    public static Connection getConnection() throws SQLException{  
        return dsOfMySql.getConnection();  
    }  
      
    public static int add(String sql,Object[] param) throws SQLException{  
        int insertNum = 0;  
        Connection connection = null;  
        PreparedStatement preparedStatement = null;  
        try {  
            connection = getConnection();  
            preparedStatement = connection.prepareStatement(sql);  
            if(param!=null){  
                for(int i =0; i<param.length; i++){  
                    preparedStatement.setObject(i+1,param[i]);  
                }  
            }  
            insertNum = preparedStatement.executeUpdate();  
            return insertNum;  
        } catch (SQLException e) {  
            throw e;  
        } finally{  
            closeAll(null,preparedStatement,connection);  
        }  
    }  
      
    public static int delete(String sql,Object[] param) throws SQLException{  
        int insertNum = 0;  
        Connection connection = null;  
        PreparedStatement preparedStatement = null;  
        try {  
            connection = getConnection();  
            preparedStatement = connection.prepareStatement(sql);  
            if(param!=null){  
                for(int i =0; i<param.length; i++){  
                    preparedStatement.setObject(i+1,param[i]);  
                }  
            }  
            insertNum = preparedStatement.executeUpdate();  
            return insertNum;  
        } catch (SQLException e) {  
            throw e;  
        } finally{  
            closeAll(null,preparedStatement,connection);  
        }  
    }  
      
    public static int update(String sql,Object[] param) throws SQLException{  
        int insertNum = 0;  
        Connection connection = null;  
        PreparedStatement preparedStatement = null;  
        try {  
            connection = getConnection();  
            preparedStatement = connection.prepareStatement(sql);  
            if(param!=null){  
                for(int i =0; i<param.length; i++){  
                    preparedStatement.setObject(i+1,param[i]);  
                }  
            }  
            insertNum = preparedStatement.executeUpdate();  
            return insertNum;  
        } catch (SQLException e) {  
            throw e;  
        } finally{  
            closeAll(null,preparedStatement,connection);  
        }  
    }  
      
    public static CachedRowSet find(String sql,Object[] param) throws SQLException{  
        Connection connection = null;  
        PreparedStatement preparedStatement = null;  
        ResultSet resultSet =null;  
        try {  
            connection = getConnection();  
            preparedStatement = connection.prepareStatement(sql);  
            if(param!=null){  
                for(int i =0; i<param.length; i++){  
                    preparedStatement.setObject(i+1,param[i]);  
                }  
            }  
            resultSet = preparedStatement.executeQuery();  
            CachedRowSet cachedRs= new CachedRowSetImpl();
    		cachedRs.populate(resultSet);
            return cachedRs;  
        } catch (SQLException e) {  
            throw e;  
        } finally{  
            closeAll(resultSet,preparedStatement,connection);  
        }  
    }  
      
    public static void closeAll(ResultSet resultSet,Statement statement,Connection connection) throws SQLException{  
        if(resultSet!=null){  
            try {  
                resultSet.close();  
            } catch (SQLException e) {  
                throw e;  
            }  
            resultSet= null;  
        }  
        if(statement!=null){  
            try {  
                statement.close();  
            } catch (SQLException e) {  
                throw e;  
            }  
            statement= null;  
        }  
        if(connection!=null){  
            try {  
                connection.close();  
            } catch (SQLException e) {  
                throw e;  
            }  
            connection= null;  
        }  
    }  
}