mysql连接池失效,Java MySQL连接池不起作用

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> 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> Rows = new ArrayList>();

Stm = Con.createStatement();

//query

ResultSet Result = null;

boolean Returning_Rows = Stm.execute(q);

if (Returning_Rows) {

Result = Stm.getResultSet();

} else {

return new ArrayList>();

}

//get metadata

ResultSetMetaData Meta = null;

Meta = Result.getMetaData();

//get column names

int Col_Count = Meta.getColumnCount();

ArrayList Cols = new ArrayList();

for (int Index=1; Index<=Col_Count; Index++) {

Cols.add(Meta.getColumnName(Index));

}

while (Result.next()) {

HashMap Row = new HashMap();

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>();

} 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

UPDATE: I should note, that Java application that uses this works like this: executes, only runs one query and closes. I figured if PHP works like this and it's using connection pooling by default, so should Java? Correct me if I'm wrong.

解决方案

You create a connection pool, but you don't put anything into it. You connection pool is empty when it is created, so your first request to it is guaranteed to to create a new connection and will be just as slow as getting a connection manually.

Try putting your code into a loop, where you repeatedly get a connection from the pool. Try it once, five times, ten times and fifteen times. Note how the results change.

Some connection pools support automatically creating and holding a minimum number of connections ready for use as well as a maximum. When the pool is initialised it will pre-fetch connections so the first few calls aren't delayed.