Unit 14: JDBC Database Programming

Opening Hook — The Database Behind Every Ticket

Learning Outcomes — Bloom's Taxonomy Mapped

Concept Explanation — JDBC from Scratch

1. JDBC Architecture — The 4-Tier Bridge

Think of JDBC as a universal translator between your Java program and any database. Just like a NEFT wire transfer — your Java app (your bank) sends SQL (payment instruction) through JDBC (banking network) to the Database (recipient bank). You don't need to know the internal workings of the recipient bank; JDBC handles the protocol.

Architecture Diagram
┌──────────────────────┐
│   JAVA APPLICATION   │  ← Your code (main(), servlets, Spring)
│   (Tier 1)           │
└────────┬─────────────┘
         │  calls java.sql API
┌────────▼─────────────┐
│      JDBC API        │  ← Interfaces: Connection, Statement,
│   (Tier 2)           │     ResultSet, PreparedStatement
└────────┬─────────────┘
         │  delegates to driver
┌────────▼─────────────┐
│   JDBC DRIVER        │  ← mysql-connector-j.jar / ojdbc.jar
│   (Tier 3)           │     Translates JDBC calls → DB protocol
└────────┬─────────────┘
         │  network (TCP/IP)
┌────────▼─────────────┐
│     DATABASE         │  ← MySQL, PostgreSQL, Oracle, SQL Server
│   (Tier 4)           │     Executes SQL, returns rows
└──────────────────────┘

2. JDBC API Overview — The Core Interfaces

The JDBC API lives in two packages: java.sql (core) and javax.sql (extensions like connection pooling, RowSets). Here are the key players:

The 5-Step JDBC Workflow

Java
// Step 1: Load the driver (auto-loaded in JDBC 4.0+)
// Class.forName("com.mysql.cj.jdbc.Driver");  // Only needed for older JDBC

// Step 2: Establish connection
Connection conn = DriverManager.getConnection(
    "jdbc:mysql://localhost:3306/college_db",
    "root", "password");

// Step 3: Create statement
PreparedStatement pstmt = conn.prepareStatement(
    "SELECT * FROM students WHERE roll_no = ?");
pstmt.setInt(1, 101);

// Step 4: Execute query & process ResultSet
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
    System.out.println(rs.getString("name") + " — " + rs.getInt("marks"));
}

// Step 5: Close resources (reverse order)
rs.close();
pstmt.close();
conn.close();

3. JDBC Driver Types — Type 1 to Type 4

A JDBC driver is the "translator" that converts JDBC calls into the specific protocol your database understands. There are four types, each with different trade-offs:

4. Statement vs PreparedStatement vs CallableStatement

⚠️ SQL Injection — Why Statement is Dangerous

Java — VULNERABLE CODE
// ❌ DANGEROUS: String concatenation with user input
String userInput = "'; DROP TABLE students; --";
String sql = "SELECT * FROM students WHERE name = '" + userInput + "'";
stmt.execute(sql);
// Executed SQL: SELECT * FROM students WHERE name = ''; DROP TABLE students; --'
// 💀 YOUR ENTIRE STUDENTS TABLE IS DELETED!

Java — SAFE CODE
// ✅ SAFE: PreparedStatement treats input as DATA, not SQL code
String userInput = "'; DROP TABLE students; --";
PreparedStatement pstmt = conn.prepareStatement(
    "SELECT * FROM students WHERE name = ?");
pstmt.setString(1, userInput);
ResultSet rs = pstmt.executeQuery();
// The input is treated as a literal string — no SQL execution.
// ✅ Table is SAFE. Query returns 0 rows (no student with that silly name).

5. ResultSet Navigation

A ResultSet is like a cursor pointing to rows returned by a SELECT query. You start before the first row and call next() to advance:

Java
ResultSet rs = pstmt.executeQuery();

// Pattern: while loop with next()
while (rs.next()) {
    int rollNo     = rs.getInt("roll_no");
    String name    = rs.getString("name");
    double marks   = rs.getDouble("marks");
    Date dob       = rs.getDate("dob");

    System.out.printf("%-5d %-20s %.1f  %s%n", rollNo, name, marks, dob);
}
rs.close();

6. CRUD Operations — Full Code for Each

Setup: Create the Database & Table

SQL — Run in MySQL
CREATE DATABASE college_db;
USE college_db;

CREATE TABLE books (
    book_id    INT PRIMARY KEY AUTO_INCREMENT,
    title      VARCHAR(100) NOT NULL,
    author     VARCHAR(80),
    isbn       VARCHAR(20) UNIQUE,
    price      DECIMAL(8,2),
    copies     INT DEFAULT 1,
    added_date DATE DEFAULT (CURRENT_DATE)
);

CREATE — INSERT a new book

Java
import java.sql.*;

public class InsertBook {
    public static void main(String[] args) {
        String url  = "jdbc:mysql://localhost:3306/college_db";
        String user = "root";
        String pass = "password";

        String sql = "INSERT INTO books (title, author, isbn, price, copies) VALUES (?, ?, ?, ?, ?)";

        try (Connection conn = DriverManager.getConnection(url, user, pass);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setString(1, "Data Structures Using Java");
            pstmt.setString(2, "R. Lafore");
            pstmt.setString(3, "978-0-13-257627-7");
            pstmt.setDouble(4, 599.00);
            pstmt.setInt(5, 5);

            int rows = pstmt.executeUpdate();
            System.out.println(rows + " book(s) inserted successfully!");

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

READ — SELECT all books

Java
public class SelectBooks {
    public static void main(String[] args) {
        String url  = "jdbc:mysql://localhost:3306/college_db";
        String sql  = "SELECT * FROM books ORDER BY title";

        try (Connection conn = DriverManager.getConnection(url, "root", "password");
             PreparedStatement pstmt = conn.prepareStatement(sql);
             ResultSet rs = pstmt.executeQuery()) {

            System.out.printf("%-5s %-30s %-20s %-10s %-6s%n",
                "ID", "Title", "Author", "Price(₹)", "Copies");
            System.out.println("—".repeat(75));

            while (rs.next()) {
                System.out.printf("%-5d %-30s %-20s ₹%-9.2f %-6d%n",
                    rs.getInt("book_id"),
                    rs.getString("title"),
                    rs.getString("author"),
                    rs.getDouble("price"),
                    rs.getInt("copies"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

UPDATE — Modify book price

Java
public class UpdateBook {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/college_db";
        String sql = "UPDATE books SET price = ?, copies = ? WHERE book_id = ?";

        try (Connection conn = DriverManager.getConnection(url, "root", "password");
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setDouble(1, 649.00);   // new price
            pstmt.setInt(2, 10);         // new copies
            pstmt.setInt(3, 1);          // book_id

            int rows = pstmt.executeUpdate();
            System.out.println(rows + " book(s) updated.");

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

DELETE — Remove a book

Java
public class DeleteBook {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/college_db";
        String sql = "DELETE FROM books WHERE book_id = ?";

        try (Connection conn = DriverManager.getConnection(url, "root", "password");
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setInt(1, 1);  // book_id to delete

            int rows = pstmt.executeUpdate();
            if (rows > 0)
                System.out.println("Book deleted successfully.");
            else
                System.out.println("No book found with that ID.");

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

7. Transaction Management

A transaction is a group of SQL operations that must ALL succeed or ALL fail. Think of it like a bank transfer: debit from Account A AND credit to Account B must both happen — or neither should.

Java — Transaction Example: Library Book Issue
public class IssueBookTransaction {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/college_db";
        Connection conn = null;

        try {
            conn = DriverManager.getConnection(url, "root", "password");

            // ① Disable auto-commit — start transaction
            conn.setAutoCommit(false);

            // ② Decrease copies in books table
            PreparedStatement ps1 = conn.prepareStatement(
                "UPDATE books SET copies = copies - 1 WHERE book_id = ? AND copies > 0");
            ps1.setInt(1, 101);
            int updated = ps1.executeUpdate();

            if (updated == 0) {
                throw new SQLException("No copies available!");
            }

            // ③ Insert issue record
            PreparedStatement ps2 = conn.prepareStatement(
                "INSERT INTO book_issues (book_id, student_id, issue_date) VALUES (?, ?, CURDATE())");
            ps2.setInt(1, 101);
            ps2.setInt(2, 2023001);
            ps2.executeUpdate();

            // ④ COMMIT — both operations succeed
            conn.commit();
            System.out.println("✅ Book issued successfully!");

        } catch (SQLException e) {
            try {
                if (conn != null) {
                    // ⑤ ROLLBACK — undo everything if any step fails
                    conn.rollback();
                    System.out.println("❌ Transaction rolled back: " + e.getMessage());
                }
            } catch (SQLException ex) { ex.printStackTrace(); }
        } finally {
            try {
                if (conn != null) {
                    conn.setAutoCommit(true);
                    conn.close();
                }
            } catch (SQLException e) { e.printStackTrace(); }
        }
    }
}

8. Batch Processing

When you need to insert 1,000 records, executing 1,000 individual queries is painfully slow (1,000 network round trips). Batch processing bundles them into a single trip:

Java — Batch INSERT
String sql = "INSERT INTO students (name, roll_no, branch) VALUES (?, ?, ?)";
PreparedStatement pstmt = conn.prepareStatement(sql);

String[][] data = {
    {"Amit Sharma",  "101", "CSE"},
    {"Priya Verma",  "102", "IT"},
    {"Rahul Singh",  "103", "ECE"},
    {"Sneha Patel",  "104", "CSE"},
    {"Vikram Reddy", "105", "ME"}
};

for (String[] row : data) {
    pstmt.setString(1, row[0]);
    pstmt.setInt(2, Integer.parseInt(row[1]));
    pstmt.setString(3, row[2]);
    pstmt.addBatch();  // add to batch, don't execute yet
}

int[] results = pstmt.executeBatch();  // execute ALL at once!
System.out.println("Inserted " + results.length + " records in one batch.");

9. Connection Pooling

Problem: Creating a new database connection takes 200–500ms (TCP handshake + authentication). If your app gets 100 requests/second, creating 100 new connections per second is a disaster.

Solution: Connection Pooling. Keep a "pool" of pre-created connections. When a request needs a connection, borrow one from the pool. When done, return it (don't close it). Think of it like an auto-rickshaw stand — rickshaws wait in a line, passengers take the next available one, and the rickshaw returns to the stand after the trip.

Java — HikariCP Connection Pool (Conceptual)
// In pom.xml: add com.zaxxer:HikariCP:5.1.0 dependency

HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:mysql://localhost:3306/college_db");
config.setUsername("root");
config.setPassword("password");
config.setMaximumPoolSize(10);  // max 10 connections in pool

HikariDataSource ds = new HikariDataSource(config);

// Get connection from pool (near-instant!)
try (Connection conn = ds.getConnection()) {
    // use connection...
}  // auto-returned to pool on close

10. RowSet — Disconnected Data Access

A RowSet is a wrapper around ResultSet that can work in disconnected mode. It fetches data, closes the connection, and lets you work with the data offline. Think of it like downloading a PDF vs reading online — you can read the PDF even without internet.

11. Database Metadata

DatabaseMetaData lets your Java program discover information about the database — name, version, tables, columns — programmatically:

Java
DatabaseMetaData meta = conn.getMetaData();
System.out.println("Database: " + meta.getDatabaseProductName());
System.out.println("Version:  " + meta.getDatabaseProductVersion());
System.out.println("Driver:   " + meta.getDriverName());
System.out.println("URL:      " + meta.getURL());

// List all tables
ResultSet tables = meta.getTables(null, null, "%", new String[]{"TABLE"});
while (tables.next()) {
    System.out.println("Table: " + tables.getString("TABLE_NAME"));
}

12. Full Application — College Library CRUD App

Java — LibraryCRUD.java
import java.sql.*;
import java.util.Scanner;

public class LibraryCRUD {
    private static final String URL  = "jdbc:mysql://localhost:3306/college_db";
    private static final String USER = "root";
    private static final String PASS = "password";

    public static void main(String[] args) {
        Scanner sc = new Scanner(System.in);
        while (true) {
            System.out.println("\n📚 COLLEGE LIBRARY SYSTEM");
            System.out.println("1. Add Book");
            System.out.println("2. Search Books");
            System.out.println("3. Update Book");
            System.out.println("4. Delete Book");
            System.out.println("5. View All Books");
            System.out.println("0. Exit");
            System.out.print("Choose: ");
            int choice = sc.nextInt(); sc.nextLine();

            switch (choice) {
                case 1: addBook(sc);      break;
                case 2: searchBooks(sc);  break;
                case 3: updateBook(sc);   break;
                case 4: deleteBook(sc);   break;
                case 5: viewAllBooks();   break;
                case 0: System.out.println("Goodbye!"); return;
                default: System.out.println("Invalid choice.");
            }
        }
    }

    static void addBook(Scanner sc) {
        System.out.print("Title: ");   String title  = sc.nextLine();
        System.out.print("Author: ");  String author = sc.nextLine();
        System.out.print("ISBN: ");    String isbn   = sc.nextLine();
        System.out.print("Price: ");   double price  = sc.nextDouble();
        System.out.print("Copies: ");  int copies    = sc.nextInt();

        String sql = "INSERT INTO books (title, author, isbn, price, copies) VALUES (?,?,?,?,?)";
        try (Connection c = DriverManager.getConnection(URL,USER,PASS);
             PreparedStatement p = c.prepareStatement(sql)) {
            p.setString(1,title); p.setString(2,author);
            p.setString(3,isbn);  p.setDouble(4,price);
            p.setInt(5,copies);
            System.out.println("✅ " + p.executeUpdate() + " book added.");
        } catch (SQLException e) { System.out.println("Error: "+e.getMessage()); }
    }

    static void searchBooks(Scanner sc) {
        System.out.print("Search keyword: ");
        String kw = "%" + sc.nextLine() + "%";
        String sql = "SELECT * FROM books WHERE title LIKE ? OR author LIKE ?";
        try (Connection c = DriverManager.getConnection(URL,USER,PASS);
             PreparedStatement p = c.prepareStatement(sql)) {
            p.setString(1,kw); p.setString(2,kw);
            ResultSet rs = p.executeQuery();
            while (rs.next()) {
                System.out.printf("[%d] %s by %s — ₹%.2f (%d copies)%n",
                    rs.getInt("book_id"), rs.getString("title"),
                    rs.getString("author"), rs.getDouble("price"),
                    rs.getInt("copies"));
            }
        } catch (SQLException e) { e.printStackTrace(); }
    }

    static void updateBook(Scanner sc) {
        System.out.print("Book ID to update: "); int id = sc.nextInt(); sc.nextLine();
        System.out.print("New price: ");         double price = sc.nextDouble();
        System.out.print("New copies: ");        int copies = sc.nextInt();
        String sql = "UPDATE books SET price=?, copies=? WHERE book_id=?";
        try (Connection c = DriverManager.getConnection(URL,USER,PASS);
             PreparedStatement p = c.prepareStatement(sql)) {
            p.setDouble(1,price); p.setInt(2,copies); p.setInt(3,id);
            System.out.println("✅ " + p.executeUpdate() + " book updated.");
        } catch (SQLException e) { e.printStackTrace(); }
    }

    static void deleteBook(Scanner sc) {
        System.out.print("Book ID to delete: "); int id = sc.nextInt();
        String sql = "DELETE FROM books WHERE book_id=?";
        try (Connection c = DriverManager.getConnection(URL,USER,PASS);
             PreparedStatement p = c.prepareStatement(sql)) {
            p.setInt(1,id);
            System.out.println("🗑️ " + p.executeUpdate() + " book deleted.");
        } catch (SQLException e) { e.printStackTrace(); }
    }

    static void viewAllBooks() {
        String sql = "SELECT * FROM books ORDER BY title";
        try (Connection c = DriverManager.getConnection(URL,USER,PASS);
             PreparedStatement p = c.prepareStatement(sql);
             ResultSet rs = p.executeQuery()) {
            System.out.printf("%-5s %-30s %-20s %-10s %-6s%n",
                "ID","Title","Author","Price","Copies");
            while (rs.next()) {
                System.out.printf("%-5d %-30s %-20s ₹%-9.2f %-6d%n",
                    rs.getInt(1),rs.getString(2),rs.getString(3),
                    rs.getDouble(5),rs.getInt(6));
            }
        } catch (SQLException e) { e.printStackTrace(); }
    }
}

13. Full Application — Student Marks Entry System

SQL — Setup
CREATE TABLE students (
    student_id  INT PRIMARY KEY,
    name        VARCHAR(60) NOT NULL,
    branch      VARCHAR(10),
    semester    INT
);

CREATE TABLE marks (
    mark_id     INT PRIMARY KEY AUTO_INCREMENT,
    student_id  INT,
    subject     VARCHAR(40),
    marks_obt   INT,
    max_marks   INT DEFAULT 100,
    FOREIGN KEY (student_id) REFERENCES students(student_id)
);

Java — StudentMarksSystem.java
import java.sql.*;
import java.util.Scanner;

public class StudentMarksSystem {
    static final String URL = "jdbc:mysql://localhost:3306/college_db";

    public static void main(String[] args) throws SQLException {
        Scanner sc = new Scanner(System.in);
        System.out.print("Enter Student ID: ");
        int sid = sc.nextInt(); sc.nextLine();
        System.out.print("Enter Name: ");
        String name = sc.nextLine();
        System.out.print("Branch: ");
        String branch = sc.nextLine();
        System.out.print("Semester: ");
        int sem = sc.nextInt(); sc.nextLine();

        Connection conn = DriverManager.getConnection(URL, "root", "password");
        conn.setAutoCommit(false);  // transaction start

        try {
            // Insert student
            PreparedStatement ps1 = conn.prepareStatement(
                "INSERT INTO students (student_id, name, branch, semester) VALUES (?,?,?,?)");
            ps1.setInt(1,sid); ps1.setString(2,name);
            ps1.setString(3,branch); ps1.setInt(4,sem);
            ps1.executeUpdate();

            // Insert marks for 5 subjects
            String[] subjects = {"Java", "DBMS", "OS", "Maths", "English"};
            PreparedStatement ps2 = conn.prepareStatement(
                "INSERT INTO marks (student_id, subject, marks_obt) VALUES (?,?,?)");

            for (String sub : subjects) {
                System.out.print("Marks in " + sub + ": ");
                int m = sc.nextInt();
                ps2.setInt(1, sid);
                ps2.setString(2, sub);
                ps2.setInt(3, m);
                ps2.addBatch();
            }
            ps2.executeBatch();

            conn.commit();  // all or nothing
            System.out.println("✅ Student & marks saved successfully!");

        } catch (SQLException e) {
            conn.rollback();
            System.out.println("❌ Rolled back: " + e.getMessage());
        } finally {
            conn.setAutoCommit(true);
            conn.close();
        }
    }
}

Learn by Doing — 3-Tier Lab: College Library CRUD with MySQL

Terminal
mysql -u root -p
# Enter password when prompted
SHOW DATABASES;

SQL
CREATE DATABASE college_db;
USE college_db;
CREATE TABLE books (
    book_id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(100) NOT NULL,
    author VARCHAR(80),
    isbn VARCHAR(20) UNIQUE,
    price DECIMAL(8,2),
    copies INT DEFAULT 1
);
INSERT INTO books (title,author,isbn,price,copies)
  VALUES ('Let Us C','Yashavant Kanetkar','978-81-8333-163-0',350.00,8);

Java
import java.sql.*;

public class TestConnection {
    public static void main(String[] args) {
        try (Connection conn = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/college_db", "root", "password")) {
            System.out.println("✅ Connected to MySQL!");
            System.out.println("Database: " + conn.getCatalog());
        } catch (SQLException e) {
            System.out.println("❌ Connection failed: " + e.getMessage());
        }
    }
}

Terminal
javac TestConnection.java
java -cp ".;lib/mysql-connector-j-8.0.33.jar" TestConnection

Problem Set — Practice Questions

Syntax Questions (5)

1. Write the JDBC URL format for connecting to a MySQL database named shopDB on localhost port 3306.
2. Write a Java statement to create a PreparedStatement that inserts a record into table products(name, price).
3. Write the code to set the 1st parameter of a PreparedStatement to the string "Laptop" and 2nd parameter to 45000.00.
4. Write a while loop that reads all rows from a ResultSet and prints the name (String) and price (double) columns.
5. Write the three lines needed to start a transaction, commit it, and rollback on failure.

Programming Questions (8)

6. Write a complete Java program that connects to MySQL and creates a table employees(id INT PRIMARY KEY, name VARCHAR(50), salary DECIMAL(10,2)).
7. Write a JDBC program to insert 3 employee records using batch processing.
8. Write a program that takes an employee name from the user (Scanner) and searches the employees table using PreparedStatement with LIKE.
9. Write a program that updates the salary of all employees in department "IT" by 10% using a single UPDATE statement.
10. Write a program that deletes all employees with salary less than ₹15,000 and prints how many records were deleted.
11. Write a program that uses DatabaseMetaData to print all table names in the current database.
12. Write a program that transfers ₹5,000 from account A to account B using transaction management (debit A, credit B, commit/rollback).
13. Write a program that reads data from the students table and writes it to a CSV file using ResultSet + FileWriter.

Industry-Scale Questions (3)

Interview Questions (3)

14. Infosys/TCS Interview: "What is the difference between Statement and PreparedStatement? Why is PreparedStatement preferred in production?" (Expected: mention SQL injection, performance, parameterisation)
15. Wipro Interview: "Explain connection pooling. Why is creating a new connection for every request bad? Name one connection pool library." (Expected: mention HikariCP, overhead, resource exhaustion)
16. Capgemini Interview: "What happens if you call conn.commit() when autoCommit is true? What is a dirty read?" (Expected: no-op for commit, explain isolation levels)

MCQ Assessment Bank — 30 Questions (Bloom's Mapped)

Remember / Identify (Q1–Q6)

Understand / Explain (Q7–Q12)

Apply / Implement (Q13–Q18)

Analyze / Compare (Q19–Q23)

Evaluate / Assess (Q24–Q27)

Create / Design (Q28–Q30)

Short Answer Questions (8)

Long Answer Questions (3)

Lab Exercises

All lab exercises for this unit are covered in Section D (Learn by Doing) with the 3-Tier lab structure:

Refer to Section D for detailed instructions, hints, and stretch goals for each tier.

Industry Spotlight — A Day in the Life

Earn With It — Freelance & Income Roadmap

⏱️ Time to First Earning: 3–4 weeks (if you complete all 3 lab tiers and pitch to 10 local businesses)

Chapter Summary

Java — JDBC in a tweet
// JDBC in 5 lines:
Connection c = DriverManager.getConnection(url,u,p);
PreparedStatement ps = c.prepareStatement("SELECT * FROM t WHERE id=?");
ps.setInt(1,42);
ResultSet rs = ps.executeQuery();
while(rs.next()) out.println(rs.getString(1));
// That's it. Java→SQL→DB→Results. 🚀

Earning Checkpoint

✅ Unit 14 complete. MCQs: 30. Ready for Unit 15: Capstone!

[QR: Link to EduArtha video tutorial — JDBC Database Programming]