============================================================
  IT6003 - Advanced Java Programming
  Lab Session 5: JDBC & File I/O
  State University of Zanzibar (SUZA)
============================================================

OBJECTIVES:
- Read and write files using BufferedReader/BufferedWriter
- Use Java NIO (Path, Files class)
- Serialize and deserialize objects
- Connect to SQLite database using JDBC
- Perform CRUD operations with PreparedStatement
- Use transactions and batch processing

PREREQUISITES:
- Download SQLite JDBC driver: sqlite-jdbc-3.x.x.jar
- Add to classpath: javac -cp sqlite-jdbc.jar *.java
- Run: java -cp .:sqlite-jdbc.jar MainClass

============================================================
PART A: File I/O Basics [30 minutes]
============================================================

Exercise 1: Writing Text Files
---------------------------------
a) Use FileWriter to write 5 lines to "output.txt".
b) Use BufferedWriter to write 100 lines to "large_output.txt" (more efficient).
c) Use PrintWriter with printf formatting: name, age, GPA per line.
d) Append text to existing file using FileWriter(filename, true).
e) Use try-with-resources for all file operations.

Exercise 2: Reading Text Files
---------------------------------
a) Use BufferedReader to read "output.txt" line by line.
b) Count total lines, words, and characters.
c) Find the longest line.
d) Read only lines containing a specific keyword.
e) Use FileReader vs InputStreamReader with charset (UTF-8).

Exercise 3: Java NIO - Files and Path
-----------------------------------------
a) Create a Path object: Path path = Paths.get("data.txt");
b) Write lines using Files.write(path, lines).
c) Read all lines: List<String> lines = Files.readAllLines(path).
d) Read as stream: Files.lines(path).forEach(System.out::println).
e) Copy file: Files.copy(source, target, StandardCopyOption.REPLACE_EXISTING).
f) Move file: Files.move(source, target).
g) Delete file: Files.deleteIfExists(path).
h) Check: Files.exists(), Files.isDirectory(), Files.size().

Exercise 4: Object Serialization
------------------------------------
a) Create a Student class implementing Serializable with fields:
   regNo, name, gpa, courses (List<String>), password (transient).
b) Add serialVersionUID: private static final long serialVersionUID = 1L;
c) Serialize a list of 5 students to "students.dat" using ObjectOutputStream.
d) Deserialize from "students.dat" using ObjectInputStream.
e) Verify password field is null after deserialization (transient).
f) Try changing serialVersionUID and deserializing old data - observe error.

============================================================
PART B: JDBC Basics [30 minutes]
============================================================

Exercise 5: Database Connection and Table Creation
------------------------------------------------------
a) Load SQLite JDBC driver and create connection:
   Connection conn = DriverManager.getConnection("jdbc:sqlite:university.db");
b) Create table using Statement:
   CREATE TABLE students (
       reg_no TEXT PRIMARY KEY,
       name TEXT NOT NULL,
       department TEXT,
       gpa REAL DEFAULT 0.0,
       email TEXT
   );
c) Create a departments table with dept_id and dept_name.
d) Print success message and close connection.
e) Use try-with-resources for Connection and Statement.

Exercise 6: CRUD with PreparedStatement
-------------------------------------------
a) INSERT: Add 10 students using PreparedStatement:
   INSERT INTO students VALUES (?, ?, ?, ?, ?)
b) SELECT all: Query all students, process ResultSet:
   while (rs.next()) { rs.getString("name"); rs.getDouble("gpa"); }
c) SELECT by department: Find all CS students.
d) UPDATE: Change a student's GPA.
e) DELETE: Remove a student by reg_no.
f) Print formatted table of all students after each operation.

Exercise 7: SQL Injection Prevention
----------------------------------------
a) WRONG way (vulnerable):
   String sql = "SELECT * FROM students WHERE name = '" + userInput + "'";
b) Try input: "Ali' OR '1'='1" - observe it returns ALL students.
c) RIGHT way (safe):
   PreparedStatement ps = conn.prepareStatement("SELECT * FROM students WHERE name = ?");
   ps.setString(1, userInput);
d) Verify the injection attempt no longer works.

============================================================
PART C: Advanced JDBC [30 minutes]
============================================================

Exercise 8: Joins and Aggregation
-------------------------------------
a) Create departments table and add 4 departments.
b) Add dept_id foreign key to students table.
c) JOIN query: Display students with department names:
   SELECT s.name, d.dept_name, s.gpa FROM students s
   JOIN departments d ON s.dept_id = d.dept_id
d) Aggregate: Average GPA per department.
e) Aggregate: Count students per department.
f) Aggregate: Top 3 students by GPA.

Exercise 9: Transactions
---------------------------
a) Set conn.setAutoCommit(false).
b) Implement transferStudent():
   - Delete student from old department count
   - Update student's dept_id
   - Insert into new department count
c) If any step fails: conn.rollback() and print error.
d) If all succeed: conn.commit().
e) Demonstrate rollback by intentionally causing an error mid-transaction.

Exercise 10: Batch Processing
---------------------------------
a) Read student data from a CSV file (20+ students).
b) Use PreparedStatement with addBatch():
   for each student:
       ps.setString(1, regNo);
       ps.setString(2, name);
       ...
       ps.addBatch();
c) Execute: int[] results = ps.executeBatch();
d) Commit the transaction.
e) Measure time vs individual inserts. Print comparison.

============================================================
PART D: Integration Challenge [30 minutes]
============================================================

Exercise 11: Student Records Application
--------------------------------------------
Build a complete command-line CRUD application:
a) Menu-driven interface:
   1. Add Student
   2. View All Students
   3. Search by Name
   4. Update GPA
   5. Delete Student
   6. Department Report
   7. Export to CSV
   8. Import from CSV
   9. Exit
b) Use PreparedStatement for all operations.
c) Use try-with-resources for all resources.
d) Handle all exceptions with meaningful messages.
e) Export: Write all students to students_export.csv using BufferedWriter.
f) Import: Read students_import.csv and batch insert.

Exercise 12: ResultSetMetaData
---------------------------------
a) Write a method: void printQueryResult(Connection conn, String sql)
b) Use ResultSetMetaData to dynamically get column names and types.
c) Print formatted table with column headers.
d) Test with different queries (students, departments, joins).

============================================================
SUBMISSION:
Submit all .java files and database file in a folder named Lab05_RegNo/
============================================================