Spring JDBC - Can't connect to database

Question

This seems like a strange issue to me. Even though it looks like the jdbcTemplate configured properly, jdbcTemplate.queryForObject fails with

o.a.tomcat.jdbc.pool.ConnectionPool      : Unable to create initial connections of pool.
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Access denied for user ''@'localhost' to database

Right before queryForObject is called, I'm printing jdbcTemplate.getDataSource().toString(), which does show the username properly (like so:

...; url=jdbc:mysql://localhost/mydb; username=test; validationQuery=SELECT 1; ... ).

Code:

public class JdbcLookupDao implements UserDao, OrderDao {

    private final JdbcTemplate jdbcTemplate;

    public JdbcLookupDao(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    @Override
    public User findUser(String phone) {
        System.out.println(jdbcTemplate.getDataSource().toString());

        return jdbcTemplate.queryForObject( //this line throws the exception
                "SELECT * FROM users WHERE phonenumber = ?",
                new Object[]{phone},
                new UserMapper()
        );
    }
....
}

Spring config file:

...
       <bean id="dataSource" class="org.apache.commons.dbcp2.BasicDataSource" destroy-method="close">
              <property name="driverClassName" value="${spring.datasource.driver-class-name}" />
              <property name="url" value="${spring.datasource.url}"/>"
              <property name="username" value="${spring.datasource.username}"/>
              <property name="password" value="${spring.datasource.password}"/>
              <property name="validationQuery" value="${spring.datasource.validation-query}"/>
       </bean>

       <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
              <property name="dataSource" ref="dataSource"/>
       </bean>

       <context:property-placeholder location="application.properties"/>
...

Exception and stack trace:

2015-08-15 23:00:16.123 ERROR 45791 --- [nio-8080-exec-1] o.a.tomcat.jdbc.pool.ConnectionPool      : Unable to create initial connections of pool.

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Access denied for user ''@'localhost' to database 'lookup'
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:422)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:400)
    at com.mysql.jdbc.Util.getInstance(Util.java:383)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:980)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3847)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3783)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:871)
    at com.mysql.jdbc.MysqlIO.proceedHandshakeWithPluggableAuthentication(MysqlIO.java:1665)
    at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1207)
    at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2249)
    at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2280)
    at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2079)
    at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:794)
    at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:44)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:422)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:400)
    at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:399)
    at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:325)
    at org.apache.tomcat.jdbc.pool.PooledConnection.connectUsingDriver(PooledConnection.java:307)
    at org.apache.tomcat.jdbc.pool.PooledConnection.connect(PooledConnection.java:200)
    at org.apache.tomcat.jdbc.pool.ConnectionPool.createConnection(ConnectionPool.java:699)
    at org.apache.tomcat.jdbc.pool.ConnectionPool.borrowConnection(ConnectionPool.java:633)
    at org.apache.tomcat.jdbc.pool.ConnectionPool.init(ConnectionPool.java:484)
    at org.apache.tomcat.jdbc.pool.ConnectionPool.<init>(ConnectionPool.java:142)
    at org.apache.tomcat.jdbc.pool.DataSourceProxy.pCreatePool(DataSourceProxy.java:115)
    at org.apache.tomcat.jdbc.pool.DataSourceProxy.createPool(DataSourceProxy.java:102)
    at org.apache.tomcat.jdbc.pool.DataSourceProxy.getConnection(DataSourceProxy.java:126)
    at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:111)
    at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:77)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:630)
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:695)
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:727)
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:737)
    at org.springframework.jdbc.core.JdbcTemplate.queryForObject(JdbcTemplate.java:805)
    at com.lookup.aws.dao.JdbcLookupDao.findUser(JdbcLookupDao.java:36)
    at com.lookup.aws.TestController.getUser(TestController.java:35)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:497)
    at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:221)
    at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:137)
    at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:110)
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandleMethod(RequestMappingHandlerAdapter.java:776)
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:705)
    at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85)
    at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:959)
    at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:893)
    at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:966)
    at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:857)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:622)
    at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:842)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:291)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.springframework.web.filter.HiddenHttpMethodFilter.doFilterInternal(HiddenHttpMethodFilter.java:77)
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:85)
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:219)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:106)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:142)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:518)
    at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1091)
    at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:668)
    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1521)
    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1478)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
    at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
    at java.lang.Thread.run(Thread.java:745)

I hope someone can help me debug. Thanks!

Answer 1

Your spring config has a missplaced double qoute

<bean id="dataSource" class="org.apache.commons.dbcp2.BasicDataSource" destroy-method="close">
          <property name="driverClassName" value="${spring.datasource.driver-class-name}" />
          <property name="url" value="${spring.datasource.url}"/>"
          <property name="username" value="${spring.datasource.username}"/>
          <property name="password" value="${spring.datasource.password}"/>
          <property name="validationQuery" value="${spring.datasource.validation-query}"/>
   </bean>

At the end of the 'url' property there is a extraneous ", remove that, and then spring will correctly pick up the username and inject it into the dataSource.

i.e.

<bean id="dataSource" class="org.apache.commons.dbcp2.BasicDataSource" destroy-method="close">
          <property name="driverClassName" value="${spring.datasource.driver-class-name}" />
          <property name="url" value="${spring.datasource.url}"/>
          <property name="username" value="${spring.datasource.username}"/>
          <property name="password" value="${spring.datasource.password}"/>
          <property name="validationQuery" value="${spring.datasource.validation-query}"/>
   </bean>

Answer 2

Check the Database defined in the properties exists on the MySql or check the url in properties file. This error comes when :-

Connection con = DriverManager.getConnection(url);

because at this point of time the connection to the database is getting created using the url which has username, password and database or schema(Oracle). So you need to check the url properly. Either some issue with the url or with the Database name if it exists on MySql server. It might be that you have changed your username on the Database and still using old username while connecting from the code.

Answer 3

It looks like a DB access permission issue. you need to give the DB access permission for user in MYSQL Admin.

Answer 4

The problem was with permissions set in MySQL. The username used in the code didn't have access to the db.

I suppose the error message Access denied for ''@'localhost' is misleading -- it would've made sense if the actual username was shown instead of ''.