Java/JavaFX - check if internet connection exists upon trying db connection

Question

Let's say I have created a simple login window in JavaFX that checks if the username and password match each other by connecting to a postgresql database, and upon success logs the user in.

Now, this works perfectly fine as long as an internet connection exists.

However, if there's no internet connection the JVM repeatedly spews out PSQLExceptions and the application freezes for somewhat a minute or so. I thought I was catching these in my connection class. It is caused by SocketTimeoutException.

Does anyone have any tips regarding how I could handle this such that an error message would display in the GUI rather than freezing and exceptions?

My connection class looks like this:

public class DBConnection {
/* DB credentials, modify as needed */
private static final String HOST     = "<HOST>";
private static final String DATABASE = "<DATABASE>";
private static final String USERNAME = "<USERNAME>";
private static final String PASSWORD = "<PASSWORD>";
private static final String PORT     = "5432";

/* !!DO NOT EDIT BELOW!! */
private static final String JDBC_DRIVER = "org.postgresql.Driver";
private static final String URL         = "jdbc:postgresql://" + HOST + ":" + PORT + "/" + DATABASE;

private static final ComboPooledDataSource POOL = new ComboPooledDataSource();

private static Connection conn = null;

public static void Connect() throws PropertyVetoException, SQLException{
    try {
        POOL.setDriverClass(JDBC_DRIVER);
    }catch (PropertyVetoException e){
        System.out.println("Could not locate JDBC driver");
        e.printStackTrace();
        throw e;
    }
    //Connection parameters
    POOL.setJdbcUrl(URL);
    POOL.setUser(USERNAME);
    POOL.setPassword(PASSWORD);

    //Pool properties
    POOL.setMinPoolSize(3);
    POOL.setAcquireIncrement(5);
    POOL.setMaxPoolSize(100);
    POOL.setMaxStatements(180);

    try {
        conn = POOL.getConnection();
    } catch (SQLException e) {
        System.out.println("Could not establish connection to database");
        e.printStackTrace();
        throw e;
    }
}
public static ResultSet ExecuteQuery(String query) throws PropertyVetoException, SQLException {
    PreparedStatement st = null;
    ResultSet rs = null;
    CachedRowSet crs = null;
    try {
        Connect();
        System.out.println("Select statement: " + query + "\n");
        st = conn.prepareStatement(query);
        rs = st.executeQuery();
        crs = new CachedRowSetImpl();
        crs.populate(rs);
    }catch (Exception e){
        System.out.println("Problem occurred at executeQuery operation: " + e);
        throw e;
    }finally {
        DbUtils.closeQuietly(conn, st, rs);
    }
    return crs;
}
public static void ExecuteUpdate(String query) throws PropertyVetoException, SQLException {
    PreparedStatement st = null;
    try {
        Connect();
        st = conn.prepareStatement(query);
        st.executeUpdate();
    }catch (Exception e){
        System.out.println("Problem occurred at executeUpdate operation: " + e);
        throw e;
    }finally {
        DbUtils.closeQuietly(st);
        DbUtils.closeQuietly(conn);
    }
}
}

Answer 1

As usual in IT, add another layer of abstraction to solve your problem.

Meaning: you could have a class that is responsible for understanding "are we connected". It could gain that knowledge by periodically ping the DB server (see here for ideas how to do that). So, before you ask to establish a connectin, you ask that "AreWeConnectedService" for its opinion.

That Service would basically work like this:

• it has an internal field "connected"
• it has an internal thread, that thread keeps periodically pinging the other side
• if ping fails you go connected = false (and probably you remember a timestamp)
• if ping works again at some point, you toggle the field agian

And then that service has a method to query that flag.

But of course, you could still lose the connection right after the service tells you "everything fine". Thus you have to further step back and fix the "other part" of your question: you have to use another thread then the SwingUX thread to perform that getConnection() call. Ideally, you might even use two threads: one that makes the actual call; and another one around that enforcing a timeout.

Beyond that: make sure that you read and understand what Oracle has to tell you about Concurrency in JavaFx! And another link - you might want to look into the Circuit breaker pattern, too.