JDBC Basics: Connection, Statement & ResultSet

JDBC (Java Database Connectivity) is the standard Java API for talking to relational databases. You open a Connection, create a Statement or PreparedStatement, execute SQL, iterate a ResultSet, and close everything. This article covers every step with working code, explains the difference between Statement and PreparedStatement, and shows how try-with-resources eliminates resource-leak bugs.

None

JDBC sits between your application code and a database driver. Four key objects are involved in every database interaction:

Object	Role
`DriverManager`	Bootstrap class: creates `Connection` from a JDBC URL
`Connection`	Session with the database; wraps a TCP connection
`Statement` / `PreparedStatement`	Sends SQL to the server; PreparedStatement uses bind parameters
`ResultSet`	Cursor over query results; advance with `next()`

The driver is discovered automatically via the ServiceLoader mechanism since JDBC 4.0: no Class.forName() call needed.

None

Add the JDBC driver for your database. PostgreSQL is used throughout this article:

XML

<dependency>
  <groupId>org.postgresql</groupId>
  <artifactId>postgresql</artifactId>
  <version>42.7.3</version>
</dependency>

For H2 in-memory testing: com.h2database:h2:2.2.224.

None

DriverManager.getConnection(url, user, password) returns a Connection. Always wrap in try-with-resources so the connection is closed even if an exception occurs:

Java

import java.sql.*;

public class JdbcDemo {

    private static final String URL  = "jdbc:postgresql://localhost:5432/mydb";
    private static final String USER = "myuser";
    private static final String PASS = "secret";

    public static void main(String[] args) throws SQLException {

        // try-with-resources auto-closes the Connection
        try (Connection conn = DriverManager.getConnection(URL, USER, PASS)) {
            System.out.println("Connected: " + conn.getMetaData().getDatabaseProductName());
        }
    }
}

For production code use a connection pool (HikariCP) instead of DriverManager directly. DriverManager creates a new TCP connection on every call.

None

Statement is fine for SQL with no user-supplied parameters. Use executeQuery() for SELECT, executeUpdate() for INSERT/UPDATE/DELETE:

Java

try (Connection conn = DriverManager.getConnection(URL, USER, PASS);
     Statement  stmt = conn.createStatement();
     ResultSet  rs   = stmt.executeQuery("SELECT id, name, email FROM users")) {

    while (rs.next()) {
        long   id    = rs.getLong("id");
        String name  = rs.getString("name");
        String email = rs.getString("email");
        System.out.printf("%-4d %-20s %s%n", id, name, email);
    }
}

Key ResultSet getter methods:

SQL Type	Java Getter
INTEGER / BIGINT	`getInt(col)` / `getLong(col)`
VARCHAR / TEXT	`getString(col)`
NUMERIC / DECIMAL	`getBigDecimal(col)`
BOOLEAN	`getBoolean(col)`
DATE / TIMESTAMP	`getDate(col)` / `getTimestamp(col)`
BLOB / BYTEA	`getBytes(col)` / `getBlob(col)`

None

executeUpdate() returns the number of affected rows:

Java

try (Connection conn = DriverManager.getConnection(URL, USER, PASS);
     Statement  stmt = conn.createStatement()) {

    // INSERT
    int inserted = stmt.executeUpdate(
        "INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com')");
    System.out.println("Rows inserted: " + inserted);

    // UPDATE
    int updated = stmt.executeUpdate(
        "UPDATE users SET email = 'alice2@example.com' WHERE name = 'Alice'");
    System.out.println("Rows updated: " + updated);

    // DELETE
    int deleted = stmt.executeUpdate(
        "DELETE FROM users WHERE name = 'Alice'");
    System.out.println("Rows deleted: " + deleted);
}

Never concatenate user input into SQL strings passed to Statement. This opens SQL injection attacks. Use PreparedStatement with bind parameters for any user-supplied value.

None

Pass Statement.RETURN_GENERATED_KEYS to get the database-assigned primary key after an INSERT:

Java

try (Connection conn = DriverManager.getConnection(URL, USER, PASS);
     Statement  stmt = conn.createStatement()) {

    stmt.executeUpdate(
        "INSERT INTO users (name, email) VALUES ('Bob', 'bob@example.com')",
        Statement.RETURN_GENERATED_KEYS);

    try (ResultSet keys = stmt.getGeneratedKeys()) {
        if (keys.next()) {
            long newId = keys.getLong(1);
            System.out.println("Generated ID: " + newId);
        }
    }
}

None

DatabaseMetaData exposes driver version, supported features, and schema information without writing SQL:

Java

try (Connection conn = DriverManager.getConnection(URL, USER, PASS)) {
    DatabaseMetaData meta = conn.getMetaData();

    System.out.println("DB:      " + meta.getDatabaseProductName()
                     + " " + meta.getDatabaseProductVersion());
    System.out.println("Driver:  " + meta.getDriverName()
                     + " " + meta.getDriverVersion());
    System.out.println("Max conn:" + meta.getMaxConnections());

    // List all tables in the public schema
    try (ResultSet tables = meta.getTables(null, "public", "%", new String[]{"TABLE"})) {
        while (tables.next()) {
            System.out.println("  Table: " + tables.getString("TABLE_NAME"));
        }
    }
}

None

Primitive getters like getInt() return 0 when the column is NULL: call wasNull() immediately after to distinguish zero from NULL:

Java

int score = rs.getInt("score");
boolean isNull = rs.wasNull();   // true if column was SQL NULL

// Safer: use getObject() for nullable columns
Integer scoreBoxed = (Integer) rs.getObject("score");  // null if SQL NULL

None

Task	API
Open connection	`DriverManager.getConnection(url, user, pass)`
Static SQL (no params)	`conn.createStatement()`
Parameterised SQL	`conn.prepareStatement(sql)`
SELECT	`stmt.executeQuery(sql)` → `ResultSet`
DML (INSERT/UPDATE/DELETE)	`stmt.executeUpdate(sql)` → row count
Any SQL	`stmt.execute(sql)` → boolean (true = ResultSet)
Auto-generated keys	`stmt.getGeneratedKeys()`
DB metadata	`conn.getMetaData()`
Resource cleanup	try-with-resources on Connection, Statement, ResultSet