Topic 07: Relational Databases

Goal

To introduce students to database connectivity concepts in Java using JDBC, enabling them to connect Java applications to relational databases, execute SQL statements, process query results, and develop database-driven applications.

🎯 Learning Objectives

Students should be able to:

1. Explain the concept of database connectivity and JDBC architecture.
2. Understand the role of JDBC interfaces such as Driver, Connection, Statement, PreparedStatement and ResultSet.
3. Load and configure JDBC drivers for database access.
4. Establish a connection between Java applications and relational databases.
5. Execute SQL statements using Statement and PreparedStatement.
6. Process and retrieve data from a ResultSet.
7. Perform CRUD (Create, Read, Update, Delete) operations using SQL.
8. Apply parameterized queries using PreparedStatement.
9. Use batch processing to execute multiple SQL statements efficiently.
10. Retrieve database metadata using DatabaseMetaData.
11. Develop simple Java database applications integrated with MySQL.

📌 Topics to Cover

1. Introduction to Database Applications
   • Components of database systems
   • Database management systems (DBMS)
   • Client-server database applications
2. Introduction to JDBC
   • JDBC overview
   • Advantages of JDBC
   • JDBC architecture
3. JDBC Interfaces and Components
   • DriverManager
   • Connection
   • Statement
   • PreparedStatement
   • ResultSet
4. Steps in JDBC Application Development
   • Load JDBC driver
   • Establish database connection
   • Create SQL statements
   • Execute queries
   • Process query results
   • Close resources
5. Connecting Java with MySQL
   • MySQL Connector/J
   • JDBC URL format
   • Database authentication
6. Executing SQL Statements
   • executeQuery()
   • executeUpdate()
7. Processing Result Sets
   • Navigating rows
   • Retrieving column values
   • Getter methods:
     • getInt()
     • getString()
     • getDouble()
8. Prepared Statements
   • Parameterized SQL queries
   • Preventing SQL injection
   • setString(), setInt(), etc.
9. Batch Processing
   • addBatch()
   • executeBatch()
   • Performance optimization
10. Database Metadata
    • DatabaseMetaData
    • Database structure information
    • Supported database features
11. Basic SQL for JDBC
    • CREATE TABLE
    • SELECT
    • WHERE
    • ORDER BY
    • INSERT
    • UPDATE
    • DELETE

🛠️ Hands-on Activity

Activity 1: JDBC Database Connection

Students create a Java program that:

• Connects to a MySQL database
• Displays a successful connection message

---

Activity 2: Create Student Table

Students write SQL code and Java programs to:

• Create a students table
• Define columns such as:
  • id
  • name
  • program
  • cgpa

Example:

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    program VARCHAR(50),
    cgpa DOUBLE
);

---

Activity 3: Insert Data Using PreparedStatement

Students develop a Java application that:

• Accepts student data from keyboard input
• Inserts records into the database using PreparedStatement

---

Activity 4: Retrieve and Display Records

Students create a program that:

• Retrieves all records using SELECT
• Displays the data using ResultSet

Example Output:

ID    Name         Program      CGPA
1     Ali          Software Eng 3.50
2     Siti         AI           3.80

---

Activity 5: Update and Delete Records

Students implement:

• Record update functionality
• Record deletion functionality
• SQL queries using WHERE clause

---

Activity 6: Batch Processing

Students develop a Java program that:

• Inserts multiple records using batch processing
• Measures execution efficiency

---

Activity 7: Database Metadata Viewer

Students create a program that:

• Retrieves database metadata
• Displays:
  • Database name
  • Driver version
  • Supported SQL features

---

📚 Suggested Readings

1. https://docs.oracle.com/en/database/oracle/oracle-database/26/jjdbc/introducing-JDBC.html#GUID-864DB502-5E50-4044-8132-33D6AAF8927A
2. JDBC Online Tutorial @ http://download.oracle.com/javase/tutorial/jdbc/index.html.
3. JDBC Home Page @ http://www.oracle.com/technetwork/java/javase/jdbc/index.html.
4. MySQL Home Page @ https://dev.mysql.com/, and documentation.
5. MySQL 8.0 Reference Manual @ https://dev.mysql.com/doc/refman/8.0/en/.
6. https://docs.oracle.com/javase/tutorial/jdbc/basics/index.html
7. https://en.wikipedia.org/wiki/Java_Database_Connectivity
8. https://www3.ntu.edu.sg/home/ehchua/programming/java/JDBC_Basic.html
9. https://www.baeldung.com/java-jdbc
10. https://medium.com/@Bharat2044/what-is-jdbc-introduction-to-java-database-connectivity-649677818a8b
11. https://www.tutorialspoint.com/jdbc/index.htm
12. https://www.geeksforgeeks.org/java/jdbc-tutorial/