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


class Main {
    private static void zadanie1(Connection con) throws SQLException {
        String zapytanie1 = "SELECT * FROM klient";
        String zapytanie2 = "SELECT * FROM adres";

        ResultSet result = con.createStatement().executeQuery(zapytanie1);
        ResultSetMetaData countCol = result.getMetaData();

        System.out.println("Tabela klient: ");
        while(result.next()) {
            System.out.print("| ");
            for(int i = 1; i <= countCol.getColumnCount(); i++)
                System.out.print(result.getString(i) + " | ");
            System.out.println();
        }
        System.out.println("\n");


        result = con.createStatement().executeQuery(zapytanie2);
        countCol = result.getMetaData();

        System.out.println("Tabela adres: ");
        while(result.next()) {
            System.out.print("| ");
            for(int i = 1; i <= countCol.getColumnCount(); i++)
                System.out.print(result.getString(i) + " | ");
            System.out.println();
        }
        System.out.println("\n");
    }


    private static void zadanie2(Connection con, String miasto) throws SQLException {
        String query = "SELECT klient.nazwisko, DATEDIFF(year, klient.data_ur, GETDATE()) AS wiek, adres.miasto " +
                " FROM klient" +
                " INNER JOIN adres ON klient.id_klient = adres.id_klient" +
                " WHERE adres.miasto = \'" + miasto + "\'";

        ResultSet result = con.createStatement().executeQuery(query);
        ResultSetMetaData countCol = result.getMetaData();

        while(result.next()) {
            System.out.print("| ");
            for(int i = 1; i <= countCol.getColumnCount(); i++)
                System.out.print(result.getString(i) + " | ");
            System.out.println();
        }
        System.out.println("\n");
    }


    private static void zadadanie3(Connection con) throws SQLException {
        String query = "SELECT K.nazwisko, DATEDIFF(year, K.data_ur, GETDATE()) AS wiek" +
                " FROM klient K" +
                " ORDER BY DATEDIFF(year, K.data_ur, GETDATE()) ASC";

        ResultSet result = con.createStatement().executeQuery(query);
        ResultSetMetaData countCol = result.getMetaData();

        result.next();

        System.out.println("Osoba najmlodsza: " + result.getString(1));
        System.out.println("\n");
    }


    private static void zadadanie4(Connection con) throws SQLException {
        String query = "SELECT miasto, COUNT(*)" +
                " FROM adres" +
                " GROUP BY miasto";

        ResultSet result = con.createStatement().executeQuery(query);
        ResultSetMetaData countCol = result.getMetaData();

        while(result.next()) {
            System.out.print("| ");
            for(int i = 1; i <= countCol.getColumnCount(); i++)
                System.out.print(result.getString(i) + " | ");
            System.out.println();
        }
        System.out.println("\n");
    }

    public static void main(String[] args) {
        Scanner scan = new Scanner(System.in);
        System.out.println("Podaj nazwę bazy danych oraz nazwę użytkownika");
        String nazwa_bazy_danych = scan.nextLine();
        Scanner scan1 = new Scanner(System.in);
        System.out.println("Podaj hasło");
        String hasło = scan.nextLine();

        Connection con;
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            con = DriverManager.getConnection("jdbc:sqlserver://"+
                    "153.19.7.13:1401;databaseName="+nazwa_bazy_danych+";"+
                    "user="+nazwa_bazy_danych+";password="+hasło+";");

            System.out.println("Połączono z bazą danych");

            System.out.println("\nZadanie 1: ");
            zadanie1(con);

            System.out.println("\nZadanie 2: ");
            zadanie2(con, "Kwidzyn");

            System.out.println("\nZadanie 3: ");
            zadadanie3(con);

            System.out.println("\nZadanie 4: ");
            zadadanie4(con);

            con.close();
        } catch(SQLException error_polaczenie) {
            System.out.println("Błąd połączenia z bazą danych");}
        catch(ClassNotFoundException error_sterownik) {
            System.out.println("Brak sterownika");
        }
    }
}