001 /*
002 * Licensed to the Apache Software Foundation (ASF) under one or more
003 * contributor license agreements. See the NOTICE file distributed with
004 * this work for additional information regarding copyright ownership.
005 * The ASF licenses this file to You under the Apache License, Version 2.0
006 * (the "License"); you may not use this file except in compliance with
007 * the License. You may obtain a copy of the License at
008 *
009 * http://www.apache.org/licenses/LICENSE-2.0
010 *
011 * Unless required by applicable law or agreed to in writing, software
012 * distributed under the License is distributed on an "AS IS" BASIS,
013 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
014 * See the License for the specific language governing permissions and
015 * limitations under the License.
016 */
017
018 package org.apache.commons.dbcp;
019
020 import java.sql.CallableStatement;
021 import java.sql.Connection;
022 import java.sql.PreparedStatement;
023 import java.sql.SQLException;
024
025 import java.util.NoSuchElementException;
026
027 import org.apache.commons.pool.KeyedObjectPool;
028 import org.apache.commons.pool.KeyedPoolableObjectFactory;
029
030 /**
031 * A {@link DelegatingConnection} that pools {@link PreparedStatement}s.
032 * <p>
033 * The {@link #prepareStatement} and {@link #prepareCall} methods, rather than creating a new PreparedStatement
034 * each time, may actually pull the statement from a pool of unused statements.
035 * The {@link PreparedStatement#close} method of the returned statement doesn't
036 * actually close the statement, but rather returns it to the pool.
037 * (See {@link PoolablePreparedStatement}, {@link PoolableCallableStatement}.)
038 *
039 *
040 * @see PoolablePreparedStatement
041 * @author Rodney Waldhoff
042 * @author Dirk Verbeeck
043 * @version $Revision: 885261 $ $Date: 2009-11-29 15:07:02 -0500 (Sun, 29 Nov 2009) $
044 */
045 public class PoolingConnection extends DelegatingConnection implements Connection, KeyedPoolableObjectFactory {
046 /** Pool of {@link PreparedStatement}s. and {@link CallableStatement}s */
047 protected KeyedObjectPool _pstmtPool = null;
048
049 /** Prepared Statement type */
050 private static final byte STATEMENT_PREPAREDSTMT = 0;
051
052 /** Callable Statement type */
053 private static final byte STATEMENT_CALLABLESTMT = 1;
054
055
056 /**
057 * Constructor.
058 * @param c the underlying {@link Connection}.
059 */
060 public PoolingConnection(Connection c) {
061 super(c);
062 }
063
064 /**
065 * Constructor.
066 * @param c the underlying {@link Connection}.
067 * @param pool {@link KeyedObjectPool} of {@link PreparedStatement}s and {@link CallableStatement}s.
068 */
069 public PoolingConnection(Connection c, KeyedObjectPool pool) {
070 super(c);
071 _pstmtPool = pool;
072 }
073
074
075 /**
076 * Close and free all {@link PreparedStatement}s or {@link CallableStatement} from the pool, and
077 * close the underlying connection.
078 */
079 public synchronized void close() throws SQLException {
080 if(null != _pstmtPool) {
081 KeyedObjectPool oldpool = _pstmtPool;
082 _pstmtPool = null;
083 try {
084 oldpool.close();
085 } catch(RuntimeException e) {
086 throw e;
087 } catch(SQLException e) {
088 throw e;
089 } catch(Exception e) {
090 throw (SQLException) new SQLException("Cannot close connection").initCause(e);
091 }
092 }
093 getInnermostDelegate().close();
094 }
095
096 /**
097 * Create or obtain a {@link PreparedStatement} from the pool.
098 * @param sql the sql string used to define the PreparedStatement
099 * @return a {@link PoolablePreparedStatement}
100 */
101 public PreparedStatement prepareStatement(String sql) throws SQLException {
102 if (null == _pstmtPool) {
103 throw new SQLException(
104 "Statement pool is null - closed or invalid PoolingConnection.");
105 }
106 try {
107 return(PreparedStatement)(_pstmtPool.borrowObject(createKey(sql)));
108 } catch(NoSuchElementException e) {
109 throw (SQLException) new SQLException("MaxOpenPreparedStatements limit reached").initCause(e);
110 } catch(RuntimeException e) {
111 throw e;
112 } catch(Exception e) {
113 throw new SQLNestedException("Borrow prepareStatement from pool failed", e);
114 }
115 }
116
117 /**
118 * Create or obtain a {@link PreparedStatement} from the pool.
119 * @param sql the sql string used to define the PreparedStatement
120 * @param resultSetType result set type
121 * @param resultSetConcurrency result set concurrency
122 * @return a {@link PoolablePreparedStatement}
123 */
124 public PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency) throws SQLException {
125 if (null == _pstmtPool) {
126 throw new SQLException(
127 "Statement pool is null - closed or invalid PoolingConnection.");
128 }
129 try {
130 return(PreparedStatement)(_pstmtPool.borrowObject(createKey(sql,resultSetType,resultSetConcurrency)));
131 } catch(NoSuchElementException e) {
132 throw (SQLException) new SQLException("MaxOpenPreparedStatements limit reached").initCause(e);
133 } catch(RuntimeException e) {
134 throw e;
135 } catch(Exception e) {
136 throw (SQLException) new SQLException("Borrow prepareStatement from pool failed").initCause(e);
137 }
138 }
139
140 /**
141 * Create or obtain a {@link CallableStatement} from the pool.
142 * @param sql the sql string used to define the CallableStatement
143 * @return a {@link PoolableCallableStatement}
144 * @throws SQLException
145 * @since 1.3
146 */
147 public CallableStatement prepareCall(String sql) throws SQLException {
148 try {
149 return (CallableStatement) (_pstmtPool.borrowObject(createKey(sql, STATEMENT_CALLABLESTMT)));
150 } catch (NoSuchElementException e) {
151 throw new SQLNestedException("MaxOpenCallableStatements limit reached", e);
152 } catch (RuntimeException e) {
153 throw e;
154 } catch (Exception e) {
155 throw new SQLNestedException("Borrow callableStatement from pool failed", e);
156 }
157 }
158
159 /**
160 * Create or obtain a {@link CallableStatement} from the pool.
161 * @param sql the sql string used to define the CallableStatement
162 * @param resultSetType result set type
163 * @param resultSetConcurrency result set concurrency
164 * @return a {@link PoolableCallableStatement}
165 * @throws SQLException
166 * @since 1.3
167 */
168 public CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency) throws SQLException {
169 try {
170 return (CallableStatement) (_pstmtPool.borrowObject(createKey(sql, resultSetType,
171 resultSetConcurrency, STATEMENT_CALLABLESTMT)));
172 } catch (NoSuchElementException e) {
173 throw new SQLNestedException("MaxOpenCallableStatements limit reached", e);
174 } catch (RuntimeException e) {
175 throw e;
176 } catch (Exception e) {
177 throw new SQLNestedException("Borrow callableStatement from pool failed", e);
178 }
179 }
180
181
182 // TODO: possible enhancement, cache these preparedStatements as well
183
184 // public PreparedStatement prepareStatement(String sql, int resultSetType,
185 // int resultSetConcurrency,
186 // int resultSetHoldability)
187 // throws SQLException {
188 // return super.prepareStatement(
189 // sql, resultSetType, resultSetConcurrency, resultSetHoldability);
190 // }
191 //
192 // public PreparedStatement prepareStatement(String sql, int autoGeneratedKeys)
193 // throws SQLException {
194 // return super.prepareStatement(sql, autoGeneratedKeys);
195 // }
196 //
197 // public PreparedStatement prepareStatement(String sql, int columnIndexes[])
198 // throws SQLException {
199 // return super.prepareStatement(sql, columnIndexes);
200 // }
201 //
202 // public PreparedStatement prepareStatement(String sql, String columnNames[])
203 // throws SQLException {
204 // return super.prepareStatement(sql, columnNames);
205 // }
206
207 /**
208 * Create a PStmtKey for the given arguments.
209 * @param sql the sql string used to define the statement
210 * @param resultSetType result set type
211 * @param resultSetConcurrency result set concurrency
212 */
213 protected Object createKey(String sql, int resultSetType, int resultSetConcurrency) {
214 String catalog = null;
215 try {
216 catalog = getCatalog();
217 } catch (SQLException e) {}
218 return new PStmtKey(normalizeSQL(sql), catalog, resultSetType, resultSetConcurrency);
219 }
220
221 /**
222 * Create a PStmtKey for the given arguments.
223 * @param sql the sql string used to define the statement
224 * @param resultSetType result set type
225 * @param resultSetConcurrency result set concurrency
226 * @param stmtType statement type - either {@link #STATEMENT_CALLABLESTMT} or {@link #STATEMENT_PREPAREDSTMT}
227 */
228 protected Object createKey(String sql, int resultSetType, int resultSetConcurrency, byte stmtType) {
229 String catalog = null;
230 try {
231 catalog = getCatalog();
232 } catch (SQLException e) {}
233 return new PStmtKey(normalizeSQL(sql), catalog, resultSetType, resultSetConcurrency, stmtType);
234 }
235
236 /**
237 * Create a PStmtKey for the given arguments.
238 * @param sql the sql string used to define the statement
239 */
240 protected Object createKey(String sql) {
241 String catalog = null;
242 try {
243 catalog = getCatalog();
244 } catch (SQLException e) {}
245 return new PStmtKey(normalizeSQL(sql), catalog);
246 }
247
248 /**
249 * Create a PStmtKey for the given arguments.
250 * @param sql the sql string used to define the statement
251 * @param stmtType statement type - either {@link #STATEMENT_CALLABLESTMT} or {@link #STATEMENT_PREPAREDSTMT}
252 */
253 protected Object createKey(String sql, byte stmtType) {
254 String catalog = null;
255 try {
256 catalog = getCatalog();
257 } catch (SQLException e) {}
258 return new PStmtKey(normalizeSQL(sql), catalog, stmtType);
259 }
260
261 /**
262 * Normalize the given SQL statement, producing a
263 * cannonical form that is semantically equivalent to the original.
264 */
265 protected String normalizeSQL(String sql) {
266 return sql.trim();
267 }
268
269 /**
270 * {@link KeyedPoolableObjectFactory} method for creating
271 * {@link PoolablePreparedStatement}s or {@link PoolableCallableStatement}s.
272 * The <code>stmtType</code> field in the key determines whether
273 * a PoolablePreparedStatement or PoolableCallableStatement is created.
274 *
275 * @param obj the key for the {@link PreparedStatement} to be created
276 * @see #createKey(String, int, int, byte)
277 */
278 public Object makeObject(Object obj) throws Exception {
279 if(null == obj || !(obj instanceof PStmtKey)) {
280 throw new IllegalArgumentException("Prepared statement key is null or invalid.");
281 } else {
282 PStmtKey key = (PStmtKey)obj;
283 if( null == key._resultSetType && null == key._resultSetConcurrency ) {
284 if (key._stmtType == STATEMENT_PREPAREDSTMT ) {
285 return new PoolablePreparedStatement(getDelegate().prepareStatement( key._sql), key, _pstmtPool, this);
286 } else {
287 return new PoolableCallableStatement(getDelegate().prepareCall( key._sql), key, _pstmtPool, this);
288 }
289 } else { // Both _resultSetType and _resultSetConcurrency are non-null here (both or neither are set by constructors)
290 if(key._stmtType == STATEMENT_PREPAREDSTMT) {
291 return new PoolablePreparedStatement(getDelegate().prepareStatement(
292 key._sql, key._resultSetType.intValue(),key._resultSetConcurrency.intValue()), key, _pstmtPool, this);
293 } else {
294 return new PoolableCallableStatement( getDelegate().prepareCall(
295 key._sql,key._resultSetType.intValue(), key._resultSetConcurrency.intValue()), key, _pstmtPool, this);
296 }
297 }
298 }
299 }
300
301 /**
302 * {@link KeyedPoolableObjectFactory} method for destroying
303 * PoolablePreparedStatements and PoolableCallableStatements.
304 * Closes the underlying statement.
305 *
306 * @param key ignored
307 * @param obj the pooled statement to be destroyed.
308 */
309 public void destroyObject(Object key, Object obj) throws Exception {
310 if(obj instanceof DelegatingPreparedStatement) {
311 ((DelegatingPreparedStatement)obj).getInnermostDelegate().close();
312 } else {
313 ((PreparedStatement)obj).close();
314 }
315 }
316
317 /**
318 * {@link KeyedPoolableObjectFactory} method for validating
319 * pooled statements. Currently always returns true.
320 *
321 * @param key ignored
322 * @param obj ignored
323 * @return <tt>true</tt>
324 */
325 public boolean validateObject(Object key, Object obj) {
326 return true;
327 }
328
329 /**
330 * {@link KeyedPoolableObjectFactory} method for activating
331 * pooled statements.
332 *
333 * @param key ignored
334 * @param obj pooled statement to be activated
335 */
336 public void activateObject(Object key, Object obj) throws Exception {
337 ((DelegatingPreparedStatement)obj).activate();
338 }
339
340 /**
341 * {@link KeyedPoolableObjectFactory} method for passivating
342 * {@link PreparedStatement}s or {@link CallableStatement}s.
343 * Invokes {@link PreparedStatement#clearParameters}.
344 *
345 * @param key ignored
346 * @param obj a {@link PreparedStatement}
347 */
348 public void passivateObject(Object key, Object obj) throws Exception {
349 ((PreparedStatement)obj).clearParameters();
350 ((DelegatingPreparedStatement)obj).passivate();
351 }
352
353 public String toString() {
354 if (_pstmtPool != null ) {
355 return "PoolingConnection: " + _pstmtPool.toString();
356 } else {
357 return "PoolingConnection: null";
358 }
359 }
360
361 /**
362 * A key uniquely identifiying {@link PreparedStatement}s.
363 */
364 static class PStmtKey {
365
366 /** SQL defining Prepared or Callable Statement */
367 protected String _sql = null;
368
369 /** Result set type */
370 protected Integer _resultSetType = null;
371
372 /** Result set concurrency */
373 protected Integer _resultSetConcurrency = null;
374
375 /** Database catalog */
376 protected String _catalog = null;
377
378 /**
379 * Statement type. Either STATEMENT_PREPAREDSTMT (PreparedStatement)
380 * or STATEMENT_CALLABLESTMT (CallableStatement)
381 */
382 protected byte _stmtType = STATEMENT_PREPAREDSTMT;
383
384 PStmtKey(String sql) {
385 _sql = sql;
386 }
387
388 PStmtKey(String sql, String catalog) {
389 _sql = sql;
390 _catalog = catalog;
391 }
392
393 PStmtKey(String sql, String catalog, byte stmtType) {
394 _sql = sql;
395 _catalog = catalog;
396 _stmtType = stmtType;
397 }
398
399 PStmtKey(String sql, int resultSetType, int resultSetConcurrency) {
400 _sql = sql;
401 _resultSetType = new Integer(resultSetType);
402 _resultSetConcurrency = new Integer(resultSetConcurrency);
403 }
404
405 PStmtKey(String sql, String catalog, int resultSetType, int resultSetConcurrency) {
406 _sql = sql;
407 _catalog = catalog;
408 _resultSetType = new Integer(resultSetType);
409 _resultSetConcurrency = new Integer(resultSetConcurrency);
410 }
411
412 PStmtKey(String sql, String catalog, int resultSetType, int resultSetConcurrency, byte stmtType) {
413 _sql = sql;
414 _catalog = catalog;
415 _resultSetType = new Integer(resultSetType);
416 _resultSetConcurrency = new Integer(resultSetConcurrency);
417 _stmtType = stmtType;
418 }
419
420 public boolean equals(Object that) {
421 try {
422 PStmtKey key = (PStmtKey)that;
423 return( ((null == _sql && null == key._sql) || _sql.equals(key._sql)) &&
424 ((null == _catalog && null == key._catalog) || _catalog.equals(key._catalog)) &&
425 ((null == _resultSetType && null == key._resultSetType) || _resultSetType.equals(key._resultSetType)) &&
426 ((null == _resultSetConcurrency && null == key._resultSetConcurrency) || _resultSetConcurrency.equals(key._resultSetConcurrency)) &&
427 (_stmtType == key._stmtType)
428 );
429 } catch(ClassCastException e) {
430 return false;
431 } catch(NullPointerException e) {
432 return false;
433 }
434 }
435
436 public int hashCode() {
437 if (_catalog==null)
438 return(null == _sql ? 0 : _sql.hashCode());
439 else
440 return(null == _sql ? _catalog.hashCode() : (_catalog + _sql).hashCode());
441 }
442
443 public String toString() {
444 StringBuffer buf = new StringBuffer();
445 buf.append("PStmtKey: sql=");
446 buf.append(_sql);
447 buf.append(", catalog=");
448 buf.append(_catalog);
449 buf.append(", resultSetType=");
450 buf.append(_resultSetType);
451 buf.append(", resultSetConcurrency=");
452 buf.append(_resultSetConcurrency);
453 buf.append(", statmentType=");
454 buf.append(_stmtType);
455 return buf.toString();
456 }
457 }
458 }