Java Database Programming (JDBC)

Almost all enterprise applications need to interact with databases. Java provides a standard, vendor-independent interface through the JDBC (Java Database Connectivity) API for executing SQL statements and interacting with relational databases.

What is JDBC?

JDBC is a Java API consisting of the java.sql and javax.sql packages. It defines how Java applications connect to and operate on databases. To connect to a specific database (such as MySQL, PostgreSQL, Oracle), you need a JDBC driver provided by that database vendor. This driver is a library that implements JDBC interfaces and is responsible for translating standard JDBC calls into the specific database's protocol.

Core JDBC Components

1. DriverManager: Manages a set of JDBC drivers. Its main function is to use the getConnection() method to create a database connection based on a database URL.
2. Connection: Represents a physical connection to a database. All communication with the database occurs in the context of a Connection.
3. Statement: Used to execute static SQL statements and return results. It has several sub-interfaces, the most commonly used being PreparedStatement.
4. PreparedStatement: A sub-interface of Statement. It represents a precompiled SQL statement that can be executed efficiently multiple times. More importantly, it effectively prevents SQL injection attacks.
5. ResultSet: Represents the result set of an SQL query. It maintains a cursor pointing to its data rows, and you can read data row by row by moving the cursor.

JDBC Operation Steps

Using JDBC to connect to a database typically follows these six steps:

1. Load JDBC Driver: In modern JDBC (4.0+), drivers are usually loaded automatically, so this step can be omitted.
2. Establish Connection: Use DriverManager.getConnection() to obtain a Connection object.
3. Create Statement Object: Create a Statement or PreparedStatement through the Connection object.
4. Execute SQL Statement: Use executeQuery() (for SELECT) or executeUpdate() (for INSERT, UPDATE, DELETE) to execute SQL.
5. Process Result Set: If a query was executed, iterate through the ResultSet object to get the data.
6. Close Resources: Close all resources in reverse order (ResultSet -> Statement -> Connection) to release database connections.

Example: Querying Data with JDBC

Assume we have a MySQL database and have added the MySQL JDBC driver to the project dependencies.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class JdbcExample {
    // Database connection information
    static final String DB_URL = "jdbc:mysql://localhost:3306/mydatabase?useSSL=false&serverTimezone=UTC";
    static final String USER = "root";
    static final String PASS = "password";

    public static void main(String[] args) {
        // Use try-with-resources statement to automatically close resources
        try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASS)) {
            System.out.println("Database connection successful!");

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

            try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
                // Set parameter in SQL statement (index of first ? is 1)
                pstmt.setInt(1, 101);

                // Execute query
                try (ResultSet rs = pstmt.executeQuery()) {
                    // Iterate through result set
                    while (rs.next()) {
                        // Get data by column name or index
                        int id = rs.getInt("id");
                        String name = rs.getString("name");
                        String email = rs.getString("email");

                        System.out.printf("ID: %d, Name: %s, Email: %s%n", id, name, email);
                    }
                }
            }
        } catch (SQLException e) {
            System.err.println("Database operation failed: " + e.getMessage());
            e.printStackTrace();
        }
    }
}

Statement vs. PreparedStatement

• Statement: Recompiles SQL statement each time it executes. If SQL contains user input, it is susceptible to SQL injection.

  // Unsafe example
  String userId = "101 OR 1=1"; // Malicious input
  statement.executeQuery("SELECT * FROM users WHERE id = " + userId);

• PreparedStatement: SQL statement is compiled only once when created. It separates SQL structure from parameters, and parameter values are escaped, thus eliminating SQL injection risks. Better performance, cleaner code, and the preferred approach.

Transaction Management

A transaction is a group of SQL operations that must succeed or fail as a whole. By default, a JDBC connection is in auto-commit mode, meaning each SQL statement is an independent transaction.

You can manually manage transactions:

Connection conn = null;
try {
    conn = DriverManager.getConnection(...);
    // 1. Disable auto-commit
    conn.setAutoCommit(false);

    // 2. Execute multiple SQL operations...
    // statement.executeUpdate(...);
    // statement.executeUpdate(...);

    // 3. If all operations succeed, commit the transaction
    conn.commit();
} catch (SQLException e) {
    // 4. If any error occurs, rollback the transaction
    if (conn != null) {
        try {
            conn.rollback();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }
} finally {
    // Restore auto-commit mode and close connection
    if (conn != null) {
        conn.setAutoCommit(true);
        conn.close();
    }
}