· 9 years ago · Nov 30, 2016, 05:30 PM
1public class Database {
2
3 public static final String URL_DATABASE = "jdbc:sqlite:test2.db";
4
5 public static final String TABLE_MASTERS = "MASTERS";
6 public static final String TABLE_PHONE = "PHONE";
7 public static final String TABLE_PRICE_LIST = "PRICE_LIST";
8 public static final String TABLE_APPLICATION = "APPLICATION";
9
10 private static ArrayList<String> listSurnameForApplication = new ArrayList<>();
11 private static ArrayList<String> listSurnameForPriceList = new ArrayList<>();
12 private static ArrayList<String> listPhoneForApplication = new ArrayList<>();
13 private static ArrayList<String> listProblemForApplication = new ArrayList<>();
14
15 public static final int COLUMN_1 = 1;
16 public static final int COLUMN_2 = 2;
17 public static final int COLUMN_3 = 3;
18 public static final int COLUMN_4 = 4;
19 public static final int COLUMN_5 = 5;
20 public static final int COLUMN_6 = 6;
21 public static final int COLUMN_7 = 7;
22 public static final int COLUMN_8 = 8;
23 public static final int COLUMN_9 = 9;
24 public static final int COLUMN_10 = 10;
25 public static final int COLUMN_11 = 11;
26
27 public static final String NAME = "NAME";
28 public static final String SURNAME = "SURNAME";
29 public static final String MIDDLE_NAME = "MIDDLE_NAME";
30 public static final String SPECIALIZATION = "SPECIALIZATION";
31 public static final String COMPANY = "COMPANY";
32 public static final String MODEL = "MODEL";
33 public static final String ARTICLE = "ARTICLE";
34 public static final String PRICE = "PRICE";
35 public static final String MASTER = "MASTER";
36 public static final String PHONE = "PHONE";
37 public static final String DATE = "DATE";
38 public static final String EMAIL = "EMAIL";
39 public static final String NUMBER_PHONE = "NUMBER_PHONE";
40 public static final String PROBLEM = "PROBLEM";
41 public static final String IMEI = "IMEI";
42 public static final String ID = "ID";
43
44 public static void connectDatabase() {
45 Connection connection = null;
46 try {
47 Class.forName("org.sqlite.JDBC");
48 connection = DriverManager.getConnection(URL_DATABASE);
49 connection.close();
50 } catch (Exception e) { System.err.println(e.getClass().getName() + ": " + e.getMessage()); }
51 System.out.println("Opened database successfully");
52 }
53
54 public static void creatTables(){
55 creatTableScript(CREATE_TABLE_MASTER_SCRIPT);
56 creatTableScript(CREATE_TABLE_PHONE_SCRIPT);
57 creatTableScript(CREATE_TABLE_PRICE_LIST_SCRIPT);
58 creatTableScript(CREATE_TABLE_APPLICATION_SCRIPT);
59 }
60
61 private static void creatTableScript (String s){
62 try {
63 Connection connection = DriverManager.getConnection(URL_DATABASE);
64 Statement statement = connection.createStatement();
65 statement.executeUpdate(s);
66 statement.close();
67 connection.close();
68 System.out.println("Database created");
69 } catch (SQLException e) { System.err.println("Error creating tables"); }
70 }
71
72
73 private static final String CREATE_TABLE_MASTER_SCRIPT =
74 "CREATE TABLE IF NOT EXISTS " + TABLE_MASTERS + " " +
75 "(ID INTEGER PRIMARY KEY AUTOINCREMENT," +
76 " NAME TEXT NOT NULL, " +
77 " SURNAME TEXT NOT NULL, " +
78 " SPECIALIZATION TEXT NOT NULL)";
79
80 private static final String CREATE_TABLE_PHONE_SCRIPT =
81 "CREATE TABLE IF NOT EXISTS " + TABLE_PHONE + " " +
82 "(ID INTEGER PRIMARY KEY AUTOINCREMENT," +
83 " COMPANY TEXT NOT NULL, " +
84 " MODEL TEXT NOT NULL)";
85
86 private static final String CREATE_TABLE_PRICE_LIST_SCRIPT =
87 "CREATE TABLE IF NOT EXISTS " + TABLE_PRICE_LIST + " " +
88 "(ID INTEGER PRIMARY KEY AUTOINCREMENT," +
89 " ARTICLE TEXT NOT NULL, " +
90 " PRICE INT NOT NULL, " +
91 " MASTER TEXT NOT NULL)";
92
93 private static final String CREATE_TABLE_APPLICATION_SCRIPT =
94 "CREATE TABLE IF NOT EXISTS " + TABLE_APPLICATION + " " +
95 "(ID INTEGER PRIMARY KEY AUTOINCREMENT," +
96 " NAME TEXT NOT NULL, " +
97 " MIDDLE_NAME TEXT NOT NULL, " +
98 " SURNAME TEXT NOT NULL, " +
99 " MASTER TEXT NOT NULL, " +
100 " PHONE TEXT NOT NULL, " +
101 " DATE TEXT NOT NULL, " +
102 " EMAIL TEXT NOT NULL, " +
103 " NUMBER_PHONE TEXT NOT NULL, " +
104 " PROBLEM TEXT NOT NULL, " +
105 " IMEI TEXT NOT NULL)";
106
107
108 public static void setDataMaster(String NAME, String SURNAME, String SPECIALIZATION){
109 try {
110 Connection connection = DriverManager.getConnection(URL_DATABASE);
111 Statement statement = connection.createStatement();
112 String script = "INSERT INTO " + TABLE_MASTERS + " (" + Database.NAME +
113 ", " + Database.SURNAME +
114 ", " + Database.SPECIALIZATION + ") " +
115 "VALUES (\'" +
116 NAME + "\',\'" +
117 SURNAME + "\',\'" +
118 SPECIALIZATION + "\');";
119 statement.executeUpdate(script);
120 statement.close();
121 connection.close();
122 } catch (SQLException e) { System.err.println("Error adding in MASTERS TABLE"); }
123 }
124
125 public static void setDataPhone(String COMPANY, String MODEL){
126 try {
127 Connection connection = DriverManager.getConnection(URL_DATABASE);
128 Statement statement = connection.createStatement();
129 String script = "INSERT INTO " + TABLE_PHONE + " (" + Database.COMPANY +
130 ", " + Database.MODEL + ") " +
131 "VALUES (\'" +
132 COMPANY + "\',\'" +
133 MODEL + "\');";
134 statement.executeUpdate(script);
135 statement.close();
136 connection.close();
137 } catch (SQLException e) { System.err.println("Error adding in PHONE TABLE"); }
138 }
139
140 public static void setDataPriceList(String ARTICLE, int PRICE, String MASTER){
141 try {
142 Connection connection = DriverManager.getConnection(URL_DATABASE);
143 Statement statement = connection.createStatement();
144 String script = "INSERT INTO " + TABLE_PRICE_LIST + " (" + Database.ARTICLE +
145 ", " + Database.PRICE +
146 ", " + Database.MASTER + ") " +
147 "VALUES (\'" +
148 ARTICLE + "\',\'" +
149 PRICE + "\',\'" +
150 MASTER + "\');";
151 statement.executeUpdate(script);
152 statement.close();
153 connection.close();
154 } catch (SQLException e) { System.err.println("Error adding in PRICE LIST TABLE"); }
155 }
156
157 public static void setDataApplication(String NAME, String MIDDLE_NAME, String SURNAME,
158 String MASTER, String PHONE, String DATE,
159 String EMAIL, String NUMBER_PHONE, String PROBLEM,
160 String IMEI){
161 try {
162 Connection connection = DriverManager.getConnection(URL_DATABASE);
163 Statement statement = connection.createStatement();
164 String script = "INSERT INTO " + TABLE_APPLICATION + " (" + Database.NAME +
165 ", " + Database.MIDDLE_NAME +
166 ", " + Database.SURNAME +
167 ", " + Database.MASTER +
168 ", " + Database.PHONE +
169 ", " + Database.DATE +
170 ", " + Database.EMAIL +
171 ", " + Database.NUMBER_PHONE +
172 ", " + Database.PROBLEM +
173 ", " + Database.IMEI + ") " +
174 "VALUES (\'" +
175 NAME + "\',\'" +
176 MIDDLE_NAME + "\',\'" +
177 SURNAME + "\',\'" +
178 MASTER + "\',\'" +
179 PHONE + "\',\'" +
180 DATE + "\',\'" +
181 EMAIL + "\',\'" +
182 NUMBER_PHONE + "\',\'" +
183 PROBLEM + "\',\'" +
184 IMEI + "\');";
185 statement.executeUpdate(script);
186 statement.close();
187 connection.close();
188 } catch (SQLException e) { System.err.println("Error adding in APPLICATION TABLE"); }
189 }
190
191 public static void deleteRowFromTable(String table, int id){
192 try {
193 Connection connection = DriverManager.getConnection(Database.URL_DATABASE);
194 Statement statement = connection.createStatement();
195 statement.executeUpdate("DELETE FROM " + table +
196 " WHERE ID = " + id);
197 statement.close();
198 connection.close();
199 } catch (SQLException ex) { System.err.println("Error deleting from list " + table); }
200 }
201
202 /* Методы Ñоздают ÑпиÑки объектов
203 Ð´Ð»Ñ Ð·Ð°Ð¿Ð¾Ð»Ð½ÐµÐ½Ð¸Ñ Ñ‚Ð°Ð±Ð»Ð¸Ñ† в оÑновных вкладках
204 "ОбращениÑ", "СпециалиÑÑ‚Ñ‹", "Телефоны", "ÐŸÑ€Ð°Ð¹Ñ Ð›Ð¸ÑÑ‚" */
205
206 public static ArrayList<Application> getApplicationForTable(){
207 ArrayList<Application> list = new ArrayList<>();
208 try {
209 Connection connection = DriverManager.getConnection(Database.URL_DATABASE);
210 Statement statement = connection.createStatement();
211 ResultSet resultSet = statement.executeQuery("SELECT ID, " +
212 Database.NAME + ", " +
213 Database.MIDDLE_NAME + ", " +
214 Database.SURNAME + ", " +
215 Database.MASTER + ", " +
216 Database.PHONE + ", " +
217 Database.DATE + ", " +
218 Database.EMAIL + ", " +
219 Database.NUMBER_PHONE + ", " +
220 Database.PROBLEM + ", " +
221 Database.IMEI +
222 " FROM " +
223 Database.TABLE_APPLICATION);
224 while (resultSet.next()) {
225 int id = resultSet.getInt(COLUMN_1);
226 String name = resultSet.getString(COLUMN_2);
227 String middle_name = resultSet.getString(COLUMN_3);
228 String surname = resultSet.getString(COLUMN_4);
229 String master = resultSet.getString(COLUMN_5);
230 String phone = resultSet.getString(COLUMN_6);
231 String date = resultSet.getString(COLUMN_7);
232 String email = resultSet.getString(COLUMN_8);
233 String number_phone = resultSet.getString(COLUMN_9);
234 String problem = resultSet.getString(COLUMN_10);
235 String imei = resultSet.getString(COLUMN_11);
236
237 System.out.println(id + " " + name + " " + middle_name + " " + surname +
238 " " + master + " " + phone + " " + date +
239 " " + email + " " + number_phone + " " + problem + " " + imei);
240 list.add(new Application(id, name, middle_name, surname, master,
241 phone, date, email, number_phone,
242 problem, imei));
243 }
244 statement.close();
245 connection.close();
246 } catch (SQLException e) { System.err.println("Error following list Application"); }
247 return list;
248 }
249
250 public static ArrayList<Phones> getPhoneForTable() {
251 ArrayList<Phones> list = new ArrayList<>();
252 listPhoneForApplication.add("Выберете модель телефона");
253 try {
254 Connection connection = DriverManager.getConnection(Database.URL_DATABASE);
255 Statement statement = connection.createStatement();
256 ResultSet resultSet = statement.executeQuery("SELECT " +
257 Database.ID + ", " +
258 Database.COMPANY + ", " +
259 Database.MODEL +
260 " FROM " +
261 Database.TABLE_PHONE);
262 while (resultSet.next()) {
263 int id = resultSet.getInt(COLUMN_1);
264 String company = resultSet.getString(COLUMN_2);
265 String model = resultSet.getString(COLUMN_3);
266 System.out.println(id + " " + company + " " + model + " ");
267 list.add(new Phones(id, company, model));
268 listPhoneForApplication.add(company + " " + model);
269 }
270 statement.close();
271 connection.close();
272 } catch (SQLException e) { System.err.println("Error following list phones"); }
273 Gui.comboBoxForApplicationPHONE = listPhoneForApplication.toArray(new String[listPhoneForApplication.size()]);
274 return list;
275 }
276
277 public static ArrayList<Masters> getMastersForTable(){
278 ArrayList<Masters> list = new ArrayList<>();
279 listSurnameForApplication.add("Выберете ÑпециалиÑта");
280 listSurnameForPriceList.add("Выберете ÑпециалиÑта");
281 try {
282 Connection connection = DriverManager.getConnection(Database.URL_DATABASE);
283 Statement statement = connection.createStatement();
284 ResultSet resultSet = statement.executeQuery("SELECT " +
285 Database.ID + ", " +
286 Database.NAME + ", " +
287 Database.SURNAME + ", " +
288 Database.SPECIALIZATION +
289 " FROM " +
290 Database.TABLE_MASTERS);
291 while (resultSet.next()) {
292 int id = resultSet.getInt(COLUMN_1);
293 String name = resultSet.getString(COLUMN_2);
294 String surname = resultSet.getString(COLUMN_3);
295 String specialization = resultSet.getString(COLUMN_4);
296 System.out.println(id + " " + name + " " + surname + " " + specialization);
297 list.add(new Masters(id, name, surname, specialization));
298 listSurnameForPriceList.add(surname + " " + name + " " + specialization);
299 listSurnameForApplication.add(surname + " " + name + " " + specialization);
300 }
301 statement.close();
302 connection.close();
303 } catch (SQLException e) { System.err.println("Error following list masters"); }
304 Gui.comboBoxForPriceListMASTERS = listSurnameForPriceList.toArray(new String[listSurnameForPriceList.size()]);
305 Gui.comboBoxForApplicationMASTERS = listSurnameForApplication.toArray(new String[listSurnameForApplication.size()]);
306 return list;
307 }
308
309 public static ArrayList<PriceList> getPriceListForTable(){
310 ArrayList<PriceList> list = new ArrayList<>();
311 listProblemForApplication.add("Выберете проблему (поломку)");
312 try {
313 Connection connection = DriverManager.getConnection(Database.URL_DATABASE);
314 Statement statement = connection.createStatement();
315 ResultSet resultSet = statement.executeQuery("SELECT " +
316 Database.ID + ", " +
317 Database.ARTICLE + ", " +
318 Database.PRICE + ", " +
319 Database.MASTER +
320 " FROM " +
321 Database.TABLE_PRICE_LIST);
322 while (resultSet.next()) {
323 int id = resultSet.getInt(COLUMN_1);
324 String article = resultSet.getString(COLUMN_2);
325 int price = resultSet.getInt(COLUMN_3);
326 String master = resultSet.getString(COLUMN_4);
327 System.out.println(id + " " + article + " " + price + " " + master);
328 list.add(new PriceList(id, article, price, master));
329 listProblemForApplication.add(article);
330 }
331 statement.close();
332 connection.close();
333 } catch (SQLException e) { System.err.println("Error following list PriceList"); }
334 Gui.comboBoxForApplicationPROBLEM = listProblemForApplication.toArray(new String[listProblemForApplication.size()]);
335 return list;
336 }
337
338 /* Методы Ñоздают ÑпиÑок объектов Ð´Ð»Ñ Ð·Ð°Ð¿Ð¾Ð»Ð½ÐµÐ½Ð¸Ñ
339 таблиц в окне результатов поиÑка */
340
341 public static ArrayList<Phones> getPhoneForSearchTable(String s){
342 ArrayList<Phones> list = new ArrayList<>();
343 try {
344 Connection connection = DriverManager.getConnection(Database.URL_DATABASE);
345 Statement statement = connection.createStatement();
346 ResultSet resultSet = statement.executeQuery("SELECT * FROM " +
347 Database.TABLE_PHONE +
348 " WHERE " +
349 Database.MODEL +
350 " LIKE " +
351 "\'%" + s + "%\'");
352 while (resultSet.next()) {
353 int id = resultSet.getInt(COLUMN_1);
354 String company = resultSet.getString(COLUMN_2);
355 String model = resultSet.getString(COLUMN_3);
356 System.out.println(id + " " + company + " " + model + " ");
357 list.add(new Phones(id, company, model));
358 }
359 statement.close();
360 connection.close();
361 } catch (SQLException e) { System.err.println("Error search phone"); }
362 return list;
363 }
364
365 public static ArrayList<Masters> getMastersForSearchTable(String s){
366 ArrayList<Masters> list = new ArrayList<>();
367 try {
368 Connection connection = DriverManager.getConnection(Database.URL_DATABASE);
369 Statement statement = connection.createStatement();
370 ResultSet resultSet = statement.executeQuery("SELECT * FROM " +
371 Database.TABLE_MASTERS +
372 " WHERE " +
373 Database.SURNAME +
374 " LIKE " +
375 "\'%" + s + "%\'");
376 while (resultSet.next()) {
377 int id = resultSet.getInt(COLUMN_1);
378 String name = resultSet.getString(COLUMN_2);
379 String surname = resultSet.getString(COLUMN_3);
380 String specialization = resultSet.getString(COLUMN_4);
381 list.add(new Masters(id, name, surname, specialization));
382 }
383 statement.close();
384 connection.close();
385 } catch (SQLException e) { System.err.println("Error search masters"); }
386 return list;
387 }
388
389 public static ArrayList<PriceList> getPriceListForSearchTable(String s){
390
391 ArrayList<PriceList> list = new ArrayList<>();
392 try {
393 Connection connection = DriverManager.getConnection(Database.URL_DATABASE);
394 Statement statement = connection.createStatement();
395 ResultSet resultSet = statement.executeQuery("SELECT * FROM " +
396 Database.TABLE_PRICE_LIST +
397 " WHERE " +
398 Database.ARTICLE +
399 " LIKE " +
400 "\'%" + s + "%\'");
401 while (resultSet.next()) {
402 int id = resultSet.getInt(COLUMN_1);
403 String article = resultSet.getString(COLUMN_2);
404 int price = resultSet.getInt(COLUMN_3);
405 String master = resultSet.getString(COLUMN_4);
406 list.add(new PriceList(id, article, price, master));
407 }
408 statement.close();
409 connection.close();
410 } catch (SQLException e) { System.err.println("Error search PriceList"); }
411 return list;
412 }
413
414 public static ArrayList<Application> getApplicationForSearchTable(String s){
415 ArrayList<Application> list = new ArrayList<>();
416 try {
417 Connection connection = DriverManager.getConnection(Database.URL_DATABASE);
418 Statement statement = connection.createStatement();
419 ResultSet resultSet = statement.executeQuery("SELECT * FROM " +
420 Database.TABLE_APPLICATION +
421 " WHERE " +
422 Database.SURNAME +
423 " LIKE " +
424 "\'%" + s + "%\'");
425 while (resultSet.next()) {
426 int id = resultSet.getInt(COLUMN_1);
427 String name = resultSet.getString(COLUMN_2);
428 String middle_name = resultSet.getString(COLUMN_3);
429 String surname = resultSet.getString(COLUMN_4);
430 String master = resultSet.getString(COLUMN_5);
431 String phone = resultSet.getString(COLUMN_6);
432 String date = resultSet.getString(COLUMN_7);
433 String email = resultSet.getString(COLUMN_8);
434 String number_phone = resultSet.getString(COLUMN_9);
435 String problem = resultSet.getString(COLUMN_10);
436 String imei = resultSet.getString(COLUMN_11);
437
438 list.add(new Application(id, name, middle_name, surname, master,
439 phone, date, email, number_phone,
440 problem, imei));
441 }
442 statement.close();
443 connection.close();
444 } catch (SQLException e) { System.err.println("Error search list Application"); }
445 return list;
446 }
447
448}