• JDBC
  • Setting Up JDBC with SQLite
  • Select Statements
  • Insert, Update, Delete
  • Auto-Commit

JDBC

JDBC is the Java Database Connectivity API. It’s a set of classes that allow us to connect to a database and execute SQL statements.

Setting Up JDBC with SQLite

We can add JDBC to our project using Gradle:

dependencies {
    implementation group: 'org.xerial', name: 'sqlite-jdbc', version: '3.36.0.3'
}

In order for JDBC to work with a database, it needs to know where the database is. We give it this information in the form of a JDBC URL:

String databaseURL = "jdbc:sqlite:database_filename";

Let’s break that down:

• jdbc: we’re using JDBC
• sqlite: we’re using a SQLite database (different database systems have subtle differences, so it’s important for JDBC to know what it’s working with!)
• database_filename: this is just the database file. If your database is called database.sqlite3, then the URL is jdbc:sqlite:database.sqlite3.

From here, we create a Connection object, which accesses the database and allows us to run queries and retrieve results.

import java.sql.*;
Connection connection = DriverManager.getConnection(databaseURL);

Select Statements

Let’s say we want to run the SQL query SELECT * FROM users;. How do we do it? We’ll start by saving the query as a string:

String query = "SELECT * FROM users"; // note there's no semicolon in the string!

Next, we create a Statement object, which handles running one (and only one!) SQL query:

Statement statement = connection.createStatement();

Finally, we just pass the query into the Statement object:

ResultSet rs = statement.executeQuery(query);

The ResultSet rs is an iterator with all of the results of the query. This means we can use the .next() method to loop through them. Each attribute of the record can be accessed with a getter based on the data type, e.g., getString(), getInt(), etc.:

while (rs.next()) {
    int id = rs.getInt("id");
    String username = rs.getString("username");
    System.out.println("User " + id + ": " + username);
}

Note that there’s no simple size() function for a ResultSet, so you’ll need to find some way to count the number of results yourself.

Insert, Update, Delete

The process for non-SELECT statements is very similar until the last step. We still create the query string and the Statement object:

String query = "INSERT INTO users (username) VALUES (mst3k)";
Statement statement = connection.createStatement();

However, instead of running executeQuery(), we run executeUpdate(), which lets us change the data in the database instead of simply reading it:

int rowCount = statement.executeUpdate(query);

rowCount tells us how many rows were affected by what we just ran.

Auto-Commit

By default, JDBC will automatically save your changes to the database (a feature known as auto-commit). This can be convenient, but in many cases we want more control about when changes are actually saved. To turn auto-commit off, just add this line of code:

connection.commit();

We can also undo any uncommitted changes we’ve made by running this:

connection.rollback();

This will reset the database to how it was immediately following the last commit.