Execute stored procedure in spring with return value and out parameters

Following code example shows how to execute stored procedure in spring with return value and out parameters. The comment along with the code explains the usage.

import java.math.BigDecimal;
import java.sql.Types;
import java.util.HashMap;
import java.util.Map;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.SqlOutParameter;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.core.simple.SimpleJdbcCall;

public class StoredProcedureExampleDAO extends SimpleJdbcCall {

 public JdbcDequeueTicketDAO(final JdbcTemplate jdbcTemplate) {
  super(jdbcTemplate);
  // Here declare all the IN and OUT parameters defined for the stored
  // procedure
  declareParameters(new SqlParameter("CountryCode", Types.VARCHAR),
    new SqlOutParameter("CountryName", Types.VARCHAR));
  // Register the stored procedure name
  withProcedureName("getCountryName");
  // This ensures the stored procedure return value also gets populated    
  // in the returned Map with key "return".
  withReturnValue();
 }
 
 public final String getCountryName(final String countryCode) {
  // Map to send the IN params values.
  Map inParams = new HashMap();
  inParams.put("CountryCode", countryCode);

  // Map for the OUT params and return value.
  Map outParams = null;
  try {
   outParams = execute(inParams);   
  } catch (Throwable t) {
   throw new DataAccessException("Failed to get the country name", t);
  }  
  if (null != outParams) {
   BigDecimal returnValue = (BigDecimal) outParams.get("return");
   // The logic of getCountryName stored procedure is that its 
   // returns 1 if execution is successful otherwise returns 0.
   if (null != returnValue && 1 == returnValue.intValue()) {
   // On success read the CountryName value
    String countryName = (String) outParams.get("CountryName");
    return countryName;
   }
   }
  throw new DataAccessException("Failed to get the country name");
 }

}

Logging using Aspect Oriented Programming

Most of the time while debugging I like to see the values of the incoming parameters and what is the returned value. The only solution is to use Eclipse debugger but some times its always not possible to run eclipse debugger. For example, the application is running in some environment where you cannot connect or sometimes you want to check quickly by just switching on DEBUG level logging.

Today I used Aspect Oriented Programming (AOP) to log incoming parameters and return value of a method execution. There are multiple ways but I like the following approach -

Ensure that required log4j and aspect jars are in the classpath of your project. The log4j is properly configured and gets initialized. If don't want to use log4j then in the MethodLogger class replace the log4j related code with whatever you require.

First create the following annotation which will be used against those methods for which logging is required -

public @interface LogMethod {
}

Now create following Aspect class in your source code. The comments explain the logic.

import org.apache.log4j.Logger;
import org.aspectj.lang.JoinPoint;
import org.aspectj.lang.annotation.AfterReturning;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;

// The Aspect annotation makes this class an Aspect
@Aspect
public class MethodLogger {

 // The Before annotation ensures beforeMethod is called before the 
 // execution of a method annotated by LogMethod.
 @Before(value = "@annotation(LogMethod)")
 public void beforeMethod(JoinPoint joinPoint) throws Throwable {
  // This ensures that the logger is of the actual class. If %c 
  // pattern is used in log4j then the class name of the actual 
  // method will print.
  Logger logger = Logger.getLogger(joinPoint.getTarget().getClass());
  if (logger.isDebugEnabled()) {
   StringBuilder builder = new StringBuilder();
   // Here we print the name of the method
   builder.append(joinPoint.getSignature().getName());
   builder.append("(");
   // Here we print the values of the method arguments
   appendArgumentValues(builder, joinPoint.getArgs());
   builder.append(")");

   logger.debug(builder.toString());
  }
 }

 // The AfterReturning annotation ensures afterReturningMethod 
 // is after the execution of a method annotated by LogMethod.
 @AfterReturning(value = "@annotation(LogMethod)", returning = "returnValue")
 public void afterReturningMethod(JoinPoint joinPoint, Object returnValue)
   throws Throwable {
  Logger logger = Logger.getLogger(joinPoint.getTarget().getClass());
  if (logger.isDebugEnabled()) {
   StringBuilder builder = new StringBuilder();
   builder.append(joinPoint.getSignature().getName());
   builder.append("(");
   // This prints the method argument class type
   appendArguments(builder, joinPoint.getArgs());
   builder.append("):");
   // This prints the return value of the method. Incase of void 
   // it prints null
   builder.append(returnValue);

   logger.debug(builder.toString());
  }
 }

 private void appendArguments(StringBuilder builder, Object[] objects) {
  for (Object obj : objects) {
   builder.append(obj.getClass().getName()).append(",");
  }

  if (objects.length > 0) {
   builder.deleteCharAt(builder.length() - 1);
  }
 }

 private void appendArgumentValues(StringBuilder builder, Object[] objects) {
  for (Object obj : objects) {
   builder.append(obj).append(",");
  }

  if (objects.length > 0) {
   builder.deleteCharAt(builder.length() - 1);
  }
 }
}