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