import java.sql.*;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class DBSUebung {
	/**
	 * Get a new database connection
	 *
	 * @return
	 * @throws SQLException
	 */
	public Connection getConnection() throws SQLException {
		String jdbcClassName="com.ibm.db2.jcc.DB2Driver";
		String url="jdbc:db2://localhost:50001/DB2DB1";
		String user="db2inst1";
		String password="ws2011";

		Connection conn = null;

		try {
			//Load class into memory
			Class.forName(jdbcClassName);
			//Establish connection
			conn = DriverManager.getConnection(url, user, password);

		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return conn;
	}

	/**
	 * Run a SQL command which does not return a recordset:
	 * CREATE/INSERT/UPDATE/DELETE/DROP/etc.
	 *
	 * @throws SQLException If something goes wrong
	 */
	public ResultSet executeQuery(Connection conn, String command) throws SQLException {
	    Statement stmt = null;
	    ResultSet rs = null;
	    try {
	        stmt = conn.createStatement();
	        rs = stmt.executeQuery(command); // This will throw a SQLException if it fails
	    } finally {

	    	// This will run whether we throw an exception or not
	        //if (stmt != null) { stmt.close(); }
	    }

	    return rs;
	}

	/**
	 * Connect to MySQL and do some stuff.
	 */
	public void run() throws SQLException {

		// Connect to MySQL
		Connection conn = null;
		conn = this.getConnection();

		ResultSet rs = null;
		ResultSet rs_inner = null;

		String inputstr = "Schwarz";

		System.out.println("MOVIES");

		rs = this.executeQuery(conn, "SELECT DISTINCT * FROM movie m WHERE title LIKE '%" + inputstr +  "%' ORDER BY title asc");

		while(rs.next()) {
			System.out.print(rs.getString("title") + ", " + rs.getString("year"));

			//genre
			rs_inner = this.executeQuery(conn, "SELECT genre FROM genre WHERE movie_id = '" + rs.getString("mid") + "'");

			while(rs_inner.next()) {
				System.out.print(", " + rs_inner.getString("genre"));
			}

			System.out.println("\r");

			//actor
			// TODO: Limit 5
			rs_inner = this.executeQuery(conn, "SELECT * FROM ((SELECT * FROM actor) UNION (SELECT * FROM actress)) act WHERE movie_id = '" + rs.getString("mid") + "'  ORDER BY act.name asc FETCH FIRST 5 ROWS ONLY");

			while(rs_inner.next()) {
				System.out.println("	" + rs_inner.getString("name"));
			}

			System.out.println("\r");
		}






		ResultSet rs_movies = null;
	    System.out.println("ACTORS");

		rs = this.executeQuery(conn, "SELECT DISTINCT name FROM ((SELECT * FROM actor) UNION (SELECT * FROM actress)) act WHERE name LIKE '%" + inputstr +  "%'");

		while(rs.next()) {
			System.out.println(rs.getString("name"));

			// played in
			System.out.println("	PLAYED IN");

			rs_movies = this.executeQuery(conn, "SELECT * FROM movie m JOIN ((SELECT * FROM actor) UNION (SELECT * FROM actress)) act ON m.mid = act.movie_id WHERE act.name = '" + rs.getString("name") + "'");

			while(rs_movies.next()) {
				System.out.println("		" + rs_movies.getString("title"));
			}

			// Co-stars
			// TODO: Anzahl der Filme, in dem beide mitgespielt haben, sortiert nach I. Anzahl II. Nam
			System.out.println("	CO-STARS");

			rs_inner = this.executeQuery(conn, "SELECT name, COUNT(movie_id) as amount FROM ((SELECT * FROM actor) UNION (SELECT * FROM actress)) act WHERE act.movie_id IN (SELECT movie_id FROM ((SELECT * FROM actor) UNION (SELECT * FROM actress)) act WHERE name LIKE '%" + inputstr +  "%' AND name <> '" + rs.getString("name") + "') GROUP BY name ORDER BY amount desc, act.name asc FETCH FIRST 5 ROWS ONLY");

			while(rs_inner.next()) {
				System.out.println("		" + rs_inner.getString("name") + " (" + rs_inner.getString("amount") + ")");
			}

			System.out.println("\r");

		}
	}

	/**
	 * Connect to the DB and do some stuff
	 */
	public static void main(String[] args) throws SQLException {
		DBSUebung app = new DBSUebung();
		app.run();
	}
}