How should I handle and pass a Connection in a javafx project?

Question

I am working on a JavaFX project that connects to a MySQL database (and as such I use a JDBC [java.sql] to perform various tasks from java to SQL). I've recently come into a lot of confusion regarding the standard procedure of connecting to a database.

From what I understand, I should create one instance of a Connection object, then I should pass this instance around the project until the end of the entire program.

Connection connection = DriverManager.getConnection(url, username, password);

If the statement above is true then I only need to call the previous line once in the entire program and pass the connection variable around the entire program whenever I need to connect to the database. If this is true, how can I pass around this instance of connection from one controller class to another?

---

Furthermore, I am seeing that it is a standard procedure (in order to maximize GUI responsiveness) to push all "costly" procedures onto another thread (not the main one that is handling the GUI) using the javafx.concurrencies library. If this statement is true, is it also true that I should push all methods that interact with the database into say a Task<V> class?

---

Up until now, I created multiple object classes that each communicate with the database. In addition, I created a Database class that allows me to initialize a "new" connection to the database. For example,

public class Database {

    public static Connection createConnectionToDB() throws SQLException{       
        Connection connection = DriverManager.getConnection(url, username, password); 
        return connection;
    }
}

Now in the various object classes, when I need to add, update, or delete some data from the database, I created an add, update, and delete method in each object to handle that object's data in the database. However, for each method, I thought (from my natural naivete) that it would be standard to first create a connection to the database via the previous Connection connection = createConnectionToDB() method in the Database class then to use this connection instance to do what needs to be done, then close the connection with the connection.close() method.

For example,

public class Object{

     add(){
          Connection connection = Database.createConnectionToDB();
          PreparedStatement statement = ...
          // some other lines 
          connection.close();
     }
     
     update(){
          Connection connection = Database.createConnectionToDB();
          PreparedStatement statement = ...
          // some other lines 
          connection.close();
     }
     
     delete(){
          Connection connection = Database.createConnectionToDB();
          PreparedStatement statement = ...
          // some other lines 
          connection.close();
     }

}

That is, for each object, and each method for each object, a new connection is created and closed. Is this bad practice?

Answer 1

It could be a bad practice if your application creates new database Connection objects with longer scopes every time it needs to perform a query operation on the database. I would suggest that you should put the Connection initiation code into a try-with-resources code block so that for each database operation, a connection is established, query operation is performed and the connection is closed within the block. The class to fetch new Connection could look like this:

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

public class DatabaseConnection {

    private static final String DB_URL = "jdbc:mysql://localhost/dbName";
    private static final String USER = "dbUser";
    private static final String PASS = "dbPassword";

    public static Connection getDatabaseConnection()
    {
        Connection databaseConnection;
        try {
            databaseConnection = DriverManager.getConnection(DB_URL, USER, PASS);
            
        } catch (SQLException e)
        {
            e.printStackTrace();
        }
        return databaseConnection;
    }
}

So, you can make a call to the static method DatabaseConnection.getDatabaseConnection() from any point (controller or other class) of your application to obtain the new Connection instance and use it.

And yes, it would be better to make use of the javafx.concurrency library in case of a JavaFX application. You can utilize the Task<V> class like:

Task<Void> task = new Task<Void>() {
            @Override
            protected Void call() throws Exception {

                String query = "SELECT id, first, last, age FROM Employees";
                try(Connection conn = DatabaseConnection.getDatabaseConnection();
                    Statement stmt = conn.createStatement();
                    ResultSet rs = stmt.executeQuery(query);) {
                    // Extract data from result set
                    while (rs.next()) {
                        // Retrieve by column name
                        System.out.print("ID: " + rs.getInt("id"));
                        System.out.print(", Age: " + rs.getInt("age"));
                        System.out.print(", First: " + rs.getString("first"));
                        System.out.println(", Last: " + rs.getString("last"));
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }

                return null;
            }
        };

And invoking the Task by:

new Thread(task).start();

Note that you should populate all the ResultSet data into your own custom non-jdbc classes so that the ResultSet class can also be disposed within the block. Furthermore, you should also consider applying Transaction Management to your code containing Database Operations (in case your application is concurrently making many database calls from different points/classes) for data consistency.