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
018package org.apache.commons.dbcp2;
019
020import java.sql.CallableStatement;
021import java.sql.Connection;
022import java.sql.PreparedStatement;
023import java.sql.SQLException;
024import java.util.NoSuchElementException;
025
026import org.apache.commons.pool2.KeyedObjectPool;
027import org.apache.commons.pool2.KeyedPooledObjectFactory;
028import org.apache.commons.pool2.PooledObject;
029import org.apache.commons.pool2.impl.DefaultPooledObject;
030
031/**
032 * A {@link DelegatingConnection} that pools {@link PreparedStatement}s.
033 * <p>
034 * The {@link #prepareStatement} and {@link #prepareCall} methods, rather than
035 * creating a new PreparedStatement each time, may actually pull the statement
036 * from a pool of unused statements.
037 * The {@link PreparedStatement#close} method of the returned statement doesn't
038 * actually close the statement, but rather returns it to the pool.
039 * (See {@link PoolablePreparedStatement}, {@link PoolableCallableStatement}.)
040 *
041 *
042 * @see PoolablePreparedStatement
043 * @author Rodney Waldhoff
044 * @author Dirk Verbeeck
045 * @version $Revision: 1572244 $ $Date: 2014-02-26 20:38:08 +0000 (Wed, 26 Feb 2014) $
046 * @since 2.0
047 */
048public class PoolingConnection extends DelegatingConnection<Connection>
049        implements KeyedPooledObjectFactory<PStmtKey,DelegatingPreparedStatement> {
050
051    /** Pool of {@link PreparedStatement}s. and {@link CallableStatement}s */
052    private KeyedObjectPool<PStmtKey,DelegatingPreparedStatement> _pstmtPool = null;
053
054    /**
055     * Constructor.
056     * @param c the underlying {@link Connection}.
057     */
058    public PoolingConnection(Connection c) {
059        super(c);
060    }
061
062
063    public void setStatementPool(
064            KeyedObjectPool<PStmtKey,DelegatingPreparedStatement> pool) {
065        _pstmtPool = pool;
066    }
067
068
069    /**
070     * Close and free all {@link PreparedStatement}s or
071     * {@link CallableStatement}s from the pool, and close the underlying
072     * connection.
073     */
074    @Override
075    public synchronized void close() throws SQLException {
076        try {
077            if (null != _pstmtPool) {
078                KeyedObjectPool<PStmtKey,DelegatingPreparedStatement> oldpool = _pstmtPool;
079                _pstmtPool = null;
080                try {
081                    oldpool.close();
082                } catch(RuntimeException e) {
083                    throw e;
084                } catch(Exception e) {
085                    throw new SQLException("Cannot close connection", e);
086                }
087            }
088        } finally {
089            try {
090                getDelegateInternal().close();
091            } finally {
092                setClosedInternal(true);
093            }
094        }
095    }
096
097    /**
098     * Create or obtain a {@link PreparedStatement} from the pool.
099     * @param sql the sql string used to define the PreparedStatement
100     * @return a {@link PoolablePreparedStatement}
101     */
102    @Override
103    public PreparedStatement prepareStatement(String sql) throws SQLException {
104        if (null == _pstmtPool) {
105            throw new SQLException(
106                    "Statement pool is null - closed or invalid PoolingConnection.");
107        }
108        try {
109            return _pstmtPool.borrowObject(createKey(sql));
110        } catch(NoSuchElementException e) {
111            throw new SQLException("MaxOpenPreparedStatements limit reached", e);
112        } catch(RuntimeException e) {
113            throw e;
114        } catch(Exception e) {
115            throw new SQLException("Borrow prepareStatement from pool failed", e);
116        }
117    }
118
119    @Override
120    public PreparedStatement prepareStatement(String sql, int autoGeneratedKeys) throws SQLException {
121        if (null == _pstmtPool) {
122            throw new SQLException(
123                    "Statement pool is null - closed or invalid PoolingConnection.");
124        }
125        try {
126            return _pstmtPool.borrowObject(createKey(sql, autoGeneratedKeys));
127        }
128        catch (NoSuchElementException e) {
129            throw new SQLException("MaxOpenPreparedStatements limit reached", e);
130        }
131        catch (RuntimeException e) {
132            throw e;
133        }
134        catch (Exception e) {
135            throw new SQLException("Borrow prepareStatement from pool failed", e);
136        }
137    }
138
139    /**
140     * Create or obtain a {@link PreparedStatement} from the pool.
141     * @param sql the sql string used to define the PreparedStatement
142     * @param resultSetType result set type
143     * @param resultSetConcurrency result set concurrency
144     * @return a {@link PoolablePreparedStatement}
145     */
146    @Override
147    public PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency) throws SQLException {
148        if (null == _pstmtPool) {
149            throw new SQLException(
150                    "Statement pool is null - closed or invalid PoolingConnection.");
151        }
152        try {
153            return _pstmtPool.borrowObject(createKey(sql,resultSetType,resultSetConcurrency));
154        } catch(NoSuchElementException e) {
155            throw new SQLException("MaxOpenPreparedStatements limit reached", e);
156        } catch(RuntimeException e) {
157            throw e;
158        } catch(Exception e) {
159            throw new SQLException("Borrow prepareStatement from pool failed", e);
160        }
161    }
162
163    /**
164     * Create or obtain a {@link CallableStatement} from the pool.
165     * @param sql the sql string used to define the CallableStatement
166     * @return a {@link PoolableCallableStatement}
167     * @throws SQLException
168     */
169    @Override
170    public CallableStatement prepareCall(String sql) throws SQLException {
171        try {
172            return (CallableStatement) _pstmtPool.borrowObject(createKey(sql, StatementType.CALLABLE_STATEMENT));
173        } catch (NoSuchElementException e) {
174            throw new SQLException("MaxOpenCallableStatements limit reached", e);
175        } catch (RuntimeException e) {
176            throw e;
177        } catch (Exception e) {
178            throw new SQLException("Borrow callableStatement from pool failed", e);
179        }
180    }
181
182    /**
183     * Create or obtain a {@link CallableStatement} from the pool.
184     * @param sql the sql string used to define the CallableStatement
185     * @param resultSetType result set type
186     * @param resultSetConcurrency result set concurrency
187     * @return a {@link PoolableCallableStatement}
188     * @throws SQLException
189     */
190    @Override
191    public CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency) throws SQLException {
192        try {
193            return (CallableStatement) _pstmtPool.borrowObject(createKey(sql, resultSetType,
194                            resultSetConcurrency, StatementType.CALLABLE_STATEMENT));
195        } catch (NoSuchElementException e) {
196            throw new SQLException("MaxOpenCallableStatements limit reached", e);
197        } catch (RuntimeException e) {
198            throw e;
199        } catch (Exception e) {
200            throw new SQLException("Borrow callableStatement from pool failed", e);
201        }
202    }
203
204
205//    TODO: possible enhancement, cache these preparedStatements as well
206
207//    public PreparedStatement prepareStatement(String sql, int resultSetType,
208//                                              int resultSetConcurrency,
209//                                              int resultSetHoldability)
210//        throws SQLException {
211//        return super.prepareStatement(
212//            sql, resultSetType, resultSetConcurrency, resultSetHoldability);
213//    }
214//
215//    public PreparedStatement prepareStatement(String sql, int columnIndexes[])
216//        throws SQLException {
217//        return super.prepareStatement(sql, columnIndexes);
218//    }
219//
220//    public PreparedStatement prepareStatement(String sql, String columnNames[])
221//        throws SQLException {
222//        return super.prepareStatement(sql, columnNames);
223//    }
224
225    protected PStmtKey createKey(String sql, int autoGeneratedKeys) {
226        String catalog = null;
227        try {
228            catalog = getCatalog();
229        } catch (SQLException e) {
230            // Ignored
231        }
232        return new PStmtKey(normalizeSQL(sql), catalog, autoGeneratedKeys);
233    }
234
235    /**
236     * Create a PStmtKey for the given arguments.
237     * @param sql the sql string used to define the statement
238     * @param resultSetType result set type
239     * @param resultSetConcurrency result set concurrency
240     */
241    protected PStmtKey createKey(String sql, int resultSetType, int resultSetConcurrency) {
242        String catalog = null;
243        try {
244            catalog = getCatalog();
245        } catch (SQLException e) {
246            // Ignored
247        }
248        return new PStmtKey(normalizeSQL(sql), catalog, resultSetType, resultSetConcurrency);
249    }
250
251    /**
252     * Create a PStmtKey for the given arguments.
253     * @param sql the sql string used to define the statement
254     * @param resultSetType result set type
255     * @param resultSetConcurrency result set concurrency
256     * @param stmtType statement type
257     */
258    protected PStmtKey createKey(String sql, int resultSetType, int resultSetConcurrency, StatementType stmtType) {
259        String catalog = null;
260        try {
261            catalog = getCatalog();
262        } catch (SQLException e) {
263            // Ignored
264        }
265        return new PStmtKey(normalizeSQL(sql), catalog, resultSetType, resultSetConcurrency, stmtType);
266    }
267
268    /**
269     * Create a PStmtKey for the given arguments.
270     * @param sql the sql string used to define the statement
271     */
272    protected PStmtKey createKey(String sql) {
273        String catalog = null;
274        try {
275            catalog = getCatalog();
276        } catch (SQLException e) {
277            // Ignored
278        }
279        return new PStmtKey(normalizeSQL(sql), catalog);
280    }
281
282    /**
283     * Create a PStmtKey for the given arguments.
284     * @param sql the SQL string used to define the statement
285     * @param stmtType statement type
286     */
287    protected PStmtKey createKey(String sql, StatementType stmtType) {
288        String catalog = null;
289        try {
290            catalog = getCatalog();
291        } catch (SQLException e) {
292            // Ignored
293        }
294        return new PStmtKey(normalizeSQL(sql), catalog, stmtType, null);
295    }
296
297    /**
298     * Normalize the given SQL statement, producing a
299     * cannonical form that is semantically equivalent to the original.
300     */
301    protected String normalizeSQL(String sql) {
302        return sql.trim();
303    }
304
305    /**
306     * {@link KeyedPooledObjectFactory} method for creating
307     * {@link PoolablePreparedStatement}s or {@link PoolableCallableStatement}s.
308     * The <code>stmtType</code> field in the key determines whether
309     * a PoolablePreparedStatement or PoolableCallableStatement is created.
310     *
311     * @param key the key for the {@link PreparedStatement} to be created
312     * @see #createKey(String, int, int, StatementType)
313     */
314    @Override
315    public PooledObject<DelegatingPreparedStatement> makeObject(PStmtKey key)
316            throws Exception {
317        if(null == key) {
318            throw new IllegalArgumentException("Prepared statement key is null or invalid.");
319        }
320        if (null == key.getResultSetType() && null == key.getResultSetConcurrency() && null == key.getAutoGeneratedKeys()) {
321            if (key.getStmtType() == StatementType.PREPARED_STATEMENT ) {
322                @SuppressWarnings({"rawtypes", "unchecked"}) // Unable to find way to avoid this
323                PoolablePreparedStatement pps = new PoolablePreparedStatement(
324                        getDelegate().prepareStatement(key.getSql()), key, _pstmtPool, this);
325                return new DefaultPooledObject<DelegatingPreparedStatement>(pps);
326            }
327            return new DefaultPooledObject<DelegatingPreparedStatement>(
328                    new PoolableCallableStatement(getDelegate().prepareCall( key.getSql()), key, _pstmtPool, this));
329        } else if (null == key.getResultSetType() && null == key.getResultSetConcurrency()){
330            @SuppressWarnings({"rawtypes", "unchecked"}) // Unable to find way to avoid this
331            PoolablePreparedStatement pps = new PoolablePreparedStatement(
332                    getDelegate().prepareStatement(key.getSql(), key.getAutoGeneratedKeys().intValue()), key, _pstmtPool, this);
333            return new DefaultPooledObject<DelegatingPreparedStatement>(pps);
334        } else { // Both _resultSetType and _resultSetConcurrency are non-null here (both or neither are set by constructors)
335            if(key.getStmtType() == StatementType.PREPARED_STATEMENT) {
336                @SuppressWarnings({"rawtypes", "unchecked"}) // Unable to find way to avoid this
337                PoolablePreparedStatement pps = new PoolablePreparedStatement(getDelegate().prepareStatement(
338                        key.getSql(), key.getResultSetType().intValue(),key.getResultSetConcurrency().intValue()), key, _pstmtPool, this);
339                return new DefaultPooledObject<DelegatingPreparedStatement>(pps);
340            }
341            return new DefaultPooledObject<DelegatingPreparedStatement>(
342                    new PoolableCallableStatement( getDelegate().prepareCall(
343                            key.getSql(),key.getResultSetType().intValue(), key.getResultSetConcurrency().intValue()), key, _pstmtPool, this));
344        }
345    }
346
347    /**
348     * {@link KeyedPooledObjectFactory} method for destroying
349     * PoolablePreparedStatements and PoolableCallableStatements.
350     * Closes the underlying statement.
351     *
352     * @param key ignored
353     * @param p the wrapped pooled statement to be destroyed.
354     */
355    @Override
356    public void destroyObject(PStmtKey key,
357            PooledObject<DelegatingPreparedStatement> p)
358            throws Exception {
359        p.getObject().getInnermostDelegate().close();
360    }
361
362    /**
363     * {@link KeyedPooledObjectFactory} method for validating
364     * pooled statements. Currently always returns true.
365     *
366     * @param key ignored
367     * @param p ignored
368     * @return <tt>true</tt>
369     */
370    @Override
371    public boolean validateObject(PStmtKey key,
372            PooledObject<DelegatingPreparedStatement> p) {
373        return true;
374    }
375
376    /**
377     * {@link KeyedPooledObjectFactory} method for activating
378     * pooled statements.
379     *
380     * @param key ignored
381     * @param p wrapped pooled statement to be activated
382     */
383    @Override
384    public void activateObject(PStmtKey key,
385            PooledObject<DelegatingPreparedStatement> p) throws Exception {
386        p.getObject().activate();
387    }
388
389    /**
390     * {@link KeyedPooledObjectFactory} method for passivating
391     * {@link PreparedStatement}s or {@link CallableStatement}s.
392     * Invokes {@link PreparedStatement#clearParameters}.
393     *
394     * @param key ignored
395     * @param p a wrapped {@link PreparedStatement}
396     */
397    @Override
398    public void passivateObject(PStmtKey key,
399            PooledObject<DelegatingPreparedStatement> p) throws Exception {
400        DelegatingPreparedStatement dps = p.getObject();
401        dps.clearParameters();
402        dps.passivate();
403    }
404
405    @Override
406    public String toString() {
407        if (_pstmtPool != null ) {
408            return "PoolingConnection: " + _pstmtPool.toString();
409        }
410        return "PoolingConnection: null";
411    }
412
413    /**
414     * The possible statement types.
415     * @since 2.0
416     */
417    protected static enum StatementType {
418        CALLABLE_STATEMENT,
419        PREPARED_STATEMENT
420    }
421}