com.hfg.sql.jdbc.JDBCConnectionPool Maven / Gradle / Ivy
Go to download
Show more of this group Show more artifacts with this name
Show all versions of com_hfg Show documentation
Show all versions of com_hfg Show documentation
com.hfg xml, html, svg, and bioinformatics utility library
package com.hfg.sql.jdbc;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashSet;
import java.util.List;
import java.util.Set;
import java.util.logging.Level;
import java.util.logging.Logger;
import com.hfg.datetime.DateUtil;
import com.hfg.security.LoginCredentials;
import com.hfg.sql.SQLUtil;
import com.hfg.util.collection.CollectionUtil;
import com.hfg.util.StringBuilderPlus;
import com.hfg.util.StringUtil;
//------------------------------------------------------------------------------
/**
A JDBC-compatible connection pool.
@author J. Alex Taylor, hairyfatguy.com
*/
//------------------------------------------------------------------------------
// com.hfg XML/HTML Coding Library
//
// This library is free software; you can redistribute it and/or
// modify it under the terms of the GNU Lesser General Public
// License as published by the Free Software Foundation; either
// version 2.1 of the License, or (at your option) any later version.
//
// This library is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
// Lesser General Public License for more details.
//
// You should have received a copy of the GNU Lesser General Public
// License along with this library; if not, write to the Free Software
// Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
//
// J. Alex Taylor, President, Founder, CEO, COO, CFO, OOPS hairyfatguy.com
// [email protected]
//------------------------------------------------------------------------------
public abstract class JDBCConnectionPool extends JDBCDataSource
{
private List mPooledConnections;
private Thread mIdleConnReaperThread;
private int mNumConnectionRequests = 0;
private boolean mIsActive = true;
private Set mBorrowerClassesToIgnore;
private Integer mDatabaseMajorVersion;
private Integer mDatabaseMinorVersion;
private String mDatabaseVersionString;
private final static Logger LOGGER = Logger.getLogger(JDBCConnectionPool.class.getName());
static
{
LOGGER.setLevel(Level.INFO);
}
//###########################################################################
// CONSTRUCTORS
//###########################################################################
//---------------------------------------------------------------------------
public JDBCConnectionPool(JDBCServer inServer, String inDatabaseName)
{
super(inServer, inDatabaseName);
}
//---------------------------------------------------------------------------
public JDBCConnectionPool(JDBCServer inServer, String inDatabaseName, LoginCredentials inCredentials)
{
super(inServer, inDatabaseName);
setCredentials(inCredentials);
}
//###########################################################################
// PUBLIC METHODS
//###########################################################################
//---------------------------------------------------------------------------
public synchronized JDBCConnection getConnection()
throws SQLException, NoAvailableConnectionException
{
JDBCConnection conn = null;
long startTime = System.currentTimeMillis();
StackTraceElement borrower = getBorrower();
while (null == conn)
{
try
{
conn = getPoolConnection(borrower);
}
catch (NoAvailableConnectionException e)
{
if (getSettings().getAvailabilityTimeoutSec() != null
&& System.currentTimeMillis() - startTime > (getSettings().getAvailabilityTimeoutSec() * 1000))
{
throw e;
}
else
{
try
{
Thread.sleep(250);
}
catch (InterruptedException e2)
{
// Ignore
}
}
}
}
mNumConnectionRequests++;
return conn;
}
//---------------------------------------------------------------------------
public synchronized void destroy()
throws SQLException
{
mIsActive = false;
if (CollectionUtil.hasValues(mPooledConnections))
{
for (PooledJDBCConnection conn : mPooledConnections)
{
conn.destroy();
}
mPooledConnections.clear();
}
mIdleConnReaperThread.interrupt();
}
//---------------------------------------------------------------------------
/**
Specifies a class to be ignored when tracking connection lending. When examining
the stacktrace to see who is requesting a connection, any elements from this class
will be skipped and a subsequent element will be used to identify the borrower.
* @param inValue a class to be ignored when tracking connection lending
* @return this connection pool object to enable method chaining
*/
public JDBCConnectionPool addIgnorableBorrowerClass(Class inValue)
{
if (inValue != null)
{
if (null == mBorrowerClassesToIgnore)
{
mBorrowerClassesToIgnore = new HashSet<>(5);
}
mBorrowerClassesToIgnore.add(inValue.getName());
}
return this;
}
//---------------------------------------------------------------------------
public int getDatabaseMajorVersion()
throws SQLException
{
if (null == mDatabaseMajorVersion)
{
Connection conn = null;
try
{
conn = getConnection();
DatabaseMetaData meta = conn.getMetaData();
// Oracle (and some other vendors) do not support
// some the following methods; therefore, we need
// to use try-catch block.
try
{
mDatabaseMajorVersion = meta.getDatabaseMajorVersion();
}
catch (Exception e)
{
System.err.println("Database major version: unsupported feature");
}
}
finally
{
SQLUtil.close(conn);
}
}
return mDatabaseMajorVersion;
}
//---------------------------------------------------------------------------
public int getDatabaseMinorVersion()
throws SQLException
{
if (null == mDatabaseMinorVersion)
{
Connection conn = null;
try
{
conn = getConnection();
DatabaseMetaData meta = conn.getMetaData();
// Oracle (and some other vendors) do not support
// some the following methods; therefore, we need
// to use try-catch block.
try
{
mDatabaseMinorVersion = meta.getDatabaseMinorVersion();
}
catch (Exception e)
{
System.err.println("Database minor version: unsupported feature");
}
}
finally
{
SQLUtil.close(conn);
}
}
return mDatabaseMajorVersion;
}
//---------------------------------------------------------------------------
public String getDatabaseVersionString()
{
if (null == mDatabaseVersionString)
{
Connection conn = null;
try
{
conn = getConnection();
DatabaseMetaData meta = conn.getMetaData();
mDatabaseVersionString = meta.getDatabaseProductVersion();
}
catch (SQLException e)
{
throw new JDBCException("Error retrieving database version string!", e);
}
finally
{
SQLUtil.close(conn);
}
}
return mDatabaseVersionString;
}
//---------------------------------------------------------------------------
public String getStatusReport()
{
StringBuilderPlus buffer = new StringBuilderPlus()
.appendln("Connection Pool Status Report " + DateUtil.getYYYY_MM_DD_HH_mm_ss(new Date()))
.appendln()
.appendln(String.format(" %s %s", getServer().getRDBMS().name(), getDatabaseVersionString()))
.appendln(" Server: " + getServer().getConnectString(getDatabaseName()))
.appendln(" Num Connection Requests: " + mNumConnectionRequests)
.appendln(String.format(" Connections (Min/Max/Avail.): %d / %d / %d",
getSettings().getMinConnections(), getSettings().getMaxConnections(), getNumAvailableConnections()));
StringBuilderPlus activeConnBuffer = new StringBuilderPlus();
if (CollectionUtil.hasValues(mPooledConnections))
{
for (int i = 0; i < mPooledConnections.size(); i++)
{
PooledJDBCConnection poolConn = mPooledConnections.get(i);
if (! poolConn.isAvailable())
{
// This connection is in use. Report who checked it out and when.
StackTraceElement caller = poolConn.getBorrower();
Long checkoutTime = poolConn.getCheckoutTime();
// Avoid possible NPEs due to the connection being returned while we're in the process of gathering info
if (caller != null
&& checkoutTime != null)
{
activeConnBuffer.appendln(String.format(" %2d. %s [%s]", (i + 1), caller, DateUtil.generateElapsedTimeString(checkoutTime)));
}
}
}
}
if (activeConnBuffer.length() > 0)
{
buffer.appendln(" Active Connections:")
.append(activeConnBuffer);
}
else
{
buffer.appendln(" No Active Connections.");
}
return buffer.toString();
}
//---------------------------------------------------------------------------
public static Logger getLogger()
{
return LOGGER;
}
//###########################################################################
// PROTECTED METHODS
//###########################################################################
//---------------------------------------------------------------------------
protected JDBCConnection getPoolConnection(StackTraceElement inBorrower)
throws SQLException, NoAvailableConnectionException
{
PooledJDBCConnection conn = null;
if (null == mPooledConnections)
{
initializePool();
}
for (int i = 0; i < mPooledConnections.size(); i++)
{
PooledJDBCConnection poolConn = mPooledConnections.get(i);
if (poolConn.isAvailable())
{
try
{
conn = poolConn.checkout(inBorrower);
if (null == conn)
{ // The connection must not be valid anymore
poolConn.destroy();
mPooledConnections.remove(i--);
}
else
{
// Connection is good to go
break;
}
}
catch (Exception e)
{
e.printStackTrace();
poolConn.destroy();
mPooledConnections.remove(i--);
}
}
}
if (null == conn)
{
// No available connections were found in the pool. Can we create a new one?
if (mPooledConnections.size() < getSettings().getMaxConnections())
{
conn = new PooledJDBCConnection(createConnection());
LOGGER.fine("Creating new connection: " + conn.name());
conn = conn.checkout(inBorrower);
if (conn != null)
{
mPooledConnections.add(conn);
}
}
else
{
throw new NoAvailableConnectionException();
}
}
return conn;
}
//---------------------------------------------------------------------------
protected T initSettings()
{
return (T) new JDBCConnectionPoolSettings();
}
//---------------------------------------------------------------------------
protected abstract JDBCConnection createConnection();
//###########################################################################
// PRIVATE METHODS
//###########################################################################
//---------------------------------------------------------------------------
private void initializePool()
{
mPooledConnections = new ArrayList<>(getSettings().getMaxConnections());
for (int i = 0; i < getSettings().getMinConnections(); i++)
{
JDBCConnection conn = createConnection();
mPooledConnections.add(new PooledJDBCConnection(conn));
}
mIdleConnReaperThread = new Thread(new IdleConnReaper());
mIdleConnReaperThread.setDaemon(true);
mIdleConnReaperThread.setPriority(Thread.MIN_PRIORITY);
mIdleConnReaperThread.setName(generateIdleRepearThreadName());
mIdleConnReaperThread.start();
}
//---------------------------------------------------------------------------
private String generateIdleRepearThreadName()
{
StringBuilderPlus name = new StringBuilderPlus().setDelimiter(" ");
if (StringUtil.isSet(name()))
{
name.append(name());
}
if (StringUtil.isSet(getServer().name()))
{
name.delimitedAppend(getServer().name());
}
if (StringUtil.isSet(getDatabaseName()))
{
name.delimitedAppend(getDatabaseName());
}
name.delimitedAppend(" Idle Conn Reaper");
return name.toString();
}
//---------------------------------------------------------------------------
private StackTraceElement getBorrower()
{
StackTraceElement borrower = null;
StackTraceElement[] stackTrace = Thread.currentThread().getStackTrace();
for (int i = 3; i < stackTrace.length; i++)
{
StackTraceElement stackTraceElement = stackTrace[i];
if (! CollectionUtil.hasValues(mBorrowerClassesToIgnore)
|| ! mBorrowerClassesToIgnore.contains(stackTraceElement.getClassName()))
{
borrower = stackTraceElement;
break;
}
}
return borrower;
}
//---------------------------------------------------------------------------
private synchronized void removeConnectionFromPool(PooledJDBCConnection inConn)
{
mPooledConnections.remove(inConn);
}
//---------------------------------------------------------------------------
private synchronized void removeIdledConnections()
throws SQLException
{
if (poolContainsMoreThanTheMinNumOfConnections())
{
for (int i = 0; i < getSettings().getMinConnections(); i++)
{
PooledJDBCConnection poolConn = mPooledConnections.get(i);
if (poolConn.isAvailable()
&& poolConn.getIdleTimeSec() > getSettings().getMaxIdleTimeSec())
{
poolConn.destroy();
mPooledConnections.remove(i--);
if (! poolContainsMoreThanTheMinNumOfConnections())
{
break;
}
}
}
}
}
//---------------------------------------------------------------------------
private boolean poolContainsMoreThanTheMinNumOfConnections()
{
return (mPooledConnections.size() > 0
&& (null == getSettings().getMinConnections()
|| mPooledConnections.size() > getSettings().getMinConnections()));
}
//---------------------------------------------------------------------------
private int getNumAvailableConnections()
{
int numAvailableConns = 0;
if (mPooledConnections != null)
{
for (int i = 0; i < mPooledConnections.size(); i++)
{
PooledJDBCConnection poolConn = mPooledConnections.get(i);
if (poolConn.isAvailable())
{
numAvailableConns++;
}
}
}
return numAvailableConns;
}
//###########################################################################
// INNER CLASS
//###########################################################################
private class PooledJDBCConnection extends JDBCConnection
{
private boolean mIsAvailable;
private Long mCheckoutTime;
private StackTraceElement mBorrower;
private Long mIdleStartTime;
//------------------------------------------------------------------------
public PooledJDBCConnection(JDBCConnection inConn)
{
super(inConn);
initPooledConnection();
}
//------------------------------------------------------------------------
private void initPooledConnection()
{
mCheckoutTime = null;
mBorrower = null;
mIdleStartTime = System.currentTimeMillis();
mIsAvailable = true;
}
//------------------------------------------------------------------------
public synchronized boolean isAvailable()
{
return mIsAvailable;
}
//------------------------------------------------------------------------
public synchronized PooledJDBCConnection checkout(StackTraceElement inBorrower)
throws SQLException
{
mIsAvailable = false;
mCheckoutTime = System.currentTimeMillis();
mBorrower = inBorrower;
mIdleStartTime = null;
if (! getAutoCommit())
{
setAutoCommit(true);
}
PooledJDBCConnection conn = this;
if (getServer().getRDBMS().getConnTestQuery() != null)
{
// Bypassing SQLUtil.execute() to avoid logging of the test SQL every time a connection is checked out.
try
{
Statement stmt = null;
try
{
stmt = createStatement();
stmt.execute(getServer().getRDBMS().getConnTestQuery().toSQL());
}
finally
{
SQLUtil.close(stmt);
}
}
catch (JDBCException e)
{
conn = null;
}
}
if (conn != null)
{
LOGGER.fine(name() + " checked out to " + inBorrower);
}
return conn;
}
//------------------------------------------------------------------------
public Long getCheckoutTime()
{
return mCheckoutTime;
}
//------------------------------------------------------------------------
public StackTraceElement getBorrower()
{
return mBorrower;
}
//------------------------------------------------------------------------
private int getIdleTimeSec()
{
int idleTimeSec = 0;
if (mIdleStartTime != null)
{
idleTimeSec = (int) (System.currentTimeMillis() - mIdleStartTime)/1000;
}
return idleTimeSec;
}
//------------------------------------------------------------------------
// Don't actually close the underlying connection. It goes back into the pool.
public void close()
throws SQLException
{
LOGGER.fine(name() + " returned from " + mBorrower);
if (isClosed())
{
removeConnectionFromPool(this);
}
else
{
clean();
initPooledConnection();
}
}
//------------------------------------------------------------------------
public void destroy()
throws SQLException
{
if (! isClosed())
{
unwrap(Connection.class).close();
}
}
//------------------------------------------------------------------------
private void clean()
throws SQLException
{
if (! getAutoCommit())
{
boolean returnedInUncommittedState = false;
try
{
setAutoCommit(true);
}
catch (SQLException e)
{
returnedInUncommittedState = true;
// Try rolling back the transaction.
try
{
rollback();
setAutoCommit(true);
}
catch (SQLException e2)
{
destroy();
removeConnectionFromPool(this);
}
}
if (returnedInUncommittedState)
{
throw new JDBCException("Connection returned in an uncommitted state!");
}
}
}
//------------------------------------------------------------------------
@Override
public String getCurrentUser()
throws SQLException
{
return ((JDBCConnection) getInnerConnection()).getCurrentUser();
}
}
//###########################################################################
// INNER CLASS
//###########################################################################
private class IdleConnReaper implements Runnable
{
private int mExecutionFreqSec = 60;
//------------------------------------------------------------------------
public void run()
{
while (mIsActive)
{
try
{
Thread.sleep(mExecutionFreqSec * 1000);
}
catch (InterruptedException e2)
{
// Ignore
}
try
{
removeIdledConnections();
}
catch (Exception e)
{
// Ignore
e.printStackTrace();
}
}
}
}
}