ALL is Well！

敏捷是一條很長的路，摸索著前進著

BlogJava :: 首頁 :: 新隨筆 :: 聯系 :: 聚合

:: 管理 ::

30 隨筆 :: 23 文章 :: 71 評論 :: 0 Trackbacks

<

2010年9月

>

日

一

二

三

四

五

六

29

30

31

2

5

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

1

2

3

4

5

6

7

8

9

公告

常用鏈接

留言簿(3)

隨筆分類

隨筆檔案

文章分類

文章檔案

搜索

閱讀排行榜

評論排行榜

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 李明閱讀(1388) 評論(0) 編輯收藏所屬分類: J2EE 、Java

新用戶注冊刷新評論列表


只有注冊用戶登錄后才能發表評論。




網站導航: 博客園 IT新聞 Chat2DB C++博客博問管理
相關文章: Hessian構建分布式系統應用[續] Hessian構建分布式系統應用 java實現數據庫連接池

ALL is Well！

公告

常用鏈接

留言簿(3)

隨筆分類

隨筆檔案

文章分類

文章檔案

交流社區

友情鏈接

學習資源庫

搜索

最新評論

閱讀排行榜

評論排行榜