Created
June 26, 2016 09:39
-
-
Save nicoknoll/84e5fac7b58eed08fb7fd2b086731f5c to your computer and use it in GitHub Desktop.
Julius Übung DBS
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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(); | |
} | |
} | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment