How to Call Stored Function in Spring JDBC

By Arvind Rai, March 02, 2023

On this page, we will learn to call stored function with SimpleJdbcCall in Spring JDBC application.

1. SimpleJdbcCall

1. A SimpleJdbcCall is a multithreaded, reusable object representing a call to a stored procedure or a stored function.
2. The SimpleJdbcCall needs stored procedure/function name and a Map containing the parameters to execute.
3. The names of the supplied parameters must match the in and out parameters declared in the stored procedure.
4. The stored function is executed using executeFunction() and withFunctionName() methods of SimpleJdbcCall class.

2. executeFunction() Method

The executeFunction() is a SimpleJdbcCall method.

<T> T executeFunction(Class<T> returnType, Object... args)
<T> T executeFunction(Class<T> returnType, Map<String,?> args)
<T> T executeFunction(Class<T> returnType, SqlParameterSource args)

The executeFunction() method executes the stored function and returns the results obtained as an Object of the specified return type.
Parameters:
The returnType is the type of the value to return.
The args is a Map or optional array containing the in parameter values to be used in the call.

Find the code snippet.

public String getCompnayName(int id) {
  SqlParameterSource in = new MapSqlParameterSource().addValue("in_id", id);
  String name = simpleJdbcCall.executeFunction(String.class, in);
  return name;
}

3. withFunctionName() Method

The withFunctionName() is a SimpleJdbcCall method.

SimpleJdbcCall withFunctionName(String functionName)

The withFunctionName specifies the function name to be used and returns the instance of this SimpleJdbcCall.
Parameters:
The functionName is the name of the stored function.

4. Complete Example using Spring Boot

1. Technologies Used
Find the technologies being used in our example.
1. Java 19
2. Spring Boot 3.0.2
3. MySQL 5.5

2. MySQL Database
Table Schema: company

// Create Table
CREATE TABLE `company` (
	`id` INT(5) NOT NULL,
	`name` VARCHAR(50) NULL DEFAULT NULL,
	PRIMARY KEY (`id`)
)  
// Insert Data
INSERT INTO `company` (`id`, `name`) VALUES (101, 'PQR Company');

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

3. application.properties

spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/concretepage
spring.datasource.username=root
spring.datasource.password=cp

4. Java Code

@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;
  }
}

Main.java

@SpringBootApplication
public class Main {
  public static void main(String[] args) {
	final ApplicationContext ctx = SpringApplication.run(Main.class, args);
	CompanyDao companyDao = ctx.getBean(CompanyDao.class);
	String name = companyDao.getCompnayName(101);
	System.out.println(name);
  }
}

Output