Utilizing SQLite API

Create a Repository

The SQLite API only provides methods to read and write to your database; it does not care how this is done. While many patterns exist for managing databases, this guide will use the repository pattern. An explanation of what the repository pattern is and why it is useful are outside the scope of this guide.

Begin by creating a new class. Naming conventions typically dictate the class ends with "Repository", so we'll name ours ServerMemberRepository. Create this class and inject SqliteDatabase.

If you have multiple different objects you would like to read & write, it is recommended to create separate repositories for each of them.

Create a DTO

Although we have our repository, we have no way of representing our ServerMember object in code. To do so, we need to create a storage object, commonly referred to as a DTO.

You may either create the DTO in a separate file, or you may create it as a nested class in your repository. This is up to you.

We now have an object that represents a single row in our database. However, we still need to create our database schema to store these objects. Let's go ahead and do that now.

Create Database Schema

The SQLite API is designed to look and feel like an ORM, but it does not operate like one. Among other things, the SQLite API does not create tables, constraints, indexes, or any other database object. The developer is responsible for creating these objects.

Open ServerMemberRepository and create a method called createSchema(). In this method, we will execute raw SQL by calling the execute() method on SqliteDatabase. The sole role of execute() is to execute raw SQL. It is an "escape hatch" if the SQLite API does not provide the functionality you need. In the case of schema creation, it is mandatory.

Note that the activeMember and uuid columns are not defined as UUID or BOOLEAN types, as might be the case for MySQL or MSSQL. SQLite only supports a handful of datatypes, so we must be sure to use "real" supported types. The SQLite API automatically handles conversion of these types on read and write. This behavior will be discussed later.

Once the method is created, you will need to call it to actually initialize your database. The recommended way to do this is by injecting your repository into a lifecycle class and call the method during onEnable(). Ultimately, how you do this is up to you.

Insert a New Record

To insert a new record, we will call the execute() method just like we did earlier when creating our database schema. However, this time we will provide some additional arguments:

In the above example, we have written a standard SQL query that you may see when using PreparedStatement. However, one major difference is the presence of all of those Param methods.

Param is a record provided by the SQLite API that contains methods for binding Java types to SQLite types. When you provide a Param to the execute() method, the SQLite API will convert the type to the appropriate SQLite type and replace the ? placeholders with the converted values.

Param contains several methods for the most common SQLite types. These methods are:

i32(Integer v)

Converts a Java Integer to a SQLite INTEGER.

i64(Long v)

Converts a Java Long to a SQLite BIGINT.

f64(Double v)

Converts a Java Double to a SQLite DOUBLE.

text(String v)

Converts a Java String to SQLite TEXT.

blob(byte[] v)

Converts a Java byte[] to a SQLite BLOB. This method uses raw bytes as the value to write.

uuid(UUID v)

Converts a Java UUID to SQLite TEXT.

bool(Boolean v)

Converts a Java Boolean to a SQLite INTEGER, writing 1 or 0 automatically.

The return result of execute() is an int which represents the number of rows affected. You may use this to validate the success or failure of your query.

Querying Data

We've successfully inserted data using execute(), so now let's query data! As we know, execute() only returns the number of rows affected, so we cannot use it to query data. So, how do we query data, then? We use query() and queryOne(), of course!

For this example, we will use queryOne(), however both methods are the exact same. In fact, queryOne() calls query() under the hood. The difference is, after it receives the result, it calls List#getFirst() to return the first result. Be sure, if you're using queryOne(), that you actually filter your data to return only one result.

Let's query our database for the player named "TerrorByte" that we inserted a moment ago:

The query() and queryOne() methods both take in at minimum two arguments. The first is raw SQL, just like you're used to writing. The second is something called a RowMapper. A RowMapper is a function that provides you with a Row, a lightweight wrapper around a ResultSet. Whatever the return type of the RowMapper is the return type of the query method.

If Param is for writing data, then Row is for reading data. The SQLite API will execute the query, mapping any parameters you provide accordingly, and then wraps each result in a Row before sending them through the RowMapper lambda you provide. This allows you to call the Row methods to convert SQLite data back into Java primitives.

In our example, we are creating a new ServerMember record, and for each argument we are calling the appropriate method for the type. Each method requires the column name which contains the data to convert. The methods in Row are the exact same as in Param, they just work backwards as previously stated.

Using Transactions

Data integrity is an important aspect of working with databases. To ensure data is updated in an "all-or-nothing" manner, you man use transactions.

To use transactions, call the transaction() method and provide it one or more queries to execute. Work can only be an execute(), query(), or queryOne() method. Transactions do not support Convenience Methods (discussed in a later guide).

The transaction method takes a lambda which represents the entire transaction. Under the hood, the SQLite API temporarily disables auto-commit and executes the transaction statements. If the statements succeed, the transaction is committed. If any of the statements fail, the transaction is rolled back.

The lambda must return a value, even if that value is null. The return value of the lambda is the return value of the transaction method. Because the return value is whatever the developer defines, transactions can be very beneficial depending on how they're used.