import java.sql.*;

class zad2 {
    private static void zad1(Connection con) throws SQLException {
        String query = "SELECT * FROM klient";
        String query2 = "SELECT * FROM adres";

        ResultSet result = con.createStatement().executeQuery(query);
        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();
        }

        result = con.createStatement().executeQuery(query2);
        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();
        }
    }


    private static void zad2(Connection con, String miasto) throws SQLException {
        String query = "SELECT K.nazwisko, DATEDIFF(year, K.data_ur, GETDATE()) AS wiek, A.miasto " +
                        " FROM klient K" +
                        " INNER JOIN adres A ON K.id_klient = A.id_klient" +
                        " WHERE a.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();
        }
    }


    private static void zad3(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));
    }


    private static void zad4(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();
        }
    }

    public static void main(String[] args) {
        Connection con;
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            con = DriverManager.getConnection("jdbc:sqlserver://"+
                    "153.19.7.13:1401;databaseName=pkrause;"+
                    "user=pkrause;password=253983;");

            System.out.println("Connection passed");

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

            System.out.println("\nZadanie 2: ");
            zad2(con, "Gdansk");

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

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

            con.close();
        } catch (SQLException e) {
            System.out.println("Connection error");
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            System.out.println("Driver error");
            e.printStackTrace();
        }
    }
}