can you look at my connection pool if it is a possible way to implement it?
public class ConnectionPool {
private static List pool = null;
private static int available = 0;
private ConnectionPool() {}
public static DBConnection getConnection() {
if (pool == null) {
pool = new ArrayList();
for (int i = 0; i < 3; i++) {
try {
pool.add(new DBConnection());
available++;
} catch (SQLException e) {
e.printStackTrace();
}
}
}
if (pool.size() > 0) {
available--;
return pool.remove(available);
} else {
return null;
}
}
public static void returnConnection(DBConnection c) {
pool.add(c);
available++;
}
}
I'm using only one array and the client should ask the connection pool for a connection use it and then return it to the connection pool.
Connection connection = ConnectionPool.getConnection();
connection.execute("insert into users values('"+user.getUsername()+"', '"+user.getPassword()+"', '"+user.getGroup()+"', '"+banned+"', '"+broker+admin+sharehodler+company+"')");
ConnectionPool.returnConnection(connection);
connection = null;
Please I need feedback on this implementation. Thank you
解决方案
There are some points that make this implementation very problematic.
Thread safety. What if several threads work with the pool? You are not locking the list on read/write access.
Static maximum pool size of 3 connections, also you immediately create all of them, whether they are needed or not. A common strategy is to create a bunch of connections and create some more when needed, until the allowed/configured maximum is reached.
You only have static methods. It should be possible to have several pools, meaning you need instances of ConnectionPool.
No way to pass host+dbname+user+password to the connections that are created.
You don't deal with 'broken' connections - you may have to re-create a connection if an existing one screwed up. This is far more relevant than I thought before I started using pools.
Use config values instead of static values, see point #2
Lastly: sure, it's interesting to write this stuff yourself - but if you need a pool for a project, pick an existing one, such as c3p0 or the tomcat connection pool.
I'm sure there's more to point out, but unless these are fixed, there's no use in continuing.