Spring - Calling a stored function using SimpleJdbcCall

This example shows how to call a database function with SimpleJdbcCall .

We are going to use MySql database server as DataSource in this example. If you do not have MySql database server installed, follow this tutorial to download, install and getting started with MySql Workbench.

Example

Creating a database Function in MySql

Copy paste following function to MySql workbench and execute it.

src/main/resources/sum-function.sql

 CREATE FUNCTION GET_SUM(first_num INT, second_num INT)
 RETURNS INT
   return first_num + second_num;

Using SimpleJdbcCall

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

  public void findSum() {
      JdbcTemplate template = new JdbcTemplate(dataSource);
      SimpleJdbcCall call = new SimpleJdbcCall(template)
              .withFunctionName("GET_SUM");

      SqlParameterSource paramMap = new MapSqlParameterSource()
              .addValue("first_num", 5)
              .addValue("second_num", 20);

      Integer sum = call.executeFunction(Integer.class, paramMap);
      System.out.println(sum);
  }
}

Java Config

@Configuration
@ComponentScan
public class AppConfig {
  @Bean
  public DataSource dataSource() {
      DriverManagerDataSource ds = new DriverManagerDataSource();
      ds.setDriverClassName(com.mysql.jdbc.Driver.class.getName());
      ds.setUrl("jdbc:mysql://localhost:3306/my_schema");
      ds.setUsername("root");
      ds.setPassword("1234");
      return ds;
  }

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

Output

25

Example Project

Dependencies and Technologies Used:

• spring-context 4.2.3.RELEASE: Spring Context.
• spring-jdbc 4.2.3.RELEASE: Spring JDBC.
• mysql-connector-java 5.1.44: MySQL JDBC Type 4 driver.
• JDK 1.8
• Maven 3.3.9