Database Access (JDBC)

JDBC (Java Database Connectivity) is the standard Java API for connecting to and interacting with relational databases. It defines a common set of interfaces — Connection, Statement, ResultSet, and friends — that every database vendor implements with its own driver, so the same Java code can talk to MySQL, PostgreSQL, Oracle, or SQL Server just by swapping the driver and connection URL.

Opening a connection

DriverManager.getConnection() takes a JDBC URL (which encodes the database vendor, host, port, and database name) plus credentials, and returns a Connection — the live link to the database that every subsequent query flows through.

Opening a JDBC connection

Java

String url = "jdbc:postgresql://localhost:5432/mydb";
String user = "app_user";
String password = "secret";

try (Connection conn = DriverManager.getConnection(url, user, password)) {
    System.out.println("Connected: " + !conn.isClosed());
} catch (SQLException e) {
    e.printStackTrace();
}

Statement vs. PreparedStatement

JDBC offers two ways to run SQL: Statement, which executes a literal SQL string, and PreparedStatement, which executes a parameterized SQL template with ? placeholders filled in separately. They look similar, but the difference matters a great deal once user input is involved.

	Statement	PreparedStatement
SQL construction	String concatenation	Parameterized template with `?` placeholders
SQL injection risk	High if user input is concatenated in	None — parameters are never interpreted as SQL
Performance (repeated calls)	Re-parsed every execution	Can be pre-compiled and reused by the driver/DB
Typical use	Fixed, hand-written admin SQL with no external input	Any query involving variable or user-supplied values

Unsafe: building SQL by concatenation

Java

// DO NOT DO THIS — vulnerable to SQL injection
String username = request.getParameter("username");
String sql = "SELECT * FROM users WHERE username = '" + username + "'";
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
// if username is:  ' OR '1'='1
// the query becomes: SELECT * FROM users WHERE username = '' OR '1'='1'
// ...returning every row in the table

Safe: PreparedStatement with a bound parameter

Java

String sql = "SELECT id, email FROM users WHERE username = ?";

try (PreparedStatement ps = conn.prepareStatement(sql)) {
    ps.setString(1, username); // bound as data, never parsed as SQL
    try (ResultSet rs = ps.executeQuery()) {
        while (rs.next()) {
            System.out.println(rs.getInt("id") + " - " + rs.getString("email"));
        }
    }
}

Always use PreparedStatement for user input

Never build SQL by concatenating strings that include user-supplied or otherwise untrusted data. Doing so opens the door to **SQL injection**, one of the most common and most damaging web vulnerabilities — an attacker can craft input that changes the meaning of your query, reads data they shouldn’t see, or even modifies or drops tables. `PreparedStatement` with `?` placeholders and `setXxx(...)` binding eliminates the risk entirely, because the parameter value is always treated as data, never as SQL syntax.

Reading results with ResultSet

A query returns a ResultSet, a cursor positioned before the first row. Call next() to advance one row at a time — it returns false once there are no more rows — and use the typed getters (getInt, getString, getDouble, ...) to read column values, either by 1-based index or by column name.

Iterating a ResultSet

Java

String sql = "SELECT id, name, price FROM products WHERE price > ?";

try (PreparedStatement ps = conn.prepareStatement(sql)) {
    ps.setDouble(1, 50.0);

    try (ResultSet rs = ps.executeQuery()) {
        while (rs.next()) {
            int id = rs.getInt("id");
            String name = rs.getString("name");
            double price = rs.getDouble("price");
            System.out.printf("%d: %s ($%.2f)%n", id, name, price);
        }
    }
}

Inserts, updates, and deletes

Statements that modify data instead of returning rows use executeUpdate(), which returns the number of rows affected.

Inserting a row

Java

String sql = "INSERT INTO products (name, price) VALUES (?, ?)";

try (PreparedStatement ps = conn.prepareStatement(sql)) {
    ps.setString(1, "Wireless Mouse");
    ps.setDouble(2, 24.99);

    int rowsInserted = ps.executeUpdate();
    System.out.println(rowsInserted + " row(s) inserted");
}

try-with-resources, again

Connection, Statement, PreparedStatement, and ResultSet are all AutoCloseable, and every example above opens them in try-with-resources. This matters more with JDBC than almost anywhere else: database connections are a scarce, pooled resource, and a leaked connection can exhaust the pool and take down the rest of the application. Always let try-with-resources close them, even when an exception is thrown mid-query.

Real applications rarely write raw JDBC like this

Most production Java applications don’t hand-write JDBC for every query. They use an ORM or data-access layer built on top of it — **JPA/Hibernate** for object-relational mapping, or **Spring Data JPA** for repository interfaces that generate queries automatically. Those tools still use JDBC underneath; understanding the raw API helps you reason about what they’re doing, debug connection-pool or SQL issues, and appreciate why parameterized queries matter even when a framework is writing the SQL for you.

JDBC is the standard API for Java-to-relational-database communication; vendors provide drivers implementing it.
DriverManager.getConnection(url, user, password) opens a Connection.
PreparedStatement with ? placeholders and setXxx() binding must be used for any query touching user input — never concatenate SQL strings.
ResultSet.next() advances a query cursor row by row; typed getters read column values.
executeUpdate() runs INSERT/UPDATE/DELETE and returns the affected row count.
Close Connection/Statement/ResultSet with try-with-resources to avoid leaking pooled connections.