Wednesday, 7 August 2013

Java MySQL connetion pool is not working

Java MySQL connetion pool is not working

I've written a function in Java that runs a MySQL query and returns
results. I've implemented connection pooling using this method here:
http://www.kodejava.org/how-do-i-create-a-database-connection-pool/. The
function is working, but connection time is still the same as it was
without pooling ~190 ms. Can somebody tell me what am I doing wrong?
This is my code:
public static ArrayList<Map<String,Object>> query(String q) throws
Exception {
long start, end;
GenericObjectPool connectionPool = null;
String DRIVER = "com.mysql.jdbc.Driver";
String URL = "jdbc:mysql://localhost/dbname";
String USER = "root";
String PASS = "";
Class.forName(DRIVER).newInstance();
connectionPool = new GenericObjectPool();
connectionPool.setMaxActive(10);
ConnectionFactory cf = new DriverManagerConnectionFactory(URL, USER,
PASS);
PoolableConnectionFactory pcf = new PoolableConnectionFactory(cf,
connectionPool, null, null, false, true);
DataSource ds = new PoolingDataSource(connectionPool);
//create statement
Statement Stm = null;
try {
Connection Con = null;
PreparedStatement stmt = null;
start = System.currentTimeMillis();
Con = ds.getConnection();
end = System.currentTimeMillis();
System.out.println("DB Connection: " + Long.toString(end - start)
+ " ms");
//fetch out rows
ArrayList<Map<String, Object>> Rows = new
ArrayList<Map<String,Object>>();
Stm = Con.createStatement();
//query
ResultSet Result = null;
boolean Returning_Rows = Stm.execute(q);
if (Returning_Rows) {
Result = Stm.getResultSet();
} else {
return new ArrayList<Map<String,Object>>();
}
//get metadata
ResultSetMetaData Meta = null;
Meta = Result.getMetaData();
//get column names
int Col_Count = Meta.getColumnCount();
ArrayList<String> Cols = new ArrayList<String>();
for (int Index=1; Index<=Col_Count; Index++) {
Cols.add(Meta.getColumnName(Index));
}
while (Result.next()) {
HashMap<String,Object> Row = new HashMap<String,Object>();
for (String Col_Name:Cols) {
Object Val = Result.getObject(Col_Name);
Row.put(Col_Name,Val);
}
Rows.add(Row);
}
//close statement
Stm.close();
//pass back rows
return Rows;
} catch (Exception Ex) {
System.out.print(Ex.getMessage());
return new ArrayList<Map<String,Object>>();
} finally {
if (Stm != null) {
Stm.close();
}
if (Stm != null) {
Stm.close();
}
System.out.println("Max connections: " +
connectionPool.getMaxActive());
System.out.println("Active connections: " +
connectionPool.getNumActive());
System.out.println("Idle connections: " +
connectionPool.getNumIdle());
}
}
This is console output every time:
DB Connection: 186 ms
Max connections: 10
Active connections: 1
Idle connections: 0

No comments:

Post a Comment