DevTut

# Obtaining a SqlRowSet from SimpleJdbcCall

This describes how to directly obtain a SqlRowSet using SimpleJdbcCall with a stored procedure in your database that has a cursor output parameter,

I am working with an Oracle database, I've attempted to create an example that should work for other databases, my Oracle example details issues with Oracle.

# SimpleJdbcCall creation

Typically, you will want to create your SimpleJdbcCalls in a Service.

This example assumes your procedure has a single output parameter that is a cursor; you will need to adjust your declareParameters to match your procedure.

@Service
public class MyService() {

@Autowired
    private DataSource dataSource;
    
    // Autowire your configuration, for example
    @Value("${db.procedure.schema}")
    String schema;
    
    private SimpleJdbcCall myProcCall;
    
    // create SimpleJdbcCall after properties are configured
    @PostConstruct
    void initialize() {
        this.myProcCall = new SimpleJdbcCall(dataSource)
                        .withProcedureName("my_procedure_name")
                        .withCatalogName("my_package")
                        .withSchemaName(schema)
                        .declareParameters(new SqlOutParameter(
                            "out_param_name",
                            Types.REF_CURSOR, 
                            new SqlRowSetResultSetExtractor()));
    }

    public SqlRowSet myProc() {
        Map<String, Object> out = this.myProcCall.execute();
        return (SqlRowSet) out.get("out_param_name");
    }

}

There are many options you can use here:

  • withoutProcedureColumnMetaDataAccess() needed if you have overloaded procedure names or just don't want SimpleJdbcCall to validate against the database.
  • withReturnValue() if procedure has a return value. First value given to declareParameters defines the return value. Also, if your procedure is a function, use withFunctionName and executeFunction when executing.
  • withNamedBinding() if you want to give arguments using names instead of position.
  • useInParameterNames() defines the argument order. I think this may be required if you pass in your arguments as a list instead of a map of argument name to value. Though it may only be required if you use withoutProcedureColumnMetaDataAccess()

# Oracle Databases

There are a number of issues with Oracle. Here's how to resolve them.

Assuming your procedure output parameter is ref cursor, you will get this exception.

java.sql.SQLException: Invalid column type: 2012

So change Types.REF_CURSOR to OracleTypes.CURSOR in simpleJdbcCall.declareParameters()

Supporting OracleTypes

You may only need to do this if you have certain column types in your data.

The next issue I encountered was that proprietary Types such as oracle.sql.TIMESTAMPTZ caused this error in SqlRowSetResultSetExtractor:

Invalid SQL type for column; nested exception is java.sql.SQLException: Invalid SQL type for column

So we need to create a ResultSetExtractor that supports Oracle types.
I will explain the reason for password after this code.

package com.boost.oracle;

import oracle.jdbc.rowset.OracleCachedRowSet;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.support.rowset.ResultSetWrappingSqlRowSet;
import org.springframework.jdbc.support.rowset.SqlRowSet;

import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * OracleTypes can cause {@link org.springframework.jdbc.core.SqlRowSetResultSetExtractor}
 * to fail due to a Oracle SQL type that is not in the standard {@link java.sql.Types}.
 *
 * Also, types such as {@link oracle.sql.TIMESTAMPTZ} require a Connection when processing
 * the ResultSet; {@link OracleCachedRowSet#getConnectionInternal()} requires a JNDI
 * DataSource name or the username and password to be set.
 *
 * For now I decided to just set the password since changing SpringBoot to a JNDI DataSource
 * configuration is a bit complicated.
 *
 * Created by Arlo White on 2/23/17.
 */
public class OracleSqlRowSetResultSetExtractor implements ResultSetExtractor<SqlRowSet> {

    private String oraclePassword;

    public OracleSqlRowSetResultSetExtractor(String oraclePassword) {
        this.oraclePassword = oraclePassword;
    }

    @Override
    public SqlRowSet extractData(ResultSet rs) throws SQLException, DataAccessException {
        OracleCachedRowSet cachedRowSet = new OracleCachedRowSet();
        // allows getConnectionInternal to get a Connection for TIMESTAMPTZ
        cachedRowSet.setPassword(oraclePassword);
        cachedRowSet.populate(rs);
        return new ResultSetWrappingSqlRowSet(cachedRowSet);
    }

}

Certain Oracle types require a Connection to obtain the column value from a ResultSet. TIMESTAMPTZ is one of these types. So when rowSet.getTimestamp(colIndex) is called, you will get this exception:

Caused by: java.sql.SQLException: One or more of the authenticating RowSet properties not set at oracle.jdbc.rowset.OracleCachedRowSet.getConnectionInternal(OracleCachedRowSet.java:560) at oracle.jdbc.rowset.OracleCachedRowSet.getTimestamp(OracleCachedRowSet.java:3717) at org.springframework.jdbc.support.rowset.ResultSetWrappingSqlRowSet.getTimestamp

If you dig into this code, you will see that the OracleCachedRowSet needs the password or a JNDI DataSource name to get a Connection. If you prefer the JNDI lookup, just verify that OracleCachedRowSet has DataSourceName set.

So in my Service, I Autowire in the password and declare the output parameter like this:

new SqlOutParameter("cursor_param_name", OracleTypes.CURSOR, new OracleSqlRowSetResultSetExtractor(oraclePassword))

← Creating and using beans →