API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Dec 19th, 2018
91
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 73.25 KB | None | 0 0
raw download clone embed print report
  1. import java.io.BufferedReader;
  2. import java.io.IOException;
  3. import java.io.InputStreamReader;
  4. import java.sql.*;
  5. import java.util.ArrayList;
  6. import java.util.Scanner;
  7.  
  8. public 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. }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!