Maduranga's Blogs: May 2014

In any software, if a user is needed to add a database connection details, a test connection button will come in handy. In this blog, I'm trying to test a database connection using a JDBC driver. First we need to download a driver. From the bellow link you can download the MySql driver.

http://dev.mysql.com/downloads/connector/j/

When we are working with the driver, first it need to be added to the CLASSPATH. It can be done as described in,
http://dev.mysql.com/doc/connector-j/en/connector-j-installing.html

For just test purposes, adding it to the build path of the IDE will be sufficient.

If we need to do some operation on the database using the driver, first we need to create a connection to the database. For this you will need driver name (for MySql, driver name is com.mysql.jdbc.Driver ), database url (for MySql database running in the localhost, the url will be jdbc:mysql://localhost:3306/db_name ), username and the password. When creating the connection, if the driver couldn't establish a valid connection, it will throw an exception depending on the course. We can easily use it to test the connection.

First we need to create a custom exception.

public class DataSourceException extends Exception {
 /**
  * 
  */
 private static final long serialVersionUID = 1L;

 public DataSourceException(String message) {
  super(message);
 }

 public DataSourceException(String message, Throwable throwable) {
  super(message, throwable);
 }
}

Now we can create a method to get the connection. This method will return a connection to the database. But to test the connection we are not much interested about the connection. We are checking whether it is throwing any exception or not and if it is the error message. Bellow method can be used to get the connection.

public Connection getConnection(String driver, String url, String uname,
   String pwd) throws DataSourceException {
  Connection conn = null;

  try {
   Class.forName(driver).newInstance();
   conn = DriverManager.getConnection(url + "?user=" + uname + "&password=" + pwd);

  } catch (SQLException ex) {
   throw new DataSourceException(
     "Error establishing data source connection: "
       + ex.getMessage(), ex);
  } catch (InstantiationException e) {
   e.printStackTrace();
  } catch (IllegalAccessException e) {
   e.printStackTrace();
  } catch (ClassNotFoundException e) {
   throw new DataSourceException("Error establishing data source connection: " + e.getMessage(), e);
  }

  return conn;
 }

Now we want to check if the connection is throwing any exception. If it is not, the connection is healthy. But if it is not, the connection is not valid and we can find the cause from the error message of the exception. test connection method will look like this,

public boolean testConnection(String driver, String url, String uname, String pwd) throws DataSourceException {
  try {
   getConnection(driver, url, uname, pwd);
  } catch (DataSourceException e) {
   throw e;
  }

  return true;
}

So now we can check the validity of the database connection.

String driverName = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://localhost:3306/driver_test";

try {
 boolean valiedConnection = testConnection(driverName, url, "maduranga", "maduranga");
 if (valiedConnection) {
  System.out.println("Connection is Healthy");
 }
}catch (DataSourceException e) {
 System.out.println(e.getMessage());
}

The error messages will be shown like bellow.

For a false driver name - Error establishing data source connection: com.mysql.jdbc.Drive
For a false connection url - Error establishing data source connection: Unknown database 'userstor'
For a false username - Error establishing data source connection: Access denied for user 'madurang'@'localhost' (using password: YES)
For a false password - Error establishing data source connection: Access denied for user 'maduranga'@'localhost' (using password: YES)
For correct data - Connection is Healthy

As the error messages are really meaningful, this will help a great deal for the user to add the database connection.

Maduranga's Blogs

Saturday, May 17, 2014

Test Java Database Connection Using JDBC Driver