Made some changes to reduce thread blocking, and now my connectionpool seems to be faster than bonecp’s.
I made 2 speedtests:
- first one is a single thread that runs 10.000 SELECT queries,
- the second test runs 10.000 SELECT queries in 5 threads (so 5*10.000 queries in total).
I’m running the server at localhost, and got a dual-core. The results were (in ms):
- bonecp: 5276, 9520
- mine: 5119, 8310
Here is the updated code + my speedtests:
MyConnection.java
[java]package _04_sql_connections;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.SQLWarning;
import java.sql.Savepoint;
import java.util.Iterator;
import java.util.concurrent.ConcurrentLinkedQueue;
import org.apache.commons.beanutils.RowSetDynaClass;
public class MyConnection
{
// constants >>
private static final String databaseDataTable = “_data”; // this table will be used like a properties file
private final ConcurrentLinkedQueue<Thread> openResultSetQueue = new ConcurrentLinkedQueue<Thread>();
private static final SQLException connectionIsntOpenError = new SQLException( “The connection isn’t open.” );
private static final SQLException databaseVersionLower = new SQLException( “The database is outdated.” );
private static final SQLException databaseVersionHigher = new SQLException( “The database has a higher version than this application.” );
// constants <<
// constructor parameters >>
private final String databaseVersion;
private final String driver;
private final String url;
private final String database;
private final String username;
private final String password;
// constructor parameters <<
// variables >>
private Connection connection;
private ResultSet currentResultSet;
private Thread currentResultSetThread;
// variables <<
public MyConnection( String databaseVersion, String driver, String url, String database, String username, String password )
{
this.databaseVersion = databaseVersion;
this.driver = driver;
this.url = url;
this.database = database;
this.username = username;
this.password = password;
}
public void open() throws ClassNotFoundException, InstantiationException, IllegalAccessException, SQLException
{
// open connection >>
Class.forName( “com.”+driver+".jdbc.Driver" );
connection = DriverManager.getConnection( “jdbc:”+driver+"://"+url+"/"+database, username, password );
// open connection <<
// prepare all statements >>
prepareAllStatements();
// prepare all statements <<
// check if database version is correct >>
String version = getDatabaseDataValue( “version” );
if( version == null )
{
setDatabaseDataValue( “version”, databaseVersion );
}
else if( !version.equals(databaseVersion) )
{
int comparedValue = version.compareTo( databaseVersion );
if( comparedValue < 0 )
throw databaseVersionLower;
else if( comparedValue > 0 )
throw databaseVersionHigher;
}
// check if database version is correct <<
}
// all prepared statements >>
//<editor-fold defaultstate=“collapsed” desc=“all prepared statements”>
/** /
private PreparedStatement statementCreateDatabaseDataTable;
/* key /
private PreparedStatement statementGetDatabaseDataValue;
/* key, value, key /
private PreparedStatement statementSetDatabaseDataValue_Insert;
/* value, key /
private PreparedStatement statementSetDatabaseDataValue_Update;
/* table /
private PreparedStatement statementDoesTableExist;
private void prepareAllStatements() throws SQLException, IllegalAccessException
{
if( statementCreateDatabaseDataTable == null )
{
String query = " CREATE TABLE “+databaseDataTable +
" (” +
" name VARCHAR( 255 ) NOT NULL PRIMARY KEY," +
" value VARCHAR( 255 ) NOT NULL" +
" );";
statementCreateDatabaseDataTable = prepareStatement( query );
}
if( statementGetDatabaseDataValue == null )
{
String query = " SELECT * " +
" FROM “+databaseDataTable +
" WHERE name = ?;”;
statementGetDatabaseDataValue = prepareStatement( query );
}
if( statementSetDatabaseDataValue_Insert == null )
{
String query = " INSERT INTO “+databaseDataTable+”( name, value )" +
" SELECT ?, ?" +
" FROM DUAL" +
" WHERE NOT EXISTS (SELECT * FROM “+databaseDataTable+” WHERE name = ?);";
statementSetDatabaseDataValue_Insert = prepareStatement( query );
}
if( statementSetDatabaseDataValue_Update == null )
{
String query = " UPDATE “+databaseDataTable +
" SET value = ?” +
" WHERE name = ?;";
statementSetDatabaseDataValue_Update = prepareStatement( query );
}
if( statementDoesTableExist == null )
{
String query = " SELECT COUNT() AS ‘count’" +
" FROM INFORMATION_SCHEMA.TABLES" +
" WHERE TABLE_SCHEMA = ‘"+database+"’"+
" AND TABLE_NAME = ?;";
statementDoesTableExist = prepareStatement( query );
}
}
//</editor-fold>
// all prepared statements <<
// queries >>
/** @return An Iterator for BasicDynaBean rows /
public Iterator openResultSetAndClose( String statement ) throws SQLException, IllegalAccessException
{
if( connection == null )
throw connectionIsntOpenError;
return openResultSetAndClose( connection.prepareStatement(statement) );
}
/* @return An Iterator for BasicDynaBean rows /
public Iterator openResultSetAndClose( PreparedStatement statement ) throws SQLException, IllegalAccessException
{
Iterator iterator = new RowSetDynaClass( openResultSet(“SELECT * FROM data”) ).getRows().iterator(); // needs apache beanutils, if you don’t use this method you can also just remove it
closeResultSet();
return iterator;
}
public ResultSet openResultSet( String statement ) throws SQLException, IllegalAccessException
{
if( connection == null )
throw connectionIsntOpenError;
return openResultSet( connection.prepareStatement(statement) );
}
public ResultSet openResultSet( PreparedStatement statement ) throws SQLException, IllegalAccessException
{
// wait until resultset is closed && other threads are done >>
Thread thread = Thread.currentThread();
synchronized( openResultSetQueue )
{
if( openResultSetQueue.contains(thread) || (thread == currentResultSetThread) )
throw new IllegalAccessException(“This thread is already opening a ResultSet, you must close the ResultSet before opening a new one.”);
openResultSetQueue.add( thread );
}
while( (currentResultSet != null) || (openResultSetQueue.peek() != thread) )
{
synchronized( this )
{
try
{
wait( 1000 );
}
catch( Exception e )
{
}
}
}
// wait until resultset is closed && other threads are done <<
// open result set >>
synchronized( openResultSetQueue )
{
currentResultSet = statement.executeQuery();
currentResultSetThread = thread;
openResultSetQueue.poll();
}
return currentResultSet;
// open result set <<
}
public void closeResultSet()
{
// close result set >>
if( currentResultSet != null )
{
synchronized( currentResultSet )
{
if( currentResultSet != null )
{
try
{
if( !currentResultSet.isClosed() )
currentResultSet.close();
}
catch( SQLException e )
{
}
currentResultSetThread = null;
currentResultSet = null;
}
}
}
// close result set <<
// notify next thread >>
synchronized( this )
{
notifyAll();
}
// notify next thread <<
}
public int execute( String statement ) throws SQLException
{
if( connection == null )
throw connectionIsntOpenError;
return execute( connection.prepareStatement(statement) );
}
public int execute( PreparedStatement statement ) throws SQLException
{
return statement.executeUpdate();
}
/* calls openResultSet() /
public String getDatabaseDataValue( String key ) throws SQLException, IllegalAccessException
{
synchronized( statementCreateDatabaseDataTable )
{
if( !doesTableExist(databaseDataTable) )
return null;
}
String value = null;
synchronized( statementGetDatabaseDataValue )
{
statementGetDatabaseDataValue.setString( 1, key );
ResultSet result = openResultSet( statementGetDatabaseDataValue );
try
{
while( result.next() )
{
value = result.getString( “value” );
break;
}
}
catch( SQLException e )
{
closeResultSet();
throw e;
}
closeResultSet();
}
return value;
}
/* calls openResultSet() /
public void setDatabaseDataValue( String key, String value ) throws SQLException, IllegalAccessException
{
synchronized( statementCreateDatabaseDataTable )
{
if( !doesTableExist(databaseDataTable) )
execute( statementCreateDatabaseDataTable );
}
synchronized( statementSetDatabaseDataValue_Insert )
{
statementSetDatabaseDataValue_Insert.setString( 1, key );
statementSetDatabaseDataValue_Insert.setString( 2, value );
statementSetDatabaseDataValue_Insert.setString( 3, key );
execute( statementSetDatabaseDataValue_Insert );
}
synchronized( statementSetDatabaseDataValue_Update )
{
statementSetDatabaseDataValue_Update.setString( 1, value );
statementSetDatabaseDataValue_Update.setString( 2, key );
execute( statementSetDatabaseDataValue_Update );
}
}
/* calls openResultSet() */
public boolean doesTableExist( String table ) throws SQLException, IllegalAccessException
{
boolean value = false;
synchronized( statementDoesTableExist )
{
statementDoesTableExist.setString( 1, table );
ResultSet result = openResultSet( statementDoesTableExist );
try
{
while( result.next() )
{
value = (result.getInt(“count”) >= 1);
break;
}
}
catch( SQLException e )
{
closeResultSet();
throw e;
}
closeResultSet();
}
return value;
}
// queries <<
// default connection methods >>
public CallableStatement prepareCall( String statement ) throws SQLException
{
if( connection == null )
throw connectionIsntOpenError;
return connection.prepareCall( statement );
}
public PreparedStatement prepareStatement( String statement ) throws SQLException
{
if( connection == null )
throw connectionIsntOpenError;
return connection.prepareStatement( statement );
}
public void commit() throws SQLException
{
if( connection == null )
throw connectionIsntOpenError;
connection.commit();
}
public void rollback() throws SQLException
{
if( connection == null )
throw connectionIsntOpenError;
connection.rollback();
}
public void rollback( Savepoint savepoint ) throws SQLException
{
if( connection == null )
throw connectionIsntOpenError;
connection.rollback( savepoint );
}
public Savepoint setSavepoint() throws SQLException
{
if( connection == null )
throw connectionIsntOpenError;
return connection.setSavepoint();
}
public Savepoint setSavepoint( String name ) throws SQLException
{
if( connection == null )
throw connectionIsntOpenError;
return connection.setSavepoint( name );
}
public void releaseSavepoint( Savepoint savepoint ) throws SQLException
{
if( connection == null )
throw connectionIsntOpenError;
connection.releaseSavepoint( savepoint );
}
public void close()
{
while( (currentResultSet != null) || !openResultSetQueue.isEmpty() )
{
try
{
Thread.sleep( 50 );
}
catch( Exception e )
{
}
}
try
{
if( connection != null )
{
connection.close();
connection = null;
}
}
catch( SQLException e )
{
}
}
public boolean isClosed()
{
try
{
if( connection == null )
return true;
return connection.isClosed();
}
catch( SQLException e )
{
return true;
}
}
public SQLWarning getWarnings() throws SQLException
{
if( connection == null )
throw connectionIsntOpenError;
return connection.getWarnings();
}
public void clearWarnings() throws SQLException
{
if( connection == null )
throw connectionIsntOpenError;
connection.clearWarnings();
}
// default connection methods <<
}
[/java]
MyConnectionPool.java
[java]package _04_sql_connections;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.sql.SQLException;
import java.util.Stack;
import javax.swing.Timer;
public class MyConnectionPool
{
// constants >>
private final Stack<MyConnectionClass> pool = new Stack<MyConnectionClass>();
// constants <<
// constructor parameter settings >>
private final int minConnections;
private final int maxConnections;
private final int timeoutSeconds;
// constructor parameter settings <<
// constructor parameters to create a connection >>
private final String databaseVersion;
private final String driver;
private final String url;
private final String database;
private final String username;
private final String password;
// constructor parameters to create a connection <<
// variables >>
private int currentConnections = 0;
private int currentConnectionsInUse = 0;
// variables <<
/**
- minConnections <= 0 means no minimum connections
- maxConnections <= 0 means no maximum connections
- timeoutSeconds <= 0 means no timeout
/
public MyConnectionPool( int minConnections, int maxConnections, int timeoutSeconds, String databaseVersion, String driver, String url, String database, String username, String password )
{
this.minConnections = minConnections;
this.maxConnections = maxConnections;
this.timeoutSeconds = timeoutSeconds;
this.databaseVersion = databaseVersion;
this.driver = driver;
this.url = url;
this.database = database;
this.username = username;
this.password = password;
closeOldConnectionsTimer.start();
}
private ActionListener closeOldConnectionsActionListener = new ActionListener()
{
public void actionPerformed( ActionEvent e )
{
closeOldConnections( minConnections, timeoutSeconds );
}
};
private Timer closeOldConnectionsTimer = new Timer( 601000, closeOldConnectionsActionListener );
public MyConnection grabTempConnection() throws ClassNotFoundException, InstantiationException, IllegalAccessException, SQLException
{
MyConnection connection = grabConnection();
releaseConnection( connection );
return connection;
}
public MyConnection grabConnection() throws ClassNotFoundException, InstantiationException, IllegalAccessException, SQLException
{
// wait until a connection becomes available (or until one can be created) >>
while( pool.isEmpty() && (currentConnections >= maxConnections) && (maxConnections > 0) )
{
synchronized( this )
{
try
{
wait( 1000 );
}
catch( Exception e )
{
}
}
}
// wait until a connection becomes available (or until one can be created) <<
synchronized( pool )
{
MyConnection connection = null;
if( !pool.isEmpty() )
{
connection = pool.pop().getConnection();
}
else
{
currentConnections++;
connection = new MyConnection( databaseVersion, driver, url, database, username, password );
connection.open();
}
synchronized( this )
{
notifyAll();
}
currentConnectionsInUse++;
return connection;
}
}
public void releaseConnection( MyConnection connection )
{
if( connection != null )
{
currentConnectionsInUse–;
synchronized( pool )
{
connection.closeResultSet();
pool.add( new MyConnectionClass(connection) );
}
synchronized( this )
{
notifyAll();
}
}
}
public void closeOldConnections( int keepMinimumAmount, int timeoutSeconds )
{
synchronized( pool )
{
@SuppressWarnings( “unchecked” )
Stack<MyConnectionClass> oldPool = (Stack<MyConnectionClass>) pool.clone();
pool.clear();
int size = currentConnectionsInUse;
while( !oldPool.isEmpty() )
{
MyConnectionClass myConnectionClass = oldPool.pop();
if( (size < keepMinimumAmount) || (myConnectionClass.getStandbyTime() < timeoutSeconds) || (timeoutSeconds <= 0) )
{
size++;
pool.add( myConnectionClass );
}
else
{
myConnectionClass.getConnection().close();
currentConnections–;
}
}
}
}
private class MyConnectionClass
{
private MyConnection connection;
private long lastUsedTime = System.currentTimeMillis();
public MyConnectionClass( MyConnection connection )
{
this.connection = connection;
}
public MyConnection getConnection()
{
return connection;
}
/** @return The time in seconds this connection hasn’t been used */
public long getStandbyTime()
{
return (System.currentTimeMillis() - lastUsedTime) / 1000;
}
}
}
[/java]
_Main_BoneCP.java
[java]package _04_sql_connections;
import com.jolbox.bonecp.BoneCP;
import com.jolbox.bonecp.BoneCPConfig;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
public class _Main_BoneCP
{
public static void main( String[] args )
{
// config connection >>
try
{
Class.forName( “com.mysql.jdbc.Driver” );
}
catch( Exception e )
{
e.printStackTrace();
System.exit( 1 );
}
BoneCPConfig config = new BoneCPConfig();
config.setJdbcUrl( “jdbc:mysql://localhost:3306/jme_game_01” ); // jdbc:mysql://127.0.0.1/yourdb
config.setUsername( “jme_game_01” );
config.setPassword( “k77” );
config.setMinConnectionsPerPartition( 1 );
config.setMaxConnectionsPerPartition( 5 );
config.setPartitionCount( 2 );
BoneCP connectionPoolTemp = null;
try
{
connectionPoolTemp = new BoneCP( config );
}
catch( Exception e )
{
e.printStackTrace();
System.exit( 1 );
}
final BoneCP connectionPool = connectionPoolTemp;
// config connection <<
// test a connection >>
try
{
connectionPool.getConnection().close();
}
catch( Exception e )
{
e.printStackTrace();
System.exit( 1 );
}
// test a connection <<
// speed tests >>
// single thread >>
try
{
long time = System.currentTimeMillis();
for( int i=1; i<=10000; i++ )
{
query( connectionPool );
}
System.out.println( System.currentTimeMillis() - time );
}
catch( Exception e )
{
e.printStackTrace();
System.exit( 1 );
}
// single thread <<
// 5 threads >>
Thread[] threads = new Thread[5];
for( int i=0; i<threads.length; i++ )
{
threads = new Thread()
{
public void run()
{
try
{
for( int i=1; i<=10000; i++ )
{
query( connectionPool );
}
}
catch( Exception e )
{
e.printStackTrace();
System.exit( 1 );
}
}
};
}
try
{
long time = System.currentTimeMillis();
for( int i=0; i<threads.length; i++ )
{
threads.start();
}
for( int i=0; i<threads.length; i++ )
{
threads.join();
}
System.out.println( System.currentTimeMillis() - time );
}
catch( Exception e )
{
e.printStackTrace();
System.exit( 1 );
}
// 5 threads <<
// speed tests <<
}
private static void query( BoneCP connectionPool ) throws SQLException
{
Connection connection = connectionPool.getConnection();
ResultSet set = connection.prepareStatement( “SELECT * FROM _data;” ).executeQuery();
set.close();
connection.close();
}
}
[/java]
_Main_Mine.java
[java]package _04_sql_connections;
import java.sql.SQLException;
public class _Main_Mine
{
public static void main( String[] args )
{
// config connection >>
final MyConnectionPool connectionPool = new MyConnectionPool( 1, 10, 60, “1.00”, “mysql”, “localhost:3306”, “jme_game_01”, “jme_game_01”, “k77” );
// config connection <<
// test a connection >>
try
{
connectionPool.grabTempConnection();
}
catch( Exception e )
{
e.printStackTrace();
System.exit( 1 );
}
// test a connection <<
// speed tests >>
// single thread >>
try
{
long time = System.currentTimeMillis();
for( int i=1; i<=10000; i++ )
{
query( connectionPool );
}
System.out.println( System.currentTimeMillis() - time );
}
catch( Exception e )
{
e.printStackTrace();
System.exit( 1 );
}
// single thread <<
// 5 threads >>
Thread[] threads = new Thread[5];
for( int i=0; i<threads.length; i++ )
{
threads = new Thread()
{
public void run()
{
try
{
for( int i=1; i<=10000; i++ )
{
query( connectionPool );
}
}
catch( Exception e )
{
e.printStackTrace();
System.exit( 1 );
}
}
};
}
try
{
long time = System.currentTimeMillis();
for( int i=0; i<threads.length; i++ )
{
threads.start();
}
for( int i=0; i<threads.length; i++ )
{
threads.join();
}
System.out.println( System.currentTimeMillis() - time );
}
catch( Exception e )
{
e.printStackTrace();
System.exit( 1 );
}
// 5 threads <<
// speed tests <<
}
private static void query( MyConnectionPool connectionPool ) throws ClassNotFoundException, InstantiationException, IllegalAccessException, SQLException
{
MyConnection connection = connectionPool.grabConnection();
connection.openResultSet( “SELECT * FROM _data;” );
connection.closeResultSet();
connectionPool.releaseConnection( connection );
}
}
[/java]