Slip No 11 Q B

Write a Java program to display sales details of Product (PID, PName, Qty, Rate, Amount) between two selected dates. (Assume Sales table is already created).

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

public class SalesBetweenDates {

    public static void main(String[] args) {

        String url = "jdbc:mysql://localhost:3306/shop"; // change DB name
        String user = "root";     // DB username
        String password = "root"; // DB password

        Scanner sc = new Scanner(System.in);

        System.out.print("Enter Start Date (YYYY-MM-DD): ");
        String startDate = sc.next();

        System.out.print("Enter End Date (YYYY-MM-DD): ");
        String endDate = sc.next();

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

            // Establish Connection
            Connection con = DriverManager.getConnection(url, user, password);

            // SQL query using BETWEEN
            String sql = "SELECT pid, pname, qty, rate, amount FROM Sales "
                       + "WHERE sales_date BETWEEN ? AND ?";

            PreparedStatement ps = con.prepareStatement(sql);
            ps.setString(1, startDate);
            ps.setString(2, endDate);

            ResultSet rs = ps.executeQuery();

            System.out.println("\nSales Details Between " + startDate + " and " + endDate);
            System.out.println("------------------------------------------------------");
            System.out.printf("%-5s %-15s %-5s %-8s %-10s%n",
                    "PID", "PName", "Qty", "Rate", "Amount");

            while (rs.next()) {
                System.out.printf("%-5d %-15s %-5d %-8.2f %-10.2f%n",
                        rs.getInt("pid"),
                        rs.getString("pname"),
                        rs.getInt("qty"),
                        rs.getDouble("rate"),
                        rs.getDouble("amount"));
            }

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

        } catch (Exception e) {
            System.out.println(e);
        }

        sc.close();
    }
}