20 Java JDBC Coding Exercises with Solutions

This collection of 20 Java JDBC exercises walks through everything needed to work with a relational database from Java, starting at a basic connection and ending with a full DAO layer.

You’ll practice connecting to a database, running DDL and CRUD statements, and protecting against SQL injection with PreparedStatement,
Next exercises move into transactions and rollback, batch inserts for bulk data, reading database and result set metadata, safely handling SQL NULL, calling a stored procedure with CallableStatement, storing and retrieving BLOBs, and handling timezone-aware timestamps, finishing with the Data Access Object (DAO) pattern.

Each coding challenge includes a Practice Problem, Hint, Solution code, and detailed Explanation, ensuring you don’t just copy code, but genuinely practice and understand how and why it works.

Also, See: Java Exercises with over 20+ topic-wise sets and 575+ coding questions to practice.

+ Table of Contents (20 Exercises)

Exercise 1: The Handshake, Connecting to a Database
Exercise 2: Create a Schema
Exercise 3: Simple Insert
Exercise 4: Read & Output
Exercise 5: Target and Delete
Exercise 6: Dynamic Registration
Exercise 7: The Vulnerability Fix
Exercise 8: The Price Adjuster
Exercise 9: Search with Wildcards
Exercise 10: Graceful Closures with Try-with-Resources
Exercise 11: Money Transfer (Transactions)
Exercise 12: Bulk Ingestion (Batch Processing)
Exercise 13: Database Inspection (DatabaseMetaData)
Exercise 14: Dynamic Column Reader (ResultSetMetaData)
Exercise 15: Handling Nulls Safely
Exercise 16: The Callable Stored Procedure
Exercise 17: Profile Picture Storage (BLOBs)
Exercise 18: File Downloader (Reading BLOBs)
Exercise 19: Timezones & Timestamps
Exercise 20: Build a DAO Layer

Exercise 1: The Handshake, Connecting to a Database

Problem Statement: Write a Java program that connects to a local database, such as MySQL or PostgreSQL, using DriverManager.getConnection(). Print a success message if the connection succeeds, and catch and print any SQLException.

Purpose: This exercise helps you practice the very first step of any JDBC program, establishing a connection, and handling the checked exception that JDBC methods are required to throw when something goes wrong. It assumes a MySQL server is running locally with the MySQL Connector/J driver available on the classpath.

Given Input: A local MySQL server with a database named company_db, reachable using a URL, username, and password.

Expected Output: Connection successful!

▼ Hint

Store the JDBC URL, username, and password as constants so they are easy to reuse across future exercises.
Wrap the call to DriverManager.getConnection() in a try-catch block, since it declares a checked SQLException.
Always close the connection once you are done with it, even if you print a success message first.

▼ Solution & Explanation

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

public class Main {

    private static final String URL = "jdbc:mysql://localhost:3306/company_db";
    private static final String USER = "root";
    private static final String PASSWORD = "your_password";

    public static void main(String[] args) {
        Connection connection = null;

        try {
            connection = DriverManager.getConnection(URL, USER, PASSWORD);
            System.out.println("Connection successful!");
        } catch (SQLException e) {
            System.out.println("Connection failed: " + e.getMessage());
        } finally {
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    System.out.println("Error closing connection: " + e.getMessage());
                }
            }
        }

        // Usage:
        // Running main() with a valid MySQL server running locally prints "Connection successful!"
    }
}Code language: Java (java)

Explanation:

DriverManager.getConnection(URL, USER, PASSWORD): Opens a connection to the database using the given credentials, and throws a SQLException if the connection cannot be established.
catch (SQLException e): Catches connection failures, such as an unreachable server or invalid credentials, and prints a readable message instead of letting the program crash.
finally { if (connection != null) ... }: Ensures the connection is closed whether or not an exception occurred, since leaving connections open can exhaust a database’s connection pool.
Alternative: You could use a try-with-resources statement instead, which closes the connection automatically without a separate finally block, as demonstrated later in Exercise 10.

Exercise 2: Create a Schema

Problem Statement: Use a JDBC Statement to programmatically create a table named Employees with columns id (Primary Key, Auto-increment), name (VARCHAR), role (VARCHAR), and salary (DOUBLE).

Purpose: This exercise helps you practice running Data Definition Language (DDL) statements through JDBC, the same mechanism used for inserts and updates but applied to schema creation instead of data manipulation.

Given Input: A connection to the company_db database created in Exercise 1, with no Employees table yet present.

Expected Output: Employees table created successfully.

▼ Hint

Write the CREATE TABLE statement as a plain SQL string, just as you would in a database client.
Use connection.createStatement() to obtain a Statement object, then call executeUpdate() on it, since DDL statements are executed the same way as updates in JDBC.
Remember to close the Statement in addition to the Connection once you are finished.

▼ Solution & Explanation

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class Main {

    private static final String URL = "jdbc:mysql://localhost:3306/company_db";
    private static final String USER = "root";
    private static final String PASSWORD = "your_password";

    public static void main(String[] args) {
        String createTableSql = "CREATE TABLE Employees ("
            + "id INT PRIMARY KEY AUTO_INCREMENT, "
            + "name VARCHAR(100), "
            + "role VARCHAR(100), "
            + "salary DOUBLE)";

        Connection connection = null;
        Statement statement = null;

        try {
            connection = DriverManager.getConnection(URL, USER, PASSWORD);
            statement = connection.createStatement();
            statement.executeUpdate(createTableSql);
            System.out.println("Employees table created successfully.");
        } catch (SQLException e) {
            System.out.println("Error creating table: " + e.getMessage());
        } finally {
            closeQuietly(statement);
            closeQuietly(connection);
        }

        // Usage:
        // Running main() creates a new Employees table with columns id, name, role, and salary
    }

    private static void closeQuietly(AutoCloseable resource) {
        if (resource != null) {
            try {
                resource.close();
            } catch (Exception e) {
                System.out.println("Error closing resource: " + e.getMessage());
            }
        }
    }
}Code language: Java (java)

Explanation:

String createTableSql = "CREATE TABLE Employees (...)": Defines the schema as a plain SQL string, with AUTO_INCREMENT letting the database assign each new row’s id automatically.
connection.createStatement(): Creates a basic Statement object suitable for SQL that has no parameters to fill in.
statement.executeUpdate(createTableSql): Executes the DDL statement, JDBC uses the same executeUpdate() method for table creation as it does for inserts, updates, and deletes.
closeQuietly(AutoCloseable resource): A small shared helper that closes a resource and swallows any exception from the close itself, keeping the main logic free of nested try-catch blocks.
Alternative: You could add an IF NOT EXISTS clause to the CREATE TABLE statement, which prevents an error if the table already exists from a previous run.

Exercise 3: Simple Insert

Problem Statement: Insert a hardcoded single record into the Employees table using stmt.executeUpdate().

Purpose: This exercise helps you practice writing data with JDBC and reading the return value of executeUpdate(), which reports how many rows were affected by the statement.

Given Input: The Employees table created in Exercise 2, currently empty.

Expected Output: Rows inserted = 1

▼ Hint

Write a standard INSERT INTO statement with the values hardcoded directly into the SQL string.
Call executeUpdate() instead of executeQuery(), since executeQuery() is reserved for statements that return a ResultSet.
Capture the return value of executeUpdate(), which tells you how many rows were inserted, updated, or deleted.

▼ Solution & Explanation

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class Main {

    private static final String URL = "jdbc:mysql://localhost:3306/company_db";
    private static final String USER = "root";
    private static final String PASSWORD = "your_password";

    public static void main(String[] args) {
        String insertSql = "INSERT INTO Employees (name, role, salary) "
            + "VALUES ('Alice Johnson', 'Software Engineer', 85000)";

        Connection connection = null;
        Statement statement = null;

        try {
            connection = DriverManager.getConnection(URL, USER, PASSWORD);
            statement = connection.createStatement();

            int rowsAffected = statement.executeUpdate(insertSql);
            System.out.println("Rows inserted = " + rowsAffected);
        } catch (SQLException e) {
            System.out.println("Error inserting record: " + e.getMessage());
        } finally {
            closeQuietly(statement);
            closeQuietly(connection);
        }

        // Usage:
        // Running main() inserts one hardcoded employee record and prints "Rows inserted = 1"
    }

    private static void closeQuietly(AutoCloseable resource) {
        if (resource != null) {
            try {
                resource.close();
            } catch (Exception e) {
                System.out.println("Error closing resource: " + e.getMessage());
            }
        }
    }
}Code language: Java (java)

Explanation:

INSERT INTO Employees (name, role, salary) VALUES (...): Omits the id column entirely, letting the database’s auto-increment behavior assign it automatically.
int rowsAffected = statement.executeUpdate(insertSql);: Runs the insert and captures how many rows were affected, which is 1 for a single successful insert.
Hardcoded values: Keeps the exercise focused on the mechanics of inserting through JDBC, real applications would use a PreparedStatement with parameters instead, as introduced in Exercise 6.
Alternative: You could retrieve the auto-generated id of the new row using Statement.RETURN_GENERATED_KEYS and getGeneratedKeys(), which is useful when you need to reference the new record immediately after inserting it.

Exercise 4: Read & Output

Problem Statement: Execute a SELECT * FROM Employees query. Loop through the resulting ResultSet using a while (resultSet.next()) loop and print the employee details neatly to the console.

Purpose: This exercise helps you practice reading query results row by row, the fundamental pattern for consuming any ResultSet in JDBC regardless of how many rows a query returns.

Given Input: The Employees table containing the record inserted in Exercise 3.

Expected Output: 1 | Alice Johnson | Software Engineer | 85000.0

▼ Hint

Use executeQuery() instead of executeUpdate(), since a SELECT statement returns a ResultSet rather than an affected-row count.
Call resultSet.next() inside a while loop, it advances the cursor to the next row and returns false once there are no more rows.
Retrieve each column’s value using the appropriate typed getter, such as getInt(), getString(), or getDouble(), matching the column’s declared type.

▼ Solution & Explanation

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

public class Main {

    private static final String URL = "jdbc:mysql://localhost:3306/company_db";
    private static final String USER = "root";
    private static final String PASSWORD = "your_password";

    public static void main(String[] args) {
        String selectSql = "SELECT * FROM Employees";

        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;

        try {
            connection = DriverManager.getConnection(URL, USER, PASSWORD);
            statement = connection.createStatement();
            resultSet = statement.executeQuery(selectSql);

            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                String role = resultSet.getString("role");
                double salary = resultSet.getDouble("salary");

                System.out.println(id + " | " + name + " | " + role + " | " + salary);
            }
        } catch (SQLException e) {
            System.out.println("Error reading records: " + e.getMessage());
        } finally {
            closeQuietly(resultSet);
            closeQuietly(statement);
            closeQuietly(connection);
        }

        // Usage:
        // Running main() prints one line per employee row, formatted as id | name | role | salary
    }

    private static void closeQuietly(AutoCloseable resource) {
        if (resource != null) {
            try {
                resource.close();
            } catch (Exception e) {
                System.out.println("Error closing resource: " + e.getMessage());
            }
        }
    }
}Code language: Java (java)

Explanation:

resultSet = statement.executeQuery(selectSql);: Runs the query and returns a cursor positioned just before the first row.
while (resultSet.next()): Advances the cursor one row at a time, looping until next() returns false to indicate there are no more rows to read.
resultSet.getInt("id"): Retrieves a column’s value by name rather than by position, which keeps the code readable and resilient to column reordering.
Alternative: You could retrieve columns by their 1-based index instead of by name, such as resultSet.getInt(1), which is slightly faster but more fragile if the column order in the table ever changes.

Exercise 5: Target and Delete

Problem Statement: Write a script that accepts an employee ID via user input using a Scanner, and deletes that specific record from the database.

Purpose: This exercise helps you practice combining console input with a JDBC write operation, a pattern used whenever a program needs to act on data selected by the user at runtime.

Given Input: The Employees table with at least one record, and an employee ID typed at the console prompt.

Expected Output: Rows deleted = 1

▼ Hint

Use a Scanner on System.in to read the ID the user types before opening the database connection.
Build a DELETE FROM Employees WHERE id = ... statement using the value the user provided.
Check the return value of executeUpdate() to confirm whether a matching row was actually found and removed.

▼ Solution & Explanation

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;

public class Exercise5 {

    private static final String URL = "jdbc:mysql://localhost:3306/company_db";
    private static final String USER = "root";
    private static final String PASSWORD = "your_password";

    public static void main(String[] args) {
        Scanner scanner = new Scanner(System.in);
        System.out.print("Enter the employee ID to delete: ");
        int employeeId = scanner.nextInt();

        String deleteSql = "DELETE FROM Employees WHERE id = " + employeeId;

        Connection connection = null;
        Statement statement = null;

        try {
            connection = DriverManager.getConnection(URL, USER, PASSWORD);
            statement = connection.createStatement();

            int rowsAffected = statement.executeUpdate(deleteSql);
            System.out.println("Rows deleted = " + rowsAffected);
        } catch (SQLException e) {
            System.out.println("Error deleting record: " + e.getMessage());
        } finally {
            closeQuietly(statement);
            closeQuietly(connection);
        }

        // Usage:
        // Entering an existing employee ID at the prompt deletes that record and prints "Rows deleted = 1"
    }

    private static void closeQuietly(AutoCloseable resource) {
        if (resource != null) {
            try {
                resource.close();
            } catch (Exception e) {
                System.out.println("Error closing resource: " + e.getMessage());
            }
        }
    }
}Code language: Java (java)

Explanation:

int employeeId = scanner.nextInt();: Reads the ID as an integer directly, so it can be inserted into the SQL string without any type conversion.
"DELETE FROM Employees WHERE id = " + employeeId: Builds the delete statement by concatenating the numeric ID directly into the SQL string.
int rowsAffected = statement.executeUpdate(deleteSql);: Returns 0 if no employee with that ID existed, letting the program report whether the delete actually matched a row.
Alternative: You could use a PreparedStatement with a placeholder for the ID instead of concatenating it directly, which is the safer approach demonstrated with string values in Exercise 6.

Exercise 6: Dynamic Registration

Problem Statement: Create a user sign-up program. Take username, email, and password from the console and safely insert them into a Users table using a PreparedStatement.

Purpose: This exercise helps you practice using PreparedStatement with parameter placeholders, which safely handles arbitrary string input without the risk of malformed or malicious SQL being built through string concatenation.

Given Input: A Users table with username, email, and password columns, and values typed at the console prompts.

Expected Output: Rows inserted = 1

▼ Hint

Write the INSERT statement with ? placeholders instead of the actual values.
Use connection.prepareStatement() to compile the statement once, then bind each value with setString(), matching the placeholder’s position starting from 1.
Call executeUpdate() with no arguments on a PreparedStatement, since the SQL and its parameters are already attached to the statement object.

▼ Solution & Explanation

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Scanner;

public class Main {

    private static final String URL = "jdbc:mysql://localhost:3306/company_db";
    private static final String USER = "root";
    private static final String PASSWORD = "your_password";

    public static void main(String[] args) {
        Scanner scanner = new Scanner(System.in);

        System.out.print("Enter username: ");
        String username = scanner.nextLine();

        System.out.print("Enter email: ");
        String email = scanner.nextLine();

        System.out.print("Enter password: ");
        String password = scanner.nextLine();

        String insertSql = "INSERT INTO Users (username, email, password) VALUES (?, ?, ?)";

        Connection connection = null;
        PreparedStatement statement = null;

        try {
            connection = DriverManager.getConnection(URL, USER, PASSWORD);
            statement = connection.prepareStatement(insertSql);
            statement.setString(1, username);
            statement.setString(2, email);
            statement.setString(3, password);

            int rowsAffected = statement.executeUpdate();
            System.out.println("Rows inserted = " + rowsAffected);
        } catch (SQLException e) {
            System.out.println("Error registering user: " + e.getMessage());
        } finally {
            closeQuietly(statement);
            closeQuietly(connection);
        }

        // Usage:
        // Entering a username, email, and password at the prompts inserts a new row into Users
        // and prints "Rows inserted = 1"
    }

    private static void closeQuietly(AutoCloseable resource) {
        if (resource != null) {
            try {
                resource.close();
            } catch (Exception e) {
                System.out.println("Error closing resource: " + e.getMessage());
            }
        }
    }
}Code language: Java (java)

Explanation:

"INSERT INTO Users (username, email, password) VALUES (?, ?, ?)": Uses ? placeholders instead of embedding the actual values directly into the SQL string.
statement.setString(1, username);: Binds a value to the first placeholder, the database driver handles any special characters in the input safely.
statement.executeUpdate();: Executes the statement with no SQL string argument, since the query and its bound values are already part of the PreparedStatement object.
Alternative: You could hash the password with a library like BCrypt before storing it, since storing raw passwords is unsafe even when the SQL itself is protected against injection.

Exercise 7: The Vulnerability Fix

Problem Statement: Write a method that searches for books by title using a standard Statement, which is vulnerable to SQL injection. Then refactor it into a secure version using PreparedStatement. Test both with a dummy payload like ' OR '1'='1.

Purpose: This exercise helps you see, firsthand, exactly why string-concatenated SQL is dangerous, and confirms that a PreparedStatement genuinely closes that gap rather than just being a stylistic preference.

Given Input: A Books table with a title column, and the payload string ' OR '1'='1 passed in as the search title.

Expected Output: The vulnerable version returns every row in the Books table, while the secure version returns no rows.

▼ Hint

In the vulnerable version, concatenate the title directly into the SQL string inside single quotes.
The payload closes the opening quote early and appends a condition that is always true, which changes the query’s logic entirely rather than searching for a literal title.
In the secure version, use a ? placeholder and setString(), which treats the entire payload as a single literal value no matter what characters it contains.

▼ Solution & Explanation

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

public class Main {

    private static final String URL = "jdbc:mysql://localhost:3306/company_db";
    private static final String USER = "root";
    private static final String PASSWORD = "your_password";

    public static void searchBooksVulnerable(String title) {
        String selectSql = "SELECT * FROM Books WHERE title = '" + title + "'";

        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;

        try {
            connection = DriverManager.getConnection(URL, USER, PASSWORD);
            statement = connection.createStatement();
            resultSet = statement.executeQuery(selectSql);

            while (resultSet.next()) {
                System.out.println(resultSet.getString("title"));
            }
        } catch (SQLException e) {
            System.out.println("Error searching books: " + e.getMessage());
        } finally {
            closeQuietly(resultSet);
            closeQuietly(statement);
            closeQuietly(connection);
        }
    }

    public static void searchBooksSecure(String title) {
        String selectSql = "SELECT * FROM Books WHERE title = ?";

        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;

        try {
            connection = DriverManager.getConnection(URL, USER, PASSWORD);
            statement = connection.prepareStatement(selectSql);
            statement.setString(1, title);
            resultSet = statement.executeQuery();

            while (resultSet.next()) {
                System.out.println(resultSet.getString("title"));
            }
        } catch (SQLException e) {
            System.out.println("Error searching books: " + e.getMessage());
        } finally {
            closeQuietly(resultSet);
            closeQuietly(statement);
            closeQuietly(connection);
        }
    }

    public static void main(String[] args) {
        String payload = "' OR '1'='1";

        System.out.println("Vulnerable search result:");
        searchBooksVulnerable(payload);

        System.out.println("Secure search result:");
        searchBooksSecure(payload);

        // Usage:
        // searchBooksVulnerable("' OR '1'='1") returns every row in the Books table
        // searchBooksSecure("' OR '1'='1") returns no rows, since the payload is treated as a literal title
    }

    private static void closeQuietly(AutoCloseable resource) {
        if (resource != null) {
            try {
                resource.close();
            } catch (Exception e) {
                System.out.println("Error closing resource: " + e.getMessage());
            }
        }
    }
}Code language: Java (java)

Explanation:

"SELECT * FROM Books WHERE title = '" + title + "'": Builds the query by direct string concatenation, so any quote characters inside title become part of the SQL itself rather than staying inside the intended value.
The payload’s effect: ' OR '1'='1 closes the opening quote early and adds a condition that is always true, turning the intended title search into a query that matches every row in the table.
statement.setString(1, title);: Binds the entire payload as a single parameter value in the secure version, so the database treats it as literal text to search for rather than as part of the SQL syntax.
Alternative: You could also mitigate injection risk by using a database user account with the minimum privileges necessary, so that even if a query is compromised, the damage it can do is limited.

Exercise 8: The Price Adjuster

Problem Statement: Create a program that takes two inputs, a product category and a discount percentage, then updates the prices of all products within that category by applying the discount using a PreparedStatement.

Purpose: This exercise helps you practice writing an UPDATE statement that modifies multiple rows at once based on a condition, with the discount calculation performed directly in SQL rather than in Java.

Given Input: A Products table with category and price columns, and a category name and discount percentage typed at the console prompts, such as "Electronics" and 10.

Expected Output: Rows updated = 4

▼ Hint

Write an UPDATE statement that recalculates price using the discount percentage directly in the SQL expression.
Use two ? placeholders, one for the discount percentage and one for the category name in the WHERE clause.
Bind the discount as a double using setDouble(), and the category as a String using setString(), matching each placeholder’s position.

▼ Solution & Explanation

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Scanner;

public class Main {

    private static final String URL = "jdbc:mysql://localhost:3306/company_db";
    private static final String USER = "root";
    private static final String PASSWORD = "your_password";

    public static void main(String[] args) {
        Scanner scanner = new Scanner(System.in);

        System.out.print("Enter product category: ");
        String category = scanner.nextLine();

        System.out.print("Enter discount percentage: ");
        double discountPercent = scanner.nextDouble();

        String updateSql = "UPDATE Products SET price = price - (price * ? / 100) WHERE category = ?";

        Connection connection = null;
        PreparedStatement statement = null;

        try {
            connection = DriverManager.getConnection(URL, USER, PASSWORD);
            statement = connection.prepareStatement(updateSql);
            statement.setDouble(1, discountPercent);
            statement.setString(2, category);

            int rowsAffected = statement.executeUpdate();
            System.out.println("Rows updated = " + rowsAffected);
        } catch (SQLException e) {
            System.out.println("Error updating prices: " + e.getMessage());
        } finally {
            closeQuietly(statement);
            closeQuietly(connection);
        }

        // Usage:
        // Entering "Electronics" and 10 applies a 10 percent discount to every product
        // in the Electronics category and prints the number of rows updated
    }

    private static void closeQuietly(AutoCloseable resource) {
        if (resource != null) {
            try {
                resource.close();
            } catch (Exception e) {
                System.out.println("Error closing resource: " + e.getMessage());
            }
        }
    }
}Code language: Java (java)

Explanation:

SET price = price - (price * ? / 100): Recomputes each matching row’s price using its own current price and the discount percentage, all evaluated by the database itself.
statement.setDouble(1, discountPercent);: Binds the discount percentage to the first placeholder, which appears in the calculation portion of the statement.
statement.setString(2, category);: Binds the category name to the second placeholder in the WHERE clause, restricting the update to matching rows only.
Alternative: You could perform the discount calculation in Java by first reading each product’s price with a SELECT, computing the new value, and issuing a separate UPDATE per row, but that requires far more round trips to the database than a single set-based UPDATE.

Exercise 9: Search with Wildcards

Problem Statement: Write a search query that returns all users whose names start with a specific letter string typed by the user, dynamically mapping the % wildcard using pstmt.setString().

Purpose: This exercise helps you practice combining a LIKE query with a PreparedStatement, showing that the wildcard character itself can be built into the bound value rather than written directly into the SQL string.

Given Input: A Users table containing usernames such as "Alice" and "Albert", with the prefix "Al" typed at the console prompt.

Expected Output:

Alice
Albert

▼ Hint

Write the SQL with LIKE ? in the WHERE clause, leaving the wildcard entirely out of the SQL string itself.
Build the actual pattern in Java by concatenating the user’s input with a trailing %, then bind the whole pattern as one parameter.
This keeps the parameter binding safe from SQL injection while still giving you full control over where the wildcard characters are placed.

▼ Solution & Explanation

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

public class Main {

    private static final String URL = "jdbc:mysql://localhost:3306/company_db";
    private static final String USER = "root";
    private static final String PASSWORD = "your_password";

    public static void main(String[] args) {
        Scanner scanner = new Scanner(System.in);
        System.out.print("Enter starting letters: ");
        String prefix = scanner.nextLine();

        String selectSql = "SELECT * FROM Users WHERE username LIKE ?";

        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;

        try {
            connection = DriverManager.getConnection(URL, USER, PASSWORD);
            statement = connection.prepareStatement(selectSql);
            statement.setString(1, prefix + "%");
            resultSet = statement.executeQuery();

            while (resultSet.next()) {
                System.out.println(resultSet.getString("username"));
            }
        } catch (SQLException e) {
            System.out.println("Error searching users: " + e.getMessage());
        } finally {
            closeQuietly(resultSet);
            closeQuietly(statement);
            closeQuietly(connection);
        }

        // Usage:
        // Entering "Al" at the prompt prints every username that starts with "Al", such as "Alice" and "Albert"
    }

    private static void closeQuietly(AutoCloseable resource) {
        if (resource != null) {
            try {
                resource.close();
            } catch (Exception e) {
                System.out.println("Error closing resource: " + e.getMessage());
            }
        }
    }
}Code language: Java (java)

Explanation:

"SELECT * FROM Users WHERE username LIKE ?": Keeps the % wildcard out of the SQL string entirely, leaving the full pattern to be supplied as a bound parameter.
statement.setString(1, prefix + "%");: Builds the actual search pattern in Java by appending % to the user’s prefix, so the database matches any username starting with that prefix.
Why not concatenate the wildcard into the SQL directly: Doing so would require embedding user input into the SQL string itself, reopening the same injection risk that PreparedStatement is meant to close.
Alternative: You could place the % on both sides of the prefix, such as "%" + prefix + "%", to match the prefix appearing anywhere in the username instead of only at the start.

Exercise 10: Graceful Closures with Try-with-Resources

Problem Statement: Refactor Exercise 4 to implement the try-with-resources pattern, ensuring the Connection, Statement, and ResultSet objects auto-close, avoiding memory and connection leaks.

Purpose: This exercise helps you practice replacing manual finally-block cleanup with Java’s try-with-resources syntax, which closes resources automatically and in the correct reverse order, even when an exception occurs partway through.

Given Input: The same Employees table and SELECT * FROM Employees query used in Exercise 4.

Expected Output: 1 | Alice Johnson | Software Engineer | 85000.0

▼ Hint

Declare the Connection, Statement, and ResultSet inside the parentheses of the try statement itself, separated by semicolons.
Since all three classes implement AutoCloseable, Java closes them automatically once the try block finishes, in the reverse order they were declared.
You still need a catch block for SQLException, try-with-resources only handles closing, not exception handling.

▼ Solution & Explanation

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

public class Main {

    private static final String URL = "jdbc:mysql://localhost:3306/company_db";
    private static final String USER = "root";
    private static final String PASSWORD = "your_password";

    public static void main(String[] args) {
        String selectSql = "SELECT * FROM Employees";

        try (Connection connection = DriverManager.getConnection(URL, USER, PASSWORD);
             Statement statement = connection.createStatement();
             ResultSet resultSet = statement.executeQuery(selectSql)) {

            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                String role = resultSet.getString("role");
                double salary = resultSet.getDouble("salary");

                System.out.println(id + " | " + name + " | " + role + " | " + salary);
            }
        } catch (SQLException e) {
            System.out.println("Error reading records: " + e.getMessage());
        }

        // Usage:
        // Running main() prints every employee row and automatically closes the Connection,
        // Statement, and ResultSet once the try block finishes, even if an exception occurs
    }
}Code language: Java (java)

Explanation:

try (Connection connection = ...; Statement statement = ...; ResultSet resultSet = ...): Declares all three resources inside the try statement, so Java manages closing every one of them automatically.
Closing order: Java closes the declared resources in reverse order, the ResultSet first, then the Statement, then the Connection, which mirrors how they were opened.
No finally block needed: The manual closeQuietly() helper and nested null checks from earlier exercises are no longer necessary, since the compiler generates the equivalent cleanup code automatically.
Alternative: You could still use the older manual try-finally pattern shown in Exercises 1 through 9, which works correctly but requires more boilerplate and is easier to get wrong, for example by forgetting to close one of the resources.

Exercise 11: Money Transfer (Transactions)

Problem Statement: Simulate a bank transfer. Deduct $100 from Account A and add $100 to Account B. Turn off autocommit with conn.setAutoCommit(false), then simulate an intentional runtime crash right after the deduction to confirm the database rolls back with conn.rollback() to its original state.

Purpose: This exercise helps you practice transaction control in JDBC, where a group of related statements must either all succeed together or all fail together, so the database never ends up in a half-updated state.

Given Input: An Accounts table with account_id and balance columns, containing rows for accounts "A" and "B", and a flag to simulate a crash after the deduction step.

Expected Output:

Transfer failed: Simulated crash after deduction
Transaction rolled back, balances restored.

▼ Hint

Call connection.setAutoCommit(false) immediately after opening the connection, this stops JDBC from committing each statement automatically.
Perform the deduction, then throw an exception to simulate the crash before the credit statement ever runs.
Catch the exception, call connection.rollback() to undo everything done since the last commit, and confirm the deducted amount was restored.

▼ Solution & Explanation

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

public class Main {

    private static final String URL = "jdbc:mysql://localhost:3306/company_db";
    private static final String USER = "root";
    private static final String PASSWORD = "your_password";

    public static void transferFunds(String fromAccount, String toAccount, double amount, boolean simulateCrash) {
        Connection connection = null;

        try {
            connection = DriverManager.getConnection(URL, USER, PASSWORD);
            connection.setAutoCommit(false);

            String deductSql = "UPDATE Accounts SET balance = balance - ? WHERE account_id = ?";
            try (PreparedStatement deductStmt = connection.prepareStatement(deductSql)) {
                deductStmt.setDouble(1, amount);
                deductStmt.setString(2, fromAccount);
                deductStmt.executeUpdate();
            }

            if (simulateCrash) {
                throw new RuntimeException("Simulated crash after deduction");
            }

            String addSql = "UPDATE Accounts SET balance = balance + ? WHERE account_id = ?";
            try (PreparedStatement addStmt = connection.prepareStatement(addSql)) {
                addStmt.setDouble(1, amount);
                addStmt.setString(2, toAccount);
                addStmt.executeUpdate();
            }

            connection.commit();
            System.out.println("Transfer completed successfully.");
        } catch (Exception e) {
            System.out.println("Transfer failed: " + e.getMessage());
            try {
                if (connection != null) {
                    connection.rollback();
                    System.out.println("Transaction rolled back, balances restored.");
                }
            } catch (SQLException rollbackException) {
                System.out.println("Rollback failed: " + rollbackException.getMessage());
            }
        } finally {
            closeQuietly(connection);
        }
    }

    public static void main(String[] args) {
        transferFunds("A", "B", 100, true);

        // Usage:
        // transferFunds("A", "B", 100, true) deducts $100 from Account A, then crashes before crediting Account B,
        // and the rollback restores Account A's balance to its original value
    }

    private static void closeQuietly(AutoCloseable resource) {
        if (resource != null) {
            try {
                resource.close();
            } catch (Exception e) {
                System.out.println("Error closing resource: " + e.getMessage());
            }
        }
    }
}Code language: Java (java)

Explanation:

connection.setAutoCommit(false);: Groups every statement issued afterward into a single transaction, none of them become permanent until commit() is called explicitly.
if (simulateCrash) throw new RuntimeException(...): Mimics a real failure occurring mid-transaction, after the deduction has already been sent to the database but before the credit has happened.
connection.rollback();: Undoes every change made since the transaction began, including the deduction, restoring Account A’s balance as if the transfer never started.
Alternative: You could use Java’s try-with-resources together with a custom transaction helper class that automatically calls commit() or rollback() based on whether an exception propagated out of the block, reducing repetitive transaction boilerplate across many methods.

Exercise 12: Bulk Ingestion (Batch Processing)

Problem Statement: Use a PreparedStatement loop alongside .addBatch() and .executeBatch() to insert 1,000 mock user rows efficiently in chunks, instead of hitting the database 1,000 individual times.

Purpose: This exercise helps you practice batching to reduce the number of round trips to the database, a significant performance improvement when inserting or updating large volumes of data.

Given Input: The Users table from earlier exercises, and 1,000 mock username, email, and password combinations to insert.

Expected Output: Inserted 1000 users successfully.

▼ Hint

Prepare the INSERT statement once outside the loop, then bind new values and call addBatch() on each iteration instead of executing immediately.
Call executeBatch() once a batch reaches a chosen chunk size, such as 100 rows, then call clearBatch() to start the next chunk fresh.
Turning off autocommit before the loop and committing once at the end can further reduce overhead, since each individual batch execution would otherwise still commit separately.

▼ Solution & Explanation

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class Main {
    private static final String URL = "jdbc:mysql://localhost:3306/company_db";
    private static final String USER = "root";
    private static final String PASSWORD = "your_password";
    private static final int BATCH_SIZE = 100;
    public static void main(String[] args) {
        String insertSql = "INSERT INTO Users (username, email, password) VALUES (?, ?, ?)";
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            connection = DriverManager.getConnection(URL, USER, PASSWORD);
            connection.setAutoCommit(false);
            statement = connection.prepareStatement(insertSql);
            for (int i = 1; i <= 1000; i++) {
                statement.setString(1, "user" + i);
                statement.setString(2, "user" + i + "@example.com");
                statement.setString(3, "password" + i);
                statement.addBatch();
                if (i % BATCH_SIZE == 0) {
                    statement.executeBatch();
                    statement.clearBatch();
                }
            }
            statement.executeBatch();
            connection.commit();
            System.out.println("Inserted 1000 users successfully.");
        } catch (SQLException e) {
            System.out.println("Error during batch insert: " + e.getMessage());
        } finally {
            closeQuietly(statement);
            closeQuietly(connection);
        }
        // Usage:
        // Running main() inserts 1000 mock user rows in batches of 100,
        // requiring only 10 round trips to the database instead of 1000
    }
    private static void closeQuietly(AutoCloseable resource) {
        if (resource != null) {
            try {
                resource.close();
            } catch (Exception e) {
                System.out.println("Error closing resource: " + e.getMessage());
            }
        }
    }
}Code language: Java (java)

Explanation:

statement.addBatch();: Queues the currently bound parameter values for later execution instead of sending them to the database immediately.
if (i % BATCH_SIZE == 0) { statement.executeBatch(); statement.clearBatch(); }: Sends the queued rows to the database in a single round trip once a full chunk has accumulated, then clears the queue to start the next chunk.
connection.setAutoCommit(false); ... connection.commit();: Wraps every batch execution into one transaction, avoiding a separate commit after each individual batch.
Alternative: You could execute a single batch of all 1,000 rows at once instead of chunking into groups of 100, but very large batches can consume more memory and make it harder to diagnose which row caused a failure if one occurs.

Exercise 13: Database Inspection (DatabaseMetaData)

Problem Statement: Use connection.getMetaData() to extract and print details about your database engine, such as the database product name, version, and a list of all existing table names in your schema.

Purpose: This exercise helps you practice querying the database itself for information about its structure and capabilities, useful for building tools that need to adapt to different database engines or inspect a schema at runtime.

Given Input: A connection to the company_db database, containing the tables created in earlier exercises.

Expected Output:

Database product = MySQL
Database version = 8.0.34
Tables:
- Employees
- Users
- Books

▼ Hint

Call connection.getMetaData() to obtain a DatabaseMetaData object, which exposes information about the database engine itself rather than about any particular query.
Use getDatabaseProductName() and getDatabaseProductVersion() for the engine details.
Call getTables() with a table type filter of "TABLE" to list only user-created tables, then read the TABLE_NAME column from the returned ResultSet.

▼ Solution & Explanation

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

public class Main {

    private static final String URL = "jdbc:mysql://localhost:3306/company_db";
    private static final String USER = "root";
    private static final String PASSWORD = "your_password";

    public static void main(String[] args) {
        Connection connection = null;
        ResultSet tables = null;

        try {
            connection = DriverManager.getConnection(URL, USER, PASSWORD);
            DatabaseMetaData metaData = connection.getMetaData();

            System.out.println("Database product = " + metaData.getDatabaseProductName());
            System.out.println("Database version = " + metaData.getDatabaseProductVersion());

            tables = metaData.getTables(null, null, "%", new String[]{"TABLE"});
            System.out.println("Tables:");
            while (tables.next()) {
                System.out.println("- " + tables.getString("TABLE_NAME"));
            }
        } catch (SQLException e) {
            System.out.println("Error reading metadata: " + e.getMessage());
        } finally {
            closeQuietly(tables);
            closeQuietly(connection);
        }

        // Usage:
        // Running main() prints the database engine's name and version,
        // followed by a list of every table currently defined in the schema
    }

    private static void closeQuietly(AutoCloseable resource) {
        if (resource != null) {
            try {
                resource.close();
            } catch (Exception e) {
                System.out.println("Error closing resource: " + e.getMessage());
            }
        }
    }
}Code language: Java (java)

Explanation:

connection.getMetaData();: Returns an object describing the database engine and its capabilities, separate from any specific table or query.
metaData.getTables(null, null, "%", new String[]{"TABLE"}): Requests every table matching the wildcard pattern "%", filtered to the "TABLE" type so views and system tables are excluded.
tables.getString("TABLE_NAME"): Reads the table name from each row of the metadata result set, which behaves like any other ResultSet once returned.
Alternative: You could query database-specific system views, such as information_schema.tables in MySQL, directly with a normal SELECT statement, but DatabaseMetaData works consistently across different database engines without engine-specific SQL.

Exercise 14: Dynamic Column Reader (ResultSetMetaData)

Problem Statement: Write a generic method that accepts any ResultSet. Use ResultSetMetaData to extract the column count and column names, then print the data out as a dynamically aligned, formatted text table.

Purpose: This exercise helps you practice writing code that works with any query’s shape at runtime, rather than hardcoding column names, which is useful for building generic tools like a database browser or a debug utility.

Given Input: The result of running SELECT * FROM Employees, without the method knowing in advance which columns that query will return.

Expected Output:

id             name           role           salary
1              Alice Johnson  Software Engineer85000.0

▼ Hint

Call resultSet.getMetaData() to obtain a ResultSetMetaData object describing the query’s shape, this works for any query passed in, not just one you wrote yourself.
Use getColumnCount() to find out how many columns to loop over, and getColumnName(i) to retrieve each column’s name using a 1-based index.
Use String.format() or System.out.printf() with a fixed-width specifier to align every column consistently regardless of how long its values are.

▼ Solution & Explanation

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
public class Main {
    private static final String URL = "jdbc:mysql://localhost:3306/company_db";
    private static final String USER = "root";
    private static final String PASSWORD = "your_password";
    public static void printResultSet(ResultSet resultSet) throws SQLException {
        ResultSetMetaData metaData = resultSet.getMetaData();
        int columnCount = metaData.getColumnCount();
        for (int i = 1; i <= columnCount; i++) {
            System.out.printf("%-15s", metaData.getColumnName(i));
        }
        System.out.println();
        while (resultSet.next()) {
            for (int i = 1; i <= columnCount; i++) {
                System.out.printf("%-15s", resultSet.getString(i));
            }
            System.out.println();
        }
    }
    public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            connection = DriverManager.getConnection(URL, USER, PASSWORD);
            statement = connection.createStatement();
            resultSet = statement.executeQuery("SELECT * FROM Employees");
            printResultSet(resultSet);
        } catch (SQLException e) {
            System.out.println("Error printing result set: " + e.getMessage());
        } finally {
            closeQuietly(resultSet);
            closeQuietly(statement);
            closeQuietly(connection);
        }
        // Usage:
        // printResultSet(resultSet) prints a header row of column names,
        // followed by every row's values, aligned into fixed-width columns
    }
    private static void closeQuietly(AutoCloseable resource) {
        if (resource != null) {
            try {
                resource.close();
            } catch (Exception e) {
                System.out.println("Error closing resource: " + e.getMessage());
            }
        }
    }
}Code language: Java (java)

Explanation:

ResultSetMetaData metaData = resultSet.getMetaData();: Retrieves shape information about the query’s results, such as how many columns exist and what they are named.
metaData.getColumnCount(): Lets the method loop over exactly as many columns as the query actually returned, instead of assuming a fixed number.
System.out.printf("%-15s", ...): Left-aligns each value within a fixed 15-character width, keeping the printed table’s columns visually lined up.
Alternative: You could calculate each column’s width dynamically based on the longest value it contains, which produces a more compact table but requires buffering all the rows in memory first to measure them before printing anything.

Exercise 15: Handling Nulls Safely

Problem Statement: Update a database column to NULL for an optional property, such as bonus_pay. When reading it back in Java, use resultSet.wasNull() to properly detect the database NULL instead of relying entirely on primitive defaults like 0.0.

Purpose: This exercise helps you practice a subtle but important JDBC pitfall, primitive getters like getDouble() silently return 0.0 for a SQL NULL, which can be indistinguishable from a genuine value of zero unless you check explicitly.

Given Input: The Employees table with a bonus_pay column, set to NULL for employee 1.

Expected Output: Bonus pay is not set for this employee.

▼ Hint

Update the column with a plain SET bonus_pay = NULL clause, no special JDBC handling is needed on the write side.
After calling a primitive getter like getDouble(), immediately call resultSet.wasNull(), it reports whether the value just read was actually a SQL NULL.
Branch your logic based on wasNull() rather than checking whether the returned primitive equals 0.0, since a real bonus of exactly 0.0 would otherwise be misreported as missing.

▼ Solution & Explanation

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

public class Main {

    private static final String URL = "jdbc:mysql://localhost:3306/company_db";
    private static final String USER = "root";
    private static final String PASSWORD = "your_password";

    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement updateStatement = null;
        Statement selectStatement = null;
        ResultSet resultSet = null;

        try {
            connection = DriverManager.getConnection(URL, USER, PASSWORD);

            updateStatement = connection.prepareStatement("UPDATE Employees SET bonus_pay = NULL WHERE id = ?");
            updateStatement.setInt(1, 1);
            updateStatement.executeUpdate();

            selectStatement = connection.createStatement();
            resultSet = selectStatement.executeQuery("SELECT id, bonus_pay FROM Employees WHERE id = 1");

            if (resultSet.next()) {
                double bonusPay = resultSet.getDouble("bonus_pay");

                if (resultSet.wasNull()) {
                    System.out.println("Bonus pay is not set for this employee.");
                } else {
                    System.out.println("Bonus pay = " + bonusPay);
                }
            }
        } catch (SQLException e) {
            System.out.println("Error handling null value: " + e.getMessage());
        } finally {
            closeQuietly(resultSet);
            closeQuietly(selectStatement);
            closeQuietly(updateStatement);
            closeQuietly(connection);
        }

        // Usage:
        // After setting bonus_pay to NULL for employee 1, reading it back prints
        // "Bonus pay is not set for this employee." instead of incorrectly reporting 0.0
    }

    private static void closeQuietly(AutoCloseable resource) {
        if (resource != null) {
            try {
                resource.close();
            } catch (Exception e) {
                System.out.println("Error closing resource: " + e.getMessage());
            }
        }
    }
}Code language: Java (java)

Explanation:

double bonusPay = resultSet.getDouble("bonus_pay");: Reads the column as a primitive double, which silently becomes 0.0 whenever the underlying database value is NULL.
resultSet.wasNull(): Must be called immediately after the getter, and reports whether that specific value just read was a SQL NULL, regardless of what the primitive default happened to be.
Why order matters: Calling wasNull() reflects the result of the most recent getter call, so checking it after any other column read would give the wrong answer.
Alternative: You could use the wrapper type Double instead of the primitive double by calling resultSet.getObject("bonus_pay", Double.class), which returns an actual null reference for a SQL NULL without needing a separate wasNull() check.

Exercise 16: The Callable Stored Procedure

Problem Statement: Write a stored procedure directly inside your database engine that calculates an employee’s annual bonus. Use a JDBC CallableStatement to invoke it from Java, handling both IN and OUT parameters.

Purpose: This exercise helps you practice calling logic that lives inside the database itself rather than in Java, and shows how JDBC lets a single call both send input values in and receive a computed result back out.

Given Input: A CalculateBonus stored procedure that takes an employee ID as input and computes 10 percent of that employee’s salary as the bonus, applied to employee 1 with a salary of 85000.

Expected Output: Calculated bonus = 8500.0

▼ Hint

Define the stored procedure with one IN parameter for the employee ID and one OUT parameter for the calculated bonus.
From Java, use connection.prepareCall() with a {call ...} escape syntax instead of prepareStatement().
Bind the input parameter normally with a setter, but also call registerOutParameter() for the output parameter before executing, so JDBC knows to expect a value back.

▼ Solution & Explanation

DELIMITER //

CREATE PROCEDURE CalculateBonus(
    IN employeeId INT,
    OUT bonusAmount DOUBLE
)
BEGIN
    DECLARE empSalary DOUBLE;

    SELECT salary INTO empSalary FROM Employees WHERE id = employeeId;
    SET bonusAmount = empSalary * 0.10;
END //

DELIMITER ;Code language: SQL (Structured Query Language) (sql)

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Types;

public class Main {

    private static final String URL = "jdbc:mysql://localhost:3306/company_db";
    private static final String USER = "root";
    private static final String PASSWORD = "your_password";

    public static void main(String[] args) {
        String callSql = "{call CalculateBonus(?, ?)}";

        Connection connection = null;
        CallableStatement callableStatement = null;

        try {
            connection = DriverManager.getConnection(URL, USER, PASSWORD);
            callableStatement = connection.prepareCall(callSql);

            callableStatement.setInt(1, 1);
            callableStatement.registerOutParameter(2, Types.DOUBLE);

            callableStatement.execute();

            double bonus = callableStatement.getDouble(2);
            System.out.println("Calculated bonus = " + bonus);
        } catch (SQLException e) {
            System.out.println("Error calling stored procedure: " + e.getMessage());
        } finally {
            closeQuietly(callableStatement);
            closeQuietly(connection);
        }

        // Usage:
        // Calling CalculateBonus for employee 1 with a salary of 85000
        // sets the OUT parameter to 8500.0, which prints as "Calculated bonus = 8500.0"
    }

    private static void closeQuietly(AutoCloseable resource) {
        if (resource != null) {
            try {
                resource.close();
            } catch (Exception e) {
                System.out.println("Error closing resource: " + e.getMessage());
            }
        }
    }
}Code language: Java (java)

Explanation:

connection.prepareCall("{call CalculateBonus(?, ?)}");: Uses the JDBC escape syntax for calling a stored procedure, distinct from the plain SQL string used with prepareStatement().
callableStatement.setInt(1, 1);: Binds the employee ID to the procedure’s IN parameter, exactly like binding a parameter on a PreparedStatement.
callableStatement.registerOutParameter(2, Types.DOUBLE);: Tells JDBC to expect a value back from the second parameter once the procedure finishes, and what SQL type to interpret it as.
Alternative: You could compute the bonus entirely in Java by first selecting the employee’s salary with a normal query, then multiplying it by the bonus rate, but keeping the calculation as a stored procedure centralizes the business rule in the database for any application that connects to it.

Exercise 17: Profile Picture Storage (BLOBs)

Problem Statement: Create a program that reads an image file from your local hard drive and saves it as a binary stream into a table’s BLOB column using pstmt.setBinaryStream().

Purpose: This exercise helps you practice storing binary data through JDBC using streams rather than loading an entire file into memory as a byte array first, which matters for larger files.

Given Input: An image file named profile.jpg located alongside the program, and a ProfilePictures table with employee_id and image (BLOB) columns.

Expected Output: Rows inserted = 1

▼ Hint

Open a FileInputStream pointed at the image file on disk.
Bind that stream directly to the BLOB placeholder using setBinaryStream(), rather than reading the file into a byte array yourself first.
Remember to close the input stream once the insert completes, in addition to closing the usual JDBC resources.

▼ Solution & Explanation

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class Main {

    private static final String URL = "jdbc:mysql://localhost:3306/company_db";
    private static final String USER = "root";
    private static final String PASSWORD = "your_password";

    public static void main(String[] args) {
        String filePath = "profile.jpg";
        String insertSql = "INSERT INTO ProfilePictures (employee_id, image) VALUES (?, ?)";

        Connection connection = null;
        PreparedStatement statement = null;
        InputStream imageStream = null;

        try {
            connection = DriverManager.getConnection(URL, USER, PASSWORD);
            statement = connection.prepareStatement(insertSql);
            imageStream = new FileInputStream(filePath);

            statement.setInt(1, 1);
            statement.setBinaryStream(2, imageStream);

            int rowsAffected = statement.executeUpdate();
            System.out.println("Rows inserted = " + rowsAffected);
        } catch (FileNotFoundException e) {
            System.out.println("Image file not found: " + e.getMessage());
        } catch (SQLException e) {
            System.out.println("Error storing image: " + e.getMessage());
        } finally {
            closeQuietly(imageStream);
            closeQuietly(statement);
            closeQuietly(connection);
        }

        // Usage:
        // Running main() reads profile.jpg from disk and stores its bytes
        // in the image BLOB column for employee 1
    }

    private static void closeQuietly(AutoCloseable resource) {
        if (resource != null) {
            try {
                resource.close();
            } catch (Exception e) {
                System.out.println("Error closing resource: " + e.getMessage());
            }
        }
    }
}Code language: Java (java)

Explanation:

imageStream = new FileInputStream(filePath);: Opens the image file as a readable stream of raw bytes, without loading the entire file into memory up front.
statement.setBinaryStream(2, imageStream);: Binds the stream directly to the BLOB column placeholder, the JDBC driver reads from the stream as needed while sending the insert to the database.
catch (FileNotFoundException e): Handles the case where the image file does not exist at the given path, separately from any database-related failure.
Alternative: You could read the entire file into a byte[] first and bind it with setBytes() instead of streaming, which is simpler for small files but uses more memory proportional to the file’s size.

Exercise 18: File Downloader (Reading BLOBs)

Problem Statement: Read the image stored in Exercise 17 back from the database, and write the binary data out to a fresh file path on your computer using Java’s file stream utilities.

Purpose: This exercise helps you practice the reverse direction of BLOB handling, reading a binary stream out of a ResultSet and copying it into a file, completing the round trip started in the previous exercise.

Given Input: The ProfilePictures row for employee 1 created in Exercise 17.

Expected Output: Image saved to downloaded_profile.jpg

▼ Hint

Call resultSet.getBinaryStream() to obtain an InputStream over the BLOB’s contents, rather than a getter that returns the whole value as one object.
Open a FileOutputStream at the destination path, and copy bytes from the BLOB’s input stream into it using a buffer.
Use a try-with-resources block for both streams together, so they are closed automatically once the copy finishes or if an error occurs partway through.

▼ Solution & Explanation

import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class Main {

    private static final String URL = "jdbc:mysql://localhost:3306/company_db";
    private static final String USER = "root";
    private static final String PASSWORD = "your_password";

    public static void main(String[] args) {
        String selectSql = "SELECT image FROM ProfilePictures WHERE employee_id = ?";
        String outputPath = "downloaded_profile.jpg";

        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;

        try {
            connection = DriverManager.getConnection(URL, USER, PASSWORD);
            statement = connection.prepareStatement(selectSql);
            statement.setInt(1, 1);
            resultSet = statement.executeQuery();

            if (resultSet.next()) {
                try (InputStream imageStream = resultSet.getBinaryStream("image");
                     OutputStream outputStream = new FileOutputStream(outputPath)) {

                    byte[] buffer = new byte[4096];
                    int bytesRead;

                    while ((bytesRead = imageStream.read(buffer)) != -1) {
                        outputStream.write(buffer, 0, bytesRead);
                    }
                }
                System.out.println("Image saved to " + outputPath);
            }
        } catch (Exception e) {
            System.out.println("Error downloading image: " + e.getMessage());
        } finally {
            closeQuietly(resultSet);
            closeQuietly(statement);
            closeQuietly(connection);
        }

        // Usage:
        // Running main() reads the BLOB stored in Exercise 17 for employee 1
        // and writes it out to downloaded_profile.jpg on disk
    }

    private static void closeQuietly(AutoCloseable resource) {
        if (resource != null) {
            try {
                resource.close();
            } catch (Exception e) {
                System.out.println("Error closing resource: " + e.getMessage());
            }
        }
    }
}Code language: Java (java)

Explanation:

resultSet.getBinaryStream("image");: Returns a readable stream over the stored BLOB’s bytes, rather than loading the entire image into a single byte array immediately.
while ((bytesRead = imageStream.read(buffer)) != -1): Copies the file in fixed-size chunks, which keeps memory usage constant regardless of how large the stored image is.
try (InputStream imageStream = ...; OutputStream outputStream = ...): Ensures both streams close automatically once the copy finishes, even if an exception interrupts the process partway through.
Alternative: You could use resultSet.getBytes("image") to retrieve the entire BLOB as a byte array and write it out in a single call with Files.write(), which is simpler for small images but loads the whole file into memory at once.

Exercise 19: Timezones & Timestamps

Problem Statement: Insert a record containing a java.sql.Timestamp. Ensure that dates written and retrieved map flawlessly despite timezone differences, by explicitly passing a Calendar object to getTimestamp().

Purpose: This exercise helps you practice a common source of subtle bugs, timestamps silently shifting by several hours because the JDBC driver, the database, and the application server all assumed different default timezones.

Given Input: An Events table with event_name and event_time columns, and the current system time recorded for an event named "Server deployment".

Expected Output: Event time (UTC) = 2026-07-04 09:15:32.0

▼ Hint

Create a single Calendar instance set to a fixed timezone, such as UTC, and reuse it for both writing and reading.
Pass that Calendar as the second argument to both setTimestamp() and getTimestamp(), this tells the driver which timezone to interpret the value in, instead of silently falling back to the JVM’s default timezone.
Using the same Calendar on both ends guarantees the value you read back matches the value you wrote, regardless of what timezone the database server itself is configured with.

▼ Solution & Explanation

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.Calendar;
import java.util.TimeZone;

public class Main {

    private static final String URL = "jdbc:mysql://localhost:3306/company_db";
    private static final String USER = "root";
    private static final String PASSWORD = "your_password";

    public static void main(String[] args) {
        Calendar utcCalendar = Calendar.getInstance(TimeZone.getTimeZone("UTC"));

        Connection connection = null;
        PreparedStatement insertStatement = null;
        Statement selectStatement = null;
        ResultSet resultSet = null;

        try {
            connection = DriverManager.getConnection(URL, USER, PASSWORD);

            insertStatement = connection.prepareStatement(
                "INSERT INTO Events (event_name, event_time) VALUES (?, ?)");
            insertStatement.setString(1, "Server deployment");
            insertStatement.setTimestamp(2, new Timestamp(System.currentTimeMillis()), utcCalendar);
            insertStatement.executeUpdate();

            selectStatement = connection.createStatement();
            resultSet = selectStatement.executeQuery(
                "SELECT event_name, event_time FROM Events WHERE event_name = 'Server deployment'");

            if (resultSet.next()) {
                Timestamp eventTime = resultSet.getTimestamp("event_time", utcCalendar);
                System.out.println("Event time (UTC) = " + eventTime);
            }
        } catch (SQLException e) {
            System.out.println("Error working with timestamps: " + e.getMessage());
        } finally {
            closeQuietly(resultSet);
            closeQuietly(selectStatement);
            closeQuietly(insertStatement);
            closeQuietly(connection);
        }

        // Usage:
        // Inserting and reading the timestamp with the same UTC Calendar
        // ensures the value round-trips correctly regardless of the server's local timezone
    }

    private static void closeQuietly(AutoCloseable resource) {
        if (resource != null) {
            try {
                resource.close();
            } catch (Exception e) {
                System.out.println("Error closing resource: " + e.getMessage());
            }
        }
    }
}Code language: Java (java)

Explanation:

Calendar.getInstance(TimeZone.getTimeZone("UTC")): Creates a calendar fixed to UTC, used consistently on both the write and read sides so the timezone interpretation never changes between them.
insertStatement.setTimestamp(2, timestamp, utcCalendar);: Tells the driver to interpret and store the timestamp relative to the given calendar’s timezone, rather than the JVM’s default timezone.
resultSet.getTimestamp("event_time", utcCalendar);: Reads the value back using the same timezone reference it was written with, guaranteeing the round trip is consistent.
Alternative: You could store timestamps using java.time.Instant and a database column type that is explicitly timezone-aware, such as TIMESTAMP WITH TIME ZONE where supported, which avoids relying on a shared Calendar convention across every part of the application.

Exercise 20: Build a DAO Layer

Problem Statement: Refactor your code away from scattered SQL statements. Implement the Data Access Object (DAO) structural pattern by creating a CustomerDAO interface and its implementation class to cleanly separate database-specific plumbing from the core Java application logic.

Purpose: This exercise helps you practice organizing JDBC code around a clear boundary, so the rest of the application interacts with plain Java objects and method calls instead of directly writing or seeing any SQL.

Given Input: A Customers table with id, name, and email columns, and a new customer named "Grace Lee" to add through the DAO.

Expected Output:

1 | John Smith | john@example.com
2 | Grace Lee | grace@example.com

▼ Hint

Define a CustomerDAO interface with methods like addCustomer(), getCustomerById(), and getAllCustomers(), described only in terms of Customer objects, with no SQL visible in the interface itself.
Write a CustomerDAOImpl class that implements the interface, containing all the JDBC code, connections, prepared statements, and result set mapping, entirely hidden behind those method signatures.
Have the calling code depend only on the CustomerDAO interface type, so the underlying database logic could be swapped out later without changing any code that uses it.

▼ Solution & Explanation

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public class Main {
    static class Customer {
        int id;
        String name;
        String email;
        Customer(int id, String name, String email) {
            this.id = id;
            this.name = name;
            this.email = email;
        }
        @Override
        public String toString() {
            return id + " | " + name + " | " + email;
        }
    }
    interface CustomerDAO {
        void addCustomer(Customer customer) throws SQLException;
        Customer getCustomerById(int id) throws SQLException;
        List<customer> getAllCustomers() throws SQLException;
    }
    static class CustomerDAOImpl implements CustomerDAO {
        private static final String URL = "jdbc:mysql://localhost:3306/company_db";
        private static final String USER = "root";
        private static final String PASSWORD = "your_password";
        @Override
        public void addCustomer(Customer customer) throws SQLException {
            String insertSql = "INSERT INTO Customers (name, email) VALUES (?, ?)";
            try (Connection connection = DriverManager.getConnection(URL, USER, PASSWORD);
                 PreparedStatement statement = connection.prepareStatement(insertSql)) {
                statement.setString(1, customer.name);
                statement.setString(2, customer.email);
                statement.executeUpdate();
            }
        }
        @Override
        public Customer getCustomerById(int id) throws SQLException {
            String selectSql = "SELECT * FROM Customers WHERE id = ?";
            try (Connection connection = DriverManager.getConnection(URL, USER, PASSWORD);
                 PreparedStatement statement = connection.prepareStatement(selectSql)) {
                statement.setInt(1, id);
                try (ResultSet resultSet = statement.executeQuery()) {
                    if (resultSet.next()) {
                        return new Customer(
                            resultSet.getInt("id"),
                            resultSet.getString("name"),
                            resultSet.getString("email"));
                    }
                }
            }
            return null;
        }
        @Override
        public List<customer> getAllCustomers() throws SQLException {
            String selectSql = "SELECT * FROM Customers";
            List<customer> customers = new ArrayList<>();
            try (Connection connection = DriverManager.getConnection(URL, USER, PASSWORD);
                 PreparedStatement statement = connection.prepareStatement(selectSql);
                 ResultSet resultSet = statement.executeQuery()) {
                while (resultSet.next()) {
                    customers.add(new Customer(
                        resultSet.getInt("id"),
                        resultSet.getString("name"),
                        resultSet.getString("email")));
                }
            }
            return customers;
        }
    }
    public static void main(String[] args) {
        CustomerDAO customerDAO = new CustomerDAOImpl();
        try {
            customerDAO.addCustomer(new Customer(0, "Grace Lee", "grace@example.com"));
            List<customer> customers = customerDAO.getAllCustomers();
            for (Customer customer : customers) {
                System.out.println(customer);
            }
        } catch (SQLException e) {
            System.out.println("Error using CustomerDAO: " + e.getMessage());
        }
        // Usage:
        // customerDAO.addCustomer(new Customer(0, "Grace Lee", "grace@example.com")) inserts a new row
        // customerDAO.getAllCustomers() returns every customer without any SQL appearing in the calling code
    }
}</customer></customer></customer></customer>Code language: Java (java)

Explanation:

interface CustomerDAO: Describes what operations are available purely in terms of Customer objects, with no SQL, connection details, or JDBC types appearing anywhere in its method signatures.
class CustomerDAOImpl implements CustomerDAO: Contains every JDBC detail, connections, prepared statements, and result set mapping, entirely inside the implementation, hidden from anything that only depends on the interface.
CustomerDAO customerDAO = new CustomerDAOImpl();: The calling code in main() works exclusively through the interface type, reading naturally as plain Java object operations rather than database calls.
Alternative: You could introduce a connection pool, such as HikariCP, inside CustomerDAOImpl instead of calling DriverManager.getConnection() on every method call, which reuses connections across calls and is far more efficient in a real application than opening a new connection each time.

Java JDBC Database Exercises: 20 Coding Problems with Solutions

Table of contents

Exercise 1: The Handshake, Connecting to a Database

Exercise 2: Create a Schema

Exercise 3: Simple Insert

Exercise 4: Read & Output

Exercise 5: Target and Delete

Exercise 6: Dynamic Registration

Exercise 7: The Vulnerability Fix

Exercise 8: The Price Adjuster

Exercise 9: Search with Wildcards

Exercise 10: Graceful Closures with Try-with-Resources

Exercise 11: Money Transfer (Transactions)

Exercise 12: Bulk Ingestion (Batch Processing)

Exercise 13: Database Inspection (DatabaseMetaData)

Exercise 14: Dynamic Column Reader (ResultSetMetaData)

Exercise 15: Handling Nulls Safely

Exercise 16: The Callable Stored Procedure

Exercise 17: Profile Picture Storage (BLOBs)

Exercise 18: File Downloader (Reading BLOBs)

Exercise 19: Timezones & Timestamps

Exercise 20: Build a DAO Layer

About Vishal

Related Tutorial Topics:

All Coding Exercises:

Python Exercises and Quizzes

About PYnative

Follow Us

Explore Python

Coding Exercises

Legal Stuff

Table of contents

Exercise 1: The Handshake, Connecting to a Database

Exercise 2: Create a Schema

Exercise 3: Simple Insert

Exercise 4: Read & Output

Exercise 5: Target and Delete

Exercise 6: Dynamic Registration

Exercise 7: The Vulnerability Fix

Exercise 8: The Price Adjuster

Exercise 9: Search with Wildcards

Exercise 10: Graceful Closures with Try-with-Resources

Exercise 11: Money Transfer (Transactions)

Exercise 12: Bulk Ingestion (Batch Processing)

Exercise 13: Database Inspection (DatabaseMetaData)

Exercise 14: Dynamic Column Reader (ResultSetMetaData)

Exercise 15: Handling Nulls Safely

Exercise 16: The Callable Stored Procedure

Exercise 17: Profile Picture Storage (BLOBs)

Exercise 18: File Downloader (Reading BLOBs)

Exercise 19: Timezones & Timestamps

Exercise 20: Build a DAO Layer

About Vishal

Related Tutorial Topics:

All Coding Exercises:

Python Exercises and Quizzes

Leave a Reply Cancel reply

About PYnative

Follow Us

Explore Python

Coding Exercises

Legal Stuff