JDBC (Java Database Connectivity) is a standard Java API that lets applications interact with relational databases using SQL, regardless of which database vendor is used underneath.

import java.sql.*;

public class JdbcExample {
    public static void main(String[] args) {
        String url = 'jdbc:mysql://localhost:3306/mydb';
        String user = 'root';
        String password = 'password';

        try (Connection conn = DriverManager.getConnection(url, user, password);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery('SELECT id, name FROM users')) {

            while (rs.next()) {
                int id = rs.getInt('id');
                String name = rs.getString('name');
                System.out.println(id + ': ' + name);
            }
        } catch (SQLException e) {
            System.out.println('Database error: ' + e.getMessage());
        }
        // try-with-resources closes Connection, Statement, and ResultSet automatically
    }
}

// Statement - VULNERABLE to SQL injection if using user input
Statement stmt = conn.createStatement();
String email = userInput;  // e.g. "' OR '1'='1"
ResultSet rs = stmt.executeQuery('SELECT * FROM users WHERE email = \'' + email + '\'');
// A malicious input can alter the query logic entirely - NEVER do this

// PreparedStatement - SAFE, parameters bound separately from SQL
String sql = 'SELECT * FROM users WHERE email = ?';
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, email);  // treated strictly as data, never as SQL code
ResultSet rs2 = pstmt.executeQuery();

// PreparedStatement for INSERT/UPDATE
PreparedStatement insertStmt = conn.prepareStatement(
    'INSERT INTO users (name, email) VALUES (?, ?)');
insertStmt.setString(1, 'John');
insertStmt.setString(2, 'john@example.com');
insertStmt.executeUpdate();

// CallableStatement - for calling a stored procedure
CallableStatement cstmt = conn.prepareCall('{call getUserById(?)}');
cstmt.setInt(1, 5);
ResultSet rs3 = cstmt.executeQuery();

// Stored procedure with an OUT parameter
CallableStatement cstmt2 = conn.prepareCall('{call getUserCount(?)}');
cstmt2.registerOutParameter(1, java.sql.Types.INTEGER);
cstmt2.execute();
int count = cstmt2.getInt(1);

PreparedStatement sends the SQL query structure to the database SEPARATELY from the parameter values. The database compiles the query first, then binds parameters strictly as data - they can never be interpreted as executable SQL, no matter what they contain.

// Demonstrating the attack against raw Statement concatenation
String maliciousInput = "' OR '1'='1";
String sql = "SELECT * FROM users WHERE email = '" + maliciousInput + "'";
// Resulting query: SELECT * FROM users WHERE email = '' OR '1'='1'
// This returns ALL users, bypassing authentication entirely!

// PreparedStatement neutralizes this completely
PreparedStatement pstmt = conn.prepareStatement(
    'SELECT * FROM users WHERE email = ?');
pstmt.setString(1, maliciousInput);
// The database treats "' OR '1'='1" as a LITERAL STRING VALUE to search for,
// not as part of the SQL syntax - the query correctly finds zero matches

// Best practices to always follow:
// 1. NEVER build SQL by concatenating user input
// 2. ALWAYS use PreparedStatement with ? placeholders for any dynamic value
// 3. Use setXxx() methods (setString, setInt, etc.) instead of manual string building
// 4. Apply the principle of least privilege to the database user's permissions

PreparedStatement safeInsert = conn.prepareStatement(
    'INSERT INTO comments (user_id, text) VALUES (?, ?)');
safeInsert.setInt(1, userId);
safeInsert.setString(2, userComment);  // safe even if userComment contains SQL syntax
safeInsert.executeUpdate();

Transactions group multiple SQL operations so they either all succeed (commit) or all fail together (rollback), preserving data consistency. JDBC connections are in auto-commit mode by default, which must be disabled to manage transactions manually.

Connection conn = null;
try {
    conn = DriverManager.getConnection(url, user, password);
    conn.setAutoCommit(false);  // start manual transaction control

    PreparedStatement debit = conn.prepareStatement(
        'UPDATE accounts SET balance = balance - ? WHERE id = ?');
    debit.setDouble(1, 100);
    debit.setInt(2, fromAccountId);
    debit.executeUpdate();

    PreparedStatement credit = conn.prepareStatement(
        'UPDATE accounts SET balance = balance + ? WHERE id = ?');
    credit.setDouble(1, 100);
    credit.setInt(2, toAccountId);
    credit.executeUpdate();

    conn.commit();  // both updates succeed together
    System.out.println('Transfer successful');

} catch (SQLException e) {
    if (conn != null) {
        try {
            conn.rollback();  // undo BOTH updates if anything failed
            System.out.println('Transaction rolled back');
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }
} finally {
    if (conn != null) {
        try {
            conn.setAutoCommit(true);  // restore default behavior
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

// Savepoints - partial rollback within a larger transaction
Savepoint savepoint = conn.setSavepoint('beforeRiskyOperation');
try {
    // risky operation
} catch (SQLException e) {
    conn.rollback(savepoint);  // rolls back only to the savepoint, not the whole transaction
}

Connection pooling maintains a reusable pool of database connections instead of opening and closing a new one for every request - creating a connection is expensive (TCP handshake, authentication), so reuse dramatically improves performance.

// Using HikariCP - the most popular JDBC connection pool library
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

HikariConfig config = new HikariConfig();
config.setJdbcUrl('jdbc:mysql://localhost:3306/mydb');
config.setUsername('root');
config.setPassword('password');
config.setMaximumPoolSize(10);      // max connections held in the pool
config.setMinimumIdle(2);           // minimum idle connections kept ready
config.setConnectionTimeout(30000); // max wait time for a free connection (ms)

HikariDataSource dataSource = new HikariDataSource(config);

// Using a connection FROM the pool
try (Connection conn = dataSource.getConnection();
     PreparedStatement pstmt = conn.prepareStatement('SELECT * FROM users')) {
    ResultSet rs = pstmt.executeQuery();
    while (rs.next()) {
        System.out.println(rs.getString('name'));
    }
}
// conn.close() here does NOT actually close the connection -
// it RETURNS it to the pool for reuse, which is much cheaper than
// tearing down and re-establishing a fresh database connection

// Other popular pooling libraries: Apache DBCP, C3P0
// Spring Boot applications commonly configure HikariCP as the default

Statement stmt = conn.createStatement();

// executeQuery() - for SELECT, returns rows
ResultSet rs = stmt.executeQuery('SELECT * FROM users');
while (rs.next()) {
    System.out.println(rs.getString('name'));
}

// executeUpdate() - for INSERT/UPDATE/DELETE, returns affected row count
int rowsInserted = stmt.executeUpdate(
    "INSERT INTO users (name, email) VALUES ('John', 'john@example.com')");
System.out.println(rowsInserted + ' row(s) inserted');

int rowsUpdated = stmt.executeUpdate(
    "UPDATE users SET name = 'Jane' WHERE id = 1");
System.out.println(rowsUpdated + ' row(s) updated');

// execute() - generic, used when statement type isn't known ahead of time
boolean isResultSet = stmt.execute('SELECT * FROM users');
if (isResultSet) {
    ResultSet result = stmt.getResultSet();
} else {
    int updateCount = stmt.getUpdateCount();
}

// Using execute() for statements that could be either (e.g. stored procedures)
boolean hasResults = stmt.execute('{call processOrder(1)}');

// Common mistake: using executeQuery() for an INSERT statement
// stmt.executeQuery('INSERT INTO users ...');  // SQLException: not a SELECT

SQLException is a checked exception carrying database-specific error information (SQL state, vendor error code, and a chain of related exceptions), and connections/statements/result sets must be properly closed even when errors occur.

public List<User> getUsers() {
    List<User> users = new ArrayList<>();
    String sql = 'SELECT id, name FROM users';

    try (Connection conn = dataSource.getConnection();
         PreparedStatement pstmt = conn.prepareStatement(sql);
         ResultSet rs = pstmt.executeQuery()) {

        while (rs.next()) {
            users.add(new User(rs.getInt('id'), rs.getString('name')));
        }

    } catch (SQLException e) {
        // SQLException carries extra diagnostic info beyond getMessage()
        System.out.println('SQL State: ' + e.getSQLState());
        System.out.println('Error Code: ' + e.getErrorCode());
        System.out.println('Message: ' + e.getMessage());

        // SQLException can be CHAINED - multiple related exceptions
        SQLException next = e.getNextException();
        while (next != null) {
            System.out.println('Chained: ' + next.getMessage());
            next = next.getNextException();
        }

        throw new RuntimeException('Failed to fetch users', e);  // wrap and rethrow with context
    }
    return users;
}

// try-with-resources handles closing automatically, even on exceptions -
// this avoids the old, verbose pattern of manually closing in finally blocks:
// finally {
//     if (rs != null) try { rs.close(); } catch (SQLException e) {}
//     if (pstmt != null) try { pstmt.close(); } catch (SQLException e) {}
//     if (conn != null) try { conn.close(); } catch (SQLException e) {}
// }

// Common SQLState prefixes: '08' = connection error, '23' = integrity
// constraint violation (e.g. duplicate key), '42' = syntax error

Batch processing groups multiple SQL statements together and sends them to the database in a SINGLE round trip, dramatically reducing network overhead compared to executing statements one at a time.

// WITHOUT batching - one round trip PER insert, very slow for bulk data
PreparedStatement pstmt = conn.prepareStatement(
    'INSERT INTO users (name, email) VALUES (?, ?)');
for (User user : userList) {
    pstmt.setString(1, user.getName());
    pstmt.setString(2, user.getEmail());
    pstmt.executeUpdate();  // network round trip for EVERY single row
}

// WITH batching - all statements sent together in one round trip
Connection conn = dataSource.getConnection();
conn.setAutoCommit(false);  // batches typically combined with transactions

PreparedStatement batchStmt = conn.prepareStatement(
    'INSERT INTO users (name, email) VALUES (?, ?)');

for (User user : userList) {
    batchStmt.setString(1, user.getName());
    batchStmt.setString(2, user.getEmail());
    batchStmt.addBatch();  // queue this set of parameters, don't execute yet
}

int[] results = batchStmt.executeBatch();  // executes ALL queued statements together
conn.commit();

System.out.println('Inserted ' + results.length + ' rows');

// For very large batches, execute in chunks to avoid excessive memory use
int batchSize = 1000;
int count = 0;
for (User user : userList) {
    batchStmt.setString(1, user.getName());
    batchStmt.setString(2, user.getEmail());
    batchStmt.addBatch();
    if (++count % batchSize == 0) {
        batchStmt.executeBatch();  // flush periodically
    }
}
batchStmt.executeBatch();  // flush any remaining statements
conn.commit();

// Performance impact: batching 10,000 inserts can be 10-50x faster
// than executing them individually, due to reduced network round trips

// JDBC - manual mapping from ResultSet to object
PreparedStatement pstmt = conn.prepareStatement('SELECT * FROM users WHERE id = ?');
pstmt.setInt(1, userId);
ResultSet rs = pstmt.executeQuery();
User user = null;
if (rs.next()) {
    user = new User();
    user.setId(rs.getInt('id'));
    user.setName(rs.getString('name'));
    user.setEmail(rs.getString('email'));
}

// Hibernate (ORM) - object-relational mapping via annotations
@Entity
@Table(name = 'users')
class User {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int id;
    private String name;
    private String email;
    // getters/setters
}

// Fetching with Hibernate - no manual SQL or ResultSet mapping needed
Session session = sessionFactory.openSession();
User user2 = session.get(User.class, userId);

// HQL (Hibernate Query Language) - object-oriented, not raw SQL
Query<User> query = session.createQuery('FROM User WHERE email = :email', User.class);
query.setParameter('email', 'john@example.com');
User result = query.uniqueResult();

// When to use which:
// JDBC: fine-grained control, performance-critical raw queries, simple apps
// Hibernate/JPA: complex domain models, less boilerplate, faster development

When inserting a row into a table with an auto-increment primary key, JDBC lets you retrieve that generated key immediately using RETURN_GENERATED_KEYS, avoiding a separate SELECT query.

String sql = 'INSERT INTO users (name, email) VALUES (?, ?)';

// Request generated keys when preparing the statement
PreparedStatement pstmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
pstmt.setString(1, 'John');
pstmt.setString(2, 'john@example.com');

int affectedRows = pstmt.executeUpdate();

if (affectedRows > 0) {
    try (ResultSet generatedKeys = pstmt.getGeneratedKeys()) {
        if (generatedKeys.next()) {
            long newId = generatedKeys.getLong(1);  // the auto-generated primary key
            System.out.println('New user ID: ' + newId);
        }
    }
}

// Alternative: specifying which column names to retrieve (some drivers require this)
PreparedStatement pstmt2 = conn.prepareStatement(sql, new String[]{'id'});

// Common use case: inserting a parent row, then using its generated ID
// for a related child row insert within the same transaction
conn.setAutoCommit(false);
try {
    long orderId;
    PreparedStatement orderStmt = conn.prepareStatement(
        'INSERT INTO orders (customer_id) VALUES (?)', Statement.RETURN_GENERATED_KEYS);
    orderStmt.setInt(1, customerId);
    orderStmt.executeUpdate();
    try (ResultSet keys = orderStmt.getGeneratedKeys()) {
        keys.next();
        orderId = keys.getLong(1);
    }

    PreparedStatement itemStmt = conn.prepareStatement(
        'INSERT INTO order_items (order_id, product) VALUES (?, ?)');
    itemStmt.setLong(1, orderId);
    itemStmt.setString(2, 'Widget');
    itemStmt.executeUpdate();

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