Connections not being released when using STRUCTS in Spring Boot and Oracle

Question

I have a Spring Boot application where I host several REST and SOAP WebServices. The latest request from my client was to execute a stored procedure which receives several parameters and 3 custom arrays.

It seems to work fine, but after 5 executions, I get the following error:

Timeout: Pool empty. Unable to fetch a connection in 30 seconds, none available[size:5; busy:5; idle:0; lastwait:30000].

I don't understand why connections are not being release when using this specific feature.

I'm extending StoredProcedure from Spring:

public class OracleArrayStoredProcedure extends StoredProcedure {

After that, I have a bunch of final Strings for my parameters (over 50), and then I have the constructor:

public OracleArrayStoredProcedure(DataSource ds) {
        super(ds, PROC_NAME);

Inside the constructor I have the parameters, which includes also the arrays:

declareParameter(new SqlParameter(PARAM1, OracleTypes.NUMBER));

        // Arrays
        declareParameter(new SqlInOutParameter(ARRAY1, OracleTypes.ARRAY, "ARRAY1"));
        declareParameter(new SqlInOutParameter(ARRAY2, OracleTypes.ARRAY, "ARRAY2"));
        declareParameter(new SqlInOutParameter(ARRAY3, OracleTypes.ARRAY, "ARRAY3"));
        compile();

Then I have the execute, which is where I get the connection I can't release:

public ArrayResponse execute(ArrayRequest arrayRequest) {

        ArrayResponse response = new ArrayResponse();
        Map<String, Object> inputs = new HashMap<String, Object>();
        try {

            OracleConnection connection = getJdbcTemplate().getDataSource().getConnection()
                    .unwrap(OracleConnection.class);

            // ARRAY1
            ArrayDescriptor arrayFirstDescriptor = new ArrayDescriptor(ARRAY1, connection);
            StructDescriptor recFirstDescriptor = new StructDescriptor("FIRSTREC", connection);

            Object[] arrayFirstStruct = new Object[arrayRequest.getArray1().size()];
            int i = 0;
            for (Iterator<Array1> iterator = arrayRequest.getArray1().iterator(); iterator
                    .hasNext();) {
                Model1 model1 = (Model1) iterator.next();
                STRUCT s = new STRUCT(arrayFirstDescriptor, connection, new Object[] { 
// Bunch of attributes
                arrayStructArray[i++] = s;
            }
            ARRAY inStructArray = new ARRAY(arrayDescriptor, connection, array1Struct);

finally I put the arrays and parameters in the inputs map and execute it:

        inputs.put(ARRAY1, inStructArray);
        Map<String, Object> out = super.execute(inputs);

The issue with this approach, is that I can't release the connection (even if I use connection.close()), so after 5 executions it doesn't work anymore. What am I doing wrong?

When I don't have to use a STRUCT, i don't need to use

OracleConnection connection = getJdbcTemplate().getDataSource().getConnection()
                        .unwrap(OracleConnection.class);

So everything works just fine.

Answer 1

I was able to fix it by implementing AbstractSqlTypeValue(), which has a method that passes the connection of the DataSource. This way, I don't have to manually get a connection. Then I simply add the structArray to the input map.

SqlTypeValue structArray = new AbstractSqlTypeValue() {
                @Override
                protected Object createTypeValue(Connection connection, int arg1, String arg2) throws SQLException {
                    Object[] modelArray = new Object[request.getArray().size()];
                    int i = 0;
                    for (Iterator<Model> iterator = request.getArray().iterator(); iterator.hasNext();) {
                        Model model = (Model) iterator.next();
                        Struct s = connection.createStruct("TEST_REC", new Object[] { 
                          // All attributes go here
                        });
                        modelArray[i++] = s;
                    }
                    Array structArray = ((OracleConnection) connection).createOracleArray("TEST_STRUCT",
                            modelArray);
                    return structArray ;
                }
            };