import java.sql.*; /** * JDBCDemo.java * Demonstrates JDBC database programming for MSc students using SQLite. * Covers: connection management, CREATE TABLE, PreparedStatement for * INSERT/SELECT/UPDATE/DELETE, ResultSet processing, transactions * with commit/rollback, batch insert, and try-with-resources. * * Prerequisites: * Download sqlite-jdbc driver from https://github.com/xerial/sqlite-jdbc * (e.g., sqlite-jdbc-3.46.0.0.jar) * * Compile: javac JDBCDemo.java * Run: java -cp .:sqlite-jdbc-3.46.0.0.jar JDBCDemo * (On Windows use ; instead of : as classpath separator) * * The program creates/uses a file called "students.db" in the current directory. */ public class JDBCDemo { // SQLite connection URL (file-based database) private static final String DB_URL = "jdbc:sqlite:students.db"; // ================================================================ // 1. Create table // ================================================================ static void createTable(Connection conn) throws SQLException { String sql = """ CREATE TABLE IF NOT EXISTS students ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, dept TEXT NOT NULL, gpa REAL NOT NULL, email TEXT UNIQUE ) """; try (Statement stmt = conn.createStatement()) { stmt.execute(sql); System.out.println("[CREATE] Table 'students' is ready."); } } // ================================================================ // 2. Insert with PreparedStatement // ================================================================ static void insertStudent(Connection conn, String name, String dept, double gpa, String email) throws SQLException { String sql = "INSERT INTO students (name, dept, gpa, email) VALUES (?, ?, ?, ?)"; try (PreparedStatement pstmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) { pstmt.setString(1, name); pstmt.setString(2, dept); pstmt.setDouble(3, gpa); pstmt.setString(4, email); pstmt.executeUpdate(); // Retrieve auto-generated key try (ResultSet keys = pstmt.getGeneratedKeys()) { if (keys.next()) { System.out.println("[INSERT] " + name + " -> id=" + keys.getInt(1)); } } } } // ================================================================ // 3. Select all students // ================================================================ static void selectAll(Connection conn) throws SQLException { String sql = "SELECT id, name, dept, gpa, email FROM students ORDER BY id"; try (Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(sql)) { System.out.println("[SELECT ALL]"); System.out.printf(" %-4s %-12s %-6s %-5s %-25s%n", "ID", "Name", "Dept", "GPA", "Email"); System.out.println(" " + "-".repeat(55)); while (rs.next()) { System.out.printf(" %-4d %-12s %-6s %-5.2f %-25s%n", rs.getInt("id"), rs.getString("name"), rs.getString("dept"), rs.getDouble("gpa"), rs.getString("email")); } } } // ================================================================ // 4. Select by department (parameterized query) // ================================================================ static void selectByDept(Connection conn, String dept) throws SQLException { String sql = "SELECT id, name, gpa FROM students WHERE dept = ?"; try (PreparedStatement pstmt = conn.prepareStatement(sql)) { pstmt.setString(1, dept); try (ResultSet rs = pstmt.executeQuery()) { System.out.println("[SELECT] Students in dept '" + dept + "':"); while (rs.next()) { System.out.printf(" id=%d name=%s gpa=%.2f%n", rs.getInt("id"), rs.getString("name"), rs.getDouble("gpa")); } } } } // ================================================================ // 5. Update GPA // ================================================================ static void updateGpa(Connection conn, int id, double newGpa) throws SQLException { String sql = "UPDATE students SET gpa = ? WHERE id = ?"; try (PreparedStatement pstmt = conn.prepareStatement(sql)) { pstmt.setDouble(1, newGpa); pstmt.setInt(2, id); int rows = pstmt.executeUpdate(); System.out.println("[UPDATE] id=" + id + " new gpa=" + newGpa + " (rows affected: " + rows + ")"); } } // ================================================================ // 6. Delete student // ================================================================ static void deleteStudent(Connection conn, int id) throws SQLException { String sql = "DELETE FROM students WHERE id = ?"; try (PreparedStatement pstmt = conn.prepareStatement(sql)) { pstmt.setInt(1, id); int rows = pstmt.executeUpdate(); System.out.println("[DELETE] id=" + id + " (rows affected: " + rows + ")"); } } // ================================================================ // 7. Batch insert // ================================================================ static void batchInsert(Connection conn, String[][] data) throws SQLException { String sql = "INSERT INTO students (name, dept, gpa, email) VALUES (?, ?, ?, ?)"; try (PreparedStatement pstmt = conn.prepareStatement(sql)) { for (String[] row : data) { pstmt.setString(1, row[0]); pstmt.setString(2, row[1]); pstmt.setDouble(3, Double.parseDouble(row[2])); pstmt.setString(4, row[3]); pstmt.addBatch(); } int[] results = pstmt.executeBatch(); System.out.println("[BATCH INSERT] Inserted " + results.length + " rows."); } } // ================================================================ // 8. Transaction demo (commit / rollback) // ================================================================ static void transactionDemo(Connection conn) throws SQLException { System.out.println("[TRANSACTION] Attempting two inserts in a transaction..."); boolean originalAutoCommit = conn.getAutoCommit(); conn.setAutoCommit(false); // begin transaction try { insertStudent(conn, "TxnStudent1", "CS", 3.5, "txn1@test.com"); insertStudent(conn, "TxnStudent2", "IS", 3.8, "txn2@test.com"); conn.commit(); System.out.println("[TRANSACTION] Committed successfully."); } catch (SQLException e) { conn.rollback(); System.out.println("[TRANSACTION] Rolled back due to error: " + e.getMessage()); } finally { conn.setAutoCommit(originalAutoCommit); } // Demonstrate rollback System.out.println("[TRANSACTION] Attempting insert + deliberate rollback..."); conn.setAutoCommit(false); try { insertStudent(conn, "WillBeRolledBack", "Math", 2.5, "rollback@test.com"); System.out.println("[TRANSACTION] About to rollback deliberately..."); conn.rollback(); // undo the insert System.out.println("[TRANSACTION] Rolled back. 'WillBeRolledBack' was NOT persisted."); } finally { conn.setAutoCommit(originalAutoCommit); } } // ================================================================ // Main // ================================================================ public static void main(String[] args) { // try-with-resources ensures the connection is closed automatically try (Connection conn = DriverManager.getConnection(DB_URL)) { System.out.println("Connected to SQLite database: " + DB_URL); System.out.println(); // Drop table if exists (clean slate for demo) try (Statement stmt = conn.createStatement()) { stmt.execute("DROP TABLE IF EXISTS students"); } // Create table createTable(conn); System.out.println(); // Insert individual students System.out.println("=== INSERT ==="); insertStudent(conn, "Ali", "CS", 3.8, "ali@university.ac.tz"); insertStudent(conn, "Fatma", "CS", 3.5, "fatma@university.ac.tz"); insertStudent(conn, "Omar", "IS", 3.9, "omar@university.ac.tz"); insertStudent(conn, "Aisha", "Math", 3.2, "aisha@university.ac.tz"); System.out.println(); // Select all selectAll(conn); System.out.println(); // Select by department selectByDept(conn, "CS"); System.out.println(); // Update System.out.println("=== UPDATE ==="); updateGpa(conn, 2, 3.75); System.out.println(); selectAll(conn); System.out.println(); // Delete System.out.println("=== DELETE ==="); deleteStudent(conn, 4); System.out.println(); selectAll(conn); System.out.println(); // Batch insert System.out.println("=== BATCH INSERT ==="); String[][] batchData = { {"Hassan", "IS", "3.7", "hassan@university.ac.tz"}, {"Zara", "Math", "3.6", "zara@university.ac.tz"}, {"Yusuf", "CS", "3.1", "yusuf@university.ac.tz"} }; batchInsert(conn, batchData); System.out.println(); selectAll(conn); System.out.println(); // Transaction demo System.out.println("=== TRANSACTION ==="); transactionDemo(conn); System.out.println(); selectAll(conn); System.out.println("\n--- JDBCDemo complete ---"); } catch (SQLException e) { System.err.println("Database error: " + e.getMessage()); e.printStackTrace(); } } }