SQLException Cant find local placeholder mapping parameter

SQLException Cant find local placeholder mapping parameter

If you see exception while calling stored procedure:
java.sql.SQLException: Can’t find local placeholder mapping for parameter named “departId”

java.sql.SQLException: Can't find local placeholder mapping for parameter named "departId".
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1086)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:975)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:920)
	at com.mysql.jdbc.CallableStatement.getNamedParamIndex(CallableStatement.java:1564)
	at com.mysql.jdbc.CallableStatement.setString(CallableStatement.java:2440)
	at com.javahonk.dao.PersonDAOImpl.returnMultipleResultSet(PersonDAOImpl.java:75)
	at com.javahonk.services.PersonServiceImpl.returnMultipleResultSet(PersonServiceImpl.java:41)
	at com.javahonk.controller.SpringMVCController.returnMultipleResultSet(SpringMVCController.java:66)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:606)
	at org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:215)
	at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:132)
	at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:104)
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandleMethod(RequestMappingHandlerAdapter.java:749)
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:689)
	at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:83)
	at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:938)
	at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:870)
	at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:961)
	at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:852)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:620)
	at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:837)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
	at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:220)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:122)
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:501)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
	at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:950)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408)
	at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1040)
	at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:607)
	at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:314)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
	at java.lang.Thread.run(Thread.java:745)
DEBUG DispatcherServlet - Null ModelAndView returned to DispatcherServlet with name 'dispatcher': assuming HandlerAdapter completed request handling
DEBUG DispatcherServlet - Successfully completed request

Solution: It means your stored procedure is expecting parameter and during making call to its parameter is missing

• Example stored procedure which takes one parameter:

DELIMITER $

CREATE DEFINER=`root`@`localhost` PROCEDURE `SelectMultiResultSet`( in departId varchar(25))
BEGIN
Select * from person;
Select * from department where DepartmentID = departId;
Select * from employee;

END

• If you will call as below you will get exception:

SqlParameter departId = new SqlParameter(Types.VARCHAR);
		
		List<SqlParameter> paramList = new ArrayList<SqlParameter>();
		paramList.add(departId);	

		final String procedureCall = "{call SelectMultiResultSet}";
		Map<String, Object> resultMap = jdbcTemplate.call(new CallableStatementCreator() {

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

						CallableStatement callableStatement = connection.prepareCall(procedureCall);
						callableStatement.setString(1, "31");
						return callableStatement;

					}
				}, paramList);
		System.out.println(resultMap);

• As you see above while calling stored procedure parameter is missing (

final String procedureCall = "{call SelectMultiResultSet}";

• To fix it please see below:

SqlParameter departId = new SqlParameter(Types.VARCHAR);
		
		List<SqlParameter> paramList = new ArrayList<SqlParameter>();
		paramList.add(departId);	

		final String procedureCall = "{call SelectMultiResultSet(?)}";
		Map<String, Object> resultMap = jdbcTemplate.call(new CallableStatementCreator() {

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

						CallableStatement callableStatement = connection.prepareCall(procedureCall);
						callableStatement.setString(1, "31");
						return callableStatement;

					}
				}, paramList);
		System.out.println(resultMap);

• For more informaton calling stored procedure in Spring please use this link