Announcement Announcement Module
No announcement yet.
Spring JDBC, DBCP and Oracle ARRAY type interaction Page Title Module
Move Remove Collapse
Conversation Detail Module
  • Filter
  • Time
  • Show
Clear All
new posts

  • Spring JDBC, DBCP and Oracle ARRAY type interaction

    I'm writing a standalone Java application that uses Commons-DBCP for connection pooling. My connection pool is talking to an Oracle 9i database and I'm trying to invoke a stored procedure that takes ARRAYs. I'm using Spring's StoredProcedure class and declarative transaction management. I have several other stored procedures that commit properly and clean up after themselves, but the stored procedure that takes the ARRAYs is not releasing its connection back to the pool correctly.

    In order to create Oracle array descriptors, I need access to the underlying Oracle-specific connection. I'm using a CommonsDbcpNativeJdbcExtractor to get to the Oracle connection object. That part works and the query executes properly, but when Spring's JDBC classes try to close the connection it's not closing properly so the app eventually runs out of connections. Does anyone know how to make this work?

    Here's my code:

    protected Map getParameters() {
    int arraySize = assets.length;

    long[] orgIds = new long[arraySize];
    long[] assetIds = new long[arraySize];
    long[] compressionTypes = new long[arraySize];
    String[] filenames = new String[arraySize];
    long[] filesizes = new long[arraySize];

    for (int i = 0; i < assets.length; i++)
    orgIds[i] = assets[i].getSubOrgId().longValue();
    assetIds[i] = assets[i].getAssetId().longValue();
    compressionTypes[i] = assets[i].getCompressionTypeId().longValue();
    filenames[i] = assets[i].getFullPathName();
    filesizes[i] = assets[i].getBytesOnDisk().longValue();

    Map params = new HashMap();
    // Top org and directory are same for all assets
    params.put(PARAM_TOP_ORG, assets[0].getTopOrgId());
    params.put(PARAM_DIRECTORY, assets[0].getDirectoryId());

    CommonsDbcpNativeJdbcExtractor nje = new CommonsDbcpNativeJdbcExtractor();

    Connection conn = nje.getNativeConnection(DataSourceUtils.getConnect ion(getJdbcTemplate().getDataSource()));

    ArrayDescriptor numArrayDesc = ArrayDescriptor.createDescriptor(NUMBER_ARRAY_TYPE , conn);
    ArrayDescriptor strArrayDesc = ArrayDescriptor.createDescriptor(VARCHAR_ARRAY_TYP E, conn);

    ARRAY orgARRAY = new ARRAY(numArrayDesc, conn, orgIds);
    params.put(PARAM_ORG_ARRAY, orgARRAY);

    ARRAY assetARRAY = new ARRAY(numArrayDesc, conn, assetIds);
    params.put(PARAM_ASSET_ARRAY, assetARRAY);

    ARRAY filesizeARRAY = new ARRAY(numArrayDesc, conn, filesizes);
    params.put(PARAM_FILESIZE_ARRAY, filesizeARRAY);

    ARRAY compressionARRAY = new ARRAY(numArrayDesc, conn, compressionTypes);
    params.put(PARAM_COMPRESSION_ARRAY, compressionARRAY);

    ARRAY filenameARRAY = new ARRAY(strArrayDesc, conn, filenames);
    params.put(PARAM_FILENAME_ARRAY, filenameARRAY);
    catch (SQLException s)
    throw new RuntimeException("Unable to set SQL Parameters for IAQuery", s);
    return params;

  • #2
    You are not showing your StoredProcedure declaration, but I suspect that you are not providing the ARRAY input parameter using an SqlTypeValue.

    You should not have to extract the connection in your code - let Spring provide it in a callback method. If you have defined a NativeJdbcExtractor then Spring will use that before you are handed the connection.


    • #3

      can you show an example configuration for defining a NativeJdbcExtractor?

      What do you mean with using SqlTypeValue? Do I have to declare a new SqlType?