bowbahdoe/jdbc

Utilities for JDBC

JDBC

Utilities for working with the raw JDBC api.

Includes

• Utilities for reading data from ResultSets
• An UncheckedSQLException for when throwing a SQLException is inconvenient, but might need to be recovered later.
• A SQLFragment class for basic query composition
• A SettableParameter interface, useful with SQLFragment (but way more useful whenever String Templates are re-previewed).
• A ParameterizedSQLFragment for producing SQLFragments using named arguments

Dependency Information

Maven

<dependency>
    <groupId>dev.mccue</groupId>
    <artifactId>jdbc</artifactId>
    <version>2025.05.29</version>
</dependency>

Gradle

dependencies {
    implementation("dev.mccue:jdbc:2025.05.29")
}

Usage

These examples use sqlite.

Wrap SQLExceptions

In many contexts the checked-ness of SQLException can be inconvenient. Just as the standard library provides UncheckedIOException to wrap an IOException, this library provides an UncheckedSQLException to wrap SQLException.

import dev.mccue.jdbc.UncheckedSQLException;

import java.sql.SQLException;

void main() throws Exception {
    var db = new SQLiteDataSource();
    db.setUrl("jdbc:sqlite:test.db");

    try (var conn = db.getConnection()) {
        try (var stmt = conn.prepareStatement("""
                SELECT name
                FROM widget
                LIMIT 1
                """)) {
            var rs = stmt.executeQuery();

            var name = rs.getString("name");
        }
    } catch (SQLException e) {
        throw new UncheckedSQLException(e);
    }
}

Read nullable primitive types

ResultSets includes helpers for reading potentially null primitive types from a ResultSet.

import dev.mccue.jdbc.ResultSets;

void main() throws Exception {
    var db = new SQLiteDataSource();
    db.setUrl("jdbc:sqlite:test.db");

    try (var conn = db.getConnection()) {
        try (var stmt = conn.prepareStatement("""
                SELECT number
                FROM widget
                LIMIT 1
                """)) {
            var rs = stmt.executeQuery();

            // Methods exist for all primitives except char 
            // (which doesn't have a method on ResultSet)
            Integer number = ResultSets.getIntegerNullable(rs, "number");
        }
    }
}

Read non-null primitive types

If you want to read a column that is primitive, but you assume is not null, there are helpers which will throw a SQLException early if that assumption is violated.

import dev.mccue.jdbc.ResultSets;

void main() throws Exception {
    var db = new SQLiteDataSource();
    db.setUrl("jdbc:sqlite:test.db");

    try (var conn = db.getConnection()) {
        try (var stmt = conn.prepareStatement("""
                SELECT number
                FROM widget
                LIMIT 1
                """)) {
            var rs = stmt.executeQuery();

            // Methods exist for all primitives except char 
            // (which doesn't have a method on ResultSet)
            int number = ResultSets.getIntegerNotNull(rs, "number");
        }
    }
}

Read a row as a Record

Often when going through a ResultSet you will want to materialize a whole row.

import dev.mccue.jdbc.ResultSets;

public record Widget(int number) {}

void main() throws Exception {
    var db = new SQLiteDataSource();
    db.setUrl("jdbc:sqlite:test.db");

    try (var conn = db.getConnection()) {
        try (var stmt = conn.prepareStatement("""
                SELECT number
                FROM widget
                LIMIT 1
                """)) {
            var rs = stmt.executeQuery();
            var widget = ResultSets.getRecord(rs, Widget.class);

            System.out.println(widget);
        }
    }
}

Read a row as a Record with customized mappings

If the name of a record component doesn't line up with what you want pulled from a ResultSet, you can use the @Column annotation.

import dev.mccue.jdbc.Column;
import dev.mccue.jdbc.ResultSets;

public record Widget(@Column(label = "number") int n) {
}

void main() throws Exception {
    var db = new SQLiteDataSource();
    db.setUrl("jdbc:sqlite:test.db");

    try (var conn = db.getConnection()) {
        try (var stmt = conn.prepareStatement("""
                SELECT number
                FROM widget
                LIMIT 1
                """)) {
            var rs = stmt.executeQuery();
            var widget = ResultSets.getRecord(rs, Widget.class);

            System.out.println(widget);
        }
    }
}

Read rows as a stream

If you want to iterate over the results of a query without the classic while (rs.next()) pattern, there is a helper to get the results as a stream.

import dev.mccue.jdbc.Column;
import dev.mccue.jdbc.ResultSets;

public record Widget(int number) {
}

void main() throws Exception {
    var db = new SQLiteDataSource();
    db.setUrl("jdbc:sqlite:test.db");

    try (var conn = db.getConnection()) {
        try (var stmt = conn.prepareStatement("""
                SELECT number
                FROM widget
                LIMIT 1
                """)) {
            ResultSets.stream(rs, ResultSets.getRecord(Widget.class))
                    .forEach(System.out::println);
        }
    }
}

Compose fragments of SQL

You can use SQLFragment to implement some relatively basic conditional query building logic.

import dev.mccue.jdbc.ResultSets;
import dev.mccue.jdbc.SQLFragment;

import java.util.ArrayList;
import java.util.List;

void main() throws Exception {
    var db = new SQLiteDataSource();
    db.setUrl("jdbc:sqlite:test.db");

    var queryFragments = new ArrayList<SQLFragment>();
    queryFragments.add(SQLFragment.of("""
            SELECT name
            FROM widget
            WHERE id = ?
            """, List.of(1)));
    
    Integer limit = Math.random() > 0.5 ? null : 1;
    if (limit != null) {
        queryFragments.add(SQLFragment.of("""
                LIMIT ?
                """, List.of(limit)));
    }
    
    var query = SQLFragment.join("", queryFragments);
    
    try (var conn = db.getConnection()) {
        try (var stmt = query.prepareStatement(conn)) {
            var rs = stmt.executeQuery();
            Integer number = ResultSets.getIntegerNullable(rs, "number");
            System.out.println(number);
        }
    }
}

By default, parameters are set with .setObject. If your particular database driver won't do the right thing with that you can wrap them with SettableParameter. The different .ofX methods on there match up 1-1 with the .setX methods on PreparedStatement.

import dev.mccue.jdbc.ResultSets;
import dev.mccue.jdbc.SQLFragment;
import dev.mccue.jdbc.SettableParameter;

import java.util.ArrayList;
import java.util.List;

void main() throws Exception {
    var db = new SQLiteDataSource();
    db.setUrl("jdbc:sqlite:test.db");

    var queryFragments = new ArrayList<SQLFragment>();
    queryFragments.add(SQLFragment.of("""
            SELECT name
            FROM widget
            WHERE id = ?
            """, List.of(SettableParameter.ofInt(1))));

    Integer limit = Math.random() > 0.5 ? null : 1;
    if (limit != null) {
        queryFragments.add(SQLFragment.of("""
                LIMIT ?
                """, List.of(SettableParameter.ofInt(limit))));
    }

    var query = SQLFragment.join("", queryFragments);

    try (var conn = db.getConnection()) {
        try (var stmt = query.prepareStatement(conn)) {
            var rs = stmt.executeQuery();
            Integer number = ResultSets.getIntegerNullable(rs, "number");
            System.out.println(number);
        }
    }
}

Compose fragments of SQL using named arguments

You can use ParameterizedSQLFragment to pass named instead of positional arguments to make SQLFragments.

import dev.mccue.jdbc.ParameterizedSQLFragment;
import dev.mccue.jdbc.ResultSets;
import dev.mccue.jdbc.SQLFragment;

import java.util.ArrayList;
import java.util.List;

void main() throws Exception {
    var db = new SQLiteDataSource();
    db.setUrl("jdbc:sqlite:test.db");

    var queryFragments = new ArrayList<SQLFragment>();
    queryFragments.add(ParameterizedSQLFragment.of("""
            SELECT name
            FROM widget
            WHERE id = :id
            """, Map.of("id", 1)));

    Integer limit = Math.random() > 0.5 ? null : 1;
    if (limit != null) {
        queryFragments.add(ParameterizedSQLFragment.of("""
                LIMIT :limit
                """, Map.of("limit", limit)));
    }

    var query = SQLFragment.join("", queryFragments);

    try (var conn = db.getConnection()) {
        try (var stmt = query.prepareStatement(conn)) {
            var rs = stmt.executeQuery();
            Integer number = ResultSets.getIntegerNullable(rs, "number");
            System.out.println(number);
        }
    }
}