How to Call Stored Function in Spring JDBC
March 02, 2023
On this page, we will learn to call stored function with SimpleJdbcCall
in Spring JDBC application.
1. SimpleJdbcCall
1. ASimpleJdbcCall
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
TheexecuteFunction()
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)
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
ThewithFunctionName()
is a SimpleJdbcCall
method.
SimpleJdbcCall withFunctionName(String functionName)
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 UsedFind 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');
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; } }
@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); } }
PQR Company