Unable to query a database through Spring 4 (HTTP 500 Request processing failure)

Question

Problem to solve: I am building a Spring MVC application and would like to interact with my database.

---

Approach: To start, I built a straightforward mapping which when called, prints out the number of items in a specified table within my database to the console.

My code is below:

@Controller
@RequestMapping("/databaseName")
public class DatabaseController {
    private JdbcTemplate jdbcTemplate;

    public void setDatabaseDataSource (DataSource dbDataSource) {
        this.jdbcTemplate = new JdbcTemplate(dbDataSource);
    }


    @RequestMapping(value = "/getSomeCountOfElementsInMyTable", method = RequestMethod.GET)
    public void getSomeTable() {
        System.out.println("SELECT count(*) FROM TEST_TABLE is now being executed...");
        int rowCount = this.jdbcTemplate.queryForObject("SELECT count(*) FROM TEST_TABLE", Integer.class);
        System.out.println(rowCount);
    }
}

beans.xml (Keep in mind that I changed up the database credentials for this post. Assume that they are correct.)

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:beans="http://www.springframework.org/schema/mvc"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc.xsd">



    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"></property>
    </bean>
    <bean id="DatabaseController" class="com.company.project.controller.DatabaseController"/>



    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="com.database.jdbc.Driver"/>
        <property name="url" value="some_url_here"/>
        <property name="username" value="user"/>
        <property name="password" value="pw"/>
    </bean>

</beans>

---

Issues faced based on approach: I receive the following error when I connect via Postman:

HTTP Status 500 - Request processing failed; nested exception is java.lang.NullPointerException

type Exception report

message Request processing failed; nested exception is java.lang.NullPointerException

description The server encountered an internal error that prevented it from fulfilling this request.

exception

org.springframework.web.util.NestedServletException: Request processing failed; nested exception is java.lang.NullPointerException
    org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:982)
    org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:861)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:620)
    org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
    org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
root cause

java.lang.NullPointerException
    com.company.project.controller.DatabaseController.getSomeTable(DatabaseController.java:44)
    sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    java.lang.reflect.Method.invoke(Method.java:497)
    org.springframework.web.bind.annotation.support.HandlerMethodInvoker.invokeHandlerMethod(HandlerMethodInvoker.java:178)
    org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.invokeHandlerMethod(AnnotationMethodHandlerAdapter.java:444)
    org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.handle(AnnotationMethodHandlerAdapter.java:432)
    org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:959)
    org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:893)
    org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:970)
    org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:861)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:620)
    org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
    org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
note The full stack trace of the root cause is available in the Apache Tomcat/7.0.52 logs.

Apache Tomcat/7.0.52

---

Additional relevant information:

1. I have tested my database connectivity in a separate, simple Java application, and I am able to get a successful connection and returned the correct result.
2. I added my database information to both the mvc-dispatcher-servlet.xml and beans.xml files in my WEB-INF folder.
3. I am not using an Oracle database, my goal is to connect to a Vertica database (I have explored the possibility of using Hibernate).

---

Question: How do I resolve the HTTP status 500 issue? It's saying that rowCount isn't appropriate to store the object but based on Spring's documentation, it is the right way to do it to my understanding. I have taken a look at a few similar questions asked (SO Question 1, SO Question 2, and CodeRanch Question 3). I do see references to a DAO but not understanding why it is needed either since it conflicts with my understanding of Spring's documentation.

Answer 1

You should set the datasource of your DatabaseController :

<bean id="DatabaseController" class="com.company.project.controller.DatabaseController">
  <property name="databaseDataSource" ref="dataSource"/>
</bean>

or just annotate your setter:

@Autowired
public void setDatabaseDataSource (DataSource dbDataSource) {
    this.jdbcTemplate = new JdbcTemplate(dbDataSource);
}

Answer 2

you have to inject the jdbcTemplate into your Controller.

Annotate it with @Autowired so that can Spring injects it.