Java JDBC: Database Connectivity, PreparedStatement & CRUD Operations

Java Database Connectivity (JDBC) is the standard enterprise API (java.sql) enabling Java applications to communicate with relational databases such as MySQL, PostgreSQL, Oracle, and SQLite.

The 5 Steps of JDBC Execution

1. Register Driver: (Automatic since JDBC 4.0 via ServiceLoader; explicitly Class.forName("com.mysql.cj.jdbc.Driver") in older environments).
2. Open Connection: DriverManager.getConnection(url, user, password).
3. Create Statement: Build a pre-compiled PreparedStatement.
4. Execute Query / Update: executeUpdate() for INSERT/UPDATE/DELETE; executeQuery() for SELECT.
5. Process Results & Close: Iterate over ResultSet and close handles via try-with-resources.

Statement vs PreparedStatement: SQL Injection Vulnerability

// DANGEROUS VULNERABLE CODE (DO NOT USE IN PRODUCTION)
String query = "SELECT * FROM users WHERE user = '" + inputUser + "' AND pass = '" + inputPass + "'";
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(query); // Attacker enters: admin' -- and bypasses login!

// 100% SECURE: PREPARED STATEMENT WITH PLACEHOLDERS (?)
String secureSql = "SELECT * FROM users WHERE user = ? AND pass = ?";
PreparedStatement pstmt = conn.prepareStatement(secureSql);
pstmt.setString(1, inputUser); // Parameter safely escaped by database engine
pstmt.setString(2, inputPass);
ResultSet rsSafe = pstmt.executeQuery();

Complete CRUD Example with Try-with-Resources

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

public class JdbcCrudDemo {
    private static final String URL = "jdbc:mysql://localhost:3306/veewom_db?useSSL=false";
    private static final String USER = "root";
    private static final String PASS = "password123";

    public static void main(String[] args) {
        // Step 1 & 2: Establish Connection with try-with-resources
        try (Connection conn = DriverManager.getConnection(URL, USER, PASS)) {
            System.out.println("Connected to Database successfully!");

            // CREATE / INSERT
            String insertSql = "INSERT INTO students (id, name, course, marks) VALUES (?, ?, ?, ?)";
            try (PreparedStatement insertStmt = conn.prepareStatement(insertSql)) {
                insertStmt.setInt(1, 101);
                insertStmt.setString(2, "Aarav Sharma");
                insertStmt.setString(3, "BCA");
                insertStmt.setDouble(4, 94.5);
                int rowsInserted = insertStmt.executeUpdate();
                System.out.println("Inserted rows: " + rowsInserted);
            }

            // READ / SELECT
            String selectSql = "SELECT id, name, course, marks FROM students WHERE marks >= ?";
            try (PreparedStatement selectStmt = conn.prepareStatement(selectSql)) {
                selectStmt.setDouble(1, 90.0);
                
                try (ResultSet rs = selectStmt.executeQuery()) {
                    System.out.println("\n--- Top Performing Students ---");
                    while (rs.next()) {
                        int id = rs.getInt("id");
                        String name = rs.getString("name");
                        String course = rs.getString("course");
                        double marks = rs.getDouble("marks");
                        System.out.printf("ID: %d | Name: %-15s | Course: %s | Marks: %.1f%n", 
                                          id, name, course, marks);
                    }
                }
            }

        } catch (SQLException e) {
            System.err.println("Database Error: " + e.getMessage());
            System.err.println("SQLState: " + e.getSQLState());
            System.err.println("Error Code: " + e.getErrorCode());
        }
    }
}

Transaction Management (commit & rollback)

Transactions ensure ACID properties (Atomicity, Consistency, Isolation, Durability) across money transfers or multi-table updates:

public static void transferFunds(Connection conn, int fromAcc, int toAcc, double amount) 
        throws SQLException {
    try {
        // Disable auto-commit to start a manual transaction
        conn.setAutoCommit(false);

        // Deduct from sender
        try (PreparedStatement debit = conn.prepareStatement(
                "UPDATE accounts SET balance = balance - ? WHERE id = ?")) {
            debit.setDouble(1, amount);
            debit.setInt(2, fromAcc);
            debit.executeUpdate();
        }

        // Credit to receiver
        try (PreparedStatement credit = conn.prepareStatement(
                "UPDATE accounts SET balance = balance + ? WHERE id = ?")) {
            credit.setDouble(1, amount);
            credit.setInt(2, toAcc);
            credit.executeUpdate();
        }

        // Commit both statements atomically
        conn.commit();
        System.out.println("Transaction committed successfully!");
    } catch (SQLException ex) {
        // Rollback all changes if any error occurred
        conn.rollback();
        System.err.println("Transaction aborted! Rolled back to previous safe state.");
        throw ex;
    } finally {
        conn.setAutoCommit(true); // Restore default
    }
}