Home/Learn/Java A–Z/JDBC — Database Connectivity

JDBC — Database Connectivity

Q: How does PreparedStatement prevent SQL injection?

How does PreparedStatement prevent SQL injection? - A Easy difficulty Java interview question commonly asked at Amazon. Study JDBC — Database Connectivity in Java to answer this.

Q: What is a connection pool and why is it important?

What is a connection pool and why is it important? - A Easy difficulty Java interview question commonly asked at Google. Study JDBC — Database Connectivity in Java to answer this.

Q: How do you handle transactions in JDBC?

How do you handle transactions in JDBC? - A Medium difficulty Java interview question commonly asked at Oracle. Study JDBC — Database Connectivity in Java to answer this.

Q: What happens if you forget to close a ResultSet?

What happens if you forget to close a ResultSet? - A Medium difficulty Java interview question commonly asked at Microsoft. Study JDBC — Database Connectivity in Java to answer this.

Intermediate

I/O and Networking

JDBC is Java's standard API for connecting to relational databases — executing SQL queries, updates, and transactions.

Overview

JDBC (Java Database Connectivity) provides a unified API for connecting to any relational database that has a JDBC driver. Core types: DriverManager (creates connections), Connection, Statement/PreparedStatement (executes SQL), ResultSet (holds query results). For production use, always use a connection pool (HikariCP), PreparedStatement (to prevent SQL injection), and transactions with proper commit/rollback.

Connecting and Querying

DriverManager.getConnection() creates a connection from a JDBC URL. Modern drivers auto-register with the DriverManager via ServiceLoader.

Always use try-with-resources for Connection, Statement, and ResultSet — they hold database resources that must be released even on exception.

JdbcQuery.java

import java.sql.*;

String url  = "jdbc:postgresql://localhost:5432/mydb";
String user = "admin";
String pass = "secret";

try (Connection conn = DriverManager.getConnection(url, user, pass);
     PreparedStatement ps = conn.prepareStatement(
         "SELECT id, name, email FROM users WHERE active = ?")) {

    ps.setBoolean(1, true);

    try (ResultSet rs = ps.executeQuery()) {
        while (rs.next()) {
            int    id    = rs.getInt("id");
            String name  = rs.getString("name");
            String email = rs.getString("email");
            System.out.printf("%d: %s <%s>%n", id, name, email);
        }
    }
}

PreparedStatement and SQL Injection Prevention

Never build SQL by concatenating user input — it allows SQL injection attacks. PreparedStatement uses ? placeholders that are parameterised at the driver level, making injection impossible.

PreparedStatement also improves performance for repeated queries because the database can cache the query plan.

PreparedStmt.java

// DANGER — SQL injection vulnerability
String name = "'; DROP TABLE users; --";
Statement stmt = conn.createStatement();
stmt.execute("SELECT * FROM users WHERE name = '" + name + "'");

// SAFE — PreparedStatement
PreparedStatement ps = conn.prepareStatement(
    "SELECT * FROM users WHERE name = ?");
ps.setString(1, name); // safely escaped by driver
ResultSet rs = ps.executeQuery();

// INSERT with generated keys
PreparedStatement insert = conn.prepareStatement(
    "INSERT INTO users (name, email) VALUES (?, ?)",
    Statement.RETURN_GENERATED_KEYS);
insert.setString(1, "Alice");
insert.setString(2, "alice@example.com");
insert.executeUpdate();

try (ResultSet keys = insert.getGeneratedKeys()) {
    if (keys.next()) System.out.println("New ID: " + keys.getLong(1));
}

Transactions and Connection Pooling

By default, JDBC auto-commits each statement. For multi-step operations, disable auto-commit, execute statements, then commit or rollback on exception.

Connection pooling (HikariCP is the standard) reuses connections, dramatically reducing latency. Never create a new connection per request in production.

Transactions.java

// Transaction
conn.setAutoCommit(false);
try {
    PreparedStatement debit = conn.prepareStatement(
        "UPDATE accounts SET balance = balance - ? WHERE id = ?");
    debit.setBigDecimal(1, amount);
    debit.setLong(2, fromId);
    debit.executeUpdate();

    PreparedStatement credit = conn.prepareStatement(
        "UPDATE accounts SET balance = balance + ? WHERE id = ?");
    credit.setBigDecimal(1, amount);
    credit.setLong(2, toId);
    credit.executeUpdate();

    conn.commit();
} catch (SQLException e) {
    conn.rollback();
    throw e;
}

// HikariCP connection pool (application startup)
HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:postgresql://localhost:5432/mydb");
config.setUsername("admin");
config.setPassword("secret");
config.setMaximumPoolSize(20);
HikariDataSource dataSource = new HikariDataSource(config);

Key Points to Remember

Always use PreparedStatement — never concatenate user input into SQL strings.
Use try-with-resources for Connection, PreparedStatement, and ResultSet.
Disable auto-commit for multi-step transactions; call rollback() on exception.
Use connection pooling (HikariCP) in production — creating a connection per request is very slow.
JDBC URLs follow the pattern: jdbc:<driver>://<host>:<port>/<database>.

Practice JDBC — Database Connectivity in the Playground

Run and modify code directly in your browser - no setup needed.

Interview Questions

What is the difference between Statement and PreparedStatement?

EasyTCS

How does PreparedStatement prevent SQL injection?

EasyAmazon

What is a connection pool and why is it important?

EasyGoogle

How do you handle transactions in JDBC?

MediumOracle

What happens if you forget to close a ResultSet?

MediumMicrosoft

Ask Aria about JDBC — Database Connectivity

Your personal AI tutor — ask anything about this concept

Socket Programming

Inner and Nested Classes