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
