8WHvx4TU

1package app;
2
3import java.sql.*;
4import java.util.Scanner;
5/**2. Спроектировать базу «Квартиры». Каждая запись 
6*в базе содержит данные о квартире (район, 
7*адрес, площадь, кол. комнат, цена). Сделать 
8*возможность выборки квартир из списка по 
9параметрам.
10*/
11/**
12 * Created by Aleksey on 06.08.2016.
13 */
14public class Main {
15    static final String DB_CONNECTION = "jdbc:mysql://localhost:3306/flatdb";
16    static final String DB_USER = "root";
17    static final String DB_PASSWORD = "eniva1989";
18
19    static Connection conn;
20    public static void main(String[] args) {
21        Scanner sc = new Scanner(System.in);
22        try {
23            try {
24                // create connection
25                conn = DriverManager.getConnection(DB_CONNECTION, DB_USER, DB_PASSWORD);
26                initDB();
27
28                while (true) {
29                    System.out.println("1: add apartment");
30                    System.out.println("2: delete apartment");
31                    System.out.println("3: change apartment");
32                    System.out.println("4: view apartments");
33                    System.out.println("5: view apartments for parameters");
34                    System.out.print("-> ");
35
36                    String s = sc.nextLine();
37                    switch (s) {
38                        case "1":
39                            addApartment(sc);
40                            break;
41                        case "2":
42                            deleteApartment(sc);
43                            break;
44                        case "3":
45                            changeApartment(sc);
46                            break;
47                        case "4":
48                            viewApartments();
49                            break;
50                        case "5":
51                            viewApartmentsForParameters(sc);
52                            break;
53                        default:
54                            return;
55                    }
56                }
57            } finally {
58                sc.close();
59                if (conn != null) conn.close();
60            }
61        } catch (SQLException ex) {
62            ex.printStackTrace();
63        }
64
65
66    }
67
68    private static void initDB() throws SQLException {
69        Statement st = conn.createStatement();
70        try {
71            st.execute("DROP TABLE IF EXISTS Apartments");
72            st.execute("CREATE TABLE Apartments (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, district VARCHAR(20) NOT NULL," +
73                    "address VARCHAR(20) NOT NULL, area DOUBLE, countRooms INT, price DOUBLE)");
74        } finally {
75            st.close();
76        }
77    }
78
79    private static void addApartment(Scanner sc) throws SQLException {
80        System.out.print("Enter apartment district: ");
81        String district = sc.nextLine();
82        System.out.print("Enter apartment address: ");
83        String address = sc.nextLine();
84        System.out.print("Enter apartment area(m^2): ");
85        String sArea = sc.nextLine();
86        double area = Double.parseDouble(sArea);
87        System.out.print("Enter apartment countRooms: ");
88        String sCountRooms = sc.nextLine();
89        int countRooms = Integer.parseInt(sCountRooms);
90        System.out.print("Enter apartment price($): ");
91        String sPrice = sc.nextLine();
92        double price = Double.parseDouble(sPrice);
93
94        PreparedStatement ps = conn.prepareStatement("INSERT INTO Apartments (district, address, area, countRooms, price)" +
95                " VALUES(?, ?, ?, ?, ?)");
96        try {
97            ps.setString(1, district);
98            ps.setString(2, address);
99            ps.setDouble(3, area);
100            ps.setInt(4, countRooms);
101            ps.setDouble(5, price);
102            ps.executeUpdate();
103        } finally {
104            ps.close();
105        }
106    }
107
108    private static void deleteApartment(Scanner sc) throws SQLException {
109
110        System.out.println("Select the option for which will remove");
111        System.out.println("1: id");
112        System.out.println("2: district");
113        System.out.println("3: address");
114        System.out.println("4: area");
115        System.out.println("5: countRooms");
116        System.out.println("6: price");
117        System.out.print("-> ");
118
119        String s = sc.nextLine();
120        switch (s) {
121
122            case "1":
123                System.out.print("Enter apartment id: ");
124                String sId = sc.nextLine();
125                int id = Integer.parseInt(sId);
126
127                PreparedStatement ps = conn.prepareStatement("DELETE FROM Apartments WHERE id = ?");
128                try {
129                    ps.setInt(1, id);
130                    ps.executeUpdate();
131                } finally {
132                    ps.close();
133                }
134                break;
135            case "2":
136                System.out.print("Enter apartment district: ");
137                String district = sc.nextLine();
138
139                ps = conn.prepareStatement("DELETE FROM Apartments WHERE district = ?");
140                try {
141                    ps.setString(1, district);
142                    ps.executeUpdate();
143
144                } finally {
145                    ps.close();
146                }
147                break;
148            case "3":
149                System.out.print("Enter apartment address: ");
150                String address = sc.nextLine();
151
152                ps = conn.prepareStatement("DELETE FROM Apartments WHERE address = ?");
153                try {
154                    ps.setString(1, address);
155                    ps.executeUpdate();
156                } finally {
157                    ps.close();
158                }
159                break;
160            case "4":
161                System.out.print("Enter apartment area(m^2): ");
162                String sArea = sc.nextLine();
163                Double area = Double.parseDouble(sArea);
164
165                ps = conn.prepareStatement("DELETE FROM Apartments WHERE area = ?");
166                try {
167                    ps.setDouble(1, area);
168                    ps.executeUpdate();
169                } finally {
170                    ps.close();
171                }
172                break;
173            case "5":
174                System.out.print("Enter apartment countRooms: ");
175                String sCountRooms = sc.nextLine();
176                int countRooms = Integer.parseInt(sCountRooms);
177
178                ps = conn.prepareStatement("DELETE FROM Apartments WHERE countRooms = ?");
179                try {
180                    ps.setInt(1, countRooms);
181                    ps.executeUpdate();
182                } finally {
183                    ps.close();
184                }
185                break;
186            case "6":
187                System.out.print("Enter apartment price($): ");
188                String sPrice = sc.nextLine();
189                Double price = Double.parseDouble(sPrice);
190
191                ps = conn.prepareStatement("DELETE FROM Apartments WHERE price = ?");
192                try {
193                    ps.setDouble(1, price);
194                    ps.executeUpdate();
195                } finally {
196                    ps.close();
197                }
198                break;
199            default:
200                return;
201        }
202    }
203
204    private static void changeApartment(Scanner sc) throws SQLException {
205        System.out.print("Enter apartment id: ");
206        String sId = sc.nextLine();
207        int id = Integer.parseInt(sId);
208
209        System.out.println("Select the option you want to change");
210        System.out.println("1: district");
211        System.out.println("2: address");
212        System.out.println("3: area");
213        System.out.println("4: countRooms");
214        System.out.println("5: price");
215        System.out.print("-> ");
216
217        String s = sc.nextLine();
218        switch (s) {
219
220            case "1":
221                System.out.print("Enter apartment district: ");
222                String district = sc.nextLine();
223
224                PreparedStatement ps = conn.prepareStatement("UPDATE Apartments SET district = ? WHERE id = ?");
225                try {
226                    ps.setString(1, district);
227                    ps.setInt(2, id);
228                    ps.executeUpdate();
229                } finally {
230                    ps.close();
231                }
232                break;
233            case "2":
234                System.out.print("Enter apartment address: ");
235                String address = sc.nextLine();
236
237                ps = conn.prepareStatement("UPDATE Apartments SET address = ? WHERE id = ?");
238                try {
239                    ps.setString(1, address);
240                    ps.setInt(2, id);
241                    ps.executeUpdate();
242                } finally {
243                    ps.close();
244                }
245                break;
246            case "3":
247                System.out.print("Enter apartment area(m^2): ");
248                String sArea = sc.nextLine();
249                Double area = Double.parseDouble(sArea);
250
251                ps = conn.prepareStatement("UPDATE Apartments SET area = ? WHERE id = ?");
252                try {
253                    ps.setDouble(1, area);
254                    ps.setInt(2, id);
255                    ps.executeUpdate();
256                } finally {
257                    ps.close();
258                }
259                break;
260            case "4":
261                System.out.print("Enter apartment countRooms: ");
262                String sCountRooms = sc.nextLine();
263                int countRooms = Integer.parseInt(sCountRooms);
264
265                ps = conn.prepareStatement("UPDATE Apartments SET countRooms = ? WHERE id = ?");
266                try {
267                    ps.setInt(1, countRooms);
268                    ps.setInt(2, id);
269                    ps.executeUpdate();
270                } finally {
271                    ps.close();
272                }
273                break;
274            case "5":
275                System.out.print("Enter apartment price($): ");
276                String sPrice = sc.nextLine();
277                Double price = Double.parseDouble(sPrice);
278
279                ps = conn.prepareStatement("UPDATE Apartments SET price = ? WHERE id = ?");
280                try {
281                    ps.setDouble(1, price);
282                    ps.setInt(2, id);
283                    ps.executeUpdate();
284                } finally {
285                    ps.close();
286                }
287                break;
288            default:
289                return;
290        }
291    }
292
293    private static void viewApartments() throws SQLException {
294        viewApartments("SELECT * FROM Apartments;");
295    }
296
297    private static void viewApartments(String str) throws SQLException {
298        PreparedStatement ps = conn.prepareStatement(str);
299        try {
300            ResultSet rs = ps.executeQuery();
301            try {
302                ResultSetMetaData md = rs.getMetaData();
303
304                for (int i = 1; i <= md.getColumnCount(); i++)
305                    System.out.print(md.getColumnName(i) + "\t\t");
306                System.out.println();
307
308                while (rs.next()) {
309                    for (int i = 1; i <= md.getColumnCount(); i++) {
310                        System.out.print(rs.getString(i) + "\t\t");
311                    }
312                    System.out.println();
313                }
314            } finally {
315                rs.close();
316            }
317        } finally {
318            ps.close();
319        }
320    }
321
322    private static void viewApartmentsForParameters(Scanner sc) throws SQLException {
323
324        System.out.println("Select the option by which will search");
325        System.out.println("1: district");
326        System.out.println("2: address");
327        System.out.println("3: area");
328        System.out.println("4: countRooms");
329        System.out.println("5: price");
330        System.out.print("-> ");
331
332        String s = sc.nextLine();
333        switch (s) {
334
335            case "1":
336                System.out.print("Enter apartment district: ");
337                String district = sc.nextLine();
338
339                String text = "SELECT * FROM Apartments WHERE district = " + district + ";";
340                viewApartments(text);
341
342                break;
343            case "2":
344                System.out.print("Enter apartment address: ");
345                String address = sc.nextLine();
346
347                text = "SELECT * FROM Apartments WHERE address = " + address + ";";
348                viewApartments(text);
349
350                break;
351            case "3":
352                System.out.print("Enter apartment area(m^2): ");
353                String sArea = sc.nextLine();
354                Double area = Double.parseDouble(sArea);
355
356                text = "SELECT * FROM Apartments WHERE area = " + area + ";";
357                viewApartments(text);
358
359                break;
360            case "4":
361                System.out.print("Enter apartment countRooms: ");
362                String sCountRooms = sc.nextLine();
363                int countRooms = Integer.parseInt(sCountRooms);
364
365                text = "SELECT * FROM Apartments WHERE countRooms = " + countRooms + ";";
366                viewApartments(text);
367
368                break;
369            case "5":
370                System.out.print("Enter apartment price($): ");
371                String sPrice = sc.nextLine();
372                Double price = Double.parseDouble(sPrice);
373
374                text = "SELECT * FROM Apartments WHERE price = " + price + ";";
375                viewApartments(text);
376
377                break;
378            default:
379                return;
380        }
381
382    }
383
384}