Home  >  Spring Core

How to Call Stored Function in Spring JDBC

By Arvind Rai, 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);
	}
}   
 
Stored Function: get_company_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
 
Table Schema: company
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`)
)
 
Download Source Code for Complete Example
how-to-call-stored-function-in-spring-jdbc.zip
POSTED BY
ARVIND RAI
ARVIND RAI
FIND MORE TUTORILAS






©2019 concretepage.com | Privacy Policy | Contact Us