How to Call Stored Function in Spring JDBC
November 09, 2013
In this page, we will learn how to call a stored function with spring JDBC. SimpleJdbcCall has a method as executeFunction() that takes return type and object of SqlParameterSource as arguments. SqlParameterSource takes in IN parameter of stored function. While creating the instance of SimpleJdbcCall, we need to pass JdbcTemplate instance and then set stored function name by calling method withFunctionName(). Find the example below.
CompanyDao.java
package com.concretepage.dao;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcCall;
import org.springframework.stereotype.Repository;
@Repository
public class CompanyDao {
private SimpleJdbcCall simpleJdbcCall;
@Autowired
public void setDataSource(DataSource dataSource) {
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
jdbcTemplate.setResultsMapCaseInsensitive(true);
this.simpleJdbcCall = new SimpleJdbcCall(jdbcTemplate).withFunctionName("get_company_name");
}
public String getCompnayName(int id){
SqlParameterSource in = new MapSqlParameterSource().addValue("in_id", id);
String name = simpleJdbcCall.executeFunction(String.class, in);
return name;
}
}
SpringTest.java
package com.concretepage;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import com.concretepage.dao.CompanyDao;
public class SpringTest {
public static void main(String[] args) {
ApplicationContext context = new ClassPathXmlApplicationContext("spring.xml");
CompanyDao companyDao = (CompanyDao)context.getBean("companyDao");
String name = companyDao.getCompnayName(1);
System.out.println(name);
}
}
CREATE FUNCTION get_company_name (in_id INTEGER) RETURNS VARCHAR(50) READS SQL DATA BEGIN declare out_name varchar(50); select name into out_name from company where id = in_id; RETURN out_name; END
CREATE TABLE `company` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `name` VARCHAR(50) NULL DEFAULT NULL, `location` VARCHAR(50) NULL DEFAULT NULL, `no_of_emp` INT(11) NULL DEFAULT NULL, PRIMARY KEY (`id`) )
how-to-call-stored-function-in-spring-jdbc.zip
