Spring JdbcTemplate – Calling Procedure

Procedure

DROP PROCEDURE IF EXISTS pr_check_menu_availability;

DELIMITER $$

CREATE PROCEDURE pr_check_menu_availability(IN in_name VARCHAR(20), OUT out_status VARCHAR(20))
BEGIN
 SET out_status='Not Available';
 IF in_name IN(SELECT NAME FROM menu)
 THEN 
  SET out_status='Available';
 END IF;
 END $$
 
 DELIMITER;
 
 CALL pr_check_menu_availability('Idli',@status);
 
 SELECT @status;

DAO

public String checkMenuAvailability(String name) {
		SimpleJdbcCall call = new SimpleJdbcCall(jdbcTemplate).withProcedureName("pr_check_menu_availability")
				.declareParameters(new SqlParameter("in_name", Types.VARCHAR),
						new SqlOutParameter("out_status", Types.VARCHAR));
		call.setAccessCallParameterMetaData(false);
		SqlParameterSource in = new MapSqlParameterSource().addValue("in_name", name);
		Map<String, Object> execute = call.execute(in);
		String status = (String) execute.get("out_status");
		return status;

	}

TestDAO

MenuDAO dao = new MenuDAO();
String status = dao.checkMenuAvailability("idli");
System.out.println(status);
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s