JDBC

Software Design

Database breeds

Networked DBMS

The most of DBMS make use of the TCP protocol for communicating with applications. They accept incoming connections on a specific TCP port. This allows both local and remote connections.

MS SQL Server (TCP:1433)
PostGreSQL (TCP:5432)
MySQL (TCP:3306)
Oracle (TCP:1521)

Local Database

A family of libraries capable of simulating a DBMS connection while providing access to a local file (or memory) using SQL.

The JDBC API

“An API that lets you access virtually any tabular data source from the Java programming language"

What’s an API? Application Programming Interface
What’s a tabular data source? Relational databases, spreadsheets, CSV files.

We’ll focus on accessing relational databases. Nevertheless, the same principles can be applied to all data sources.

Vendor specific drivers

JDBC drivers provide the connection to the database and implement the protocol for transferring queries and results between the client and the database. JDBC is an abstract API mostly composed of interfaces and abstract classes. Concrete implementations are provided within specific drivers:

[PostGreSQL] https://jdbc.postgresql.org/
[MySQL] https://dev.mysql.com/downloads/connector/j/
[SQL Server] https://learn.microsoft.com/en-us/sql/connect/jdbc/download-microsoft-jdbc-driver-for-sql-server?view=sql-server-ver16
[SQLite] https://github.com/xerial/sqlite-jdbc

Drivers are Java binary classes (.class files) usually packaged in a single .jar archive and have to be included in the CLASSPATH. For including vendor specific drivers (or any external library) into the project, two main ways exist:

Manually download a .jar file and add it to the CLASSPATH of the project (not a good idea!)
Configure a building tool (e.g., Maven, Gradle) to download it and make it available to the project

An example of Maven configuration is reported below. The dependencies section actually refers to external libraries to be included within the project.

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.nbicocchi.javafx</groupId>
    <artifactId>jdbc-planes</artifactId>
    <version>1.0-SNAPSHOT</version>
    <name>jdbc-planes</name>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <junit.version>5.10.0</junit.version>
        <java.version>21</java.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.openjfx</groupId>
            <artifactId>javafx-controls</artifactId>
            <version>21</version>
        </dependency>
        <dependency>
            <groupId>org.openjfx</groupId>
            <artifactId>javafx-fxml</artifactId>
            <version>21</version>
        </dependency>

        <dependency>
            <groupId>com.fasterxml.jackson.datatype</groupId>
            <artifactId>jackson-datatype-jsr310</artifactId>
            <version>2.16.1</version>
        </dependency>

        <dependency>
            <groupId>com.zaxxer</groupId>
            <artifactId>HikariCP</artifactId>
            <version>5.1.0</version>
            <exclusions>
                <exclusion>
                    <groupId>org.slf4j</groupId>
                    <artifactId>slf4j-api</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <version>42.7.1</version>
        </dependency>

        <dependency>
            <groupId>ch.qos.logback</groupId>
            <artifactId>logback-classic</artifactId>
            <version>1.4.14</version>
        </dependency>

        <dependency>
            <groupId>org.junit.jupiter</groupId>
            <artifactId>junit-jupiter-api</artifactId>
            <version>${junit.version}</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.junit.jupiter</groupId>
            <artifactId>junit-jupiter-engine</artifactId>
            <version>${junit.version}</version>
            <scope>test</scope>
        </dependency>
    </dependencies>

    ...

</project>

Installing a DBMS: Local vs Cloud-Native (Docker)

Option 1: Local Installation

Step 1: Download installer from official website (e.g., PostgreSQL, MySQL)
Step 2: Run the installer and follow GUI or CLI setup wizard
Step 3: Configure:
- Authentication method (e.g., password)
- Users
- Schemas
Step 4: Start the DBMS as a service (via systemd, Windows Service, etc.)

✅ Pros: Full control, easy access to configuration files

❌ Cons: OS-specific issues, harder to manage across multiple environments

Option 2: Cloud-Native via Docker

Step 1: Pull the image
```
docker pull postgres:latest
```

Step 2: Run container

docker run --name my-postgres \
  -e POSTGRES_PASSWORD=mysecret \
  -p 5432:5432 \
  -d postgres

Step 3: Connect via client (e.g., psql, pgAdmin)

✅ Pros: Fast setup, consistent across systems, easy to stop/start

❌ Cons: Requires Docker, persistence needs volume management

Establish Connections

Driver Manager

The basic service for managing a set of JDBC drivers. When the method getConnection is called, the DriverManager will attempt to locate a suitable driver from amongst those loaded at initialization and those loaded explicitly using the same classloader as the current applet or application.

DriverManager.getConnection(String url);
DriverManager.getConnection(String url, String user, String password);
DriverManager.getConnection(String url, Properties prop);

/* PostgreSQL */
Connection connection = DriverManager.getConnection(
        "jdbc:postgresql://localhost:5432/jdbc_schema?user=user&password=secret&ssl=false");

/* MySQL*/
Connection connection = DriverManager.getConnection(
        "jdbc:mysql://localhost/dbname?user=user&password=pass");

/* SQLite */
Connection connection = DriverManager.getConnection(
        "jdbc:sqlite:filename.db");

Connection Pooling: Boosting DB Performance

What is Connection Pooling?

A data access pattern that caches and reuses DB connections
Reduces overhead from repeatedly opening/closing connections
Increases performance of database-driven applications

Why It's Needed

Typical DB connection lifecycle:

Open DB connection (driver)
Open TCP socket
Read/write over socket
Close connection
Close socket

Each step is costly — especially when repeated

How Pooling Helps

Connection pool = cache of reusable DB connections
Avoids repeated setup/teardown
Maintains a ready-to-use set of open connections
Reduces latency and resource consumption

Benefits

Faster data access
Better resource utilization
Scalable and more efficient applications

public class App {
    private final static Logger LOG = LoggerFactory.getLogger(App.class);
    HikariDataSource dataSource;

    // ...

    private void dbConnection() {
        LOG.info("** dbConnection() **");
        HikariConfig config = new HikariConfig();
        config.setDriverClassName("org.postgresql.Driver");
        config.setJdbcUrl("jdbc:postgresql://localhost:5432/jdbc_schema?user=user&password=secret&ssl=false");
        config.setLeakDetectionThreshold(2000);
        dataSource = new HikariDataSource(config);
    }

JDBC Statements: Executing SQL in Java

int executeUpdate(String SQL): Used for writing the database. Use this method to execute SQL statements such as INSERT, UPDATE, DELETE, CREATE TABLE, DROP TABLE, etc. Returns the number of rows affected by the execution of the SQL statement.

private void testUpdate() {
    LOG.info("** testUpdate() **");
    try (
            Connection connection = dataSource.getConnection();
            PreparedStatement ps = connection.prepareStatement("UPDATE book SET pages=? WHERE id=?")) {
        ps.setInt(1, 333);
        ps.setInt(2, 1);
        ps.executeUpdate();
    } catch (SQLException e) {
        throw new RuntimeException();
    }
}

ResultSet executeQuery(String SQL): Used for reading the database. Use this method to execute SQL statements such as SELECT. Returns a ResultSet object.

private void testSelect() {
    LOG.info("** testSelect() **");
    try (
            Connection connection = dataSource.getConnection();
            PreparedStatement ps = connection.prepareStatement("SELECT * FROM book LIMIT 100");
            ResultSet rs = ps.executeQuery()) {
        while (rs.next()) {
            LOG.info(String.format("--> id=%d, title=%s, author=%s, pages=%d",
                    rs.getInt("id"),
                    rs.getString("title"),
                    rs.getString("author"),
                    rs.getInt("pages")));
        }
    } catch (SQLException e) {
        throw new RuntimeException();
    }
}

Issues with Locales

Locales (among other things) influence the way in which strings are formatted. They can have nasty consequences when creating SQL statements using string formatting (String.format()).

String sql = String.format(
    "INSERT INTO person (name, surname, salary) VALUES ('%s', '%s', '%f')",  
    person.getName(),
    person.getSurname(),
    person.getSalary()
);

statement.executeUpdate(sql);

The sql variable can either contain:

INSERT INTO person (name, surname, salary) VALUES ('Mario', 'Rossi', '11.2')

INSERT INTO person (name, surname, salary) VALUES ('Mario', 'Rossi', '11,2')

Depending on the Locale used. For example:

Locale locale = new Locale("en", "US");

Locale locale = new Locale("it", "IT");

Receive ResultSets

The java.sql.ResultSet interface represents the result set of a database query. Objects implementing the ResultSet interface maintain a cursor pointing to the current row of the result set and offer a useful group of methods for navigating and getting those results.

Navigational Methods in `ResultSet`

These methods allow moving the cursor within the rows of a ResultSet:

next(): Moves forward one row.
previous(): Moves back one row.
first() / last(): Moves to the first or last row.
beforeFirst() / afterLast(): Positions the cursor just before the first row or just after the last row.
absolute(int row): Moves to a specific row number.
relative(int rows): Moves a relative number of rows from the current position.

Getter methods

This group of methods is used to get the data in the columns of the current row being pointed by the cursor. They all have the following form:

resultSet.gettype(id)

where:

type is the primitive data type of the request column.
id can be the String name of the column or its numerical id.

For example, given this table:

Column Name	Data Type	Description
`id`	`BIGINT`	Unique identifier for each row.
`lastName`	`VARCHAR`	Employee's last name.
`age`	`INT`	Employee's age.
`salary`	`DOUBLE`	Employee's salary.

CREATE TABLE employees (
    id BIGINT PRIMARY KEY,
    lastName VARCHAR(100),
    age INT,
    salary DOUBLE
);

It is possible to fetch data from the ResultSet with:

Long id = resultSet.getLong("id");
String lastName = resultSet.getString("lastName");
Integer age = resultSet.getInt("age");
Double salary = resultSet.getDouble("salary");

Long id = resultSet.getLong(1);
String lastName = resultSet.getString(2);
Integer age = resultSet.getInt(3);
Double salary = resultSet.getDouble(4);

As a general rule, names are preferred over id because they make the code more readable.

Operational Aspects

Dealing with errors

Programs should recover from errors and always leave the database in a consistent state. Runtime errors must be minimized in industrial applications!

The try-with-resources statement is a try statement that declares one or more resources. A resource is an object that must be closed after the program is finished with it. The try-with-resources statement ensures that each resource is closed at the end of the statement. Any object that implements java.lang.AutoCloseable, which includes all objects which implement java.io.Closeable, can be used as a resource.

private void testSelect() {
    LOG.info("** testSelect() **");
    try (
            Connection connection = dataSource.getConnection();
            PreparedStatement ps = connection.prepareStatement("SELECT * FROM book LIMIT 100");
            ResultSet rs = ps.executeQuery()) {
        while (rs.next()) {
            LOG.info(String.format("--> id=%d, title=%s, author=%s, pages=%d",
                    rs.getInt("id"),
                    rs.getString("title"),
                    rs.getString("author"),
                    rs.getInt("pages")));
        }
    } catch (SQLException e) {
        throw new RuntimeException();
    }
}

Mapping JDBC and Java types

There are significant variations between the SQL types supported by different database products. For example, most of the major databases support an SQL data type for large binary values, but Oracle calls this type LONG RAW, Sybase calls it IMAGE and Informix calls it BYTE.

JDBC programmers mostly program with existing database tables, and they need not concern themselves with the exact SQL type names that were used. The one major place where programmers may need to use SQL type names is in the SQL CREATE TABLE statement when they are creating a new database table. In this case programmers must take care to use SQL type names that are supported by their target database.

Java Type	SQL Type
`boolean`	`BOOLEAN` or `BIT`
`byte`	`TINYINT`
`short`	`SMALLINT`
`int`	`INT`
`long`	`BIGINT`
`float`	`REAL`
`double`	`DOUBLE`
`java.math.BigDecimal`	`DECIMAL` or `NUMERIC`
`java.sql.Date`	`DATE`
`java.sql.Time`	`TIME`
`java.sql.Timestamp`	`TIMESTAMP`
`String`	`VARCHAR` or `CHAR`
`byte[]`	`VARBINARY` or `BLOB`
`java.sql.Array`	`ARRAY`
`java.sql.ResultSet`	`CURSOR`
`java.sql.Struct`	`STRUCT`
`java.sql.Clob`	`CLOB`
`java.sql.Blob`	`BLOB`
`java.sql.Ref`	`REF`
`java.sql.SQLXML`	`SQLXML`
`java.util.UUID`	`UUID`

Note: This table provides a general mapping, and there may be variations depending on the specific SQL database you are using.

Advanced Result Set

ResultSet are iterator-like objects. With default ResultSets:

It is not possible to move back and forth. Only next() can be called.
It is not possible to modify the underlying the database. Data have to be manipulated in memory and stored back with another operation (Statement.executeUpdate()).

Statement createStatement(int resultSetType,
                          int resultSetConcurrency) throws SQLException;

However, the above method call creates Statement objects with additional capabilities:

resultSetType - a result set type; one of ResultSet.TYPE_FORWARD_ONLY, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.TYPE_SCROLL_SENSITIVE
resultSetConcurrency - a concurrency type; one of ResultSet.CONCUR_READ_ONLY, ResultSet.CONCUR_UPDATABLE

Scrollable ResultSet

Statement statement = connection.createStatement( 
    ResultSet.TYPE_SCROLL_SENSITIVE, 
    ResultSet.CONCUR_READ_ONLY);

ResultSet resultSet = statement.executeQuery("SELECT * FROM person");

resultSet.previous();     // go 1 record back
resultSet.relative(-5);   // go 5 records back
resultSet.relative(7);    // go 7 records forward
resultSet.absolute(100);  // go to 100th record

Updateable ResultSet

Statement statement = connection.createStatement( 
    ResultSet.TYPE_SCROLL_SENSITIVE, 
    ResultSet.CONCUR_UPDATABLE);
    
ResultSet resultSet = s.executeQuery(“SELECT * FROM students WHERE type="car_lover");

while (resultSet.next()) {
    int grade = rs.getInt("grade");
    resultSet.updateInt("grade", grade + 1);
    resultSet.updateRow();
}

Transactions

A transaction is a set of actions to be performed atomically. Either all the actions are carried out, or none of them are.

The classic example of when transactions are necessary is movement between bank accounts. You need to transfer $100 from one account to the other. You do so by subtracting $100 from the first account and adding $100 to the second account. If this process fails after you have subtracted the $100 from the first bank account, the $100 is never added to the second bank account. The money is lost in cyberspace.

JDBC allows SQL statements to be grouped together into a single transaction
Transaction control is performed by the Connection object, default mode is auto-commit, i.e., each sql statement is treated as a transaction
We can turn off the auto-commit mode with connection.setAutoCommit(false);
And turn it back on with connection.setAutoCommit(true);
Once auto-commit is off, no SQL statement will be committed until an explicit is invoked connection.commit(). At this point all changes done by the SQL statements will be made permanent in the database.

private void testUpdateWithTransactions() {
    try (
            Connection connection = dataSource.getConnection();
            PreparedStatement ps = connection.prepareStatement("UPDATE book SET pages=? WHERE id=?")
    ) {
        // disable auto-commit
        connection.setAutoCommit(false);

        // the first book has 100 pages
        ps.setInt(1, 100);
        ps.setInt(2, 1);
        ps.executeUpdate();

        // the second book has 200 pages
        ps.setInt(1, 200);
        ps.setInt(2, 2);
        ps.executeUpdate();

        // all changes are actually committed together
        connection.commit();

        // re-enable auto-commit
        connection.setAutoCommit(true);
    } catch (SQLException e) {
        throw new RuntimeException();
    }
}

Software Engineering

Repository

The Repository layer isolates domain objects from details of the database access code and to minimize scattering and duplication of query code. This pattern is especially useful in systems where number of domain classes is large or heavy querying is utilized. Repository architectural pattern creates a uniform layer of data repositories that can be used for CRUD operations.

The Repository interface defines methods providing CRUD operations for all entities.

public interface Repository<T, ID> {
    Optional<T> findById(ID id);
    Iterable<T> findAll();
    T save(T entity);
    void delete(T entity);
    void deleteById(ID id);
    void deleteAll();
}

The Repository interface has a specific implementation for each entity. In the example below, we implement a repository for Plane objects having a Long as primary key.

public class PlaneRepository implements Repository<Plane, Long> {
    private final HikariDataSource dataSource;

    public PlaneRepository(HikariDataSource dataSource) {
        this.dataSource = dataSource;
        checkTable();
    }

    private void checkTable() {
        String sql = "SELECT * FROM planes LIMIT 1";
        try (Connection connection = dataSource.getConnection();
             PreparedStatement statement = connection.prepareStatement(sql)) {
            ResultSet rs = statement.executeQuery();
        } catch (SQLException e) {
            // Must be disabled in production!
            initTable();
        }
    }

    private void initTable() {
        String sql = "DROP TABLE IF EXISTS planes;" +
                "CREATE TABLE planes " +
                "(id SERIAL, " +
                "name VARCHAR(50) DEFAULT NULL, " +
                "length DOUBLE PRECISION DEFAULT NULL, " +
                "wingspan DOUBLE PRECISION DEFAULT NULL, " +
                "firstFlight DATE DEFAULT NULL, " +
                "category VARCHAR(50) DEFAULT NULL, " +
                "PRIMARY KEY (id))";
        try (Connection connection = dataSource.getConnection();
             PreparedStatement statement = connection.prepareStatement(sql)) {
            statement.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e.getMessage());
        }
    }

    @Override
    public Optional<Plane> findById(Long Id) {
        String sql = "SELECT * FROM planes WHERE id=?";
        try (Connection connection = dataSource.getConnection();
             PreparedStatement statement = connection.prepareStatement(sql)) {
            statement.setLong(1, Id);
            ResultSet rs = statement.executeQuery();

            if (!rs.next()) {
                return Optional.empty();
            }

            return Optional.of(new Plane(rs.getLong("id"), rs.getString("name"), rs.getDouble("length"), rs.getDouble("wingspan"), convertSQLDateToLocalDate(rs.getDate("firstFlight")), rs.getString("category")));
        } catch (SQLException e) {
            throw new RuntimeException(e.getMessage());
        }
    }

    //...
}

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

JDBC

Software Design

Database breeds

The JDBC API

Vendor specific drivers

Installing a DBMS: Local vs Cloud-Native (Docker)

Option 1: Local Installation

Option 2: Cloud-Native via Docker

Establish Connections

Driver Manager

Connection Pooling: Boosting DB Performance

JDBC Statements: Executing SQL in Java

Issues with Locales

Receive ResultSets

Navigational Methods in `ResultSet`

Getter methods

Operational Aspects

Dealing with errors

Mapping JDBC and Java types

Advanced Result Set

Scrollable ResultSet

Updateable ResultSet

Transactions

Software Engineering

Repository

FilesExpand file tree

E3.1 - JDBC.md

Latest commit

History

E3.1 - JDBC.md

File metadata and controls

JDBC

Software Design

Database breeds

The JDBC API

Vendor specific drivers

Installing a DBMS: Local vs Cloud-Native (Docker)

Option 1: Local Installation

Option 2: Cloud-Native via Docker

Establish Connections

Driver Manager

Connection Pooling: Boosting DB Performance

JDBC Statements: Executing SQL in Java

Issues with Locales

Receive ResultSets

Navigational Methods in ResultSet

Getter methods

Operational Aspects

Dealing with errors

Mapping JDBC and Java types

Advanced Result Set

Scrollable ResultSet

Updateable ResultSet

Transactions

Software Engineering

Repository

Navigational Methods in `ResultSet`