Using Threads to Make Database Requests

Using threads to make database requests

Threading Rules for JavaFX

There are two basic rules for threads and JavaFX:

Any code that modifies or accesses the state of a node that is part of a scene graph must be executed on the JavaFX application thread. Certain other operations (e.g. creating new Stages) are also bound by this rule.
Any code that may take a long time to run should be executed on a background thread (i.e. not on the FX Application Thread).

The reason for the first rule is that, like most UI toolkits, the framework is written without any synchronization on the state of elements of the scene graph. Adding synchronization incurs a performance cost, and this turns out to be a prohibitive cost for UI toolkits. Thus only one thread can safely access this state. Since the UI thread (FX Application Thread for JavaFX) needs to access this state to render the scene, the FX Application Thread is the only thread on which you can access "live" scene graph state. In JavaFX 8 and later, most methods subject to this rule perform checks and throw runtime exceptions if the rule is violated. (This is in contrast to Swing, where you can write "illegal" code and it may appear to run fine, but is in fact prone to random and unpredictable failure at arbitrary time.) This is the cause of the IllegalStateException you are seeing: you are calling courseCodeLbl.setText(...) from a thread other than the FX Application Thread.

The reason for the second rule is that the FX Application Thread, as well as being responsible for processing user events, is also responsible for rendering the scene. Thus if you perform a long-running operation on that thread, the UI will not be rendered until that operation is complete, and will become unresponsive to user events. While this won't generate exceptions or cause corrupt object state (as violating rule 1 will), it (at best) creates a poor user experience.

Thus if you have a long-running operation (such as accessing a database) that needs to update the UI on completion, the basic plan is to perform the long-running operation in a background thread, returning the results of the operation when it is complete, and then schedule an update to the UI on the UI (FX Application) thread. All single-threaded UI toolkits have a mechanism to do this: in JavaFX you can do so by calling Platform.runLater(Runnable r) to execute r.run() on the FX Application Thread. (In Swing, you can call SwingUtilities.invokeLater(Runnable r) to execute r.run() on the AWT event dispatch thread.) JavaFX (see later in this answer) also provides some higher-level API for managing the communication back to the FX Application Thread.

General Good Practices for Multithreading

The best practice for working with multiple threads is to structure code that is to be executed on a "user-defined" thread as an object that is initialized with some fixed state, has a method to perform the operation, and on completion returns an object representing the result. Using immutable objects for the initialized state and computation result is highly desirable. The idea here is to eliminate the possibility of any mutable state being visible from multiple threads as far as possible. Accessing data from a database fits this idiom nicely: you can initialize your "worker" object with the parameters for the database access (search terms, etc). Perform the database query and get a result set, use the result set to populate a collection of domain objects, and return the collection at the end.

In some cases it will be necessary to share mutable state between multiple threads. When this absolutely has to be done, you need to carefully synchronize access to that state to avoid observing the state in an inconsistent state (there are other more subtle issues that need to be addressed, such as liveness of the state, etc). The strong recommendation when this is needed is to use a high-level library to manage these complexities for you.

Using the javafx.concurrent API

JavaFX provides a concurrency API that is designed for executing code in a background thread, with API specifically designed for updating the JavaFX UI on completion of (or during) the execution of that code. This API is designed to interact with the java.util.concurrent API, which provides general facilities for writing multithreaded code (but with no UI hooks). The key class in javafx.concurrent is Task, which represents a single, one-off, unit of work intended to be performed on a background thread. This class defines a single abstract method, call(), which takes no parameters, returns a result, and may throw checked exceptions. Task implements Runnable with its run() method simply invoking call(). Task also has a collection of methods which are guaranteed to update state on the FX Application Thread, such as updateProgress(...), updateMessage(...), etc. It defines some observable properties (e.g. state and value): listeners to these properties will be notified of changes on the FX Application Thread. Finally, there are some convenience methods to register handlers (setOnSucceeded(...), setOnFailed(...), etc); any handlers registered via these methods will also be invoked on the FX Application Thread.

So the general formula for retrieving data from a database is:

Create a Task to handle the call to the database.
Initialize the Task with any state that is needed to perform the database call.
Implement the task's call() method to perform the database call, returning the results of the call.
Register a handler with the task to send the results to the UI when it is complete.
Invoke the task on a background thread.

For database access, I strongly recommend encapsulating the actual database code in a separate class that knows nothing about the UI (Data Access Object design pattern). Then just have the task invoke the methods on the data access object.

So you might have a DAO class like this (note there is no UI code here):

public class WidgetDAO {

    // In real life, you might want a connection pool here, though for
    // desktop applications a single connection often suffices:
    private Connection conn ;

    public WidgetDAO() throws Exception {
        conn = ... ; // initialize connection (or connection pool...)
    }

    public List<Widget> getWidgetsByType(String type) throws SQLException {
        try (PreparedStatement pstmt = conn.prepareStatement("select * from widget where type = ?")) {
            pstmt.setString(1, type);
            ResultSet rs = pstmt.executeQuery();
            List<Widget> widgets = new ArrayList<>();
            while (rs.next()) {
                Widget widget = new Widget();
                widget.setName(rs.getString("name"));
                widget.setNumberOfBigRedButtons(rs.getString("btnCount"));
                // ...
                widgets.add(widget);
            }
            return widgets ;
        }
    }

    // ...

    public void shutdown() throws Exception {
        conn.close();
    }
}

Retrieving a bunch of widgets might take a long time, so any calls from a UI class (e.g a controller class) should schedule this on a background thread. A controller class might look like this:

public class MyController {

    private WidgetDAO widgetAccessor ;
    
    // java.util.concurrent.Executor typically provides a pool of threads...
    private Executor exec ;

    @FXML
    private TextField widgetTypeSearchField ;

    @FXML
    private TableView<Widget> widgetTable ;

    public void initialize() throws Exception {
        widgetAccessor = new WidgetDAO();

        // create executor that uses daemon threads:
        exec = Executors.newCachedThreadPool(runnable -> {
            Thread t = new Thread(runnable);
            t.setDaemon(true);
            return t ;
        });
    }

    // handle search button:
    @FXML
    public void searchWidgets() {
        final String searchString = widgetTypeSearchField.getText();
        Task<List<Widget>> widgetSearchTask = new Task<List<Widget>>() {
            @Override
            public List<Widget> call() throws Exception {
                return widgetAccessor.getWidgetsByType(searchString);
            }
        };

        widgetSearchTask.setOnFailed(e -> {
           widgetSearchTask.getException().printStackTrace();
            // inform user of error...
        });

        widgetSearchTask.setOnSucceeded(e -> 
            // Task.getValue() gives the value returned from call()...
            widgetTable.getItems().setAll(widgetSearchTask.getValue()));

        // run the task using a thread from the thread pool:
        exec.execute(widgetSearchTask);
    }

    // ...
}

Notice how the call to the (potentially) long-running DAO method is wrapped in a Task which is run on a background thread (via the accessor) to prevent blocking the UI (rule 2 above). The update to the UI (widgetTable.setItems(...)) is actually executed back on the FX Application Thread, using the Task's convenience callback method setOnSucceeded(...) (satisfying rule 1).

In your case, the database access you are performing returns a single result, so you might have a method like

public class MyDAO {

    private Connection conn ; 

    // constructor etc...

    public Course getCourseByCode(int code) throws SQLException {
        try (PreparedStatement pstmt = conn.prepareStatement("select * from course where c_code = ?")) {
            pstmt.setInt(1, code);
            ResultSet results = pstmt.executeQuery();
            if (results.next()) {
                Course course = new Course();
                course.setName(results.getString("c_name"));
                // etc...
                return course ;
            } else {
                // maybe throw an exception if you want to insist course with given code exists
                // or consider using Optional<Course>...
                return null ;
            }
        }
    }

    // ...
}

And then your controller code would look like

final int courseCode = Integer.valueOf(courseId.getText());
Task<Course> courseTask = new Task<Course>() {
    @Override
    public Course call() throws Exception {
        return myDAO.getCourseByCode(courseCode);
    }
};
courseTask.setOnSucceeded(e -> {
    Course course = courseTask.getCourse();
    if (course != null) {
        courseCodeLbl.setText(course.getName());
    }
});
exec.execute(courseTask);

The API docs for Task have many more examples, including updating the progress property of the task (useful for progress bars..., etc.

Related

JavaFX - Background Thread for SQL Query
Sample for accessing a local database from JavaFX using concurrent tasks for database operations so that the UI remains responsive.

Multithreading for queries in SQL Database

Basically when you open a connection in any, it is a good approach to close it and then create a new connection. In the above code you are executing two queries and fetching after the second thread. Now you will get result for on the second thread. The right approach is, create a connection, fetch the result and close the connection on your operation is completed and then open a new connection as alike perform the operation and close it. This approach is very good to follow in any programming language. Below is the sample code,

# in thread 1:

   cursor.execute('SELECT * FROM messages WHERE user_id = %s;', client1_id)

   cursor.fetchall()   
   con.close()   

# in thread 2

   cursor.execute('SELECT * FROM messages WHERE user_id = %s;', client2_id)   

   cursor.fetchall()
   con.close()

Handling requests using threads

Exactly. Each HTTP request is already a thread at its own. Keep in mind that the web container will create only one servlet instance during application's lifetime and that the servlet code is been shared among all requests. This implies that any class-level variables or static variables are going to be shared among all requests. If you have such one variable, it is not threadsafe. You need to declare request-specific variables threadlocal at method-level.

As to JDBC: just write solid code and everything should go well. Using a connection pool is only useful to improve connecting performance (which is really worth the effort, believe me, connecting the DB is a fairly expensive task which may account up to at least 200ms or even more, while reusing a connection from the pool costs almost nothing). It only doesn't change anything to the threadsafety of the code you write, it's still in your control/hands. To get a clear picture of how to do the basic JDBC coding the right way, you may find this article useful.

Multiprocessing/multithreading for database query in Python

The below link helped me
Multiprocessing with JDBC connection and pooling
I can get around 25% gain on my local.machine.

Share Application Thread Pool among Requests

We can use semaphores to limit the number of concurrent threads accessing a specific resource.java.util.concurrent.Semaphore In the following example, we will implement a simple login queue to limit number users in the system:

class LoginQueueUsingSemaphore {

    private Semaphore semaphore;

    public LoginQueueUsingSemaphore(int slotLimit) {
        semaphore = new Semaphore(slotLimit);
    }

    boolean tryLogin() {
        return semaphore.tryAcquire();
    }

    void logout() {
        semaphore.release();
    }

    int availableSlots() {
        return semaphore.availablePermits();
    }

}

Notice how we used the following methods:

tryAcquire()

return true if a permit is available immediately and acquire it otherwise return false, but acquire() acquires a permit and blocking until one is available.

release()

release a permit

availablePermits()

return number of current permits available

PS: exemple using is from site

Multithreaded database access query

How can I execute multiple SELECT queries from single database table at the same time?

Just do it. Use a separate MySqlConnection for each query: Connections themselves are not thread-safe. But you can create a connection, or use one from a connection pool, in each thread or process. Satisfying concurrent queries efficiently from different connections is a core feature of all database servers.

Can I just normally do multiple queries using threads or tasks

Yes.

and with different order/sorting criteria

That is no problem.

I know that for inserting, updating and removing data it will be a problem

It can be, but usually isn't. Databases have transactions.

Is it possible to use Node worker threads to perform database inserts?

It is of course possible, but it's a very bad idea.

Database drivers are already asynchronous and non-blocking of the JavaScript thread. Moving your insert calls to a separate thread as you propose will not only get you no performance gains, it will actually decrease overall performance because of the overhead involved with interthread communication:

Synchronization and message passing is not free
JavaScript uses structured cloning when moving data between threads. This means all your rowsToInsert must be copied, which is (relatively) expensive.

Generally, the only time it's really appropriate to use JS threads is when your JavaScript code is performing CPU-intensive work. The node docs say as much right at the top:

Workers (threads) are useful for performing CPU-intensive JavaScript operations. They will not help much with I/O-intensive work. Node.js’s built-in asynchronous I/O operations are more efficient than Workers can be.

This means if you're doing a lot of parsing, math, or similar, it may be appropriate to do the work in a thread. However, simply shoveling data from one place to another (ie, I/O) is not a good candidate for a thread — after all, node's design is tuned to be efficient at this kind of work.

You don't say where your rowsToInsert come from, but if it's coming in from HTTP request(s), a thread is the wrong thing to use. However, if you're parsing eg a CSV or JSON file on the server, it may be worthwhile to do that in a thread, but it's important that the thread does all the work (so memory need not be moved between threads). The message you post to the worker should just be "process the file located at /foo/bar.csv", and then the worker thread does the rest.

The error you're getting is the same that you'd get without worker threads: you're trying to use import in a regular, non-module JS file. Either rename the worker file to *.mjs or use require('knex') instead.

node's ES module documentation goes into detail about how import differs from require.

Using Threads to Make Database Requests