Connection Leak, using JDBCTemplate

[apunkto]

Hi, I'm using Spring JDBCTemplate, Weblogic 8.16

When I used Spring 1.2.8, it worked fine, but with 2.0 RC3 I'm getting following Weblogic warnings:

<29.08.2006 16:39:42 EEST> <Warning> <JDBC> <BEA-001074> <A JDBC pool connection leak was detected. A connection leak occurs when a connection obtained from the pool was not closed explicitly by calling close() and then was disposed by the garbage collector and returned to the connection pool. The following stack trace at create shows where the leaked connection was created. JTAConnection leaked due to using it in xa mode without close it.>

My code:

JdbcTemplate jdbc = new JdbcTemplate(dataSource);
long[] tehingArray = new long[]{1,2};
ArrayDescriptor descriptor = ArrayDescriptor.createDescriptor("TABLE_OF_NUMBER" , dataSource.getConnection() );
oracle.sql.ARRAY array = new oracle.sql.ARRAY(descriptor, dataSource.getConnection(), tehingArray);
Object[] args = {array};
String stmt = "update table r set r.field=? where id in (select * from table(cast(? as TABLE_OF_NUMBER))))";
jdbc.update(stmt,args);

Seems like jdbc.update does not close the connection?

[Marten Deinum]

In your code I see 2 locations in which you are using connections yourself, without closing the connections the next time.

1) ArrayDescriptor descriptor = ArrayDescriptor.createDescriptor("TABLE_OF_NUMBER" , dataSource.getConnection() );
2) oracle.sql.ARRAY array = new oracle.sql.ARRAY(descriptor, dataSource.getConnection(), tehingArray);

Everytime you call getConnection you will get a connection from the pool. If you don't close it, things are about to go wrong. So I guess your problem is in your own code, not in the spring code.

Just out of curiosity why are you using the oracle ARRAY type and not just put the primitives/Strings in as an array of objects?

[wpoitras]

This is a Spring way to accomplish what you are trying to do:

JdbcTemplate jdbc = new JdbcTemplate(dataSource);
final long[] tehingArray = new long[]{1,2};
Object[] args = { jdbc.execute(new ConnectionCallback() {
  Object doInConnection(Connection con) throws SQLException, DataAccessException {
    ArrayDescriptor descriptor =   ArrayDescriptor.createDescriptor("TABLE_OF_NUMBER" , con);
    return new oracle.sql.ARRAY(descriptor, con, tehingArray);
  }
String stmt = "update table r set r.field=? where id in (select * from table(cast(? as TABLE_OF_NUMBER))))";
jdbc.update(stmt,args);

[douglas.mendes]

Nice!

Let me share with you guys this method that in my opinion should be included on jdbcTemplate (ok, probably not exactly as it is...). It executes an insert and returns the generated key. Thanks, wpoitras, for mentioning the ConnectionCallback option!

	public int insert(final String sql) {
		Integer generatedKey = jdbcTemplate.execute(new ConnectionCallback<Integer>() {
			@Override
			public Integer doInConnection(Connection con)
					throws SQLException, DataAccessException {
				Integer generatedPk = null;
				Statement statement = con.createStatement();
				statement.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
				ResultSet rs = statement.getGeneratedKeys();
				rs.next();
				generatedPk = rs.getInt(1);
				return generatedPk;
			}
		});
		return generatedKey.intValue();
	}

[Marten Deinum]

Have you read the docs?! The method update(sql, keyholder) is the one you want... So this is actually already provided...

[douglas.mendes]

The method you mentioned seems proper, Marten.

But we had problems using it against a SQL Server 2000 database. The solution I posted worked fine, so I'll keep it here in case anyone else have problems.

Have you read the docs?! The method update(sql, keyholder) is the one you want... So this is actually already provided...