· 9 years ago · Jun 19, 2017, 07:20 AM
1package database;
2
3import java.sql.Connection;
4import java.sql.DriverManager;
5import java.sql.ResultSet;
6import java.sql.SQLException;
7import java.sql.Statement;
8
9public class Apteka {
10 private Connection connection;
11 private Statement statement;
12
13 public Apteka() throws SQLException {
14 System.out.println("** TworzÄ™ aptekÄ™. ÅÄ…czenie z bazÄ…...**");
15 connect();
16 create();
17 delete();
18 insert();
19 select1();
20 select2();
21 select3();
22 update();
23 select3();
24 delete();
25 drop();
26 }
27
28 private void connect() throws SQLException {
29 connection = DriverManager.getConnection("jdbc:sqlite:C:/Dev/Eclipse/apteka.db");
30
31 if (!connection.isClosed()) {
32 System.out.println("## Znaleziono plik bazy!");
33 statement = connection.createStatement();
34
35 if (!statement.isClosed())
36 System.out.println("## Połączono z bazą!");
37
38 else System.out.println("## NIE UDAÅO SIĘ USTANOWIĆ POÅÄ„CZENIA!");
39 }
40
41 else System.out.println("## NIE ZNALEZIONO PLIKU BAZY!");
42 }
43
44 private void create() throws SQLException {
45 String create = "CREATE TABLE IF NOT EXISTS LEKARSTWO " +
46 "(ID INTEGER PRIMARY KEY NOT NULL, " +
47 "NAZWA TEXT NOT NULL, " +
48 "CENA INTEGER NOT NULL);";
49 statement.execute(create);
50
51 create = "CREATE TABLE IF NOT EXISTS KLIENT " +
52 "(ID INTEGER PRIMARY KEY NOT NULL, " +
53 "IMIE TEXT NOT NULL, " +
54 "NAZWISKO TEXT NOT NULL);";
55 statement.execute(create);
56
57 create = "CREATE TABLE IF NOT EXISTS ZAMOWIENIE " +
58 "(ID INTEGER PRIMARY KEY NOT NULL," +
59 "ILOSC INTEGER NOT NULL, " +
60 "ID_LEKARSTWA INTEGER NOT NULL, " +
61 "ID_KLIENTA INTEGER NOT NULL, " +
62 "FOREIGN KEY (ID_LEKARSTWA) REFERENCES LEKARSTWO(ID)," +
63 "FOREIGN KEY (ID_KLIENTA) REFERENCES KLIENT(ID));";
64 statement.execute(create);
65
66 System.out.println("## Baza została utworzona!");
67 }
68
69 private void delete() throws SQLException {
70 String delete = "DELETE FROM ZAMOWIENIE";
71 statement.execute(delete);
72
73 delete = "DELETE FROM LEKARSTWO";
74 statement.execute(delete);
75
76 delete = "DELETE FROM KLIENT";
77 statement.execute(delete);
78
79 System.out.println("## Baza została wyczyszczona!");
80 }
81
82 private void drop() throws SQLException {
83 String drop = "DROP TABLE ZAMOWIENIE";
84 statement.execute(drop);
85
86 drop= "DROP TABLE LEKARSTWO";
87 statement.execute(drop);
88
89 drop = "DROP TABLE KLIENT";
90 statement.execute(drop);
91
92 System.out.println("## Baza została usunięta!");
93 }
94
95 private void insert() throws SQLException {
96 String insert = "INSERT INTO LEKARSTWO VALUES(1, 'Srakolin Turbo Forte', 45);";
97 statement.executeUpdate(insert);
98
99 insert = "INSERT INTO LEKARSTWO VALUES(2, 'Eutanazol Max Plus', 99);";
100 statement.executeUpdate(insert);
101
102 insert = "INSERT INTO LEKARSTWO VALUES(3, 'APAP Noc', 12);";
103 statement.executeUpdate(insert);
104
105 insert = "INSERT INTO LEKARSTWO VALUES(4, 'APAP Lewa Noga', 14);";
106 statement.executeUpdate(insert);
107
108 insert = "INSERT INTO KLIENT VALUES(1, 'Darth', 'Vader');";
109 statement.executeUpdate(insert);
110
111 insert = "INSERT INTO KLIENT VALUES(2, 'Obi-Wan', 'Kenobi');";
112 statement.executeUpdate(insert);
113
114 insert = "INSERT INTO KLIENT VALUES(3, 'Jar-Jar', 'Binks');";
115 statement.executeUpdate(insert);
116
117 insert = "INSERT INTO KLIENT VALUES(4, 'Darth', 'Sidious');";
118 statement.executeUpdate(insert);
119
120 insert = "INSERT INTO ZAMOWIENIE VALUES(1, 3, 3, 1);";
121 statement.executeUpdate(insert);
122
123 insert = "INSERT INTO ZAMOWIENIE VALUES(2, 9, 2, 3);";
124 statement.executeUpdate(insert);
125
126 insert = "INSERT INTO ZAMOWIENIE VALUES(3, 2, 4, 3);";
127 statement.executeUpdate(insert);
128
129 insert = "INSERT INTO ZAMOWIENIE VALUES(4, 1, 1, 4);";
130 statement.executeUpdate(insert);
131
132 insert = "INSERT INTO ZAMOWIENIE VALUES(5, 5, 2, 4);";
133 statement.executeUpdate(insert);
134
135 insert = "INSERT INTO ZAMOWIENIE VALUES(6, 2, 3, 2);";
136 statement.executeUpdate(insert);
137
138 System.out.println("## Baza została uzupełniona!");
139 }
140
141 private void select1() throws SQLException {
142 System.out.println("## Zapytanie pierwsze (lista zamówień)");
143 String select = "SELECT KLIENT.IMIE, KLIENT.NAZWISKO, LEKARSTWO.NAZWA, ZAMOWIENIE.ILOSC " +
144 "FROM ZAMOWIENIE, KLIENT, LEKARSTWO " +
145 "WHERE ZAMOWIENIE.ID_KLIENTA = KLIENT.ID AND ZAMOWIENIE.ID_LEKARSTWA = LEKARSTWO.ID";
146 ResultSet result = statement.executeQuery(select);
147 while (result.next())
148 System.out.println("----------\n Imię: " + result.getString(1) + "\n Nazwisko: " + result.getString(2) + "\n Lekarstwo: " + result.getString(3) + "\n Ilość: " + result.getInt(4));
149 }
150
151 private void select2() throws SQLException {
152 System.out.println("## Zapytanie drugie (lista lekarstw)");
153 String select = "SELECT * FROM LEKARSTWO";
154 ResultSet result = statement.executeQuery(select);
155 while (result.next())
156 System.out.println("----------\n Nazwa: " + result.getString(2) + "\n Cena: " + result.getInt(3) + " zł");
157 }
158
159 private void select3() throws SQLException {
160 System.out.println("## Zapytanie trzecie (lista klientów)");
161 String select = "SELECT * FROM KLIENT";
162 ResultSet result = statement.executeQuery(select);
163 while (result.next())
164 System.out.println("----------\n ImiÄ™: " + result.getString(2) + "\n Nazwisko: " + result.getString(3));
165 }
166
167 private void update() throws SQLException {
168 String update = "UPDATE KLIENT SET NAZWISKO = 'Nikt go nie lubi' WHERE IMIE = 'Jar-Jar'";
169 statement.execute(update);
170 System.out.println("## Dokonano modyfikacji Jar Jara!");
171 }
172
173 private void deleteUnderline() {
174 // Ta metoda nic nie robi
175 // Po prostu wkurwia mnie podkreślenie nazwy obiektu
176 }
177
178 public static void main(String[] args) throws SQLException {
179 Apteka apteka = new Apteka();
180 apteka.deleteUnderline();
181 }
182
183}
184
185
186
187
188
189
190
191package database;
192
193import java.sql.Connection;
194import java.sql.DriverManager;
195import java.sql.ResultSet;
196import java.sql.SQLException;
197import java.sql.Statement;
198import java.util.Scanner;
199
200public class Hotel {
201 private static final String connectionURL = "jdbc:sqlite:C:/Dev/Eclipse/database.db";
202 private static Connection connection;
203 private static Statement statement;
204 private static Scanner scanner;
205
206 private static void connect() throws SQLException {
207 scanner = new Scanner(System.in);
208 connection = DriverManager.getConnection(connectionURL);
209 if (!connection.isClosed()) {
210 System.out.println("## Nawiązano połączenie z bazą!");
211
212 statement = connection.createStatement();
213 if (!statement.isClosed()) {
214 System.out.println("## Można teraz przeprowadzać operacje!");
215 showMenu();
216 }
217
218 else System.out.println("## Ale coś się popsuło...");
219 }
220
221 else {
222 System.out.println("## Nie udało się nawiązać połączenia z bazą...");
223 close();
224 }
225 }
226
227 private static void create() throws SQLException {
228 String create = "CREATE TABLE IF NOT EXISTS PRACOWNIK " +
229 "(ID INTEGER PRIMARY KEY NOT NULL," +
230 "IMIE TEXT NOT NULL," +
231 "NAZWISKO TEXT NOT NULL," +
232 "PENSJA INTEGER NOT NULL);";
233 statement.execute(create);
234
235 create = "CREATE TABLE IF NOT EXISTS KLIENT " +
236 "(ID INTEGER PRIMARY KEY NOT NULL," +
237 "IMIE TEXT NOT NULL," +
238 "NAZWISKO TEXT NOT NULL);";
239 statement.execute(create);
240
241 create = "CREATE TABLE IF NOT EXISTS HOTEL " +
242 "(ID INTEGER PRIMARY KEY NOT NULL," +
243 "KWOTA INTEGER NOT NULL," +
244 "ID_KLIENTA INTEGER NOT NULL," +
245 "ID_PRACOWNIKA INTEGER NOT NULL," +
246 "FOREIGN KEY (ID_KLIENTA) REFERENCES KLIENT(ID_KLIENTA)," +
247 "FOREIGN KEY (ID_PRACOWNIKA) REFERENCES PRACOWNIK(ID_PRACOWNIKA));";
248 statement.execute(create);
249
250 System.out.println("## Baza została utworzona!");
251 showMenu();
252 }
253
254 private static void drop() throws SQLException {
255 String drop = "DROP TABLE HOTEL";
256 statement.execute(drop);
257
258 drop = "DROP TABLE KLIENT";
259 statement.execute(drop);
260
261 drop = "DROP TABLE PRACOWNIK";
262 statement.execute(drop);
263
264 System.out.println("## Baza została usunięta!");
265 showMenu();
266 }
267
268 private static void delete() throws SQLException {
269 Statement statement = connection.createStatement();
270
271 String delete = "DELETE FROM HOTEL;";
272 statement.execute(delete);
273
274 delete = "DELETE FROM KLIENT;";
275 statement.execute(delete);
276
277 delete = "DELETE FROM PRACOWNIK;";
278 statement.execute(delete);
279
280 System.out.println("## Zawartość bazy została wyczyszczona!");
281 showMenu();
282 }
283
284 private static void insert() throws SQLException {
285 String insert = "INSERT INTO PRACOWNIK VALUES (1, 'Darth', 'Vader', 2580)";
286 statement.executeUpdate(insert);
287
288 insert = "INSERT INTO PRACOWNIK VALUES (2, 'Imperator', 'Palpatine', 6850)";
289 statement.executeUpdate(insert);
290
291 insert = "INSERT INTO PRACOWNIK VALUES (3, 'Jar Jar', 'Binks', 10)";
292 statement.executeUpdate(insert);
293
294 insert = "INSERT INTO KLIENT VALUES (1, 'Han', 'Solo')";
295 statement.executeUpdate(insert);
296
297 insert = "INSERT INTO KLIENT VALUES (2, 'Luke', 'Skywalker')";
298 statement.executeUpdate(insert);
299
300 insert = "INSERT INTO KLIENT VALUES (3, 'Leia', 'Organa')";
301 statement.executeUpdate(insert);
302
303 insert = "INSERT INTO HOTEL VALUES (1, 250, 2, 1)";
304 statement.executeUpdate(insert);
305
306 insert = "INSERT INTO HOTEL VALUES (2, 350, 3, 1)";
307 statement.executeUpdate(insert);
308
309 insert = "INSERT INTO HOTEL VALUES (3, 175, 1, 2)";
310 statement.executeUpdate(insert);
311
312 insert = "INSERT INTO HOTEL VALUES (4, 10, 1, 13)";
313 statement.executeUpdate(insert);
314
315 System.out.println("## Dodano zawartość do bazy!");
316 showMenu();
317 }
318
319 private static void select() throws SQLException {
320 String select = "SELECT KLIENT.IMIE, KLIENT.NAZWISKO, HOTEL.KWOTA, PRACOWNIK.NAZWISKO " +
321 "FROM HOTEL, KLIENT, PRACOWNIK " +
322 "WHERE HOTEL.ID_KLIENTA = KLIENT.ID AND HOTEL.ID_PRACOWNIKA = PRACOWNIK.ID";
323 ResultSet result = statement.executeQuery(select);
324 System.out.println("## ZAPYTANIE");
325 while (result.next()) {
326 System.out.println("Imię klienta: " + result.getString(1) + "\nNazwisko klienta: " + result.getString(2) + "\nZapłata: " + result.getInt(3) + "\nNazwisko pracownika: " + result.getString(4) + "\n");
327 }
328 }
329
330 private static void close() throws SQLException {
331 System.out.println("## Zamykam bazÄ™!");
332 statement.close();
333 connection.close();
334 System.exit(1);
335 }
336
337 private static void showMenu() throws SQLException {
338 System.out.println("\n1. Utwórz bazę | 2. Usuń bazę | 3. Wyczyść bazę | 4. Wprowadź dane | 5. Zapytaj | 0. Wyłącz program");
339 int choice = scanner.nextInt();
340
341 switch (choice) {
342 case 1:
343 create();
344 break;
345
346 case 2:
347 drop();
348 break;
349
350 case 3:
351 delete();
352 break;
353
354 case 4:
355 insert();
356 break;
357
358 case 5:
359 select();
360 break;
361
362 case 0:
363 close();
364 break;
365
366 default:
367 System.out.println("Podano nieprawidłowy numer!");
368 showMenu();
369 break;
370 }
371 }
372
373 public static void main(String[] args) throws Exception {
374 System.out.println("** To jest program do obsługi bazy danych **");
375 connect();
376 }
377
378}