Spring - Calling a stored procedure with SimpleJdbcCall

SimpleJdbcCall wraps JdbcTemplate to simplify the code needed to call a stored procedure or a stored function.

Example

Using SimpleJdbcCall

@Component
public class ClientBean {
  @Autowired
  private DataSource dataSource;
  private SimpleJdbcCall jdbcCall;

  @PostConstruct
  public void init() {
      JdbcTemplate template = new JdbcTemplate(dataSource);
      jdbcCall = new SimpleJdbcCall(template)
              .withProcedureName("GET_SUM")
              .declareParameters(
                      new SqlParameter("a", Types.INTEGER),
                      new SqlParameter("b", Types.INTEGER),
                      new SqlOutParameter("theSum", Types.INTEGER));

  }

  public void findSum() {
      MapSqlParameterSource paramMap = new MapSqlParameterSource()
              .addValue("a", 5)
              .addValue("b", 20);
      Map<String, Object> resultMap = jdbcCall.execute(paramMap);
      resultMap.entrySet().forEach(System.out::println);
  }
}

src/main/resources/sum-procedure.sql

 CREATE PROCEDURE GET_SUM(IN first_num INT, IN second_num INT, OUT result INT)
  BEGIN ATOMIC
     SET result = first_num + second_num;
  END
  /;

Java Config

@Configuration
@ComponentScan
public class AppConfig {
  @Bean
  public DataSource dataSource() {
      return new EmbeddedDatabaseBuilder()
              .setType(EmbeddedDatabaseType.HSQL)
              .addScript("sum-procedure.sql")
              .setSeparator("/;")
              .build();
  }

  public static void main(String[] args) {
      AnnotationConfigApplicationContext context =
              new AnnotationConfigApplicationContext(AppConfig.class);
      context.getBean(ClientBean.class).findSum();
  }
}

Output

#update-count-1=0
theSum=25

Example Project

Dependencies and Technologies Used:

• spring-context 4.2.3.RELEASE: Spring Context.
• spring-jdbc 4.2.3.RELEASE: Spring JDBC.
• hsqldb 2.4.0: HSQLDB - Lightweight 100% Java SQL Database Engine.
• JDK 1.8
• Maven 3.3.9