Vaadin + SQL = SQLNonTransientConnectionException?

Hallo there dear Vaadin Board,
i recently started using the Framework and implemented an UI which was fun and also good looking and safed me a lot of work. Now i’m trying to connect my Application to a Database. I surfed the Net for so long and found so many topics on this, but nothing actually helped :frowning:

So i’ve got some Classes. The ConnectToServer-Class is the one which creates the Database Connection and can return it if asked.

public class ConnectToServer {
    //Database credentials
    private static final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";
    private static final String DB_IP = "jdbc:mysql://......";
    private static final String USER = "user";
    private static final String PASS = "password";
    private SimpleJDBCConnectionPool connection = null;
    
    public ConnectToServer() {
        
    }
    
    public void Connect() throws Exception{
        
            connection = new SimpleJDBCConnectionPool(JDBC_DRIVER,DB_IP,USER,PASS);
            System.out.println("Connected to database!");
            
        
    }
    
    public SimpleJDBCConnectionPool getConnection(){
        System.out.println("Here is your connection Object!");
        return this.connection;
    }

i then try to Access this new connection with another Class, especially with this one Method.

public SQLContainer getUserTable(String table) throws Exception{
        server = new ConnectToServer();
        try {
            server.Connect();
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        Connection conn = null;
        SQLContainer temp = null;
        SimpleJDBCConnectionPool serverconn= server.getConnection();
        System.out.println("serverobject handled");
        this.sqlquery = "SELECT * FROM " + table;
        
        System.out.println("Inside createMySQLContainer");
        conn = serverconn.reserveConnection();
        Statement stmt = conn.createStatement();
        rs = stmt.executeQuery(sqlquery);
    
        while (rs.next()) {
            String username = rs.getString("Username");
            String password = rs.getString("Passwort");
            int id = rs.getInt("UserID");
            boolean admin = rs.getBoolean("Admin");
            
            System.out.println("Username: " + username +
                               " Password: " + password +
                               " ID: " + id +
                               " Admin: " + admin);
            }
        server.getConnection().releaseConnection(conn);
        return temp;
[/code][code]

protected void init(VaadinRequest request){
        
        executeTest();

    }
    
    private void executeTest(){
        try {
            UserBase.getUserTable("User");
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

Its seems like the first Chunk of Code (ConnectToServer) is working properly, but the application always skips when breaks when using "conn = serverconn.reserveConnection()
and gives out a SQLNonTransientConnectionException, caused by an NullPointerException.

This gets me so confused ._.

I’m looking forward to any help on this topic but please dont suggest using JPA or SpringData, as i just started using Databases in Java i would like to
learn it from the beginning with an JDBC-Connection.

Thanks in advance!

Can you post the full stacktrace?

Hey Tom and thank you for your fast answer!

Tue Jun 21 17:35:39 CEST 2016 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
java.sql.SQLNonTransientConnectionException: Could not create connection to database server.
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:676)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:663)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:653)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:638)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:606)
    at com.mysql.cj.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:1857)
    at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:1673)
    at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:656)
    at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:349)
    at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:221)
    at java.sql.DriverManager.getConnection(DriverManager.java:664)
    at java.sql.DriverManager.getConnection(DriverManager.java:247)
    at com.vaadin.data.util.sqlcontainer.connection.SimpleJDBCConnectionPool.createConnection(SimpleJDBCConnectionPool.java:141)
    at com.vaadin.data.util.sqlcontainer.connection.SimpleJDBCConnectionPool.initializeConnections(SimpleJDBCConnectionPool.java:91)
    at com.vaadin.data.util.sqlcontainer.connection.SimpleJDBCConnectionPool.reserveConnection(SimpleJDBCConnectionPool.java:99)
    at de.hsb.mystocklist.UserManagement.getUserTable(UserManagement.java:52)
    at de.hsb.mystocklist.MyUI.executeTest(MyUI.java:55)
    at de.hsb.mystocklist.MyUI.init(MyUI.java:49)
    at com.vaadin.ui.UI.doInit(UI.java:639)
    at com.vaadin.server.communication.UIInitHandler.getBrowserDetailsUI(UIInitHandler.java:222)
    at com.vaadin.server.communication.UIInitHandler.synchronizedHandleRequest(UIInitHandler.java:74)
    at com.vaadin.server.SynchronizedRequestHandler.handleRequest(SynchronizedRequestHandler.java:41)
    at com.vaadin.server.VaadinService.handleRequest(VaadinService.java:1402)
    at com.vaadin.server.VaadinServlet.service(VaadinServlet.java:305)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)
    at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:769)
    at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:585)
    at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:143)
    at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:577)
    at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:223)
    at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1125)
    at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:515)
    at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:185)
    at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1059)
    at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141)
    at org.eclipse.jetty.server.handler.ContextHandlerCollection.handle(ContextHandlerCollection.java:215)
    at org.eclipse.jetty.server.handler.HandlerCollection.handle(HandlerCollection.java:110)
    at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:97)
    at org.eclipse.jetty.server.Server.handle(Server.java:497)
    at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:311)
    at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:248)
    at org.eclipse.jetty.io.AbstractConnection$2.run(AbstractConnection.java:540)
    at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:610)
    at org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:539)
    at java.lang.Thread.run(Thread.java:745)
Caused by: java.lang.NullPointerException
    at java.util.Properties$LineReader.readLine(Properties.java:434)
    at java.util.Properties.load0(Properties.java:353)
    at java.util.Properties.load(Properties.java:341)
    at com.mysql.cj.jdbc.util.TimeUtil.loadTimeZoneMappings(TimeUtil.java:163)
    at com.mysql.cj.jdbc.util.TimeUtil.getCanonicalTimezone(TimeUtil.java:109)
    at com.mysql.cj.mysqla.MysqlaSession.configureTimezone(MysqlaSession.java:308)
    at com.mysql.cj.jdbc.ConnectionImpl.initializePropsFromServer(ConnectionImpl.java:2474)
    at com.mysql.cj.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:1817)
    ... 39 more

[/code]

thats what printStackTrace() produces… let me know if it helps you!

Could be a bug in the mysql driver: https://bugs.mysql.com/bug.php?id=79343
Does the exception happen when you append ‘?useLegacyDatetimeCode=true’ to the connection URL (your DB_IP variable)?

just tried both, but had the same results as before.

when im using “com.mysql.jdbc.Driver” i get the Information that this is deprected and that “com.mysql.cj.jdbc.Driver” instead.
Could it be possible that it has something to do with the way i set up the project? I followed the vaadin-guide with maven and jetty as an webcontainer. Maybe its because the webcontainer doesn’t let me communicate from SimpleJDBCConnectionPool back to Database which is deployed on a webserver?

I also tried implementing a JDBC-Connection without Vaadin (only console). That Connection worked fine and i was able to execute all Statements (SELECT, UPDATE, INSERT,JOIN)?

I’m really starting to get clueless about whats causing the errors

That shouldn’t be an issue IMHO.
I’d try an older Connector/J release and see if it still happens (you might have to change JDBC_DRIVER = “com.mysql.cj.jdbc.Driver” to “com.mysql.jdbc.Driver” if you use one with version 5.x).