Topic 5: Java Database Connectivity - zhamri/CSC584-Enterprise_Programming GitHub Wiki

Goal

To enable students to connect Java web applications to a database using JDBC and perform basic data operations such as inserting and retrieving records.

🎯 Learning Objectives

Students should be able to:

  1. Explain the role of JDBC in Java applications
  2. Describe the components of the JDBC API
  3. Establish a connection between Java and a database
  4. Execute SQL statements using Java
  5. Process query results using ResultSet
  6. Integrate database operations with Servlet and JSP

📌 Topics to Cover

  1. Overview of Java Database Programming
    • What is JDBC
    • Importance in enterprise applications
  2. JDBC API
    • DriverManager
    • Connection
    • Statement / PreparedStatement
  3. JDBC Drivers
    • Types of drivers
    • JDBC architecture
  4. Executing SQL Statements
    • INSERT
    • SELECT
    • UPDATE / DELETE (basic concept)
  5. ResultSet Object
    • Retrieving data from the database
  6. 2-Tier Client-Server Model
    • Application ↔ Database
  7. JDBC with JSP/Servlet
    • Integrating the database into the web application

🛠️ Hands-on Activity

Activity: Student Registration System with Database (JDBC Integration)

Students are required to:

  1. Create Database Table. Example: student
Column Type
id INT (PK)
name VARCHAR
email VARCHAR
program VARCHAR
  1. Connect to Database (Servlet)
    • Establish JDBC connection
    • Use:
DriverManager.getConnection()

  1. Insert Data (Create)

    • Save form data into the database
    • Use PreparedStatement for INSERT

  2. Retrieve Data (Read)

    • Retrieve all student records
    • Store result in ResultSet

  3. Display Data (JSP)

    • Forward data from Servlet to JSP
    • Display in table format:
Name      Email              Program
Ali       [email protected]      SE
Ahmad     [email protected]    IT
  1. (Optional Enhancement)
    • Add Update and Delete functionality (CRUD complete)

Expected Output

Students should produce:

  1. Database table
  2. Servlet with JDBC connection
  3. JSP page displaying student list
  4. Working flow:
HTML Form → Servlet → Database → JSP

📚 Suggested Readings

  1. Oracle JDBC Documentation https://docs.oracle.com/javase/tutorial/jdbc/
  2. https://en.wikipedia.org/wiki/Java_Database_Connectivity
  3. https://www3.ntu.edu.sg/home/ehchua/programming/java/JDBC_Basic.html