Database support of a JavaFX application

A window forgets everything the moment it closes, and a database has no buttons. This lesson joins them: a button's handler opens a JDBC connection, runs one SQL statement, and writes the answer back into the window as text. You will see it done twice, first with a students table and five buttons, then with an employees table driven by three input fields. Both programs are here in full, run click by click, and then repaired where they fall short.

How the pieces fit#

Both programs in this lesson are built from the same five layers. Each layer knows very little, and only one of them, the event handler, knows about the window and the database at once.

Follow one click on “Select Records” down the stack and back up:

1. 1The JavaFX Application Thread notices the click and calls the lambda that was registered with selectBtn.setOnAction.
2. 2The handler calls DBConnection.getConnection(). DriverManager passes the URL to the MySQL driver, which opens a network connection to port 3306 on this machine and logs in as root.
3. 3conn.createStatement() makes a Statement, and executeQuery("SELECT * FROM students") sends the SQL text to the server.
4. 4MySQL runs the query and sends the rows back. The driver hands them to the handler as a ResultSet.
5. 5The while (rs.next()) loop copies each row into a StringBuilder, and output.setText(...) puts the finished text in the TextArea.
6. 6The try-with-resources closes the ResultSet, the Statement and the Connection, in that order. The handler returns, and the FX thread redraws the window with the new text.

Because the layers meet only inside the handlers, you can replace the TextArea with a table control without touching DBConnection, or point DBConnection at another server without touching the window.

Key idea

The event handler is the join. It is the only code that reads controls and calls JDBC: it takes the input from the window, sends one statement to the database, and puts the answer, or the error message, back in the window.

Before you run anything#

A program that needs JavaFX and MySQL at once has more ways to fail on its first run than either half alone. Check these five things first. Nearly every first-run failure is one of them.

• MySQL is installed and running. The server listens on port 3306. If mysql -u root -p lets you log in at a terminal, or MySQL Workbench connects, the server is up.
• The database exists. At the mysql> prompt, run CREATE DATABASE studentdb;. StudentApp can create its own table, but neither program can create the database: the URL in DBConnection names studentdb, and connecting to a database that does not exist fails with Unknown database 'studentdb'.
• The driver jar is in your project folder. MySQL's JDBC driver is Connector/J, today a file called mysql-connector-j-9.7.0.jar (the “Platform Independent” download from dev.mysql.com, or Maven com.mysql:mysql-connector-j). Older guides name mysql-connector-java-8.0.x.jar, the old name of the same driver. Any 9.x jar works with this code.
• The JavaFX SDK is unzipped somewhere you know. The class uses javafx-sdk-27; run it on JDK 25 or newer. You need the path to its lib folder.
• The password in DBConnection is yours. Replace your_password with your MySQL root password, or every click ends in Access denied.

Compiling and running#

Put DBConnection.java, StudentApp.java (later also EmployeeManagerApp.java) and the driver jar in one folder, open a terminal there, and compile every .java file at once. The commands are the JavaFX commands from setting up JavaFX with the driver jar added to the class path.

javac prints nothing when it succeeds, and the class files land in the out folder.
java opens the StudentApp window. The console prints "Database Connected Successfully!"
each time a button opens a connection.

Same result. Replace /path/to with wherever you unzipped the SDK.

Why JavaFX and the driver go on different paths#

JavaFX is published as a set of Java modules (javafx.base, javafx.graphics, javafx.controls and others). When the class you launch extends Application, the java launcher checks that the JavaFX modules were loaded as modules. Put the JavaFX jars on the class path instead and it stops with Error: JavaFX runtime components are missing, and are required to run this application. So JavaFX goes on the module path and is asked for by name with --add-modules.

The driver is the opposite case. Your code never names a single MySQL class. At run time, DriverManager.getConnection looks through the class path for jars that announce a JDBC driver and offers the URL to each one until a driver accepts jdbc:mysql:. A plain jar on the class path is exactly what that search finds. Leave the jar out of -cp and the window still opens, but every connection fails with No suitable driver found for jdbc:mysql://localhost:3306/studentdb.

StudentApp: the CRUD program#

This program is a CRUD (Create, Read, Update, Delete) application: it manages student records in a database through JDBC. CRUDnames the four things almost every database program does to its rows. Here “Insert Record” is the Create, “Select Records” the Read, and “Update Record” and “Delete Record” the other two. The fifth button, “Create Table”, is set-up: it builds the empty table the other four work on.

DBConnection: a first version and the one used here#

Every handler begins by asking one small class for a connection. Here is a first version, the kind many tutorials show:

On success: prints "Database Connected Successfully!" on the console and returns the Connection.
On failure: prints a stack trace on the console and returns null.

DBConnection is a utility class: it holds no data and exists so that the connection details are written once instead of in every handler. It imports the three JDBC types it uses, Connection, DriverManager and SQLException. Its one method is static, so a handler calls DBConnection.getConnection() without creating a DBConnection object first (static members). The URL jdbc:mysql://localhost:3306/studentdb reads as: JDBC, the MySQL driver, the server on this machine at port 3306, the database studentdb. DriverManager.getConnection(url, user, password) opens the connection and logs in. On success the method prints a confirmation and returns the Connection, which the handlers use to run their insert, update, delete and select statements.

On success: the same message, and the Connection is returned.
On failure: getConnection throws SQLException, the calling handler's
catch (SQLException ex) receives it, and the reason appears in the TextArea.

Two things changed: the method header now says throws SQLException, and the try, the catch and the null are gone. Every handler below already ends in catch (SQLException ex), and that catch now covers a failed connection as well. The handlers compile unchanged with either version, so the programs below need no edits to use it.

StudentApp.java, complete#

The program has three parts: the buttons and the TextArea, then the five handlers, then the layout and main. Put together, it is one file:

A 400 by 300 window titled "JavaFX JDBC Example": five buttons stacked down the
left edge, 10 pixels apart, and a TextArea filling the space below them.
Nothing reaches MySQL until a button is clicked. Each click prints
"Database Connected Successfully!" on the console and puts one message in the TextArea.

Each handler is a lambda passed to setOnAction, so it runs every time its button is clicked (event handling). Inside, all five follow the JDBC path from the queries lesson:

• try-with-resources opens the connection and the statement (and, for Select, the ResultSet) and closes them in reverse order when the block ends, whether it ends normally or with an exception. Nothing leaks, however the handler exits.
• Statement or PreparedStatement. A plain Statement runs SQL that is fixed text, here the CREATE TABLE and the SELECT. A PreparedStatement is used wherever a value goes into the SQL. Each ? is a placeholder, numbered from 1 left to right and filled with setString or setInt before the statement runs. The values travel separately from the SQL text, so a value can never change what the statement does, which is why a PreparedStatement prevents SQL injection.
• executeUpdate or executeQuery. executeUpdate runs statements that change the database (CREATE, INSERT, UPDATE, DELETE) and returns a row count, which this program ignores. executeQuery runs a SELECT and returns a ResultSet.
• One message either way. The last line of each try writes a success message into the TextArea. If anything in the try throws an SQLException, the catch writes ex.getMessage() there instead.

IF NOT EXISTS lets “Create Table” be clicked again without an error, and AUTO_INCREMENT means MySQL picks each new id, which is why the insert names only name and age. The values themselves are fixed in the code: every click on “Insert Record” inserts John, aged 20, because the window has no field to type a name into. The employee program adds the fields. The Select handler builds its text in a StringBuilder that starts with Students: and gains one line per row: the id, the name and the age, separated by " - ".

The last lines build the window. new VBox(10, ...) stacks its children from top to bottom in the order given, with 10 pixels between neighbours. The VBox has no padding, so the controls touch the edges of the window. new Scene(root, 400, 300) makes the content 400 pixels wide and 300 tall. A Button in a VBox keeps the width of its label (it grows only if you call setMaxWidth(Double.MAX_VALUE) on it), while the TextArea stretches across the window and takes whatever height is left.

Run it in your head#

Start with an empty studentdb, with no students table yet, and click the buttons in this order. The middle column shows the SQL with its placeholders filled in.

The last Select still shows Students:, because the StringBuilder starts with that line before the loop runs, and a loop over zero rows adds nothing. A few clicks outside this order are worth predicting too, because they are the ones that catch people out:

• Insert Record before Create Table. MySQL refuses, and the TextArea shows Table 'studentdb.students' doesn't exist.
• Create Table a second time. IF NOT EXISTS turns the statement into a no-op, there is no error, and the TextArea still says Table created successfully!
• Insert Record twice. Two Johns, with ids 1 and 2. “Update Record” then changes both, because WHERE name='John' matches both rows, and “Delete Record” removes both. The messages stay singular.
• Update or Delete with no John in the table. Nothing changes, yet the TextArea says Record updated! or Record deleted!. The handlers ignore the count that executeUpdate returns, so they cannot tell.
• Insert Record after a delete. The new John gets the next id, 2 if the deleted row was 1. MySQL does not reuse AUTO_INCREMENT values.

EmployeeManagerApp: input fields and conditions#

The second program manages a table of employees. It has four jobs:

• Insert an employee. The user types a name, a department and a salary, and the program adds the row.
• Update salaries by department. Give every employee in one department a new salary.
• Delete Employee by Condition. Delete employees whose salary is less than a given amount.
• View by salary. Show every employee whose salary is above a given amount.

Read each job as one SQL statement. Insert is an INSERT with three values. Updating by department is an UPDATE whose WHERE names the department. Deleting below an amount and viewing above one are a DELETE and a SELECT whose WHERE compares the salary with a number. Every value the user supplies becomes a ? in a PreparedStatement. That gives the window its shape: three input fields (name, department, salary), four buttons, and a TextArea for the answers.

The employees table#

The program never creates the employees table itself, so until it exists every statement the program sends fails with Table 'studentdb.employees' doesn't exist. Run this once at the mysql> prompt. It goes in studentdb because the program reuses DBConnection, whose URL still names that database.

The columns are the ones the handlers use: name, department and salary, plus an idthat the View handler prints. You could equally give the program a “Create Table” button like StudentApp's.

EmployeeManagerApp.java, complete#

A 500 by 500 window titled "Employee Management - JavaFX JDBC": three empty fields
with grey prompt text, four buttons, and a TextArea. Each click prints
"Database Connected Successfully!" on the console and puts one result in the TextArea,
such as "Employee inserted successfully!" or "2 employee(s) updated!".

Compared with StudentApp, three things are new:

• Prompt text. setPromptText("Enter Name") shows a grey hint while the field is empty, in place of a separate label. A field that has the keyboard focus hides its prompt, so at launch, with the cursor in the first field, “Enter Name” stays hidden until you click somewhere else.
• Values from the fields. getText() returns whatever is typed, always as a String. Name and department go straight into setString; the salary is turned into a double by Double.parseDouble before setDouble.
• Row counts. Update and Delete keep the int that executeUpdate returns and report it, as in 2 employee(s) updated!.

Four buttons, one salary field#

The single salary field plays three roles: the salary to store (Insert and Update), the limit below which rows are deleted (Delete), and the limit above which rows are listed (View). Only Insert reads the name field. Update reads the department and the salary, and Delete and View read the salary alone, whatever the other fields contain. Both comparisons are strict: salary<? and salary>? leave out an employee whose salary equals the number typed, so that employee is neither deleted nor listed.

A worked session#

Four inserts. Start with an empty employeestable. Type each employee below into the three fields and click “Insert Employee”. The TextArea says Employee inserted successfully! four times, and the table holds:

Update Sales to 50000. The program never clears the fields, so they still hold Divya, IT and 39000. Change the department to Sales and the salary to 50000, leave Divya in the name field (Update never reads it), and click “Update Salary by Department”. The handler runs UPDATE employees SET salary=50000.0 WHERE department='Sales', two rows match, and the TextArea shows 2 employee(s) updated!. Anil's 35000 and Meera's 42000 both become 50000: SET replaces the value, it does not add to it. A 10 percent raise would be SET salary = salary * 1.10.

Delete below 30000. Type 30000in the salary field and click “Delete Employees with Salary < Value”. DELETE FROM employees WHERE salary<30000.0 removes Rahul, the only salary under 30000, and the TextArea shows 1 employee(s) deleted!.

View above 40000. Type 40000and click “View Employees with Salary > Value”. SELECT * FROM employees WHERE salary>40000.0returns Anil and Meera. Divya's 39000 is not greater than 40000, so she is left out. The window now looks like this:

The TextArea shows 50000.0 where MySQL shows 50000, because getDouble returns a double, and appending a double always writes a decimal point and at least one digit after it. (From ten million upwards Java switches to scientific notation, so a salary of 10000000 would appear as 1.0E7.) The rows arrive in id order here, but SQL promises no order at all without ORDER BY. Add ORDER BY salary DESC to the query if the order matters.

What the first versions get wrong#

Both programs work when every input is sensible and MySQL is up. Real users are less kind. Here are five weak spots, each with its fix, and then one handler with every fix applied.

A salary that is not a number#

Type 45,000 with a comma, or leave the salary empty, and click any of the four employee buttons. Double.parseDouble throws a NumberFormatException, with the message For input string: "45,000" or empty String. That exception is unchecked and is not an SQLException, so the handler's only catch does not match it (checked and unchecked exceptions). The try-with-resources still closes the connection on the way out. Then the exception leaves the handler, and JavaFX prints it on the console under Exception in thread "JavaFX Application Thread". The window stays open and the TextArea keeps its old text, which may well be Employee inserted successfully! from the previous click, so the user believes the insert worked.

All four handlers parse the salary field, so all four need the same fix, one more catch:

Salary "45,000":                   Salary must be a number, for example 45000
Salary "50000", department "Sales": 2 employee(s) updated!

The two catch blocks can go in either order, because neither exception type is a subclass of the other. A multi-catch, catch (SQLException | NumberFormatException ex), also compiles, but then the user reads For input string: "45,000", which explains less than a sentence you write yourself. Double.parseDouble ignores spaces at either end, so " 45000 " is accepted. Commas, currency signs and letters are not.

A connection that is null#

This is the DBConnection problem from earlier, seen from the window. With the first version, the four failures below look identical to the user: they click, and nothing happens, while the console collects two stack traces per click. With the version that throws SQLException, each failure reaches the handler's catch and the TextArea names the cause:

These four cover most first-run failures of a JavaFX and JDBC program, and with the better DBConnection the window tells you which one you have.

Empty fields#

An empty TextField returns an empty string, not null, and MySQL is happy to store an empty string. Fill in only the salary, click “Insert Employee”, and you get an employee with no name and no department. Leave the department empty and click “Update Salary by Department”, and the WHERE becomes department='', which matches nothing: 0 employee(s) updated!. Check the fields before opening a connection, the way the registration form in form creation checked its fields before accepting them:

Name or department empty, or only spaces: Enter a name and a department first.
Nothing is sent to MySQL.

return ends the lambda early, so the handler never reaches the database. Trimming first means a field that holds only spaces counts as empty, and a stray space after Sales cannot spoil the department match.

A delete with no way back#

“Delete Employees with Salary < Value” removes every row below the number without a question. One slip, 300000 instead of 30000, deletes most of the company, and the delete is permanent the moment it runs, because JDBC commits every statement straight away unless told otherwise. Ask first, with a confirmation Alert:

Salary field "30000": a Confirmation dialog asks
"Delete every employee earning less than 30000?"
OK:                              1 employee(s) deleted!
Cancel, or closing the dialog:   Nothing was deleted.
Salary field "abc":              Type a salary limit first, for example 30000

The number is parsed first, so a typo never opens a dialog or a connection. showAndWait() shows the dialog and pauses the handler until the user answers, then returns the button pressed, wrapped in an Optional. orElse(ButtonType.CANCEL) treats anything other than a real answer as Cancel, so only a click on OK goes on to the DELETE. The window does not freeze while the dialog waits: showAndWait keeps the FX thread handling events for the dialog.

Messages that tell the truth#

StudentApp says Record updated! and Record deleted! even when no row matched, because it throws away the number executeUpdate returns. EmployeeManagerApp already reports the count. StudentApp needs only a few more lines:

One John in the table:  1 record(s) updated.
Two Johns:              2 record(s) updated.
No John:                No student called John, nothing updated.

Create Table needs a different fix. With IF NOT EXISTS the statement succeeds whether or not it created anything, and executeUpdate returns 0 either way, so the honest message is one that is true in both cases, such as Table students is ready.

All of it together: a careful insert#

Here is the employee Insert handler with every fix applied. It is the pattern for any handler that takes input: read and check everything first, connect last, and finish with a message either way.

All fields empty:        Enter a name, a department and a salary.
Salary "45,000":         Salary must be a number, for example 45000.
Salary "-500":           Salary cannot be negative.
MySQL stopped:           Database error: Communications link failure ...
Anil, Sales, 35000:      Inserted Anil (Sales, 35000.0), and the three fields clear.

Key idea

Every click should end with a message in the window, whatever happened: success, a typo, an empty field or a dead database. The console is for you. The TextArea is for the user.

Keeping the window responsive#

Every handler on this page runs on the JavaFX Application Thread. That one thread also redraws the window and delivers every click, key press and resize. While a handler is busy, none of that happens. The window is frozen until the handler returns.

Against MySQL on your own machine, a handler finishes in a fraction of a second and nobody notices. The freeze becomes visible when the database is slow to answer: a server across a network that is down or behind a firewall (the connection attempt can wait tens of seconds before giving up), a SELECT over a very large table, or an UPDATEwaiting for a row that another program has locked (MySQL waits up to 50 seconds by default). For the whole wait, the clicked button stays drawn pressed in, the fields and the other buttons do not react, and after a few seconds Windows adds “(Not Responding)” to the title bar while macOS shows its spinning wait cursor.

Moving the query onto a Task#

The fix is to do the database work on a second thread and come back to the FX thread only to show the result. JavaFX's tool for this is javafx.concurrent.Task. A Task is a Runnable, so a plain Thread can run it, exactly like the threads in the threads lesson, and it also carries the result back to the FX thread. Here is StudentApp's Select handler, converted. It needs one more import, import javafx.concurrent.Task;.

Click Select Records: the button greys out and the TextArea says "Loading..." at once.
While the query runs, the window can be moved and resized and the other buttons still respond.
When the rows arrive, the TextArea shows "Students:" and one line per row,
and the button comes back. If the query fails, the TextArea shows the error message.

1. 1call() is the only part that runs on the background thread. It does the JDBC work and returns the finished text. It has no catch: whatever it throws is kept by the task. The task itself is an anonymous class, as in inner classes.
2. 2setOnSucceeded runs on the FX thread after call() has returned. task.getValue() is the string that call() returned.
3. 3setOnFailed runs on the FX thread if call() threw. task.getException() is what it threw, for example the SQLException from a failed connection.
4. 4Before starting the thread, the handler disables the button and shows Loading.... The window now stays responsive, so the user could click again; the disabled button stops a second query from starting while the first is still running.
5. 5worker.start() runs call() on the new thread. It is start(), not run(), as with every thread: run() would do the work on the FX thread and freeze the window again. setDaemon(true) lets the program exit when the window closes, even if a query is still waiting.

The inner lambdas take ev rather than e, because a lambda parameter may not reuse the name of a variable that is already in scope, and e belongs to the outer lambda.

Key idea

Slow work goes on a background thread, controls stay on the FX thread, and a Task carries the result from one to the other.

The rule: controls belong to the FX thread#

Task fits a job that produces one result. When a thread you started yourself needs to change a control once, use Platform.runLater from javafx.application.Platform, as in Platform.runLater(() -> output.setText("Done")). It puts the lambda in the FX thread's queue, and the FX thread runs it as soon as it is free. Task uses the same mechanism to run your setOnSucceeded and setOnFailed handlers on the FX thread.

Writing one from a blank page#

A typical task gives you a table and three or four operations, like the employee example. Write the parts in this order and nothing gets left out:

1. 1Imports. javafx.application.Application, javafx.scene.Scene, javafx.scene.control.*, javafx.scene.layout.VBox, javafx.stage.Stage and java.sql.*. The same six lines in every such program.
2. 2DBConnection. A class with public static Connection getConnection() throws SQLException that returns DriverManager.getConnection(url, user, password), the URL being jdbc:mysql://localhost:3306/ followed by the database name.
3. 3The table. Write the CREATE TABLE statement you are assuming, so the column names in your SQL have something to match.
4. 4The class. public class LibraryApp extends Application, containing @Override public void start(Stage stage).
5. 5Controls. One TextField per input, each with setPromptText, one Button per operation, and one TextArea output.
6. 6Layout. VBox root = new VBox(10, ...), listing the fields, the buttons and the TextArea in screen order.
7. 7One handler per button. btn.setOnAction(e -> { ... }) holding a try-with-resources that opens the connection and a PreparedStatement, fills each ? from 1 upwards, runs the statement and writes the result to output. Then catch (NumberFormatException ex) if you parsed a number, and catch (SQLException ex) with output.setText(ex.getMessage()).
8. 8The right execute method. INSERT, UPDATE and DELETE use executeUpdate() and report the count it returns. SELECT uses executeQuery() and a while (rs.next()) loop that appends each row to a StringBuilder.
9. 9Scene and stage. new Scene(root, 500, 500), width first, then stage.setTitle(...), stage.setScene(scene) and stage.show().
10. 10main. public static void main(String[] args) { launch(args); }