9T3rA1jW

1package myGrId;
2
3import java.sql.*;
4import java.util.Scanner;
5
6/**
7 * Created by genalee on 04.02.16.
8 */
9public class MainClass {
10    static final String DB_CONNECTION = "jdbc:mysql://localhost:3306/mydbTest";
11    static final String DB_USER = "root";
12    static final String DB_PASSWORD = "*******";
13
14    static Connection conn;
15
16    public static void main(String[] args) {
17        Scanner sc = new Scanner(System.in);
18        try {
19            try {
20                // create connection
21                conn = DriverManager.getConnection(DB_CONNECTION, DB_USER, DB_PASSWORD);
22                initDB();
23
24                while (true) {
25                    System.out.println("1: add flat");
26                    System.out.println("2: view flat with params");
27                    System.out.print("-> ");
28
29                    String s = sc.nextLine();
30                    switch (s) {
31                        case "1":
32                            addFlat(sc);
33                            break;
34                        case "2":
35                            viewFlats(sc);
36                            break;
37                        default:
38                            return;
39                    }
40                }
41            } finally {
42                sc.close();
43                if (conn != null) conn.close();
44            }
45        } catch (SQLException ex) {
46            ex.printStackTrace();
47            return;
48        }
49    }
50
51    private static void viewFlats(Scanner scan) throws SQLException {
52
53        while (true) {
54            System.out.println("1: Выбор квартир по городу");
55            System.out.println("2: Выбор квартир по адресу (по фрагменту)");
56            System.out.println("3: Выбор по кол-ву комнат");
57            System.out.println("4: Выбор по цене (больше или равно)");
58            System.out.println("5: Выбор по цене (меньше или равно)");
59            System.out.print("-> ");
60
61            String s = scan.nextLine();
62            String sql="";
63            String price = "";
64            switch (s) {
65                case "1":
66                    System.out.print("Введите город: ");
67                    String city = scan.nextLine();
68                    sql = "SELECT * FROM flats WHERE UPPER(city)=\""+city.toUpperCase()+"\"";
69                    viewQuery(sql);
70                    System.out.println();
71                    break;
72                case "2":
73                    System.out.print("Введите фрагмент адреса: ");
74                    String address = scan.nextLine();
75                    sql = "SELECT * FROM flats WHERE UPPER(address) like \'%"+address.toUpperCase()+"%\'";
76                    viewQuery(sql);
77                    System.out.println();
78                    break;
79                case "3":
80                    System.out.print("Введите кол-во комнат: ");
81                    String rooms = scan.nextLine();
82                    sql = "SELECT * FROM flats WHERE room_count="+rooms;
83                    viewQuery(sql);
84                    System.out.println();
85                    break;
86                case "4":
87                    System.out.print("Введите минимальную цену: ");
88                    price = scan.nextLine();
89                    sql = "SELECT * FROM flats WHERE price >="+price;
90                    viewQuery(sql);
91                    System.out.println();
92                    break;
93                case "5":
94                    System.out.print("Введите максимальную цену: ");
95                    price = scan.nextLine();
96                    sql = "SELECT * FROM flats WHERE price <="+price;
97                    viewQuery(sql);
98                    System.out.println();
99                    break;
100                default:
101                    return;
102            }
103        }
104    }
105
106    private static void viewQuery(String sql) throws SQLException {
107        PreparedStatement ps = conn.prepareStatement(sql);
108        try {
109            ResultSet rs = ps.executeQuery();
110            try {
111                ResultSetMetaData md = rs.getMetaData();
112
113                for (int i = 1; i <= md.getColumnCount(); i++)
114                    System.out.print(md.getColumnName(i) + "\t\t");
115                System.out.println();
116
117                while (rs.next()) {
118                    for (int i = 1; i <= md.getColumnCount(); i++) {
119                        System.out.print(rs.getString(i) + "\t\t");
120                    }
121                    System.out.println();
122                }
123            } finally {
124                rs.close(); // rs can't be null according to the docs
125            }
126        } finally {
127            ps.close();
128        }
129
130    }
131    private static void initDB() throws SQLException {
132        Statement s = conn.createStatement();
133        try{
134            s.execute("CREATE TABLE IF NOT EXISTS flats (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, city VARCHAR(20) NOT NULL, " +
135                    "address VARCHAR (30) NOT NULL, area FLOAT(10,2), room_count INTEGER, price FLOAT (15,3))");
136        } finally {
137            s.close();
138        }
139
140
141    }
142
143    private static void addFlat(Scanner scan) throws SQLException {
144        System.out.print("Введите город: ");
145        String city = scan.nextLine();
146        System.out.print("Введите адрес (улицу, номер дома, номер квартиры): ");
147        String address = scan.nextLine();
148        System.out.print("Введите площадь квартиры: ");
149        String sArea = scan.nextLine();
150        Float area = Float.parseFloat(sArea);
151        System.out.print("Введите кол-во комнат: ");
152        String sRooms = scan.nextLine();
153        int rooms = Integer.parseInt(sRooms);
154        System.out.print("Введите цену: ");
155        String sPrice = scan.nextLine();
156        Float price = Float.parseFloat(sPrice);
157
158        String sql = "INSERT into flats (city, address, area, room_count, price) VALUES (?,?,?,?,?)";
159        PreparedStatement ps = conn.prepareStatement(sql);
160        try {
161            ps.setString(1,city);
162            ps.setString(2,address);
163            ps.setFloat(3,area);
164            ps.setInt(4,rooms);
165            ps.setFloat(5, price);
166            System.out.println("Вставлено "+ ps.executeUpdate()+ " записей");
167        } finally {
168            ps.close();
169        }
170
171
172    }
173}