import java.sql.*;

public class Appartments {
    private static final String DB_DRIVER = "com.mysql.jdbc.Driver";
    private static final String DB_CONNECTION = "jdbc:mysql://localhost:3306/mydb";
    private static final String DB_USER = "root";
    private static final String DB_PASSWORD = "";

    private static final String CREATE_TABLE_SQL = "CREATE TABLE IF NOT EXISTS Appartments (" +
            "id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, " +
            "region VARCHAR(100) NOT NULL, " +
            "address VARCHAR(100) NOT NULL, " +
            "space INT(10) NOT NULL, " +
            "rooms INT(10) NOT NULL, " +
            "price INT(10) NOT NULL)";

    public static void main(String[] args) {
        Connection conn = getDBConnection();
        if (conn == null) {
            System.out.println("Error creating connection!");
            return;
        }

        try {
            Statement st = conn.createStatement();
            st.execute(CREATE_TABLE_SQL);
            //fillDb(st, conn);
            selectDb(st, conn, "*", "appartments", "price", "2000", "4000");
            selectDb(st, conn, "address", "appartments", "rooms='2'");
            selectDb(st, conn, "id", "appartments");
            sortDb(st, conn, "price");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private static void sortDb(Statement st, Connection conn,  String sort) throws SQLException {
        st = conn.createStatement();
        try {
            ResultSet rs = st.executeQuery("SELECT * FROM appartments ORDER BY "+sort+";");
            ResultSetMetaData md = rs.getMetaData();

            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();
            }
        } finally {
            if (st != null) st.close();
        }
    }

    private static void selectDb(Statement st, Connection conn, String... array) throws SQLException {
        String arr[] = array;
        st = conn.createStatement();
        try {
            ResultSet rs;
            if(arr.length == 5)
                rs = st.executeQuery("SELECT "+arr[0]+" FROM "+arr[1]+" WHERE " +arr[2]+
                        " BETWEEN "+Integer.parseInt(arr[3])+" AND "+Integer.parseInt(arr[4])+";");
            else if(arr.length == 3)
                rs = st.executeQuery("SELECT "+arr[0]+" FROM "+arr[1]+" WHERE " +arr[2]+";");
            else
                rs = st.executeQuery("SELECT "+arr[0]+" FROM "+arr[1]+";");

            ResultSetMetaData md = rs.getMetaData();

            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();
            }
        } finally {
            if (st != null) st.close();
        }
    }

    private static void fillDb(Statement st, Connection conn) throws SQLException {
        try {
            st = conn.createStatement();
            st.executeUpdate("INSERT INTO Appartments (region, address, space, rooms, price) " +
                    "VALUES ('Darnitsa', 'Grirorenko 15/7', '55', '2', '4000');");
            st.executeUpdate("INSERT INTO Appartments (region, address, space, rooms, price) " +
                    "VALUES ('Darnitsa', 'Ahmatova 156/88', '27', '1', '2000');");
            st.executeUpdate("INSERT INTO Appartments (region, address, space, rooms, price) " +
                    "VALUES ('Darnitsa', 'Ahmatova 1a/77', '28', '1', '3000');");
            st.executeUpdate("INSERT INTO Appartments (region, address, space, rooms, price) " +
                    "VALUES ('Darnitsa', 'Bazana 13/3', '40', '2', '5000');");
            st.executeUpdate("INSERT INTO Appartments (region, address, space, rooms, price) " +
                    "VALUES ('Darnitsa', 'Bazana 15/99', '60', '3', '55000');");
            st.executeUpdate("INSERT INTO Appartments (region, address, space, rooms, price) " +
                    "VALUES ('Darnitsa', 'Bazana 21/123', '67', '3', '5300');");
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            if (st != null) st.close();
        }
    }

    private static Connection getDBConnection() {
        Connection dbConnection = null;
        try {
            Class.forName(DB_DRIVER);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            return null;}

        try {
            dbConnection = DriverManager.getConnection(DB_CONNECTION, DB_USER, DB_PASSWORD);
        } catch (SQLException e) {
            e.printStackTrace();
            return null;}
        return dbConnection;
    }
}