JDBC Fundamentals: Connections, Statements, and ResultSets

Executive Summary

JDBC fundamentals start with one idea: JDBC is a standard interface over vendor drivers. The driver JAR arrives as a dependency, and DriverManager opens a Connection from a URL like jdbc:h2:mem:catalog. Then the API takes over. SQL strings carry typed parameters, result rows arrive as Java types, and exceptions surface as the checked SQLException.

The working triangle has three parts. First, a Connection wraps a real socket to the database, and try-with-resources closes it. Second, PreparedStatement is the only statement class that matters. Its ? placeholders carry parameters safely and stop SQL injection by construction. Third, a ResultSet is a cursor whose next() advances one row at a time. Its get-by-label methods read columns into Java types. Queries return rows through executeQuery, and mutations return counts through executeUpdate. When requested, the same statement also returns generated keys. Row mapping is the records article doing database work: one loop, one constructor call per row, immutable results downstream.

Two disciplines separate production code from tutorials. First, everything closes through try-with-resources, because leaked connections are the pool exhaustion the debugging article diagnosed. Second, user input never, ever, enters SQL by string concatenation. That is the OWASP injection guidance in one rule. Finally, pools and transactions are the next article, because they exist to solve the connection cost and the multi-statement consistency problem.

JDBC Fundamentals: A Standard Over Drivers

JDBC fundamentals come down to two things, and keeping them separate explains everything else. The API, java.sql, is fixed: Connection, Statement, ResultSet, SQLException, the vocabulary of every database conversation. The drivers, by contrast, are pluggable. Each database ships a JAR that implements the API for its protocol, via a Maven dependency like com.h2database:h2 or org.postgresql:postgresql. The JDBC URL you pass then picks both the driver and its target:

// the three shapes of a JDBC URL:
jdbc:h2:mem:catalog;DB_CLOSE_DELAY=-1    // H2, in-memory (tests, examples)
jdbc:postgresql://localhost:5432/books  // PostgreSQL over TCP
jdbc:mysql://localhost:3306/books        // MySQL over TCP

Modern JDBC finds the driver from the URL automatically, and the ancient Class.forName ritual is gone. As a result, opening a connection is one call. What that call buys you is worth naming. A Connection is a real socket, stateful and expensive, with a session on the database side. That is why the closing discipline and the pooling article both exist.

The First Connection and Query

Here is the complete loop: connect, query, map rows, close. The exceptions article’s try-with-resources closes every resource in reverse order, no matter what happens:

import java.sql.*;

public class FirstQuery {
    public static void main(String[] args) throws SQLException {
        var url = "jdbc:h2:mem:catalog;DB_CLOSE_DELAY=-1";

        try (Connection conn = DriverManager.getConnection(url)) {

            try (Statement setup = conn.createStatement()) {          // create a table
                setup.execute("""
                    CREATE TABLE books (
                        isbn VARCHAR(20) PRIMARY KEY,
                        title VARCHAR(200) NOT NULL,
                        year INT)
                    """);
                setup.execute("""
                    INSERT INTO books VALUES
                        ('978-0134685991', 'Effective Java', 2018)
                    """);
            }

            try (Statement query = conn.createStatement();
                 ResultSet rs = query.executeQuery("SELECT isbn, title FROM books")) {

                while (rs.next()) {                                    // one row at a time
                    System.out.println(rs.getString("isbn")
                            + " - " + rs.getString("title"));
                }
            }
        }   // closing order: rs, then statement, then connection, all guaranteed
    }
}

In short, three mechanics in that block carry all of the JDBC fundamentals. First, a Connection creates statements, and a statement runs SQL and can produce a ResultSet. Each close in the try-with-resources runs in reverse declaration order: rows before statement before connection. That is exactly the nesting that the file tools taught. Second, the cursor model matters. The cursor starts before the first row, and next() returns true and advances onto the next row. So the while loop reads the right way, and a missing next() call is the classic EmptyResult bug. Third, SQLException is the checked exception reminding you the database is another computer, over a network, that can refuse.

PreparedStatement: The Only Statement That Matters

Of course, plain Statement has exactly one legitimate use: fixed SQL you wrote yourself, like the table creation above. However, the moment any input reaches the query, PreparedStatement is mandatory. The reason is the most exploited bug class in the history of databases:

// WRONG: concatenation builds SQL from input: injection, the OWASP headline bug
String sql = "SELECT * FROM users WHERE name = '" + userInput + "'";
// userInput = "x' OR '1'='1"      -> returns every row
// userInput = "x'; DROP TABLE users;--"  -> say goodbye

// RIGHT: parameters travel as DATA, never as SQL structure
String sql = "SELECT * FROM users WHERE name = ?";

try (PreparedStatement ps = conn.prepareStatement(sql)) {
    ps.setString(1, userInput);        // position 1: bound as a value, not parsed
    try (ResultSet rs = ps.executeQuery()) {
        while (rs.next()) { ... }
    }
}

The delta is the difference between data and code. Concatenation makes user input part of the SQL the database parses. In contrast, the placeholder keeps input in the data channel, where OR ‘1’=’1 is just a strange string that matches nothing. The OWASP rule has no exceptions worth memorizing, because there are none. Every dynamic value is a ?, bound with setString, setInt, setObject, at its position. The bonus is real. The same statement precompiles once on the database side and re-binds per execution. So the safe version is also the fast version for repeated queries.

Reading ResultSets: Mapping Rows to Records

The ResultSet cursor hands you one row at a time. Production code’s job is turning each row into a real Java object. The records article is the natural fit. Rows are immutable data, so a Book record per row is the mapping in one loop:

record Book(String isbn, String title, int year) {}

static List<Book> catalog(Connection conn) throws SQLException {
    try (PreparedStatement ps = conn.prepareStatement(
             "SELECT isbn, title, year FROM books ORDER BY title");
         ResultSet rs = ps.executeQuery()) {

        var books = new ArrayList<Book>();
        while (rs.next()) {
            books.add(new Book(
                    rs.getString("isbn"),      // by label: survives column reordering
                    rs.getString("title"),
                    rs.getInt("year")));
        }
        return List.copyOf(books);             // immutable out: records all the way down
    }
}

In particular, four details separate this from a tutorial loop. First, reading by column label, not index, survives a SELECT reorder and documents itself. Second, mapping inside the loop keeps the ResultSet’s lifetime short. Rows become immutable values, and the connection moves on. Third, NULL needs one special rule. getInt on a NULL column returns 0, indistinguishable from a real 0. So read nullable columns with rs.getObject(“col”, Integer.class), or check with rs.wasNull() immediately after the read. Finally, the query selects explicit columns instead of *. That way, the mapping and the SQL agree on shape, and the SELECT cannot silently grow.

Writing: executeUpdate and Generated Keys

Mutations use executeUpdate, which returns the number of affected rows. That return value is information your code should use, because a 0 from an UPDATE means the row was not there:

static void save(Connection conn, Book book) throws SQLException {
    try (PreparedStatement ps = conn.prepareStatement("""
            MERGE INTO books KEY(isbn) VALUES (?, ?, ?)
            """)) {
        ps.setString(1, book.isbn());
        ps.setString(2, book.title());
        ps.setInt(3, book.year());
        ps.executeUpdate();
    }
}

static long insertReturningId(Connection conn, Book book) throws SQLException {
    try (PreparedStatement ps = conn.prepareStatement(
            "INSERT INTO books(isbn, title, year) VALUES (?, ?, ?)",
            Statement.RETURN_GENERATED_KEYS)) {
        ps.setString(1, book.isbn());
        ps.setString(2, book.title());
        ps.setInt(3, book.year());
        ps.executeUpdate();

        try (ResultSet keys = ps.getGeneratedKeys()) {
            keys.next();
            return keys.getLong(1);
        }
    }
}

The generated-key pattern answers the insert’s natural question: the row exists now, so what ID did the database give it? Request the keys at prepare time, then read them from a small ResultSet after the update. The same method shape (prepare, bind, execute, close) is every write you will ever do in raw JDBC. Later, the CRUD article assembles these primitives into a full repository, two articles from now.

How Real Systems Do This

Raw JDBC is not a learning artifact. Instead, it is the layer everything runs on, since every ORM, connection pool, and report tool compiles to these calls. Plenty of high-throughput services even skip the mapping layers deliberately. They run plain JDBC with hand-written row mappers for full control over the SQL. The H2 engine powering this article’s examples is a real relational database. It also serves as the standard in-memory test database. Meanwhile, production-grade testing increasingly runs the same API against the real engine in a container. So the mapping code is identical in both tiers.

Indeed, the injection near-miss that made me a reviewer of SQL first is a two-line story with a career-long lesson. A legacy service built its search query by concatenation in one of the oldest files in the codebase. Then a product feature asked for a new search field. The implementing engineer, competent and careful, extended the concatenation. The only reason it never shipped was a code-review question: where do the quotes come from on this line? Ten minutes of quiet horror followed as we traced what a user could do with a closing quote.

The rewrite to a PreparedStatement took under an hour, yet the vulnerability had existed for years. Afterwards, the penetration test we commissioned confirmed the exploitability we had suspected. The lesson I carry is that SQL concatenation is not a style issue. It is a loaded weapon, and the review question for every SQL string is simple: count the ? placeholders, and if the user input is inside the quotes instead, the review is over until it is not.

Decision Framework

1. Does any user input reach the SQL? PreparedStatement with ? bindings, without exception, and the OWASP rule is the review gate.
2. Is the SQL fixed and written entirely by you, like DDL? Then plain Statement is acceptable, and a text block makes it readable.
3. Are you reading rows? Explicit column list, map by label, one record per row inside the loop, and List.copyOf on the way out.
4. Are nullable columns mapped to primitives? Use getObject with the wrapper type, or wasNull immediately after the read, because getInt on NULL is a lying 0.
5. Are you inserting and needing the new ID? RETURN_GENERATED_KEYS at prepare time, getGeneratedKeys after the update.
6. Do you ignore executeUpdate’s return? Do not, because a 0 from an UPDATE is your not-found signal. Checking it converts silent misses into handled cases.
7. Are multiple statements part of one operation, all-or-nothing? That is the next article’s transactions, because partial writes without them are a data bug waiting for its moment.

When NOT to Use This

• Do not concatenate anything into SQL, not even a table name from a whitelist. Structure belongs to your code, and data belongs to parameters. Instead, choose identifiers that must vary from a map of fixed strings.
• Do not hold a Connection for the lifetime of an application object. Connections are per-operation resources. In fact, the pooling article exists precisely because per-operation open is too slow without pooling, and per-application open is too wrong.
• Do not swallow SQLException with an empty catch. Follow the exceptions article’s discipline: log with the throwable, or translate and rethrow. After all, a database that refuses is a story worth telling.
• Do not map rows into mutable shared objects. Rows are values, and records keep them values. Otherwise, shared mutable row objects recreate the races this course retired in Part 5.
• Do not SELECT * into broad mappings. The explicit column list is the contract between your SQL and your record. Otherwise, * lets schema drift break code at runtime instead of compile time.

Common Mistakes

• String-concatenated SQL with user input. This is the injection bug this article opened with, and the review question that catches it is counting placeholders.
• Forgetting next() before the first read. The cursor starts before the first row, so reading without next() throws or, in edge cases, reads nothing and reports empty.
• Reading by index and then reordering the SELECT. The label read survives, but the index read breaks silently, and the exception arrives as a confusing runtime failure far away.
• getInt on a nullable column. NULL becomes 0, and 0 is a real value, so nullable columns need the wrapper type and getObject.
• Leaking connections outside try-with-resources. Every leak is a held database session, and the debugging article’s pool-exhaustion hang is the exact destination.
• Ignoring the executeUpdate count. The update that changed nothing looks identical to success, so the not-found case becomes a silent no-op.
• One giant method mixing connection, mapping, and business logic. The CRUD article’s repository split exists because that method becomes unmaintainable by the second query.

Key Takeaways

• JDBC fundamentals rest on a standard API over pluggable drivers: a dependency JAR, a URL, and DriverManager.getConnection. The ancient Class.forName ritual is gone.
• A Connection is a real, expensive, stateful resource. Open it per operation, close it with try-with-resources, and pool it in production per the next article.
• PreparedStatement with ? bindings is the only way input meets SQL: injection becomes a harmless strange string, and repeated queries precompile for free.
• ResultSet is a one-row-at-a-time cursor. Call next() to advance, read by label, map to records inside the loop, and handle NULL with wrapper types.
• executeQuery returns rows, executeUpdate returns a count you should check, and generated keys answer what ID the insert received.
• Row mapping is records doing database work: immutable values in, List.copyOf out. Also, the CSV practice’s row-processing habits transfer directly.
• Every ORM you will meet compiles to these calls. Therefore, this article is the reading key for JPA, Hibernate, and everything above them.

FAQ

What is JDBC in Java?

Java Database Connectivity is the standard API for talking to relational databases. Vendor drivers implement the interface, and DriverManager opens connections from a JDBC URL. SQL then travels through statements, and results come back through ResultSets. Every Java ORM and pool is built on it.

What is PreparedStatement used for in JDBC?

Executing SQL with parameters: the ? placeholders bind user input as typed data, preventing SQL injection by construction, and repeated executions reuse the database’s compiled query plan. It replaces plain Statement for any SQL that involves input.

How do I prevent SQL injection in Java?

Never concatenate input into SQL: every dynamic value becomes a ? in a PreparedStatement, bound with setString, setInt, or setObject. The input then travels as data the database will not parse as SQL, which closes the injection class entirely.

What is ResultSet in JDBC?

A cursor over a query’s rows, starting before the first row. Its next() method advances one row at a time, and getString, getInt, and getObject read columns by label into Java types. Map each row to a record inside the loop and keep the cursor’s lifetime short.

What does a JDBC URL look like?

jdbc:driver-name:target, for example jdbc:h2:mem:catalog for in-memory H2 or jdbc:postgresql://localhost:5432/books for PostgreSQL over TCP. The prefix selects the driver, the rest is driver-specific target and settings.

Conclusion

With these JDBC fundamentals, you can now move data both ways between Java objects and relational tables. You have connections through the driver of your choice and safe parameterized SQL. You also have rows mapped to records and writes with counts and generated keys, all closed by structure. That is the complete mechanics of persistence in Java, so everything fancier builds on it.

The next article solves the two problems this one deliberately left open. Connections are expensive to open per operation, so pooling amortizes them. Likewise, multi-statement operations must be all-or-nothing, so transactions group them. Together they turn these mechanics into a production data layer.

Parameters are data, rows are records, and every resource closes itself. The database is now just another API you happen to speak over a socket.

Last updated on 20 September 2026.