Spring Boot JDBC - Calling Stored Procedure with SimpleJdbcCall

By Arvind Rai, January 30, 2023

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

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 meta-data processing is based on the Java DatabaseMetaData provided by the JDBC driver. The "auto-detection" can only be used for databases that are known to provide accurate meta-data, for example, Derby, MySQL, Microsoft SQL Server, Oracle, DB2, Sybase and PostgreSQL.
5. If the database does not support the auto-detection of meta-data, we need to explicitly declare all parameters. Even if the database supports auto-detection, we can declare all parameters explicitly.
6. We can turn off any meta-data processing if we want to use parameter names that do not match declared during the stored procedure compilation.

Calling Stored Procedure

We have created a stored procedure in MySQL.

CREATE PROCEDURE read_article ( 
  IN in_id INTEGER, 
  OUT out_title VARCHAR(100), 
  OUT out_category VARCHAR(100)
)
BEGIN 
  SELECT title, category
  INTO out_title, out_category
  FROM articles WHERE article_id = in_id;
END;

To run the stored procedure, we are using SimpleJdbcCall.

SimpleJdbcCall simpleJdbcCall = new SimpleJdbcCall(dataSource);
SqlParameterSource in = new MapSqlParameterSource().addValue("in_id", articleId);
Map<String, Object> out = simpleJdbcCall.withProcedureName("read_article").execute(in);
Article article = new Article();
article.setCategory(out.get("out_category").toString());
article.setTitle(out.get("out_title").toString());

In the above code,
1. Instantiate the SimpleJdbcCall using DataSource.
2. Assign stored procedure name using withProcedureName method.
3. Create SqlParameterSource with in parameters.
4. Call execute method and pass the SqlParameterSource that returns a Map with out values.
5. If we have database schema and catalog name, we can assign them as following.

this.simpleJdbcCall 
 .withSchemaName(schemaName)
 .withCatalogName(catalogName)
 .withProcedureName(procedureName);

6. The SimpleJdbcCall can also be instantiated using JdbcTemplate.

@Autowired
public void setDataSource(JdbcTemplate jdbcTemplate) {
    this.simpleJdbcCall = new SimpleJdbcCall(jdbcTemplate)
	   .withProcedureName("read_article");
}

SimpleJdbcCall Methods for Stored Procedure

1. execute

Map<String,Object> execute(Object... args)
Map<String,Object> execute(Map<String,?> args)
Map<String,Object> execute(SqlParameterSource parameterSource)

Execute the stored procedure and return a map of output params, keyed by name as in parameter declarations.
The parameter args contains the in parameter values to be used in the call.
In case of execute(Object... args), parameter values must be provided in the same order as the parameters are defined for the stored procedure.
The parameter as Map is the key/value where key is the in parameter name.
The SqlParameterSource offers parameter values for named SQL parameters.
2. executeObject

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

Execute the stored procedure and return the single out parameter as an Object of the specified return type.
3.

SimpleJdbcCall declareParameters(SqlParameter... sqlParameters)

Specify one or more SqlParameter.
4.

SimpleJdbcCall withCatalogName(String catalogName)

Specify the name of the catalog that contains the stored procedure.
5.

SimpleJdbcCall withSchemaName(String schemaName)

Specify the name of the schema that contains the stored procedure.
6.

SimpleJdbcCall withProcedureName(String procedureName)

Specify the procedure name to be used.
7.

SimpleJdbcCall withNamedBinding()

Indicates that parameters should be bound by name.
8.

SimpleJdbcCall withReturnValue()

Indicates the procedure's return value should be included in the results returned.
9.

SimpleJdbcCall withoutProcedureColumnMetaDataAccess()

Turn off any processing of parameter meta-data information obtained via JDBC.

Examples

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 used in Demo Project

CREATE TABLE `articles` (
	`article_id` INT(5) NOT NULL AUTO_INCREMENT,
	`title` VARCHAR(200) NOT NULL,
	`category` VARCHAR(100) NOT NULL,
	PRIMARY KEY (`article_id`)
)

CREATE PROCEDURE read_article ( 
  IN in_id INTEGER, 
  OUT out_title VARCHAR(100), 
  OUT out_category VARCHAR(100)
)
BEGIN 
  SELECT title, category
  INTO out_title, out_category
  FROM articles WHERE article_id = in_id;
END;


CREATE PROCEDURE add_article ( 
  IN in_title VARCHAR(100), 
  IN in_category VARCHAR(100)
)
BEGIN 
  INSERT INTO articles (title, category) values (in_title, in_category);
END;

3. DAO

@Transactional
@Repository
public class ArticleDAO implements IArticleDAO {
  @Autowired
  private JdbcTemplate jdbcTemplate;

  @Override
  public Article getArticleById(int articleId) {
	SimpleJdbcCall simpleJdbcCall = new SimpleJdbcCall(jdbcTemplate);
	SqlParameterSource in = new MapSqlParameterSource().addValue("in_id", articleId);
	Map<String, Object> out = simpleJdbcCall.withProcedureName("read_article").execute(in);
	Article article = new Article();
	article.setCategory(out.get("out_category").toString());
	article.setTitle(out.get("out_title").toString());
	article.setArticleId(articleId);
	return article;
  }
  @Override
  public void addArticle(Article article) {
	SimpleJdbcCall simpleJdbcCall = new SimpleJdbcCall(jdbcTemplate);
	SqlParameterSource in = new MapSqlParameterSource()
                .addValue("in_title", article.getTitle())
                .addValue("in_category", article.getCategory());
	simpleJdbcCall.withProcedureName("add_article").execute(in);
  }
}

Reference

Class SimpleJdbcCall

Download Complete Source Code

spring-boot-jdbc-call-stored-procedure-simplejdbccall.zip

POSTED BY

ARVIND RAI

LEARN MORE

JAVA SPRING BOOT ANGULAR Angular Material NGRX JAVASCRIPT ANDROID THYMELEAF FREEMARKER Hibernate Struts 2 Testing MAVEN APACHE API JACKSON API JUNIT 5