Connecting Mysql database with Java

Question

--CODE UPDATED--

I'm trying to connect a MySQL database using JDBC but I'm not able to get the connection to work. I'm not sure if the way I've constructed the connection string is right. Here's the code and the error:

Code:

    Connection conn;
    String userName = "root";
    String serverName = "127.0.0.1";
    String portNumber = "3306";
    String password = "password";
    String dbName = "myDB";

    /**
     * Establishes connection with the sql database
     * @throws SQLException
     */
    public SQLConnector() throws SQLException {
        setConnection();
    }

    private void setConnection() throws SQLException {
        Properties connectionProps = new Properties();
        connectionProps.put("user", this.userName);
        connectionProps.put("password", this.password);
        String connectionString = "jdbc:mysql://" + this.serverName
                + ":" + this.portNumber + "/" + this.dbName;
        System.out.println(connectionString);
        this.conn = DriverManager.getConnection(connectionString, connectionProps);
    }

Error:

    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.
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
    at java.lang.reflect.Constructor.newInstance(Unknown Source)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:377)
    at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1036)
    at com.mysql.jdbc.MysqlIO.readPacket(MysqlIO.java:627)
    at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1013)
    at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2234)
    at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2265)
    at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2064)
    at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:790)
    at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:44)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
    at java.lang.reflect.Constructor.newInstance(Unknown Source)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:377)
    at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:395)
    at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:325)
    at java.sql.DriverManager.getConnection(Unknown Source)
    at java.sql.DriverManager.getConnection(Unknown Source)
    at com.source.database.SQLConnector.setConnection(SQLConnector.java:69)
    at com.source.database.SQLConnector.<init>(SQLConnector.java:25)
    at com.source.main.Start.main(Start.java:13)
Caused by: java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.
    at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:2914)
    at com.mysql.jdbc.MysqlIO.readPacket(MysqlIO.java:559)
    ... 18 more

Refer http://stackoverflow.com/questions/2983248/com-mysql-jdbc-exceptions-jdbc4-communicationsexception-communications-link-fai — Mind Peace, Nov 05 '14 at 21:06
And don't use a field for your connection, it's get, use and dispose, not get and keep forever ;) — , Nov 05 '14 at 21:12
@RC: Erm. No. Creating database connections is actually pretty expensive operation. This is why people use connection pools – to have connections ready when they need one. — Markus W Mahlberg, Nov 06 '14 at 18:53
@MarkusWMahlberg that's why we use connection pooling (with validity check). .@EternallyCurious because your application might live for a long time and the server might close the connection during that time see http://stackoverflow.com/questions/8345133/when-my-app-loses-connection-how-should-i-try-to-recover — , Nov 06 '14 at 19:10
@RC: Exactly. And the pool creates connections _in advance_. And _reuses_ them. — Markus W Mahlberg, Nov 06 '14 at 19:11
@RC & MarkusWMahlberg. I appreciate your comments. My main problem remains unsolved, though. Please help me figure out a solution. — EternallyCurious, Nov 06 '14 at 20:16

score -1 · Answer 1 · answered Nov 05 '14 at 21:20

I did tried connecting mysql with java sometime back. Here is how i did it.

private Connection getConnection() throws SQLException 
    {
        Connection conn = null;
        Properties connectionProps = new Properties();
        connectionProps.put("user", this.userName);
        connectionProps.put("password", this.password);

        conn = DriverManager.getConnection("jdbc:mysql://"
                + this.serverName + ":" + this.portNumber + "/" + this.dbName,
                connectionProps);

        return conn;
    }

public boolean executeUpdate(Connection connection, String command) throws SQLException
    {
        try
        {
          Statement st = connection.createStatement();

          st.executeUpdate(command);

          connection.close();
        }
        catch (Exception e)
        {
          System.err.println("Got an exception!");
          e.printStackTrace();
        }

        return false;
    }

executeUpdate was used to update, insert and delete from the tables. Hope that helped..

Oh, and also JDBC driver must be in the program path.

I replaced my code with yours. Still getting the same error. Can you please ensure, your connection string is correctly made? — EternallyCurious, Nov 06 '14 at 18:37
Here is the reference materials i used: [This](http://www.ccs.neu.edu/home/kathleen/classes/cs3200/JDBCtutorial.pdf) is the tutorial and [this](http://www.ccs.neu.edu/home/kathleen/classes/cs3200/DBDemo.java) is the .java file used to run it. If everything is correct, it should run and your output will be that you connected to the DB and table created and dropped. Lemme know if that works or not :) — Tsar, Nov 07 '14 at 16:34
Also, check [this](http://stackoverflow.com/questions/2983248/com-mysql-jdbc-exceptions-jdbc4-communicationsexception-communications-link-fai) out.. same exception error — Tsar, Nov 07 '14 at 16:41

Connecting Mysql database with Java

1 Answers1