JDBC Core APIs in Depth

The previous chapter showed you how to open a connection and run basic CRUD with Statement. That was enough to get started, but it left two big problems unsolved: SQL injection and rigid result set handling. In this chapter we dive into the five interfaces you will use in every real JDBC application: DriverManager, Connection, PreparedStatement, CallableStatement, and ResultSet. By the end you will know how to write safe, parameterized queries and how to inspect result sets dynamically.

Prerequisites

• MySQL running with the jdbc_demo database
• The users table created in the previous chapter
• A Maven project with mysql-connector-j in pom.xml

DriverManager Revisited

DriverManager is the entry point for every JDBC connection. You have already used getConnection(url, user, password), but there are a few useful details worth knowing.

Listing Loaded Drivers

Since JDBC 4.0, drivers register themselves automatically through the ServiceLoader mechanism. You can inspect which drivers are currently available:

On a project with only the MySQL connector, you will see com.mysql.cj.jdbc.Driver.

Connection URL Overloads

DriverManager provides three overloads of getConnection:

// URL only (for drivers that encode credentials inside the URL)
Connection conn = DriverManager.getConnection(url);
 
// URL + Properties object (useful when you read settings from a file)
Properties props = new Properties();
props.setProperty("user", "jdbc_user");
props.setProperty("password", "jdbcpass");
Connection conn = DriverManager.getConnection(url, props);
 
// URL + user + password (the form you have been using)
Connection conn = DriverManager.getConnection(url, "jdbc_user", "jdbcpass");

The Properties version is handy when you externalize configuration, which we will cover in Exception handling and best practices.

Connection Essentials

A Connection is more than a wire to the database. It controls transaction boundaries and exposes metadata about the server.

Quick Metadata Peek

You can ask the connection which database product and version it is talking to:

This is just a taste of DatabaseMetaData. Later, in Advanced topics, you will use it to list tables, columns, and primary keys programmatically.

Transaction Control Preview

By default, every SQL statement runs in its own transaction and commits immediately. This behavior is called auto-commit. You can turn it off when you need to group multiple statements into a single atomic unit:

conn.setAutoCommit(false);
// run inserts, updates, or deletes
conn.commit();   // make permanent
// or
conn.rollback(); // discard everything since the last commit

Transactions covers isolation levels, savepoints, and batching in depth.

PreparedStatement

The UserDao from the previous chapter built SQL by concatenating strings. That pattern is dangerous because it opens the door to SQL injection.

The Injection Problem

Imagine this method:

public int deleteByName(String name) throws SQLException {
    String sql = "DELETE FROM users WHERE name = '" + name + "'";
    // ...
}

If a malicious user passes name as '; DROP TABLE users; --, the resulting SQL becomes:

DELETE FROM users WHERE name = ''; DROP TABLE users; --'

Parameterized Queries

PreparedStatement replaces string concatenation with placeholders. A placeholder is a single ? character. The driver treats the bound values as literal data, not as executable SQL.

Here is the safe version of the same delete method:

Even if name contains quotes or semicolons, the driver escapes them automatically.

Refactoring the UserDao

Here is a fully refactored UserDao that uses PreparedStatement for every variable value:

createTable still uses a plain Statement because the CREATE TABLE DDL has no variable parts.

Retrieving Auto-Generated Keys

When you insert a row into a table with an AUTO_INCREMENT primary key, you usually want the generated ID back. Pass Statement.RETURN_GENERATED_KEYS when you create the PreparedStatement:

Handling NULL Values

Databases distinguish between a real zero and a NULL. PreparedStatement has a dedicated method for binding NULL:

// Insert a user whose age is unknown
ps.setString(1, "Carol");
ps.setString(2, "carol@example.com");
// Mark the third parameter as a SQL NULL of type INTEGER
ps.setNull(3, java.sql.Types.INTEGER);
ps.executeUpdate();

When reading back a nullable integer, use wasNull():

int age = rs.getInt("age");
if (rs.wasNull()) {
    // The database value was NULL, not 0
    age = -1; // or handle it however your application requires
}

Alternatively, rs.getObject("age", Integer.class) returns null for database NULL values, which is often more convenient.

CallableStatement

CallableStatement is JDBC's mechanism for invoking stored procedures. You will use it less frequently than PreparedStatement, but it is important to know it exists.

Creating a Simple Stored Procedure

First, create a procedure in MySQL:

DELIMITER //
CREATE PROCEDURE CountUsers(OUT total INT)
BEGIN
    SELECT COUNT(*) INTO total FROM users;
END //
DELIMITER ;

You can run this in the MySQL client, in DBeaver, or in IntelliJ IDEA's SQL console.

Calling the Procedure from Java

ResultSet Navigation and Types

So far you have only seen forward-only iteration with while (rs.next()). The JDBC specification defines several result set types that give you more control over the cursor.

Requesting a Scrollable ResultSet

When you create a Statement, you can request specific behavior:

ResultSet Type Matrix

MySQL's Connector/J supports TYPE_SCROLL_INSENSITIVE and CONCUR_READ_ONLY well, but CONCUR_UPDATABLE is limited. For most applications, treat ResultSet as read-only and issue explicit UPDATE statements instead.

Reading Data from ResultSet

You already know getInt and getString. Here is the complete picture.

Common Getter Methods

int id          = rs.getInt("id");
String name     = rs.getString("name");
double salary   = rs.getDouble("salary");
java.sql.Date d = rs.getDate("birth_date");
java.sql.Timestamp ts = rs.getTimestamp("created_at");
Object anything = rs.getObject("any_column");

Column Index vs. Column Name

You can retrieve by one-based column index:

int id = rs.getInt(1);      // first column
String name = rs.getString(2); // second column

Column indexes are slightly faster because the driver avoids a name lookup, but they are brittle. If someone reorders the SELECT clause, the indexes shift. Names are safer for maintainability.

Detecting NULL

Primitive return types cannot represent NULL. The driver returns 0, 0.0, or false for null values. Always use wasNull() when a column is nullable:

int age = rs.getInt("age");
if (rs.wasNull()) {
    // age is undefined in the database
} else {
    // age is a real integer value
}

For objects, getObject returns Java null:

Integer ageObj = rs.getObject("age", Integer.class);
if (ageObj == null) {
    // database NULL
}

Inspecting ResultSet Metadata

Sometimes you write a generic tool that does not know the column names or types ahead of time. ResultSetMetaData lets you inspect the structure of a result set dynamically.

This pattern is the foundation of generic query browsers, report generators, and data-export utilities.

FAQ

What is the difference between Statement and PreparedStatement?

Statement executes a static SQL string exactly as provided. PreparedStatement sends the SQL template to the database for compilation first, then binds parameters separately. This prevents SQL injection and usually improves performance when the same statement is reused.

Can PreparedStatement prevent all SQL injection?

No. It protects values bound via ? placeholders, but it cannot guard against injection in structural parts of the SQL string, such as table names, column names, or sort order. Never concatenate user input into the SQL template itself; validate identifiers against a whitelist if they must be dynamic.

When should I use CallableStatement?

Use it when you need to call a stored procedure or function that lives inside the database. For ordinary CRUD, PreparedStatement is sufficient and preferred.

Why does ResultSet column indexing start at 1?

JDBC follows the SQL standard, which numbers columns starting at one. It is a frequent source of bugs for developers used to zero-based indexing.

What happens if I forget to close a ResultSet?

You risk memory leaks on the client and open cursors on the database server, which can eventually exhaust the server's cursor limit. Always declare ResultSet inside a try-with-resources block, or rely on it being closed automatically when its parent Statement is closed.

What is the difference between TYPE_SCROLL_INSENSITIVE and TYPE_SCROLL_SENSITIVE?

TYPE_SCROLL_INSENSITIVE captures a snapshot of the data at query time; changes made by other transactions are not visible as you scroll. TYPE_SCROLL_SENSITIVE theoretically reflects changes immediately, but many drivers, including MySQL Connector/J, do not fully support it. For portable code, assume insensitive behavior.

Is CONCUR_UPDATABLE a good way to edit data?

Generally no. It is driver-dependent and can behave inconsistently across databases. It is safer to read data with a CONCUR_READ_ONLY result set and then issue an explicit UPDATE or DELETE via PreparedStatement.