JdbcHelper，,//数据源配置信息 DB

JdbcHelper，,//数据源配置信息 DB

//数据源配置信息 DBInfo.properties

test.AcquireIncrement = 1

test.AcquireRetryAttempts = 30

test.AcquireRetryDelay = 1000

test.CheckoutTimeout = 3000

test.Driver = com.mysql.jdbc.Driver

test.IdleConnectionTestPeriod = 18000

test.InitialPoolSize = 10

test.MaxIdleTime = 18000

test.MaxPoolSize = 300

test.MaxStatements = 0

test.MinPoolSize = 10

test.Password = 123456

test.TestConnectionOnCheckin = false

test.TestConnectionOnCheckout = false

test.Url &nb;

package ben.JdbcUtility;import java.io.InputStream;import java.io.IOException;import java.util.HashMap;import java.util.Properties;import com.mchange.v2.c3p0.ComboPooledDataSource;/** * c3p0数据源管理类 * Description: 把多个c3p0数据源放进容器中管理  * */public final class DataSourceManager {    //保存数据库连接池的容器    public static HashMap<String, ComboPooledDataSource> dataSourceMap = new HashMap<String, ComboPooledDataSource>();    //数据库信息的配置文件    private static Properties pp = null;    private static InputStream fs = null;    private DataSourceManager(){    }    /**     * 从连接池容器中返回连接池对象     * @return 连接池的名称     * */    public static ComboPooledDataSource getDataSource(String dataSourceName){        //如果指定数据源不存在，则创建        if (!dataSourceMap.containsKey(dataSourceName.toString())){            try {                ComboPooledDataSource ds = new ComboPooledDataSource();                //读取数据库配置文件                pp = new Properties();                fs = DataSourceManager.class.getClassLoader().getResourceAsStream("DBInfo.properties");                pp.load(fs);                //配置数据源                ds.setDriverClass(pp.getProperty(dataSourceName + "." + "Driver"));                ds.setJdbcUrl(pp.getProperty(dataSourceName + "." + "Url"));                ds.setUser(pp.getProperty(dataSourceName + "." + "User"));                ds.setPassword(pp.getProperty(dataSourceName + "." + "Password"));                ds.setMaxPoolSize(Integer.parseInt(pp.getProperty(dataSourceName + "." + "MaxPoolSize")));                ds.setMinPoolSize(Integer.parseInt(pp.getProperty(dataSourceName + "." + "MinPoolSize")));                ds.setMaxIdleTime(Integer.parseInt(pp.getProperty(dataSourceName + "." + "MaxIdleTime")));                ds.setInitialPoolSize(Integer.parseInt(pp.getProperty(dataSourceName + "." + "InitialPoolSize")));                ds.setAcquireIncrement(Integer.parseInt(pp.getProperty(dataSourceName + "." + "AcquireIncrement")));                ds.setAcquireRetryAttempts(Integer.parseInt(pp.getProperty(dataSourceName + "." + "AcquireRetryAttempts")));                ds.setAcquireRetryDelay(Integer.parseInt(pp.getProperty(dataSourceName + "." + "AcquireRetryDelay")));                ds.setMaxStatements(Integer.parseInt(pp.getProperty(dataSourceName + "." + "MaxStatements")));                ds.setIdleConnectionTestPeriod(Integer.parseInt(pp.getProperty(dataSourceName + "." + "IdleConnectionTestPeriod")));                ds.setCheckoutTimeout(Integer.parseInt(pp.getProperty(dataSourceName + "." + "CheckoutTimeout")));                ds.setTestConnectionOnCheckin(Boolean.parseBoolean(pp.getProperty(dataSourceName + "." + "TestConnectionOnCheckin")));                ds.setTestConnectionOnCheckout(Boolean.parseBoolean(pp.getProperty(dataSourceName + "." + "TestConnectionOnCheckout")));                //把数据源放进容器中                dataSourceMap.put(dataSourceName, ds);                return ds;            } catch (Exception e) {                e.printStackTrace();                throw new RuntimeException("无法根据配置文件创建连接池对象", e);            }            finally{                try {                    fs.close();                } catch (IOException e2) {                    e2.printStackTrace();                    throw new RuntimeException("无法找到配置文件", e2);                }            }        }        else {            return (ComboPooledDataSource)dataSourceMap.get(dataSourceName.toString());        }    }}//----------------------------------------------------------------------------------------------------package ben.JdbcUtility;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.CallableStatement;import java.sql.ResultSet;import javax.sql.RowSet;import javax.sql.rowset.CachedRowSet;import com.mchange.v2.c3p0.ComboPooledDataSource;import com.sun.rowset.CachedRowSetImpl;/** * Jdbc应用类 * Description:  * 1、需要在调用项目中引用c3p0数据源jar包 * 2、需要把DBInfo.properties文件放在classes文件夹中 * 3、DBInfo.properties文件的配置前序为数据源名称。例如：配置为webdemo.Driver,则引用时JdbcHelper.executeUpdate("webdemo", sql, params);  * */public final class JdbcHelper {    /**     * 获取一个数据库连接     * @return 一个数据库连接     * */    private static synchronized Connection getConnection(String dataSourceName){        try {            ComboPooledDataSource ds = DataSourceManager.getDataSource(dataSourceName);            return ds.getConnection();        } catch (Exception e) {            e.printStackTrace();            return null;        }    }    /**     * 关闭数据库连接释放资源     * */    private static void close(Connection conn, PreparedStatement pstm, ResultSet rs){        try {            if (conn != null){                conn.close();            }            if (pstm != null){                pstm.close();            }            if (rs != null){                rs.close();            }        } catch (Exception e) {            e.printStackTrace();            throw new RuntimeException(e);        }    }    /**     * 执行单个数据库操作 Insert,Update,Delete     * @return 成功执行的记录数     * */    public static Integer executeUpdate(String dsName, String sql, String[] params){        Connection conn = null;        PreparedStatement pstm = null;        try {            conn = getConnection(dsName);            pstm = conn.prepareStatement(sql);            if (params != null){                for (int i=0; i<params.length; i++){                    pstm.setString(i+1, params[i]);                }            }            return pstm.executeUpdate();        } catch (Exception e) {            e.printStackTrace();            return -1;        }        finally{            close(conn, pstm, null);        }    }    /**     * 执行多个数据库操作，包含事务处理功能     * @return 如果事务执行成功返回1，如果事务执行不成功返回0     * */    public static Integer executeUpdate(String dsName, String[] sqls, String[][] params){        Connection conn = null;        PreparedStatement pstm = null;        try {            conn=getConnection(dsName);            //禁止自动提交事务            conn.setAutoCommit(false);            for (int i=0; i<sqls.length; i++){                pstm = conn.prepareStatement(sqls[i]);                if (params != null){                    for (int j=0; j<params[i].length; j++){                        pstm.setString(j+1, params[i][j]);                    }                }                pstm.executeUpdate();            }            conn.commit();            return 1;        } catch (Exception e) {            e.printStackTrace();            try {                conn.rollback();            } catch (Exception e2) {                e2.printStackTrace();            }            return 0;        }        finally{            close(conn, pstm, null);        }    }    /**     * 执行数据库查询操作     * @return 查询结果的离线RowSet     * */    public static RowSet executeQuery(String dsName, String sql, String[] params){        Connection conn = null;        PreparedStatement pstm = null;        ResultSet rs = null;        CachedRowSet crs = null;        try {            conn = getConnection(dsName);            pstm = conn.prepareStatement(sql);            if (params != null){                for (int i=0; i<params.length; i++){                    pstm.setString(i+1, params[i]);                }            }            rs = pstm.executeQuery();            //创建CacheRowSet            crs = new CachedRowSetImpl();            crs.populate(rs);        } catch (Exception e) {            e.printStackTrace();        }        finally{            close(conn, pstm, rs);        }        return crs;    }    /**     * 执行需要分页的数据库查询操作     * @return 查询结果的离线RowSet     * */    public static RowSet executeQuery(String dsName, String sql, String[] params, Integer pageIndex, Integer pageSize){        Connection conn = null;        PreparedStatement pstm = null;        ResultSet rs = null;        CachedRowSet crs = null;        try {            conn = getConnection(dsName);            pstm = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);            if (params != null){                for (int i=0; i<params.length; i++){                    pstm.setString(i+1, params[i]);                }            }            rs = pstm.executeQuery();            //创建CacheRowSet            crs = new CachedRowSetImpl();            crs.setPageSize(pageSize);            crs.populate(rs, (pageIndex-1)*pageSize+1);        } catch (Exception e) {            e.printStackTrace();        }        finally{            close(conn, pstm, rs);        }        return crs;    }    /**     * 执行查询的存储过程"{ call addUser(?,?,?,?) }"     * @return 返回查询结果的RowSet集合     * */    public static RowSet executeStoredProcedure(String dsName, String sp_name, String[] params){        Connection conn = null;        CallableStatement cstm = null;        ResultSet rs = null;        CachedRowSet crs = null;        try {            conn = getConnection(dsName);            cstm = conn.prepareCall(sp_name);            if (params != null){                for (int i=0; i<params.length; i++){                    cstm.setString(i+1, params[i]);                }            }            rs = cstm.executeQuery();            //创建CacheRowSet            crs = new CachedRowSetImpl();            crs.populate(rs);        } catch (Exception e) {            e.printStackTrace();        }        finally{            close(conn, cstm, rs);        }        return crs;    }}//该片段来自于http://byrx.net