View Javadoc
1   /*
2    * Copyright (c) 2002-2022, City of Paris
3    * All rights reserved.
4    *
5    * Redistribution and use in source and binary forms, with or without
6    * modification, are permitted provided that the following conditions
7    * are met:
8    *
9    *  1. Redistributions of source code must retain the above copyright notice
10   *     and the following disclaimer.
11   *
12   *  2. Redistributions in binary form must reproduce the above copyright notice
13   *     and the following disclaimer in the documentation and/or other materials
14   *     provided with the distribution.
15   *
16   *  3. Neither the name of 'Mairie de Paris' nor 'Lutece' nor the names of its
17   *     contributors may be used to endorse or promote products derived from
18   *     this software without specific prior written permission.
19   *
20   * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
21   * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
22   * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
23   * ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDERS OR CONTRIBUTORS BE
24   * LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR
25   * CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF
26   * SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS
27   * INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN
28   * CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE)
29   * ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
30   * POSSIBILITY OF SUCH DAMAGE.
31   *
32   * License 1.0
33   */
34  package fr.paris.lutece.util.pool.service;
35  
36  import fr.paris.lutece.portal.service.util.AppException;
37  
38  import org.apache.commons.collections.CollectionUtils;
39  import org.apache.logging.log4j.Logger;
40  
41  import java.io.PrintWriter;
42  
43  import java.sql.Connection;
44  import java.sql.DriverManager;
45  import java.sql.SQLException;
46  import java.sql.Statement;
47  
48  import java.util.ArrayList;
49  import java.util.List;
50  
51  import javax.sql.DataSource;
52  
53  /**
54   * This class manages a database connection pool. <br>
55   * Connections are wrapped by a {@link LuteceConnection} to avoid explicit calls to {@link Connection#close()}. Connections are released to this pool when
56   * {@link Connection#close()} is called, and are not actually closed until {@link #release()} call.
57   * 
58   * @see LuteceConnection
59   */
60  public class ConnectionPool implements DataSource
61  {
62      private static final String DEFAULT_CHECK_VALID_CONNECTION_SQL = "SELECT 1";
63      private String _strUrl;
64      private String _strUser;
65      private String _strPassword;
66      private int _nMaxConns;
67      private int _nTimeOut;
68      private Logger _logger;
69      private int _nCheckedOut;
70      private List<Connection> _freeConnections = new ArrayList<>( );
71      private String _strCheckValidConnectionSql; // Added in v1.4
72      private PrintWriter _logWriter;
73  
74      /**
75       * Constructor.
76       *
77       * @param strName
78       *            Nom du pool
79       * @param strUrl
80       *            JDBC Data source URL
81       * @param strUser
82       *            SQL User
83       * @param strPassword
84       *            SQL Password
85       * @param nMaxConns
86       *            Max connections
87       * @param nInitConns
88       *            Initials connections
89       * @param nTimeOut
90       *            Timeout to get a connection
91       * @param logger
92       *            the Logger object
93       * @param strCheckValidConnectionSql
94       *            The SQL syntax used for check connexion validatation
95       */
96      public ConnectionPool( String strName, String strUrl, String strUser, String strPassword, int nMaxConns, int nInitConns, int nTimeOut, Logger logger,
97              String strCheckValidConnectionSql )
98      {
99          _strUrl = strUrl;
100         _strUser = strUser;
101         _strPassword = strPassword;
102         _nMaxConns = nMaxConns;
103         _nTimeOut = ( nTimeOut > 0 ) ? nTimeOut : 5;
104         _logger = logger;
105         initPool( nInitConns );
106         _logger.info( "New pool created : {}", strName );
107 
108         _strCheckValidConnectionSql = ( ( strCheckValidConnectionSql != null ) && !strCheckValidConnectionSql.equals( "" ) ) ? strCheckValidConnectionSql
109                 : DEFAULT_CHECK_VALID_CONNECTION_SQL;
110 
111         String lf = System.getProperty( "line.separator" );
112         _logger.debug( "{} url={}{} user= {}{} initconns= {}{} maxConns={}{} logintimeout={}", lf, strUrl, lf, _strUser, lf,
113                 nInitConns, lf, _nMaxConns, lf, _nTimeOut );
114         _logger.debug( ( ) -> getStats( ) );
115     }
116 
117     /**
118      * Initializes the pool
119      *
120      * @param initConns
121      *            Number of connections to create at the initialisation
122      */
123     private void initPool( int initConns )
124     {
125         for ( int i = 0; i < initConns; i++ )
126         {
127             try
128             {
129                 Connection pc = newConnection( );
130                 _freeConnections.add( pc );
131             }
132             catch( SQLException e )
133             {
134                 throw new AppException( "SQL Error executing command : " + e.toString( ), e );
135             }
136         }
137     }
138 
139     /**
140      * Returns a connection from the pool.
141      *
142      * @return An open connection
143      * @throws SQLException
144      *             The SQL exception
145      */
146     @Override
147     public Connection getConnection( ) throws SQLException
148     {
149         _logger.debug( "Request for connection received" );
150 
151         try
152         {
153             return getConnection( _nTimeOut * 1000L );
154         }
155         catch( SQLException e )
156         {
157             _logger.error( "Exception getting connection", e );
158             throw e;
159         }
160     }
161 
162     /**
163      * Returns a connection from the pool.
164      *
165      * @param timeout
166      *            The maximum time to wait for a connection
167      * @return An open connection
168      * @throws SQLException
169      *             The SQL exception
170      */
171     private synchronized Connection getConnection( long timeout ) throws SQLException
172     {
173         // Get a pooled Connection from the cache or a new one.
174         // Wait if all are checked out and the max limit has
175         // been reached.
176         long startTime = System.currentTimeMillis( );
177         long remaining = timeout;
178         Connection conn = null;
179 
180         while ( ( conn = getPooledConnection( ) ) == null )
181         {
182             try
183             {
184                 _logger.debug( "Waiting for connection. Timeout= {}", remaining );
185 
186                 wait( remaining );
187             }
188             catch( InterruptedException e )
189             {
190                 _logger.debug( "A connection has been released by another thread.", e );
191             }
192 
193             remaining = timeout - ( System.currentTimeMillis( ) - startTime );
194 
195             if ( remaining <= 0 )
196             {
197                 // Timeout has expired
198                 _logger.debug( "Time-out while waiting for connection" );
199                 throw new SQLException( "getConnection() timed-out" );
200             }
201         }
202 
203         // Check if the Connection is still OK
204         if ( !isConnectionOK( conn ) )
205         {
206             // It was bad. Try again with the remaining timeout
207             _logger.error( "Removed selected bad connection from pool" );
208 
209             return getConnection( remaining );
210         }
211 
212         _nCheckedOut++;
213         _logger.debug( "Delivered connection from pool" );
214         _logger.debug( getStats( ) );
215 
216         return conn;
217     }
218 
219     /**
220      * Checks a connection to see if it's still alive
221      *
222      * @param conn
223      *            The connection to check
224      * @return true if the connection is OK, otherwise false.
225      */
226     private boolean isConnectionOK( Connection conn )
227     {
228         Statement testStmt = null;
229 
230         try
231         {
232             if ( !conn.isClosed( ) )
233             {
234                 // Try to createStatement to see if it's really alive
235                 testStmt = conn.createStatement( );
236                 testStmt.executeQuery( _strCheckValidConnectionSql );
237                 testStmt.close( );
238             }
239             else
240             {
241                 return false;
242             }
243         }
244         catch( SQLException e )
245         {
246             if ( testStmt != null )
247             {
248                 try
249                 {
250                     testStmt.close( );
251                 }
252                 catch( SQLException se )
253                 {
254                     throw new AppException( "ConnectionService : SQL Error executing command : " + se.toString( ) );
255                 }
256             }
257 
258             _logger.error( "Pooled Connection was not okay", e );
259 
260             return false;
261         }
262 
263         return true;
264     }
265 
266     /**
267      * Gets a connection from the pool
268      *
269      * @return An opened connection
270      * @throws SQLException
271      *             The exception
272      */
273     private Connection getPooledConnection( ) throws SQLException
274     {
275         Connection conn = null;
276 
277         if ( CollectionUtils.isNotEmpty( _freeConnections ) )
278         {
279             // Pick the first Connection in the Vector
280             // to get round-robin usage
281             conn = _freeConnections.get( 0 );
282             _freeConnections.remove( 0 );
283         }
284         else
285             if ( ( _nMaxConns == 0 ) || ( _nCheckedOut < _nMaxConns ) )
286             {
287                 conn = newConnection( );
288             }
289 
290         return conn;
291     }
292 
293     /**
294      * Creates a new connection. <br>
295      * The connection is wrapped by {@link LuteceConnection}
296      *
297      * @return The new created connection
298      * @throws SQLException
299      *             The SQL exception
300      */
301     private Connection newConnection( ) throws SQLException
302     {
303         Connection conn;
304 
305         if ( _strUser == null )
306         {
307             conn = DriverManager.getConnection( _strUrl );
308         }
309         else
310         {
311             conn = DriverManager.getConnection( _strUrl, _strUser, _strPassword );
312         }
313 
314         // wrap connection so this connection pool is used when conn.close() is called
315         conn = LuteceConnectionFactory.newInstance( this, conn );
316         _logger.info( "New connection created. Connections count is : " + ( getConnectionCount( ) + 1 ) );
317         return conn;
318     }
319 
320     /**
321      * Returns a connection to pool.
322      *
323      * @param conn
324      *            The released connection to return to pool
325      */
326     public synchronized void freeConnection( Connection conn )
327     {
328         // Put the connection at the end of the Vector
329         _freeConnections.add( conn );
330         _nCheckedOut--;
331         notifyAll( );
332         _logger.debug( "Returned connection to pool" );
333         _logger.debug( ( ) -> getStats( ) );
334     }
335 
336     /**
337      * Releases the pool by closing all its connections.
338      */
339     public synchronized void release( )
340     {
341         for ( Connection connection : _freeConnections )
342         {
343             try
344             {
345                 if ( connection instanceof LuteceConnection )
346                 {
347                     ( (LuteceConnection) connection ).closeConnection( );
348                 }
349                 else
350                 {
351                     connection.close( );
352                 }
353 
354                 _logger.debug( "Closed connection" );
355             }
356             catch( SQLException e )
357             {
358                 _logger.error( "Couldn't close connection", e );
359             }
360         }
361 
362         _freeConnections.clear( );
363     }
364 
365     /**
366      * Returns stats on pool's connections
367      *
368      * @return Stats as String.
369      */
370     private String getStats( )
371     {
372         return "Total connections: " + getConnectionCount( ) + " Available: " + getFreeConnectionCount( ) + " Checked-out: " + getBusyConnectionCount( );
373     }
374 
375     /**
376      * Returns the number of connections opened by the pool (available or busy)
377      * 
378      * @return A connection count
379      */
380     public int getConnectionCount( )
381     {
382         return getFreeConnectionCount( ) + getBusyConnectionCount( );
383     }
384 
385     /**
386      * Returns the number of free connections of the pool (available or busy)
387      * 
388      * @return A connection count
389      */
390     public int getFreeConnectionCount( )
391     {
392         return _freeConnections.size( );
393     }
394 
395     /**
396      * Returns the number of busy connections of the pool (available or busy)
397      * 
398      * @return A connection count
399      */
400     public int getBusyConnectionCount( )
401     {
402         return _nCheckedOut;
403     }
404 
405     /**
406      * Returns the maximum number of connections of the pool
407      * 
408      * @return A connection count
409      */
410     public int getMaxConnectionCount( )
411     {
412         return _nMaxConns;
413     }
414 
415     /**
416      * Returns the connection of the pool.
417      *
418      * @param username
419      *            the username
420      * @param password
421      *            the password
422      * @return A connection
423      * @throws SQLException
424      *             the sQL exception
425      */
426     @Override
427     public Connection getConnection( String username, String password ) throws SQLException
428     {
429         return getConnection( );
430     }
431 
432     /**
433      * Get the log.
434      *
435      * @return A log writer
436      * @throws SQLException
437      *             the sQL exception
438      */
439     @Override
440     public PrintWriter getLogWriter( ) throws SQLException
441     {
442         _logger.debug( "ConnectionPool : DataSource getLogWriter called" );
443 
444         return _logWriter;
445     }
446 
447     /**
448      * Set the log.
449      *
450      * @param out
451      *            the new log writer
452      * @throws SQLException
453      *             the sQL exception
454      */
455     @Override
456     public void setLogWriter( PrintWriter out ) throws SQLException
457     {
458         _logger.debug( "ConnectionPool : DataSource setLogWriter called" );
459         _logWriter = out;
460     }
461 
462     /**
463      * Set Login Timeout.
464      *
465      * @param seconds
466      *            the new login timeout
467      * @throws SQLException
468      *             the sQL exception
469      */
470     @Override
471     public void setLoginTimeout( int seconds ) throws SQLException
472     {
473         // Do nothing
474     }
475 
476     /**
477      * Get loging timeout.
478      *
479      * @return A time out
480      * @throws SQLException
481      *             the sQL exception
482      */
483     @Override
484     public int getLoginTimeout( ) throws SQLException
485     {
486         return _nTimeOut;
487     }
488 
489     /**
490      * Get the unwrap.
491      *
492      * @param <T>
493      *            the generic type
494      * @param iface
495      *            the iface
496      * @return null
497      * @throws SQLException
498      *             the sQL exception
499      */
500     @Override
501     public <T> T unwrap( Class<T> iface ) throws SQLException
502     {
503         return null;
504     }
505 
506     /**
507      * Get the wrapper.
508      *
509      * @param iface
510      *            the iface
511      * @return false
512      * @throws SQLException
513      *             the sQL exception
514      */
515     @Override
516     public boolean isWrapperFor( Class<?> iface ) throws SQLException
517     {
518         return false;
519     }
520 
521     /**
522      * Implementation of JDBC 4.1's getParentLogger method (Java 7)
523      *
524      * @return the parent logger
525      */
526     public java.util.logging.Logger getParentLogger( )
527     {
528         return java.util.logging.Logger.getLogger( java.util.logging.Logger.GLOBAL_LOGGER_NAME );
529     }
530 }