Wednesday, 3 December 2008

Getting sql parameters from CallableStatementCreatorFactory

I had a massive headache trying to work out what data was passed to a procedure call that was using Spring's JdbcTemplate. The problem was, the parameters can be passed in as a map, so all you have is a set of names and value with no idea what order they go in. If you have a lot of statements to sift through then manually rebuilding the sql to execute is a pain.

After trying many times to work out a way to extend CallableStatementCreatorFactory in order to pull out the information to build an sql statement I eventually reverted to just replacing the whole source file.
Below is the resulting file, with date formatting (and select from dual) directed towards Oracle. Still useful for other vendors as it's pretty simple to adjust. I have no idea why this or something like it wasn't included in spring.
/*
 * Copyright 2002-2006 the original author or authors.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package org.springframework.jdbc.core;
 
import java.math.BigDecimal;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
 
import org.apache.log4j.Logger;
import org.springframework.dao.InvalidDataAccessApiUsageException;
import org.springframework.jdbc.support.nativejdbc.NativeJdbcExtractor;
 
/**
* Helper class that can efficiently create multiple CallableStatementCreator objects with different
* parameters based on a SQL statement and a single set of parameter declarations.
*
* CW: updated to include debug which outputs an excutable sql statement with parameters substituted
*
* @author Rod Johnson
* @author Thomas Risberg
* @author Juergen Hoeller
* @author Chris Watts
*/
public class CallableStatementCreatorFactory
{
   private static final Logger logger = Logger.getLogger(CallableStatementCreatorFactory.class);
 
   /** The SQL call string, which won't change when the parameters change. */
   private final String callString;
 
   /** List of SqlParameter objects. May not be <code>null</code>. */
   private final List declaredParameters;
 
   private int resultSetType = ResultSet.TYPE_FORWARD_ONLY;
 
   private boolean updatableResults = false;
 
   private NativeJdbcExtractor nativeJdbcExtractor;
 
   /**
    * Create a new factory. Will need to add parameters via the addParameter() method or have no
    * parameters.
    */
   public CallableStatementCreatorFactory(String callString)
   {
       this.callString = callString;
       this.declaredParameters = new LinkedList();
   }
 
   /**
    * Create a new factory with sql and the given parameters.
    *
    * @param callString
    *            the SQL call string
    * @param declaredParameters
    *            list of SqlParameter objects
    */
   public CallableStatementCreatorFactory(String callString, List declaredParameters)
   {
       this.callString = callString;
       this.declaredParameters = declaredParameters;
   }
 
   /**
    * Add a new declared parameter. Order of parameter addition is significant.
    */
   public void addParameter(SqlParameter param)
   {
       this.declaredParameters.add(param);
   }
 
   /**
    * Set whether to use prepared statements that return a specific type of ResultSet.
    *
    * @param resultSetType
    *            the ResultSet type
    * @see java.sql.ResultSet#TYPE_FORWARD_ONLY
    * @see java.sql.ResultSet#TYPE_SCROLL_INSENSITIVE
    * @see java.sql.ResultSet#TYPE_SCROLL_SENSITIVE
    */
   public void setResultSetType(int resultSetType)
   {
       this.resultSetType = resultSetType;
   }
 
   /**
    * Set whether to use prepared statements capable of returning updatable ResultSets.
    */
   public void setUpdatableResults(boolean updatableResults)
   {
       this.updatableResults = updatableResults;
   }
 
   /**
    * Specify the NativeJdbcExtractor to use for unwrapping CallableStatements, if any.
    */
   public void setNativeJdbcExtractor(NativeJdbcExtractor nativeJdbcExtractor)
   {
       this.nativeJdbcExtractor = nativeJdbcExtractor;
   }
 
   /**
    * Return a new CallableStatementCreator instance given this parameters.
    *
    * @param inParams
    *            List of parameters. May be <code>null</code>.
    */
   public CallableStatementCreator newCallableStatementCreator(Map inParams)
   {
       return new CallableStatementCreatorImpl(inParams != null ? inParams : new HashMap());
   }
 
   /**
    * Return a new CallableStatementCreator instance given this parameter mapper.
    *
    * @param inParamMapper
    *            ParameterMapper implementation that will return a Map of parameters. May not be
    *            <code>null</code>.
    */
   public CallableStatementCreator newCallableStatementCreator(ParameterMapper inParamMapper)
   {
       return new CallableStatementCreatorImpl(inParamMapper);
   }
 
   /**
    * CallableStatementCreator implementation returned by this class.
    */
   private class CallableStatementCreatorImpl implements CallableStatementCreator, SqlProvider,
           ParameterDisposer
   {
 
       private ParameterMapper inParameterMapper;
 
       private Map inParameters;
 
       /**
        * Create a new CallableStatementCreatorImpl.
        *
        * @param inParamMapper
        *            ParameterMapper implementation for mapping input parameters. May not be
        *            <code>null</code>.
        */
       public CallableStatementCreatorImpl(ParameterMapper inParamMapper)
       {
           this.inParameterMapper = inParamMapper;
       }
 
       /**
        * Create a new CallableStatementCreatorImpl.
        *
        * @param inParams
        *            list of SqlParameter objects. May not be <code>null</code>.
        */
       public CallableStatementCreatorImpl(Map inParams)
       {
           this.inParameters = inParams;
       }
 
       public CallableStatement createCallableStatement(Connection con) throws SQLException
       {
           // If we were given a ParameterMapper - we must let the mapper do its thing to create the Map.
           if (this.inParameterMapper != null)
           {
               this.inParameters = this.inParameterMapper.createMap(con);
           }
           else
           {
               if (this.inParameters == null)
               {
                   throw new InvalidDataAccessApiUsageException(
                           "A ParameterMapper or a Map of parameters must be provided");
               }
           }
 
           CallableStatement cs = null;
           if (resultSetType == ResultSet.TYPE_FORWARD_ONLY && !updatableResults)
           {
               cs = con.prepareCall(callString);
           }
           else
           {
               cs = con.prepareCall(callString, resultSetType, updatableResults ? ResultSet.CONCUR_UPDATABLE
                       : ResultSet.CONCUR_READ_ONLY);
           }
 
           // Determine CallabeStatement to pass to custom types.
           CallableStatement csToUse = cs;
           if (nativeJdbcExtractor != null)
           {
               csToUse = nativeJdbcExtractor.getNativeCallableStatement(cs);
           }
 
           int sqlColIndx = 1;
           ArrayList<Object> parameterValues = new ArrayList<Object>();
           for (int i = 0; i < declaredParameters.size(); i++)
           {
               SqlParameter declaredParameter = (SqlParameter) declaredParameters.get(i);
               if (!declaredParameter.isResultsParameter())
               {
                   // So, it's a call parameter - part of the call string.
                   // Get the value - it may still be null.
                   Object inValue = this.inParameters.get(declaredParameter.getName());
                   if (declaredParameter instanceof ResultSetSupportingSqlParameter)
                   {
                       // It's an output parameter: SqlReturnResultSet parameters already excluded.
                       // It need not (but may be) supplied by the caller.
                       if (declaredParameter instanceof SqlOutParameter)
                       {
                           parameterValues.add(null);// out parameters
                           if (declaredParameter.getTypeName() != null)
                           {
                               cs.registerOutParameter(sqlColIndx, declaredParameter.getSqlType(),
                                       declaredParameter.getTypeName());
                           }
                           else
                           {
                               if (declaredParameter.getScale() != null)
                               {
                                   cs.registerOutParameter(sqlColIndx, declaredParameter.getSqlType(),
                                           declaredParameter.getScale().intValue());
                               }
                               else
                               {
                                   cs.registerOutParameter(sqlColIndx, declaredParameter.getSqlType());
                               }
                           }
                           if ((declaredParameter).isInputValueProvided() || inValue != null)
                           {
                               StatementCreatorUtils.setParameterValue(csToUse, sqlColIndx,
                                       declaredParameter, inValue);
                           }
                       }
                   }
                   else
                   {
                       // It's an input parameter- must be supplied by the caller.
                       if (!this.inParameters.containsKey(declaredParameter.getName()))
                       {
                           throw new InvalidDataAccessApiUsageException("Required input parameter '"
                                   + declaredParameter.getName() + "' is missing");
                       }
                       StatementCreatorUtils.setParameterValue(csToUse, sqlColIndx, declaredParameter,
                               inValue);
                       parameterValues.add(inValue);// out parameters
                   }
                   sqlColIndx++;
               }
           }
 
           if (logger.isDebugEnabled())
           {
               logger.debug(buildCallableSql(callString, parameterValues));
           }
           return cs;
       }
 
       public String getSql()
       {
           return callString;
       }
 
       public void cleanupParameters()
       {
           if (this.inParameters != null)
           {
               StatementCreatorUtils.cleanupParameters(this.inParameters.values());
           }
       }
 
       public String toString()
       {
           StringBuffer buf = new StringBuffer(
                   "CallableStatementCreatorFactory.CallableStatementCreatorImpl: sql=[");
           buf.append(callString).append("]; parameters=").append(this.inParameters);
           return buf.toString();
       }
   }
 
   private static SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
 
   public static String buildCallableSql(String sql, List<Object> orderedParams)
   {
       //StringBuilder sb = new StringBuilder(sql);
       String sqlStr = sql;
       if (sqlStr.startsWith("{? = call "))
       {
           //remove first (output parameter)
           orderedParams.remove(0);
           sqlStr = sqlStr.substring("{? = call ".length());
           sqlStr = sqlStr.substring(0, sqlStr.length() - 1);
           sqlStr = "select " + sqlStr + " from dual;";
       }
       for (Object val : orderedParams)
       {
           String value;
           if (val == null)
               value = "null";
           else if (val instanceof String)
               value = "'" + ((String) val).replace("'", "''") + "'"; //escape
           else if (val instanceof Date)
               value = "TO_DATE('" + dateFormat.format((Date) val) + "', 'YYYY/MM/DD HH24:MI:SS')";
           else if (val instanceof Integer || val instanceof Long || val instanceof BigDecimal
                   || val instanceof Double)
               value = val.toString().replace("'", "''"); //escape
           else
               value = "'" + val.toString().replace("'", "''") + "'"; //escape
 
           sqlStr = sqlStr.replaceFirst("[?]", value);
       }
       return sqlStr;
   }
}

No comments:

Post a Comment