Spring SimpleJdbcCall Examples

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)

About the Author:

Nam Ha Minh is certified Java programmer (SCJP and SCWCD). He began programming with Java back in the days of Java 1.4 and has been passionate about it ever since. You can connect with him on Facebook and watch his Java videos on YouTube.