Java Notes

JDBC Projects

Hands-on JDBC projects — Student Management System, Employee Payroll, Library Management with full working code including DAO pattern, connection pooling, and menu-driven interface.

Project 1: Student Management System

A complete console-based CRUD application with the DAO (Data Access Object) pattern.

Database Setup

sql example

CREATE DATABASE IF NOT EXISTS student_mgmt;
USE student_mgmt;

CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    roll_number VARCHAR(20) UNIQUE NOT NULL,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    course VARCHAR(50),
    semester INT CHECK (semester BETWEEN 1 AND 8),
    gpa DECIMAL(3,2) CHECK (gpa BETWEEN 0.0 AND 4.0),
    phone VARCHAR(15),
    admission_date DATE,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE INDEX idx_course ON students(course);
CREATE INDEX idx_semester ON students(semester);

Model Class

java example

import java.time.LocalDate;

public class Student {
    private int id;
    private String rollNumber;
    private String firstName;
    private String lastName;
    private String email;
    private String course;
    private int semester;
    private double gpa;
    private String phone;
    private LocalDate admissionDate;
    private boolean active;
    
    // Constructors
    public Student() {}
    
    public Student(String rollNumber, String firstName, String lastName, 
                   String email, String course, int semester) {
        this.rollNumber = rollNumber;
        this.firstName = firstName;
        this.lastName = lastName;
        this.email = email;
        this.course = course;
        this.semester = semester;
        this.admissionDate = LocalDate.now();
        this.active = true;
    }
    
    // Getters and Setters (omitted for brevity)
    
    @Override
    public String toString() {
        return String.format("| %-4d | %-10s | %-15s %-15s | %-8s | Sem %d | GPA %.2f |",
            id, rollNumber, firstName, lastName, course, semester, gpa);
    }
}

Database Connection Manager

java example

import java.sql.*;
import java.io.InputStream;
import java.util.Properties;

public class DatabaseManager {
    private static final Properties props = new Properties();
    
    static {
        try (InputStream is = DatabaseManager.class.getClassLoader()
                .getResourceAsStream("db.properties")) {
            if (is != null) props.load(is);
            else {
                props.setProperty("url", "jdbc:mysql://localhost:3306/student_mgmt?useSSL=false&serverTimezone=UTC");
                props.setProperty("user", "root");
                props.setProperty("password", "password");
            }
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (Exception e) {
            throw new RuntimeException("Database initialization failed", e);
        }
    }
    
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(
            props.getProperty("url"),
            props.getProperty("user"),
            props.getProperty("password"));
    }
}

DAO Interface and Implementation

java example

import java.util.List;
import java.util.Optional;

public interface StudentDAO {
    int insert(Student student) throws SQLException;
    Optional<Student> findById(int id) throws SQLException;
    Optional<Student> findByRollNumber(String rollNumber) throws SQLException;
    List<Student> findAll() throws SQLException;
    List<Student> findByCourse(String course) throws SQLException;
    List<Student> search(String keyword) throws SQLException;
    boolean update(Student student) throws SQLException;
    boolean delete(int id) throws SQLException;
    long count() throws SQLException;
    double averageGPA(String course) throws SQLException;
}

Example

import java.sql.*; import java.util.*; public class StudentDAOImpl implements StudentDAO { @Override public int insert(Student s) throws SQLException { String sql = "INSERT INTO students (roll_number, first_name, last_name, email, " + "course, semester, gpa, phone, admission_date) VALUES (?,?,?,?,?,?,?,?,?)"; try (Connection conn = DatabaseManager.getConnection(); PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) { ps.setString(1, s.getRollNumber()); ps.setString(2, s.getFirstName()); ps.setString(3, s.getLastName()); ps.setString(4, s.getEmail()); ps.setString(5, s.getCourse()); ps.setInt(6, s.getSemester()); ps.setDouble(7, s.getGpa()); ps.setString(8, s.getPhone()); ps.setDate(9, java.sql.Date.valueOf( s.getAdmissionDate() != null ? s.getAdmissionDate() : java.time.LocalDate.now())); ps.executeUpdate(); ResultSet keys = ps.getGeneratedKeys(); return keys.next() ? keys.getInt(1) : -1; } } @Override public Optional<Student> findById(int id) throws SQLException { String sql = "SELECT * FROM students WHERE id = ? AND is_active = TRUE"; try (Connection conn = DatabaseManager.getConnection(); PreparedStatement ps = conn.prepareStatement(sql)) { ps.setInt(1, id); ResultSet rs = ps.executeQuery(); return rs.next() ? Optional.of(mapRow(rs)) : Optional.empty(); } } @Override public List<Student> findAll() throws SQLException { String sql = "SELECT * FROM students WHERE is_active = TRUE ORDER BY roll_number"; List<Student> students = new ArrayList<>(); try (Connection conn = DatabaseManager.getConnection(); Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(sql)) { while (rs.next()) students.add(mapRow(rs)); } return students; } @Override public List<Student> search(String keyword) throws SQLException { String sql = "SELECT * FROM students WHERE is_active = TRUE AND " + "(first_name LIKE ? OR last_name LIKE ? OR roll_number LIKE ? OR email LIKE ?)"; String pattern = "%" + keyword + "%"; List<Student> results = new ArrayList<>(); try (Connection conn = DatabaseManager.getConnection(); PreparedStatement ps = conn.prepareStatement(sql)) { for (int i = 1; i <= 4; i++) ps.setString(i, pattern); ResultSet rs = ps.executeQuery(); while (rs.next()) results.add(mapRow(rs)); } return results; } @Override public boolean update(Student s) throws SQLException { String sql = "UPDATE students SET first_name=?, last_name=?, email=?, " + "course=?, semester=?, gpa=?, phone=? WHERE id=?"; try (Connection conn = DatabaseManager.getConnection(); PreparedStatement ps = conn.prepareStatement(sql)) { ps.setString(1, s.getFirstName()); ps.setString(2, s.getLastName()); ps.setString(3, s.getEmail()); ps.setString(4, s.getCourse()); ps.setInt(5, s.getSemester()); ps.setDouble(6, s.getGpa()); ps.setString(7, s.getPhone()); ps.setInt(8, s.getId()); return ps.executeUpdate() > 0; } } @Override public boolean delete(int id) throws SQLException { String sql = "UPDATE students SET is_active = FALSE WHERE id = ?"; try (Connection conn = DatabaseManager.getConnection(); PreparedStatement ps = conn.prepareStatement(sql)) { ps.setInt(1, id); return ps.executeUpdate() > 0; } } private Student mapRow(ResultSet rs) throws SQLException { Student s = new Student(); s.setId(rs.getInt("id")); s.setRollNumber(rs.getString("roll_number")); s.setFirstName(rs.getString("first_name")); s.setLastName(rs.getString("last_name")); s.setEmail(rs.getString("email")); s.setCourse(rs.getString("course")); s.setSemester(rs.getInt("semester")); s.setGpa(rs.getDouble("gpa")); s.setPhone(rs.getString("phone")); Date d = rs.getDate("admission_date"); if (d != null) s.setAdmissionDate(d.toLocalDate()); s.setActive(rs.getBoolean("is_active")); return s; } }

Example

import java.util.*; public class StudentManagementApp { private static final StudentDAO dao = new StudentDAOImpl(); private static final Scanner scanner = new Scanner(System.in); public static void main(String[] args) { System.out.println("╔══════════════════════════════════════╗"); System.out.println("║ Student Management System v1.0 ║"); System.out.println("╚══════════════════════════════════════╝"); while (true) { showMenu(); int choice = getIntInput("Enter choice: "); try { switch (choice) { case 1: addStudent(); break; case 2: viewAllStudents(); break; case 3: searchStudent(); break; case 4: updateStudent(); break; case 5: deleteStudent(); break; case 6: viewStatistics(); break; case 0: System.out.println("Goodbye!"); return; default: System.out.println("Invalid choice!"); } } catch (Exception e) { System.err.println("Error: " + e.getMessage()); } } } private static void showMenu() { System.out.println("\n--- MENU ---"); System.out.println("1. Add Student"); System.out.println("2. View All Students"); System.out.println("3. Search Student"); System.out.println("4. Update Student"); System.out.println("5. Delete Student"); System.out.println("6. View Statistics"); System.out.println("0. Exit"); } private static void addStudent() throws Exception { System.out.println("\n--- Add New Student ---"); Student s = new Student(); s.setRollNumber(getStringInput("Roll Number: ")); s.setFirstName(getStringInput("First Name: ")); s.setLastName(getStringInput("Last Name: ")); s.setEmail(getStringInput("Email: ")); s.setCourse(getStringInput("Course: ")); s.setSemester(getIntInput("Semester (1-8): ")); s.setGpa(getDoubleInput("GPA (0.0-4.0): ")); s.setPhone(getStringInput("Phone: ")); int id = dao.insert(s); System.out.println("✅ Student added with ID: " + id); } private static void viewAllStudents() throws Exception { List<Student> students = dao.findAll(); if (students.isEmpty()) { System.out.println("No students found."); return; } System.out.println("\n" + "-".repeat(80)); students.forEach(System.out::println); System.out.println("-".repeat(80)); System.out.println("Total: " + students.size() + " students"); } }

Output

╔══════════════════════════════════════╗
║   Student Management System v1.0    ║
╚══════════════════════════════════════╝
\n--- MENU ---
1. Add Student
2. View All Students
3. Search Student
4. Update Student
5. Delete Student
6. View Statistics
0. Exit
\n--- Add New Student ---
No students found.
Total: " + students.size() + " students

Key Takeaways for Projects

Always use DAO pattern — separates data access from business logic
Use PreparedStatement — never concatenate user input into SQL
Close resources — use try-with-resources
Use transactions — for multi-statement operations
Connection pooling — mandatory for web applications
Soft deletes — use is_active flag instead of hard DELETE
Proper error handling — catch specific exceptions, provide meaningful messages

Interview Questions

Q1: What is the DAO pattern and why use it?

Answer: DAO (Data Access Object) encapsulates all database operations for an entity in a dedicated class. Benefits: separates persistence logic from business logic, makes code testable (mock the DAO), enables switching databases without changing business code.

Q2: How would you handle pagination in a JDBC application?

Answer: Use LIMIT ? OFFSET ? in MySQL (or FETCH FIRST ? ROWS ONLY in standard SQL). Pass page number and page size as parameters. Calculate offset as (pageNumber - 1) * pageSize. Include a count query for total pages.

Q3: How would you implement connection pooling in a standalone Java app?

Answer: Use HikariCP: create a HikariConfig with URL, credentials, and pool settings (max size, timeouts). Create HikariDataSource and call getConnection(). Connections are returned to pool on close. Shutdown the pool when the app exits.

Q4: How would you secure database credentials in a production app?

Answer: Never hardcode passwords. Use environment variables, encrypted config files, secrets managers (AWS Secrets Manager, HashiCorp Vault), or JNDI DataSource configured by the app server. In CI/CD, inject secrets at deploy time.

Summary

In this chapter, we learned about JDBC Projects in detail. Here are the key points:

Basic introduction to the concept and why it is needed
Syntax and structure with complete examples
Internal working and best practices
Real-world applications and use cases
Common mistakes and how to avoid them
How this topic is asked in interviews

To practice these concepts, write and run the code in your IDE and verify the output. Modify each example and experiment so that you deeply understand the concept.

Exam Focus

Revise definitions, diagrams, examples, and short-answer points for JDBC Projects.

Interview Use

Prepare one clear explanation, one practical example, and one common mistake for this Java Master Course topic.

Search Terms

java-master-course, java master course, java, master, course, jdbc, projects, jdbc projects

Continue learning this concept

JDBCBatch Processing in JDBCMaster JDBC batch processing — Statement and PreparedStatement batching, performance optimization, error handling, and bulk data import patterns.JDBCCallableStatement in JDBCComplete guide to CallableStatement — calling stored procedures and functions, IN/OUT/INOUT parameters, and handling multiple result sets from MySQL procedures.JDBCJDBC ArchitectureDeep dive into JDBC architecture — two-tier and three-tier models, driver manager, connection pooling, DataSource, and internal execution flow.JDBCJDBC IntroductionComplete introduction to JDBC — Java Database Connectivity concepts, API overview, driver types, and how Java applications communicate with databases.JDBCMySQL Connection with JDBCStep-by-step guide to connecting Java with MySQL — driver setup, connection URLs, properties, SSL configuration, and troubleshooting common errors.JDBCPreparedStatement in JDBCMaster PreparedStatement — parameterized queries, SQL injection prevention, performance benefits, batch operations, and CRUD patterns.

PreviousJDBC Introduction NextMySQL Connection with JDBC

Java Notes

JDBC Projects

Hands-on JDBC projects — Student Management System, Employee Payroll, Library Management with full working code including DAO pattern, connection pooling, and menu-driven interface.

Project 1: Student Management System

A complete console-based CRUD application with the DAO (Data Access Object) pattern.

Database Setup

sql example

CREATE DATABASE IF NOT EXISTS student_mgmt;
USE student_mgmt;

CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    roll_number VARCHAR(20) UNIQUE NOT NULL,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    course VARCHAR(50),
    semester INT CHECK (semester BETWEEN 1 AND 8),
    gpa DECIMAL(3,2) CHECK (gpa BETWEEN 0.0 AND 4.0),
    phone VARCHAR(15),
    admission_date DATE,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE INDEX idx_course ON students(course);
CREATE INDEX idx_semester ON students(semester);

Model Class

java example

import java.time.LocalDate;

public class Student {
    private int id;
    private String rollNumber;
    private String firstName;
    private String lastName;
    private String email;
    private String course;
    private int semester;
    private double gpa;
    private String phone;
    private LocalDate admissionDate;
    private boolean active;
    
    // Constructors
    public Student() {}
    
    public Student(String rollNumber, String firstName, String lastName, 
                   String email, String course, int semester) {
        this.rollNumber = rollNumber;
        this.firstName = firstName;
        this.lastName = lastName;
        this.email = email;
        this.course = course;
        this.semester = semester;
        this.admissionDate = LocalDate.now();
        this.active = true;
    }
    
    // Getters and Setters (omitted for brevity)
    
    @Override
    public String toString() {
        return String.format("| %-4d | %-10s | %-15s %-15s | %-8s | Sem %d | GPA %.2f |",
            id, rollNumber, firstName, lastName, course, semester, gpa);
    }
}

Database Connection Manager

java example

import java.sql.*;
import java.io.InputStream;
import java.util.Properties;

public class DatabaseManager {
    private static final Properties props = new Properties();
    
    static {
        try (InputStream is = DatabaseManager.class.getClassLoader()
                .getResourceAsStream("db.properties")) {
            if (is != null) props.load(is);
            else {
                props.setProperty("url", "jdbc:mysql://localhost:3306/student_mgmt?useSSL=false&serverTimezone=UTC");
                props.setProperty("user", "root");
                props.setProperty("password", "password");
            }
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (Exception e) {
            throw new RuntimeException("Database initialization failed", e);
        }
    }
    
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(
            props.getProperty("url"),
            props.getProperty("user"),
            props.getProperty("password"));
    }
}

DAO Interface and Implementation

java example

import java.util.List;
import java.util.Optional;

public interface StudentDAO {
    int insert(Student student) throws SQLException;
    Optional<Student> findById(int id) throws SQLException;
    Optional<Student> findByRollNumber(String rollNumber) throws SQLException;
    List<Student> findAll() throws SQLException;
    List<Student> findByCourse(String course) throws SQLException;
    List<Student> search(String keyword) throws SQLException;
    boolean update(Student student) throws SQLException;
    boolean delete(int id) throws SQLException;
    long count() throws SQLException;
    double averageGPA(String course) throws SQLException;
}

Example

Output

╔══════════════════════════════════════╗
║   Student Management System v1.0    ║
╚══════════════════════════════════════╝
\n--- MENU ---
1. Add Student
2. View All Students
3. Search Student
4. Update Student
5. Delete Student
6. View Statistics
0. Exit
\n--- Add New Student ---
No students found.
Total: " + students.size() + " students

Key Takeaways for Projects

Always use DAO pattern — separates data access from business logic
Use PreparedStatement — never concatenate user input into SQL
Close resources — use try-with-resources
Use transactions — for multi-statement operations
Connection pooling — mandatory for web applications
Soft deletes — use is_active flag instead of hard DELETE
Proper error handling — catch specific exceptions, provide meaningful messages

Interview Questions

Q1: What is the DAO pattern and why use it?

Q2: How would you handle pagination in a JDBC application?

Q3: How would you implement connection pooling in a standalone Java app?

Q4: How would you secure database credentials in a production app?

Summary

In this chapter, we learned about JDBC Projects in detail. Here are the key points:

Basic introduction to the concept and why it is needed
Syntax and structure with complete examples
Internal working and best practices
Real-world applications and use cases
Common mistakes and how to avoid them
How this topic is asked in interviews

To practice these concepts, write and run the code in your IDE and verify the output. Modify each example and experiment so that you deeply understand the concept.

Exam Focus

Revise definitions, diagrams, examples, and short-answer points for JDBC Projects.

Interview Use

Prepare one clear explanation, one practical example, and one common mistake for this Java Master Course topic.

Search Terms

java-master-course, java master course, java, master, course, jdbc, projects, jdbc projects

Continue learning this concept

PreviousJDBC Introduction NextMySQL Connection with JDBC

JDBC Projects

Project 1: Student Management System

Database Setup

Model Class

Database Connection Manager

DAO Interface and Implementation

Main Application (Menu-Driven)

Key Takeaways for Projects

Interview Questions

Q1: What is the DAO pattern and why use it?

Q3: How would you implement connection pooling in a standalone Java app?

Q4: How would you secure database credentials in a production app?

Summary

Continue learning this concept

JDBC Projects

Project 1: Student Management System

Database Setup

Model Class

Database Connection Manager

DAO Interface and Implementation

Main Application (Menu-Driven)

Key Takeaways for Projects

Interview Questions

Q1: What is the DAO pattern and why use it?

Q3: How would you implement connection pooling in a standalone Java app?

Q4: How would you secure database credentials in a production app?

Summary

Continue learning this concept