Java: Trouble connecting to MySQL

Question

I'm writing a desktop java app on that I want to connect to a MySQL database on a server. Here is the code to do that:

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

private static Connection getDBConnection() throws SQLException, InstantiationException, IllegalAccessException, ClassNotFoundException {
        String username = "myUserName";
        String password = "myPassWord";
        String url = "jdbc:mysql://www.domainName.com:3306/databaseName";

        Class.forName("com.mysql.jdbc.Driver");
        System.out.println("Connecting to database...");

        //hangs here    
        Connection conn = DriverManager.getConnection(url, username, password);
        return conn;
    }

When I run this, it hangs on the DriverManager.getConnection() call. Why does this happen? Is my URL malformed?

(I'm not getting any error messages, but the program doesn't respond as if in an infinite loop. I haven't waited longer than 90 seconds to see if the connection will ever be established.)

Also, what is the purpose of the Class.forName() call? How does it work?

I am almost entirely certain that the username and password are correct. (I just used userName and passWord as placeholders above.)

UPDATE: I fixed the port number, and now I get this error:

Cannot connect to database: java.sql.SQLException: Access denied for user 'userName'@'r236059121.resnet.mySchool.edu' (using password: YES)

Does this mean I need to configure settings on the database? Or does it mean that I've got the credentials wrong? (They work for PHP scripts deployed on the server that contains the database.)

SOLUTION: Added the host above to the Access Host list on cPanel.

Answer 1

Seems to me like your database is not reachable and you will probably get an error when the call runs into a timeout. Are you sure the hostname and port are right and reachable from your machine?

You don't need the newInstance() at the end of Class.forName(). Class.forName() triggers the classloader to load that class, which in turn triggers some internal registration code in the driver which makes the driver available.

Answer 2

I think the line should just be

Class.forName("com.mysql.jdbc.Driver");

(close the .newInstance() bit)

That causes the driver to register itself with the driver manager and allows the driver manager to pick a driver for the database url.

I think the hang is caused by a DNS problem, or some other reason why your db cannot be reached. By default, the MySQL JDBC driver does not time out for a connection. See http://dev.mysql.com/doc/refman/5.0/en/connector-j-reference-configuration-properties.html and look for connectTimeout.

In your code, you have

  String url = "jdbc:mysql://www.domainName.com:portNumber/databaseName";

I take it that you used a real port there? By default, it should be 3306. You can test with the test database which is present in virtually all mysql instances:

  String url = "jdbc:mysql://www.domainName.com:3306/test";

You also wrote:

 String username = "myUserName";
 String password = "myPassWord";

Obviously you should use real credentials here too. Ask your dba what they are. If you're the DBA then...well you should probably read up on MySQl administration :) Seriously when you installed MySQL you were probably promted for a password for the root user. Use those (in the obvious way)

In real code you should probably not hang when the db is not there. So I advise adding a connectTimeout option like so:

  String url = "jdbc:mysql://www.domainName.com:3306/test?connectTimeout=3000";

(connectTimeout is in milliseconds, so this would time out after 3 seconds)

Answer 3

Rosarch - You are not able to connect to your DB since its unreachable. It'll timeout after a while. Try telnetting -

telnet <IP-OF-domainName.com> <PortNumber>

You'll mostly see that it shows timeout.

Solutions - 1.) If you are behind a firewall, you need to punch a hole to allow access 2.) If you are behind a proxy, need to configure it to allow access