· 10 years ago · May 23, 2016, 02:42 PM
1package ua.kiev.prog;
2
3import java.sql.*;
4import java.util.Random;
5import java.util.Scanner;
6
7public class Main {
8 static final String DB_CONNECTION = "jdbc:mysql://localhost:3306/flats";
9 static final String DB_USER = "Server";
10 static final String DB_PASSWORD = "12345";
11
12 static Connection conn;
13
14 public static void main(String[] args) {
15 Scanner sc = new Scanner(System.in);
16 try {
17 try {
18 // create connection
19 conn = DriverManager.getConnection(DB_CONNECTION, DB_USER, DB_PASSWORD);
20 initDB();
21
22 while (true) {
23 System.out.println("1: add client");
24 System.out.println("2: add random clients");
25 System.out.println("3: delete client");
26 System.out.println("4: change client");
27 System.out.println("5: view clients");
28 System.out.println("6: view clients of filter");
29 System.out.print("-> ");
30
31 String s = sc.nextLine();
32 switch (s) {
33 case "1":
34 addClient(sc);
35 break;
36 case "2":
37 insertRandomClients(sc);
38 break;
39 case "3":
40 deleteClient(sc);
41 break;
42 case "4":
43 changeClient(sc);
44 break;
45 case "5":
46 viewClients();
47 break;
48 case "6":
49 viewClientsFilter(sc);
50 break;
51 default:
52 return;
53 }
54 }
55 } finally {
56 sc.close();
57 if (conn != null) conn.close();
58 }
59 } catch (SQLException ex) {
60 ex.printStackTrace();
61 return;
62 }
63 }
64
65 private static void initDB() throws SQLException {
66 Statement st = conn.createStatement();
67 try {
68 st.execute("DROP TABLE IF EXISTS flats");
69 //st.execute("CREATE TABLE Clients (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(20) NOT NULL, age INT)");
70 st.execute("CREATE TABLE flats (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, district VARCHAR(20) NOT NULL, address VARCHAR(50) NOT NULL, square INT NOT NULL, quantityRooms INT NOT NULL, price INT NOT NULL)");
71 } finally {
72 st.close();
73 }
74 }
75
76 private static void addClient(Scanner sc) throws SQLException {
77 System.out.print("Enter district: ");
78 String district = sc.nextLine();
79
80 System.out.print("Enter client address: ");
81 String address = sc.nextLine();
82
83 System.out.print("Enter client square:");
84 String sSquare = sc.nextLine();
85 int square = Integer.parseInt(sSquare);
86
87 System.out.print("Enter client quantityRooms:");
88 String sQnt = sc.nextLine();
89 int quantityRooms = Integer.parseInt(sQnt);
90
91 System.out.print("Enter client price:");
92 String sPrice = sc.nextLine();
93 int price = Integer.parseInt(sPrice);
94
95
96 PreparedStatement ps = conn.prepareStatement("INSERT INTO flats (district, address, square, quantityRooms, price) VALUES(?, ?, ?, ?, ?)");
97 try {
98 ps.setString(1, district);
99 ps.setString(2, address);
100 ps.setInt(3, square);
101 ps.setInt(4, quantityRooms);
102 ps.setInt(5, price);
103 ps.executeUpdate(); // for INSERT, UPDATE & DELETE
104 } finally {
105 ps.close();
106 }
107 }
108
109 private static void deleteClient(Scanner sc) throws SQLException {
110 System.out.print("Enter flat id: ");
111 String name = sc.nextLine();
112
113 PreparedStatement ps = conn.prepareStatement("DELETE FROM flats WHERE id = ?");
114 try {
115 ps.setString(1, name);
116 ps.executeUpdate(); // for INSERT, UPDATE & DELETE
117 } finally {
118 ps.close();
119 }
120 }
121
122 private static void changeClient(Scanner sc) throws SQLException {
123 System.out.print("Enter flat id: ");
124 String name = sc.nextLine();
125 System.out.print("Enter district: ");
126 String district = sc.nextLine();
127
128 System.out.print("Enter client address: ");
129 String address = sc.nextLine();
130
131 System.out.print("Enter client square:");
132 String sSquare = sc.nextLine();
133 int square = Integer.parseInt(sSquare);
134
135 System.out.print("Enter client quantityRooms:");
136 String sQnt = sc.nextLine();
137 int quantityRooms = Integer.parseInt(sQnt);
138
139 System.out.print("Enter client price:");
140 String sPrice = sc.nextLine();
141 int price = Integer.parseInt(sPrice);
142
143 PreparedStatement ps = conn.prepareStatement("UPDATE district, address, square, quantityRooms, price WHERE id = ?");
144 try {
145 ps.executeUpdate(); // for INSERT, UPDATE & DELETE
146 } finally {
147 ps.close();
148 }
149 }
150
151 private static void insertRandomClients(Scanner sc) throws SQLException {
152 System.out.print("Enter flats count: ");
153 String sCount = sc.nextLine();
154 int count = Integer.parseInt(sCount);
155 Random rnd = new Random();
156
157 conn.setAutoCommit(false); // enable transactions
158 try {
159 try {
160 PreparedStatement ps = conn.prepareStatement("INSERT INTO flats (district, address, square, quantityRooms, price) VALUES(?, ?, ?, ?, ?)");
161 try {
162 for (int i = 0; i < count; i++) {
163 ps.setInt(2, rnd.nextInt(100));
164 ps.setString(1, "district" + i);
165 ps.setString(2, "address" + i);
166 ps.setInt(3, rnd.nextInt(100));
167 ps.setInt(4, rnd.nextInt(5));
168 ps.setInt(5, rnd.nextInt(100000));
169 ps.executeUpdate();
170 }
171 conn.commit();
172 } finally {
173 ps.close();
174 }
175 } catch (Exception ex) {
176 conn.rollback();
177 }
178 } finally {
179 conn.setAutoCommit(true); // return to default mode
180 }
181 }
182
183 private static void viewClients() throws SQLException {
184 PreparedStatement ps = conn.prepareStatement("SELECT * FROM flats");
185 try {
186 // table of data representing a database result set,
187 ResultSet rs = ps.executeQuery();
188 try {
189 // can be used to get information about the types and properties of the columns in a ResultSet object
190 ResultSetMetaData md = rs.getMetaData();
191
192 for (int i = 1; i <= md.getColumnCount(); i++)
193 System.out.print(md.getColumnName(i) + "\t\t\t");
194 System.out.println();
195
196 while (rs.next()) {
197 for (int i = 1; i <= md.getColumnCount(); i++) {
198 System.out.print(rs.getString(i) + "\t\t\t");
199 }
200 System.out.println();
201 }
202 } finally {
203 rs.close(); // rs can't be null according to the docs
204 }
205 } finally {
206 ps.close();
207 }
208 }
209
210 private static void viewClientsFilter(Scanner sc) throws SQLException {
211
212 System.out.print("Enter low price:");
213 String lPrice = sc.nextLine();
214 int lP = Integer.parseInt(lPrice);
215 System.out.print("Enter high price:");
216 String hPrice = sc.nextLine();
217 int hP = Integer.parseInt(hPrice);
218
219 PreparedStatement ps = conn.prepareStatement("SELECT * FROM flats WHERE (price>? AND price<?)");
220 try {
221 ps.setInt(1, lP);
222 ps.setInt(2, hP);
223 // table of data representing a database result set,
224 ResultSet rs = ps.executeQuery();
225 try {
226 // can be used to get information about the types and properties of the columns in a ResultSet object
227 ResultSetMetaData md = rs.getMetaData();
228
229 for (int i = 1; i <= md.getColumnCount(); i++)
230 System.out.print(md.getColumnName(i) + "\t\t\t");
231 System.out.println();
232
233 while (rs.next()) {
234 for (int i = 1; i <= md.getColumnCount(); i++) {
235 System.out.print(rs.getString(i) + "\t\t\t");
236 }
237 System.out.println();
238 }
239 } finally {
240 rs.close(); // rs can't be null according to the docs
241 }
242 } finally {
243 ps.close();
244 }
245 }
246}