Re: PGPoolingDataSource problem.

On 06/08/11 18:36, Benjamin(dot)Galaviz(at)LSGSkyChefs(dot)com wrote:
> I am using the JDBC3 PGPoolingDataSource to create a pool of 5 connections
> (for test) to my database.
>
> public class DatabasePool {
>

Improved and corrected (albeit untested) versions of your code below -
but please read my comment at the end of this message on why you don't
want to reinvent the wheel.

public class ConnectionFactory {
private static PGPoolingDataSource ds;

private ConnectionFactory() {
// enforce singleton
}

private static synchronized DataSource getDataSource() throws
SQLException {
if (ds == null){
ds = new PGPoolingDataSource();
ds.setDataSourceName("DataSource");
ds.setServerName("XX.XX.XX.XX");
ds.setDatabaseName("dbname");
ds.setUser("user");
ds.setPassword("password");
ds.setMaxConnections(5);
}

return ds;
}

public static Connection getConnection() throws SQLException {
return getDataSource.getConnection();
}

public static void releaseConnection(final Connection connection) {
if (connection != null) {
try {
connection.close();
} catch (SQLException exception) {
// log warning ...
}
}
}
}
>
> I get a leaked connections when I grab more than one connection from the
> pool at a time.
>
> Public void insertData(){
>
> Private String getValue(String XX){
>

public void insertData(final String sql) throws SQLException {
Connection connection = null;

try {
connection = ConnectionFactory.getConnection();

PreparedStatement ps = connection.preparedStatement(sql);
ps.setString(1, "foo");
ps.executeUpdate();
ps.close();
} finally{
ConnectionFactory.releaseConnection(connection);
}
}

public String getValue(final String sql, final String foo) throws
SQLException {
String value = null;

Connection connection = null;

try {
connection = ConnectionFactory.getConnection();

PreparedStatement ps = connection.preparedStatement(sql);

ResultSet rs = ps.executeQuery();

if (rs.next()) {
value = rs.getString(1);
}

rs.close();

ps.close();
} finally{
ConnectionFactory.releaseConnection(connection);
}

return value;
}

However, you'd be much better served by using a framework that hides
the verboseness of JDBC. The Spring framework has an excellent wrapper
for JDBC that also supports pooling, and deals well with the various
niggles that such a wrapper exposes.

Chris

Chris Wareham
Senior Software Engineer
London & Partners
6th floor,
2 More London Riverside
London SE1 2RR

Tel: +44 (0)20 7234 5848
Fax: +44 (0)20 7234 5753

http://www.londonandpartners.com/


'London & Partners Limited' is registered in England under No.7493460;
Registered Office:London & Partners, City Hall, The Queen's Walk, London SE1 2AA.

London & Partners is the official promotional agency for London attracting and delivering value to businesses, students and visitors. London & Partners is a not-for-profit public private partnership, funded by the Mayor of London and our network of commercial partners.

The information contained in this e-mail is confidential and intended for the named recipient(s) only. If you have received it in error, please notify the sender immediately and then delete the message. If you are not the intended recipient, you must not use, disclose, copy or distribute this email. The views expressed in this e-mail are those of the individual and not of London & Partners Limited. We reserve the right to read and monitor any email or attachment entering or leaving our systems without prior notice.

Please don't print this e-mail unless you really need to.