Managing data can be a nightmare. Flat files can be slow to load – databases need servers and networking and maintenance…

Fortunately, SQLite is where speed and simplicity meet in the middle.

JavaFX can be connected to SQLite in 4 simple steps:

  1. Include the SQLite JDBC library in your project
  2. Include java.sql and the JDBC driver in your module-info.java file
  3. Connect to the database using the java.sql.Connection object
  4. Interact with your database using SQL syntax and PreparedStatement objects

What you’ll get from this article

In this article I want to walk you through the steps of connecting your JavaFX application with an SQLite database.

Table of contents:

The App you’ll create:

The app itself is broken into layers, meaning you can reuse classes like the data-access utility classes for other projects, or swap out persistence layers for other forms of connectivity.

A simple JavaFX app with database connections with SQLite

What you’ll need

90% of the work here is going to be in creating the data structures you need to connect to and interact with the database.

You will need the database file in order to create the app. You can find it here.

In fact, if you want to see how it pieces together, the entire project is on my GitHub here.

1. Include JDBC driver in your project

To include the SQLite JDBC driver in your application, you’ll need to do two things. Firstly, import the driver code into your app, and then set up your app to know it’s there.

Option 1: Maven

Because I manage everything through Maven, I tend to add depenencies from Maven Central, which you can tag onto your dependency list, which already includes JavaFX. Check out the latest version of the SQLite JDBC in Maven Central, which also has preformatted code snippets you can paste into your pom.xml.

<dependencies>
    <!-- Other dependencies... -->
    <dependency>
        <groupId>org.xerial</groupId>
        <artifactId>sqlite-jdbc</artifactId>
        <version>3.36.0</version>
    </dependency>
</dependencies>

Option 2: Jar

If you prefer to import dependencies as a JAR, you can also download the JAR for the SQLite JDBC driver by navigating to a specific version (for example 3.36.0.1) and finding the downloadable JAR:

Once you have the dependency inside your project, you’ll need to tell Java that you want to use the SQLite implementation of the Java JDBC. To do that, you need to load the class using Java’s ClassLoader and then register the driver with the DriverManager object, which is part of the java.sql package.

The dependencies needed by JavaFX app to connect with a SQLite database

I usually do this inside the main() method, or the JavaFX-specific start() method – or hide them away in a database connectivity later, depending on how you want to implement it.

Class.forName("org.sqlite.JDBC"); //force Java ClassLoader to load class
DriverManager.registerDriver(new org.sqlite.JDBC()); //register class with DriverManager

Of course, you’ll need to wrap this to run it, because it throws some errors, so here’s something you can copy and paste:

public static void main(String[] args) {
    checkDrivers();    //check for driver errors
}
private static boolean checkDrivers() {
    try {
        Class.forName("org.sqlite.JDBC");
        DriverManager.registerDriver(new org.sqlite.JDBC());
        return true;
    } catch (ClassNotFoundException | SQLException classNotFoundException) {
        Logger.getAnonymousLogger().log(Level.SEVERE, LocalDateTime.now() + ": Could not start SQLite Drivers");
        return false;
    }
}

Finally, before this code can execute, we need to tell Java’s module system that your project intends needs to use code from other modules.

To do this, we need to modify our module-info.java file. We’ll include a reference to java.sql and to org.xerial.sqlitejdbc, which is the specific driver we’ll be using.

It probably already has a few lines in from being a JavaFX application. This is what mine looks like for this App:

module com.edencoding {
    requires java.sql;
    requires org.xerial.sqlitejdbc;
    requires javafx.controls;
    requires javafx.fxml;
    opens com.edencoding.models to javafx.base;
    opens com.edencoding.controllers to javafx.fxml;
    exports com.edencoding;
}

2. Connect your Java application with a database

Once your project recognises the the java.sql package, creating a connection to the SQLite database object is relatively simple. The entire Java JDBC is centred around the concept of providing the same interface for all database connections, but with different back-end drivers.

To connect a JavaFX application to a SQLite database file, the java.sql Connection object requires that you specify the file location along with the type of database to which you want to connect.

Connection connection = DriverManager.getConnection("path/to/database.db");

The way we do this is pretty similar to a web address using an https protocol, or a file using the file:// protocol. Here, we’ll use the jdbc protocol for SQLite.

That consists of the jdbc:sqlite: identifier, plus the location of the database file.

URLs:

Absolute files:

Relative files:

“jdbc:sqlite:http://web.address/to/databse.db“

“jdbc:sqlite:C:/absolute/path/to/database.db“

“jdbc:sqlite:relative/path/to/database.db“

A warning on relative locations

If your database is within your project resources, a safe way to access relative paths from within your resources is using the getClass().getResources().toExternalForm() URL resolver.

In this case you’ll need to use SQLiteExampleApp.class rather than getClass() to use it from the main method, which has a static context.

The benefit this brings is that it can automatically navigate to resources nested within a resources folder.

Project
│   
└───sqlite
     └───src
        └───main
            ├───java
            │   │   module-info.java
            │   │   
            │   └───com
            │       └───edencoding
            │               SQLiteExampleApp.java
            │                   
            └───resources
                 └───com
                    └───edencoding
                        └───database
                                database.db

To access the database in the resources folder above, we just need to write:

SQLiteExampleApp.class.getResource("database/database.db").toExternalForm()

…and Java works out that SQLiteExampleApp is in the same package as the resources database folder automatically.

If you want to learn more about where to put resources in a JavaFX application, check out this full guide. It goes through how to reference resource files from Java code, CSS and FXML all in one.

Once you’ve created your location string correctly, you simply need to pass it to the DriverManager.getConnection() method as a String. You’ll need to wrap it in a try-catch block again, but here’s some code you can copy and run.

Just don’t forget to replace my location String with one relevant to your project.

private static final String location = SQLiteExampleApp.class.getResource("database/database.db").toExternalForm();
public static void main(String[] args){
    checkDrivers();    //check for driver errors
    Connection connection = connect(location);
}
private static Connection connect(String location) {
    String dbPrefix = "jdbc:sqlite:";
    Connection connection;
    try {
        connection = DriverManager.getConnection(dbPrefix + location);
    } catch (SQLException exception) {
        Logger.getAnonymousLogger().log(Level.SEVERE,
                LocalDateTime.now() + ": Could not connect to SQLite DB at " +
                        location);
        return null;
    }
    return connection;
}

3. How to get data to and from your SQLite database

Now, we need to create the objects that will interact with our database. I say objects deliberately. The temptation – especially with small apps – is to put all of your SQL code straight into the controller (I did this once in a 20,000 line application that was a nightmare to maintain…)

For that reason, I usually use a persistence layer to separate the database-interaction logic from the business logic of the app itself.

How to structure a JavaFX database application

Half the battle is structure, and while adding a persistence layer to your app can create a little more code, it pays off in the long run in terms of maintainability.

The structure of a simple JavaFX app for SQLite connectivity

I will stop short here of implementing a full data access object pattern, which would involve programming to a DAO interface, which is then implemented.

For larger apps, it’s worth considering, but here it just adds another object when it’s not 100% necessary.

What I will create is a CRUDHelper class, which segregates all of the specific SQL query building from the access objects. That way, as your domain model grows, you don’t have to needlessly repeat the code

Now we’ve got a helper class to manage all of the SQL itself, I want to create a data access object for Person objects that can create, read, update and delete Person records from our database while maintaining a useful cache for us to display in our app.

I’ll do each in turn

a. Reading data from SQLite

I’ll do reading first because it’s crucial to pulling data from the database and displaying it in our view. We’ll want to create two functions:

  1. Retrieve all records from the database
  2. Retrieve just one record based on an id.

1. Retrieve all Person records from the database

To get all every Person record in our database, we’ll construct a query of type “SELECT * from Persons” (* is a select-all wild card), and then execute that query to pull the results.

Once we’ve pull the results from the database, we iterate through them and cache them in an ObservableList so we can display it in our view later.

private static final String tableName = "Persons";
private static final ObservableList<Person> persons;
private static void updatePersonsFromDB() {
    String query = "SELECT * FROM " + tableName;
    try (Connection connection = Database.connect()) {
        PreparedStatement statement = connection.prepareStatement(query);
        ResultSet rs = statement.executeQuery();
        persons.clear();
        while (rs.next()) {
            persons.add(new Person(
                    rs.getString(firstNameColumn),
                    rs.getString(lastNameColumn),
                    rs.getInt(ageColumn),
                    rs.getInt(idColumn)));
        }
    } catch (SQLException e) {
        Logger.getAnonymousLogger().log(
                Level.SEVERE,
                LocalDateTime.now() + ": Could not load Persons from database ");
        persons.clear();
    }
}

Note: Storing the table name as a String within the DAO is a good idea. Then, if we need to change it later, it’s much easier to change a single parameter than to search through every SQL query in all our methods.

I’m actually going to add this to a static block inside my class, so as the class is accessed for the first time, it populates the list.

private static final ObservableList<Person> persons;
static {
    persons = FXCollections.observableArrayList();
    updatePersonsFromDB();
}

2. Reading individual records based on an id

Now we have all of the Person records cached in our app, we can search through all of the cached records and retrieve it based on the person’s id.

Here I’m going to retrieve it as an Optional because I haven’t validated the input id in any way, so it’s plausible the search might come up empty.

public static Optional<Person> getPerson(int id) {
    for (Person person : persons) {
        if (person.getId() == id) return Optional.of(person);
    }
    return Optional.empty();
}

Larger Apps: For more complicated apps, with a larger user base, it might be good to implement protocols to individually pull records from the database and update the cache when they’re different than expected. You could also periodically check for database changes using background tasks, which is quite common too.

b. Creating records in SQLite

An important part of our App is going to be creating new records. This involves creating an SQL query of type “INSERT INTO Persons (firstName, lastName, age) VALUES (“Ed”, “Eden-Rump”, 34)”.

We’ll need to be able to update the cache and the back-end database, which we’ll do one after another.

public static void insertPerson(String firstName, String lastName, int age) {
    //update database
    int id = (int) CRUDHelper.create(
            tableName,
            new String[]{"LastName", "FirstName", "Age"},
            new Object[]{lastName, firstName, age},
            new int[]{Types.VARCHAR, Types.VARCHAR, Types.INTEGER});
    //update cache
    persons.add(new Person(
            firstName,
            lastName,
            age,
            id
    ));
}

This uses the CRUD code I gave above, which simplifies the looks of this class, so check it out if you scrolled past it before.

c. Updating records in SQLite

Now we can create new records, we need to be able to update records that are already in the database. This involves creating an SQL query of type “UPDATE Persons SET firstName = “Ed”, lastName = “Eden-Rump” WHERE id = 12″.

Again, we’ll need to update the SQLite database file while maintaining our in-memory records.

    public static void update(Person newPerson) {
        //udpate database
        int rows = CRUDHelper.update(
                tableName,
                new String[]{firstNameColumn, lastNameColumn, ageColumn},
                new Object[]{newPerson.getFirstName(), newPerson.getLastName(), newPerson.getAge()},
                new int[]{Types.VARCHAR, Types.VARCHAR, Types.INTEGER},
                idColumn,
                Types.INTEGER,
                newPerson.getId()
        );
        if (rows == 0)
            throw new IllegalStateException("Person to be updated with id " + newPerson.getId() + " didn't exist in database");
        //update cache
        Optional<Person> optionalPerson = getPerson(newPerson.getId());
        optionalPerson.ifPresentOrElse((oldPerson) -> {
            persons.remove(oldPerson);
            persons.add(newPerson);
        }, () -> {
            throw new IllegalStateException("Person to be updated with id " + newPerson.getId() + " didn't exist in database");
        });
    }

Because there’s no validation check on the id you pass in with newPerson, I’ve included some checks in this code about whether anything was updated in the database. Obviously, if you have some in-app validation, you can remove that redundancy, but for now it makes the method a little safer.

This uses the CRUD code I gave above, which simplifies the looks of this class, so check it out if you scrolled past it before.

d. Deleting records from SQLite

Finally, we want to be able to remove records we no longer need. This involves creating an SQL statement of type “DELETE FROM Persons WHERE id = 12”.

public static int delete(String tableName, int id) {
    String sql = "DELETE FROM " + tableName + " WHERE id = ?";
    try (Connection conn = Database.connect()) {
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setInt(1, id);
        return pstmt.executeUpdate();
    } catch (SQLException e) {
        Logger.getAnonymousLogger().log(
                Level.SEVERE,
                LocalDateTime.now() + ": Could not delete from " + tableName + " by id " + id +
                        " because " + e.getCause());
        return -1;
    }
}

Again, I’ll use the CRUDHelper class I gave before so as you create more DAO objects for more complicated apps, you don’t have to repeat that code over and over again.

Now you have a fully functioning data access object that’s going to spit out Customer objects for your app, you need an app that’s going to show them to your users!

4. Create a view and controller to interact with your users

Once we have a data access object that maintains our ObservableList of Persons, we are 100% free to display these to our users in any way we want.

I’ll link everything together in two steps:

  1. Create the view and get it to display all the records in the database
  2. Hook up the buttons to add functionality to add, edit and delete records.

1. Create a view and populate it

Here, I’m going to grab code from the ‘how to style a TableView article‘ I wrote last year to give us a pre-styled TableView that I can really quickly hook into the PersonDAO. Here’s the CSS and FXML I’ll use to create the TableView.

So this is what it looks like ahead of populating it:

Now, inside the Controller, we need to link together the PersonDAO with the TableView. We’ll do this in two stages:

  1. Link the table’s columns with properties in the Person object
  2. Populate the table itself using the PersonDAO.

So firstly, to link the table’s columns with the Person object, we’ll use JavaFX’s PropertyValueFactory object to automatically link the columns with the properties of the Person object.

We’ll put that inside the initialize() method of the controller.

idColumn.setCellValueFactory(new PropertyValueFactory<>("id"));
firstNameColumn.setCellValueFactory(new PropertyValueFactory<>("firstName"));
lastNameColumn.setCellValueFactory(new PropertyValueFactory<>("lastName"));
ageColumn.setCellValueFactory(new PropertyValueFactory<>("age"));

Using PropertyValueFactory

Using the PropertyValueFactory object is really concise, but it does require reflection. That means you need to open up whatever package your Person model is in to allow that reflection to take place.

My Person object is inside com.edencoding.models, so I’ve added this line to my module-info.java:

opens com.edencoding.models to javafx.base;

If you want to avoid this, you can create a cell value factory callback manually like this:

idColumn.setCellValueFactory(new Callback<TableColumn.CellDataFeatures<Person, Integer>, ObservableValue<Integer>>() {
    @Override
    public ObservableValue<Integer> call(TableColumn.CellDataFeatures<Person, Integer> param) {
        return param.getValue().ageProperty().asObject();
    }
});

With that done, we just need to populate the table. So, just after the code above, also in the initialze() method, we’ll populate our table.

exampleTable.setItems(PersonDAO.getPersons());

Finally, we need to ensure the edit and delete buttons are all disabled if nothing is selected.. Again, this can go in the initialize() method of the controller.

editButton.disableProperty().bind(Bindings.isEmpty(exampleTable.getSelectionModel().getSelectedItems()));
deleteButton.disableProperty().bind(Bindings.isEmpty(exampleTable.getSelectionModel().getSelectedItems()));

2. Add functionality to add, edit and delete buttons

Firstly, the “add” and “edit” dialogs are going to be virtually identical, so I want to create one utility method that can create the dialog for me, and then if I pass it a Person record, it will auto-fill the fields so I can edit them for editing functionality.

A simple dialog to add or edit records in a SQLite database using JavaFX
Fields are pre-populated for editing, or blank for adding new records.

a. “Add” button functionality

To create my “add” button functionality, I’ll link the button action in my FXML with a method in the controller:

<Button styleClass="add-button" text="Add" onAction="#addPerson"/>

Now I just need to create that method, which will create the dialog, passing in null so that the form is empty when it’s presented for the user. Then, if the user submits the form, it retrieves the new Person record, and adds it to the database using the PersonDAO.

public void addPerson(ActionEvent event) {
    Dialog<Person> addPersonDialog = createPersonDialog(null);
    Optional<Person> result = addPersonDialog.showAndWait();
    result.ifPresent(person ->
            PersonDAO.insertPerson(
                    person.getFirstName(),
                    person.getLastName(),
                    person.getAge()
            ));
    event.consume();
}

b. “Edit” button functionality

To create my “edit” button functionality, I’m going to do exactly the same thing, but as I create the method, I’ll

Here’s my FXML for the button:

<Button styleClass="edit-button" text="Edit" fx:id="editButton" onAction="#editPerson"/>

And the method, getting the row that’s currently selected and using it to populate the dialog.

public void editPerson(ActionEvent event) {
    if (exampleTable.getSelectionModel().getSelectedItems().size() != 1) {
        Alert alert = new Alert(Alert.AlertType.ERROR);
        alert.setTitle("Error");
        alert.setHeaderText("Person editing error");
        alert.setContentText("One person must be selected when editing");
    } else {
        Dialog<Person> dialog = createPersonDialog(exampleTable.getSelectionModel().getSelectedItem());
        Optional<Person> optionalPerson = dialog.showAndWait();
        optionalPerson.ifPresent(PersonDAO::update);
    }
    event.consume();
}

Just a quick note to say I haven’t validated the person before I update it. There’s no checking for whether a change has actually been made, so please add this if you think your app’s going to need it.

c. “Delete” button functionality

Deleting records is potentially the simplest thing here. Again, I’ll hook my button up in the FXML, and then find the ID from the currently selected row. Then all I have to do is delete the record based on the ID.

<Button styleClass="delete-button" text="Delete" fx:id="deleteButton" onAction="#deletePerson"/>

And the code:

public void deletePerson(ActionEvent event) {
    for (Person person : exampleTable.getSelectionModel().getSelectedItems()) {
        PersonDAO.delete(person.getId());
    }
    event.consume();
}

And that’s it!

You should now be running a fully-functioning app, linked to a local database, with basic functionality to display, add, update and delete information using a JavaFX interface.

Conclusions

Creating a JavaFX app to connect to an SQLite database is 90% about the structure. Connecting to any database means using Java’s in-built JDBC, which provides consistent object for connecting to any database. These include the Connection and PreparedStatement objects we used in this app.

Once you’ve connected your database with the raw Java code, you need to create JavaFX-compatible data models, and objects to help you load those objects into memory.

We did this by creating the Person object, which was loaded using the data-access object PersonDAO.

The data objects needed to load information in and out of memory, and sync with the database

Finally, once the underlying data structures are in place, we can create a simple View using FXML, style it with CSS and control it using Java code to create an MVC app with direct links to the SQLite database.

The controller and view of the JavaFX SQLite application

All of the code here is hosted on my GitHub, so check it out if you want to see how the whole app works together.