JAVA-250 · Lesson 1 of 12 · 26 min

Databases from Java

▶ Video coming soon A video version of this lesson is on the way.

On Friday, September 17, 2027, the first session of Hamza's persistence course met where his collections course had met the winter before: in Second Gear's parts room, from 2:00 to 4:00 in the afternoon, with Ingrid working from home on Fridays and walking over. StockRoom, the tool Hamza had built in April, had run the parts room all summer. It loaded Kofi's parts list from a file when it started, kept every count in a HashMap and a few TreeMaps while it ran, and wrote the file back when Kofi closed it. That had worked until Tuesday, August 24, when the parts-room computer restarted itself for an operating system update in the middle of the afternoon. StockRoom had not been closed, so it never saved, and the eleven sales and receipts entered since the morning were gone. Kofi rebuilt them from the receipts in the cash drawer.

"Memory is where a program thinks," Ingrid said. "A database is where a business remembers." This course is about connecting Java programs to databases: the JDBC API that every Java database library is built on, the patterns that keep database code tidy, and the Jakarta Persistence API (JPA) with Hibernate, which maps objects to tables for you. The prerequisites matter here. You need the object-oriented Java of JAVA-102 and the SQL of WEB-201, because this course writes SQL constantly but does not teach it from the beginning.

What a database gives a program

A file can store data, and StockRoom's file did. A database management system adds the things a file cannot give on its own. The first is durability: once the database says a change is saved, a crash or a power cut does not lose it. The second is concurrent access: the counter computer, the parts-room computer and, later, the shop's web service can all read and change the same data at once without overwriting each other's work. The third is integrity: the database itself refuses data that breaks its rules, such as a negative quantity or a booking for a bike that does not exist. The fourth is querying: a question such as "which parts are below their reorder level" is one line of SQL instead of a loop the programmer has to write and test. The last is transactions, which let several changes succeed or fail together, the subject of lesson 4.

The example below replays what happened on August 24 in a few lines. The program keeps the stock in a map, saves a snapshot only when asked, and then loses everything since the last save when it restarts. Nothing in it is wrong as Java; the design simply has no durable place to put each change as it happens.

Java Example · Main.java
import java.util.*;

public class Main {
    static Map<String, Integer> stock = new TreeMap<>();
    static Map<String, Integer> savedFile = new TreeMap<>();

    static void change(String code, int by) {
        stock.merge(code, by, Integer::sum);
    }

    public static void main(String[] args) {
        stock.putAll(Map.of("TUBE-700x25", 14, "BELL", 6, "CHAIN-11", 3));
        savedFile.putAll(stock);                      // saved when StockRoom was last closed

        String[] codes = {"TUBE-700x25", "BELL", "TUBE-700x25", "CHAIN-11", "BELL", "TUBE-700x25",
                          "BELL", "TUBE-700x25", "CHAIN-11", "TUBE-700x25", "BELL"};
        int[] changes = {-1, -1, -2, -1, -1, 10, -1, -1, -1, -2, 6};
        for (int i = 0; i < codes.length; i++) change(codes[i], changes[i]);
        System.out.println("In memory before the restart: " + stock);

        stock = new TreeMap<>(savedFile);             // the computer restarts; StockRoom loads its file
        System.out.println("After the restart:            " + stock);
        System.out.println("Changes lost: " + codes.length);
    }
}
Output
In memory before the restart: {BELL=9, CHAIN-11=1, TUBE-700x25=18}
After the restart:            {BELL=6, CHAIN-11=3, TUBE-700x25=14}
Changes lost: 11

What this code does

The program applies eleven changes to the map, the same number Kofi lost, and the in-memory counts are correct. When the restart replaces the map with the last saved copy, all eleven changes vanish, because nothing was written anywhere durable as they happened. A database fixes this by recording each change the moment it is made.

The relational model in one page

WEB-201 taught relational databases in depth, so this is a reminder rather than a lesson. A relational database stores data in tables. Each table has named columns with types, and each row is one record. A primary key is a column, or a set of columns, whose value identifies each row uniquely; the part code is a natural primary key for Second Gear's parts, because Kofi already uses codes such as TUBE-700x25 on every bin. A foreign key is a column whose values must match the primary key of another table, which is how a database records relationships: each part names its supplier by the supplier's id, and each rental booking names the bike by its tag. Constraints such as NOT NULL, UNIQUE and CHECK state rules the database enforces on every change.

Hamza's first job was to write StockRoom's data as tables. He kept money in whole cents, as every Second Gear program had since PartsDesk, because an INTEGER column holds 899 exactly while a floating-point column would hold something slightly different. The schema below is the one the rest of the course uses, run against SQLite to produce the result tables you will see in each lesson.

schema.sql (first part)
CREATE TABLE supplier (
    id   INTEGER PRIMARY KEY,
    name TEXT NOT NULL UNIQUE
);
CREATE TABLE part (
    code        TEXT PRIMARY KEY,
    name        TEXT NOT NULL,
    shelf       TEXT,
    quantity    INTEGER NOT NULL CHECK (quantity >= 0),
    reorder_at  INTEGER NOT NULL,
    pack_size   INTEGER NOT NULL DEFAULT 1,
    price_cents INTEGER NOT NULL,
    supplier_id INTEGER REFERENCES supplier(id)
);
CREATE TABLE stock_movement (
    id     INTEGER PRIMARY KEY AUTOINCREMENT,
    code   TEXT NOT NULL REFERENCES part(code),
    change INTEGER NOT NULL,
    reason TEXT NOT NULL,
    at     TEXT NOT NULL
);

What this code does

Three of the five tables in the sample database: suppliers, parts and the log of stock movements. The CHECK constraint makes the database itself refuse a negative quantity, the rule StockRoom used to enforce only in Java. Every movement row names its part by code, and the REFERENCES clause makes that a foreign key.

SELECT code, name, shelf, quantity, reorder_at
FROM part
WHERE quantity < reorder_at
ORDER BY code;

What this code does

The question Kofi asks every Friday, written as one SQL query. The WHERE clause keeps only the parts whose count has fallen below the level at which he reorders, and ORDER BY sorts them by code.

Result: the three parts below their reorder level in the sample data.
codenameshelfquantityreorder_at
BELLBrass bellC-0334
CHAIN-1111-speed chainB-1413
PAD-ROADRoad brake pads (pair)B-0735

The sample data matches the shop's canon: nine kinds of parts on shelves A-02 to C-03, the two suppliers, the six rental bikes (four city bikes, one cargo bike and one tandem) and a handful of bookings and movements. The real parts room has 612 kinds of parts, but nine are enough to see every idea.

Choosing a database

Databases come in two broad shapes. An embedded database runs inside the application's own process and stores its data in a local file; SQLite is the most widely used example in the world, and H2 and HSQLDB are embedded databases written in Java. A client–server database such as PostgreSQL, MySQL or SQL Server runs as a separate program, often on another machine, and many applications connect to it over the network at once. Embedded databases need no installation or administration, which makes them ideal for one computer and for tests; client–server databases handle many users, large data and security far better.

Embedded and client–server databases compared.
NeedEmbedded (SQLite, H2)Client–server (PostgreSQL)
InstallationA library and a fileA server process to install, secure and back up
Users at onceOne program, or a few on one machineMany programs on many machines
Good forDesktop tools, tests, small single-site systemsWeb services, shared business data
BackupsCopy the file safely (or use its backup command)Tools such as pg_dump and point-in-time recovery
Second GearStockStore on the parts-room computer, from January 2028Planned for the shop web service in JAVA-260

Ingrid and Hamza chose SQLite for the first version: one file, stockroom.db, on the parts-room computer, used by StockRoom's successor, which Hamza named StockStore. They agreed to write portable SQL and keep all database code behind interfaces, so that moving to PostgreSQL when the web service arrives in JAVA-260 would change configuration and a few queries, not the whole program. Lesson 11's migrations make that move repeatable.

JDBC: the standard way Java talks to databases

JDBC, Java Database Connectivity, is the standard Java API for relational databases. It is part of the JDK, in the java.sql package of the java.sql module, with a few extra types in javax.sql; Java 21 ships version 4.3 of the specification. JDBC is mostly a set of interfaces: the JDK defines what a connection, a statement and a result set can do, and each database vendor supplies a driver, a library that implements those interfaces for its database. Your code is written against the interfaces, so the same code talks to SQLite, PostgreSQL or SQL Server once the right driver is on the class path. Hibernate, Spring's JdbcTemplate, jOOQ and every other Java database library are built on JDBC underneath.

The core JDBC types.
TypePackageWhat it does
DriverManagerjava.sqlFinds the driver for a JDBC URL and opens connections
DataSourcejavax.sqlA factory for connections, usually a pool; the preferred way in applications
Connectionjava.sqlOne session with the database; makes statements and controls transactions
Statementjava.sqlRuns a fixed SQL string
PreparedStatementjava.sqlRuns SQL with ? placeholders whose values are sent separately
ResultSetjava.sqlA cursor over the rows a query returns
SQLExceptionjava.sqlThe checked exception every JDBC method can throw
Four stacked layers. At the top, StockStore's own code. Below it, the JDBC API in the JDK: DriverManager, DataSource, Connection, PreparedStatement and ResultSet. Below that, a row of three drivers side by side: SQLite, PostgreSQL and H2. At the bottom, the database each driver talks to: a stockroom.db file, a PostgreSQL server and an in-memory test database.
Application code is written against the JDBC interfaces; a vendor driver implements them for one database.

Here is the whole shape of JDBC code, the pattern every later lesson builds on. It opens a connection, prepares a query with a placeholder, binds a value, runs it and walks through the rows. Every object it opens is declared in a try-with-resources header, so all of them are closed even when something fails. It is shown as a listing because the SQLite driver is not part of the JDK; lesson 2 explains where drivers come from.

Java Example · LowStock.java
import java.sql.*;

public class LowStock {
    public static void main(String[] args) throws SQLException {
        String url = "jdbc:sqlite:stockroom.db";
        String sql = "SELECT code, quantity, reorder_at FROM part WHERE quantity < ? ORDER BY code";
        try (Connection conn = DriverManager.getConnection(url);
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, 4);
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    System.out.println(rs.getString("code") + " " + rs.getInt("quantity"));
                }
            }
        }
    }
}

What this code does

DriverManager finds the SQLite driver from the jdbc:sqlite: prefix and opens a connection to the file. The question mark is a placeholder, filled in by setInt before the query runs, and each call to next moves the result set to the following row. With the sample data and the SQLite driver on the class path, it prints BELL 3, CHAIN-11 1, PAD-ROAD 3 and PUMP-FLOOR 2 on four lines.

How SQL types meet Java types

Every column has an SQL type, and JDBC converts between it and a Java type when you read or write. The conversions are mostly what you would expect, with two traps. First, SQL has NULL and Java's primitive int does not, so getInt returns 0 for a NULL column and you must check wasNull afterwards or read an Integer with getObject. Second, the old date classes java.sql.Date, java.sql.Time and java.sql.Timestamp predate java.time; since JDBC 4.2 you can ask for a LocalDate or LocalDateTime directly with rs.getObject("start_date", LocalDate.class), and modern drivers support it.

Common SQL types and the Java types JDBC reads them as.
SQL typeJava type to useNotes
INTEGER, BIGINTint, long (or Integer, Long when NULL is possible)getInt returns 0 for NULL; check wasNull
VARCHAR, TEXTStringgetString returns null for NULL
NUMERIC, DECIMALBigDecimalExact decimals; never double for money
BOOLEANbooleanSQLite stores 0 and 1
DATELocalDategetObject(col, LocalDate.class)
TIMESTAMPLocalDateTime or OffsetDateTimeStore moments in UTC or with an offset
BLOB, BYTEAbyte[]Photos, files

The JDK includes the type codes themselves, in the constants of java.sql.Types and the newer enum JDBCType, and the legacy date classes convert to and from java.time. These parts of JDBC need no driver, so they run here.

Java Example · Main.java
import java.sql.*;
import java.time.*;

public class Main {
    public static void main(String[] args) {
        int[] codes = {Types.INTEGER, Types.VARCHAR, Types.DECIMAL, Types.DATE, Types.TIMESTAMP};
        for (int code : codes) {
            System.out.println(code + " -> " + JDBCType.valueOf(code));
        }

        LocalDate start = LocalDate.of(2027, 10, 2);
        java.sql.Date sqlDate = java.sql.Date.valueOf(start);
        System.out.println("java.sql.Date: " + sqlDate + ", back to LocalDate: " + sqlDate.toLocalDate());

        LocalDateTime received = LocalDateTime.of(2027, 9, 14, 10, 5);
        Timestamp ts = Timestamp.valueOf(received);
        System.out.println("Timestamp: " + ts + ", back: " + ts.toLocalDateTime());
    }
}
Output
4 -> INTEGER
12 -> VARCHAR
3 -> DECIMAL
91 -> DATE
93 -> TIMESTAMP
java.sql.Date: 2027-10-02, back to LocalDate: 2027-10-02
Timestamp: 2027-09-14 10:05:00.0, back: 2027-09-14T10:05

What this code does

Each type constant is just an int, and JDBCType.valueOf turns it into a readable name. java.sql.Date.valueOf and Timestamp.valueOf convert from java.time and back without changing the date or time. The trailing .0 on the Timestamp is its nanoseconds field, which the older class always prints.

Rows become objects

A query returns rows; a Java program wants objects. Turning one into the other is called mapping, and in plain JDBC you write it yourself, usually in one small method per table. Hamza already had a Part record from his testing course, with a code, a quantity, a reorder level and a pack size, so the mapping code reads each column by name and calls the record's constructor. The example below uses a list of maps to stand in for the rows a driver would return, so it runs without a database; lesson 3 does the same with a real ResultSet implementation from the JDK.

Java Example · Main.java
import java.util.*;

public class Main {
    record Part(String code, String name, int quantity, int reorderAt, int priceCents) {
        boolean low() { return quantity < reorderAt; }
    }

    static Part toPart(Map<String, Object> row) {
        return new Part((String) row.get("code"), (String) row.get("name"),
                (Integer) row.get("quantity"), (Integer) row.get("reorder_at"),
                (Integer) row.get("price_cents"));
    }

    public static void main(String[] args) {
        List<Map<String, Object>> rows = List.of(
                Map.of("code", "BELL", "name", "Brass bell", "quantity", 3, "reorder_at", 4, "price_cents", 650),
                Map.of("code", "CHAIN-11", "name", "11-speed chain", "quantity", 1, "reorder_at", 3, "price_cents", 3999),
                Map.of("code", "GRIPS", "name", "Lock-on grips", "quantity", 9, "reorder_at", 4, "price_cents", 1299));

        List<Part> parts = rows.stream().map(Main::toPart).toList();
        for (Part p : parts) {
            System.out.printf("%-9s %-15s %2d %s%n", p.code(), p.name(), p.quantity(), p.low() ? "LOW" : "ok");
        }
        int value = parts.stream().mapToInt(p -> p.quantity() * p.priceCents()).sum();
        System.out.println("Stock value: " + value + " cents");
    }
}
Output
BELL      Brass bell       3 LOW
CHAIN-11  11-speed chain   1 LOW
GRIPS     Lock-on grips    9 ok
Stock value: 17640 cents

What this code does

Each row is a map from column name to value, the way a driver hands over a row, and toPart reads the five columns it needs and builds a record. Once the rows are records, ordinary Java such as streams and methods on the record do the rest. The stock value is 3 × 650 + 1 × 3,999 + 9 × 1,299, or 17,640 cents.

"Look how little the mapping knows," Ingrid said. "One method per table, one place to change when a column is renamed." Hamza wrote that down as the first rule of the course. The second came from the August restart: every change to stock would be written to the database the moment it happened, never saved up for later.

Try it yourself

Count what a restart loses

StockRoom saved its file only at the times listed in saves, which are positions in the list of changes: a save at position 4 means the first four changes were saved.

The computer restarted after all the changes in changes. Print how many changes were lost, which is the number made after the last save, in the form Lost 3 of 9 changes.

import java.util.*;

public class Main {
    public static void main(String[] args) {
        List<Integer> changes = List.of(-1, -2, 10, -1, -1, -1, 6, -2, -1);
        List<Integer> saves = List.of(2, 6);
        int lost = 0;   // work out the changes made after the last save
        System.out.println("Lost " + lost + " of " + changes.size() + " changes");
    }
}

Try it yourself

Map rows to records

Each map in rows is one row from the bike table, with the columns tag and kind.

Write toBike so it builds a Bike record from a row, then print the bikes of kind CARGO or TANDEM, one per line, as SG-0007-AR CARGO.

import java.util.*;

public class Main {
    record Bike(String tag, String kind) {}

    static Bike toBike(Map<String, Object> row) {
        return new Bike("?", "?");
    }

    public static void main(String[] args) {
        List<Map<String, Object>> rows = List.of(
                Map.of("tag", "SG-0001-CI", "kind", "CITY"),
                Map.of("tag", "SG-0007-AR", "kind", "CARGO"),
                Map.of("tag", "SG-0003-CI", "kind", "CITY"),
                Map.of("tag", "SG-0012-TA", "kind", "TANDEM"));
        for (Map<String, Object> row : rows) {
            Bike b = toBike(row);
            System.out.println(b.tag() + " " + b.kind());
        }
    }
}

Where the course goes

Lessons 2 to 6 are plain JDBC: drivers and connections, prepared statements and result sets, transactions, connection pools and the data access object pattern that keeps SQL in one layer. Lessons 7 to 10 are JPA and Hibernate: mapping entities and relationships, querying with JPQL, and the performance traps of lazy loading. Lesson 11 manages the schema itself with Flyway migrations, and lesson 12 builds StockStore, the persistent inventory service, which Hamza planned to build in the first week of January 2028 and show Ingrid and Kofi on Friday, January 7.

Some parts of this course cannot run in your browser or on the page, because they need a database driver or a database server. Those parts are shown as listings with their output described or shown as a representative run, and every one of them uses only standard, documented APIs. The runnable examples use only the JDK, and their output is real.

Common mistakes

  • MistakeKeeping changes in memory and saving them later.

    InsteadWrite each change to the database when it happens.

  • MistakeStoring money as a double or a REAL column.

    InsteadStore whole cents in an INTEGER column, or use DECIMAL with BigDecimal.

  • MistakeReading a nullable INTEGER column with getInt and trusting the 0.

    InsteadCall wasNull afterwards, or read it with getObject as an Integer.

  • MistakeWriting database code against one driver's own classes.

    InsteadWrite against the java.sql interfaces so the database can change.

  • MistakeLeaving connections and result sets open.

    InsteadOpen them in try-with-resources so they are always closed.

Summary and key terms

  • A database management system gives a program durability, safe concurrent access, integrity rules, queries and transactions, which a file kept in memory cannot.
  • Relational databases store rows in tables; primary keys identify rows, foreign keys link tables and constraints such as NOT NULL and CHECK are enforced on every change.
  • Embedded databases such as SQLite and H2 run inside the program and store a local file; client–server databases such as PostgreSQL serve many programs over a network.
  • JDBC is the JDK's standard database API, in java.sql and javax.sql; vendors supply drivers that implement its interfaces.
  • The core types are DriverManager, DataSource, Connection, Statement, PreparedStatement, ResultSet and SQLException.
  • JDBC converts between SQL and Java types; watch NULL with primitives and read dates as java.time types.
  • Mapping turns each row into an object, usually in one small method per table.
Database management system
Software that stores data durably and lets many programs query and change it safely.
Durability
The guarantee that a saved change survives a crash.
Primary key
A column or set of columns whose value identifies each row uniquely.
Foreign key
A column whose values must match the primary key of another table.
Constraint
A rule, such as NOT NULL or CHECK, that the database enforces on every change.
Embedded database
A database that runs inside the application and stores a local file, such as SQLite or H2.
Client–server database
A database that runs as a separate server used by many programs over a network, such as PostgreSQL.
JDBC
Java Database Connectivity, the JDK's standard API for relational databases.
Driver
A vendor library that implements the JDBC interfaces for one database.
Mapping
Turning database rows into objects and objects back into rows.

Practice problems

Work each problem on paper first, then reveal the answer to check.

  1. What did StockRoom lose on August 24, and why?

    Show answer

    Eleven changes made since the morning, because it saved its file only when closed.

  2. Which column makes a good natural primary key for parts?

    Show answer

    The part code, such as TUBE-700x25.

  3. Name one embedded and one client–server database.

    Show answer

    SQLite (or H2) and PostgreSQL.

  4. Who supplies the classes that implement Connection for SQLite?

    Show answer

    The SQLite JDBC driver, not the JDK.

  5. What does getInt return for a NULL column?

    Show answer

    0, so check wasNull or use getObject.

Lesson quiz · 5 questions

Score 60% or more to complete this lesson.

Take the quiz