public static void exec(String command) {
   try {
     Runtime r = Runtime.getRuntime();
     Process p = r.exec(command);
     p.waitFor();
   } catch (Exception e) {
     Debug.println("erreur d'execution " + command + e.toString());
   }
 }
  static void loadData(String uname, String pword) throws Exception {
    System.out.println("Data load started..\n");
    PreparedStatement dropTrans = con.prepareStatement("drop table Trans");
    PreparedStatement dropItems = con.prepareStatement("drop table Items");
    PreparedStatement dropFISet = con.prepareStatement("drop table FISet");
    PreparedStatement dropPairs = con.prepareStatement("drop table pairs");
    PreparedStatement dropTempFISet = con.prepareStatement("drop table TempFISet");
    PreparedStatement dropTemp = con.prepareStatement("drop table temp");
    PreparedStatement dropTempset = con.prepareStatement("drop table tempset");
    PreparedStatement dropTempright = con.prepareStatement("drop table tempright");
    PreparedStatement dropArtable = con.prepareStatement("drop table artable");
    PreparedStatement createTrans =
        con.prepareStatement("CREATE TABLE TRANS (TRANSID NUMBER,ITEMID NUMBER)");
    PreparedStatement createItems =
        con.prepareStatement("CREATE TABLE ITEMS(ITEMID NUMBER,ITEMNAME VARCHAR2(100))");
    PreparedStatement createFISet =
        con.prepareStatement("CREATE TABLE FISet(ISetID NUMBER,ITEMID NUMBER,percent NUMBER)");
    PreparedStatement createPairs =
        con.prepareStatement("create table pairs(id1 number,id2 number)");
    PreparedStatement createTempFISet =
        con.prepareStatement("CREATE TABLE TempFISet(ISetid number,itemid number,percent number)");
    PreparedStatement createTemp = con.prepareStatement("CREATE TABLE Temp (ITEMID NUMBER)");
    PreparedStatement createTempset = con.prepareStatement("CREATE TABLE tempset (ITEMID NUMBER)");
    PreparedStatement createTempright =
        con.prepareStatement("CREATE TABLE tempright (ITEMID NUMBER)");
    PreparedStatement createArtable =
        con.prepareStatement(
            "CREATE TABLE artable(ruleid NUMBER,itemid NUMBER,itemname VARCHAR2(100 BYTE),confidence NUMBER,isleft CHAR(1 BYTE),support NUMBER)");

    try {
      dropTrans.executeUpdate();
    } catch (Exception e) {

    }
    try {

      dropItems.executeUpdate();
    } catch (Exception e) {

    }
    try {
      dropFISet.executeUpdate();
    } catch (Exception e) {

    }
    try {
      dropPairs.executeUpdate();
    } catch (Exception e) {

    }
    try {
      dropTempset.executeUpdate();
    } catch (Exception e) {

    }
    try {
      dropTempright.executeUpdate();
    } catch (Exception e) {

    }
    try {
      dropArtable.executeUpdate();
    } catch (Exception e) {

    }
    try {
      dropTempFISet.executeUpdate();
    } catch (Exception e) {

    }
    try {
      dropTemp.executeUpdate();
    } catch (Exception e) {

    }
    con.commit();

    createTrans.executeUpdate();
    createItems.executeUpdate();
    createFISet.executeUpdate();
    createPairs.executeUpdate();
    createTempFISet.executeUpdate();
    createTemp.executeUpdate();
    createTempset.executeUpdate();
    createTempright.executeUpdate();
    createArtable.executeUpdate();
    writeItemsControlFile();
    writeTransControlFile();
    String loadItemsString =
        "sqlldr " + uname.trim() + "/" + pword.trim() + "@orcl control=items.ctl";
    String loadTransString =
        "sqlldr " + uname.trim() + "/" + pword.trim() + "@orcl control=trans.ctl";

    Process p1 = Runtime.getRuntime().exec(loadItemsString);
    p1.waitFor();
    Process p2 = Runtime.getRuntime().exec(loadTransString);
    p2.waitFor();
    File f = new File("arm.sql");
    if (f.exists()) {
      Process p3 =
          Runtime.getRuntime()
              .exec("sqlplus " + uname.trim() + "@orcl/" + pword.trim() + " @arm.sql");
      p3.waitFor();
      con.commit();
      System.out.println("Data Load Completed...\n");
    } else {
      System.out.println(
          "The arm.sql file which has the stored procedures forthe project is not in the current directory");
      System.exit(0);
    }
  }
Exemple #3
0
  public static void main(String[] argv) throws Exception {
    String[] sArray = new String[8];
    long time = System.currentTimeMillis();
    try {
      FileInputStream fstream = new FileInputStream("system.in");
      DataInputStream in = new DataInputStream(fstream);
      BufferedReader br = new BufferedReader(new InputStreamReader(in));
      int i = 0;
      while ((sArray[i] = br.readLine()) != null) {
        sArray[i] = sArray[i].split("=")[1].trim();
        System.out.println(sArray[i]);
        i++;
      }
      in.close();
    } catch (Exception e) {

    }
    System.out.println("-------- Oracle JDBC Connection Testing ------");

    try {

      Class.forName("oracle.jdbc.driver.OracleDriver");

    } catch (ClassNotFoundException e) {

      System.out.println("Where is your Oracle JDBC Driver?");
      e.printStackTrace();
      return;
    }

    System.out.println("Oracle JDBC Driver Registered!");

    Connection connection = null;

    try {

      connection =
          DriverManager.getConnection(
              "jdbc:oracle:thin:@//oracle1.cise.ufl.edu:1521/orcl", sArray[0], sArray[1]);

    } catch (SQLException e) {

      System.out.println("Connection Failed! Check output console");
      e.printStackTrace();
      return;
    }
    ResultSet rset = null;
    if (connection != null) {
      System.out.println(" -|||- ");

      Process p =
          Runtime.getRuntime()
              .exec("sqlplus " + sArray[0] + "@orcl/" + sArray[1] + " @adwords.sql");
      p.waitFor();

      // CallableStatement cstmt;
      System.out.println(" ||| ");

      System.out.println(
          "Time taken in Milliseconds (establishing connection): "
              + (System.currentTimeMillis() - time));
      time = System.currentTimeMillis();

      Process proc3 =
          Runtime.getRuntime()
              .exec(
                  "sqlldr "
                      + sArray[0]
                      + "/"
                      + sArray[1]
                      + "@orcl DATA=Keywords.dat CONTROL=Keywords.ctl LOG=Keywords.log");
      proc3.waitFor();

      Process proc1 =
          Runtime.getRuntime()
              .exec(
                  "sqlldr "
                      + sArray[0]
                      + "/"
                      + sArray[1]
                      + "@orcl DATA=Advertisers.dat CONTROL=Advertisers.ctl LOG=Advertiser.log");
      proc1.waitFor();

      Process proc2 =
          Runtime.getRuntime()
              .exec(
                  "sqlldr "
                      + sArray[0]
                      + "/"
                      + sArray[1]
                      + "@orcl DATA=Queries.dat CONTROL=Queries.ctl LOG=Queries.log");
      proc2.waitFor();
      System.out.println(" ||| ");

      System.out.println(
          "Time taken in Milliseconds (loading dat): " + (System.currentTimeMillis() - time));
      time = System.currentTimeMillis();

      CallableStatement callableStatement = null;
      String storeProc = "{call sq(?,?,?,?,?,?)}";

      try {
        callableStatement = connection.prepareCall(storeProc);
        callableStatement.setInt(1, Integer.parseInt(sArray[2]));
        callableStatement.setInt(2, Integer.parseInt(sArray[3]));
        callableStatement.setInt(3, Integer.parseInt(sArray[4]));
        callableStatement.setInt(4, Integer.parseInt(sArray[5]));
        callableStatement.setInt(5, Integer.parseInt(sArray[6]));
        callableStatement.setInt(6, Integer.parseInt(sArray[7]));
        callableStatement.executeUpdate();
      } catch (SQLException e) {
        System.out.println(e.getMessage());
      }

      /*callableStatement = connection.prepareCall("{call sq( " +
      Integer.parseInt(sArray[2]) + ", " +
      Integer.parseInt(sArray[3]) + ", " +
      Integer.parseInt(sArray[4]) + ", " +
      Integer.parseInt(sArray[5]) + ", " +
      Integer.parseInt(sArray[6]) + ", " +
      Integer.parseInt(sArray[7]) + " ) }");*/
      System.out.println(" ||| ");

      System.out.println(
          "Time taken in Milliseconds (sql procedure): " + (System.currentTimeMillis() - time));
      time = System.currentTimeMillis();

      FileWriter fw = null;
      File file = null;
      PrintWriter pw = null;
      Statement resStmt = null;
      ResultSet resRs = null;
      int qid = 0;
      int rank;

      for (int i = 0; i < 6; i++) {

        file = new File("system.out." + (i + 1));
        try {
          if (!file.exists()) file.createNewFile();
          else file.delete();

          fw = new FileWriter(file.getPath(), true);
        } catch (Exception e) {
          System.out.println(e.getMessage());
        }
        pw = new PrintWriter(fw);
        resStmt = connection.createStatement();
        resRs =
            resStmt.executeQuery(
                "SELECT * FROM OUTPUT" + (i + 1) + " order by qid asc, adrank asc");
        while (resRs.next()) {
          qid = resRs.getInt("QID");
          rank = (int) resRs.getFloat("ADRANK");
          int advertiserId = resRs.getInt("ADVERTISERID");
          float balance = resRs.getFloat("BALANCE");
          float budget = resRs.getFloat("BUDGET");
          StringBuffer resStr = new StringBuffer();
          resStr.append(qid + "," + rank + "," + advertiserId + "," + balance + "," + budget);
          // System.out.println(resStr.toString());
          pw.println(resStr.toString());
        }

        pw.close();
      }

    } else {
      System.out.println("Failed to make connection!");
    }

    System.out.println(
        "Time taken in Milliseconds (file write): " + (System.currentTimeMillis() - time));
  }