· 7 years ago · Dec 19, 2018, 02:24 PM
1import java.io.BufferedReader;
2import java.io.IOException;
3import java.io.InputStreamReader;
4import java.sql.*;
5import java.util.ArrayList;
6import java.util.Scanner;
7
8public class Main {
9 private static Connection connection = null;
10 private static InputStreamReader input = new InputStreamReader(System.in);
11 private static BufferedReader reader = new BufferedReader(input);
12 private static Statement stmt, stmtAux;
13 private static String utilizadorUsername;
14
15 public static void connectDataBase() {
16 try {
17 Class.forName("org.postgresql.Driver");
18
19 } catch (ClassNotFoundException e) {
20
21 System.out.println("Driver not found, check if the jar is reachable !");
22 e.printStackTrace();
23 return;
24
25 }
26 System.out.println("JDBC Driver funciona .. tentar a ligacao");
27 try {
28 connection = DriverManager.getConnection(
29 "jdbc:postgresql://127.0.0.1:5432/postgres",
30 "postgres",
31 "basedados");
32 } catch (SQLException e) {
33 System.out.println("Ligacao falhou.. erro:");
34 e.printStackTrace();
35 return;
36
37 }
38 if (connection != null) {
39 System.out.println("Connect successfully");
40 } else {
41 System.out.println("Nao conseguimos estabelecer a ligacao");
42 }
43 }
44
45 public static void createTables(){
46 try {
47 Statement stmt;
48
49 if (connection.createStatement() == null) {
50 connection = DriverManager.getConnection(
51 "jdbc:postgresql://127.0.0.1:5432/postgres",
52 "postgres",
53 "basedados");
54 }
55
56 if ((stmt = connection.createStatement()) == null) {
57 System.out.println("Erro nao foi possÃvel criar uma statement ou retornou null");
58 System.exit(-1);
59 }
60 if ((stmtAux = connection.createStatement()) == null) {
61 System.out.println("Erro nao foi possÃvel criar uma statement ou retornou null");
62 System.exit(-1);
63 }
64 String createTables = "CREATE TABLE artista (\n" +
65 "\tn_artista\t BIGINT,\n" +
66 "\tnome\t\t VARCHAR(512) NOT NULL,\n" +
67 "\tperiodo\t VARCHAR(512) NOT NULL,\n" +
68 "\thistoria\t VARCHAR(512) NOT NULL,\n" +
69 "\tbanda_elementos VARCHAR(512),\n" +
70 "\tsolo_nome\t VARCHAR(512),\n" +
71 "\tPRIMARY KEY(n_artista)\n" +
72 ");\n" +
73 "\n" +
74 "CREATE TABLE album (\n" +
75 "\tn_album\t\t BIGINT,\n" +
76 "\tnome\t\t VARCHAR(512) NOT NULL,\n" +
77 "\tgenero\t\t VARCHAR(512) NOT NULL,\n" +
78 "\tdata_album\t VARCHAR(512) NOT NULL,\n" +
79 "\tartista_n_artista BIGINT NOT NULL,\n" +
80 "\tPRIMARY KEY(n_album)\n" +
81 ");\n" +
82 "\n" +
83 "CREATE TABLE musica (\n" +
84 "\tn_musica\t\t BIGINT,\n" +
85 "\tnome\t\t VARCHAR(512) NOT NULL,\n" +
86 "\tletra\t\t VARCHAR(512) NOT NULL,\n" +
87 "\tduracao\t\t SMALLINT NOT NULL,\n" +
88 "\tcompositor\t VARCHAR(512) NOT NULL,\n" +
89 "\teditora_n_editora BIGINT NOT NULL,\n" +
90 "\tPRIMARY KEY(n_musica)\n" +
91 ");\n" +
92 "\n" +
93 "CREATE TABLE playlist (\n" +
94 "\tn_playlist\t\t BIGINT,\n" +
95 "\tnome\t\t VARCHAR(512) NOT NULL,\n" +
96 "\tutilizador_username VARCHAR(512) NOT NULL,\n" +
97 "\tPRIMARY KEY(n_playlist)\n" +
98 ");\n" +
99 "\n" +
100 "CREATE TABLE utilizador (\n" +
101 "\tusername VARCHAR(512),\n" +
102 "\tpassword VARCHAR(512) NOT NULL,\n" +
103 "\tPRIMARY KEY(username)\n" +
104 ");\n" +
105 "\n" +
106 "CREATE TABLE editor (\n" +
107 "\tutilizador_username VARCHAR(512),\n" +
108 "\tPRIMARY KEY(utilizador_username)\n" +
109 ");\n" +
110 "\n" +
111 "CREATE TABLE nao_editor (\n" +
112 "\tutilizador_username VARCHAR(512),\n" +
113 "\tPRIMARY KEY(utilizador_username)\n" +
114 ");\n" +
115 "\n" +
116 "CREATE TABLE critica (\n" +
117 "\tn_critica\t\t BIGINT,\n" +
118 "\tpontuacao\t\t SMALLINT NOT NULL,\n" +
119 "\tcritica\t\t VARCHAR(512) NOT NULL,\n" +
120 "\tutilizador_username VARCHAR(512) NOT NULL,\n" +
121 "\tPRIMARY KEY(n_critica)\n" +
122 ");\n" +
123 "\n" +
124 "CREATE TABLE concerto (\n" +
125 "\tn_concerto BIGINT,\n" +
126 "\tdata\t VARCHAR(512) NOT NULL,\n" +
127 "\tlocalidade VARCHAR(512) NOT NULL,\n" +
128 "\tPRIMARY KEY(n_concerto)\n" +
129 ");\n" +
130 "\n" +
131 "CREATE TABLE ficheiro (\n" +
132 "\tuploaded_file\t BOOL NOT NULL,\n" +
133 "\tmusica_n_musica BIGINT,\n" +
134 "\tPRIMARY KEY(musica_n_musica)\n" +
135 ");\n" +
136 "\n" +
137 "CREATE TABLE editora (\n" +
138 "\tn_editora BIGINT,\n" +
139 "\tnome\t VARCHAR(512) NOT NULL,\n" +
140 "\tPRIMARY KEY(n_editora)\n" +
141 ");\n" +
142 "\n" +
143 "CREATE TABLE ficheiro_utilizador (\n" +
144 "\tficheiro_musica_n_musica BIGINT,\n" +
145 "\tutilizador_username\t VARCHAR(512),\n" +
146 "\tPRIMARY KEY(ficheiro_musica_n_musica,utilizador_username)\n" +
147 ");\n" +
148 "\n" +
149 "CREATE TABLE musica_critica (\n" +
150 "\tmusica_n_musica\t BIGINT NOT NULL,\n" +
151 "\tcritica_n_critica BIGINT,\n" +
152 "\tPRIMARY KEY(critica_n_critica)\n" +
153 ");\n" +
154 "\n" +
155 "CREATE TABLE album_editora (\n" +
156 "\talbum_n_album\t BIGINT,\n" +
157 "\teditora_n_editora BIGINT,\n" +
158 "\tPRIMARY KEY(album_n_album,editora_n_editora)\n" +
159 ");\n" +
160 "\n" +
161 "CREATE TABLE album_musica (\n" +
162 "\talbum_n_album\t BIGINT,\n" +
163 "\tmusica_n_musica BIGINT,\n" +
164 "\tPRIMARY KEY(album_n_album,musica_n_musica)\n" +
165 ");\n" +
166 "\n" +
167 "CREATE TABLE playlist_musica (\n" +
168 "\tplaylist_n_playlist BIGINT,\n" +
169 "\tmusica_n_musica\t BIGINT,\n" +
170 "\tPRIMARY KEY(playlist_n_playlist,musica_n_musica)\n" +
171 ");\n" +
172 "\n" +
173 "CREATE TABLE album_critica (\n" +
174 "\talbum_n_album\t BIGINT NOT NULL,\n" +
175 "\tcritica_n_critica BIGINT,\n" +
176 "\tPRIMARY KEY(critica_n_critica)\n" +
177 ");\n" +
178 "\n" +
179 "CREATE TABLE concerto_artista (\n" +
180 "\tconcerto_n_concerto BIGINT,\n" +
181 "\tartista_n_artista\t BIGINT,\n" +
182 "\tPRIMARY KEY(concerto_n_concerto,artista_n_artista)\n" +
183 ");\n" +
184 "\n" +
185 "CREATE TABLE artista_musica (\n" +
186 "\tartista_n_artista BIGINT,\n" +
187 "\tmusica_n_musica\t BIGINT,\n" +
188 "\tPRIMARY KEY(artista_n_artista,musica_n_musica)\n" +
189 ");\n" +
190 "\n" +
191 "CREATE TABLE playlist_privada_utilizador (\n" +
192 "\tplaylist_n_playlist BIGINT,\n" +
193 "\tutilizador_username VARCHAR(512),\n" +
194 "\tPRIMARY KEY(playlist_n_playlist,utilizador_username)\n" +
195 ");";
196
197 stmt.executeUpdate(createTables);
198
199 } catch (SQLException e) {
200 e.printStackTrace();
201 }
202 }
203
204 public static void insertData(){
205 try {
206 Statement stmt;
207
208 if (connection.createStatement() == null) {
209 connection = DriverManager.getConnection(
210 "jdbc:postgresql://127.0.0.1:5432/postgres",
211 "postgres",
212 "basedados");
213 }
214
215 if ((stmt = connection.createStatement()) == null) {
216 System.out.println("Erro nao foi possÃvel criar uma statement ou retornou null");
217 System.exit(-1);
218 }
219 } catch (SQLException e) {
220 e.printStackTrace();
221 }
222 }
223
224
225 public static boolean isEditor(String username) throws SQLException {
226 String query = "select utilizador_username from editor where utilizador_username = '" +username+ "';";
227 ResultSet res = stmt.executeQuery(query);
228 if (res.next()) {
229 return true;
230 }
231 else {
232 return false;
233 }
234 }
235
236 public static void login_register() throws IOException, SQLException {
237 String a, name, password;
238
239 while (true) {
240 System.out.print("\nType login or register\n> ");
241 a = reader.readLine();
242 if (a.equals("login")) {
243 System.out.print("\nWhat is your username?\n> ");
244 name = reader.readLine();
245 utilizadorUsername = name;
246 System.out.print("\nWhat is your password?\n> ");
247 password = reader.readLine();
248
249 String look = "select username, password from utilizador where username ='" + name + "' and password ='" + password + "';";
250 ResultSet res = stmt.executeQuery(look);
251
252 if (!res.next()) {
253 System.out.println("Wrong information, try again");
254 continue;
255 }
256 else {
257 System.out.println("\nStatus Logged | Welcome back to DropMusic");
258 break;
259 }
260 }
261 else if (a.equals("register")) {
262 System.out.print("\nWhat is your username?\n> ");
263 name = reader.readLine();
264 utilizadorUsername = name;
265 System.out.print("\nWhat is your password? \n> ");
266 password = reader.readLine();
267 String look = "select username from utilizador where username ='" + name + "';";
268 ResultSet res = stmt.executeQuery(look);
269
270 if (res.next()) {
271 System.out.println("Username already exists, try again");
272 continue;
273 }
274 else {
275 String query = "INSERT INTO public.utilizador(\n" +
276 "\tusername, password)\n" +
277 "\tVALUES ('" + name + "', '" + password + "');";
278 stmt.executeUpdate(query);
279
280 String query2 = "INSERT INTO public.nao_editor(\n" +
281 "\tutilizador_username)\n" +
282 "\tVALUES ('"+name+"');";
283 stmt.executeUpdate(query2);
284
285 System.out.println("\nStatus Subscribed | Welcome to DropMusic");
286 break;
287 }
288 }
289 else {
290 System.out.println("Something went wrong, try again");
291 continue;
292 }
293 }
294 }
295
296 public static void searchSongByAlbum() throws IOException, SQLException {
297 System.out.print("\nType album's name:\n> ");
298 String album12 = reader.readLine();
299 ArrayList <Integer> musicas = new ArrayList();
300
301 String query12 = "select nome\n"+
302 "from album\n" +
303 "where nome = '" + album12 + "';";
304 ResultSet res12 = stmt.executeQuery(query12);
305
306 if (!res12.next()){
307 System.out.println("That album doesn't exist");
308 }
309 else{
310 String query121 = "select musica.nome, musica.n_musica\n" +
311 "from album, musica, album_musica\n" +
312 "where album.nome = '" + album12 + "' and n_musica = musica_n_musica\n"+
313 "and n_album = album_n_album;";
314 ResultSet res = stmt.executeQuery(query121);
315
316 int i=1;
317 System.out.println();
318 while (res.next()) {
319 System.out.println("> " + i + ". " + res.getString(1));
320 musicas.add(Integer.parseInt(res.getString(2)));
321 i++;
322 }
323 printSongInfo(musicas);
324 }
325 }
326
327 public static void searchSongByGenre() throws IOException, SQLException {
328 System.out.print("\nType album's genre:\n> ");
329 String genre = reader.readLine();
330 ArrayList <Integer> musicas = new ArrayList();
331
332 String query12 = "select nome\n"+
333 "from album\n" +
334 "where genero = '" + genre + "';";
335 ResultSet res12 = stmt.executeQuery(query12);
336
337 if (res12.next()) {
338 String query121 = "select musica.nome, musica.n_musica\n" +
339 "from album, musica, album_musica\n" +
340 "where album.genero = '" + genre + "' and n_musica = musica_n_musica and n_album = album_n_album;";
341 ResultSet res121 = stmt.executeQuery(query121);
342
343 int i=1;
344 System.out.println();
345 while (res121.next()) {
346 System.out.println(i + ". " + res121.getString(1));
347 musicas.add(Integer.parseInt(res121.getString(2)));
348 i++;
349 }
350
351 printSongInfo(musicas);
352 }
353 else {
354 System.out.println("That genre doesn't exist");
355 }
356 }
357
358 public static void searchSongByDate() throws IOException, SQLException {
359 System.out.print("\nType album's date:\n> ");
360 String data = reader.readLine();
361 ArrayList <Integer> musicas = new ArrayList();
362
363 String query12 = "select nome\n"+
364 "from album\n" +
365 "where data_album = '" + data + "';";
366 ResultSet res12 = stmt.executeQuery(query12);
367
368 if (res12.next()) {
369 String query121 = "select musica.nome, musica.n_musica\n" +
370 "from album, musica, album_musica\n" +
371 "where album.data_album = '" + data + "' and n_musica = musica_n_musica and n_album = album_n_album;";
372 ResultSet res121 = stmt.executeQuery(query121);
373
374 int i=1;
375 System.out.println();
376 while (res121.next()) {
377 System.out.println(i + ". " + res121.getString(1));
378 musicas.add(Integer.parseInt(res121.getString(2)));
379 i++;
380 }
381
382 printSongInfo(musicas);
383 }
384 else {
385 System.out.println("That date doesn't exist");
386 }
387 }
388
389 public static void detailsArtist() throws IOException, SQLException {
390 System.out.print("\nType artist's name:\n> ");
391 String artist3 = reader.readLine();
392
393 String query3 = "select nome, periodo, historia, banda_elementos, solo_nome, concerto.data, concerto.localidade\n"+
394 "\tfrom artista, concerto, concerto_artista\n"+
395 "\twhere nome='"+artist3+"' and concerto_n_concerto = n_concerto and artista_n_artista = n_artista;";
396 ResultSet res = stmt.executeQuery(query3);
397
398 Boolean aux = false;
399 while (res.next()) {
400 aux = true;
401 System.out.println("Name: " + res.getString(1));
402 System.out.println("Date: " + res.getString(2));
403 System.out.println("History: " + res.getString(3));
404 if(res.getString(4).equals(' '))
405 System.out.println("Real name: " + res.getString(5));
406 else
407 System.out.println("Elements: " + res.getString(4));
408 System.out.println("Concerts:");
409 System.out.println(res.getString(6) + " ; " + res.getString(7));
410 }
411
412 if (!aux)
413 System.out.println("Artist name doesn't exist");
414 }
415
416 public static void makeEditor() throws IOException, SQLException {
417 if (isEditor(utilizadorUsername)) {
418 listUsernames();
419 System.out.print("\nType username:\n> ");
420 String user7 = reader.readLine();
421
422 String query71 = "select username\n"+
423 "from utilizador ,nao_editor\n" +
424 "where utilizador_username = username and username = '" + user7 + "' and utilizador_username = '" + user7 +"';";
425 ResultSet res71 = stmt.executeQuery(query71);
426 if (!res71.next()) {
427 System.out.println("The user is not in the data base or is already an editor");
428 }
429 else {
430 String remove= "DELETE FROM public.nao_editor\n" +
431 "\tWHERE utilizador_username='"+user7+"';";
432 stmt.executeUpdate(remove);
433
434 String query72 = "INSERT INTO public.editor(\n" +
435 "\tutilizador_username)\n" +
436 "\tVALUES ('"+user7+"');";
437 stmt.executeUpdate(query72);
438 System.out.println(user7 + " is now an editor");
439 }
440 }
441 }
442
443 public static void detailsOfAnAlbum(String albumName) throws SQLException {
444 //Details of an album
445
446 String queryAlbum= "select album.n_album, album.nome, artista.nome, album.genero, album.data_album "+
447 "from album, artista "+
448 "where artista.n_artista= album.artista_n_artista AND album.nome='"+albumName+"';";
449 ResultSet res = stmt.executeQuery(queryAlbum);
450
451 Boolean aux = true;
452 while(res.next()) {
453 aux = false;
454 int albumNumber= Integer.parseInt(res.getString(1));
455 System.out.println("Album: " + res.getString(2));
456 System.out.println("> Artist: " + res.getString(3));
457 System.out.println("> Genre: " + res.getString(4));
458 System.out.println("> Released: " + res.getString(5));
459
460 String queryLabels= "select editora.nome from editora, album, album_editora "+
461 "where editora.n_editora= album_editora.editora_n_editora AND album.n_album= album_editora.album_n_album AND album.n_album= "+albumNumber+";";
462 ResultSet resultAux= stmtAux.executeQuery(queryLabels);
463 System.out.print("> Record Label(s): ");
464 while(resultAux.next()){
465 System.out.println(resultAux.getString(1)+" ");
466 }
467 System.out.println();
468 String queryReviews= "select critica.pontuacao, critica.critica from critica, album_critica "+
469 "where critica.n_critica= album_critica.critica_n_critica "+
470 "AND album_critica.album_n_album= "+albumNumber+";";
471 resultAux= stmtAux.executeQuery(queryReviews);
472 boolean first=true;
473 while(resultAux.next()){
474 if(first) {
475 System.out.println("Album's reviews:");
476 first = false;
477 }
478 System.out.println("Score: "+resultAux.getString(1));
479 System.out.println("Review: "+resultAux.getString(2));
480
481 }
482 String queryMusicas="select musica.nome, musica.compositor, musica.duracao "+
483 "from album, musica, album_musica "+
484 "where album.n_album=album_musica.album_n_album AND musica.n_musica= album_musica.musica_n_musica AND album.n_album= '"+albumNumber+"';";
485
486 resultAux= stmtAux.executeQuery(queryMusicas);
487 first=true;
488 while(resultAux.next()) {
489 if(first) {
490 System.out.println("List of album's songs");
491 first = false;
492 }
493 System.out.println("> Song: " + resultAux.getString(1) + ", Writer: " + resultAux.getString(2) + ", Duration: " + resultAux.getString(3) + "sec");
494 }
495 }
496
497 if (aux)
498 System.out.println("Album name doesn't exist");
499
500 }
501
502 public static void searchSongByArtist(String artistName) throws SQLException, IOException {
503 //Search song by artist name
504
505 String searchArtistNumber= "select artista.n_artista from artista where artista.nome= '"+artistName+"';";
506 ResultSet res = stmt.executeQuery(searchArtistNumber);
507 ResultSet resAux;
508 String querySearchArtist;
509 Boolean first=true, exists=false;
510
511 ArrayList<Integer> songsFound= new ArrayList<>();
512 int opcaoUtilizador=1; //não esquecer de subtrair 1
513
514 while(res.next()) {
515 exists=true;
516 if(first){
517 first=false;
518 }
519 int artistNumber= Integer.parseInt(res.getString(1));
520 querySearchArtist= "select musica.n_musica, musica.nome " +
521 "from musica, artista, artista_musica "+
522 "where artista.n_artista= "+artistNumber+" AND (artista_musica.musica_n_musica =musica.n_musica AND artista_musica.artista_n_artista=artista.n_artista)"+
523 "union select musica.n_musica, musica.nome "+
524 "from musica, album, album_musica "+
525 "where musica.n_musica=album_musica.musica_n_musica AND album.n_album=album_musica.album_n_album AND album.artista_n_artista= "+artistNumber+";";
526
527 resAux = stmtAux.executeQuery(querySearchArtist);
528 System.out.println();
529 while(resAux.next()) {
530 System.out.println("> "+opcaoUtilizador+". "+resAux.getString(2));
531 opcaoUtilizador++;
532 songsFound.add(Integer.parseInt(resAux.getString(1)));
533 }
534 }
535 if(!exists){
536 System.out.println("Artist name doesn't exist");
537 }
538 else{
539 printSongInfo(songsFound);
540 }
541 }
542
543 public static void searchSongByScore() throws SQLException, IOException {
544 int pontuacao=8;
545
546 Boolean first=true, exists=false;
547 ArrayList<Integer> songsFound= new ArrayList<>();
548 int opcaoUtilizador=1;
549
550 String query="select musica.n_musica, musica.nome" +
551 " from musica"+
552 " where "+pontuacao+"= (select avg((critica.pontuacao)) "+"" +
553 "from critica, musica, musica_critica "+
554 " where musica_critica.musica_n_musica= musica.n_musica AND musica_critica.critica_n_critica= critica.n_critica);";
555
556 ResultSet res = stmt.executeQuery(query);
557 while(res.next()) {
558 exists=true;
559 if(first){
560 System.out.println("Songs found searching score "+pontuacao);
561 first=false;
562 }
563 System.out.println(opcaoUtilizador+". "+res.getString(2));
564 songsFound.add(Integer.parseInt(res.getString(1)));
565
566 }
567 if(!exists){
568 System.out.println("There were no songs found with that score");
569 }
570 else{
571 printSongInfo(songsFound);
572 }
573 }
574
575 public static int searchSongByName(String songName, String operation) throws SQLException, IOException {
576 String querySearchNumber= "select musica.n_musica from musica "+
577 "where musica.nome= '"+songName+"';";
578 ResultSet res= stmt.executeQuery(querySearchNumber);
579
580 int option=1;
581 ArrayList<Integer> songsFound= new ArrayList<>();
582
583 boolean exists=false;
584 while (res.next()) {
585 exists=true;
586 int songNumber= Integer.parseInt(res.getString(1));
587 System.out.println(option+". "+songName+"\nBy: ");
588 songsFound.add(songNumber);
589 String queryArtists= "select artista.nome from artista, musica, artista_musica "+
590 "where artista.n_artista= artista_musica.artista_n_artista AND musica.n_musica= artista_musica.musica_n_musica AND musica.n_musica="+songNumber+";";
591 ResultSet resAux= stmtAux.executeQuery(queryArtists);
592 while(resAux.next()){
593 System.out.println(resAux.getString(1));
594 }
595 }
596
597 if(!exists){
598 System.out.println("Song name doesn't exist");
599 }
600 if(operation.equals("add playlist")){
601 System.out.print("Option: ");
602 option = Integer.parseInt(reader.readLine());
603 System.out.println(option);
604 if (option <= songsFound.size() && option>0)
605 return songsFound.get(option - 1);
606 else return-1;
607 }
608 else{
609 printSongInfo(songsFound);
610
611 return -1;
612 }
613 }
614
615 public static String [] writeReview() throws IOException {
616 String review;
617 String scoreAux = null;
618 int score;
619
620 while (true) {
621 System.out.print("\nType album's score (0 to 10):\n> ");
622 scoreAux = reader.readLine();
623 try {
624 score = Integer.parseInt(scoreAux);
625 if (score >= 0 && score <= 10) {
626 break;
627 } else {
628 System.out.println("That input is invalid");
629 continue;
630 }
631 } catch (NumberFormatException e) {
632 System.out.println("That input is invalid");
633 continue;
634 }
635 }
636
637 System.out.print("\nWrite review (300 characters):\n> ");
638 String reviewAux = reader.readLine();
639 if (reviewAux.length() > 300)
640 review = reviewAux.substring(0, 300);
641 else
642 review = reviewAux;
643
644 String [] result = new String[2];
645 result[0] = review;
646 result[1] = String.valueOf(score);
647
648 return result;
649 }
650
651 public static void reviewAlbum() throws IOException, SQLException {
652 System.out.print("\nType album's name:\n> ");
653 String album = reader.readLine();
654 String query= "select n_album, album.nome, artista.nome\n"+
655 "from album, artista\n"+
656 "where n_artista=artista_n_artista and album.nome = '" + album + "';";
657 ResultSet resAux= stmt.executeQuery(query);
658
659 Boolean auxb = true;
660 int i=1;
661
662 ArrayList <int []> albuns = new ArrayList ();
663
664 while (resAux.next()) {
665 if(auxb)
666 System.out.println();
667
668 auxb = false;
669 System.out.println(i + ". " + resAux.getString(2) + " by " + resAux.getString(3));
670 i++;
671
672 int [] aux = new int [2];
673 aux[0] = i;
674 aux[1] = resAux.getInt(1);
675 albuns.add(aux);
676 }
677
678 if(auxb){
679 System.out.println("Album name doesn't exist");
680 }
681 else{
682 System.out.print(">");
683 Scanner sc = new Scanner(System.in);
684 int op = sc.nextInt();
685
686 int id_a = 0;
687
688 for (int j=0; j<albuns.size(); j++){
689 if (op+1 == albuns.get(j)[0]){
690 id_a = albuns.get(j)[1];
691 }
692 }
693
694 String [] aux = writeReview();
695 String review = aux[0];
696 String score = aux[1];
697
698 //defenir a id da critica que tem de ser unica
699 String querySelect = "select max(n_critica)\n"+
700 "from critica;";
701 ResultSet resSelect = stmt.executeQuery(querySelect);
702
703 int id_c = 0;
704 if(resSelect.next()) {
705 id_c = resSelect.getInt(1) + 1;
706 }
707
708 //inserir a critica
709 String query1 = "INSERT INTO public.critica(\n" +
710 "\tn_critica, pontuacao, critica, utilizador_username)\n" +
711 "\tVALUES ('" + id_c + "','" + score + "','" + review + "','" + utilizadorUsername + "');";
712 stmt.executeUpdate(query1);
713
714 String query2 = "INSERT INTO public.album_critica(\n" +
715 "\talbum_n_album, critica_n_critica)\n" +
716 "\tVALUES ('" + id_a +"','" + id_c + "');";
717 stmt.executeUpdate(query2);
718
719 System.out.println("Review added");
720 }
721 }
722
723 public static void reviewSong() throws IOException, SQLException {
724 System.out.print("\nType song's name:\n> ");
725 String song = reader.readLine();
726 String query= "select musica.n_musica, musica.nome, artista.nome\n"+
727 "from artista, musica, artista_musica\n"+
728 "where n_artista = artista_n_artista and n_musica = musica_n_musica and musica.nome = '" + song + "'"+
729 "union select musica.n_musica, musica.nome, artista.nome "+
730 "from musica, album, album_musica, artista "+
731 "where album.artista_n_artista= artista.n_artista AND musica.n_musica=album_musica.musica_n_musica AND album.n_album=album_musica.album_n_album "+
732 "AND musica.nome= '"+song+"';";
733 ResultSet resAux= stmt.executeQuery(query);
734
735 Boolean auxb = true;
736 int i=1;
737
738 ArrayList <int []> songs = new ArrayList ();
739
740 while (resAux.next()) {
741 if(auxb) {
742 System.out.println();
743 }
744
745 auxb = false;
746 System.out.println(i + ". " + resAux.getString(2) + " by " + resAux.getString(3));
747 i++;
748
749 int [] aux = new int [2];
750 aux[0] = i;
751 aux[1] = resAux.getInt(1);
752 songs.add(aux);
753 }
754
755 if(auxb){
756 System.out.println("Song name doesn't exist");
757 }
758 else{
759 System.out.print(">");
760 Scanner sc = new Scanner(System.in);
761
762 int op = sc.nextInt();
763
764 int id_a = 0;
765
766 for (int j=0; j<songs.size(); j++){
767 System.out.println(songs.get(j)[0] + ";" + songs.get(j)[1]);
768 if (op+1 == songs.get(j)[0])
769 id_a = songs.get(j)[1];
770 }
771
772 String [] aux = writeReview();
773 String review = aux[0];
774 String score = aux[1];
775
776 //defenir a id da critica que tem de ser unica
777 String querySelect = "select max(n_critica)\n"+
778 "from critica;";
779 ResultSet resSelect = stmt.executeQuery(querySelect);
780
781 int id_c = 0;
782 if(resSelect.next()) {
783 id_c = resSelect.getInt(1) + 1;
784 }
785
786 //inserir a critica
787 String query1 = "INSERT INTO public.critica(\n" +
788 "\tn_critica, pontuacao, critica, utilizador_username)\n" +
789 "\tVALUES ('" + id_c + "','" + score + "','" + review + "','" + utilizadorUsername + "');";
790 stmt.executeUpdate(query1);
791
792 String query2 = "INSERT INTO public.musica_critica(\n" +
793 "\tmusica_n_musica, critica_n_critica)\n" +
794 "\tVALUES ('" + id_a + "','" + id_c + "');";
795 stmt.executeUpdate(query2);
796
797 System.out.println("Review added");
798 }
799 }
800
801 public static void printSongInfo(ArrayList<Integer> songsFound) throws IOException, SQLException {
802 while(true) {
803 System.out.print("Option: ");
804 int option = Integer.parseInt(reader.readLine());
805 if (option <= songsFound.size() && option>0) {
806 int songNumber = songsFound.get(option - 1);
807
808 System.out.println();
809
810 String querySongInfo = "select musica.nome, musica.compositor, musica.duracao, editora.nome, musica.letra " +
811 "from musica, editora " +
812 "where editora.n_editora= musica.editora_n_editora AND musica.n_musica =" + songNumber + ";";
813 ResultSet res = stmt.executeQuery(querySongInfo);
814 if (res.next()) {
815 System.out.println("Song: " + res.getString(1) + "\n> Writer: " + res.getString(2) + "\n> Duration: " + res.getString(3) + "sec\n" +
816 "> Record Label: " + res.getString(4) + "\n> Lyrics: " + res.getString(5));
817 }
818 String queryReviews="select critica.pontuacao, critica.critica from critica, musica_critica "+
819 "where critica.n_critica= musica_critica.critica_n_critica "+
820 "AND musica_critica.musica_n_musica= "+songNumber+";";
821 res = stmt.executeQuery(queryReviews);
822 Boolean first=true;
823 while(res.next()){
824 if(first) {
825 System.out.println("Song's reviews:");
826 first = false;
827 }
828 System.out.println("> Score: "+res.getString(1));
829 System.out.println("> Review: "+res.getString(2));
830 }
831 break;
832 } else {
833 System.out.println("Invalid option, please try again");
834 }
835 }
836 }
837
838 public static void createPlaylist() throws SQLException, IOException {
839 //Condição, uma playlist tem uma ou mais músicas
840
841 String playlistName, option;
842
843 System.out.print("\nPlease, choose an option:\n1. Public\n2. Private\n>");
844 option = reader.readLine();
845 System.out.print("\nPlaylist name\n>");
846 playlistName= reader.readLine();
847
848 String queryVerify="select playlist.nome from playlist where utilizador_username= '"+utilizadorUsername+"'";
849 ResultSet res= stmt.executeQuery(queryVerify);
850 if(res.next())
851 System.out.println("You already have a playlist with that name");
852 else {
853 String playlistNumberAux= "select max(playlist.n_playlist) from playlist;";
854 res= stmt.executeQuery(playlistNumberAux);
855 int playlistNumber;
856 if(res.next()) playlistNumber= Integer.parseInt(res.getString(1))+1;
857 else playlistNumber=1;
858 String query = "INSERT INTO public.playlist(\n" +
859 "\tn_playlist, nome, utilizador_username)\n" +
860 "\tVALUES (" + playlistNumber + ", '" + playlistName + "','" + utilizadorUsername + "');";
861 stmt.executeUpdate(query);
862 if (option.equals("2")) {
863 String queryPrivate = "INSERT INTO public.playlist_privada_utilizador(\n" +
864 "\tplaylist_n_playlist, utilizador_username)\n" +
865 "\tVALUES (" + playlistNumber + ", '" + utilizadorUsername + "');";
866 stmt.executeUpdate(queryPrivate);
867 }
868 System.out.println("Please insert a song name to add to the new playlist");
869 String songName= reader.readLine();
870 int songNumber=searchSongByName(songName,"add playlist");
871 String queryInsertMusic = "INSERT INTO public.playlist_musica(\n" +
872 "\tplaylist_n_playlist, musica_n_musica)\n" +
873 "\tVALUES (" + playlistNumber + ", " + songNumber + ");";
874 stmt.executeUpdate(queryInsertMusic);
875 }
876 }
877
878 public static int getOwnPlaylistNumber(String playlistName) throws SQLException {
879 String query= "select playlist.n_playlist from playlist where playlist.utilizador_username='"+utilizadorUsername+"' AND playlist.nome='"+playlistName+"';";
880 ResultSet res= stmt.executeQuery(query);
881 //vai ser único
882 if(res.next()){
883 return Integer.parseInt(res.getString(1));
884 }
885 else return -1;
886
887 }
888 public static void showPlaylists() throws SQLException {
889 boolean first=true;
890
891 String query="select DISTINCT playlist.n_playlist, playlist.nome, playlist.utilizador_username "+
892 "from playlist, playlist_privada_utilizador "+
893 "where (playlist.n_playlist NOT IN (select playlist.n_playlist "+
894 "from playlist, playlist_privada_utilizador where playlist_privada_utilizador.playlist_n_playlist=playlist.n_playlist))"+
895 " OR (playlist_privada_utilizador.playlist_n_playlist=playlist.n_playlist AND playlist_privada_utilizador.utilizador_username= '"+utilizadorUsername+"');";
896 ResultSet res= stmt.executeQuery(query);
897 while(res.next()){
898 if(first) {
899 System.out.println("Playlists found in DropMusic:");
900 first = false;
901 }
902 System.out.println("> '"+res.getString(2)+"' created by "+res.getString(3));
903 int playlistNumber= Integer.parseInt(res.getString(1));
904 String querySongs= "select musica.nome "+
905 "from musica, playlist_musica "+
906 "where musica.n_musica= playlist_musica.musica_n_musica AND playlist_musica.playlist_n_playlist= "+playlistNumber+";";
907 ResultSet resAux= stmtAux.executeQuery(querySongs);
908 while(resAux.next()){
909 System.out.println(resAux.getString(1));
910 }
911 }
912 if(first) System.out.println("No playlists found in DropMusic");
913 }
914
915 public static void addSongToPlaylist() throws SQLException, IOException {
916 System.out.print("\nPlaylist name\n>");
917 String playlistName= reader.readLine();
918
919 int playlistNumber= getOwnPlaylistNumber(playlistName);
920 if(playlistNumber!=-1){
921 System.out.print("Please insert a song name to add to the new playlist\n>");
922 String songName= reader.readLine();
923 int songNumber=searchSongByName(songName, "add playlist");
924 if(songNumber!=-1) {
925 String queryInsertMusic = "INSERT INTO public.playlist_musica(\n" +
926 "\tplaylist_n_playlist, musica_n_musica)\n" +
927 "\tVALUES (" + playlistNumber + ", " + songNumber + ");";
928 stmt.executeUpdate(queryInsertMusic);
929 }
930
931 }
932 else System.out.println("Playlist not found or you're not the owner");
933 }
934
935 public static void sharePlaylist() throws IOException, SQLException {
936 System.out.print("\nPlease enter your playlist name\n>");
937 String playlistName= reader.readLine();
938
939 int playlistNumber= getOwnPlaylistNumber(playlistName);
940
941 if(playlistNumber!=-1){
942 listUsernames();
943 System.out.print("Enter the username\n>");
944 String username= reader.readLine();
945 String look = "select username from utilizador ;";
946 ResultSet res = stmt.executeQuery(look);
947
948 if (!res.next()) {
949 System.out.println("Username not found");
950 }
951 else {
952 String queryAddUser= "INSERT INTO public.playlist_privada_utilizador(\n" +
953 "\tplaylist_n_playlist, utilizador_username)\n" +
954 "\tVALUES ("+playlistNumber+",'"+username+"');";
955 stmt.executeUpdate(queryAddUser);
956 System.out.println("Playlist was successfully shared with " + username);
957 }
958 }
959 else System.out.println("Playlist not found or you're not the owner");
960 }
961
962 public static void insertArtist() throws IOException, SQLException {
963 System.out.print("\nType artist's name:\n> ");
964 String artist811 = reader.readLine();
965 System.out.print("\nType artist's history(description):\n> ");
966 String description811 = reader.readLine();
967 System.out.print("\nType artist's working period:\n> ");
968 String period811 = reader.readLine();
969
970 System.out.print("\nIs it a band(1) or solo artist(2)?\n>");
971 Scanner sc = new Scanner(System.in);
972 String op = sc.next();
973
974 String element = "";
975
976 String queryAux = "select max(n_artista)\n"+
977 "from artista";
978 ResultSet r = stmt.executeQuery(queryAux);
979
980 int id = 0;
981 if(r.next()) {
982 id = r.getInt(1) + 1;
983 }
984
985 switch (op) {
986 case "1":
987 System.out.print("\nWrite the band´s elements names.\nSeparate the elements names by ','\n>");
988 element = reader.readLine();
989 String query61 = "INSERT INTO public.artista(\n" +
990 "\tn_artista, nome, periodo, historia, banda_elementos, solo_nome)\n" +
991 "\tVALUES ('" + id + "', '" + artist811 + "', '" + period811 + "', '" + description811 + "', '" + element + "', ' ');";
992 stmt.executeUpdate(query61);
993 break;
994 case "2":
995 System.out.print("\nWrite solos artist's real name\n>");
996 element = reader.readLine();
997 String query6 = "INSERT INTO public.artista(\n" +
998 "\tn_artista, nome, periodo, historia, banda_elementos, solo_nome)\n" +
999 "\tVALUES ('" + id + "', '" + artist811 + "', '" + period811 + "', '" + description811 + "', ' ', '" + element + "');";
1000 stmt.executeUpdate(query6);
1001 break;
1002 }
1003
1004 System.out.println("To insert an Artist you have to also insert a song associated with that artist");
1005 insertSong(id);
1006 System.out.println("Artist was added to the date base");
1007 }
1008
1009 public static void insertSong(int id_art) throws IOException, SQLException {
1010 System.out.print("\nType artist's name:\n> ");
1011 String artist = reader.readLine();
1012
1013 System.out.print("\nNew information:");
1014 System.out.print("\nType song's name:\n> ");
1015 String song = reader.readLine();
1016 System.out.print("Type song's duration:\n> ");
1017 String duration = reader.readLine();
1018 System.out.print("Type song's composer:\n> ");
1019 String composer = reader.readLine();
1020 System.out.print("Type song's lyrics:\n> ");
1021 String lyrics = reader.readLine();
1022
1023 String queryRL = "select n_editora, nome\n"+
1024 "from editora";
1025 ResultSet rRL =stmt.executeQuery(queryRL);
1026
1027 int x=1;
1028 ArrayList <int []> editoras = new ArrayList<>();
1029 System.out.print("Add Record Label:\n");
1030 while (rRL.next()){
1031 System.out.println("> " + x + ". " + rRL.getString(2));
1032 x++;
1033
1034 int [] aux = new int [2];
1035
1036 aux[0] = x;
1037 aux[1] = rRL.getInt(1);
1038 editoras.add(aux);
1039 }
1040
1041 Scanner sc = new Scanner(System.in);
1042 int opRL = sc.nextInt();
1043 int id_e = 0;
1044 for(int k=0; k<editoras.size(); k++){
1045 if(opRL+1==editoras.get(k)[0])
1046 id_e = editoras.get(k)[1];
1047 }
1048
1049 String queryAux = "select max(n_musica)\n"+
1050 "from musica";
1051 ResultSet r = stmt.executeQuery(queryAux);
1052 int id = 0;
1053 if(r.next()) {
1054 id = r.getInt(1) + 1;
1055 }
1056
1057 String query = "INSERT INTO public.musica(\n" +
1058 "\tn_musica, nome, letra, duracao, compositor, editora_n_editora)\n" +
1059 "\tVALUES ('" + id + "','" + song + "','" + lyrics + "','" + duration + "','" + composer + "'," + id_e + ");";
1060 stmt.executeUpdate(query);
1061
1062 if (id_art!=0){
1063 String query1 = "INSERT INTO public.artista_musica(\n" +
1064 "\tartista_n_artista, musica_n_musica)\n" +
1065 "\tVALUES ('" + id_art + "','" + id + "');";
1066 stmt.executeUpdate(query1);
1067 }
1068 else{
1069 String querySelect = "select n_artista, nome, historia\n"+
1070 "from artista\n"+
1071 "where nome = '" + artist + "'";
1072 ResultSet rSelect = stmt.executeQuery(querySelect);
1073
1074 Boolean a = true;
1075 int i=1;
1076 ArrayList <int []> art = new ArrayList<>();
1077 while(rSelect.next()){
1078 a = false;
1079 System.out.println(i + ". " + rSelect.getString(2) + ", History: " + rSelect.getString(3));
1080 i++;
1081
1082 int [] aux = new int[2];
1083 aux[0] = i;
1084 aux[1] = rSelect.getInt(1);
1085 art.add(aux);
1086 }
1087
1088 if(a){
1089 System.out.println("Artist name doesn't exist");
1090 }
1091 else{
1092 System.out.print("Add to:\n>");
1093 int op = sc.nextInt();
1094
1095 for(int j=0; j<art.size(); j++){
1096 if(op+1==art.get(j)[0]){
1097 id_art = art.get(j)[1];
1098 }
1099 }
1100
1101 String query2 = "INSERT INTO public.artista_musica(\n" +
1102 "\tartista_n_artista, musica_n_musica)\n" +
1103 "\tVALUES ('" + id_art + "','" + id + "');";
1104 stmt.executeUpdate(query2);
1105 }
1106 }
1107
1108 String query21 = "INSERT INTO public.ficheiro(\n" +
1109 "\tuploaded_file, musica_n_musica)\n" +
1110 "\tVALUES (false, +'" + id + "');";
1111 stmt.executeUpdate(query21);
1112
1113
1114 System.out.println("Song was added to the database");
1115 }
1116
1117 public static void insertAlbum() throws IOException, SQLException {
1118 System.out.print("\nType artist's name:\n> ");
1119 String artist = reader.readLine();
1120
1121 System.out.print("\nNew information:\n");
1122 System.out.print("Type album's name:\n> ");
1123 String album = reader.readLine();
1124 System.out.print("Type album's genre:\n> ");
1125 String genre = reader.readLine();
1126 System.out.print("Type album's date:\n> ");
1127 String date = reader.readLine();
1128
1129 String queryAux = "select max(n_album)\n"+
1130 "from album";
1131 ResultSet r = stmt.executeQuery(queryAux);
1132 int id = 0;
1133 if(r.next()) {
1134 id = r.getInt(1) + 1;
1135 }
1136
1137 System.out.print("Add to:\n>");
1138 ArrayList <int []> art = auxDetailsArtist(artist);
1139
1140 int id_art = 0;
1141 if(!art.isEmpty()){
1142 Scanner sc = new Scanner(System.in);
1143 int op = sc.nextInt();
1144
1145 for(int j=0; j<art.size(); j++){
1146 if(op+1==art.get(j)[0])
1147 id_art = art.get(j)[1];
1148 }
1149 }
1150
1151 String query62 = "INSERT INTO public.album(\n" +
1152 "\tn_album, nome, genero, data_album, artista_n_artista)\n" +
1153 "\tVALUES (" + id +",'" + album + "','" + genre + "','" + date + "','" + id_art + "');";
1154 stmt.executeUpdate(query62);
1155
1156 String queryRL = "select n_editora, nome\n"+
1157 "from editora";
1158 ResultSet rRL =stmt.executeQuery(queryRL);
1159
1160 int i=1;
1161 ArrayList <int []> editoras = new ArrayList<>();
1162 System.out.print("Add Record Label:\n");
1163 while (rRL.next()){
1164 System.out.println("> " + i + ". " + rRL.getString(2));
1165 i++;
1166
1167 int [] aux = new int [2];
1168 aux[0] = i;
1169 aux[1] = rRL.getInt(1);
1170 editoras.add(aux);
1171 }
1172 Scanner sc = new Scanner(System.in);
1173 int opRL = sc.nextInt();
1174 int id_e = 0;
1175 for(int k=0; k<editoras.size(); k++){
1176 if(opRL+1==editoras.get(k)[0])
1177 id_e = editoras.get(k)[1];
1178 }
1179 String queryR = "INSERT INTO public.album_editora(\n" +
1180 "\talbum_n_album, editora_n_editora)\n" +
1181 "\tVALUES (" + id + ","+ id_e + ");";
1182 stmt.executeUpdate(queryR);
1183
1184 System.out.println("To insert an Album you have to also insert a song associated with that album");
1185 insertSong(id_art);
1186 System.out.println("Album was added to the database");
1187 }
1188
1189 public static void changeArtist() throws IOException, SQLException {
1190 System.out.print("\nType artist's name:\n> ");
1191 String artist = reader.readLine();
1192
1193 ArrayList <int []> art = auxDetailsArtist(artist);
1194
1195 if(!art.isEmpty()) {
1196 System.out.print("Option: ");
1197 Scanner sc = new Scanner(System.in);
1198 int op = sc.nextInt();
1199
1200 System.out.print("\nType artist's new name:\n> ");
1201 String newartist = reader.readLine();
1202 System.out.print("\nType artist's new history (description):\n> ");
1203 String newd = reader.readLine();
1204 System.out.print("\nType artist's new working period:\n> ");
1205 String newp = reader.readLine();
1206
1207 int id_art = 0;
1208 for(int j=0; j<art.size(); j++){
1209 if(op+1==art.get(j)[0]){
1210 id_art = art.get(j)[1];
1211 }
1212 }
1213
1214 System.out.print("\nIs it a band(1) or solo artist(2)?\n>");
1215 String op1 = sc.next();
1216 String element = "";
1217
1218 switch (op1) {
1219 case "1":
1220 System.out.print("\nWrite the band´s elements names.\nSeparate the elements names by ','\n>");
1221 element = reader.readLine();
1222 String query61 = "UPDATE public.artista\n" +
1223 "\tSET n_artista='" + id_art + "', nome='" + newartist + "',periodo='" + newp + "',historia='" + newd + "',banda_elementos='" + element + "', solo_nome=' '\n"+
1224 "\tWHERE n_artista='" + id_art + "';";
1225 stmt.executeUpdate(query61);
1226 break;
1227 case "2":
1228 System.out.print("\nWrite solos artist's real name\n>");
1229 element = reader.readLine();
1230 String query6 = "UPDATE public.artista\n" +
1231 "\tSET n_artista='" + id_art + "', nome='" + newartist + "',periodo='" + newp + "',historia='" + newd + "',banda_elementos=' ', solo_nome='" + element +"'\n"+
1232 "\tWHERE n_artista='" + id_art + "';";
1233 stmt.executeUpdate(query6);
1234 break;
1235 }
1236 }
1237 }
1238
1239 public static void changeAlbum() throws IOException, SQLException {
1240 System.out.print("\nType album's name:\n> ");
1241 String album = reader.readLine();
1242
1243 String querySelect = "select n_artista, artista.nome, n_album, album.nome\n"+
1244 "from artista, album\n"+
1245 "where album.nome = '" + album + "' and n_artista = artista_n_artista";
1246 ResultSet rSelect = stmt.executeQuery(querySelect);
1247
1248 Boolean ab = false;
1249 int i=1;
1250 ArrayList <int []> a = new ArrayList<>();
1251 while(rSelect.next()){
1252 ab = true;
1253
1254 System.out.println("> " + i + ". " + rSelect.getString(4) + ", By " + rSelect.getString(2));
1255 i++;
1256
1257 int [] aux = new int[3];
1258 aux[0] = i;
1259 aux[1] = rSelect.getInt(1);
1260 aux[2] = rSelect.getInt(3);
1261 a.add(aux);
1262 }
1263
1264 if(!ab) {
1265 System.out.println("Album name doesn't exist");
1266 }
1267
1268 if(!a.isEmpty()) {
1269 System.out.print("Option: ");
1270 Scanner sc = new Scanner(System.in);
1271 int op = sc.nextInt();
1272
1273 System.out.print("\nType album's new name:\n> ");
1274 String newalbum = reader.readLine();
1275 System.out.print("\nType album's new description:\n> ");
1276 String newdesc = reader.readLine();
1277 System.out.print("\nType album's new genre:\n> ");
1278 String newgenre = reader.readLine();
1279 System.out.print("\nType album's new date:\n> ");
1280 String newdate = reader.readLine();
1281
1282 int id_a = 0, id = 0;
1283 for(int j=0; j<a.size(); j++){
1284 if(op+1==a.get(j)[0]){
1285 id_a = a.get(j)[1];
1286 id = a.get(j)[2];
1287 }
1288 }
1289
1290 String query61 = "UPDATE public.album\n" +
1291 "\tSET n_album='" + id + "', nome='" + newalbum + "',genero='" + newgenre + "',data_album='" + newdate + "',artista_n_artista='" + id_a + "'\n"+
1292 "\tWHERE n_album='" + id + "';";
1293 stmt.executeUpdate(query61);
1294
1295 String queryRL = "select n_editora, nome\n"+
1296 "from editora";
1297 ResultSet rRL =stmt.executeQuery(queryRL);
1298
1299 int x=1;
1300 ArrayList <int []> editoras = new ArrayList<>();
1301 System.out.print("Add Record Label:\n");
1302 while (rRL.next()){
1303 System.out.println("> " + x + ". " + rRL.getString(2));
1304 x++;
1305
1306 int [] aux = new int [2];
1307 aux[0] = x;
1308 aux[1] = rRL.getInt(1);
1309 editoras.add(aux);
1310 }
1311
1312 int opRL = sc.nextInt();
1313 int id_e = 0;
1314 for(int k=0; k<editoras.size(); k++){
1315 if(opRL+1==editoras.get(k)[0])
1316 id_e = editoras.get(k)[1];
1317 }
1318
1319 String queryEditora = "UPDATE public.album_editora\n" +
1320 "\tSET album_n_album=" +id + ", editora_n_editora=" + id_e + "\n" +
1321 "\tWHERE editora_n_editora = " +id_e + ";";
1322 stmt.executeUpdate(queryEditora);
1323 }
1324 }
1325
1326 public static void changeSong() throws IOException, SQLException {
1327 System.out.print("\nType song's name:\n> ");
1328 String song = reader.readLine();
1329
1330 String querySelect= "select musica.n_musica, musica.nome, artista.nome\n"+
1331 "from artista, musica, artista_musica\n"+
1332 "where n_artista = artista_n_artista and n_musica = musica_n_musica and musica.nome = '" + song + "'"+
1333 "union select musica.n_musica, musica.nome, artista.nome "+
1334 "from musica, album, album_musica, artista "+
1335 "where album.artista_n_artista= artista.n_artista AND musica.n_musica=album_musica.musica_n_musica AND album.n_album=album_musica.album_n_album "+
1336 "AND musica.nome= '"+song+"';";
1337 ResultSet rSelect = stmt.executeQuery(querySelect);
1338
1339 Boolean ab = false;
1340 int i=1;
1341 ArrayList <int []> a = new ArrayList<>();
1342 while(rSelect.next()){
1343 ab = true;
1344 System.out.println("> " + i + ". " + rSelect.getString(2) + ", By " + rSelect.getString(3));
1345 i++;
1346
1347 int [] aux = new int[2];
1348 aux[0] = i;
1349 aux[1] = rSelect.getInt(1);
1350 a.add(aux);
1351 }
1352
1353 if(!ab) {
1354 System.out.println("Song name doesn't exist");
1355 }
1356
1357 if(!a.isEmpty()) {
1358 System.out.print("Option: ");
1359 Scanner sc = new Scanner(System.in);
1360 int op = sc.nextInt();
1361
1362 System.out.print("\nType song's new name:\n> ");
1363 String newsong = reader.readLine();
1364 System.out.print("\nType song's new duration:\n> ");
1365 String newdur = reader.readLine();
1366 System.out.print("\nType song's new composer:\n> ");
1367 String newcomp = reader.readLine();
1368 System.out.print("\nType song's new lyrics:\n> ");
1369 String newlyrics = reader.readLine();
1370
1371 String queryRL = "select n_editora, nome\n"+
1372 "from editora";
1373 ResultSet rRL =stmt.executeQuery(queryRL);
1374
1375 int x=1;
1376 ArrayList <int []> editoras = new ArrayList<>();
1377 System.out.print("Add Record Label:\n");
1378 while (rRL.next()){
1379 System.out.println("> " + x + ". " + rRL.getString(2));
1380 x++;
1381
1382 int [] aux = new int [2];
1383 aux[0] = x;
1384 aux[1] = rRL.getInt(1);
1385 editoras.add(aux);
1386 }
1387 int opRL = sc.nextInt();
1388 int id_e = 0;
1389 for(int k=0; k<editoras.size(); k++){
1390 if(opRL+1==editoras.get(k)[0])
1391 id_e = editoras.get(k)[1];
1392 }
1393
1394 int id_s = 0;
1395 for(int j=0; j<a.size(); j++){
1396 if(op+1==a.get(j)[0]){
1397 id_s = a.get(j)[1];
1398 }
1399 }
1400
1401 String query61 = "UPDATE public.musica\n" +
1402 "\tSET n_musica=" + id_s + ", nome='" + newsong + "',letra='" + newlyrics + "',duracao='" + newdur + "',compositor='" + newcomp + "' , editora_n_editora =" + id_e + "\n"+
1403 "\tWHERE n_musica='" + id_s + "';";
1404 stmt.executeUpdate(query61);
1405 }
1406 }
1407
1408 public static ArrayList<int[]> auxDetailsArtist(String artist) throws SQLException {
1409 String querySelect = "select n_artista, nome, historia\n"+
1410 "from artista\n"+
1411 "where nome = '" + artist + "'";
1412 ResultSet rSelect = stmt.executeQuery(querySelect);
1413
1414 Boolean a = false;
1415 int i=1;
1416 ArrayList <int []> art = new ArrayList<>();
1417 while(rSelect.next()){
1418 a = true;
1419
1420 System.out.println("> " + i + ". " + rSelect.getString(2) + ", History: " + rSelect.getString(3));
1421 i++;
1422
1423 int [] aux = new int[2];
1424 aux[0] = i;
1425 aux[1] = rSelect.getInt(1);
1426 art.add(aux);
1427 }
1428
1429 if(!a) {
1430 System.out.println("Artist name doesn't exist");
1431 return art;
1432 }
1433
1434 return art;
1435 }
1436
1437 public static void uploadSongFile() throws IOException, SQLException {
1438 System.out.println("Please insert a song name to upload");
1439 String songName= reader.readLine();
1440 int songNumber=searchSongByName(songName, "add playlist");
1441 if(songNumber!=-1) {
1442 String uploadCheck= "select uploaded_file from ficheiro where musica_n_musica="+songNumber+";";
1443 ResultSet res=stmt.executeQuery(uploadCheck);
1444 if(res.next()) System.out.println("Sorry, someone already uploaded a file for that song");
1445 else {
1446 String query = "INSERT INTO public.ficheiro(\n" +
1447 "\tuploaded_file, musica_n_musica)\n" +
1448 "\tVALUES (" + true + ", " + songNumber + ");";
1449 stmt.executeUpdate(query);
1450 String query1 = "INSERT INTO public.ficheiro_utilizador(\n" +
1451 "\tficheiro_musica_n_musica, utilizador_username)\n" +
1452 "\tVALUES (" + songNumber + ", '" + utilizadorUsername + "');";
1453 stmt.executeUpdate(query1);
1454 System.out.println("Song uploaded");
1455 }
1456 }
1457
1458 }
1459 public static void shareSongFile() throws SQLException, IOException {
1460
1461 String query= "select musica.n_musica, musica.nome from musica, ficheiro, ficheiro_utilizador "+
1462 "where ficheiro.uploaded_file=true AND musica.n_musica= ficheiro.musica_n_musica "+
1463 "AND ficheiro_utilizador.ficheiro_musica_n_musica=ficheiro.musica_n_musica "+
1464 "AND ficheiro_utilizador.utilizador_username='"+utilizadorUsername+"';";
1465 ResultSet res= stmt.executeQuery(query);
1466 int option=1;
1467 ArrayList<Integer> songsFound= new ArrayList<>();
1468 boolean first=true;
1469 while(res.next()){
1470 if(first){
1471 System.out.println("List of songs you have permission to share, please choose a number");
1472 first=false;
1473 }
1474 System.out.println(option+". "+res.getString(2));
1475 option++;
1476 songsFound.add(Integer.parseInt(res.getString(1)));
1477 }
1478 if(first) System.out.println("You don't have permission to share a song file with that name");
1479 else {
1480 String aux = reader.readLine();
1481
1482 if(Integer.parseInt(aux)<=songsFound.size() && Integer.parseInt(aux)>0) {
1483
1484 int songNumber = songsFound.get(Integer.parseInt(aux) - 1);
1485 listUsernames();
1486 System.out.println("Please enter a username");
1487 aux = reader.readLine();
1488 String usernameCheck = "select username from utilizador where username='" + aux + "';";
1489 res = stmt.executeQuery(usernameCheck);
1490 if (res.next()) {
1491 String auxx = "select ficheiro_utilizador.utilizador_username " +
1492 "from musica, ficheiro, ficheiro_utilizador " +
1493 "where ficheiro.uploaded_file=true AND musica.n_musica= ficheiro.musica_n_musica " +
1494 "AND ficheiro_utilizador.ficheiro_musica_n_musica=ficheiro.musica_n_musica " +
1495 "AND ficheiro_utilizador.utilizador_username='" + aux + "';";
1496 ResultSet resAux = stmt.executeQuery(auxx);
1497 if (resAux.next()) System.out.println("Song already shared with that user");
1498 else {
1499 String query1 = "INSERT INTO public.ficheiro_utilizador(\n" +
1500 "\tficheiro_musica_n_musica, utilizador_username)\n" +
1501 "\tVALUES (" + songNumber + ", '" + aux + "');";
1502 stmt.executeUpdate(query1);
1503 }
1504 } else System.out.println("Invalid username");
1505 } else System.out.println("Invalid option");
1506 }
1507 }
1508
1509 public static void printMySongsFiles() throws SQLException {
1510 String querySongs= "select musica.nome " +
1511 "from musica, ficheiro, ficheiro_utilizador " +
1512 "where ficheiro.uploaded_file=true AND musica.n_musica= ficheiro.musica_n_musica " +
1513 "AND ficheiro_utilizador.ficheiro_musica_n_musica=ficheiro.musica_n_musica " +
1514 "AND ficheiro_utilizador.utilizador_username='" + utilizadorUsername + "';";
1515 ResultSet res = stmt.executeQuery(querySongs);
1516 boolean first=true;
1517 while(res.next()){
1518 if(first){
1519 System.out.println("Your songs and songs shared with you");
1520 first=false;
1521 }
1522 System.out.println("> "+res.getString(1));
1523 }
1524 }
1525
1526 public static void listUsernames() throws SQLException {
1527 String query= "select utilizador.username from utilizador;";
1528 ResultSet res= stmt.executeQuery(query);
1529 boolean first=true;
1530 while(res.next()){
1531 if(first){
1532 System.out.println("Usernames found in DropMusic");
1533 first=false;
1534 }
1535 System.out.println("> "+res.getString(1));
1536 }
1537 }
1538
1539 public static void main(String[] args) {
1540 connectDataBase();
1541 //createTables();
1542 //insertData();
1543
1544 try {
1545 if (connection.createStatement() == null) {
1546 connection = DriverManager.getConnection(
1547 "jdbc:postgresql://127.0.0.1:5432/postgres",
1548 "postgres",
1549 "potato");
1550 }
1551
1552 if ((stmt = connection.createStatement()) == null) {
1553 System.out.println("Erro nao foi possÃvel criar uma statement ou retornou null");
1554 System.exit(-1);
1555 }
1556 if ((stmtAux = connection.createStatement()) == null) {
1557 System.out.println("Erro nao foi possÃvel criar uma statement ou retornou null");
1558 System.exit(-1);
1559 }
1560 //MENU
1561 try {
1562 login_register();
1563
1564 while (true) {
1565 System.out.println("\n1- Search song");
1566 System.out.println("2- Details of an album");
1567 System.out.println("3- Details of an artist");
1568 System.out.println("4- Write a review");
1569 System.out.println("5- Manage song files");
1570 System.out.println("6- Playlists");
1571 if (isEditor(utilizadorUsername)) {
1572 System.out.println("7- Manage information");
1573 System.out.println("8- Make an user a new editor");
1574 }
1575 System.out.println("0- Logout");
1576 System.out.print("> ");
1577 Scanner sc = new Scanner(System.in);
1578 String num = sc.next();
1579 switch (num) {
1580 case "1":
1581 System.out.println("\n1- Trough artist");
1582 System.out.println("2- Trough album");
1583 System.out.println("3- By song name");
1584 System.out.println("4- By genre");
1585 System.out.println("5- By date");
1586 System.out.print("6- By score\n>");
1587 String aux1 = sc.next();
1588 switch (aux1) {
1589 case "1":
1590 System.out.print("\nType artist's name:\n> ");
1591 String artist13 = reader.readLine();
1592 searchSongByArtist(artist13);
1593 continue;
1594
1595 case "2":
1596 searchSongByAlbum();
1597 continue;
1598
1599 case "3":
1600 System.out.print("\nType song's name:\n> ");
1601 String song13 = reader.readLine();
1602 searchSongByName(song13, "");
1603 continue;
1604
1605 case "4":
1606 searchSongByGenre();
1607 continue;
1608
1609 case "5":
1610 searchSongByDate();
1611 continue;
1612
1613 case "6":
1614 searchSongByScore();
1615 continue;
1616
1617 }
1618 case "2":
1619 System.out.print("\nType album's name:\n> ");
1620 String album2 = reader.readLine();
1621 detailsOfAnAlbum(album2);
1622 continue;
1623
1624 case "3":
1625 detailsArtist();
1626 continue;
1627
1628 case "4":
1629 System.out.println("\n1- Album");
1630 System.out.print("2- Song\n>");
1631 String aux4 = sc.next();
1632
1633 switch (aux4){
1634 case "1":
1635 reviewAlbum();
1636 continue;
1637
1638 case "2":
1639 reviewSong();
1640 continue;
1641
1642 default:
1643 continue;
1644 }
1645 case "5":
1646 System.out.println("\n1- Upload song");
1647 System.out.println("\n2- Share song");
1648 System.out.print("3- Show my songs\n>");
1649 String aux5 = sc.next();
1650
1651 switch (aux5){
1652 case "1":
1653 uploadSongFile();
1654 continue;
1655
1656 case "2":
1657 shareSongFile();
1658 continue;
1659
1660 case "3":
1661 printMySongsFiles();
1662 continue;
1663
1664 default:
1665 continue;
1666 }
1667
1668 case "6":
1669 System.out.println("\n1- Create playlist");
1670 System.out.println("2- Add user to playlist");
1671 System.out.println("3- Add song to playlist");
1672 System.out.print("4- Print all available playlists\n>");
1673 String aux6 = sc.next();
1674 switch (aux6) {
1675 case "1":
1676 createPlaylist();
1677 continue;
1678
1679 case "2":
1680 sharePlaylist();
1681 continue;
1682
1683 case "3":
1684 addSongToPlaylist();
1685 continue;
1686
1687 case "4":
1688 showPlaylists();
1689 continue;
1690
1691 default:
1692 continue;
1693
1694 }
1695
1696 case "7":
1697 if (true) {
1698 System.out.println("\n1- Insert");
1699 System.out.print("2- Change\n> ");
1700 String aux8 = sc.next();
1701 switch (aux8) {
1702 case "1":
1703 System.out.println("\n1- Artist");
1704 System.out.println("2- Album");
1705 System.out.print("3- Song\n> ");
1706 String aux81 = sc.next();
1707 switch (aux81) {
1708 case "1":
1709 insertArtist();
1710 continue;
1711
1712 case "2":
1713 insertAlbum();
1714 continue;
1715
1716 case "3":
1717 insertSong(0);
1718 continue;
1719
1720 default:
1721 continue;
1722 }
1723 case "2":
1724 System.out.println("\n1- Artist");
1725 System.out.println("2- Album");
1726 System.out.print("3- Song\n> ");
1727 String aux82 = sc.next();
1728 switch (aux82) {
1729 case "1":
1730 changeArtist();
1731 continue;
1732
1733 case "2":
1734 changeAlbum();
1735 continue;
1736
1737 case "3":
1738 changeSong();
1739 continue;
1740
1741 default:
1742 continue;
1743 }
1744 default:
1745 continue;
1746 }
1747 } else {
1748 continue;
1749 }
1750 case "8":
1751 makeEditor();
1752 continue;
1753
1754 case "0":
1755 System.out.println("Goodbye â¤");
1756 System.exit(0);
1757 default:
1758 continue;
1759 }
1760 }
1761 }
1762 catch (IOException e) {
1763 System.out.println("Input null");
1764 main(args);
1765 }
1766 }
1767 catch (SQLException e) {
1768 e.printStackTrace();
1769 }
1770 }
1771}