java实现数据库连接池

连接池管理类

  1import java.sql.Connection;
  2import java.sql.DriverManager;
  3import java.sql.SQLException;
  4import java.util.Vector;
  5import com.mysql.jdbc.Driver;
  6/** *//**
  7 * ConnectionManager
  8 *
  9 * 数据库连接
10 *
11 * @author bzwm
12 *
13 * @version 1.0
14 */
15public class ConnectionManager {
16    /** *//** 连接数 */
17    static int iRequestCount = 0;
18    /** *//** 连接Pool */
19    static Vector connectionPool = null;
20    /** *//** 初始连接数 */
21    static final int INIT_NUM_CONNECTION = 2;
22    /** *//** 追加连接数 */
23    static final int ADD_NUM_CONNECTION = 1;
24    /** *//** 最大连接数 */
25    static final int MAX_NUM_CONNECTION = 10;
26    /** *//** 最小连接数 */
27    static final int MIN_NUM_CONNECTION = INIT_NUM_CONNECTION;
28    /** *//** 初始化标志 */
29    boolean bInitialized = false;
30    static String serverName = "172.16.1.182";
31    static String sDBDriver = "com.mysql.jdbc.Driver";
32    static String dbInstance = "DB_QQ";
33    static String sConnStr = "jdbc:mysql://" + serverName + "/" + dbInstance;
34    static String dbUser = "root";
35    static String userPwd = "123456";
36    static {
37        try {
38            Class.forName(sDBDriver);
39            DriverManager.registerDriver(new Driver());
40        } catch (Exception ex) {
41            ex.printStackTrace();
42        }
43    }
44    /** *//**
45     * ConnectionPoolElement
46     *
47     * 数据库连接数
48     */
49    class ConnectionPoolElement {
50        Connection con;
51        boolean used;
52    }
53    /** *//**
54     * 构造函数
55     *
56     * @throws SQLException
57     *
58     */
59    public ConnectionManager() throws SQLException {
60        if (connectionPool == null) {
61            connectionPool = new Vector();
62        }
63        init();
64    }
65    /** *//**
66     * Connection的取得*
67     *
68     * @throws SQLException
69     */
70    public synchronized Connection getConnection() throws SQLException {
71        ConnectionPoolElement elm = null;
72        for (;;) {
73            synchronized (connectionPool) {
74                for (int i = 0; i < connectionPool.size(); i++) {
75                    elm = (ConnectionPoolElement) (connectionPool.elementAt(i));
76                    if (!elm.used) {
77                        elm.used = true;
78                        return elm.con;
79                    }
80                }
81            }
82            // 超过最大连接数，则追加
83            if (connectionPool.size() < MAX_NUM_CONNECTION) {
84                createConnectionPool(ADD_NUM_CONNECTION);
85            } else {
86                try {
87                    this.wait(100);
88                } catch (InterruptedException e) {
89                }
90            }
91        }
92    }
93    /** *//**
94     * 连接完之后发行
95     *
96     * @param con
97     *            Connection
98     *
99     * @throws SQLException
100     */
101    public synchronized void releaseConnection(Connection con) throws SQLException {
102        ConnectionPoolElement elm;
103        synchronized (connectionPool) {
104            for (int i = 0; i < connectionPool.size(); i++) {
105                elm = (ConnectionPoolElement) (connectionPool.elementAt(i));
106                if (elm.con == con) {
107                    elm.used = false;
108                    return;
109                }
110            }
111        }
112        throw new SQLException("unknown Connection");
113    }
114    /** *//**
115     * 数据库初始化
116     *
117     * @throws SQLException
118     *
119     */
120    public void init() throws SQLException {
121        if (bInitialized)
122            return;
123        synchronized (connectionPool) {
124            if (connectionPool.size() < INIT_NUM_CONNECTION) {
125                try {
126                    // 数据库Pool的生成
127                    createConnectionPool(INIT_NUM_CONNECTION);
128                } catch (Exception ex) {
129                    ex.printStackTrace();
130                    throw new SQLException("データベース初期化エラー");
131                }
132                synchronized (this) {
133                    iRequestCount++;
134                }
135            } else {
136                synchronized (this) {
137                    iRequestCount++;
138                }
139            }
140        }
141        bInitialized = true;
142    }
143    /** *//**
144     * 从数据库断开
145     *
146     */
147    public void destroy() {
148        synchronized (this) {
149            iRequestCount--;
150        }
151        if (iRequestCount < 0) {
152            try {
153                destroyConnection();
154            } catch (SQLException ex) {
155            }
156        }
157    }
158    /** *//**
159     * 设定ConnectionPool*
160     *
161     * @param int
162     *            numConnection
163     * @throws SQLException
164     */
165    private synchronized void createConnectionPool(int numConnection) throws SQLException {
166        ConnectionPoolElement elm;
167        synchronized (connectionPool) {
168            for (int i = 0; i < numConnection; i++) {
169                elm = new ConnectionPoolElement();
170                elm.con = DriverManager.getConnection(sConnStr, dbUser, userPwd);
171                connectionPool.addElement(elm);
172            }
173        }
174    }
175    /** *//**
176     * ConnectionPool的Connection的关闭
177     *
178     * @throws SQLException
179     *
180     */
181    synchronized void destroyConnection() throws SQLException {
182        ConnectionPoolElement elm;
183        synchronized (connectionPool) {
184            for (int i = 0; i < connectionPool.size(); i++) {
185                elm = (ConnectionPoolElement) (connectionPool.elementAt(i));
186                elm.con.close();
187            }
188        }
189    }
190}

数据库操作类

  1import java.sql.Connection;
  2import java.sql.PreparedStatement;
  3import java.sql.ResultSet;
  4import java.sql.ResultSetMetaData;
  5import java.sql.SQLException;
  6import java.util.ArrayList;
  7import java.util.HashMap;
  8import java.util.List;
  9import java.util.Map;
10public class DBAccess {
11    private ConnectionManager cm = null;
12    public DBAccess() {
13        try {
14            cm = new ConnectionManager();
15        } catch (SQLException re) {
16            re.printStackTrace();
17        }
18    }
19    private Connection getConnect() throws SQLException {
20        return cm.getConnection();
21    }
22    public void releaseConnection(Connection cn) throws SQLException {
23        cm.releaseConnection(cn);
24    }
25    /** *//**
26     * 检索
27     * @param sqlId sqlid 对应于sql.properties中的id
28     * @return 执行结果 List 中保存Map，每个Map是一个条记录。Key：列名，Value：值
29     * @throws SQLException
30     */
31    public List executeQuery(String sqlId) throws SQLException {
32        List resultList = new ArrayList();
33        ResultSet resultSet = null;
34        PreparedStatement ps = null;
35        Connection cn = null;
36        try {
37            cn = getConnect();
38            ps = cn.prepareStatement(getSQL(sqlId));
39            resultSet = ps.executeQuery();
40            Map map;
41            for (; resultSet.next(); resultList.add(map)) {
42                map = doCreateRow(resultSet);
43            }
44        } catch (SQLException sqle) {
45            throw sqle;
46        } catch (NullPointerException e) {
47        } finally {
48            try {
49                resultSet.close();
50                ps.close();
51                releaseConnection(cn);
52            } catch (NullPointerException e) {
53            }
54        }
55        return resultList;
56    }
57    /** *//**
58     * 检索
59     * @param sqlId
60     * @param strParams 查找时需要的params
61     * @return
62     * @throws SQLException
63     */
64    public List executeQuery(String sqlId, String[] strParams) throws SQLException {
65        List resultList = new ArrayList();
66        ResultSet resultSet = null;
67        PreparedStatement ps = null;
68        Connection cn = null;
69        try {
70            cn = getConnect();
71            ps = cn.prepareStatement(getSQL(sqlId));
72            for (int i = 1; i <= strParams.length; i++) {
73                ps.setString(i, strParams[i - 1]);
74            }
75            resultSet = ps.executeQuery();
76            Map map;
77            for (; resultSet.next(); resultList.add(map)) {
78                map = doCreateRow(resultSet);
79            }
80        } catch (NullPointerException e) {
81        } catch (SQLException sqle) {
82            throw sqle;
83        } finally {
84            try {
85                resultSet.close();
86                ps.close();
87                releaseConnection(cn);
88            } catch (NullPointerException e) {
89            }
90        }
91        return resultList;
92    }
93    /** *//**
94     * 更新DB
95     * @param sqlId
96     * @return
97     * @throws SQLException
98     */
99    public int executeUpdate(String sqlId) throws SQLException {
100        int count = 0;
101        Connection cn = null;
102        PreparedStatement ps = null;
103        try {
104            cn = getConnect();
105            ps = cn.prepareStatement(getSQL(sqlId));
106            count = ps.executeUpdate();
107            cn.commit();
108        } catch (SQLException sqle) {
109            throw sqle;
110        } catch (NullPointerException e) {
111        } finally {
112            try {
113                ps.close();
114                releaseConnection(cn);
115            } catch (NullPointerException e) {
116            }
117        }
118        return count;
119    }
120    /** *//**
121     * 更新DB
122     * @param sqlId
123     * @param lsParam
124     * @return
125     * @throws SQLException
126     */
127    public int executeUpdate(String sqlId, String[] lsParam) throws SQLException {
128        int count = 0;
129        Connection cn = null;
130        PreparedStatement ps = null;
131        try {
132            cn = getConnect();
133            ps = cn.prepareStatement(getSQL(sqlId));
134            for (int i = 1; i <= lsParam.length; i++) {
135                ps.setString(i, lsParam[i - 1]);
136            }
137            count = ps.executeUpdate();
138            // cn.commit();
139        } catch (SQLException sqle) {
140            throw sqle;
141        } catch (NullPointerException e) {
142        } finally {
143            try {
144                ps.close();
145                releaseConnection(cn);
146            } catch (NullPointerException e) {
147            }
148        }
149        return count;
150    }
151    /** *//**
152     * 根据id取得sql文
153     * @param sqlId
154     * @return
155     * @throws SQLException
156     */
157    private String getSQL(String sqlId) throws SQLException {
158        String sqlData = "";
159        if (sqlId == null || sqlId.length() == 0) {
160            throw new SQLException();
161        } else {
162            Map sqlMap = ResourceReader.getSqlMap();
163            sqlData = (String) sqlMap.get(sqlId);
164            if (sqlData.trim().length() == 0) {
165                throw new SQLException();
166            } else {
167                return sqlData;
168            }
169        }
170    }
171    /** *//**
172     * 将执行sql文的结果放在List中
173     * @param resultSet
174     * @return
175     * @throws SQLException
176     */
177    private final Map doCreateRow(ResultSet resultSet) throws SQLException {
178        Map result = new HashMap();
179        try {
180            ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
181            int count = resultSetMetaData.getColumnCount();
182            for (int i = 1; i <= count; i++) {
183                String label = resultSetMetaData.getColumnLabel(i);
184                Object value = resultSet.getObject(i);
185                result.put(label.toUpperCase(), value);
186            }
187        } catch (SQLException e) {
188            throw e;
189        }
190        return result;
191    }
192}

根据sqlid 取得sql文

1import java.util.Enumeration;
2import java.util.HashMap;
3import java.util.Map;
4import java.util.MissingResourceException;
5import java.util.ResourceBundle;
6/** *//**
7 * @author bzwm
8 *
9 */
10public class ResourceReader {
11    //sql.properties的路径，根据自己需要配置
12    private static final String _path = "com.chat.commons.property.sql";
13    private static ResourceReader _instance = null;
14    private Map _sqlMap = new HashMap();
15    private ResourceReader() {
16        try {
17            ResourceBundle bundle = ResourceBundle.getBundle(_path);
18            Enumeration enumeration = bundle.getKeys();
19            while (enumeration.hasMoreElements()) {
20                String key = (String) enumeration.nextElement();
21                _sqlMap.put(key, bundle.getString(key));
22            }
23        } catch (MissingResourceException e) {
24            e.printStackTrace();
25        }
26    }
27    public synchronized static void initConfigFile() {
28        if (_instance == null)
29            _instance = new ResourceReader();
30    }
31    public static Map getSqlMap() {
32        return _instance._sqlMap;
33    }
34    public static void main(String args[]) {
35        new ResourceReader();
36    }
37}

sql.properties
保存sql语句

1sql001=select password from t_qq_user where qq_num = ?

用法：

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

----2009年02月03日

posted on 2010-09-01 11:36 李明阅读(1377) 评论(0) 编辑收藏所属分类: J2EE 、Java

新用户注册刷新评论列表


只有注册用户登录后才能发表评论。




网站导航: 博客园 IT新闻知识库 C++博客博问管理
相关文章: Hessian构建分布式系统应用[续] Hessian构建分布式系统应用 java实现数据库连接池

ALL is Well！

公告

常用链接

留言簿(3)

随笔分类

随笔档案

文章分类

文章档案

交流社区

友情链接

学习资源库

搜索

最新评论

阅读排行榜

评论排行榜