1. Why we need JDBC and why it looks like “three nested dolls”
When you first see JDBC code, it often looks weird: why, just to “fetch a list of expenses,” do you need to open a connection, then create a statement, then get a result set, then move some cursor around… In reality, JDBC is a low-level standard SQL API in the JVM world, and it honestly reflects reality: a database is an external resource, a query is a separate operation, and a result is a stream of rows.
If you remember the “three nested dolls,” everything gets much simpler:
- Connection — “I connected to the database; I have a communication channel.”
- PreparedStatement — “I prepared an SQL query (with parameters); I’m ready to execute.”
- ResultSet — “I got a tabular result; I read rows one by one.”
To make it fully visual, here’s a query lifecycle diagram:
flowchart TD
A["DriverManager.getConnection(...)"] --> B["Connection"]
B --> C["prepareStatement(sql)"]
C --> D["PreparedStatement"]
D -->|SELECT| E["executeQuery() → ResultSet"]
D -->|INSERT/UPDATE/DELETE| F["executeUpdate() → Int"]
E --> G["rs.next() → read columns"]
G --> H["Close ResultSet / Statement / Connection"]
The main idea of this lecture: you will learn to write JDBC code so that it is (1) safe, (2) readable, (3) free of resource leaks.
2. Connection: connecting and working with resources
A database connection is not a “regular Kotlin object” that you can create and forget. It holds external resources: sockets, file descriptors, locks, internal driver structures. That’s why Connection must be closed, even if it feels like “the program is small and will exit soon anyway.” That’s exactly how bugs are born that show up only “an hour later in production.”
The minimal connection skeleton usually looks like this (I’ll show SQLite because it’s the simplest option for examples):
import java.sql.DriverManager
fun main() {
val url = "jdbc:sqlite:app.db"
DriverManager.getConnection(url).use { conn ->
println("connected=${!conn.isClosed}") // connected=true
}
}
Notice use(). It does an important thing: it guarantees close() at the end of the block. This is exactly the case where Kotlin helps you not forget to close a resource. The use() idiom is recommended for AutoCloseable resources so you don’t have to manually write finally { close() }.
A quick note about the url
In tutorial examples, we’ll keep url as a string in the code. In a real project, it’s usually passed via config/environment variables, but that’s an infrastructure question, not the JDBC core.
3. PreparedStatement: parameters instead of concatenation
If you ever wanted to do this:
val sql = "SELECT id FROM users WHERE email = '$email'"
then JDBC (and your future career) gently asks you to stop and think. String concatenation in SQL is about quote-related bugs, escaping issues, and the risk of SQL injection.
The right approach: SQL is fixed, and values are passed via ? parameters.
import java.sql.Connection
fun findUserIdByEmail(conn: Connection, email: String): Long? {
val sql = "SELECT id FROM users WHERE email = ?"
conn.prepareStatement(sql).use { st ->
st.setString(1, email) // parameter indexing starts at 1!
st.executeQuery().use { rs ->
return if (rs.next()) rs.getLong("id") else null
}
}
}
Here you can already see “two nested dolls”: the statement and the result set are also closed via use().
Why parameter indexing starts at 1
Because JDBC is ancient—like legends about Excel spreadsheets without autosave. It’s a historical API decision: the first parameter has index 1. Not 0. Don’t ask. Just accept it like the weather.
4. executeQuery() and executeUpdate()
JDBC has a very practical separation: some queries return a table of rows (SELECT), others simply change data (INSERT/UPDATE/DELETE). That’s why the methods are different.
For SELECT, use executeQuery() and get a ResultSet.
import java.sql.Connection
fun countUsers(conn: Connection): Int {
val sql = "SELECT COUNT(*) AS cnt FROM users"
conn.prepareStatement(sql).use { st ->
st.executeQuery().use { rs ->
rs.next()
return rs.getInt("cnt")
}
}
}
For INSERT/UPDATE/DELETE, use executeUpdate() and get the number of affected rows.
import java.sql.Connection
fun deleteUser(conn: Connection, id: Long): Int {
val sql = "DELETE FROM users WHERE id = ?"
conn.prepareStatement(sql).use { st ->
st.setLong(1, id)
return st.executeUpdate() // 0 or 1 (ideally)
}
}
The number of affected rows is your minimal “sanity sensor.” If you expected to delete one record but deleted 17, that’s a reason to be cautious.
5. ResultSet: cursor, next(), and mapping into a data class
ResultSet is not a list. It’s a “cursor” over the result. Initially it is positioned before the first row, and until you call rs.next(), you cannot read columns. This is one of the most common beginner mistakes: “why is everything null / why does it crash?”
Example: read one record
import java.sql.Connection
data class User(val id: Long, val email: String, val name: String)
fun findUser(conn: Connection, email: String): User? {
val sql = "SELECT id, email, name FROM users WHERE email = ?"
conn.prepareStatement(sql).use { st ->
st.setString(1, email)
st.executeQuery().use { rs ->
return if (!rs.next()) null else User(
id = rs.getLong("id"),
email = rs.getString("email"),
name = rs.getString("name")
)
}
}
}
There’s a subtle point here: rs.getString(...) in Kotlin land is a platform type (String!). That means Kotlin will allow assigning it to a String, but if the DB actually contains NULL, you might get surprises later. At the beginner level, a good rule is: if a column can be NULL, store it as String?.
Example: read many rows into a list
Let’s say our tutorial app is a console expense tracker (we already “mentally” built it in previous topics about layered architecture). Let’s add an expense model:
data class Expense(
val id: Long,
val amountCents: Long,
val category: String,
val note: String?
)
Now reading the list of expenses:
import java.sql.Connection
fun listExpenses(conn: Connection): List<Expense> {
val sql = "SELECT id, amount_cents, category, note FROM expenses ORDER BY id DESC"
conn.prepareStatement(sql).use { st ->
st.executeQuery().use { rs ->
val result = mutableListOf<Expense>()
while (rs.next()) {
result += Expense(
id = rs.getLong("id"),
amountCents = rs.getLong("amount_cents"),
category = rs.getString("category"),
note = rs.getString("note") // let it be nullable by meaning
)
}
return result
}
}
}
Two “ResultSet laws” are visible here:
- to read a row, first call next(),
- to read many rows, use while (rs.next()).
Column by name vs. by index
You can read either way: rs.getLong(1) or rs.getLong("id"). By index is usually a bit faster, but for learning and readability by name is almost always better: fewer chances to mix up column order when the SQL changes.
6. Closing resources: use {} and readability
JDBC objects are almost always resources: Connection, PreparedStatement, ResultSet. If you don’t close them, you get leaks. Sometimes leaks show up quickly, sometimes after 10–30 minutes—and that’s the worst kind of bug: “it works until it doesn’t.”
The old approach: try/finally
Sometimes it’s useful to understand the “manual” mechanics to appreciate use().
import java.sql.Connection
import java.sql.PreparedStatement
fun closeQuietly(st: PreparedStatement?) {
try { st?.close() } catch (_: Exception) {}
}
fun example(conn: Connection) {
val st = conn.prepareStatement("SELECT 1")
try {
st.executeQuery()
} finally {
closeQuietly(st)
}
}
It works, but it looks like a mini ritual.
The Kotlin approach: use {} and nesting
Kotlin recommends use() for AutoCloseable so that closing happens automatically.
import java.sql.DriverManager
fun pingDb(url: String): Int {
return DriverManager.getConnection(url).use { conn ->
conn.prepareStatement("SELECT 1 AS one").use { st ->
st.executeQuery().use { rs ->
rs.next()
rs.getInt("one")
}
}
}
}
Yes, it’s “three use calls in a row.” But it’s rock-solid: even if the query fails with an exception, resources will be closed in reverse order.
How to make it more readable without losing safety
When the nesting starts to hurt your eyes, small wrapper functions usually help. For example, a “run with a connection” function:
import java.sql.Connection
import java.sql.DriverManager
inline fun <T> withConnection(url: String, block: (Connection) -> T): T {
return DriverManager.getConnection(url).use { conn ->
block(conn)
}
}
Usage:
fun isAlive(url: String): Boolean =
withConnection(url) { conn -> !conn.isClosed }
We’re not using any “future topics” here; we’re just applying already familiar functions and lambdas to make JDBC code less “stair-steppy.”
7. Storage layer: JdbcExpenseRepository without magic
It’s important not only to be able to write a query, but also to avoid smearing JDBC all over the application. In previous architecture topics, we discussed boundaries: CLI/scenarios should not know storage details. Here we’ll build a small repository that hides JDBC internals.
Let’s start with an interface (the outside world sees only it):
interface ExpenseRepository {
fun add(amountCents: Long, category: String, note: String?): Long
fun listLatest(limit: Int): List<Expense>
}
Now the implementation. In this lecture we won’t dive into transactions and “getting the generated id” for all databases (that’s a separate topic and drivers can have nuances), so we’ll do a simple tutorial version: the add method will return 1L as “success.”
import java.sql.DriverManager
class JdbcExpenseRepository(private val url: String) : ExpenseRepository {
override fun add(amountCents: Long, category: String, note: String?): Long {
val sql = "INSERT INTO expenses(amount_cents, category, note) VALUES (?, ?, ?)"
DriverManager.getConnection(url).use { conn ->
conn.prepareStatement(sql).use { st ->
st.setLong(1, amountCents)
st.setString(2, category)
st.setString(3, note)
st.executeUpdate()
return 1L // for simplicity: “success”
}
}
}
override fun listLatest(limit: Int): List<Expense> {
val sql = """
SELECT id, amount_cents, category, note
FROM expenses
ORDER BY id DESC
LIMIT ?
""".trimIndent()
return DriverManager.getConnection(url).use { conn ->
conn.prepareStatement(sql).use { st ->
st.setInt(1, limit)
st.executeQuery().use { rs ->
val items = mutableListOf<Expense>()
while (rs.next()) {
items += Expense(
id = rs.getLong("id"),
amountCents = rs.getLong("amount_cents"),
category = rs.getString("category"),
note = rs.getString("note")
)
}
items
}
}
}
}
}
Here we use LIMIT ? as a parameter. It’s convenient: the query stays fixed, and you don’t have to build an SQL string via concatenation.
Mini table: JDBC types and common pitfalls
When a beginner starts mapping rows into a data class, they often get confused: getInt or getLong? setString or setObject? Can you pass null? To reduce the chaos, keep a small “everyday” mapping table handy.
| SQL meaning (simplified) | PreparedStatement.set... | ResultSet.get... | Comment |
|---|---|---|---|
| integer | |
|
if the sum/ID can be large, Long is used more often |
| text | |
|
getString can return null if the DB contains NULL |
| nullable value | |
|
in Kotlin it’s better to store it as String? if the field is optional |
If you feel like types are “drifting,” it’s useful to adopt a rule: keep stable types in application code (Long for money in cents, String for category), and adapt SQL to match.
8. Typical mistakes when working with JDBC
Mistake #1: SQL concatenation instead of parameters (?).
When values are “pasted” into the SQL string, you get quote issues, escaping problems, and potentially open the door to SQL injection. Even if you’re sure “the user isn’t malicious,” the data format might be. The habit of using PreparedStatement plus setString/setLong/... solves this at the technical level, not the hope level.
Mistake #2: forgetting that parameters are numbered from 1, and calling setString(0, ...).
A classic. JDBC follows the rule “the first parameter has index 1.” With zero, you’ll get an exception—often at the worst possible moment. A simple discipline helps: keep set...() calls right next to the SQL so the order of ? visually matches the order of set.
Mistake #3: calling rs.getLong(...) before rs.next().
ResultSet starts “before the first row.” Until you call next(), the current row does not exist. For a single record, use the pattern if (rs.next()) { ... } else null; for a list, use while (rs.next()) { ... }. This isn’t style—it’s the API contract.
Mistake #4: mixing up executeQuery() and executeUpdate().
If you call executeQuery() for an INSERT, the driver may complain, or it may behave oddly. Keep a simple rule: SELECT → executeQuery() (and ResultSet), INSERT/UPDATE/DELETE → executeUpdate() (and an Int row count). This makes code predictable and makes it easier to verify the result.
Mistake #5: not closing resources (or closing only Connection but forgetting ResultSet).
In JDBC, there are many resources, and each one can hold external stuff. If you close the connection but forget to close the ResultSet, the driver may keep things alive longer than you’d like. The simplest and most reliable Kotlin style is nested use() calls for all JDBC objects you create. That’s exactly why use() is so valued for AutoCloseable.
Mistake #6: assuming getString() “cannot be null” because your Kotlin type is String.
JDBC methods from Java often appear to Kotlin as platform types, and the compiler won’t always force you to handle null. If a column in the DB allows NULL, don’t play the hero: store the field in your model as String? and handle null explicitly. That’s cheaper than debugging an NPE a week later.
Mistake #7: keeping JDBC code in main and smearing SQL all over the application.
For the first couple of queries it’s “faster,” but later you end up with a project where schema changes require hunting SQL across ten files. It’s much calmer to define a storage layer: a repository/DAO that is the only place that knows about JDBC, while the rest of the code works with methods like add, listLatest, and your data class models.
GO TO FULL VERSION