import com.mysql.jdbc.Connection;
import com.mysql.jdbc.Statement;

import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;


/**
 * Created by User on 12.06.2016.
 */
public class Main {

    public static void main(String[] args) throws ClassNotFoundException, Exception {
        createTable();
        createFlat();
        searchByStreatAndPrice();
        searchByRegion();

    }
    private static Connection getDBConnection(){
        Connection dbConnection = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            System.out.println(e.getMessage());
        }
        try {
            dbConnection = (Connection) DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/flats", "root","304FF76v");
            return dbConnection;
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
        return dbConnection;
    }
    public static void createTable() throws Exception {
        Connection dbConnection = null;
        Statement statement = null;

        String sqlCreateTable = "create table flats (id int primary key auto_increment, streat varchar(100) not null, number varchar(7) not null, region varchar(100) not null, area int not null, room int not null, price int not null)";

        try {
            dbConnection = getDBConnection();

            statement = (Statement) dbConnection.createStatement();
            statement.execute(sqlCreateTable);
            System.out.println("Create table flats");
        }catch (SQLException e){
            System.out.println(e);
        }finally {
            if (dbConnection != null){
                dbConnection.close();
            }
            if (statement != null){
                statement.close();
            }
        }
    }
    public static void createFlat() throws SQLException {
        Connection dbConnection = null;
        Statement statement = null;
        String sqlCreateFlat = "insert  flats set streat = 'Kitaevskaua', number = '3', region = 'Goloseevskiy', area = '50', room = '4', price = '4700' ";

        try {
            dbConnection = getDBConnection();
            statement = (Statement) dbConnection.createStatement();
            statement.execute(sqlCreateFlat);
            System.out.println("Create row in flats");
        }catch (SQLException e){
            System.out.println(e);
        }finally {
            if (dbConnection != null){
                dbConnection.close();
            }
            if (statement != null){
                statement.close();
            }
        }
    }
    public static void searchByStreatAndPrice() throws SQLException {
        Connection dbConnection = null;
        Statement statement = null;

        String sqlSearch = "select * from flats where streat = 'Nauki' and price = '4700'";

        dbConnection = getDBConnection();
        statement = (Statement) dbConnection.createStatement();
        ResultSet query = statement.executeQuery(sqlSearch);
        while (query.next()){
            String streat = query.getString("streat");
            String number = query.getString("number");
            String region = query.getString("region");
            String area = query.getString("area");
            String room = query.getString("room");
            String price = query.getString("price");
            //print
            System.out.println("--> Streat " + streat
                                + " Number " + number
                                + " Region " + region
                                + " Area " + area
                                + " Rooms " + room
                                + " Price " + price);

        }


    }
    public static void searchByRegion() throws SQLException {
        Connection dbConnection = null;
        Statement statement = null;

        String sqlSearch = "select * from flats where region = 'Goloseevskiy' ";

        dbConnection = getDBConnection();
        statement = (Statement) dbConnection.createStatement();
        ResultSet query = statement.executeQuery(sqlSearch);
        while (query.next()){
            String streat = query.getString("streat");
            String number = query.getString("number");
            String region = query.getString("region");
            String area = query.getString("area");
            String room = query.getString("room");
            String price = query.getString("price");
            //print
            System.out.println("--> Streat " + streat
                    + " Number " + number
                    + " Region " + region
                    + " Area " + area
                    + " Rooms " + room
                    + " Price " + price);

        }
    }
}