package populationdemo;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;

public class PopulationDemo
{

    public static void main(String[] args)
    {
        String sql;
        char choice;
        final String DB_URL = "jdbc:mysql://localhost:3306/CityDB";

        do
        {
            choice = menu();
            switch(choice)
            {
                case 'A':
                    a(DB_URL);
                    break;
                case 'a':
                    a(DB_URL);
                    break;
                case 'B':
                    b(DB_URL);
                    break;
                case 'b':
                    b(DB_URL);
                    break;
                case 'C':
                    c(DB_URL);
                    break;
                case 'c':
                    c(DB_URL);
                    break;
            }
        } while(choice != '0');
    }

    public static char menu()
    {
        System.out.println();
        System.out.println("A. Sort the list of cities by population, in ascending order");
        System.out.println("B. Get the total population of all the cities");
        System.out.println("C. Get the lowest population");
        System.out.println("Choose an option: ");

        Scanner keyboard = new Scanner(System.in);
        char choice = keyboard.nextLine().charAt(0);
        return choice;
    }

    public static void a(String DB_URL)
    {
        String sql = "Select * from city order by population desc";
        try
        {
            Connection conn = DriverManager.getConnection(DB_URL,"root","");
            Statement stmt = conn.createStatement();
            ResultSet rs;
            rs = stmt.executeQuery(sql);

            System.out.printf("%-20s %-20s\n",
            "City", "Population");
            while(rs.next())
            {
                System.out.printf("%-20s %-20s\n",
                rs.getString("CityName"),
                rs.getString("Population"));
            }
        }
        catch(SQLException ex)
        {
            System.out.println("ERROR: " + ex.getMessage());
        }
    }

    public static void b(String DB_URL)
    {
        String sql = "Select population, count(population) FROM city";
        try
        {
            Connection conn = DriverManager.getConnection(DB_URL,"root","");
            Statement stmt = conn.createStatement();
            ResultSet rs;
            rs = stmt.executeQuery(sql);

            System.out.printf("%-20s\n",
            "Total Population");
            while(rs.next())
            {
                System.out.printf("%-20s\n",
                rs.getString("Population"));
            }
        }
        catch(SQLException ex)
        {
            System.out.println("ERROR: " + ex.getMessage());
        }
    }

    public static void c(String DB_URL)
    {
        String sql = "Select CityName, Min(Population) AS Population from City";
        try
        {
            Connection conn = DriverManager.getConnection(DB_URL,"root","");
            Statement stmt = conn.createStatement();
            ResultSet rs;
            rs = stmt.executeQuery(sql);

            System.out.printf("%-20s %-20s\n",
            "City", "Population");
            while(rs.next())
            {
                System.out.printf("%-20s %-20s\n",
                rs.getString("CityName"),
                rs.getString("Population"));
            }
        }
        catch(SQLException ex)
        {
            System.out.println("ERROR: " + ex.getMessage());
        }
    }
}