Spring Default Symbols (Optional) SimpleJdbcCall

Question

I am trying to call a stored procedure that has default arguments (optional) without passing them, and it does not work. Essentially the same problem as described here .

My code is:

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 . It is also confirmed that I can start this procedure without the PARTNAME argument manually.

Answer 1

Ater refused this question and simply passed all the parameters, including additional ones, I was faced with his inability to pass logical arguments, because boolean is not a SQL data type, but only PL / SQL.

So my current solution is that JDBC is not suitable for running stored procedures, and this is how I work 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 } } ); 

Answer 2

Here is another approach that I have taken. I added the ability for the user to set the number of parameters that they will provide when called. This will be the first n number of positional parameters. Any remaining options available in stored-proc must be set using database default processing. This allows you to add new parameters to the end of the list with default values ​​or to be zero, without breaking code that you don’t know to provide a value.

I subclassed SimpleJdbcCall and added methods to set "maxParamCount". I also used a bit of malicious reflection to set my subclassified 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 subclass, I store maxInParamCount and use it to trim the list of parameters that are known to exist in-proc memory.

 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; } } 

Usage is basically the same, with the exception of choosing the maximum number of parameters.

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

LITTLE RENT: It's funny that Spring is well known for its IOC container. But within his utility classes, I have to resort to reflection to provide an alternative implementation of the dependent class.

Answer 3

Also struggled with the problem and did not want to deal with strings. There might be a more interesting solution if we get the default values ​​from the metadata that spring does not matter with the default implementation, but I just put zeros there. The solution was as follows:

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); } } 

code>

And override 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); } 

code>

Answer 4

I came up with a decent solution for this today, which copes with non-zero default values ​​and does not use fruit reflection methods. It works by creating a metadata context for an external function to retrieve all types of parameters, etc., then manually creating SimpleJdbcCall.

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()); 

Then create a SimpleJdbcCall, but make it not search for its own metadata:

 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());