Spring SimpleJdbcCall default (optional) arguments

Question

I am trying to invoke a stored procedure which has default (optional) arguments without passing them and it is not working. Essentially the same problem as described here.

My code:

  SqlParameterSource in = new MapSqlParameterSource()
        .addValue("ownname", "USER")
        .addValue("tabname", cachedTableName)
        .addValue("estimate_percent", 20)
        .addValue("method_opt", "FOR ALL COLUMNS SIZE 1")
        .addValue("degree", 0)
        .addValue("granularity", "AUTO")
        .addValue("cascade", Boolean.TRUE)
        .addValue("no_invalidate", Boolean.FALSE)
        .addValue("force", Boolean.FALSE);

And I get an exception:

Caused by: org.springframework.dao.InvalidDataAccessApiUsageException: Required input parameter 'PARTNAME' is missing
    at org.springframework.jdbc.core.CallableStatementCreatorFactory$CallableStatementCreatorImpl.createCallableStatement(CallableStatementCreatorFactory.java:209)

Where PARTNAME is an optional parameter according to this. Also confirmed by the fact that I can run this procedure w/o the PARTNAME argument manually.

the second hyper link shows 404 . – Azhagu Surya Nov 18 '21 at 09:16 — Azhagu Surya, Nov 18 '21 at 09:16

score 7 · Accepted Answer · answered Oct 31 '12 at 19:23

Ater giving up on this question and just passing all the parameters, including optional ones I ran into its inability to pass boolean arguments, because boolean is not an SQL data type, only PL/SQL.

So my current solution is that JDBC is not suited for running stored procedures and this is how I'm working around it:

  jdbcTemplate.execute(
        new CallableStatementCreator() {
           public CallableStatement createCallableStatement(Connection con) throws SQLException{
              CallableStatement cs = con.prepareCall("{call sys.dbms_stats.gather_table_stats(ownname=>user, tabname=>'" + cachedMetadataTableName + "', estimate_percent=>20, method_opt=>'FOR ALL COLUMNS SIZE 1', degree=>0, granularity=>'AUTO', cascade=>TRUE, no_invalidate=>FALSE, force=>FALSE) }");
              return cs;
           }
        },
        new CallableStatementCallback() {
           public Object doInCallableStatement(CallableStatement cs) throws SQLException{
              cs.execute();
              return null; // Whatever is returned here is returned from the jdbcTemplate.execute method
           }
        }
  );

score 4 · Answer 2 · answered May 18 '17 at 00:24

Came up with a decent solution to this today, that copes with non-null defaults, and does not use fruity reflection techniques. It works by creating the metadata context for the function externally to retrieve all the parameter types and so forth, then constructing the SimpleJdbcCall manually from that.

First, create a CallMetaDataContext for the function:

    CallMetaDataContext context = new CallMetaDataContext();
    context.setFunction(true);
    context.setSchemaName(schemaName);
    context.setProcedureName(functionName);
    context.initializeMetaData(jdbcTemplate.getDataSource());
    context.processParameters(Collections.emptyList());

Next, create the SimpleJdbcCall, but force it to not do its own metadata lookup:

SimpleJdbcCall simpleJdbcCall = new SimpleJdbcCall(jdbcTemplate);
// This forces the call object to skip metadata lookup, which is the part that forces all parameters
simpleJdbcCall.setAccessCallParameterMetaData(false);

// Now go back to our previously created context and pull the parameters we need from it
simpleJdbcCall.addDeclaredParameter(context.getCallParameters().get(0));
for (int i = 0; i < params.length; ++i) {
    simpleJdbcCall.addDeclaredParameter(context.getCallParameters().get(i));
}
// Call the function and retrieve the result
Map<String, Object> resultsMap = simpleJdbcCall
                        .withSchemaName(schemaName)
                        .withFunctionName(functionName)
                        .execute(params);
Object returnValue = resultsMap.get(context.getScalarOutParameterName());

score 3 · Answer 3 · answered Dec 07 '19 at 14:19

I found solution for my case with SimpleJdbcCall and Spring 5.2.1, Java 8, Oracle 12.

You need to:

Use .withoutProcedureColumnMetaDataAccess()
Use .withNamedBinding()
Declare parameters, you know about in .declareParameters() call. Procedure will be called only with parameters, declared in this method. Default parameters, you dont want to set, arent writing here.

Example call is below

final String dataParamName = "P_DATA";
final String ageParamName = "P_AGE";
final String genderParamName = "P_GENDER";

final String acceptedParamName = "P_ACCEPTED";


SimpleJdbcCall simpleJdbcCall = new SimpleJdbcCall(getJdbcTemplate())
        .withCatalogName("PKG_USER")
        .withProcedureName("USER_CHECK")
        .withoutProcedureColumnMetaDataAccess()
        .withNamedBinding()
        .declareParameters(
                new SqlParameter(dataParamName, OracleTypes.VARCHAR),
                new SqlParameter(ageParamName, OracleTypes.NUMBER),
                new SqlParameter(genderParamName, OracleTypes.VARCHAR),
                new SqlOutParameter(acceptedParamName, OracleTypes.NUMBER)
        );

SqlParameterSource parameterSource = new MapSqlParameterSource()
        .addValue(dataParamName, data)
        .addValue(ageParamName, age)
        .addValue(genderParamName, gender);

Map<String, Object> out = simpleJdbcCall.execute(parameterSource);

score 1 · Answer 4 · answered Mar 31 '16 at 14:40

Here is a different approach that I have taken. I added the ability for the user to set the number of parameters they will be providing on the call. These will be the first n number of positional parameters. Any remaining parameters available in the stored-proc, will have to be set via the database's default value handling. This allows new parameters to be added to the end of the list with default values, or to be null-able, without breaking code that does not know to provide a value.

I sub-classed SimpleJdbcCall and added the methods to set the "maxParamCount". I also used a bit a evil reflection to set my sub-classed version of CallMetaDataContext.

public class MySimpleJdbcCall extends SimpleJdbcCall
{
    private final MyCallMetaDataContext callMetaDataContext = new MyCallMetaDataContext();

    public MySimpleJdbcCall(DataSource dataSource)
    {
        this(new JdbcTemplate(dataSource));
    }

    public MySimpleJdbcCall(JdbcTemplate jdbcTemplate)
    {
        super(jdbcTemplate);

        try
        {
            // Access private field
            Field callMetaDataContextField = AbstractJdbcCall.class.getDeclaredField("callMetaDataContext");
            callMetaDataContextField.setAccessible(true);

            // Make it non-final
            Field modifiersField = Field.class.getDeclaredField("modifiers");
            modifiersField.setAccessible(true);
            modifiersField.setInt(callMetaDataContextField, callMetaDataContextField.getModifiers() & ~Modifier.FINAL);

            // Set field
            callMetaDataContextField.set(this, this.callMetaDataContext);
        }
        catch (NoSuchFieldException | IllegalAccessException ex)
        {
            throw new RuntimeException("Exception thrown overriding AbstractJdbcCall.callMetaDataContext field", ex);
        }
    }

    public MySimpleJdbcCall withMaxParamCount(int maxInParamCount)
    {
        setMaxParamCount(maxInParamCount);
        return this;
    }

    public int getMaxParamCount()
    {
        return this.callMetaDataContext.getMaxParamCount();
    }

    public void setMaxParamCount(int maxInParamCount)
    {
        this.callMetaDataContext.setMaxParamCount(maxInParamCount);
    }
}

In my CallMetaDataContext sub-class, I store the maxInParamCount, and use it to trim the list of parameters known to exist in the stored-proc.

public class MyCallMetaDataContext extends CallMetaDataContext
{
    private int maxParamCount = Integer.MAX_VALUE;

    public int getMaxParamCount()
    {
        return maxParamCount;
    }

    public void setMaxParamCount(int maxInParamCount)
    {
        this.maxParamCount = maxInParamCount;
    }

    @Override
    protected List<SqlParameter> reconcileParameters(List<SqlParameter> parameters)
    {
        List<SqlParameter> limittedParams = new ArrayList<>();
        int paramCount = 0;
        for(SqlParameter param : super.reconcileParameters(parameters))
        {
            if (!param.isResultsParameter())
            {
                paramCount++;
                if (paramCount > this.maxParamCount)
                    continue;
            }

            limittedParams.add(param);
        }
        return limittedParams;
    }
}

Use is basically the same except for seeting the max parameter count.

SimpleJdbcCall call = new MySimpleJdbcCall(jdbcTemplate)
        .withMaxParamCount(3)
        .withProcedureName("MayProc");

SMALL RANT: It's funny that Spring is well know for its IOC container. But, within its utility classes, I have to resort to reflection to provide an alternate implementation of a dependent class.

Paul Z. · Answer 5 · 2015-12-24T09:22:52.093

Was also struggling with the problem, and didn't want to deal with strings. There could be more interesting solution, if we get default values from meta data, which spring doesn't care about in default implementation, but I simply put nulls there. The solution came like the following:

Overridden simpleJdbcCall

 private class JdbcCallWithDefaultArgs extends SimpleJdbcCall {

    CallableStatementCreatorFactory callableStatementFactory;

    public JdbcCallWithDefaultArgs(JdbcTemplate jdbcTemplate) {
        super(jdbcTemplate);
    }

    @Override
    protected CallableStatementCreatorFactory getCallableStatementFactory() {
        return callableStatementFactory;
    }

    @Override
    protected void onCompileInternal() {
        callableStatementFactory =
                new CallableStatementCreatorWithDefaultArgsFactory(getCallString(), this.getCallParameters());
        callableStatementFactory.setNativeJdbcExtractor(getJdbcTemplate().getNativeJdbcExtractor());

    }


    @Override
    public Map<String, Object> execute(SqlParameterSource parameterSource) {
        ((CallableStatementCreatorWithDefaultArgsFactory)callableStatementFactory).cleanupParameters(parameterSource);
        return super.doExecute(parameterSource);
    }
}

And overriden CallableStatementCreatorFactory

public class CallableStatementCreatorWithDefaultArgsFactory extends CallableStatementCreatorFactory {

private final Logger logger = LoggerFactory.getLogger(getClass());
private final List<SqlParameter> declaredParameters;

public CallableStatementCreatorWithDefaultArgsFactory(String callString, List<SqlParameter> declaredParameters) {
    super(callString, declaredParameters);
    this.declaredParameters = declaredParameters;
}

protected void cleanupParameters(SqlParameterSource sqlParameterSource) {
    MapSqlParameterSource mapSqlParameterSource = (MapSqlParameterSource) sqlParameterSource;
    Iterator<SqlParameter> declaredParameterIterator = declaredParameters.iterator();
    Set<String> parameterNameSet = mapSqlParameterSource.getValues().keySet();
    while (declaredParameterIterator.hasNext()) {
        SqlParameter parameter = declaredParameterIterator.next();
        if (!(parameter instanceof SqlOutParameter) &&
                (!mapContainsParameterIgnoreCase(parameter.getName(), parameterNameSet))) {
            logger.warn("Missing value parameter "+parameter.getName() + " will be replaced by null!");
            mapSqlParameterSource.addValue(parameter.getName(), null);
        }
    }
}

private boolean mapContainsParameterIgnoreCase(String parameterName, Set<String> parameterNameSet) {
    String lowerParameterName = parameterName.toLowerCase();
    for (String parameter : parameterNameSet) {
        if (parameter.toLowerCase().equals(lowerParameterName)) {
            return true;
        }
    }
    return false;
}

@Override
public void addParameter(SqlParameter param) {
    this.declaredParameters.add(param);
}

score 0 · Answer 6 · answered Nov 01 '19 at 13:06

0

I use this util method:

public <T> void setOptionalParameter(MapSqlParameterSource parameters, String name, T value) {
    if (value == null)
        parameters.addValue(name, value, Types.NULL);
    else
        parameters.addValue(name, value);
}

answered Nov 01 '19 at 13:06

Gustavo Amaro

131
1
4
7

Spring SimpleJdbcCall default (optional) arguments

6 Answers6

Linked