欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

使用DBCP创建MySQL数据库连接池

程序员文章站 2022-04-22 08:32:54
...

        DBCP(DataBase connection pool),数据库连接池。是 apache 上的一个 java 连接池项目,也是 tomcat 使用的连接池组件。单独使用dbcp需要3个包:commons-dbcp.jar,commons-pool.jar,commons-collections.jar。

       由于建立数据库连接是一个非常耗时耗资源的行为,所以通过连接池预先同数据库建立一些连接,放在内存中,应用程序需要建立数据库连接时直接到连接池中申请一个就行,用完后再放回去。

【1】配置文件

#mysql
db.className=com.mysql.jdbc.Driver
#mysql to write
wdb.url=jdbc:mysql://
wdb.username=root
wdb.pwd=123456
wdb.maxActive=30
wdb.maxIdle=10
wdb.maxWait=3600000
#mysql common config info
common.db.removeAbandoned=false
common.db.removeAbandonedTimeout=120
common.db.testOnBorrow=true
common.db.logAbandoned=true

 【2】创建数据库连接池并获得数据库连接

package hhf.dbcp.util;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

import org.apache.commons.dbcp2.BasicDataSource;
import org.apache.commons.dbcp2.BasicDataSourceFactory;

/**
 * Get the connection from writing DB
 */
public class WDBConnUtils {
	
	private static BasicDataSource dataSource = null;  
	
    public static void init() {  
        if (dataSource != null) {  
            try {  
                dataSource.close();  
            } catch (Exception e) {  
                e.printStackTrace(); 
            }  
            dataSource = null;  
        }  
        try {  
            Properties p = new Properties();  
            p.setProperty("driverClassName", SystemConstants.DB_CLASS_NAME);  
            p.setProperty("url", SystemConstants.W_DB_URL);  
            p.setProperty("username", SystemConstants.W_DB_USER_NAME);  
            p.setProperty("password", SystemConstants.W_DB_USER_PWD);  
            p.setProperty("maxActive", SystemConstants.W_DB_MAX_ACTIVE);  
            p.setProperty("maxIdle", SystemConstants.W_DB_MAX_IDLE);  
            p.setProperty("maxWait", SystemConstants.W_DB_MAX_WAIT);  
            p.setProperty("removeAbandoned", SystemConstants.DB_REMOVE_ABANDONED);  
            p.setProperty("removeAbandonedTimeout", SystemConstants.DB_REMOVE_ABANDONED_TIMEOUT);  
            p.setProperty("testOnBorrow", SystemConstants.DB_TEST_ONBORROW);  
            p.setProperty("logAbandoned", SystemConstants.DB_LOG_ABANDONED);  
            dataSource = (BasicDataSource) BasicDataSourceFactory.createDataSource(p);  
        } catch (Exception e) {  
            e.printStackTrace();
        }  
    }  
  
  
    public static synchronized Connection getConnection() throws  SQLException {  
        if (dataSource == null) {  
            init();  
        }  
        Connection conn = null;  
        if (dataSource != null) {  
            conn = dataSource.getConnection();  
            conn.setAutoCommit(false);
        }  
        return conn;  
    }  

	/**
	 * 关闭资源
	 * @param conn
	 * @param pstmt
	 * @param rs
	 */
	public static void closeResources(Connection conn, PreparedStatement pstmt,	ResultSet rs) {
		if (null != rs) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
				throw new RuntimeException(e);
			} finally {
				if (null != pstmt) {
					try {
						pstmt.close();
					} catch (SQLException e) {
						e.printStackTrace();
						throw new RuntimeException(e);
					} finally {
						if (null != conn) {
							try {
								conn.close();
							} catch (SQLException e) {
								e.printStackTrace();
								throw new RuntimeException(e);
							}
						}
					}
				}
			}
		}
	}
	
	/**
	 * 关闭资源
	 * @param conn
	 * @param pstmt
	 */
	public static void closeResources(Connection conn, PreparedStatement pstmt) {
		if (null != pstmt) {
			try {
				pstmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
				throw new RuntimeException(e);
			} finally {
				if (null != conn) {
					try {
						conn.close();
					} catch (SQLException e) {
						e.printStackTrace();
						throw new RuntimeException(e);
					}
				}
			}
		}
	}	
}

 【3】执行插入和查询操作

package hhf.dbcp.throughput;

import hhf.dbcp.util.WDBConnUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
 * 数据库操作
 * @author HHF
 * 2014年12月29日
 */
public class InsertQueryDao {
    /**
     * 插入数据
     * @param num
     */
	public void save(String num) {
		String NEWS_INFO_INSERT = "INSERT INTO table(num) values(?)";
		Connection conn = null;
		PreparedStatement pst = null;
		try {
			conn = WDBConnUtils.getConnection();
			pst = conn.prepareStatement(NEWS_INFO_INSERT);
			pst.setString(1, num);
			pst.execute();
			conn.commit();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			WDBConnUtils.closeResources(conn, pst);
		}
	}
	/**
	 * 查询数据
	 * @param num
	 * @return
	 */
	public String query(String num) {
		String NEWS_INFO_QUERY_LIST = "SELECT news_id FROM table num=? ";
		Connection conn = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		String result = null;
		try {
			conn = WDBConnUtils.getConnection();
			pst = conn.prepareStatement(NEWS_INFO_QUERY_LIST);
			pst.setString(1, num);
			rs = pst.executeQuery();
			while(rs.next()){
				result = rs.getString("news_id");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			WDBConnUtils.closeResources(conn, pst, rs);
		}
		return result;
	}
}

 【4】测试

public class Main {
	  private static final Log log = LogFactory.getLog(Main.class); 
	
	  public void doProcess(String processTime1, String processTime2, int runMode) {
		    InsertQueryDao dao = new InsertQueryDao();
		    String num = dao.query("0");//获得查询结果
			log.info("num " + num);
	  }
}

 (PS:附上项目测试源码和相关的jar包)