JavaFX - Background Thread for SQL Query

Question

I'm wondering if anybody can help me with a rather annoying problem regarding creating a background thread in JavaFX! I currently have several SQL queries that add data to the UI which currently run on the JavaFX Application Thread (see example below). However when each of these queries execute it freezes the UI because it isn't running on a background thread. I've looked at various examples that use Task and sort of understand them but I cannot get them to work when doing database queries, some of which take a few seconds to run.

Here is one of the methods that executes a query:

public void getTopOrders() {
    customerOrders.clear();
    try {
        Connection con = DriverManager.getConnection(connectionUrl);
        //Get all records from table
        String SQL = "EXEC dbo.Get_Top_5_Customers_week";
        ResultSet rs;
        try (Statement stmt = con.createStatement();) {
            rs = stmt.executeQuery(SQL);

            while (rs.next()) {
                double orderValue = Double.parseDouble(rs.getString(3));
                customerOrders.add(new CustomerOrders(rs.getString(1),
                        rs.getString(2), "£" + formatter.format(orderValue),
                        rs.getString(4).substring(6, 8) + "/" + 
                        rs.getString(4).substring(4, 6) + "/" + 
                        rs.getString(4).substring(0, 4)));
            }
        }

    } catch (SQLException | NumberFormatException e) {
    }
}

Each processed record is added to an ObservableList which is linked to a TableView, or graph or simply sets the text on a label (depends on the query). How can I execute the query on a background thread and still leave the interface free to use and be updated from the queries

Thanks in advance

Answer 1

I created a sample solution for using a Task (as suggested in Alexander Kirov's comment) to access a database on a concurrently executing thread to the JavaFX application thread.

The relevant parts of the sample solution are reproduced below:

// fetches a collection of names from a database.
class FetchNamesTask extends DBTask<ObservableList<String>> {
  @Override protected ObservableList<String> call() throws Exception {
    // artificially pause for a while to simulate a long 
    // running database connection.
    Thread.sleep(1000); 

    try (Connection con = getConnection()) {
      return fetchNames(con);
    }
  }

  private ObservableList<String> fetchNames(Connection con) throws SQLException {
    logger.info("Fetching names from database");
    ObservableList<String> names = FXCollections.observableArrayList();

    Statement st = con.createStatement();      
    ResultSet rs = st.executeQuery("select name from employee");
    while (rs.next()) {
      names.add(rs.getString("name"));
    }

    logger.info("Found " + names.size() + " names");

    return names;
  }
}

// loads a collection of names fetched from a database into a listview.
// displays a progress indicator and disables the trigge button for
// the operation while the data is being fetched.
private void fetchNamesFromDatabaseToListView(
        final Button triggerButton, 
        final ProgressIndicator databaseActivityIndicator, 
        final ListView listView) {
  final FetchNamesTask fetchNamesTask = new FetchNamesTask();
  triggerButton.setDisable(true);
  databaseActivityIndicator.setVisible(true);
  databaseActivityIndicator.progressProperty().bind(fetchNamesTask.progressProperty());
  fetchNamesTask.setOnSucceeded(new EventHandler<WorkerStateEvent>() {
    @Override public void handle(WorkerStateEvent t) {
      listView.setItems(fetchNamesTask.getValue());
    }
  });
  fetchNamesTask.runningProperty().addListener(new ChangeListener<Boolean>() {
    @Override public void changed(ObservableValue<? extends Boolean> observable, Boolean wasRunning, Boolean isRunning) {
      if (!isRunning) {
        triggerButton.setDisable(false);
        databaseActivityIndicator.setVisible(false);
      }
    };
  });
  databaseExecutor.submit(fetchNamesTask);
}

private Connection getConnection() throws ClassNotFoundException, SQLException {
  logger.info("Getting a database connection");
  Class.forName("org.h2.Driver");
  return DriverManager.getConnection("jdbc:h2:~/test", "sa", "");
}  

abstract class DBTask<T> extends Task<T> {
  DBTask() {
    setOnFailed(new EventHandler<WorkerStateEvent>() {
      @Override public void handle(WorkerStateEvent t) {
        logger.log(Level.SEVERE, null, getException());
      }
    });
  }
}

// executes database operations concurrent to JavaFX operations.
private ExecutorService databaseExecutor = Executors.newFixedThreadPool(
  1, 
  new DatabaseThreadFactory()
);  

static class DatabaseThreadFactory implements ThreadFactory {
  static final AtomicInteger poolNumber = new AtomicInteger(1);

  @Override public Thread newThread(Runnable runnable) {
    Thread thread = new Thread(runnable, "Database-Connection-" + poolNumber.getAndIncrement() + "-thread");
    thread.setDaemon(true);

    return thread;
  }
}    

Note that once you start doing things concurrently, your coding and your UI gets more complicated than the default mode without Tasks when everything is single threaded. For example, in my sample I disabled the button which initiates the Task so you cannot have multiple Tasks running in the background doing the same thing (this kind of processing is similar to the web world where you might disable a form post button to prevent a form being double posted). I also added an animated progress indicator to the scene while the long running database task was executing so that the user has an indication that something is going on.

Sample program output demonstrating the UI experience when a long running database operation is in progress (note the progress indicator is animating during the fetch which means the UI is responsive though the screenshot does not show this):

To compare the additional complexity and functionality of an implementation with concurrent tasks versus an implementation which executes everything on the JavaFX application thread, you can see another version of the same sample which does not use tasks. Note that in my case with a toy, local database the additional complexity of the task based application is unnecessary because the local database operations execute so quickly, but if you were connecting to a large remote database using long running complex queries, than the Task based approach is worthwhile as it provides users with a smoother UI experience.

Answer 2

Managed to resolve using the solution provided by jewelsea. It is worth noting that if implementing this method when not using lists, tables and/or observable lists where you need to update an item on the UI such as a text field or label then simply add the update code within Platform.runLater. Below are some code snippets that show my working solution.

Code:

public void getSalesData() {
    try {
        Connection con = DriverManager.getConnection(connectionUrl);
        //Get all records from table
        String SQL = "EXEC dbo.Order_Information";
        try (Statement stmt = con.createStatement(); ResultSet rs =
                        stmt.executeQuery(SQL)) {
            while (rs.next()) {

                todayTot = Double.parseDouble(rs.getString(7));
                weekTot = Double.parseDouble(rs.getString(8));
                monthTot = Double.parseDouble(rs.getString(9));
                yearTot = Double.parseDouble(rs.getString(10));
                yearTar = Double.parseDouble(rs.getString(11));
                monthTar = Double.parseDouble(rs.getString(12));
                weekTar = Double.parseDouble(rs.getString(13));
                todayTar = Double.parseDouble(rs.getString(14));
                deltaValue = Double.parseDouble(rs.getString(17));

                yearPer = yearTot / yearTar * 100;
                monthPer = monthTot / monthTar * 100;
                weekPer = weekTot / weekTar * 100;
                todayPer = todayTot / todayTar * 100;

                //Doesn't update UI unless you add the update code to Platform.runLater...
                Platform.runLater(new Runnable() {
                    public void run() {
                        todayTotal.setText("£" + formatter.format(todayTot));
                        weekTotal.setText("£" + formatter.format(weekTot));
                        monthTotal.setText("£" + formatter.format(monthTot));
                        yearTotal.setText("£" + formatter.format(yearTot));
                        yearTarget.setText("£" + formatter.format(yearTar));
                        monthTarget.setText("£" + formatter.format(monthTar));
                        weekTarget.setText("£" + formatter.format(weekTar));
                        todayTarget.setText("£" + formatter.format(todayTar));
                        yearPercent.setText(percentFormatter.format(yearPer) + "%");
                        currentDelta.setText("Current Delta (Week Ends): £"
                                + formatter.format(deltaValue));
                    }
                });

            }
        }

    } catch (SQLException | NumberFormatException e) {
    }
}


    public void databaseThreadTester() {
    fetchDataFromDB();
}

private void fetchDataFromDB() {
    final testController.FetchNamesTask fetchNamesTask = new testController.FetchNamesTask();
    databaseActivityIndicator.setVisible(true);
    databaseActivityIndicator.progressProperty().bind(fetchNamesTask.progressProperty());
    fetchNamesTask.setOnSucceeded(new EventHandler<WorkerStateEvent>() {
        @Override
        public void handle(WorkerStateEvent t) {
        }
    });
    fetchNamesTask.runningProperty().addListener(new ChangeListener<Boolean>() {
        @Override
        public void changed(ObservableValue<? extends Boolean> observable, Boolean wasRunning, Boolean isRunning) {
            if (!isRunning) {
                databaseActivityIndicator.setVisible(false);
            }
        }
    ;
    });
databaseExecutor.submit(fetchNamesTask);
}

abstract class DBTask<T> extends Task {

    DBTask() {
        setOnFailed(new EventHandler<WorkerStateEvent>() {
            @Override
            public void handle(WorkerStateEvent t) {
            }
        });
    }
}

class FetchNamesTask extends testController.DBTask {

    @Override
    protected String call() throws Exception {

        fetchNames();

        return null;
    }

    private void fetchNames() throws SQLException, InterruptedException {
        Thread.sleep(5000);
        getTopOrders();
        getSalesData();
    }
}

The only thing that doesn't appear to work with this implementation is the following, not sure why it doesn't work but it doesn't draw the graph.

public void addCricketGraphData() {

    yearChart.getData().clear();
    series.getData().clear();
    series2.getData().clear();
    try {
        Connection con = DriverManager.getConnection(connectionUrl);
        //Get all records from table
        String SQL = "...omitted...";
        try (Statement stmt = con.createStatement(); ResultSet rs =
                        stmt.executeQuery(SQL)) {
            while (rs.next()) {
                Platform.runLater(new Runnable() {
                    @Override
                    public void run() {
                        try {
                            series.getData().add(new XYChart.Data<String, Number>(rs.getString(1),
                                    Double.parseDouble(rs.getString(7))));
                            series2.getData().add(new XYChart.Data<String, Number>(rs.getString(1),
                                    Double.parseDouble(rs.getString(8))));
                        } catch (SQLException ex) {
                            Logger.getLogger(testController.class.getName()).log(Level.SEVERE, null, ex);
                        }
                    }
                });

            }
        }

    } catch (SQLException | NumberFormatException e) {
    }
    yearChart = createChart();
}

 protected LineChart<String, Number> createChart() {
    final CategoryAxis xAxis = new CategoryAxis();
    final NumberAxis yAxis = new NumberAxis();

    // setup chart
    series.setName("Target");
    series2.setName("Actual");
    xAxis.setLabel("Period");
    yAxis.setLabel("£");

    //Add custom node for each point of data on the line chart.
    for (int i = 0; i < series2.getData().size(); i++) {
        nodeCounter = i;
        final int value = series.getData().get(nodeCounter).getYValue().intValue();
        final int value2 = series2.getData().get(nodeCounter).getYValue().intValue();
        int result = value2 - value;

        Node node = new HoveredThresholdNode(0, result);
        node.toBack();
        series2.getData().get(nodeCounter).setNode(node);
    }

    yearChart.getData().add(series);
    yearChart.getData().add(series2);

    return yearChart;
}