Announcement Announcement Module
Collapse
No announcement yet.
Connection Leak, using JDBCTemplate Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Connection Leak, using JDBCTemplate

    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?

  • #2
    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?
    Last edited by Marten Deinum; Aug 29th, 2006, 10:19 AM.

    Comment


    • #3
      This is a Spring way to accomplish what you are trying to do:
      Code:
      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);

      Comment


      • #4
        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!

        Code:
        	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();
        	}

        Comment


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

          Comment


          • #6
            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.

            Originally posted by Marten Deinum View Post
            Have you read the docs?! The method update(sql, keyholder) is the one you want... So this is actually already provided...
            Last edited by douglas.mendes; Feb 18th, 2013, 11:57 AM.

            Comment


            • #7
              Thank you for posting this. It really helped me a lot. I understand that using this way, there will be no connection leakage issues. However, I am getting below error when I used this piece of code in WAS.

              Here is the stack trace.

              [7/26/13 16:48:19:970 IST] 0000002b SystemErr R java.lang.ClassCastException: $Proxy63 incompatible with oracle.jdbc.OracleConnection
              [7/26/13 16:48:19:971 IST] 0000002b SystemErr R at oracle.sql.ArrayDescriptor.createDescriptor(ArrayD escriptor.java:149)
              [7/26/13 16:48:19:971 IST] 0000002b SystemErr R at oracle.sql.ArrayDescriptor.createDescriptor(ArrayD escriptor.java:115)

              Please advise me on this. Thank you.

              Comment


              • #8
                Adding an additional information. The connection object I received from WAS is below:

                com.ibm.ws.rsadapter.jdbc.WSJdbcConnection@3ff03ff 0

                I wanted my code to work on servers like JBOSS, Tomcat and WAS. Please advise.

                Comment

                Working...
                X