JDBC basics

Data that must survive a program restart — users, orders, products — is usually kept in a relational database: PostgreSQL, MySQL, SQLite. The data sits in tables, and you work with it in the SQL language. Java talks to any such database through JDBC (Java Database Connectivity) — a standard set of interfaces from the java.sql package. The code stays the same; only the driver and the database address change.

The driver

A driver is a library that implements the JDBC interfaces for a particular database. The lessons in this chapter use SQLite: it keeps a database in a single file or right in memory and needs no server installed. In a Maven project the driver is added as a dependency:

<dependency>
    <groupId>org.xerial</groupId>
    <artifactId>sqlite-jdbc</artifactId>
    <version>3.53.4.0</version>
</dependency>

In Gradle it's the line implementation("org.xerial:sqlite-jdbc:3.53.4.0"). To run a single file, download sqlite-jdbc-3.53.4.0.jar from Maven Central, put it next to Main.java as sqlite-jdbc.jar and pass it with -cp:

java --enable-native-access=ALL-UNNAMED -cp sqlite-jdbc.jar Main.java

The SQLite driver loads native code, and without the --enable-native-access option Java 25 prints a warning about it. All the examples in this chapter are run this way.

Connecting

A connection to the database is a Connection object. DriverManager.getConnection(url) opens it: from the address (URL) it finds a suitable driver itself. The address jdbc:sqlite::memory: creates a temporary database in memory. A connection is a resource, so it's closed with try-with-resources. The java.sql package isn't part of java.base, so even a compact file needs an import:

import java.sql.*;
 
void main() throws SQLException {
    try (Connection conn = DriverManager.getConnection("jdbc:sqlite::memory:")) {
        DatabaseMetaData meta = conn.getMetaData();
        IO.println("Database: " + meta.getDatabaseProductName() + " " + meta.getDatabaseProductVersion());
        IO.println("Driver: " + meta.getDriverName());
    }
}

Output:

Database: SQLite 3.53.4
Driver: SQLite JDBC

Almost every JDBC method can throw the checked SQLException, which is why main has throws SQLException.

Statement: SQL commands

A Statement runs SQL commands. For commands that change data or structure — CREATE TABLE, INSERT, UPDATE, DELETE — you call executeUpdate. It returns the number of affected rows:

import java.sql.*;
 
void main() throws SQLException {
    try (Connection conn = DriverManager.getConnection("jdbc:sqlite::memory:");
         Statement st = conn.createStatement()) {
        st.executeUpdate("CREATE TABLE products (id INTEGER PRIMARY KEY, title TEXT, price REAL)");
 
        int added = st.executeUpdate("""
                INSERT INTO products (title, price)
                VALUES ('Tea', 4.5), ('Coffee', 7.25), ('Cocoa', 5.0)""");
        IO.println("Rows added: " + added);
    }
}

Output:

Rows added: 3

The database fills the id INTEGER PRIMARY KEY column itself: 1, 2, 3. Long queries are convenient to write in """ text blocks.

ResultSet: reading rows

A SELECT query is run with executeQuery. It returns a ResultSet — a cursor over the result rows. next() moves to the next row and returns false when the rows run out. Column values are read with getString, getInt, getDouble by column name:

import java.sql.*;
 
void main() throws SQLException {
    try (Connection conn = DriverManager.getConnection("jdbc:sqlite::memory:");
         Statement st = conn.createStatement()) {
        st.executeUpdate("CREATE TABLE products (id INTEGER PRIMARY KEY, title TEXT, price REAL)");
        st.executeUpdate("INSERT INTO products (title, price) VALUES ('Tea', 4.5), ('Coffee', 7.25), ('Cocoa', 5.0)");
 
        try (ResultSet rs = st.executeQuery("SELECT id, title, price FROM products ORDER BY price")) {
            while (rs.next()) {
                int id = rs.getInt("id");
                String title = rs.getString("title");
                double price = rs.getDouble("price");
                IO.println(id + ". " + title + " — $" + price);
            }
        }
    }
}

Output:

1. Tea — $4.5
3. Cocoa — $5.0
2. Coffee — $7.25

Right after executeQuery the cursor sits before the first row, so next() is called before reading. Columns can also be read by number — rs.getString(2) — and numbering starts at 1.

Other databases

With PostgreSQL or MySQL the code doesn't change — only the driver in the dependencies (org.postgresql:postgresql, com.mysql:mysql-connector-j) and the address with a user name and password:

// src/main/java/shop/Database.java
Connection conn = DriverManager.getConnection(
        "jdbc:postgresql://localhost:5432/shop", "shop_user", System.getenv("DB_PASSWORD"));

For MySQL the address looks like jdbc:mysql://localhost:3306/shop. An SQLite database in a file is jdbc:sqlite:shop.db. The password isn't written in the code but taken, for example, from an environment variable.

Common mistake

If the driver isn't on the classpath — you forgot -cp sqlite-jdbc.jar or the dependency in the build — DriverManager can't find anyone to hand the address to:

import java.sql.*;
 
void main() throws SQLException {
    try (Connection conn = DriverManager.getConnection("jdbc:sqlite::memory:")) {
        IO.println("Connected");
    }
}

Running without the driver:

java Main.java

Output:

Exception in thread "main" java.sql.SQLException: No suitable driver found for jdbc:sqlite::memory:
	at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:638)
	at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:222)
	at Main.main(Main.java:4)

The same error appears with a typo at the start of the address, such as jdbc:sqllite:. Check that the driver is added and the address starts with the right prefix.