JAVA WEB开发：c3p0连接池+JDBC封装集合数据库操作通用类

Author：飘易 Source：飘易
Categories：JAVA开发 PostTime：2016-3-11 16:18:41

正文：

一、C3P0连接池使用；

在生产环境中，开发人员在对数据库进行CRUD操作的时候，由于数据库的链接是有限的，因此不得不使用连接池来实现资源复用，以此降低数据库的性能瓶颈(尽管这么说有些不太友好，因为并发环境下，单靠连接池是不能够解决问题的，而常用的方案更多是诸如Redis之类的内存数据库抗住70%传统DBMS数据的受访压力、数据库先做垂直分库，再做水平分区，当然Master/Sleave是必不可少的，经过这些步骤之后，才能够说基本上解决了理论上可能出现的数据库在高并发环境下的瓶颈)。

定义JdbcManager，有2种方式：

1、硬编码方式，把用户名这些信息直接写在java代码中

package com.jdbc.dbutils;
import java.sql.Connection;
import java.sql.SQLException;
import com.mchange.v2.c3p0.ComboPooledDataSource;

public class JdbcManager {
    //数据库用户名
    private static final String USERNAME = "root";
    //数据库密码
    private static final String PASSWORD = "root";
    //驱动信息
    private static final String DRIVER = "com.mysql.jdbc.Driver";
    //数据库地址
    private static final String JDBCURL = "jdbc:mysql://localhost:3306/java_1st";

    //C3P0连接池 ,也可以采用c3p0-config.xml配置方式
    public static ComboPooledDataSource dataSource;
    static {
        try {
            dataSource = new ComboPooledDataSource();
            dataSource.setUser(USERNAME);
            dataSource.setPassword(PASSWORD);
            dataSource.setJdbcUrl(JDBCURL);
            dataSource.setDriverClass(DRIVER);
            dataSource.setInitialPoolSize(10);
            dataSource.setMinPoolSize(5);
            dataSource.setMaxPoolSize(50);
            dataSource.setMaxStatements(100);
            dataSource.setMaxIdleTime(60);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 从C3P0连接池中获取数据源链接
     * @return Connection 数据源链接
     */
    public static Connection getConnection() {
     Connection conn = null;
        try {
         conn = dataSource.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }

}

2、C3P0连接池 ,也可以采用c3p0-config.xml配置方式

package com.jdbc.dbutils;
import java.sql.Connection;
import java.sql.SQLException;
import com.mchange.v2.c3p0.ComboPooledDataSource;

public class JdbcManager {
    public static ComboPooledDataSource dataSource;
    static {
        try {
            dataSource = new ComboPooledDataSource();//默认的缺省的配置
            //dataSource = new ComboPooledDataSource("mysql2");//named-config别名方式
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 从C3P0连接池中获取数据源链接 - 方法2
     * @return Connection 数据源链接
     */
    public static Connection getConnection() {
     Connection conn = null;
        try {
         conn = dataSource.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }

}

然后需要把 c3p0-config.xml 配置文件放到应用目录下，比如飘易用的是eclipse J2EE开发环境，我把 c3p0-config.xml 文件放到 WebContent/WEB-INF/classes/c3p0-config.xml 。到这里，如果你运行，将会提示出错，找不到数据库资源。我们还需要配置下eclipse 环境，把 WebContent/WEB-INF/classes/ 这个目录加入到类路径CLASSPATH里：
运行-运行配置-Apache Tomcat-类路径：

到这里，再重启tomcat服务器就可以找到配置文件了。

关于C3P0的具体介绍，可以移步这里：http://www.mchange.com/projects/c3p0/

二、增删改查具体的数据库操作类JdbcUtils

package com.jdbc.dbutils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class JdbcUtils {
    private Connection conn;
    private PreparedStatement pstmt;
    private ResultSet rs;

    /**
     * 构造函数
     */
    public JdbcUtils() {
     //从连接池里获得一个连接
     conn = JdbcManager.getConnection();
    }

    /**
     * 从C3P0连接池中获取数据源链接
     * @return Connection 数据源链接
     */
    public Connection getConnection() {
        return conn;
    }

    /**
     * 关闭数据库连接
     */
    public void close(){
        if(rs != null){
            try{
                rs.close();
            }catch(SQLException e){
                e.printStackTrace();
            }
        }
        if(pstmt != null){
            try{
             pstmt.close();
            }catch(SQLException e){
                e.printStackTrace();
            }
        }
        if(conn != null){
            try{
             conn.close();
            }catch(SQLException e){
                e.printStackTrace();
            }
        }
    }

    /**
     * 查询单条记录
     * @param String table
     * @param String cols
     * @param String where
     * @param Object[] params
     * @return Map<String, Object>
     * @throws SQLException
     */
    public Map<String, Object> select(String table, String cols, String where, Object[] params) throws SQLException{
        Map<String, Object> map = new HashMap<String, Object>();
        int index  = 1;
        pstmt = conn.prepareStatement("select "+cols+" from "+table+" where "+where);
        if(params != null && params.length!=0){
            for(int i=0; i<params.length; i++){
                pstmt.setObject(index++, params[i]);
            }
        }
        rs = pstmt.executeQuery();//返回查询结果
        ResultSetMetaData metaData = rs.getMetaData();
        int col_len = metaData.getColumnCount();
        while(rs.next()){
            for(int i=0; i<col_len; i++ ){
                String cols_name = metaData.getColumnName(i+1);
                Object cols_value = rs.getObject(cols_name);
                if(cols_value == null){
                    cols_value = "";
                }
                map.put(cols_name, cols_value);
            }
        }
        return map;
    }

    /**
     * 查询单条记录 - 重载1
     * @param String table
     * @param String where
     * @param Object[] params
     * @return Map<String, Object>
     * @throws SQLException
     */
    public Map<String, Object> select(String table, String where, Object[] params) throws SQLException{
     String cols="*";
        return select(table, cols, where, params);
    }

    /**
     * 查询单条记录 - 重载2
     * @param String table
     * @param String where
     * @return Map<String, Object>
     * @throws SQLException
     */
    public Map<String, Object> select(String table, String where) throws SQLException{
     String cols="*";
     Object[] params=null;
        return select(table, cols, where, params);
    }

    /**
     * 查询单条记录 - 重载3
     * @param String table
     * @param String cols
     * @param String where
     * @return Map<String, Object>
     * @throws SQLException
     */
    public Map<String, Object> select(String table, String cols, String where) throws SQLException{
     Object[] params=null;
        return select(table, cols, where, params);
    }

    /**
     * 查询多条记录
     * @param String table
     * @param String cols
     * @param String where
     * @param Object[] params
     * @return List<Map<String, Object>>
     * @throws SQLException
     */
    public List<Map<String, Object>> select_all(String table, String cols, String where, Object[] params) throws SQLException{
        List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
        int index = 1;
        pstmt = conn.prepareStatement("select "+cols+" from "+table+" where "+where);
        if(params != null && params.length!=0){
            for(int i = 0; i<params.length; i++){
                pstmt.setObject(index++, params[i]);
            }
        }
        rs = pstmt.executeQuery();
        ResultSetMetaData metaData = rs.getMetaData();
        int cols_len = metaData.getColumnCount();
        while(rs.next()){
            Map<String, Object> map = new HashMap<String, Object>();
            for(int i=0; i<cols_len; i++){
                String cols_name = metaData.getColumnName(i+1);
                Object cols_value = rs.getObject(cols_name);
                if(cols_value == null){
                    cols_value = "";
                }
                map.put(cols_name, cols_value);
            }
            list.add(map);
        }
        return list;
    }

    /**
     * 查询多条记录 - 重载1
     * @param String table
     * @param String cols
     * @param String where
     * @param Object[] params
     * @return List<Map<String, Object>>
     * @throws SQLException
     */
    public List<Map<String, Object>> select_all(String table, String where, Object[] params) throws SQLException{
     String cols="*";
     return select_all(table, cols, where, params);
    }

    /**
     * 查询多条记录 - 重载2
     * @param String table
     * @param String where
     * @return List<Map<String, Object>>
     * @throws SQLException
     */
    public List<Map<String, Object>> select_all(String table, String where) throws SQLException{
     String cols="*";
     Object[] params=null;
     return select_all(table, cols, where, params);
    }

    /**
     * 查询多条记录 - 重载3
     * @param String table
     * @param String cols
     * @param String where
     * @return List<Map<String, Object>>
     * @throws SQLException
     */
    public List<Map<String, Object>> select_all(String table, String cols, String where) throws SQLException{
     Object[] params=null;
     return select_all(table, cols, where, params);
    }


    /**
     * 增加
     * @param String table
     * @param Map<String, Object> map
     * @return int
     * @throws SQLException
     */
    public int insert(String table, Map<String, Object> map) throws SQLException{
        int result = -1;
        StringBuffer sb = new StringBuffer();
        for (Map.Entry<String, Object> entry : map.entrySet()) { //遍历
         String key = entry.getKey();
         sb.append("`"+key+"`=?,");
        }
        String str = sb.toString();
        if(!str.equals("")){
         str = str.substring(0, str.length()-1);//删除最后一个字符
        }
        //预查询
        pstmt = conn.prepareStatement("insert into `"+table+"` set "+str);
        int index = 1;
        for (Map.Entry<String, Object> entry : map.entrySet()) { //遍历
         Object value = entry.getValue();
         pstmt.setObject(index++, value);
        }
        result = pstmt.executeUpdate();
        return result;
    }

    /**
     * 修改
     * @param String table
     * @param Map<String, Object> map
     * @param String where
     * @param Object[] params
     * @return int
     * @throws SQLException
     */
    public int update(String table, Map<String, Object> map, String where, Object[] params) throws SQLException{
        int result = -1;
        StringBuffer sb = new StringBuffer();
        for (Map.Entry<String, Object> entry : map.entrySet()) { //遍历
         String key = entry.getKey();
         sb.append("`"+key+"`=?,");
        }
        String str = sb.toString();
        if(!str.equals("")){
         str = str.substring(0, str.length()-1);//删除最后一个字符
        }
        //预查询
        pstmt = conn.prepareStatement("update `"+table+"` set "+str+" where "+where);
        int index = 1;
        for (Map.Entry<String, Object> entry : map.entrySet()) { //遍历
         Object value = entry.getValue();
         pstmt.setObject(index++, value);
        }
        if(params != null && params.length!=0){
        for (int i = 0; i < params.length; i++) {
Object object = params[i];
pstmt.setObject(index++, object);
}
        }
        result = pstmt.executeUpdate();
        return result;
    }

    /**
     * 修改 - 重载
     * @param String table
     * @param Map<String, Object> map
     * @param String where
     * @return int
     * @throws SQLException
     */
    public int update(String table, Map<String, Object> map, String where) throws SQLException{
     Object[] params=null;
        return update(table, map, where, params);
    }

    /**
     * 删除
     * @param String table
     * @param String where
     * @param Object[] params
     * @return int
     * @throws SQLException
     */
    public int delete(String table, String where, Object[] params) throws SQLException{
        int result = -1;
        //预查询
        pstmt = conn.prepareStatement("delete from `"+table+"` where "+where);
        if(params != null && params.length!=0){
         int index = 1;
        for (int i = 0; i < params.length; i++) {
Object object = params[i];
pstmt.setObject(index++, object);
}
        }
        result = pstmt.executeUpdate();
        return result;
    }

    /**
     * 删除 - 重载
     * @param String table
     * @param String where
     * @return int
     * @throws SQLException
     */
    public int delete(String table, String where) throws SQLException{
     Object[] params=null;
        return delete(table, where, params);
    }

    //--------------------------------------------------------------
    //我是分隔线
    //--------------------------------------------------------------

    /**
     * 增、删、改 - 集成方法
     * @param String sql
     * @param Object[] params
     * @return boolean
     * @throws SQLException
     */
    public boolean execute(String sql, Object[] params) throws SQLException{
        boolean flag = false;
        int result = -1;
        pstmt = conn.prepareStatement(sql);
        if(params != null && params.length!=0){
         int index = 1;
            for(int i=0; i<params.length; i++){
                pstmt.setObject(index++, params[i]);
            }
        }
        result = pstmt.executeUpdate();
        flag = result > 0 ? true : false;
        return flag;
    }

    /**
     * 查询单条记录
     * @param String sql
     * @param Object[] params
     * @return Map<String, Object>
     * @throws SQLException
     */
    public Map<String, Object> query(String sql, Object[] params) throws SQLException{
        Map<String, Object> map = new HashMap<String, Object>();
        int index  = 1;
        pstmt = conn.prepareStatement(sql);
        if(params != null && params.length!=0){
            for(int i=0; i<params.length; i++){
                pstmt.setObject(index++, params[i]);
            }
        }
        rs = pstmt.executeQuery();//返回查询结果
        ResultSetMetaData metaData = rs.getMetaData();
        int col_len = metaData.getColumnCount();
        while(rs.next()){
            for(int i=0; i<col_len; i++ ){
                String cols_name = metaData.getColumnName(i+1);
                Object cols_value = rs.getObject(cols_name);
                if(cols_value == null){
                    cols_value = "";
                }
                map.put(cols_name, cols_value);
            }
        }
        return map;
    }

    /**查询多条记录
     * @param String sql
     * @param Object[] params
     * @return List<Map<String, Object>>
     * @throws SQLException
     */
    public List<Map<String, Object>> queryMulti(String sql, Object[] params) throws SQLException{
        List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
        int index = 1;
        pstmt = conn.prepareStatement(sql);
        if(params != null && params.length!=0){
            for(int i = 0; i<params.length; i++){
                pstmt.setObject(index++, params[i]);
            }
        }
        rs = pstmt.executeQuery();
        ResultSetMetaData metaData = rs.getMetaData();
        int cols_len = metaData.getColumnCount();
        while(rs.next()){
            Map<String, Object> map = new HashMap<String, Object>();
            for(int i=0; i<cols_len; i++){
                String cols_name = metaData.getColumnName(i+1);
                Object cols_value = rs.getObject(cols_name);
                if(cols_value == null){
                    cols_value = "";
                }
                map.put(cols_name, cols_value);
            }
            list.add(map);
        }
        return list;
    }

    /**
     * 开始事务
     */
    public void beginTransaction() {
        try {
conn.setAutoCommit(false);//设置事务为非自动提交
} catch (SQLException e) {
e.printStackTrace();
}
}

    /**
     * 事务提交
     */
    public void commit() {
        try {
conn.commit();
} catch (SQLException e) {
e.printStackTrace();
}
}

    /**
     * 事务回滚
     */
    public void rollback() {
        try {
conn.rollback();
} catch (SQLException e) {
e.printStackTrace();
}
}

    /**
     * 结束事务
     */
    public void endTransaction() {
        try {
conn.setAutoCommit(true);//设置事务为自动提交
} catch (SQLException e) {
e.printStackTrace();
}
}

}

然后，具体应用的时候，先实例化 JdbcUtils jdbcUtils = new JdbcUtils(); 然后调用里面的方法即可，示例：

JdbcUtils jdbc = new JdbcUtils();

//初始化map
Map<String, Object> map = new HashMap<String, Object>() {
private static final long serialVersionUID = 1L;
{
put("title", request.getParameter("title"));
put("content", request.getParameter("content"));
}
};
int res = -1;
try {
res = jdbc.insert("msg", map);//新增
} catch (SQLException e) {
e.printStackTrace();
}
jdbc.close();//关闭数据库连接
if(res>0){
//恭喜！添加成功！
}
else{
//抱歉！添加失败！
}

好了，就介绍到这里，如果有任何问题，欢迎留下评论。

Tag：JAVA WEB开发 c3p0连接池 JDBC 浏览(次) 我要评论(0条)

上一篇：没有了

下一篇：PHP 单例模式意义和实现方法

0条评论 “JAVA WEB开发：c3p0连接池+JDBC封装集合数据库操作通用类”

No Comment .

发表评论

名称(*必填)

邮件(选填)

网站(选填)

记住我,下次回复时不用重新输入个人信息

飘易搜索