Slip No 16 Q B

Write a Java program to accept the details of students (rno, sname, per) at least 5 Records, store it into database and display the details of student having highest percentage. (Use PreparedStatement Interface)

import java.sql.*;
import java.util.Scanner;

public class StudentRecords {

    public static void main(String[] args) {
        Scanner sc = new Scanner(System.in);

        String url = "jdbc:mysql://localhost:3306/testdb"; // Change DB name if required
        String user = "root"; // DB username
        String password = "root"; // DB password

        try {
            // Load JDBC Driver
            Class.forName("com.mysql.cj.jdbc.Driver");

            // Establish Connection
            Connection con = DriverManager.getConnection(url, user, password);
            System.out.println("Connected to Database");

            // Create Student table if not exists
            String createTable = "CREATE TABLE IF NOT EXISTS Student ("
                    + "rno INT PRIMARY KEY, "
                    + "sname VARCHAR(50), "
                    + "per DOUBLE)";
            Statement stmt = con.createStatement();
            stmt.executeUpdate(createTable);
            System.out.println("Student Table Created");

            // Insert Records using PreparedStatement
            String insertQuery = "INSERT INTO Student (rno, sname, per) VALUES (?, ?, ?)";
            PreparedStatement ps = con.prepareStatement(insertQuery);

            // Accept at least 5 student records
            for(int i=1; i<=5; i++) {
                System.out.println("Enter details for student " + i + ":");

                System.out.print("Roll No: ");
                int rno = sc.nextInt();
                sc.nextLine(); // Consume newline

                System.out.print("Student Name: ");
                String sname = sc.nextLine();

                System.out.print("Percentage: ");
                double per = sc.nextDouble();

                ps.setInt(1, rno);
                ps.setString(2, sname);
                ps.setDouble(3, per);

                ps.executeUpdate();
                System.out.println("Record inserted successfully!\n");
            }

            // Display student(s) with highest percentage
            String highestPerQuery = "SELECT * FROM Student WHERE per = (SELECT MAX(per) FROM Student)";
            ResultSet rs = stmt.executeQuery(highestPerQuery);

            System.out.println("Student(s) with Highest Percentage:");
            System.out.println("----------------------------------");
            while(rs.next()) {
                System.out.println("Roll No: " + rs.getInt("rno"));
                System.out.println("Name   : " + rs.getString("sname"));
                System.out.println("Percent: " + rs.getDouble("per"));
                System.out.println("----------------------------------");
            }

            // Close resources
            rs.close();
            ps.close();
            stmt.close();
            con.close();
            sc.close();

        } catch(Exception e) {
            System.out.println("Error: " + e);
        }
    }
}