package db.test;

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

public class DBTest {

    static final String DB_CONNECTION = "jdbc:mysql://localhost:3306/mysql";
    static final String DB_USER = "root";
    static final String DB_PASSWORD = "root";
    static Connection conn;

    private static void addApatament(String command) throws SQLException {

        try (PreparedStatement ps = conn.prepareStatement(command)) {

            ps.executeUpdate();
        }
    }
            //Формируем запрос на поиск нужной квартиры
    private static void viewRequest(String cm) throws SQLException {
        String command = "SELECT * FROM Apart WHERE square = " + cm;
        try (PreparedStatement ps = conn.prepareStatement(command);
            ResultSet rs = ps.executeQuery()) {
                ResultSetMetaData md = rs.getMetaData();
                System.out.println("Результат поиска:");
                for (int i = 1; i <= md.getColumnCount(); i++) {
                    System.out.print(md.getColumnName(i) + "\t\t");
                }
                System.out.println();
                while (rs.next()) {
                    for (int i = 1; i <= md.getColumnCount(); i++) {
                        System.out.print(rs.getString(i) + "\t\t");
                    }
                    System.out.println();

                }
        }
    }

    private static void viewApatament() throws SQLException {
        try (PreparedStatement ps = conn.prepareStatement("SELECT * FROM Apart");
            ResultSet rs = ps.executeQuery()) {
                ResultSetMetaData md = rs.getMetaData();
                System.out.println("Содержание БД:");
                for (int i = 1; i <= md.getColumnCount(); i++) {
                    System.out.print(md.getColumnName(i) + "\t\t");
                }
                System.out.println();
                while (rs.next()) {
                    for (int i = 1; i <= md.getColumnCount(); i++) {
                        System.out.print(rs.getString(i) + "\t\t");
                    }
                    System.out.println();
                }
        }
    }

    private static void initDB() throws SQLException {
        try (Statement st = conn.createStatement()) {
            st.execute("DROP TABLE IF EXISTS Apart");
            st.execute("CREATE TABLE Apart (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, region VARCHAR(20)"+
                    "NOT NULL, adress VARCHAR(20) NOT NULL, square INT, quantity_room INT, price FLOAT)");
        }
    }

    public static void main(String[] args) {
        Scanner sc = new Scanner(System.in);
        try {
            try {
                //Соеденение с БД
                conn = DriverManager.getConnection(DB_CONNECTION, DB_USER, DB_PASSWORD);
                initDB(); //Создам и заплоняем БД
                addApatament("INSERT INTO Apart (region, adress, square, quantity_room, price) VALUES('Kiev', 'Obolon #17/4', 65, 4, 125600.00)");
                addApatament("INSERT INTO Apart (region, adress, square, quantity_room, price) VALUES('Odesa', 'Arkadia #25', 56, 3, 120600.00)");
                addApatament("INSERT INTO Apart (region, adress, square, quantity_room, price) VALUES('Lviv', 'Banders str. #4', 67, 3, 105600.00)");
                addApatament("INSERT INTO Apart (region, adress, square, quantity_room, price) VALUES('Dnepr', 'Parus #18', 60, 3, 128600.00)");
                viewApatament();
                viewRequest("65"); //Ищем квартиру полщадью 65 квадратных метров
            } finally {
                sc.close();
                if (conn != null) {
                    conn.close();
                }
            }
        } catch (SQLException ex) {
            System.out.println(ex);
        }
    }

}