handling simpleJdbcTemplate error

[Aaron Jeffries]

Hello everyone! I have posted a working method below that uses a SimpleJdbcTemplate to retrieve an object from the database (below). What I need to learn is how to handle situations where there is no match found.

When I use SimpleJdbcTemplate to return a list, it will return the list empty if no match is found and I can redirect the user. When SimpleJdbcTemplate searches for a single object I get a stack trace (also below).

How do you redirect the user to a "item not found" page in this case? Thanks for your help.

      private SimpleJdbcTemplate simpleJdbcTemplate = DbUtils.getSimpleJdbcTemplate();

	public Drug getDrugByName(String name) {
		
			String sql = "SELECT P_id, name, classOfDrug, whyIsThisMedicationPrescribed, otherBrands, sideEffects, warnings, treats FROM drug where name=?";
			ParameterizedRowMapper<Drug> mapper = new ParameterizedRowMapper<Drug>() {
		        public Drug mapRow(ResultSet rs, int rowNum) throws SQLException {
		        	int id = rs.getInt("P_id");
		        	String drugName = rs.getString("name");
		        	String classOfDrug = rs.getString("classOfDrug");
		        	String whyIsThisMedicationPrescribed = rs.getString("whyIsThisMedicationPrescribed");
		        	String otherBrands = rs.getString("otherBrands");
		        	String sideEffects = rs.getString("sideEffects");
		        	String warnings = rs.getString("warnings");
		        	String treats = rs.getString("treats");
			
		        	Drug drug = new Drug (id, drugName, classOfDrug, whyIsThisMedicationPrescribed, otherBrands, sideEffects, warnings, treats);
		        	return drug;
		        }
			};
			
			return simpleJdbcTemplate.queryForObject(sql, mapper, name);
			
	}  

HTTP ERROR 500

Problem accessing /uRateDrugs-webapp/searchResultsByDrugName.html. Reason:

Incorrect result size: expected 1, actual 0

Caused by:

org.springframework.dao.EmptyResultDataAccessException: Incorrect result size: expected 1, actual 0
at org.springframework.dao.support.DataAccessUtils.requiredSingleResult(DataAccessUtils.java:71)
at org.springframework.jdbc.core.JdbcTemplate.queryForObject(JdbcTemplate.java:722)
at org.springframework.jdbc.core.simple.SimpleJdbcTemplate.queryForObject(SimpleJdbcTemplate.java:169)
at com.acwebsitedesign.uRateDrugs.domain.DrugRepositoryImpl.getDrugByName(DrugRepositoryImpl.java:37)
at com.acwebsitedesign.uRateDrugs.domain.DrugServiceImpl.getDrugByName(DrugServiceImpl.java:14)
at com.acwebsitedesign.uRateDrugs.web.SearchDrugsController.searchByResultsDrugName(SearchDrugsController.java:33)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.springframework.web.bind.annotation.support.HandlerMethodInvoker.doInvokeMethod(HandlerMethodInvoker.java:421)
at org.springframework.web.bind.annotation.support.HandlerMethodInvoker.invokeHandlerMethod(HandlerMethodInvoker.java:136)
at org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.invokeHandlerMethod(AnnotationMethodHandlerAdapter.java:326)
at org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.handle(AnnotationMethodHandlerAdapter.java:313)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:875)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:807)
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:571)
at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:511)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:820)
at org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:502)
at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:390)
at org.mortbay.jetty.security.SecurityHandler.handle(SecurityHandler.java:216)
at org.mortbay.jetty.servlet.SessionHandler.handle(SessionHandler.java:182)
at org.mortbay.jetty.handler.ContextHandler.handle(ContextHandler.java:765)
at org.mortbay.jetty.webapp.WebAppContext.handle(WebAppContext.java:418)
at org.mortbay.jetty.handler.ContextHandlerCollection.handle(ContextHandlerCollection.java:230)
at org.mortbay.jetty.handler.HandlerCollection.handle(HandlerCollection.java:114)
at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152)
at org.mortbay.jetty.Server.handle(Server.java:326)
at org.mortbay.jetty.HttpConnection.handleRequest(HttpConnection.java:536)
at org.mortbay.jetty.HttpConnection$RequestHandler.content(HttpConnection.java:928)
at org.mortbay.jetty.HttpParser.parseNext(HttpParser.java:747)
at org.mortbay.jetty.HttpParser.parseAvailable(HttpParser.java:218)
at org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:405)
at org.mortbay.io.nio.SelectChannelEndPoint.run(SelectChannelEndPoint.java:409)
at org.mortbay.thread.QueuedThreadPool$PoolThread.run(QueuedThreadPool.java:582)

[Mark Spritzler]

Simple you wrap your query call in a try catch block and catch EmptyResultDataAccessException

in the catch what we do is just return null;

Mark

[Aaron Jeffries]

Thanks Mark.

[Kamala Boddapati]

Thanks, Mark. that helped my issue.

[vijay satlawar]

it works perfect .

But you should only catch 'EmptyResultDataAccessException' . This exception occurs only when there are no results

..Vijay Satlawar

[Bill Gorder]

queryForObject was designed to enforce this. It is assumed that if no results are returned there is a problem. If not finding an object is not an exceptional case and you don't like catching the exception and returning null, consider using query instead.