In this Spring JDBC tutorials, I will share with you how to use the SimpleJdbcCall class in Spring to call a stored procedure and execute a function in database. You will see how Spring simplifies the code you need to write, as compared with using plain JDBC code to call stored procedure.

Suppose that we have a data source declared as follows:

DriverManagerDataSource dataSource = new DriverManagerDataSource();;

dataSource.setDriverClassName("com.mysql.jdbc.Driver");
dataSource.setUrl("jdbc:mysql://localhost:3306/dbname");
dataSource.setUsername("root");
dataSource.setPassword("password");

Basically, here are the steps to call a database stored procedure using SimpleJdbcCall in Spring:

 

1. Create a new instance of SimpleJdbcCall and specify the name of the procedure:

SimpleJdbcCall actor = new SimpleJdbcCall(dataSource).withProcedureName("name");

2. Specify some IN parameters if required by the procedure:

SqlParameterSource params = new MapSqlParameterSource();
params.addValue("in_param_1", "value1")
	  .addValue("in_param_2", "value2");

3. Execute the procedure and get the values of the OUT parameters into a Map:

Map<String, Object> out = actor.execute(params);

4. Read out the values returned from the procedure:

String value1 = (String) out.get("out_param_1");
Integer value2 = (Integer) out.get("out_param_2");

Note the names of the IN and OUT parameters must match the names declared by the stored procedure/function.

Now, let’s see some concrete examples.

 

1. Use SimpleJdbcCall to call a stored procedure with IN and OUT parameters

Suppose that we have a stored procedure named get_contact in MySQL database declared as follows:

CREATE PROCEDURE `get_contact`(IN contact_id INTEGER,
	OUT _name varchar(45),
    OUT _email varchar(45),
    OUT _address varchar(45),
    OUT _phone varchar(45))
BEGIN
	SELECT name, email, address, telephone
    INTO _name, _email, _address, _phone
    FROM Contact WHERE id = contact_id;
END

Note that this stored procedure has an IN parameter contact_id and 4 OUT parameters _name, _email, _address and _phone. The following code snippet illustrates how to use SimpleJdbCall to call that stored procedure:

int contactId = 10;

SimpleJdbcCall actor = new SimpleJdbcCall(dataSource).withProcedureName("get_contact");
SqlParameterSource inParams = new MapSqlParameterSource().addValue("contact_id", contactId);

Map<String, Object> outParams = actor.execute(inParams);

String name = (String) outParams.get("_name");
String email = (String) outParams.get("_email");
String address = (String) outParams.get("_address");
String phone = (String) outParams.get("_phone");

System.out.println(name + ", " + email + ", " + address + ", " + phone);

If there’s only one IN parameter, you can pass the value directly to the execute() method like this:

Map<String, Object> out = simpleJdbcCall.execute(10);

 

2. Use SimpleJdbcCall to call a stored procedure that returns a result set

Suppose that we have a stored procedure that returns rows from a table like this:

CREATE PROCEDURE `list_contact`()
BEGIN
	SELECT * FROM Contact ORDER BY name ASC;
END

The following code example shows you how to call this stored procedure and read the result:

SimpleJdbcCall procedureActor = new SimpleJdbcCall(dataSource)
		.withProcedureName("list_contact")
		.returningResultSet("contacts", new RowMapper<Contact>() {

			@Override
			public Contact mapRow(ResultSet rs, int rowNum) throws SQLException {
				Contact contact = new Contact();
				
				contact.setId(rs.getInt("id"));
				contact.setName(rs.getString("name"));
				contact.setEmail(rs.getString("email"));
				contact.setAddress(rs.getString("address"));
				contact.setTelephone(rs.getString("telephone"));
				
				return contact;
			}
		});

Map<String, Object> out = procedureActor.execute();

List<Contact> listContacts = (List<Contact>) out.get("contacts");

As you can see, we call the returningResultSet() method with two arguments: the first one is for the name of a key in the returning Map which we will use to read the result. And the second one is a RowMapper to read the result set directly.

If you have a domain class containing the field names exactly match the column names in the database table, you can use the BeanPropertyRowMapper class to simplify the code as follows:

SimpleJdbcCall procedureActor = new SimpleJdbcCall(dataSource)
		.withProcedureName("list_contact")
		.returningResultSet("contacts", BeanPropertyRowMapper.newInstance(Contact.class));

Map<String, Object> out = procedureActor.execute();
List<Contact> listContacts = (List<Contact>) out.get("contacts");

Code of the Contact class is as follows:

public class Contact {
	private Integer id;
	private String name;
	private String email;
	private String address;
	private String telephone;

	
	// getters...
	// setters...
	
}

 

3. Use SimpleJdbcCall to execute a function

Suppose that we have a function in the database declared as follows:

CREATE FUNCTION `calculate_book_rating`(book_title varchar(128)) RETURNS double
    READS SQL DATA
BEGIN
	DECLARE out_value DOUBLE;
	SELECT AVG(r.rating) AS AvgRating FROM Review r JOIN Book b 
		ON r.book_id = b.book_id AND b.title = book_title
        INTO out_value;
RETURN out_value;
END

This function has one parameter named book_title and it returns a double value. The following code example shows how to execute this function using SimpleJdbcCall in Spring:

SimpleJdbcCall procedureActor = new SimpleJdbcCall(dataSource)
		.withFunctionName("calculate_book_rating");

String bookTitle = "Effective Java (3rd Edition)";

Double rating = procedureActor.executeFunction(Double.class, bookTitle);

That’s how to use the SimpleJdbcCall class in Spring to call a stored procedure and execute a function.

 

References:

• Simplifying JDBC operations with the SimpleJdbc classes
• SimpleJdbcCall Javadoc

 

Related Spring and Database Tutorials:

• Spring JdbcTemplate Simple Example
• Spring MVC with JdbcTemplate Tutorial
• Spring SimpleJdbcInsert Examples
• How to configure Spring MVC JdbcTemplate with JNDI Data Source in Tomcat
• Spring and Hibernate Integration Tutorial (XML Configuration)
• Understand Spring Data JPA with Simple Example
  
• Spring MVC + Spring Data JPA + Hibernate - CRUD Example

 

Other Spring Tutorials:

• Understand the core of Spring framework
• Understand Spring MVC
• Understand Spring AOP
• Spring MVC beginner tutorial with Spring Tool Suite IDE
• Spring MVC Form Handling Tutorial
• Spring MVC Form Validation Tutorial
• 14 Tips for Writing Spring MVC Controller
• Spring Web MVC Security Basic Example (XML Configuration)