Introduction

There are various libraries you can use to persist data in a database within Kotlin. One of the leading libraries is Exposed, which was developed by JetBrains, the developers of Kotlin itself. Exposed provides a high level interface to query a database, in which you can write your queries using Kotlin rather than having to write the SQL itself. However the Kotlin queries are still written with SQL semantics, e.g. you include where clauses in your code.

Introduction to JDBC

Before we look at Exposed, we are going to take a look at JDBC, because Exposed works with JDBC. JDBC stands for Java Database Connectivity and provides a common standard for connecting to any supported database, such as SQLite, Oracle, MySQL, PostgreSQL and so on. The great advantage of JDBC over the "native" methods for connecting to each database is that the same code (either the low-level JDBC API, or a higher-level abstraction such as Exposed) can be used to connect to different databases. The only thing you need to change is the driver and connection.

JDBC drivers

JDBC connects to a range of different databases using drivers. Each database has its own driver, and the driver provides the interface between JDBC and the native database. In other words, the driver translates the common interface that JDBC provides into the specific implementation details needed for each type of database. So there is a MySQL driver, an Oracle driver, and so on.

The diagram below shows a typical architecture of a JDBC application, showing the different components and how they interact. Note that not all databases include all layers.

As can be seen, the JDBC API of your application (common to all databases) communicates with the driver. The driver would then typically communicate with a Java API for your specific database (e.g. SQLite) which then may use a native (C or C++) API for that database to actually communicate with the database itself - as most native database APIs are written in C or C++.

The connection details (the connection string) is different depending on which driver and database are being used. For example:

The jdbc: denotes the protocol, which confirms we are using JDBC. The next part of the string is the specific database we are using: sqlite here. The final part of the string is the .db file storing the database. This will be loaded from the main project folder by default.

Working with Exposed

Exposed concepts

When working with Exposed, you will be using several concepts from the API.

• Table. Each database table is represented using an instance of the Table class. When you want to query a given table, you subclass Table, or you can create a singleton object (see below).
• Transaction. You must perform your database queries within a database transaction, referenced using the Transaction class.
• Query. You perform your queries using an instance of the Query class. Once the query has been executed, Queries can hold database results, which you can iterate through.
• ResultRow. Each row returned by a SELECT query is represented by a ResultRow, which you can index to obtain the individual columns
• Column. A class representing a database column. You use instances of Column to index the ResultRow objects obtained by looping through the database results.
• Statement. Statements represent a statement that is currently being executed, such as InsertStatement or UpdateStatement.

Exposed's default API is known as the DSL (Domain Specific Language) API. What does this mean? Kotlin allows us to create "meta-languages" for performing specific tasks using very clean and intuitive code, making use of lambda functions extensively. In the case of Exposed, we use a "meta-language" which resembles SQL in its logic, yet uses Kotlin code.

We will look at DSLs in more detail in the Mobile Application Development module. For now, you can find out more in the Kotlin documentation.

Setting up an Exposed project

You should select Gradle as your build tool when creating a new Exposed project in IntelliJ IDEA:

You need to specify the Exposed dependencies in the build.gradle.kts file. Replace the existing dependencies block with the following:

and also specify the version of Exposed in gradle.properties (1.0 release candidate 3; a full 1.0 release will arrive soon):

We have added the core and jdbc sub-modules of Exposed as dependencies, as well as the Xerial SQLite JDBC driver. Once they are added, ensure you sync your build.gradle.kts to download the dependencies by clicking on the resync icon (a small elephant, which is the logo for Gradle, with a "resync" image):

Connecting to a database with Exposed

When connecting to a database with Exposed, you must provide the JDBC connection string and driver, detailed above. Here is an example:

Here, jdbc:sqlite:wadsongs.db is the connection string (i.e. we are connecting to an SQLite database named wadsongs.db) and org.sqlite.JDBC is the driver, in this case the Xerial SQLite JDBC driver (the recommended SQLite JDBC driver for Exposed, which we included in our build.gradle.kts).

Working with tables

We can either work with an existing table in an existing database or create tables from scratch. In either case, we must work with objects which inherit from the Table class. Here is an example:

• First of all note the syntax

This looks like inheritance but we are using object, not class. Why? We are creating a singleton object. A singleton is an object that will only ever have one instance, and is a feature of the singleton pattern (we will look at patterns next week). This makes sense here. We are only working with one database and only one instance of a given table within that database. So rather than creating a class called Wadsongs, we create a single-instance object, or singleton, instead. When we need this object later on, we refer to it by its name, Wadsongs.

• As the Wadsongs singleton object inherits from Table with an argument of "wadsongs", the data will be stored in, and read from, the table wadsongs.
• In the body of our Wadsongs object we define a series of attributes representing each column in the database. These are Column objects. Note how we initialise them via methods corresponding to their SQL types, and pass in the column name. Additionally, we mark the id as auto-incrementing by calling the autoIncrement() method.
• Note how we specify the primary key. We override the primaryKey attribute of the superclass by creating a PrimaryKey object using the id column. So, id will be the primary key of the table.

You can find out more about working with tables in the official documentation. This includes examples of some topics we will not cover here, such as foreign keys.

Creating tables

If our database does not contain the required tables already, we need to create them. We do this with the method SchemaUtils.create, passing our table object in as an argument. For example:

This creates two tables corresponding to the Songs and Users singleton Table objects. Note how all database queries need to be placed within a transaction. transaction is a function which creates a Transaction object, and executes the given lambda so that this within the lambda refers to the Transaction that has been created. Inside the transaction we create the two tables we need.

CRUD operations

CRUD (Create, Retrieve, Update, Delete) describes the standard data operations of creating data, looking up data, updating data and deleting data (in SQL, INSERT, SELECT, UPDATE and DELETE respectively). Here is how we perform CRUD operations with Exposed. You can find out more in the official documentation.

Retrieval (SELECT statements)

Here is an example of some code to perform a SELECT statement to find all rows in the table.

selectAll() selects all columns; as it is not followed by anything else, it does a SELECT with no WHERE clause. It returns a Query object which can be looped through : each time we iterate, a new ResultRow will be obtained. So a complete example to display all results might be:

Note how we iterate through the result. In the forEach lambda, it will refer to the current ResultRow. We then use our Column objects to index the ResultRow to find the values in the columns for the current row, and in this example, display them.

SELECT with a WHERE clause

Here is an example of a SELECT with a WHERE clause.

Note how we chain a where() call onto the results of selectAll(). This takes a lambda as an argument and this lambda should specify how we will will filter the results to find only those which meet a certain condition. Here, we are selecting only those rows where the Wadsongs.artist column has a value equal to (using the eq() method) the artist that the user is searching for, i.e. chosenArtist.

Infix functions

We could also write the above example as:

What have we done here? eq is a method, but a special type of method called an infix function. Infix functions (marked with the keyword infix) can be called using a special syntax in which the dot and the parentheses are omitted. This leads to more readable code (note how the second example reads more like a human language construct than the first).

Another example of an infix function is until which returns a Range object representing a range of values up to, but not including, its argument. For example:

loops from 1 to 9. This is equivalent to:

until is actually an infix function which returns an IntRange object up to, but not including, the argument. Furthermore, note how until() is a method of the constant integer 1. This is because, in Kotlin, even primitive types, such as Int, are classes, with their own methods.

SELECTing specific columns

We can select specific columns only by using select() rather than selectAll() and passing in a series of arguments consisting of the Column objects we want to select. For example:

SELECT where only one row is returned

In cases where only one row is returned you can use the singleOrNull() method of Query to pull out the single row in the results. If no row was returned, null will be returned instead. The return value of singleOrNull() is thus a nullable ResultRow, i.e. ResultRow?.

INSERT statements

To INSERT a new record you use the insert() method of Table, using your singleton object. This takes a lambda which will be run by the insert() method. In this lambda, it refers to an InsertStatement object which can be indexed by column. So you set the appropriate columns to the values you want to insert:

Note that insert() returns the completed InsertStatement object, which will now have the value of the id column filled in. So to access the newly allocated ID, we index this InsertStatement using the Column object Wadsongs.id.

UPDATE statements

The pattern for UPDATE statements combines aspects of INSERT and SELECT. You call the update() method of your Table and supply a lambda which performs the update. Inside the lambda, it will refer to an UpdateStatement in which you can set the columns to values in the same way as for INSERT. However, update() needs a condition which specifies the rows which will be updated. For example:

In this example we find all rows containing the misspelt artist "The Beetles" and updates them to "The Beatles". Note that update() returns the nunber of rows that were updated.

DELETE statements

DELETE statements are quite easy as we just have to supply a condition. Here is an example:

In this example we delete the song where the ID is equal to 123. Similar to update(), deleteWhere() returns the number of rows that were deleted.

Data Access Objects and Data Entities

When writing object-oriented code to interact with a database or other data source (e.g. a file, or a network data source), we typically make our code easier to read, and thus maintain, by making use of data access objects (DAOs) and data entities. The DAO approach allows you to keep all your database interaction code in one place, keeping the rest of your code "cleaner" and easier to maintain.

• What are data entities and data access objects?
  • A data entity is a standard Kotlin object representing an individual entity in your table, e.g. a Student object (from the Student class you have already written) representing a record in a students table
  • A DAO provides an interface to your table as a whole, and might contain methods such as findStudentById() or findStudentsByName() (for a students table). The methods of the DAO would either return data entities or take them as parameters.
  • Exposed comes with its own DAO API, however to help you understand the concept of DAOs more clearly, we are going to write our own, using the Exposed DSL API.
• Typically we implement a DAO as an interface which specifies a number of methods which will interact with a data source, and then provide an implementation of that interface for a specific data source. This means that, as the interface defines a series of methods with parameters and return types which will always be present in the DAO, we can easily substitute one data source (e.g. database) with another (e.g. network data source) simply by changing the exact DAO class. The method calls will not need to be changed as they are defined by the interface.

Example of a DAO for the HitTastic! database

We will implement a DAO as an interface which specifies a number of methods which will interact with a data source, as discussed above.Below is a Song data class and a WadsongsDao interface specifying some typical CRUD operations. Note how the interface has no dependency on Exposed.

We then provide an implementation for the DAO which supports a specific data source/database API, in this case Exposed:

DAO - Explanation

• Note how the DAO contains a series of methods which perform various database operations and the data entity (Song) represents an individual song in the database
• The rest of our Kotlin code would interface with the DAO, rather than using the Exposed DSL directly. For example, your other classes would call the DAO's findSongById() method to find a song using its ID.
• Many of the methods contain a Song object as a parameter; the DAO will take this Song object and perform the appropriate operation using the data contained within it
  • e.g. the updateSong() method will update the details of the song with the ID specified in the song object, to the details contained within the song object

• Note this code in the findSongById() method of the DAO:

  As we saw above, singleOrNull() will return either a single row (if one exists) or null (if one doesn't). In this code, if the given ID exists in the database, a row is returned, but if it isn't, null is returned. We then need to create a Song using the ResultRow from the database. However we only want to do this if the ResultRow is not null. A let block with a null-safety operator (?.) is an elegant way of only performing an operation if the subject of the let call is not null. It takes a lambda, containing an operation to be performed. Inside this lambda, it will refer to the subject of the let call (i.e. resultRow). So if resultRow is not null, we will create a Song containing the data from the returned row. Otherwise, the song returned from findSongById() will remain null.

Exercise

1. Implement a console-mode Exposed/SQLite CRUD application for your university application. It should make use of the Student class (from previous weeks) and allow the user to:
   • Add a new student, specifying name, course and mark. The student ID will be the primary key in the table and should auto-increment.
   • Search for students by course
   • Search for student by ID, returning null if one cannot be found.
   • Delete a student by ID
   • Edit a student's details (name, course, mark). The user should enter the student ID and new student details, and then the application should update the details of the student with that ID.

Use this version of Student:

2. Try re-implementing using a DAO, if you haven't already.

3. Try converting your Compose application from Week 8 to use an Exposed database to store the students. So when a student is added, the student should be added to the database and when the user searches for a student by course, the database should be searched (and the state list updated). Use the version without University. You don't need to worry about the difference between undergraduate and masters' students; just use the Student class given above.

Older versions of Exposed

The current version of Exposed is 1.0.0-rc-3, the third release candidate for Exposed 1.0. It's possible you may need to use an older version of Exposed (0.61.0) on environments with older Kotlin (e.g. 1.9). If this is the case, you will need to change your imports as follows in your main():

and in your table objects: