[Java] SQL Connection Pool

[Anthony]

Well, I while ago I remembered arguing with some guy about what the purpose of a connection pool was and I just realized it, if you want to submit multiple query at the same time from different threads you don't want to wait for each one to finish executing because well it's slow. This allows you to fire queries from multiple threads and wait for their execution.

/*
* Copyright (C) 2010 Anthony Snavely
*
* This program is free software. It comes without any warranty, to
* the extent permitted by applicable law. You can redistribute it
* and/or modify it under the terms of the Do What The Fuck You Want
* To Public License, Version 2, as published by Sam Hocevar. See
* Only the registered members can see the link. for more details.
*/
package com.anthony.mscp.dbconnpool;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.concurrent.ArrayBlockingQueue;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
*
* @author Anthony Snavely <anthony.snavely@gmail.com>
*/
public class SqlConnectionPool {

public static final int POOL_SIZE = 10;
private static final int PORT = 3306;
private static final String HOST = "HOST";
private static final String USER = "USER";
private static final String PASS = "PASS";
private static final String DATABASE = "DATABASE";
private static ArrayBlockingQueue<Statement> dbStatements = new ArrayBlockingQueue<Statement>(POOL_SIZE);
private static ExecutorService queryExecutor = Executors.newFixedThreadPool(POOL_SIZE / 5);

public static void executeStatement(SqlStatement statement) {
queryExecutor.submit(statement);
}

public static Statement getNextStatement() {
return dbStatements.poll();
}

public static void returnStatement(Statement statement) {
dbStatements.offer(statement);
}

public static void closeConnection(Statement statement) {
try {
dbStatements.remove(statement);
statement.close();
statement.getConnection().close();
} catch (SQLException ex) {
Logger.getLogger(SqlConnectionPool.class.getName() ).log(Level.SEVERE, null, ex);
}

}

static class SqlSafeConnectionCloser extends Thread {
@Override
public void run() {
Statement s = null;
while((s = getNextStatement()) != null) {
closeConnection(s);
}
}
}

static {
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
} catch (InstantiationException ex) {
Logger.getLogger(SqlConnectionPool.class.getName() ).info("Couldn't load JDBC driver.");
} catch (IllegalAccessException ex) {
Logger.getLogger(SqlConnectionPool.class.getName() ).info("Couldn't load JDBC driver.");
} catch (ClassNotFoundException ex) {
Logger.getLogger(SqlConnectionPool.class.getName() ).info("Couldn't load JDBC driver.");
}
for (int i = 0; i < POOL_SIZE; i++) {
try {
Connection conn = DriverManager.getConnection("jdbc:mysql://" + HOST + ":" + PORT + "/" + DATABASE, USER, PASS);
Statement statement = conn.createStatement();
statement.setEscapeProcessing(true);
if (!dbStatements.offer(statement)) {
System.out.println("Coulnd't add statement to the queue.");
}
} catch (SQLException ex) {
Logger.getLogger(SqlConnectionPool.class.getName() ).info("Cannot connect to database " + i);
}
Runtime.getRuntime().addShutdownHook(new SqlSafeConnectionCloser());
}
}
}


/*
* Copyright (C) 2010 Anthony Snavely
*
* This program is free software. It comes without any warranty, to
* the extent permitted by applicable law. You can redistribute it
* and/or modify it under the terms of the Do What The Fuck You Want
* To Public License, Version 2, as published by Sam Hocevar. See
* Only the registered members can see the link. for more details.
*/
package com.anthony.mscp.dbconnpool;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
*
* @author Anthony Snavely <anthony.snavely@gmail.com>
*/
public class SqlStatement implements Runnable {

public enum SqlStatementType {

UPDATE,
RESULT
}
private String query;
private SqlStatementListener listener;
private SqlStatementType type;

public SqlStatement(SqlStatementType type, String query) {
this.type = type;
this.query = query;
}

public void setListener(SqlStatementListener listener) {
this.listener = listener;
}

@Override
public void run() {
Statement statement = SqlConnectionPool.getNextStatement();
if (statement != null) {
try {
Object results = type == SqlStatementType.RESULT ? statement.executeQuery(query) : statement.executeUpdate(query);
if (listener != null) {
if (SqlStatementType.RESULT == type) {
listener.queryComplete((ResultSet) results);
} else {
listener.updateComplete((Integer) results);
}
SqlConnectionPool.returnStatement(statement);
}
} catch (SQLException ex) {
if (listener != null) {
listener.statementException(ex);
}
}
}
}
}


/*
* Copyright (C) 2010 Anthony Snavely
*
* This program is free software. It comes without any warranty, to
* the extent permitted by applicable law. You can redistribute it
* and/or modify it under the terms of the Do What The Fuck You Want
* To Public License, Version 2, as published by Sam Hocevar. See
* Only the registered members can see the link. for more details.
*/

package com.anthony.mscp.dbconnpool;

import java.sql.ResultSet;
import java.sql.SQLException;

/**
*
* @author Anthony Snavely <anthony.snavely@gmail.com>
*/
public interface SqlStatementListener {

public void queryComplete(ResultSet results);

public void updateComplete(int val);

public void statementException(SQLException exception);

}

[Johnny]

Lol, doubt this yours. Seen before

[The Soul]

Lol, doubt this yours. Seen before

You're pretty damn ignorant then; this is definitely Anthony's.

[Anthony]

Lol, doubt this yours. Seen before

You know I do have the ability to post on other forums?