package inf.smart.prestatement;

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

class SearchTest {
    private static final String allRecords =
            "SELECT Trackers.Name FROM Trackers";

    private static final String withPhone =
            "SELECT Trackers.Name FROM Trackers, Phones " +
                    "WHERE Trackers.Name = Phones.Name";

    private static final String phoneByName =
            "SELECT Phone FROM Phones WHERE Name = ?";

    private static final String ipWithPhone =
            "SELECT Trackers.Ip, Phones.Phone FROM Trackers, Phones " +
                    "WHERE Trackers.Name = Phones.Name AND Phones.Name = ?";

    private static Connection conn;
    private static PreparedStatement stmt;
    private static ResultSet resultSet;
    private static final String URL =
            "jdbc:mysql://localhost:3306/dreambase?user=avalon&password=aladin";

    public static void main(String[] args) throws IOException, SQLException {

        Scanner in = new Scanner(System.in);

        try {
            conn = DriverManager.getConnection(URL);

            System.out.println(" ***** All persons: ");

            stmt = conn.prepareStatement(allRecords);
            resultSet = stmt.executeQuery();
            int i = 0;
            while (resultSet.next())
                System.out.println(++i + ". " + resultSet.getString(1));

            System.out.println(" ***** Persons with phone number: ");
            stmt = conn.prepareStatement(withPhone);
            resultSet = stmt.executeQuery();
            i = 0;
            while (resultSet.next())
                System.out.println(++i + ". " + resultSet.getString(1));
            String name;
            i = 0;

            while (i == 0) // не вывелась строка - значит не правильный ввод
            {
                System.out.println(" ***** Phone by name : ");
                name = in.nextLine().trim();

                stmt = conn.prepareStatement(phoneByName);
                stmt.setString(1, name);   // параметры - номер переменной и ее значение
                resultSet = stmt.executeQuery();

                while (resultSet.next())
                    System.out.println(++i + ". " + resultSet.getString(1));
            }

            i = 0;
            while (i == 0) {
                System.out.println(" ***** IP with phone by name : ");
                name = in.nextLine().trim();

                stmt = conn.prepareStatement(ipWithPhone);
                stmt.setString(1, name);
                resultSet = stmt.executeQuery();

                while (resultSet.next())
                    System.out.println(++i + ". " + resultSet.getString(1) +
                            ", " + resultSet.getString(2));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                resultSet.close();
                stmt.close();
                conn.close();
            } catch (SQLException e) {}
        }
    }
}