JDBC Fundamentals from Kotlin

JDBC (Java Database Connectivity) is the lowest-level, most universal way to talk to a relational database on the JVM. Every higher-level tool you will meet later in this course — Spring Data JDBC, JPA/Hibernate, jOOQ, Exposed — ultimately sits on top of it. Understanding JDBC directly demystifies all of them: you learn what a connection actually is, why parameter binding matters, and where resources can leak. Even when you never write raw JDBC in production, this mental model is what lets you debug the abstractions when they misbehave.

The entry point is a DataSource, not a single connection. A DataSource is a factory that hands you pooled connections on demand; in a Spring Boot app it is auto-configured (HikariCP by default) and injected for you. You ask it for a Connection with dataSource.connection, run your work, and return the connection to the pool by closing it. A Connection is a short-lived, expensive resource representing one open session with the database — you borrow it, use it, and give it back as quickly as possible. Holding one open across a slow network call or user think-time is a classic way to exhaust the pool.

Never build SQL by concatenating user input. A statement like "SELECT * FROM users WHERE email = '" + email + "'" is a textbook SQL injection hole: a crafted email value can rewrite your query entirely. The fix is a PreparedStatement with positional placeholders (?) and bound parameters. The driver sends the query template and the values separately, so the database treats your inputs strictly as data — never as executable SQL. As a bonus, prepared statements can be parsed once and reused, which is faster for repeated queries.

Parameters are bound by 1-based index using typed setters: setString(1, email), setLong(2, id), setBoolean(3, active), and so on. The index refers to the position of each ? in the SQL, left to right. Matching the setter to the column type keeps the driver from doing surprising coercions, and setNull(index, sqlType) is the correct way to bind a SQL NULL rather than passing an empty string. Because the value never becomes part of the SQL text, even an input like "'; DROP TABLE users; --" is stored and compared as the literal string it is — nothing more.

Reading results means iterating a ResultSet. Calling executeQuery() returns a cursor positioned before the first row; each call to next() advances it and returns false when the rows run out, which is why the canonical loop is while (rs.next()) { ... }. Inside the loop you pull columns with getString("email"), getLong("id"), getInt, getBoolean, and friends — addressing columns by name is more readable and more refactor-proof than by index. Be mindful that getInt on a SQL NULL returns 0; when nullability matters, read the value and then check rs.wasNull().

All three of these objects — Connection, PreparedStatement, ResultSet — hold native resources that must be released, even when an exception is thrown. In Java you reach for try-with-resources; Kotlin gives you the more elegant use {} extension. Any Closeable or AutoCloseable can call use { resource -> ... }, which runs your block and guarantees close() is invoked afterward, propagating your exception if one occurs and closing cleanly regardless. Nesting use blocks (connection, then statement, then result set) closes everything in the correct reverse order with no finally noise.

The idiomatic Kotlin touch is mapping a raw row into a real domain type instead of passing a ResultSet around your codebase. Give your data class a companion object with a fromRow(rs: ResultSet): User factory. This keeps the brittle, column-name-to-property knowledge in exactly one place: if a column is renamed, you change one function, not twenty call sites. Your query function then becomes a clean pipeline — borrow a connection, prepare and bind, iterate, and collect User.fromRow(rs) into a list — with the database details neatly encapsulated.

A word on the bigger picture: classic JDBC is blocking. Every call to executeQuery() parks the calling thread until the database answers, so you must keep it off the main or event-loop threads. In a coroutine-based service you wrap blocking JDBC work in withContext(Dispatchers.IO), which hands it to a thread pool sized for blocking I/O. (For truly non-blocking database access you would reach for R2DBC, a later topic.) For now, internalize the four-beat rhythm — DataSource gives a Connection, PreparedStatement binds parameters, ResultSet yields rows, and use {} closes everything — and the rest of the persistence ecosystem will feel like convenience on top of fundamentals you already own.

import javax.sql.DataSource
import java.sql.ResultSet

data class User(val id: Long, val email: String, val active: Boolean) {
    companion object {
        // Single source of truth for column -> property mapping.
        fun fromRow(rs: ResultSet) = User(
            id = rs.getLong("id"),
            email = rs.getString("email"),
            active = rs.getBoolean("active"),
        )
    }
}

class UserRepository(private val dataSource: DataSource) {

    fun findActiveByEmail(email: String): User? {
        val sql = "SELECT id, email, active FROM users WHERE email = ? AND active = ?"
        // Each use {} closes its resource even if an exception is thrown.
        return dataSource.connection.use { conn ->
            conn.prepareStatement(sql).use { stmt ->
                stmt.setString(1, email)   // bound, not concatenated
                stmt.setBoolean(2, true)
                stmt.executeQuery().use { rs ->
                    if (rs.next()) User.fromRow(rs) else null
                }
            }
        }
    }
}

import javax.sql.DataSource

fun UserRepository.findRecent(dataSource: DataSource, limit: Int): List<User> {
    val sql = "SELECT id, email, active, login_count FROM users ORDER BY id DESC LIMIT ?"
    val results = ArrayList<User>()
    dataSource.connection.use { conn ->
        conn.prepareStatement(sql).use { stmt ->
            stmt.setInt(1, limit)
            stmt.executeQuery().use { rs ->
                while (rs.next()) {                 // false when rows are exhausted
                    results += User.fromRow(rs)
                    // Nullable column example:
                    val loginCount = rs.getInt("login_count")
                    val realCount: Int? = if (rs.wasNull()) null else loginCount
                    // ... use realCount as needed
                }
            }
        }
    }
    return results
}

import kotlinx.coroutines.Dispatchers
import kotlinx.coroutines.runBlocking
import kotlinx.coroutines.withContext

// Stand-in for a blocking JDBC call (Thread.sleep simulates DB latency).
fun blockingQuery(email: String): String {
    Thread.sleep(50)
    return "user:" + email
}

suspend fun fetchUser(email: String): String =
    // Keep blocking I/O off the main/event-loop thread.
    withContext(Dispatchers.IO) { blockingQuery(email) }

fun main() = runBlocking {
    val result = fetchUser("ada@example.com")
    println("Loaded " + result + " on " + Thread.currentThread().name)
}