Hello all,

I have an spring MVC app, that connects to my backend MySQL DB. I have my data source configured properly in my spring bean config XML file, as follow:

    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver" />
        <property name="url" value="jdbc:mysql://localhost:3406/mydb" />
        <property name="username" value="root" />
        <property name="password" value="passwd" />
    </bean>

    <bean id="dbManagerBean" class="com.my.project.MyDbManager">
        <constructor-arg ref="dataSource"/>
    </bean>

    <bean id="bookBean" class="com.myProject.service.Books">
          <constructor-arg ref="dbManagerBean" />
    </bean>

The ^ above dependency injection is working correctly, as I can see the sysout print after my injection. However, when I attempt to use my JdbcTemplate.query() method, as follow, then I get the below error:

@Autowired
MyDataBaseManager dbManager = null;
JdbcTemplate jt = null;

public DAOBook (MyDataBaseManager dbManager) {
    jt = dbManager.getJdbcTemplate();
}

public ArrayList<Books> getBooks() {
    ArrayList<Books> books = new ArrayList<Books>();
    String sql = "SELECT * FROM BOOK_TBL";
    // **** The following jt.query() throws the error.
    jt.query(sql, new RowMapper<Books>() {       
        @Override
        public Books mapRow(ResultSet rs, int rowNum) throws SQLException {
            Books books = new Books();
            books.setTitle(rs.getString("TITLE"));
            books.setAuthor(rs.getString("AUTHOR"));
            books.setEdition(rs.getString("EDITION"));
            return books;
        }
    });
    return bookInfoList;
}

ERROR:

org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
    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

org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
    org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:80)
    org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:394)
    org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:474)
    org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:484)
    com.myProject.service.Books.getBooks(Books.java:67);   <-------- This is the line in my code snipt
    sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    java.lang.reflect.Method.invoke(Unknown Source)
    org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:221)
    org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:136)
    org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:114)
    org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:827)
    org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:738)
    org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85)
    org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:963)
    org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:897)
    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)
root cause

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)

NOTE: My Datasource configuration are correct, because I can use the same info and can connect to my MySQL db, using mysql command line utility.

Any help where I am going wrong?

Quick questions...

MyDataBaseManager dbManager = null;
JdbcTemplate jt = null;

public DAOBook (MyDataBaseManager dbManager) {
    jt = dbManager.getJdbcTemplate();
}

Do you intend to accept MyDataBaseManager in your constructor? How did you implement the method getJdbcTemplate() and how the method deals with exception? Why do you need another member variable for dbManager? Also would you want to check if the argument is valid (not null) to be used as is?

PS: You could check a solution on StackOverflow

Dear Taywin,

Thank you for your reply.

You are correct! I didn't need my member variable "MyDatabaseManager". I realized that too, and deleted it (line #1).

The following is my implementation of MyDatabaseManager class.
I am not sure if I need to get a connection, since I am using JdbcTemplate, which can also execute query, i.e. JdbcTemplate.query(...).

public class MyDataBaseManager {
    private JdbcTemplate jdbcTemplate;

    public MyDataBaseManager (DataSource dataSource) {
        jdbcTemplate = new JdbcTemplate(dataSource);
        System.out.println("JdbcTemplate obtained: DataSource is :=" + dataSource.toString());
    }

    public JdbcTemplate getJdbcTemplate() {
        return this.jdbcTemplate;
    }
}

I don't have time to take a look at your question right now. Please take a look at how JdbcTemplate.query() works here.

Do you think it has to do with usage of JdbcTemplate.query() method? or something is wrong with connectivity to my MySQL DB?