Close

Spring - Calling a stored function using SimpleJdbcCall

[Updated: Sep 15, 2017, Created: Sep 15, 2017]

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
Wed Sep 27 02:07:47 CDT 2017 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
Wed Sep 27 02:07:47 CDT 2017 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.

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

SimpleJdbcCall for Stored Procedure Example Select All Download
  • simple-jdbc-function-call
    • src
      • main
        • java
          • com
            • logicbig
              • example
        • resources

See Also