Best way to open and return a database connection in a Java application?

Question

I have come up with the following utility class:

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

public class MySqlConnection
{
    private static String dbUrl = "jdbc:mysql://localhost:3306/database";
    private static String dbUsername = "root";
    private static String dbPassword = "mysql";

    public static Connection getConnection()
    {
        Connection connection = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection(dbUrl, dbUsername, dbPassword);
        } catch (ClassNotFoundException e) {
            System.out.println("Could not load JDBC driver: " + e.getMessage());
        } catch (SQLException e) {
            System.out.println("Could not connect to DB: " + e.getMessage());
        }
        return connection;
    }
}

The problem is: I do not want to return null from my method, because by doing so I force my callers to do a if (connection != null) {...} check every time they open and want to use a connection. I find these null checks to be error prone and want to avoid them at all costs. What other approach could I follow to manage database connections in my application?

Answer 1

First of all, as of JDBC 4.0 / Java 6, calling Class.forName() is no longer necessary.
(See is Class.forName() necessary)

Next, don't bury the exceptions. Throw them up the stack, and let the callers decide how the exceptions should be handled. Depending on when getConnection() is being called you might want to:

• Display an error popup box to the user
• Try a different database for a connection
• Run a script to try and check the status of the database and attempt to restart it if it appears to be down
• Retry getConnection() completely

My point being, don't be afraid to throw Exceptions up the stack and let the caller handle the Exception appropriately.

All that being said, your getConnection() method should just need to store your DB URL, username, and password.

public class MySqlConnection
{
    private static String dbUrl = "jdbc:mysql://localhost:3306/database";
    private static String dbUsername = "root";
    private static String dbPassword = "mysql";

    public static Connection getConnection() throws SQLException
    {
        return DriverManager.getConnection(dbUrl, dbUsername, dbPassword);
    }
}

Realistically, getConnection() will very very rarely throw an SQLException. The only scenario I've ever seen it throw an SQLException is because credentials were incorrect or the database was down.

Answer 2

You can throw an exception in your original code upwards, and then have to deal with a possible exception every time you would want to get a connection. Granted, you will not have null issues, but this could end up being more work. However, it does make it very explicit that you have to handle the connection not working, and this could be clearer than simply returning a null.

Answer 3

Refer these two questions , Should a retrieval method return 'null' or throw an exception when it can't produce the return value? and How to show if a method may return null

that should clear things a bit.

You can't avoid returning a NULL if you can't produce desired object ( In some cases, we have an option to return EMPTY objects but that is not applicable to JDBC connection object ) - All you can do is properly document your methods.

You should also avoid doing Sysouts and let the caller know about errors to expect by adding throws to your method and by re throwing exceptions. You have an option to wrap your exceptions to something more useful ( specific to your application ) before re throwing.

Also , do explicit NULL returns from your catch ( return NULL; ) instead of relying on last return statement - return connection; . There is no point of not returning immediately if not wiling to eat Exception and continue.

Lastly, you should try to use @Null and @NotNull annotations to properly document your methods.