// # Execute:
// javac ImdbSuche.java
// java ImdbSuche

import java.sql.*;
import java.util.*;

public class ImdbSuche {

	static Connection con;

	public static void log (Object line) {
		System.out.println(line);
	}

	public static void main (String[] args) {

		// Defaults
		String databaseName = "db2db1";
		String serverIp = "192.168.189.128";
		String port = "50001";
		String username = "db2inst1";
		String password = "ws2011";
		String keyword = "Schwarz";

		// No switch to increase compatibility (switch needs jdk > 7)
		for (int i = 0; i < args.length; i++) {
			if (args[i].equals("-d"))
				databaseName = args[i + 1];

			else if (args[i].equals("-s"))
				serverIp = args[i + 1];

			else if (args[i].equals("-p"))
				port = args[i + 1];

			else if (args[i].equals("-u"))
				username = args[i + 1];

			else if (args[i].equals("-pw"))
				password = args[i + 1];

			else if (args[i].equals("-k"))
				keyword = args[i + 1];
		}

		connect(
			serverIp,
			port,
			databaseName,
			username,
			password
		);

		Dictionary[] movies = queryDbForMoviesWithKeyword(keyword);

		log("MOVIES");

		for (int i = 0; i < movies.length; i++) {

			log(
				movies[i].get("TITLE") +
				", " +
				movies[i].get("YEAR") +
				movies[i].get("GENRE")
			);

			Dictionary[] actors = queryDbForActorsWithMovie(
				movies[i].get("TITLE").toString()
			);

			for (int j = 0; j < actors.length; j++) {
				log("\t" + actors[j].get("NAME"));
			}
		}

		log("\nACTORS");

		Dictionary[] actors = queryDbForActorsWithKey(keyword);

		for (int i = 0; i < actors.length; i++) {
			log(actors[i].get("NAME"));
			log("\tPLAYED IN");

			Dictionary[] films = queryDbForFilmsWithActor(
				actors[i]
					.get("NAME")
					.toString()
			);

			for (int j = 0; j < films.length; j++) {
				log("\t\t" + films[j].get("TITLE"));
			}

			log("\tCO-STARS IN");

			Dictionary[] costars = queryDbForCoStarsWithActor(
				actors[i]
					.get("NAME")
					.toString()
			);

			for (int j = 0; j < costars.length; j++) {
				log(
					"\t\t" +
					costars[j].get("NAME") +
					" (" + costars[j].get("ANZAHL") +
					")"
				);
			}
		}
	}

	@SuppressWarnings("unchecked")

	public static Dictionary[] queryDbForMoviesWithKeyword (String key) {
		String query = "SELECT DISTINCT title, year, mid" +
			"FROM movie " +
			"WHERE title " +
			"LIKE '%" + key + "%' " +
			"ORDER BY title ASC";

		Dictionary[] movies = query(query);

		for (Dictionary movie : movies) {
			query = "SELECT DISTINCT genre " +
				"FROM genre " +
				"WHERE movie_id " +
				"LIKE '" + movie.get("MID") + "%' " +
				"ORDER BY genre ASC";

			Dictionary[] genresResult = query(query);

			String genreString = "";

			for (int i = 0; i < genresResult.length; i++)
				genreString += ", " +
					((String) genresResult[i].get("GENRE"));

			movie.put("GENRE", genreString);
		}

		return movies;
	}

	public static Dictionary[] queryDbForActorsWithMovie(String key) {
		String query = "SELECT DISTINCT name " +
			"FROM (" +
				"(SELECT name FROM Actor " +
					"WHERE movie_id LIKE '%" + key + "%' ORDER BY name ASC) " +
				"UNION " +
				"(SELECT name FROM Actress " +
					"WHERE movie_id LIKE '%" + key + "%' ORDER BY name ASC)) " +
			"ORDER BY name ASC FETCH FIRST 5 ROWS ONLY";

		Dictionary[] actors = query(query);
		return actors;
	}

	public static Dictionary[] queryDbForActorsWithKey (String key) {
		String query = "SELECT DISTINCT name " +
			"FROM " +
				"((SELECT name FROM Actor " +
					"WHERE name LIKE '%" + key + "%' ORDER BY name ASC) " +
					"UNION " +
					"(SELECT name FROM Actress " +
						"WHERE name LIKE '%" + key + "%' ORDER BY name ASC)) " +
			"ORDER BY name ASC";

		Dictionary[] actors = query(query);
		return actors;
	}

	public static Dictionary[] queryDbForFilmsWithActor (String key) {
		String query = "SELECT DISTINCT title, mid " +
			"FROM " +
				"((SELECT name, movie_id FROM Actor " +
					"WHERE name LIKE '%" + key + "%' ORDER BY name ASC) " +
				"UNION " +
				"(SELECT name, movie_id FROM Actress " +
					"WHERE name LIKE '%" + key + "%' ORDER BY name ASC)) " +
			"AS actors, movie " +
			"WHERE movie.mid = actors.movie_id " +
			"ORDER BY title ASC";

		Dictionary[] films = query(query);
		return films;
	}

	public static Dictionary[] queryDbForCoStarsWithActor (String key) {
		String query = "SELECT name, COUNT(*) AS Anzahl " +
			"FROM (SELECT mid FROM " +
				"((SELECT name, movie_id FROM Actor " +
					"WHERE name LIKE '%" + key + "%' ORDER BY name ASC) " +
				"UNION " +
				"(SELECT name, movie_id FROM Actress " +
					"WHERE name LIKE '%" + key + "%' ORDER BY name ASC)) " +
			"AS actors, movie WHERE movie.mid = actors.movie_id) " +
			"AS movies, ((SELECT * FROM actor WHERE name NOT LIKE '"+key+"') " +
			"UNION (SELECT * FROM actress WHERE name NOT LIKE '"+key+"')) " +
			"AS actors WHERE movies.mid = actors.movie_id GROUP " +
			"BY actors.name ORDER BY Anzahl, Name FETCH FIRST 5 ROWS ONLY";
		Dictionary[] films = query(query);
		return films;
	}


	public static void connect(
			String ip,
			String port,
			String db_name,
			String user,
			String password
		){

		try {
			Class
				.forName("com.ibm.db2.jcc.DB2Driver")
				.newInstance();

			String URL = "jdbc:db2://" + ip + ":" + port + "/" + db_name;

			Connection con = DriverManager.getConnection(URL, user, password);

			ImdbSuche.con = con;
		}
		catch (Exception error) {
			log(error);
		}
	}

	@SuppressWarnings("unchecked")

	public static Dictionary[] query (String query) {

		ArrayList<Dictionary> list = new ArrayList<Dictionary>();

		try {
			/*
			// Slow
			Statement stmt = con.createStatement();
			ResultSet resultSet = stmt.executeQuery(query);
			*/

			// Fast
			PreparedStatement pstmt = con.prepareStatement(query);
			ResultSet resultSet = pstmt.executeQuery();

			while (resultSet.next()) {
				// Put it into a dictionary
				ResultSetMetaData rsmd = resultSet.getMetaData();
				int columnCount = rsmd.getColumnCount();

				Dictionary dict = new Hashtable();

				for (int i = 1; i <= columnCount; i++) {
					String name = rsmd.getColumnName(i);
					dict.put(name, resultSet.getString(i));
				}

				list.add(dict);
			}
		}
		catch (Exception error) {
			log(error);
		}

		return list.toArray(new Dictionary[list.size()]);
	}
}

Keyword = Prior

java ImdbSuche \
	-d db2db1 \
	-s 192.168.189.128 \
	-p 50001 \
	-u db2inst1 \
	-pw ws2011 \
	-k Prior

MOVIES

ACTORS
Prior, Herbert
	PLAYED IN
		Martyrdom of Philip Strong, The
		Next
		Unsullied Shield, An
	CO-STARS IN
	Connery, Van (1)
	Cooper, Bigelow (1)
	Dawley, Janet (1)
	Edwin, Walter (1)
	Eytinge, Harry (1)

Prior, Tom
	PLAYED IN
		Warning: Parental Advisory
	CO-STARS IN
		Adair, Dwight (1)
		Amiss, Susannah (1)
		Beatty, Jim (1)
		Berryman, Joe (1)
		Born, David (1)

Keyword = Schwarz

java ImdbSuche \
	-d db2db1 \
	-s 192.168.189.128 \
	-p 50001 \
	-u db2inst1 \
	-pw ws2011 \
	-k Schwarz

MOVIES
Tatort - Schwarzer Advent, 1998, Crime
	Berkel, Christian
	Erhart, Peter
	Fitz, Michael (I)
	Grosser, Renate
	Hartmann, Alexandra

ACTORS
Schwarz, Josef
	PLAYED IN
		Meister Eder und sein Pumuckl
	CO-STARS IN
		Ackermann, Kathrin (1)
		Astor, Eva (1)
		Basedow, Rainer (1)
		Baumgartner, Monika (1)
		Bayrhammer, Gustl (1)
Schwarz, Norbert
	PLAYED IN
		Es geht seinen Gang oder M?hen in unserer Ebene
	CO-STARS IN
		Bathke, Wolfgang (1)
		Bauer, Reinhard Vom (1)
		Behrends, Ela (1)
		Branding, Heinz Theo (1)
		Gissel, Henning (1)
Schwarzkopf, Klaus
	PLAYED IN
		Meister Eder und sein Pumuckl
	CO-STARS IN
		Ackermann, Kathrin (1)
		Astor, Eva (1)
		Basedow, Rainer (1)
		Baumgartner, Monika (1)
		Bayrhammer, Gustl (1)

Anmerkung: Die Co-Stars sollten zuerst nach der Anzahl gemeinsamer Filme sortiert werden.