/**
* Copyright 2007 - 2011 Skyway Software, Inc.
*/
package org.skyway.spring.util.dao.call;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import javax.sql.DataSource;
import org.springframework.jdbc.BadSqlGrammarException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.core.SqlParameterValue;
import org.springframework.jdbc.core.SqlReturnResultSet;
import org.springframework.jdbc.core.simple.SimpleJdbcCall;
/**
* The SimpleJdbcCall class allows you to provide a value for
* a parameter based on its name as stored in the database
* metadata. Unfortunately, we do not have the names of
* the parameters. In our case, we have a jdbc call statement
* that looks like this:
* call(${var1}, ${var2}, ${var3})
*
* We want to be able to take advantage of the metadata provided
* by the superclass. So we need a way to map the input database
* parameters to actual values (for IN and IN/OUT parameters) and
* the output values by logical name so that a client can retrieve
* them without knowing the database parameter name.
* We do this by requiring calls to addMappedParameter
* to match the order of the parameters as defined
* in the actual stored procedure.
*/
public class MetaDataJdbcCall extends SimpleJdbcCall {
/**
* Map a logical parameter name supplied by a client
* to a value. This map is used to provide outputs
* to a client.
*/
private List<SqlParameter> logicalParameters = new ArrayList<SqlParameter>();
/**
* Map a database parameter name to a logical parameter name.
* This is an intermediate table to lookup a logical parameter
* name provided a database parameter name.
*/
private Map<String, SqlParameter> dbNameToLogicalParameterMap = new HashMap<String, SqlParameter>();
/**
* Map a database parameter name to a logical parameter name.
* This is an intermediate table to lookup a logical parameter
* name provided a database parameter name.
*/
private Map<String, String> logicalNameToDbNameMap = new HashMap<String, String>();
private boolean compiled = false;
/**
* Map a database parameter name to a value. This map is
* used to provide inputs to the call.
*/
private Map<String, Object> dbNameToValueMap = new HashMap<String, Object>();
/**
* Instantiates a new meta data jdbc call.
*
* @param dataSource the data source
*/
public MetaDataJdbcCall(DataSource dataSource) {
super(dataSource);
}
/**
* Instantiates a new meta data jdbc call.
*
* @param jdbcTemplate the jdbc template
*/
public MetaDataJdbcCall(JdbcTemplate jdbcTemplate) {
super(jdbcTemplate);
}
public void reset(){
logicalParameters = new ArrayList<SqlParameter>();
dbNameToLogicalParameterMap = new HashMap<String, SqlParameter>();
logicalNameToDbNameMap = new HashMap<String, String>();
dbNameToValueMap = new HashMap<String, Object>();
compiled = false;
getCallParameters().clear();
}
/**
* Override so that we can support a procedure name prefixed
* by a schema name.
*
* @param jdbcTemplate the jdbc template
*/
@Override
public SimpleJdbcCall withProcedureName(String procedureName) {
String schemaName;
int index;
reset();
if (procedureName != null && (index = procedureName.indexOf(".")) != -1){ //$NON-NLS-1$
schemaName = procedureName.substring(0, index);
procedureName = procedureName.substring(index + 1);
super.withSchemaName(schemaName);
}
return super.withProcedureName(procedureName);
}
// Since we reuse this we always return false
@Override
public boolean isCompiled(){
return compiled;
}
/**
* Add a logical parameter. SqlReturnResultSet parameters
* occur before the call keyword but are not returned by the
* database meta data. In this case we add it directly to
* the call parameters list.
*
* NOTE: This method must be called in the sequence that the
* parameters are defined in the database and it must be called
* once for each parameter.
*
* @param jdbcTemplate the jdbc template
*/
public void addParameter(SqlParameter sqlParameter){
if (sqlParameter instanceof SqlReturnResultSet){
getCallParameters().add(0, sqlParameter);
}
logicalParameters.add(sqlParameter);
}
/**
* This method is called to match the meta data parameters with
* a value. We populated the dbNameToValueMap in the onCompileInternal
* method. We just need to populate the args map with the values
* from our dbNameToValueMap map and call the super class method
* that does the real processing.
*
* @return map from database name to input value
*/
@Override
protected Map<String, Object> matchInParameterValuesWithCallParameters(Map<String, Object> args) {
args.putAll(getDbNameToValueMap());
return super.matchInParameterValuesWithCallParameters(args);
}
protected Map<String, Object> getDbNameToValueMap(){
return dbNameToValueMap;
}
/**
* This method is executed after the jdbc call has been compiled. The
* important part of the compilation to us is that the meta data has
* been retrieved and SqlParameters have been created. We go through
* the meta data parameters and for each parameter we map the database
* parameter name to the logical (client provided) name. Next, if the
* database parameter is an IN or IN/OUT parameter, we map the database
* parameter name to the value supplied by the client.
*/
@SuppressWarnings("nls")
@Override
protected void onCompileInternal(){
Iterator<SqlParameter> logicalParametersIterator = logicalParameters.iterator();
List<SqlParameter> callParameters = getCallParameters();
SqlParameter logicalParameter;
Object value = null;
int index = 0;
compiled = true;
for (SqlParameter callParameter : callParameters){
if (!logicalParametersIterator.hasNext()){
// Metadata has more parameters than were passed
throw new BadSqlGrammarException("JDBC Call", this.getCallString(), new SQLException("Not enough parameters provided."));
}
// The logical name is the name this parameter is known as to a client
logicalParameter = logicalParametersIterator.next();
// Map the database parameter name to the logical parameter and the logical
// parameter name to the db name
dbNameToLogicalParameterMap.put(callParameter.getName(), logicalParameter);
logicalNameToDbNameMap.put(logicalParameter.getName(), callParameter.getName());
// If this is an IN or IN/OUT parameter, map the database name to
// the value supplied by the client. Calls convertInputValue to
// give subclasses a chance to convert the provided input value.
if (callParameter.isInputValueProvided()){
if (logicalParameter instanceof NamedSqlParameterValue){
value = convertInputValue(callParameter, ((NamedSqlParameterValue)logicalParameter).getValue());
}else if (logicalParameter instanceof SqlParameterValue){
value = convertInputValue(callParameter, ((SqlParameterValue)logicalParameter).getValue());
}else{
value = null;
}
dbNameToValueMap.put(callParameter.getName(), value);
}
index++;
}
if (logicalParametersIterator.hasNext()){
// More parameters were passed than exist in the meta data
throw new BadSqlGrammarException("JDBC Call", this.getCallString(), new SQLException("Too many parameters provided."));
}
}
/**
* Utility method to get the logical parameter name given the
* db name.
*
* @param dbName the name of the parameter as defined in the db
*/
protected String getLogicalParameterName(String dbName){
SqlParameter parameter = dbNameToLogicalParameterMap.get(dbName);
return parameter != null ? parameter.getName() : null;
}
/**
* Utility method to get the logical parameter given the
* db name.
*
* @param dbName the name of the parameter as defined in the db
*/
protected SqlParameter getLogicalParameterByDbName(String dbName){
return dbNameToLogicalParameterMap.get(dbName);
}
/**
* Utility method to get the db parameter given the
* db name.
*
* @param dbName the name of the parameter as defined in the db
*/
protected SqlParameter getDbParameterByDbName(String dbName){
for (SqlParameter callParameter : getCallParameters()){
if (callParameter.getName().equals(dbName))
return callParameter;
}
return null;
}
/**
* Results are returned as a map keyed by the database parameter name.
* Go through each key (which is the database parameter name) in the
* results map and look for a corresponding logical name. If we find a
* logical name, add the value from the results map for the database
* parameter name to the logical map, keyed by the logical name. Return
* the logical map so clients can lookup values by logical name, instead
* of database parameter name.
*
* @return map from logical name to output value
*/
@Override
public Map<String, Object> execute() {
Map<String, Object> logicalMap = new LinkedHashMap<String, Object>();
Map<String, Object> resultsMap = super.execute();
String logicalParameterName;
Object value;
if (resultsMap != null){
for (String dbName : resultsMap.keySet()){
value = convertOutputValue(getDbParameterByDbName(dbName), resultsMap.get(dbName));
logicalParameterName = getLogicalParameterName(dbName);
if (logicalParameterName != null){
logicalMap.put(logicalParameterName, value);
}
}
}
return logicalMap;
}
/**
* Method provided so subclasses can convert the provided input value.
* This may be necessary when dealing with vendor specific types.
*/
public Object convertInputValue(SqlParameter sqlParameter, Object value){
return value;
}
/**
* Method provided so subclasses can convert the retrieved output value.
* This may be necessary when dealing with vendor specific types.
*/
public Object convertOutputValue(SqlParameter sqlParameter, Object value){
return value;
}
}