HomeSpring BootJdbcTemplate - Call Stored Procedures

JdbcTemplate – Call Stored Procedures

September 24, 2022

This guide demonstrates several examples on how to call Stored Procedures/Functions using JdbcTemplate and SimpleJdbcCall in Spring/Spring Boot applications.

1. Calling Stored Procedure using JdbcTemplate

1.1. Simple Mysql Stored procedure to get search results for users by name

DROP procedure IF EXISTS `search_users_proc`$

CREATE PROCEDURE `search_users_proc` (IN name VARCHAR(50))
BEGIN
    SELECT *
    FROM USER
    WHERE username LIKE CONCAT('%', name , '%');
END$

1.2. Calling Stored Procedure using Spring JdbcTemplate.

	@Autowired
	private JdbcTemplate jdbcTemplate;

	/* Calling Stored Procedure using JdbcTemplate */
	public Map<String, Object> searchUserByName(String searchTerm) {

		List<SqlParameter> parameters = Arrays.asList(new SqlParameter(Types.NVARCHAR));
		
		return jdbcTemplate.call(new CallableStatementCreator() {

			@Override
			public CallableStatement createCallableStatement(Connection con) throws SQLException {

				CallableStatement cs = con.prepareCall("{call search_users_proc(?)}");
				cs.setString(1, searchTerm);
				return cs;
			}
		}, parameters);
	}

1.3. Process Results and Test

	@Autowired
	private UserJdbcTemplate jdbcTemplate;

	@Test
	@DisplayName("JdbcTemplate Stored Procedure Test ")
	void searchUserTest() {

		Map<String, Object> out = jdbcTemplate.searchUserByName("pet");
	        // #result-set-1
		List<User> users = mapUsers(out, 1);
		
		assertTrue(users.size() == 4);
	}

	public List<User> mapUsers(Map<String, Object> out, int resultSetPosition) {
		List<User> users = new ArrayList<User>();
		List<Map<String, Object>> results = (List<Map<String, Object>>) out.get("#result-set-" + resultSetPosition);

		results.forEach(u -> {
			User user = new User();
			user.setId((Long) u.get("Id"));
			user.setUserName((String) u.get("USERNAME"));
			user.setPassword((String) u.get("PASSWORD"));
			user.setCreatedTime((Date) u.get("CREATEDTIME"));
			if (u.get("UPDATEDTIME") != null)
				user.setCreatedTime((Date) u.get("UPDATEDTIME"));
			user.setUserType(UserType.valueOf((String) u.get("USERTYPE")));
			user.setDateofBirth((Date) u.get("DATEOFBIRTH"));

			users.add(user);
		});

		return users;
	}

2. Calling Stored Procedure using SimpleJdbcCall

2.1. If you use SimpleJdbcCall, “IN” and “OUT” parameters automatically detected for Derby, MySQL, Microsoft SQL Server, Oracle, DB2, Sybase and PostgreSQL databases. For any other databases you are required to declare all parameters explicitly. Following is the simple stored procedure to get search count in out parameter and search results.

/* Stored procedure to get search count for paged users by name */
DROP procedure IF EXISTS `count_search_users_proc`$

CREATE PROCEDURE `count_search_users_proc` (
   IN  order_by VARCHAR(50),
   IN  direction VARCHAR(4),
   IN  limit_ INT,
   IN  offset_ INT,
   OUT total INT
)
BEGIN
  SELECT COUNT(id)
  INTO total
  FROM USER;
  SELECT * from USER ORDER BY CONCAT(order_by,' ', direction) LIMIT limit_ OFFSET offset_;
END$

2.2. Following is a simple example to call Stored Procedure using Spring SimpleJdbcCall. To initialize SimpleJdbcCall, you need to provide JdbcTemplate or DataSource instance. Since IN and OUT parameters auto detected by SimpleJdbcCall , declaring parameters is optional, so that commented out in following program.

	private SimpleJdbcCall simpleJdbcCall;

	/* Calling Stored Procedure using SimpleJdbcCall */
	public Map<String, Object> searchUsersWithCount(String by, String direction, int size, int offset) {

		simpleJdbcCall = new SimpleJdbcCall(jdbcTemplate)
				.withProcedureName("count_search_users_proc");
				/*.declareParameters(
						new SqlParameter("order_by", Types.NVARCHAR),
						new SqlParameter("direction", Types.VARCHAR),
						new SqlParameter("limit_", Types.INTEGER),
						new SqlParameter("offset_", Types.INTEGER),
						new SqlOutParameter("total", Types.INTEGER));*/
		
		
		Map<String, Object> out = simpleJdbcCall.execute(
				new MapSqlParameterSource("order_by", by)
				.addValue("direction", direction)
				.addValue("limit_", size)
				.addValue("offset_", offset));
     
		return out;
	}

2.3. Process Results and Test

	@Test
	@DisplayName("SimpleJdbcCall - Stored Procedure Test ")
	void countSearchUserTest() {

		Map<String, Object> out = jdbcTemplate.searchUsersWithCount("USERNAME", "DESC", 5, 0);
	        // #result-set-1
		List<User> users = mapUsers(out, 1);

		// out param
		int total = (Integer) out.get("total");

		assertTrue(users.size() == 5);
		assertTrue(total == 12);
	}

3. Stored Procedures with Multiple Result Sets

3.1. A simple Stored Procedure with multiple results and to demonstrate on how to get rows count for recently updated statements.

/* Stored procedure to get multple result sets */
DROP procedure IF EXISTS `search_weak_users_proc`$

CREATE PROCEDURE `search_weak_users_proc` (
   IN  user_type VARCHAR(50),
   OUT type_total INT
)
BEGIN

  SELECT COUNT(ID) INTO type_total from USER WHERE USERTYPE = user_type;

  SELECT * from USER 
  WHERE USERTYPE = user_type AND LENGTH(USERNAME) < 7;
  
  SELECT * from USER 
  WHERE USERTYPE = user_type AND LENGTH(PASSWORD) <= 8;
  
  UPDATE USER SET UPDATEDTIME = NOW() WHERE USERTYPE = user_type;
  
END$

3.2. A Spring SimplJdbcCall example to get multiple result sets on stored procedure call.

	/* Example to get Multiple Result sets on stored procedure call */
	public Map<String, Object> searchWeakUsers(String userType) {

		simpleJdbcCall = new SimpleJdbcCall(jdbcTemplate)
				.withProcedureName("search_weak_users_proc")
				.declareParameters(
						new SqlParameter("user_type", Types.VARCHAR),
						new SqlOutParameter("type_total", Types.INTEGER));
		
		
		Map<String, Object> out = simpleJdbcCall.execute(
				new MapSqlParameterSource("user_type", userType));
     
		return out;
	}

3.3. Process Results and Test

	@Test
	@DisplayName("Stored Procedure Multiple Result Sets Test")
	void searchWeakUserTest() {

		Map<String, Object> out = jdbcTemplate.searchWeakUsers(UserType.EMPLOYEE.toString());
		System.err.println(out);

		// #result-set-1
		List<User> usersHasWeakUsername = mapUsers(out, 1);
		// #result-set-2
		List<User> usersHasWeakPassword = mapUsers(out, 2);

		// out param
		int total = (Integer) out.get("type_total");

		// Last updated statements count
		int updatedCount = (Integer) out.get("#update-count-1");

		System.out.println(total);
	}

4. Calling Stored Function

4.1. A simple mysql Stored Function.

/* Stored Function Example */
DROP function IF EXISTS `user_has_strongpwd_fn`$

CREATE FUNCTION `user_has_strongpwd_fn` (u_id BIGINT) RETURNS tinyint(1)
BEGIN
  DECLARE length_pwd INT;
  
  SELECT LENGTH(PASSWORD) INTO length_pwd FROM USER WHERE ID = u_id;
  
  IF (length_pwd >= 7 && length_pwd < 15) THEN
     RETURN FALSE;
  ELSE
     RETURN TRUE;
  END IF;
END$

4.2. Calling Stored Function example using Spring SimpleJdbcCall. To call stored functions use .withFunctionName to provide stored function name.

	/* Example to call Stored Function  */
	public boolean hasUserStrongPassword(Long id) {

		simpleJdbcCall = new SimpleJdbcCall(jdbcTemplate)
				.withFunctionName("user_has_strongpwd_fn")
				.declareParameters(new SqlParameter("u_id", Types.BIGINT));
		
		boolean out = simpleJdbcCall.executeFunction(Boolean.class,
				new MapSqlParameterSource("u_id", id));
     
		return out;
	}

4.3. Process results and Test

	@Test
	@DisplayName("Stored Function Test")
	void userHasWeakPasswordTest() {

		boolean userHasWeakPass = jdbcTemplate.hasUserStrongPassword(Long.valueOf(1));

		assertTrue(!userHasWeakPass);
	}

5. Conclusion

We have covered several examples on how to call Stored Procedures/Functions using Spring SimpleJdbcCall and JdbcTemplate. SimpleJdbcCall is recommended to call stored procedures, it auto detects the IN and OUT parameters and provides cleaner way of registering procedure/function/schema/catalog names and parameters binding.

Checkout source code at GitHub.

Other Spring JDBC Examples :

6. References

Spring Boot