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 addTab(String command) throws SQLException {

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

            ps.executeUpdate();
        }
    }
    //Запрос по трем таблицам по номеру заказа
    private static void viewAllTab(String cm) throws SQLException {
        String command = "SELECT * FROM good, client, ordere WHERE good.id_oreder ="+cm+" AND client.id_oreder ="+cm+" AND ordere.id_oreder ="+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 viewTab(String tb) throws SQLException {
        String tab="SELECT * FROM "+tb;
        try (PreparedStatement ps = conn.prepareStatement(tab);
            ResultSet rs = ps.executeQuery()) {
                ResultSetMetaData md = rs.getMetaData();
                System.out.println("Содержание БД: "+tb);
                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 createTabGood() throws SQLException {
        try (Statement st = conn.createStatement()) {
            st.execute("DROP TABLE IF EXISTS good");
            st.execute("CREATE TABLE good (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY NOT NULL, id_oreder INT, name VARCHAR(20)"+
                    "NOT NULL, class VARCHAR(20) NOT NULL, made_in VARCHAR(20) NOT NULL, price FLOAT NOT NULL)");
        }
    }
        //Создаем таблицу клиентов
     private static void createTabClient() throws SQLException {
        try (Statement st = conn.createStatement()) {
            st.execute("DROP TABLE IF EXISTS client");
            st.execute("CREATE TABLE client (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY NOT NULL, id_oreder INT,"+
                    "name VARCHAR(20) NOT NULL, adress VARCHAR(20) NOT NULL)");
        }
    }
        //Создаем таблицу заказов
    private static void createTabOrder() throws SQLException {
        try (Statement st = conn.createStatement()) {
            st.execute("DROP TABLE IF EXISTS ordere");
            st.execute("CREATE TABLE ordere (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY NOT NULL, id_oreder INT,"+
                    "good VARCHAR(20) NOT NULL, Quantity_goods INT NOT NULL)");
        }
    }

    public static void main(String[] args) {

        try {

            //Соеденение с БД
            conn = DriverManager.getConnection(DB_CONNECTION, DB_USER, DB_PASSWORD);
            createTabGood(); //Создаем таблицы
            createTabClient();
            createTabOrder();
            //Заполняем таблицу товаров
            addTab("INSERT INTO good (id_oreder, name, class, made_in,price) VALUES(658, 'Вино', 'Мадера', 'Франция', 600.00)");
            addTab("INSERT INTO good (id_oreder, name, class, made_in,price) VALUES(3254, 'Пиво', 'Оболнь', 'Укарина', 45.17)");
            addTab("INSERT INTO good (id_oreder, name, class, made_in,price) VALUES(584, 'Сигареты', 'Mallboro', 'США', 125.10)");
            //Заполняем таблицу клиентов
            addTab("INSERT INTO client (id_oreder, name, adress) VALUES(658, 'Madam Clico', 'Parise')");
            addTab("INSERT INTO client (id_oreder, name, adress) VALUES(3254, 'Пивзавод - Оболонь', 'Киев')");
            addTab("INSERT INTO client (id_oreder, name, adress) VALUES(584, 'Phillip Moris', 'Virdginia')");
            //Заполняем таблицу заказов
            addTab("INSERT INTO ordere (id_oreder, good, Quantity_goods) VALUES(658, 'Vine', 285)");
            addTab("INSERT INTO ordere (id_oreder, good, Quantity_goods) VALUES(3254, 'Beer', 658)");
            addTab("INSERT INTO ordere (id_oreder, good, Quantity_goods) VALUES(584, 'Sigarete', 12568)");
            //Просмотр содержимого таблиц
            viewTab("good");
            viewTab("client");
            viewTab("ordere");
            viewAllTab("658"); //Формируем заказ по заданному номеру

        } catch (SQLException ex) {
            System.out.println(ex);
        }
    }

}