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();
}
} Output25 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 ProjectDependencies 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
|