· 7 years ago · Nov 21, 2018, 07:20 PM
1import java.io.BufferedReader;
2import java.io.IOException;
3import java.io.InputStreamReader;
4import java.sql.*;
5import java.util.Scanner;
6
7public class Main {
8 private static Connection connection= null;
9
10 public static void connectDataBase(){
11 try {
12 Class.forName("org.postgresql.Driver");
13
14 } catch (ClassNotFoundException e) {
15
16 System.out.println("Driver not found, check if the jar is reachable !");
17 e.printStackTrace();
18 return;
19
20 }
21 System.out.println("JDBC Driver funciona .. tentar a ligacao");
22 try {
23 connection = DriverManager.getConnection(
24 "jdbc:postgresql://127.0.0.1:5432/postgres",
25 "postgres",
26 "basedados");
27 } catch (SQLException e) {
28 System.out.println("Ligacao falhou.. erro:");
29 e.printStackTrace();
30 return;
31
32 }
33 if (connection != null) {
34 System.out.println("Ligação feita com sucessso");
35 } else {
36 System.out.println("Nao conseguimos estabelecer a ligacao");
37 }
38 }
39 public static void createTables(){
40 try {
41 Statement stmt;
42
43 if (connection.createStatement() == null) {
44 connection = DriverManager.getConnection(
45 "jdbc:postgresql://127.0.0.1:5432/postgres",
46 "postgres",
47 "basedados");
48 }
49
50 if ((stmt = connection.createStatement()) == null) {
51 System.out.println("Erro nao foi possÃvel criar uma statement ou retornou null");
52 System.exit(-1);
53 }
54 String createTables = "CREATE TABLE artista (\n" +
55 "\tn_artista\t BIGINT,\n" +
56 "\tnome\t\t VARCHAR(512) NOT NULL,\n" +
57 "\tperiodo\t VARCHAR(512) NOT NULL,\n" +
58 "\thistoria\t VARCHAR(512) NOT NULL,\n" +
59 "\talbum_n_album\t BIGINT NOT NULL,\n" +
60 "\tbanda_elementos VARCHAR(512),\n" +
61 "\tsolo_nome\t VARCHAR(512),\n" +
62 "\tPRIMARY KEY(n_artista)\n" +
63 ");\n" +
64 "\n" +
65 "CREATE TABLE album (\n" +
66 "\tn_album BIGINT,\n" +
67 "\tnome\t VARCHAR(512) NOT NULL,\n" +
68 "\tgenero\t VARCHAR(512) NOT NULL,\n" +
69 "\tdata\t VARCHAR(512) NOT NULL,\n" +
70 "\tPRIMARY KEY(n_album)\n" +
71 ");\n" +
72 "\n" +
73 "CREATE TABLE musica (\n" +
74 "\tn_musica\t BIGINT,\n" +
75 "\tnome\t\t VARCHAR(512) NOT NULL,\n" +
76 "\tletra\t VARCHAR(512) NOT NULL,\n" +
77 "\tduracao\t SMALLINT NOT NULL,\n" +
78 "\tcompositor\t VARCHAR(512) NOT NULL,\n" +
79 "\talbum_n_album BIGINT NOT NULL,\n" +
80 "\tPRIMARY KEY(n_musica)\n" +
81 ");\n" +
82 "\n" +
83 "CREATE TABLE playlist (\n" +
84 "\tn_playlist\t\t BIGINT,\n" +
85 "\tnome\t\t VARCHAR(512) NOT NULL,\n" +
86 "\tutilizador_username VARCHAR(512) NOT NULL,\n" +
87 "\tPRIMARY KEY(n_playlist)\n" +
88 ");\n" +
89 "\n" +
90 "CREATE TABLE utilizador (\n" +
91 "\tusername VARCHAR(512),\n" +
92 "\tpassword VARCHAR(512) NOT NULL,\n" +
93 "\tPRIMARY KEY(username)\n" +
94 ");\n" +
95 "\n" +
96 "CREATE TABLE editor (\n" +
97 "\tutilizador_username VARCHAR(512),\n" +
98 "\tPRIMARY KEY(utilizador_username)\n" +
99 ");\n" +
100 "\n" +
101 "CREATE TABLE nao_editor (\n" +
102 "\tutilizador_username VARCHAR(512),\n" +
103 "\tPRIMARY KEY(utilizador_username)\n" +
104 ");\n" +
105 "\n" +
106 "CREATE TABLE critica (\n" +
107 "\tpontuacao\t\t SMALLINT NOT NULL,\n" +
108 "\tcritica\t\t VARCHAR(512) NOT NULL,\n" +
109 "\talbum_n_album\t BIGINT,\n" +
110 "\tutilizador_username VARCHAR(512) NOT NULL,\n" +
111 "\tPRIMARY KEY(album_n_album)\n" +
112 ");\n" +
113 "\n" +
114 "CREATE TABLE concerto (\n" +
115 "\tn_concerto BIGINT,\n" +
116 "\tdata\t VARCHAR(512) NOT NULL,\n" +
117 "\tlocalidade VARCHAR(512) NOT NULL,\n" +
118 "\tPRIMARY KEY(n_concerto)\n" +
119 ");\n" +
120 "\n" +
121 "CREATE TABLE ficheiro (\n" +
122 "\tuploaded_file\t BYTEA NOT NULL,\n" +
123 "\tmusica_n_musica BIGINT,\n" +
124 "\tPRIMARY KEY(musica_n_musica)\n" +
125 ");\n" +
126 "\n" +
127 "CREATE TABLE editora (\n" +
128 "\tn_editora\t BIGINT,\n" +
129 "\tnome\t\t VARCHAR(512) NOT NULL,\n" +
130 "\tmusica_n_musica BIGINT NOT NULL,\n" +
131 "\tPRIMARY KEY(n_editora)\n" +
132 ");\n" +
133 "\n" +
134 "CREATE TABLE compositor (\n" +
135 "\tfield_0 BIGINT,\n" +
136 "\tPRIMARY KEY(field_0)\n" +
137 ");\n" +
138 "\n" +
139 "CREATE TABLE album_editora (\n" +
140 "\talbum_n_album\t BIGINT,\n" +
141 "\teditora_n_editora BIGINT,\n" +
142 "\tPRIMARY KEY(album_n_album,editora_n_editora)\n" +
143 ");\n" +
144 "\n" +
145 "CREATE TABLE playlist_musica (\n" +
146 "\tplaylist_n_playlist BIGINT,\n" +
147 "\tmusica_n_musica\t BIGINT,\n" +
148 "\tPRIMARY KEY(playlist_n_playlist,musica_n_musica)\n" +
149 ");\n" +
150 "\n" +
151 "CREATE TABLE concerto_artista (\n" +
152 "\tconcerto_n_concerto BIGINT,\n" +
153 "\tartista_n_artista\t BIGINT,\n" +
154 "\tPRIMARY KEY(concerto_n_concerto,artista_n_artista)\n" +
155 ");\n" +
156 "\n" +
157 "CREATE TABLE artista_musica (\n" +
158 "\tartista_n_artista BIGINT,\n" +
159 "\tmusica_n_musica\t BIGINT,\n" +
160 "\tPRIMARY KEY(artista_n_artista,musica_n_musica)\n" +
161 ");\n" +
162 "\n" +
163 "CREATE TABLE playlist_privada_utilizador (\n" +
164 "\tplaylist_n_playlist BIGINT,\n" +
165 "\tutilizador_username VARCHAR(512),\n" +
166 "\tPRIMARY KEY(playlist_n_playlist,utilizador_username)\n" +
167 ");\n";
168
169 stmt.executeQuery(createTables);
170 } catch (SQLException e) {
171 e.printStackTrace();
172 }
173 }
174
175
176 public static boolean isEditor(String username){
177 try {
178 Statement stmt;
179
180 if (connection.createStatement() == null) {
181 connection = DriverManager.getConnection(
182 "jdbc:postgresql://127.0.0.1:5432/postgres",
183 "postgres",
184 "basedados");
185 }
186
187 if ((stmt = connection.createStatement()) == null) {
188 System.out.println("Erro nao foi possÃvel criar uma statement ou retornou null");
189 System.exit(-1);
190 }
191 String query = "select editor from utilizador where username = '"+username+"';";
192 } catch (SQLException e) {
193 e.printStackTrace();
194 }
195 return true;
196 }
197 public static void main(String[] args) {
198 connectDataBase();
199 createTables();
200
201 try {
202 Statement stmt;
203
204 if(connection.createStatement() == null){
205 connection = DriverManager.getConnection(
206 "jdbc:postgresql://127.0.0.1:5432/postgres",
207 "postgres",
208 "basedados");
209 }
210
211 if((stmt = connection.createStatement()) == null) {
212 System.out.println("Erro nao foi possÃvel criar uma statement ou retornou null");
213 System.exit(-1);
214 }
215 //MENU
216 String something = "";
217 try {
218 String a, name, password;
219 Boolean editor;
220
221 InputStreamReader input = new InputStreamReader(System.in);
222 BufferedReader reader = new BufferedReader(input);
223
224 /*
225 while (true) {
226 String message;
227 //something --> mensagem a receber e analisar
228
229 System.out.print("\nType login or register\n> ");
230 a = reader.readLine();
231 if (a.equals("login")) {
232 System.out.print("\nWhat is your username?\n> ");
233 name = reader.readLine();
234 System.out.print("\nWhat is your password?\n> ");
235 password = reader.readLine();
236
237 //verificar a info
238 if (something.equals("no")) {//informação errada
239 System.out.println("\nWrong information, try again");
240 continue;
241 } else if (something.equals("yes")) {
242 System.out.println("\nStatus Logged | Welcome back to DropMusic");
243
244 if (something.equals("yes")) {
245 editor = true;
246 //c.setEditor(true);
247 } else {
248 editor = false;
249 //c.setEditor(false);
250 }
251
252 //c.setUsername(name);
253 break;
254 }
255 } else if (a.equals("register")) {
256 System.out.print("\nWhat is your username?\n> ");
257 name = reader.readLine();
258 System.out.print("\nWhat is your password? \n> ");
259 password = reader.readLine();
260
261 //verificar a info
262
263 if (something.equals("no")) {
264 System.out.println("Username already exists, try again");
265 continue;
266 } else if (something.equals("yes")) {
267 System.out.println("\nStatus Subscribed | Welcome to DropMusic");
268
269 //c.setUsername(name);
270 break;
271 }
272 } else {
273 System.out.println("Something went wrong, try again");
274 continue;
275 }
276 }
277 */
278
279 while (true) {
280 System.out.println("\n1- Search song");
281 System.out.println("2- Details of an album");
282 System.out.println("3- Details of an artist");
283 System.out.println("4- Write a review of an album");
284 System.out.println("5- Share with friends uploaded song");
285 if (true) {
286 System.out.println("6- Manage information");
287 System.out.println("7- Make an user a new editor");
288 }
289 System.out.println("0- Logout");
290 System.out.print("> ");
291 Scanner sc = new Scanner(System.in);
292 String num = sc.next();
293
294 switch (num) {
295 case "1":
296 System.out.println("\n1- Trough artist");
297 System.out.println("2- Trough album");
298 System.out.print("3- By song name\n> ");
299 String aux1 = sc.next();
300 String artist11 = "";
301 switch (aux1) {
302 case "1":
303 System.out.print("\nType artist's name:\n> ");
304 artist11 = reader.readLine();
305
306 //procurar artista
307
308 if (something.equals("yes-artist")) {
309 System.out.print("\nChoose one song:\n> ");
310 String song11 = reader.readLine();
311 System.out.println("\n1- Duration");
312 System.out.println("2- Composer");
313 System.out.println("3- Lyrics");
314 System.out.println("4- Upload");
315 System.out.print("5- Download\n> ");
316 String aux11 = reader.readLine();
317
318 switch (aux11) {
319 case "1":
320 //send message
321
322 continue;
323 case "2":
324 //send message
325 continue;
326 case "3":
327 //send message
328 continue;
329 case "4":
330 //send message
331 continue;
332 case "5":
333 //send message
334 continue;
335 default:
336 continue;
337 }
338 } else {
339 continue;
340 }
341 case "2":
342 System.out.print("\nType album's name:\n> ");
343 String album12 = reader.readLine();
344
345 //procurar album
346
347 if (something.equals("yes-album")) {
348 System.out.print("\nChoose one song:\n> ");
349 String song12 = reader.readLine();
350 System.out.println("\n1- Duration");
351 System.out.println("2- Composer");
352 System.out.println("3- Lyrics");
353 System.out.println("4- Upload");
354 System.out.print("5- Download\n> ");
355 String aux12 = reader.readLine();
356
357 switch (aux12) {
358 case "1":
359 //send message
360 continue;
361 case "2":
362 //send message
363 continue;
364 case "3":
365 //send message
366 continue;
367 case "4":
368 //send message
369 continue;
370 case "5":
371 //send message;
372 default:
373 continue;
374 }
375 } else {
376 continue;
377 }
378 case "3":
379 System.out.print("\nType song's name:\n> ");
380 String song13 = reader.readLine();
381 System.out.println("\n1- Duration");
382 System.out.println("2- Composer");
383 System.out.println("3- Lyrics");
384 System.out.println("4- Upload");
385 System.out.print("5- Download\n> ");
386 String aux13 = reader.readLine();
387
388 switch (aux13) {
389 case "1":
390 //send message
391 continue;
392 case "2":
393 //send message
394 continue;
395 case "3":
396 //send message
397 continue;
398 case "4":
399 //send message;
400 case "5":
401 //send message
402 continue;
403 default:
404 continue;
405 }
406 default:
407 continue;
408 }
409 case "2":
410 System.out.print("\nType artist's name:\n> ");
411 String artist2 = reader.readLine();
412 System.out.print("\nType album's name:\n> ");
413 String album2 = reader.readLine();
414 System.out.println("\n1- Description");
415 System.out.println("2- List of songs");
416 System.out.print("3- Reviews\n> ");
417 String aux2 = sc.next();
418 switch (aux2) {
419 case "1":
420 //send message
421 continue;
422 case "2":
423 //send message
424 continue;
425 case "3":
426 //send message
427 continue;
428 default:
429 continue;
430 }
431 case "3":
432 System.out.print("\nType artist's name:\n> ");
433 String artist3 = reader.readLine();
434 //send message
435 continue;
436 case "4":
437 System.out.print("\nType album's name:\n> ");
438 String album4 = reader.readLine();
439
440 String score4 = null;
441 while (true) {
442 System.out.print("\nType album's score (0 to 10):\n> ");
443 score4 = reader.readLine();
444 try {
445 int score41 = Integer.parseInt(score4);
446 if (score41 >= 0 && score41 <= 10) {
447 break;
448 } else {
449 System.out.println("That input is invalid");
450 continue;
451 }
452 } catch (NumberFormatException e) {
453 System.out.println("That input is invalid");
454 continue;
455 }
456 }
457
458 System.out.print("\nWrite review (300 characters):\n> ");
459 String review4 = reader.readLine();
460 String review42;
461 if (review4.length() > 300)
462 review42 = review4.substring(0, 300);
463 else
464 review42 = review4;
465
466 //send message
467 continue;
468 case "5":
469 System.out.print("\nType album's name:\n> ");
470 String album5 = reader.readLine();
471 System.out.print("\nType song's name:\n> ");
472 String song5 = reader.readLine();
473 System.out.println("\nType your friends usernames (write BREAK to end):");
474 String user_friends5 = "";
475 while (true) {
476 System.out.print(">");
477 String friend5 = reader.readLine();
478 if (friend5.equals("BREAK")) {
479 break;
480 } else {
481 user_friends5 += friend5 + "//";
482 continue;
483 }
484 }
485 //send message
486 continue;
487 case "6":
488 if (true) {
489 System.out.println("\n1- Insert");
490 System.out.print("2- Change\n> ");
491 String aux8 = sc.next();
492 switch (aux8) {
493 case "1":
494 System.out.println("\n1- Artist");
495 System.out.println("2- Album");
496 System.out.print("3- Song\n> ");
497 String aux81 = sc.next();
498 switch (aux81) {
499 case "1":
500 System.out.print("\nType artist's name:\n> ");
501 String artist811 = reader.readLine();
502 System.out.print("\nType artist's description:\n> ");
503 String description811 = reader.readLine();
504 System.out.print("\nType artist's working period:\n> ");
505 String period811 = reader.readLine();
506
507 //send message
508 continue;
509 case "2":
510 System.out.print("\nType artist's name:\n> ");
511 String artist812 = reader.readLine();
512 System.out.print("\nType album's name:\n> ");
513 String album812 = reader.readLine();
514 System.out.print("\nType album's genre:\n> ");
515 String genre812 = reader.readLine();
516 System.out.print("\nType album's description:\n> ");
517 String description812 = reader.readLine();
518 System.out.print("\nType album's date:\n> ");
519 String date812 = reader.readLine();
520
521 //send message
522 continue;
523 case "3":
524 System.out.print("\nType artist's name:\n> ");
525 String artist813 = reader.readLine();
526 System.out.print("\nType album's name:\n> ");
527 String album813 = reader.readLine();
528 System.out.print("\nType song's name:\n> ");
529 String song813 = reader.readLine();
530 System.out.print("\nType song's duration:\n> ");
531 String duration813 = reader.readLine();
532 System.out.print("\nType song's composer:\n> ");
533 String composer813 = reader.readLine();
534 System.out.print("\nType song's lyrics:\n> ");
535 String lyrics813 = reader.readLine();
536
537 //send message
538 continue;
539 default:
540 continue;
541 }
542 case "2":
543 System.out.println("\n1- Artist");
544 System.out.println("2- Album");
545 System.out.print("3- Song\n> ");
546 String aux82 = sc.next();
547 switch (aux82) {
548 case "1":
549 System.out.println("\n1- Artist's name");
550 System.out.println("2- Artist's description");
551 System.out.print("3- Artist's working period\n> ");
552 String aux821 = reader.readLine();
553
554 System.out.print("\nType artist's name:\n> ");
555 String artist821 = reader.readLine();
556 switch (aux821) {
557 case "1":
558 System.out.print("\nType artist's new name:\n> ");
559 String newartist8211 = reader.readLine();
560 //send message
561 continue;
562 case "2":
563 System.out.print("\nType artist's new description:\n> ");
564 String newd8212 = reader.readLine();
565 //send message
566 continue;
567 case "3":
568 System.out.print("\nType artist's new working period:\n> ");
569 String newp8213 = reader.readLine();
570 //send message
571 continue;
572 default:
573 continue;
574 }
575 case "2":
576 System.out.println("\n1- Album's name");
577 System.out.println("2- Album's description");
578 System.out.println("3- Album's genre");
579 System.out.print("4- Album's date\n> ");
580 String aux822 = reader.readLine();
581
582 System.out.print("\nType artist's name:\n> ");
583 String artist822 = reader.readLine();
584 System.out.print("\nType album's name:\n> ");
585 String album822 = reader.readLine();
586 switch (aux822) {
587 case "1":
588 System.out.print("\nType album's new name:\n> ");
589 String newalbum8221 = reader.readLine();
590 //send message
591 continue;
592 case "2":
593 System.out.print("\nType album's new description:\n> ");
594 String newdesc8222 = reader.readLine();
595 //send message
596 continue;
597 case "3":
598 System.out.print("\nType album's new genre:\n> ");
599 String newgenre8223 = reader.readLine();
600 //send message
601 continue;
602 case "4":
603 System.out.print("\nType album's new date:\n> ");
604 String newdate8224 = reader.readLine();
605 //send message
606 continue;
607 default:
608 continue;
609 }
610 case "3":
611 System.out.println("\n1- Song's name");
612 System.out.println("2- Song's duration");
613 System.out.println("3- Songs's composer");
614 System.out.print("4- Song's lyrics\n> ");
615 String aux823 = reader.readLine();
616
617 System.out.print("\nType artist's name:\n> ");
618 String artist823 = reader.readLine();
619 System.out.print("\nType album's name:\n> ");
620 String album823 = reader.readLine();
621 System.out.print("\nType song's name:\n> ");
622 String song823 = reader.readLine();
623 switch (aux823) {
624 case "1":
625 System.out.print("\nType song's new name:\n> ");
626 String newsong8231 = reader.readLine();
627 //send message
628 continue;
629 case "2":
630 System.out.print("\nType song's new duration:\n> ");
631 String newdur8232 = reader.readLine();
632 //send message
633 continue;
634 case "3":
635 System.out.print("\nType song's new composer:\n> ");
636 String newcomp8233 = reader.readLine();
637 //send message
638 continue;
639 case "4":
640 System.out.print("\nType song's new lyrics:\n> ");
641 String newlyrics8234 = reader.readLine();
642 //send message
643 continue;
644 default:
645 continue;
646 }
647 default:
648 continue;
649 }
650 default:
651 continue;
652 }
653 } else {
654 continue;
655 }
656 case "7":/*
657 if (c.getEditor()) {
658 System.out.print("\nType username:\n> ");
659 String user9 = reader.readLine();
660 //send message
661 continue;
662 } else
663 continue;*/
664 break;
665 case "0":
666 System.exit(0);
667 default:
668 continue;
669 }
670 }
671 }catch (IOException e){
672 System.out.println("Input null");
673 }
674
675
676
677
678
679
680
681
682 /*
683 String nomealbum = "ol";
684 String query2 = "INSERT INTO album( n_album, nome, genero, data, editora) VALUES (2, '"+nomealbum+"', 'fdsfsdf', null, 'dgfd');";
685 String query = "select * from album where nome = '"+nomealbum+"';";
686 int ik = stmt.executeUpdate(query2);
687 System.out.println(ik);
688
689 System.out.println("querying: "+ query);
690 ResultSet res = stmt.executeQuery(query);
691 // para podermos saber quantas colunas o resultado tem
692 // To check how many columns does the result holds
693 ResultSetMetaData rsmd = res.getMetaData();
694 int columnsNumber = rsmd.getColumnCount();
695
696 for(int i = 1 ; i <= columnsNumber ; i++){
697 System.out.print(rsmd.getColumnName(i) +", ");
698 }
699
700
701 while (res.next()) {
702 // Listar o resultado da query
703 // List the result from the query
704 for (int i = 1; i <= columnsNumber; i++) {
705 if (i > 1) System.out.print(", ");
706 String columnValue = res.getString(i);
707 System.out.print(columnValue);
708 }
709 System.out.println("");
710 }*/
711 } catch (SQLException e) {
712 e.printStackTrace();
713 }
714
715 }
716}