java实现数据库连接池

现在的位置: 首页 > 综合 > 正文

java实现数据库连接池

2013年01月14日 ⁄ 综合 ⁄ 共 7713字 ⁄ 字号小中大 ⁄ 评论关闭

连接池管理类

import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.util.Vector; import com.mysql.jdbc.Driver; /** * ConnectionManager * * 数据库连接 * * @author bzwm * * @version 1.0 */ public class ConnectionManager { /** 连接数 */ static int iRequestCount = 0; /** 连接Pool */ static Vector connectionPool = null; /** 初始连接数 */ static final int INIT_NUM_CONNECTION = 2; /** 追加连接数 */ static final int ADD_NUM_CONNECTION = 1; /** 最大连接数 */ static final int MAX_NUM_CONNECTION = 10; /** 最小连接数 */ static final int MIN_NUM_CONNECTION = INIT_NUM_CONNECTION; /** 初始化标志 */ boolean bInitialized = false; static String serverName = "172.16.1.182"; static String sDBDriver = "com.mysql.jdbc.Driver"; static String dbInstance = "DB_QQ"; static String sConnStr = "jdbc:mysql://" + serverName + "/" + dbInstance; static String dbUser = "root"; static String userPwd = "123456"; static { try { Class.forName(sDBDriver); DriverManager.registerDriver(new Driver()); } catch (Exception ex) { ex.printStackTrace(); } } /** * ConnectionPoolElement * * 数据库连接数 */ class ConnectionPoolElement { Connection con; boolean used; } /** * 构造函数 * * @throws SQLException * */ public ConnectionManager() throws SQLException { if (connectionPool == null) { connectionPool = new Vector(); } init(); } /** * Connection的取得* * * @throws SQLException */ public synchronized Connection getConnection() throws SQLException { ConnectionPoolElement elm = null; for (;;) { synchronized (connectionPool) { for (int i = 0; i < connectionPool.size(); i++) { elm = (ConnectionPoolElement) (connectionPool.elementAt(i)); if (!elm.used) { elm.used = true; return elm.con; } } } // 超过最大连接数，则追加 if (connectionPool.size() < MAX_NUM_CONNECTION) { createConnectionPool(ADD_NUM_CONNECTION); } else { try { this.wait(100); } catch (InterruptedException e) { } } } } /** * 连接完之后发行 * * @param con * Connection * * @throws SQLException */ public synchronized void releaseConnection(Connection con) throws SQLException { ConnectionPoolElement elm; synchronized (connectionPool) { for (int i = 0; i < connectionPool.size(); i++) { elm = (ConnectionPoolElement) (connectionPool.elementAt(i)); if (elm.con == con) { elm.used = false; return; } } } throw new SQLException("unknown Connection"); } /** * 数据库初始化 * * @throws SQLException * */ public void init() throws SQLException { if (bInitialized) return; synchronized (connectionPool) { if (connectionPool.size() < INIT_NUM_CONNECTION) { try { // 数据库Pool的生成 createConnectionPool(INIT_NUM_CONNECTION); } catch (Exception ex) { ex.printStackTrace(); throw new SQLException("データベース初期化エラー"); } synchronized (this) { iRequestCount++; } } else { synchronized (this) { iRequestCount++; } } } bInitialized = true; } /** * 从数据库断开 * */ public void destroy() { synchronized (this) { iRequestCount--; } if (iRequestCount < 0) { try { destroyConnection(); } catch (SQLException ex) { } } } /** * 设定ConnectionPool* * * @param int * numConnection * @throws SQLException */ private synchronized void createConnectionPool(int numConnection) throws SQLException { ConnectionPoolElement elm; synchronized (connectionPool) { for (int i = 0; i < numConnection; i++) { elm = new ConnectionPoolElement(); elm.con = DriverManager.getConnection(sConnStr, dbUser, userPwd); connectionPool.addElement(elm); } } } /** * ConnectionPool的Connection的关闭 * * @throws SQLException * */ synchronized void destroyConnection() throws SQLException { ConnectionPoolElement elm; synchronized (connectionPool) { for (int i = 0; i < connectionPool.size(); i++) { elm = (ConnectionPoolElement) (connectionPool.elementAt(i)); elm.con.close(); } } } }

数据库操作类

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 DBAccess { private ConnectionManager cm = null; public DBAccess() { try { cm = new ConnectionManager(); } catch (SQLException re) { re.printStackTrace(); } } private Connection getConnect() throws SQLException { return cm.getConnection(); } public void releaseConnection(Connection cn) throws SQLException { cm.releaseConnection(cn); } /** * 检索 * @param sqlId sqlid 对应于sql.properties中的id * @return 执行结果 List 中保存Map，每个Map是一个条记录。Key：列名，Value：值 * @throws SQLException */ public List executeQuery(String sqlId) throws SQLException { List resultList = new ArrayList(); ResultSet resultSet = null; PreparedStatement ps = null; Connection cn = null; try { cn = getConnect(); ps = cn.prepareStatement(getSQL(sqlId)); resultSet = ps.executeQuery(); Map map; for (; resultSet.next(); resultList.add(map)) { map = doCreateRow(resultSet); } } catch (SQLException sqle) { throw sqle; } catch (NullPointerException e) { } finally { try { resultSet.close(); ps.close(); releaseConnection(cn); } catch (NullPointerException e) { } } return resultList; } /** * 检索 * @param sqlId * @param strParams 查找时需要的params * @return * @throws SQLException */ public List executeQuery(String sqlId, String[] strParams) throws SQLException { List resultList = new ArrayList(); ResultSet resultSet = null; PreparedStatement ps = null; Connection cn = null; try { cn = getConnect(); ps = cn.prepareStatement(getSQL(sqlId)); for (int i = 1; i <= strParams.length; i++) { ps.setString(i, strParams[i - 1]); } resultSet = ps.executeQuery(); Map map; for (; resultSet.next(); resultList.add(map)) { map = doCreateRow(resultSet); } } catch (NullPointerException e) { } catch (SQLException sqle) { throw sqle; } finally { try { resultSet.close(); ps.close(); releaseConnection(cn); } catch (NullPointerException e) { } } return resultList; } /** * 更新DB * @param sqlId * @return * @throws SQLException */ public int executeUpdate(String sqlId) throws SQLException { int count = 0; Connection cn = null; PreparedStatement ps = null; try { cn = getConnect(); ps = cn.prepareStatement(getSQL(sqlId)); count = ps.executeUpdate(); cn.commit(); } catch (SQLException sqle) { throw sqle; } catch (NullPointerException e) { } finally { try { ps.close(); releaseConnection(cn); } catch (NullPointerException e) { } } return count; } /** * 更新DB * @param sqlId * @param lsParam * @return * @throws SQLException */ public int executeUpdate(String sqlId, String[] lsParam) throws SQLException { int count = 0; Connection cn = null; PreparedStatement ps = null; try { cn = getConnect(); ps = cn.prepareStatement(getSQL(sqlId)); for (int i = 1; i <= lsParam.length; i++) { ps.setString(i, lsParam[i - 1]); } count = ps.executeUpdate(); // cn.commit(); } catch (SQLException sqle) { throw sqle; } catch (NullPointerException e) { } finally { try { ps.close(); releaseConnection(cn); } catch (NullPointerException e) { } } return count; } /** * 根据id取得sql文 * @param sqlId * @return * @throws SQLException */ private String getSQL(String sqlId) throws SQLException { String sqlData = ""; if (sqlId == null || sqlId.length() == 0) { throw new SQLException(); } else { Map sqlMap = ResourceReader.getSqlMap(); sqlData = (String) sqlMap.get(sqlId); if (sqlData.trim().length() == 0) { throw new SQLException(); } else { return sqlData; } } } /** * 将执行sql文的结果放在List中 * @param resultSet * @return * @throws SQLException */ private final Map doCreateRow(ResultSet resultSet) throws SQLException { Map result = new HashMap(); try { ResultSetMetaData resultSetMetaData = resultSet.getMetaData(); int count = resultSetMetaData.getColumnCount(); for (int i = 1; i <= count; i++) { String label = resultSetMetaData.getColumnLabel(i); Object value = resultSet.getObject(i); result.put(label.toUpperCase(), value); } } catch (SQLException e) { throw e; } return result; } }

根据sqlid 取得sql文

import java.util.Enumeration; import java.util.HashMap; import java.util.Map; import java.util.MissingResourceException; import java.util.ResourceBundle; /** * @author bzwm * */ public class ResourceReader { //sql.properties的路径，根据自己需要配置 private static final String _path = "com.chat.commons.property.sql"; private static ResourceReader _instance = null; private Map _sqlMap = new HashMap(); private ResourceReader() { try { ResourceBundle bundle = ResourceBundle.getBundle(_path); Enumeration enumeration = bundle.getKeys(); while (enumeration.hasMoreElements()) { String key = (String) enumeration.nextElement(); _sqlMap.put(key, bundle.getString(key)); } } catch (MissingResourceException e) { e.printStackTrace(); } } public synchronized static void initConfigFile() { if (_instance == null) _instance = new ResourceReader(); } public static Map getSqlMap() { return _instance._sqlMap; } public static void main(String args[]) { new ResourceReader(); } }

sql.properties
保存sql语句

sql001=select password from t_qq_user where qq_num = ?

用法：

//参数是sqlId和 qq号， sql001=select password from t_qq_user where qq_num = ? new DBAccess().executeQuery("sql001", new String[]{"123456"});

【上篇】我的Linux PC开发环境
【下篇】【c++ primer】深入解析 strcpy() 笔试面试高频题

作者: chace

该日志由 chace 于11年前发表在综合分类下，最后更新于 2013年01月14日.
转载请注明: java实现数据库连接池 | 学步园 +复制链接

抱歉!评论已关闭.

学步园

java实现数据库连接池

作者: chace

书签

最新文章New

本站推荐

返回首页