Assignment2.java

import java.util.ArrayList;
import java.sql.*;
import java.util.Collections;

public class Assignment2 {

	/* A connection to the database */
	private Connection connection;

	/**
	 * Empty constructor. There is no need to modify this. 
	 */
	public Assignment2() {
		try {
			Class.forName("org.postgresql.Driver");
		} catch (ClassNotFoundException e) {
			System.err.println("Failed to find the JDBC driver");
		}
	}

	/**
	* Establishes a connection to be used for this session, assigning it to
	* the instance variable 'connection'.
	*
	* @param  url       the url to the database
	* @param  username  the username to connect to the database
	* @param  password  the password to connect to the database
	* @return           true if the connection is successful, false otherwise
	*/
	public boolean connectDB(String url, String username, String password) {
		try {
			this.connection = DriverManager.getConnection(url, username, password);
			return true;
		} catch (SQLException se) {
			System.err.println("SQL Exception. <Message>: " + se.getMessage());
			return false;
		}
	}

	/**
	* Closes the database connection.
	*
	* @return true if the closing was successful, false otherwise
	*/
	public boolean disconnectDB() {
		try {
			this.connection.close();
		return true;
		} catch (SQLException se) {
			System.err.println("SQL Exception. <Message>: " + se.getMessage());
			return false;
		}
	}

	/**
	 * Returns a sorted list of the names of all musicians and bands 
	 * who released at least one album in a given genre. 
	 *
	 * Returns an empty list if no such genre exists or no artist matches.
	 *
	 * NOTE:
	 *    Use Collections.sort() to sort the names in ascending
	 *    alphabetical order.
	 *
	 * @param genre  the genre to find artists for
	 * @return       a sorted list of artist names

		QUERY:
		
		SELECT Artist.name FROM Artist,Album,Genre 
		WHERE Album.artist_id = Artist.artist_id 
		AND Genre.genre_id = Album.genre_id 
		AND Genre.genre = INPUT;	 
	 
	 */
	public ArrayList<String> findArtistsInGenre(String genre) {
		
		StringBuffer query = new StringBuffer();
		//Create the query to execute		
		StringBuffer setup = new StringBuffer();
		setup.append("SET search_path TO artistdb;");
		query.append("SELECT DISTINCT Artist.name FROM Artist, Album, Genre WHERE Album.artist_id = Artist.artist_id AND Genre.genre_id = Album.genre_id AND Genre.genre = '");
		query.append(genre);
		query.append("';");

		//Execute the Query
		try{
			this.connection.prepareStatement(setup.toString()).execute();
			ResultSet rs = this.connection.prepareStatement(query.toString()).executeQuery();
			ArrayList<String> result = new ArrayList<String>();
			while(rs.next()){
				result.add(rs.getObject(1).toString());
			}
			Collections.sort(result);
			return result;
	
		}catch (SQLException se) {
			System.err.println("SQL Exception. <Message>: " + se.getMessage());
			return null;
		}
	}

	/**
	 * Returns a sorted list of the names of all collaborators
	 * (either as a main artist or guest) for a given artist.  
	 *
	 * Returns an empty list if no such artist exists or the artist 
	 * has no collaborators.
	 *
	 * NOTE:
	 *    Use Collections.sort() to sort the names in ascending
	 *    alphabetical order.
	 *
	 * @param artist  the name of the artist to find collaborators for
	 * @return        a sorted list of artist names
	 
	 QUERY:
			 
	 SELECT A1.name, A2.name FROM Artist as A1, Artist as A2, Collaboration as C, WHERE A1.artist_id != A2.artist_id AND A1.artist_id = C.artist1 AND A2.artist_id = C.artist2 
	 
	 */
	public ArrayList<String> findCollaborators(String artist) {
		StringBuffer setup = new StringBuffer();
		StringBuffer query = new StringBuffer();
		setup.append("SET search_path TO artistdb;");
		query.append("SELECT DISTINCT A1.name, A2.name FROM Artist as A1, Artist as A2, Collaboration as C WHERE A1.artist_id != A2.artist_id AND A1.artist_id = C.artist1 AND A2.artist_id = C.artist2 AND (A1.name = '");
		query.append(artist);
		query.append("' OR A2.name = '");
		query.append(artist);
		query.append("');");
		try{
			this.connection.prepareStatement(setup.toString()).execute();
			ResultSet rs = this.connection.prepareStatement(query.toString()).executeQuery();
			ArrayList<String> result = new ArrayList<String>();
			while(rs.next()){
				String a = rs.getObject(1).toString();
				String b = rs.getObject(2).toString();
				 				
				result.add(a.equals(artist) ? b : a);
			}
			Collections.sort(result);
			return result;
		}catch (SQLException se){
			System.err.println("SQL Exception. <Message>: " + se.getMessage());
			return null;
		}
	}


	/**
	 * Returns a sorted list of the names of all songwriters
	 * who wrote songs for a given artist (the given artist is excluded).  
	 *
	 * Returns an empty list if no such artist exists or the artist 
	 * has no other songwriters other than themself.
	 *
	 * NOTE:
	 *    Use Collections.sort() to sort the names in ascending
	 *    alphabetical order.
	 *
	 * @param artist  the name of the artist to find the songwriters for
	 * @return        a sorted list of songwriter names
	 
	 
SELECT SW.name 
FROM Artist as SW, Song, Album, BelongsToAlbum, Artist as Singer
WHERE Singer.name = INPUT 
AND 	SW.artist_id != Singer.artist_id
AND 	SW.artist_id = Song.songwriter_id 
AND	Album.artist_id = Singer.artist_id
AND 	Song.song_id = BelongsToAlbum.song_id
AND	Album.album_id = BelongsToAlbum.album_id
	 */
	public ArrayList<String> findSongwriters(String artist) {
		StringBuffer setup = new StringBuffer();
		StringBuffer query = new StringBuffer();
		setup.append("SET search_path TO artistdb;");
		query.append("SELECT SW.name FROM Artist as SW, Song, Album, BelongsToAlbum, Artist as Singer WHERE Singer.name = '");
		query.append(artist);  
		query.append("' AND SW.artist_id != Singer.artist_id AND SW.artist_id = Song.songwriter_id AND Album.artist_id = Singer.artist_id AND Song.song_id = BelongsToAlbum.song_id AND	Album.album_id = BelongsToAlbum.album_id");		
		try{
			this.connection.prepareStatement(setup.toString()).execute();
			ResultSet rs = this.connection.prepareStatement(query.toString()).executeQuery();
			ArrayList<String> result = new ArrayList<String>();
			while(rs.next()){
				result.add(rs.getObject(1).toString());
			}
			Collections.sort(result);
			return result;
		}catch (SQLException se){
			System.err.println("SQL Exception. <Message>: " + se.getMessage());
			return null;
		}
	}

	/**
	 * Returns a sorted list of the names of all acquaintances
	 * for a given pair of artists.  
	 *
	 * Returns an empty list if either of the artists does not exist, 
	 * or they have no acquaintances.
	 *
	 * NOTE:
	 *    Use Collections.sort() to sort the names in ascending
	 *    alphabetical order.
	 *
	 * @param artist1  the name of the first artist to find acquaintances for
	 * @param artist2  the name of the second artist to find acquaintances for
	 * @return         a sorted list of artist names
	 */
	public ArrayList<String> findAcquaintances(String artist1, String artist2) {
		ArrayList<String> collabs1 = this.findCollaborators(artist1);
		ArrayList<String> sw1 = this.findSongwriters(artist1);
		ArrayList<String> collabs2 = this.findCollaborators(artist2);
		ArrayList<String> sw2 = this.findSongwriters(artist2);
		
		ArrayList<String> commCollabs = new ArrayList<String>();
		for(int i = 0; i < collabs1.size(); ++i){
			if(collabs2.contains(collabs1.get(i))) commCollabs.add(collabs1.get(i));		
		}
		ArrayList<String> commAcqs = new ArrayList<String>();
		for(int i = 0; i < sw1.size(); ++i){
			if(sw2.contains(sw1.get(i))) commAcqs.add(sw2.get(i));		
		}
		
		for(int i = 0; i < commCollabs.size(); ++i){
			if(!commAcqs.contains(commCollabs.get(i))) commAcqs.add(commCollabs.get(i));	
		}		
		
		return commAcqs;				
		
	}
	
	
	public static void main(String[] args) {
		
		Assignment2 a2 = new Assignment2();
		
		/* TODO: Change the database name and username to your own here. */
		a2.connectDB("jdbc:postgresql://localhost:5432/csc343h-c5shinwo",
		             "c5shinwo",
		             "");
		
                System.err.println("\n----- ArtistsInGenre -----");
                ArrayList<String> res = a2.findArtistsInGenre("Rock");
                if(res == null){
                	System.err.println("res set is empty");}
                for (String s : res) {
                  System.err.println(s);
                }

		System.err.println("\n----- Collaborators -----");
		res = a2.findCollaborators("Michael Jackson");
		for (String s : res) {
		  System.err.println(s);
		}
		
		System.err.println("\n----- Songwriters -----");
	        res = a2.findSongwriters("Justin Bieber");
		for (String s : res) {
		  System.err.println(s);
		}
		
		System.err.println("\n----- Acquaintances -----");
		res = a2.findAcquaintances("Jaden Smith", "Miley Cyrus");
		for (String s : res) {
		  System.err.println(s);
		}

		
		a2.disconnectDB();
	}
}