连接池
1 package kl.db;
2
3 import java.sql.Connection;
4 import java.sql.DriverManager;
5 import java.sql.PreparedStatement;
6 import java.sql.ResultSet;
7 import java.sql.SQLException;
8 import java.sql.Statement;
9 import java.util.Stack;
10
11 /*
12 * 驱动:JTDS
13 * 需放在Java\jre6\lib\ext 中
14 *
15 * 使用说明:使用本类请不要New ConnManagerPool;
16 * 要获得本类的对象请使用函数getInstance();
17 * 用例:ConnManagerPool myConn;
18 * myConn = ConnManagerPool.getInstance();
19 */
20 public class ConnManagerPool {
21
22 /*
23 * 使用SQL server 2000 + 数据库驱动STDS 的连接参数
24 */
25 final String driver = "net.sourceforge.jtds.jdbc.Driver";
26 final String url = "jdbc:jtds:sqlserver://localhost:1433;DatabaseName=MarketManager";
27 final String user="sa";
28 final String password="sa";
29
30 /*
31 * 数据库连接池的保存栈
32 */
33 private int minConn = 0;
34 private int maxConn = 10;
35 private int connAmount = 0;
36 private Stack<Connection> connStack = new Stack<Connection>();
37
38 /*
39 * 全局共享一个数据库连接类
40 */
41 private static ConnManagerPool instance;
42 public static ConnManagerPool getInstance(){
43 if( instance == null )
44 instance = new ConnManagerPool();
45 return instance;
46 }
47
48 /*
49 * 构造函数:初始化自动生成最少数量的连接Connection;
50 */
51 public ConnManagerPool() {
52 for(int i = 0; i < minConn; i++ )
53 connStack.push( newConnection() );
54 }
55
56 /*
57 * 功能:内部使用，生成一个数据库连接并返回
58 *if success return default connection, failed return null;
59 */
60 public Connection newConnection() {
61 Connection conn = null;
62 try {
63 Class.forName( driver );
64 conn= DriverManager.getConnection(url,user,password);
65 connAmount++;
66 } catch (ClassNotFoundException e) {
67 conn = null;
68 } catch (SQLException e) {
69 conn = null;
70 }
71 return conn;
72 }
73
74 /*
75 * 功能:通过累对象返回的数据库连接
76 *if success return default connection, failed return null;
77 */
78 public Connection getConnection() {
79 Connection conn = null;
80 if( !connStack.isEmpty() ){
81 conn = connStack.pop();
82 }else if( connAmount < maxConn ){
83 conn = newConnection();
84 }else{
85 try {
86 wait( 100000 );
87 return getConnection();
88 } catch ( InterruptedException e) {
89 conn = null;
90 }
91 }
92 return conn;
93 }
94
95 /*
96 * 功能:释放数据库连接，并压回连接池
97 */
98 public void freeConnection( Connection conn ) {
99 try{
100 if( conn != null )
101 {
102 connStack.push( conn );
103 notifyAll();
104 }
105 }catch( Exception e){
106
107 }
108 }
109
110 /*
111 * 功能:Statement，通过类名调用
112 * if success return a Statement, failed return null;
113 */
114 public Statement getStatement( Connection conn ) {
115 Statement stmt = null;
116 try {
117 if( conn != null ) {
118 stmt = conn.createStatement();
119 }
120 } catch ( SQLException e ) {
121 stmt = null;
122
123 }
124 return stmt;
125 }
126
127 /*
128 * 功能:PreparedStatement，通过类名调用
129 * if success return a PreparedStatement, failed return null;
130 */
131 public PreparedStatement getPreparedStatement(Connection conn,String sql) {
132 PreparedStatement pstmt = null;
133 try {
134 if( conn != null ) {
135 pstmt = conn.prepareStatement(sql);
136 }
137 } catch ( SQLException e ) {
138 pstmt = null;
139 }
140 return pstmt;
141 }
142
143 /*
144 * 功能:通过SQL语句，返回结果集，通过类名调用
145 * if success return data of database, failed return null;
146 */
147 public ResultSet executeQuery( Statement stmt, String sql ) {
148 ResultSet rs = null;
149 try {
150 if(stmt != null) {
151 rs = stmt.executeQuery(sql);
152 }
153 } catch (SQLException e) {
154 rs = null;
155 }
156 return rs;
157 }
158
159 public ResultSet executeQuery( PreparedStatement pstmt) {
160 ResultSet rs = null;
161 try {
162 if(pstmt != null) {
163 rs = pstmt.executeQuery();
164 }
165 } catch (SQLException e) {
166 rs = null;
167 }
168 return rs;
169 }
170
171 /*
172 * 功能:根据SQL代码更新数据库
173 * if success return true, failed return false;
174 */
175 public boolean executeUpdate( Statement stmt, String sql ) {
176 boolean mark = false;
177 try {
178 if(stmt != null) {
179 stmt.executeUpdate(sql);
180 }
181 mark = true;
182 } catch (SQLException e) {
183 mark = false;
184 }
185 return mark;
186 }
187
188 public boolean executeUpdate( PreparedStatement pstmt) {
189 boolean mark = false;
190 try {
191 if(pstmt != null) {
192 pstmt.executeUpdate();
193 }
194 mark = true;
195 } catch (SQLException e) {
196 mark = false;
197 }
198 return mark;
199 }
200
201 /*
202 * 功能:关闭ResultSet
203 * if success return true, failed return false;
204 */
205 public boolean close(ResultSet rs) {
206 boolean mark = false;
207 try {
208 if( rs != null ) {
209 rs.close();
210 rs = null;
211 }
212 mark = true;
213 } catch ( SQLException e ) {
214 mark = false;
215 }
216 return mark;
217 }
218
219 /*
220 * 功能:关闭Statement
221 * if success return true, failed return false;
222 */
223 public boolean close(Statement stmt) {
224 boolean mark = false;
225 try {
226 if( stmt != null ) {
227 stmt.close();
228 stmt = null;
229 }
230 mark = true;
231 } catch ( SQLException e ) {
232 mark = false;
233 }
234 return mark;
235 }
236
237 /*
238 * 功能:关闭PreparedStatement
239 * if success return true, failed return false;
240 */
241 public boolean close(PreparedStatement pstmt) {
242 boolean mark = false;
243 try {
244 if( pstmt != null ) {
245 pstmt.close();
246 pstmt = null;
247 }
248 mark = true;
249 } catch ( SQLException e ) {
250 mark = false;
251 }
252 return mark;
253 }
254
255 /*
256 * 功能:关闭Connection
257 * if success return true, failed return false;
258 */
259 public boolean close(Connection conn) {
260 boolean mark = false;
261 try {
262 if( conn != null ) {
263 conn.close();
264 conn = null;
265 }
266 mark = true;
267 } catch ( SQLException e ) {
268 mark = false;
269 }
270 return mark;
271 }
272 }
273
274
275
2
3 import java.sql.Connection;
4 import java.sql.DriverManager;
5 import java.sql.PreparedStatement;
6 import java.sql.ResultSet;
7 import java.sql.SQLException;
8 import java.sql.Statement;
9 import java.util.Stack;
10
11 /*
12 * 驱动:JTDS
13 * 需放在Java\jre6\lib\ext 中
14 *
15 * 使用说明:使用本类请不要New ConnManagerPool;
16 * 要获得本类的对象请使用函数getInstance();
17 * 用例:ConnManagerPool myConn;
18 * myConn = ConnManagerPool.getInstance();
19 */
20 public class ConnManagerPool {
21
22 /*
23 * 使用SQL server 2000 + 数据库驱动STDS 的连接参数
24 */
25 final String driver = "net.sourceforge.jtds.jdbc.Driver";
26 final String url = "jdbc:jtds:sqlserver://localhost:1433;DatabaseName=MarketManager";
27 final String user="sa";
28 final String password="sa";
29
30 /*
31 * 数据库连接池的保存栈
32 */
33 private int minConn = 0;
34 private int maxConn = 10;
35 private int connAmount = 0;
36 private Stack<Connection> connStack = new Stack<Connection>();
37
38 /*
39 * 全局共享一个数据库连接类
40 */
41 private static ConnManagerPool instance;
42 public static ConnManagerPool getInstance(){
43 if( instance == null )
44 instance = new ConnManagerPool();
45 return instance;
46 }
47
48 /*
49 * 构造函数:初始化自动生成最少数量的连接Connection;
50 */
51 public ConnManagerPool() {
52 for(int i = 0; i < minConn; i++ )
53 connStack.push( newConnection() );
54 }
55
56 /*
57 * 功能:内部使用，生成一个数据库连接并返回
58 *if success return default connection, failed return null;
59 */
60 public Connection newConnection() {
61 Connection conn = null;
62 try {
63 Class.forName( driver );
64 conn= DriverManager.getConnection(url,user,password);
65 connAmount++;
66 } catch (ClassNotFoundException e) {
67 conn = null;
68 } catch (SQLException e) {
69 conn = null;
70 }
71 return conn;
72 }
73
74 /*
75 * 功能:通过累对象返回的数据库连接
76 *if success return default connection, failed return null;
77 */
78 public Connection getConnection() {
79 Connection conn = null;
80 if( !connStack.isEmpty() ){
81 conn = connStack.pop();
82 }else if( connAmount < maxConn ){
83 conn = newConnection();
84 }else{
85 try {
86 wait( 100000 );
87 return getConnection();
88 } catch ( InterruptedException e) {
89 conn = null;
90 }
91 }
92 return conn;
93 }
94
95 /*
96 * 功能:释放数据库连接，并压回连接池
97 */
98 public void freeConnection( Connection conn ) {
99 try{
100 if( conn != null )
101 {
102 connStack.push( conn );
103 notifyAll();
104 }
105 }catch( Exception e){
106
107 }
108 }
109
110 /*
111 * 功能:Statement，通过类名调用
112 * if success return a Statement, failed return null;
113 */
114 public Statement getStatement( Connection conn ) {
115 Statement stmt = null;
116 try {
117 if( conn != null ) {
118 stmt = conn.createStatement();
119 }
120 } catch ( SQLException e ) {
121 stmt = null;
122
123 }
124 return stmt;
125 }
126
127 /*
128 * 功能:PreparedStatement，通过类名调用
129 * if success return a PreparedStatement, failed return null;
130 */
131 public PreparedStatement getPreparedStatement(Connection conn,String sql) {
132 PreparedStatement pstmt = null;
133 try {
134 if( conn != null ) {
135 pstmt = conn.prepareStatement(sql);
136 }
137 } catch ( SQLException e ) {
138 pstmt = null;
139 }
140 return pstmt;
141 }
142
143 /*
144 * 功能:通过SQL语句，返回结果集，通过类名调用
145 * if success return data of database, failed return null;
146 */
147 public ResultSet executeQuery( Statement stmt, String sql ) {
148 ResultSet rs = null;
149 try {
150 if(stmt != null) {
151 rs = stmt.executeQuery(sql);
152 }
153 } catch (SQLException e) {
154 rs = null;
155 }
156 return rs;
157 }
158
159 public ResultSet executeQuery( PreparedStatement pstmt) {
160 ResultSet rs = null;
161 try {
162 if(pstmt != null) {
163 rs = pstmt.executeQuery();
164 }
165 } catch (SQLException e) {
166 rs = null;
167 }
168 return rs;
169 }
170
171 /*
172 * 功能:根据SQL代码更新数据库
173 * if success return true, failed return false;
174 */
175 public boolean executeUpdate( Statement stmt, String sql ) {
176 boolean mark = false;
177 try {
178 if(stmt != null) {
179 stmt.executeUpdate(sql);
180 }
181 mark = true;
182 } catch (SQLException e) {
183 mark = false;
184 }
185 return mark;
186 }
187
188 public boolean executeUpdate( PreparedStatement pstmt) {
189 boolean mark = false;
190 try {
191 if(pstmt != null) {
192 pstmt.executeUpdate();
193 }
194 mark = true;
195 } catch (SQLException e) {
196 mark = false;
197 }
198 return mark;
199 }
200
201 /*
202 * 功能:关闭ResultSet
203 * if success return true, failed return false;
204 */
205 public boolean close(ResultSet rs) {
206 boolean mark = false;
207 try {
208 if( rs != null ) {
209 rs.close();
210 rs = null;
211 }
212 mark = true;
213 } catch ( SQLException e ) {
214 mark = false;
215 }
216 return mark;
217 }
218
219 /*
220 * 功能:关闭Statement
221 * if success return true, failed return false;
222 */
223 public boolean close(Statement stmt) {
224 boolean mark = false;
225 try {
226 if( stmt != null ) {
227 stmt.close();
228 stmt = null;
229 }
230 mark = true;
231 } catch ( SQLException e ) {
232 mark = false;
233 }
234 return mark;
235 }
236
237 /*
238 * 功能:关闭PreparedStatement
239 * if success return true, failed return false;
240 */
241 public boolean close(PreparedStatement pstmt) {
242 boolean mark = false;
243 try {
244 if( pstmt != null ) {
245 pstmt.close();
246 pstmt = null;
247 }
248 mark = true;
249 } catch ( SQLException e ) {
250 mark = false;
251 }
252 return mark;
253 }
254
255 /*
256 * 功能:关闭Connection
257 * if success return true, failed return false;
258 */
259 public boolean close(Connection conn) {
260 boolean mark = false;
261 try {
262 if( conn != null ) {
263 conn.close();
264 conn = null;
265 }
266 mark = true;
267 } catch ( SQLException e ) {
268 mark = false;
269 }
270 return mark;
271 }
272 }
273
274
275
