Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- package DatabaseAssignment2;
- import java.io.FileInputStream;
- import java.io.FileNotFoundException;
- import java.sql.Connection;
- import java.sql.DriverManager;
- import java.sql.ResultSet;
- import java.sql.Statement;
- import java.sql.SQLException;
- import java.util.Scanner;
- import oracle.jdbc.*;
- import oracle.net.aso.s;
- import oracle.xdb.XMLType;
- public class DatabaseAssignment2 {
- private static String username, password, hostname, serviceName;
- private static Connection conn;
- private static Scanner userInput;
- public static void main(String[] args) throws SQLException {
- // TODO code application logic here
- loadOracleJdbcDriver();
- connectToServer(username, password, hostname, serviceName);
- //Statement s = conn.createStatement();
- }
- public static void createMenu(Connection conn) throws SQLException {
- try {
- conn = DriverManager.getConnection("jdbc"
- + ":oracle:thin:"
- + getUsername() + "/" + getPassword() + "@" + getHostname() + ":1521/"
- + getServicename());
- } catch (SQLException e) {
- System.out.println("Error:" + e.toString());
- }
- StringBuilder sb = new StringBuilder();
- int choice = 0;
- try {
- sb.append("\nOracle Film Database"
- + "\n1 - Get the titles of all films"
- + "\n2 - Get the titles of all films without the surrounding <title> tag"
- + "\n3 - Get the names of all actors who appeard in the Godfather"
- + "\n4 - Get the title and year of all crime films"
- + "\n5 - Get the title and year of all films in which Marlon Brando has "
- + "acted"
- + "\n6 - Get the titles of all films which have only one director"
- + "\n7 - Get the title and names of directors of all films which have more "
- + "than one director"
- + "\n8 - Get the names of persons who have acted in a film and also been "
- + "the sole director of the same film"
- + "\n9 - Exit Program");
- do {
- System.out.println(sb.toString());
- System.out.println("Enter Option (1-9)");
- Scanner userInput = new Scanner(System.in);
- choice = userInput.nextInt();
- switch (choice) {
- case 1:
- showTitleOfAllFilms(conn);
- break;
- case 2:
- showTitleOfAllFilmWithoutTitleTags(conn);
- break;
- case 3:
- showNamesOfActorsWhoAppearedInGodfather(conn);
- break;
- case 4:
- showTitleAndYearOfAllCrimeFilms(conn);
- break;
- case 5:
- showTitleAndYearOfAllFilmsMarlonBrando(conn);
- break;
- /* case 6:
- getOneDirectorFilms();
- break;
- case 7:
- getTitleAndNamesMoreThanOneDirector();
- break;
- case 8:
- getTheNamesOfActorsAndSoleDirectorOfSameFilm();
- break;
- */
- case 9:
- System.out.println("Exiting Program");
- System.exit(0);
- break;
- }
- } while (choice != 9);
- } catch (SQLException e) {
- System.out.println("Error - " + e.toString());
- }
- }
- public static void loadOracleJdbcDriver() {
- try {
- Class.forName("oracle.jdbc.driver.OracleDriver");
- } catch (ClassNotFoundException e) {
- System.out.println("Error -" + e.toString());
- System.out.println("Could not load the driver");
- }
- }
- public static void connectToServer(String username, String password,
- String hostname, String serviceName) throws SQLException {
- try {
- Scanner sc = new Scanner(System.in);
- System.out.println("Type userid, password, hostname, servicename: ");
- username = sc.next();
- setUsername(username);
- password = sc.next();
- setPassword(password);
- hostname = sc.next();
- setHostname(hostname);
- serviceName = "orcl";
- setServiceName(serviceName);
- System.out.println(username + " " + password + " " + hostname);
- System.out.println("Connecting to Database...");
- // userid, password and hostname are obtained from the console
- //try-with-resources
- try (
- Connection conn = DriverManager.getConnection("jdbc"
- + ":oracle:thin:"
- + username + "/" + password + "@" + hostname + ":1521/"
- + serviceName)) {
- createFilmTable(conn);
- populateFilmTable(conn);
- createMenu(conn);
- showTitleOfAllFilms(conn);
- showTitleOfAllFilmWithoutTitleTags(conn);
- showNamesOfActorsWhoAppearedInGodfather(conn);
- showTitleAndYearOfAllCrimeFilms(conn);
- showTitleAndYearOfAllFilmsMarlonBrando(conn);
- conn.setAutoCommit(false);
- }
- } catch (SQLException e) {
- System.out.println("Error + " + e.toString());
- System.out.println("Connection Failure");
- }
- }
- public static void createFilmTable(Connection conn) throws SQLException {
- Statement s = conn.createStatement();
- s.executeUpdate("DROP TABLE ASS2_FILM");
- System.out.println("Table Dropped");
- s.executeUpdate("CREATE TABLE ASS2_FILM(fnum VARCHAR(20) "
- + "CONSTRAINT fnum_PK PRIMARY KEY, "
- + " film SYS.XMLTYPE)");
- System.out.println("Made");
- }
- public static void populateFilmTable(Connection conn) throws SQLException {
- Statement s = conn.createStatement();
- try {
- FileInputStream fis = new FileInputStream("src/DatabaseAssignment2/film.xml");
- XMLType xmlv = new XMLType(conn, fis);
- System.out.println(xmlv.getStringVal());
- //String sqltxt = "INSERT INTO ASS2_FILM VALUES (?, ?)";
- s.executeUpdate("INSERT INTO ASS2_FILM VALUES( '01', "
- + "sys.XMLType.createXML( ' " + xmlv.getStringVal() + " ' ))");
- FileInputStream fis2 = new FileInputStream("src/DatabaseAssignment2/film2.xml");
- XMLType xmlv2 = new XMLType(conn, fis2);
- System.out.println(xmlv2.getStringVal());
- //String sqltxt = "INSERT INTO ASS2_FILM VALUES (?, ?)";
- s.executeUpdate("INSERT INTO ASS2_FILM VALUES( '02', "
- + "sys.XMLType.createXML( ' " + xmlv2.getStringVal() + " ' ))");
- FileInputStream fis3 = new FileInputStream("src/DatabaseAssignment2/film3.xml");
- XMLType xmlv3 = new XMLType(conn, fis3);
- System.out.println(xmlv.getStringVal());
- //String sqltxt = "INSERT INTO ASS2_FILM VALUES (?, ?)";
- s.executeUpdate("INSERT INTO ASS2_FILM VALUES( '03', "
- + "sys.XMLType.createXML( ' " + xmlv3.getStringVal() + " ' ))");
- FileInputStream fis4 = new FileInputStream("src/DatabaseAssignment2/film4.xml");
- XMLType xmlv4 = new XMLType(conn, fis4);
- System.out.println(xmlv.getStringVal());
- //String sqltxt = "INSERT INTO ASS2_FILM VALUES (?, ?)";
- s.executeUpdate("INSERT INTO ASS2_FILM VALUES( '04', "
- + "sys.XMLType.createXML( ' " + xmlv4.getStringVal() + " ' ))");
- System.out.println("Rows added to table----------------------------------------");
- } catch (FileNotFoundException ex) {
- System.out.println("Error + " + ex.toString());
- System.out.println("Connection Failure");
- }
- }
- public static void showTitleOfAllFilms(Connection conn) throws SQLException {
- Statement s = conn.createStatement();
- ResultSet result = s.executeQuery("SELECT a.fnum, a.film.extract('/film/title').getStringval() "
- + "FROM ASS2_FILM a ");
- System.out.println("\n Fnum and /Film: \n");
- while (result.next()) {
- System.out.println(result.getString(1) + " " + result.getString(2));
- }
- }
- public static void showTitleOfAllFilmWithoutTitleTags(Connection conn) throws SQLException {
- Statement s = conn.createStatement();
- ResultSet result = s.executeQuery("SELECT a.fnum, a.film.extract('film//title/text()').getStringval() "
- + "FROM ASS2_FILM a ");
- System.out.println("\n Fnum and /Title: \n");
- while (result.next()) {
- System.out.println(result.getString(1) + " " + result.getString(2));
- }
- }
- public static void showNamesOfActorsWhoAppearedInGodfather(Connection conn) throws SQLException {
- Statement s = conn.createStatement();
- ResultSet result = s.executeQuery("SELECT a.fnum, a.film.extract('film//actor/text()').getStringval() "
- + "FROM ASS2_FILM a "
- + "WHERE a.film.extract('film//title/text()').getStringval() = 'The Godfather'");
- System.out.println("\n Fnum and /Actors: \n");
- while (result.next()) {
- System.out.println(result.getString(1) + " " + result.getString(2));
- }
- }
- public static void showTitleAndYearOfAllCrimeFilms(Connection conn) throws SQLException {
- Statement s = conn.createStatement();
- ResultSet result = s.executeQuery("SELECT a.film.extract('/film/title/@year').getStringval, "
- + "a.film.extract('/film/title/text()').getStringval() "
- + "FROM ASS2_FILM a "
- + "WHERE a.film.extract('/film//genres/genre[1]/text()').getStringval() = 'Crime'");
- System.out.println("\n Film Title and Year: \n");
- while (result.next()) {
- System.out.println(result.getString(1) + " " + result.getString(2));
- }
- }
- public static void showTitleAndYearOfAllFilmsMarlonBrando(Connection conn) throws SQLException {
- Statement s = conn.createStatement();
- ResultSet result = s.executeQuery("SELECT a.film.extract('/film/title/@year').getStringval, "
- + "a.film.extract('/film/title/text()').getStringval() "
- + "FROM ASS2_FILM a "
- + "WHERE a.film.extract('/film//cast/performer[1]/actor/text()').getStringval() = 'Marlon Brando'");
- System.out.println("\n Film Title and Year: \n");
- while (result.next()) {
- System.out.println(result.getString(1) + " " + result.getString(2));
- }
- }
- public static void showOneDirectorFilms() {
- }
- public static void showTitleAndNamesMoreThanOneDirector() {
- }
- public static void ShowTheNamesOfActorsAndSoleDirectorOfSameFilm() {
- }
- public static String getUsername() {
- return username;
- }
- public static void setUsername(String username) {
- DatabaseAssignment2.username = username;
- }
- public static String getPassword() {
- return password;
- }
- public static void setPassword(String password) {
- DatabaseAssignment2.password = password;
- }
- public static String getHostname() {
- return hostname;
- }
- public static void setHostname(String hostname) {
- DatabaseAssignment2.hostname = hostname;
- }
- public static String getServicename() {
- return serviceName;
- }
- public static void setServiceName(String serviceName) {
- DatabaseAssignment2.serviceName = serviceName;
- }
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement