java实现数据库连接池

连接池管理类

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"});