예제 #1
0
  public void connectItems(String query)
        // PRE:  query must be initialized
        // POST: Updates the list of Items based on the query.
      {
    String driver = "org.apache.derby.jdbc.ClientDriver"; // Driver for DB
    String url = "jdbc:derby://localhost:1527/ShopDataBase"; // Url for DB
    String user = "******"; // Username for db
    String pass = "******"; // Password for db
    Connection myConnection; // Connection obj to db
    Statement stmt; // Statement to execute a result appon
    ResultSet results; // A set containing the returned results
    int rowcount; // Num objects in the resultSet
    int i; // Index for the array

    try { // Try connection to db

      Class.forName(driver).newInstance(); // Create our db driver

      myConnection = DriverManager.getConnection(url, user, pass); // Initalize our connection

      stmt =
          myConnection.createStatement(
              ResultSet.TYPE_SCROLL_INSENSITIVE,
              ResultSet.CONCUR_UPDATABLE); // Create a new statement
      results = stmt.executeQuery(query); // Store the results of our query

      rowcount = 0;
      if (results.last()) // Go to the last result
      {
        rowcount = results.getRow();
        results.beforeFirst();
      }
      itemsArray = new Item[rowcount];

      i = 0;
      while (results.next()) // Itterate through the results set
      {
        itemsArray[i] =
            new Item(
                results.getInt("item_id"),
                results.getString("item_name"),
                results.getString("item_type"),
                results.getInt("price"),
                results.getInt("owner_id"),
                results.getString("item_path")); // Creat new Item
        i++;
      }

      results.close(); // Close the ResultSet
      stmt.close(); // Close the statement
      myConnection.close(); // Close the connection to db

    } catch (Exception e) // Cannot connect to db
    {
      System.err.println(e.toString());
      System.err.println("Error, something went horribly wrong in connectItems!");
    }
  }
예제 #2
0
  public SingleNameReport(WritableWorkbook workbook, Statement new_statement, String[] arguments) {

    String main_tsn = "";
    String main_rank = "";
    String main_kingdom = "";
    row = 0;
    try {
      statement = new_statement;
      func = new AdditionalFunctions(statement);
      copy = workbook;
      kingdom = arguments[0];
      hrank = arguments[1];
      lrank = arguments[2];
      scientificName = arguments[3];
      date_from = arguments[4];
      date_to = arguments[5];
      user = arguments[6];

      String temp = "";
      temp =
          "SELECT tsn,rank_id,kingdom_id from Tree where scientificName='" + scientificName + "'";
      System.out.println(temp);
      result = statement.executeQuery(temp);
      metadata = result.getMetaData();
      int numberOfRows = 0;
      int hrankVal = Integer.parseInt(hrank);
      int main_rankVal = 0;
      if (result.last()) numberOfRows = result.getRow();
      result.first();
      if (numberOfRows > 0 && metadata.getColumnCount() > 0) {
        main_tsn = result.getString(1);
        main_rank = result.getString(2);
        main_rankVal = Integer.parseInt(main_rank);
        main_kingdom = result.getString(3);
      }
      if (HasChildren(main_tsn) == true) FindChildInformation(main_tsn, arguments, func);

      FileInFrontPage frontPage = new FileInFrontPage(copy, statement, arguments);
      copy.write();
      copy.close();
    } catch (Exception e) {
      e.printStackTrace();
      System.exit(1);
    }
  } // end of constructor
예제 #3
0
  // public method that checks for existance of children
  public boolean HasChildren(String tsn) {

    String temp = "SELECT tsn from Tree where parent_tsn='" + tsn + "'";
    if (lrank.compareTo("0") != 0 && lrank.compareTo("7") != 0)
      temp = temp + " AND rank_id<=" + lrank;
    System.out.println(temp);
    int numberOfRows = 0;
    boolean flag = false;
    try {
      result = statement.executeQuery(temp);
      metadata = result.getMetaData();
      if (result.last()) numberOfRows = result.getRow();
      result.first();
      if (numberOfRows > 0 && metadata.getColumnCount() > 0) flag = true;
    } catch (SQLException sql) {
      sql.printStackTrace();
      System.exit(1);
    }
    return flag;
  } // end of method HasChildren
예제 #4
0
  public synchronized void service(HttpServletRequest request, HttpServletResponse response)
      throws IOException, ServletException {
    HttpSession dbSession = request.getSession();
    JspFactory _jspxFactory = JspFactory.getDefaultFactory();
    PageContext pageContext =
        _jspxFactory.getPageContext(this, request, response, "", true, 8192, true);
    ServletContext dbApplication = dbSession.getServletContext();

    nseer_db_backup1 stock_db = new nseer_db_backup1(dbApplication);

    try {
      if (stock_db.conn((String) dbSession.getAttribute("unit_db_name"))) {
        int i;
        int intRowCount;
        String sqll =
            "select * from stock_config_public_char where describe1='\u51fa\u5165\u5e93\u7406\u7531'";
        ResultSet rs = stock_db.executeQuery(sqll);
        rs.next();
        rs.last();
        intRowCount = rs.getRow();
        String[] del = new String[intRowCount];
        del = (String[]) dbSession.getAttribute("del");
        if (del != null) {
          for (i = 1; i <= intRowCount; i++) {
            String sql = "delete from stock_config_public_char where id='" + del[i - 1] + "'";
            stock_db.executeUpdate(sql);
          }
        }
        stock_db.commit();
        stock_db.close();
        response.sendRedirect("stock/config/apply_gather_pay/reason.jsp");
      } else {
        response.sendRedirect("error_conn.htm");
      }
    } catch (Exception ex) {
      ex.printStackTrace();
    }
  }
예제 #5
0
  // public method to extract and write the Publication information
  public static void WritePublication(String publicationId, int rowNum) {

    WritableSheet sheet2 = copy.getSheet(1);

    try {
      String query =
          "SELECT address,author,publicationTitle,chapter,edition,editor,month,day,note,number,publisher,"
              + "series,title,volume,year,isbn,issn,pages FROM Publication WHERE id="
              + publicationId;
      System.out.println(query);

      ResultSet result_query = statement.executeQuery(query);
      ResultSetMetaData metadata_query = result_query.getMetaData();
      int numberOfRows = 0;
      if (result_query.last()) numberOfRows = result_query.getRow();
      result_query.first();
      if (numberOfRows > 0 && metadata_query.getColumnCount() > 0) {
        String aut_edi = result_query.getString(2);
        System.out.println("autor is " + aut_edi);
        if (result_query.getString(6).compareTo("") != 0)
          aut_edi = aut_edi + " / " + result_query.getString(6) + ",eds.";
        Label label = new Label(7, rowNum, aut_edi);
        sheet2.addCell(label);
        label = new Label(8, rowNum, result_query.getString(13));
        sheet2.addCell(label);
        String publication_name = result_query.getString(3);
        if (result_query.getString(12).compareTo("") != 0)
          publication_name = publication_name + ", ser. " + result_query.getString(12);
        if (result_query.getString(14).compareTo("0") != 0)
          publication_name = publication_name + ", vol. " + result_query.getString(14);
        if (result_query.getString(10).compareTo("0") != 0)
          publication_name = publication_name + ",no. " + result_query.getString(10);
        label = new Label(9, rowNum, publication_name);
        sheet2.addCell(label);
        label = new Label(12, rowNum, result_query.getString(11));
        sheet2.addCell(label);
        label = new Label(13, rowNum, result_query.getString(1));
        sheet2.addCell(label);
        label = new Label(15, rowNum, result_query.getString(9));
        sheet2.addCell(label);
        label = new Label(16, rowNum, result_query.getString(16));
        sheet2.addCell(label);
        label = new Label(17, rowNum, result_query.getString(17));
        sheet2.addCell(label);
        label = new Label(14, rowNum, result_query.getString(18));
        sheet2.addCell(label);

        String date = result_query.getString(15);
        String month = "Jan";
        System.out.println("month is " + result_query.getString(7));
        if (result_query.getString(7).compareTo("") != 0) month = result_query.getString(7);
        month = func.ConvertMonth(month);
        date = month + "/" + date;
        String day = "01";
        if (result_query.getString(8).compareTo("0") != 0) day = result_query.getString(8);
        if (day.length() == 1) day = "0" + day;
        date = day + "/" + date;
        label = new Label(10, rowNum, date);
        sheet2.addCell(label);
        label = new Label(11, rowNum, date);
        sheet2.addCell(label);
      }
    } catch (Exception e) {
      e.printStackTrace();
      System.exit(1);
    }
  } // end of WritePublication
예제 #6
0
  // public method to find child information
  public void FindChildInformation(String tsn, String[] arguments, AdditionalFunctions func) {

    WritableSheet sheet2 = copy.getSheet(1);
    Label label = null;
    String children[] = null;
    String temp = "", current_tsn = "", dateCreated = "";
    int dateCheck = 1;
    temp = "SELECT distinct tsn from Tree where parent_tsn='" + tsn + "'";
    try {
      result = statement.executeQuery(temp);
      metadata = result.getMetaData();
      int numberOfRows = 0;
      if (result.last()) numberOfRows = result.getRow();
      if (numberOfRows > 0 && metadata.getColumnCount() > 0) {
        result.first();
        children = new String[numberOfRows];
        for (int j = 0; j < numberOfRows; j++) {
          children[j] = result.getString(1);
          result.next();
        }
      }
      for (int j = 0; j < numberOfRows; j++) {
        int current_tsnId = Integer.parseInt(children[j]);
        if (current_tsnId >= 999000000) {
          temp =
              "SELECT distinct Tree.tsn,unit_name1,unit_name2,unit_name3,unit_name4,scientificName,`usage`,"
                  + "taxon_author,parent_tsn,rank_name, pages, publicationId, userId, dateCreated, nameType FROM Tree,"
                  + " BaseObject, TaxonConcepts, TaxonAuthors,TaxonUnitTypes"
                  + " WHERE Tree.tsn=TaxonConcepts.tsn AND Tree.tsn='"
                  + current_tsnId
                  + "' AND "
                  + "BaseObject.id=TaxonConcepts.id AND TaxonAuthors.taxon_author_id=Tree.taxon_author_id AND "
                  + "Tree.rank_id=TaxonUnitTypes.rank_id";
          System.out.println(temp);

          result = statement.executeQuery(temp);
          metadata = result.getMetaData();
          int RowNumber = 0;
          if (result.last()) RowNumber = result.getRow();
          if (RowNumber > 0 && metadata.getColumnCount() > 0) {
            current_tsn = result.getString(1);
            dateCreated = result.getString(14);
            DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
            Date d1 = df.parse(dateCreated);
            Date d2 = null;
            if (date_from.compareTo("all") != 0) {
              d2 = df.parse(date_from);
              if (d2.before(d1) || d2.equals(d1)) dateCheck = 1;
              else dateCheck = 0;
            }
            if (date_to.compareTo("today") != 0) {
              d2 = df.parse(date_to);
              if (d1.before(d2) || d1.equals(d2)) dateCheck = 1;
              else dateCheck = 0;
            }
            if (result.getString(7).compareTo("public") == 0
                && result.getString(15).compareTo("Regular scientific name") == 0
                && result.getString(12).compareTo("") != 0
                && dateCheck == 1) {

              String scientificName = result.getString(6);
              String taxon_author = result.getString(8);
              String publicationId = result.getString(12);
              for (int k = 0; k < 4; k++) {
                if (result.getString(k + 2).compareTo("null") != 0) {
                  label = new Label(k, row + 1, result.getString(k + 2));
                  sheet2.addCell(label);
                }
              }
              label = new Label(4, row + 1, taxon_author);
              sheet2.addCell(label);
              label = new Label(5, row + 1, result.getString(10));
              sheet2.addCell(label);

              String parent_tsn = result.getString(9);
              String userId = result.getString(13);
              WritePublication(publicationId, row + 1);
              label = new Label(18, row + 1, func.GetSubmitter(userId));
              sheet2.addCell(label);
              label = new Label(6, row + 1, func.GetParent(parent_tsn));
              sheet2.addCell(label);

              row = row + 1;
            }
          }
        }
        continue;
      }
      for (int j = 0; j < numberOfRows; j++) {
        if (HasChildren(children[j]) == true) FindChildInformation(children[j], arguments, func);
        else continue;
      }
    } catch (Exception e) {
      e.printStackTrace();
      System.exit(1);
    }
  } // end of FindChildInformation
예제 #7
0
  public void doPost(HttpServletRequest req, HttpServletResponse resp)
      throws ServletException, IOException {

    resp.setContentType("text/html");
    PrintWriter out = resp.getWriter();

    Connection con = null; // init DB objects
    PreparedStatement pstmt = null;
    Statement stmt = null;
    ResultSet rs = null;

    try {
      con = dbConn.Connect("demopaul");
    } catch (Exception ignore) {
    }

    String stype_id = req.getParameter("type_id");
    int type_id = 0;

    String sgroup_id = req.getParameter("group_id");
    int group_id = 0;

    String sitem_id = req.getParameter("item_id");
    int item_id = 0;

    try {
      type_id = Integer.parseInt(stype_id);
    } catch (NumberFormatException ignore) {
    }

    try {
      group_id = Integer.parseInt(sgroup_id);
    } catch (NumberFormatException ignore) {
    }

    try {
      item_id = Integer.parseInt(sitem_id);
    } catch (NumberFormatException ignore) {
    }

    out.println(
        "<!-- type_id=" + type_id + ", group_id=" + group_id + ", item_id=" + item_id + " -->");

    out.println("<script>");

    out.println("function load_types() {");
    out.println(" try {document.forms['frmSelect'].item_id.selectedIndex = -1; } catch (err) {}");
    out.println(" document.forms['frmSelect'].group_id.selectedIndex = -1;");
    out.println(" document.forms['frmSelect'].submit();");
    out.println("}");

    out.println("function load_groups() {");
    out.println(" document.forms['frmSelect'].submit();");
    out.println("}");

    out.println("</script>");

    out.println("<form name=frmSelect>");

    // LOAD ACTIVITY TYPES
    out.println("<select name=type_id onchange=\"load_types()\">");

    if (type_id == 0) {

      out.println("<option>CHOOSE TYPE</option>");
    }

    try {

      stmt = con.createStatement();

      rs = stmt.executeQuery("SELECT * FROM activity_types");

      while (rs.next()) {

        Common_Config.buildOption(rs.getInt("type_id"), rs.getString("type_name"), type_id, out);
      }
      stmt.close();

    } catch (Exception exc) {

      out.println("<p>ERROR:" + exc.toString() + "</p>");
    }

    out.println("");
    out.println("</select>");

    // LOAD ACTIVITIES BY GROUP TYPE
    out.println("<select name=group_id onchange=\"load_groups()\">");

    if (type_id == 0) {

      out.println("<option>CHOOSE TYPE</option>");

    } else {

      try {

        stmt = con.createStatement();
        rs =
            stmt.executeQuery(
                "SELECT group_id, group_name FROM activity_groups WHERE type_id = " + type_id);

        rs.last();
        if (rs.getRow() == 1) {
          group_id = rs.getInt("group_id");
          out.println("<!-- ONLY FOUND 1 GROUP -->");
        } else {
          out.println("<option value=\"0\">CHOOSE...</option>");
        }

        rs.beforeFirst();

        while (rs.next()) {

          Common_Config.buildOption(
              rs.getInt("group_id"), rs.getString("group_name"), group_id, out);
        }
        stmt.close();

      } catch (Exception exc) {

        out.println("<p>ERROR:" + exc.toString() + "</p>");
      }
    }

    out.println("");
    out.println("</select>");

    if (group_id > 0) { // || sitem_id != null

      // LOAD ACTIVITIES BY ITEM TYPE
      out.println("<select name=item_id onchange=\"load_times()\">");

      if (group_id == 0) {

        out.println("<option value=\"0\">CHOOSE GROUP</option>");

      } else {

        try {

          stmt = con.createStatement();
          rs =
              stmt.executeQuery(
                  "SELECT item_id, item_name FROM activity_items WHERE group_id = " + group_id);

          rs.last();
          if (rs.getRow() == 1) {
            item_id = rs.getInt("item_id");
            out.println("<!-- ONLY FOUND 1 ITEM -->");
          } else {
            out.println("<option value=\"0\">CHOOSE...</option>");
          }

          rs.beforeFirst();

          while (rs.next()) {

            Common_Config.buildOption(
                rs.getInt("item_id"), rs.getString("item_name"), item_id, out);
          }
          stmt.close();

        } catch (Exception exc) {

          out.println("<p>ERROR:" + exc.toString() + "</p>");
        }
      }

      out.println("");
      out.println("</select>");
    }

    out.println("</form>");

    out.println("<p><a href=\"Member_genrez\">Reset</a></p>");

    try {
      con.close();
    } catch (Exception ignore) {
    }

    out.close();
  }
예제 #8
0
  public static void createExternalTable(
      Connection conn,
      String osServer,
      String refreshType,
      String sourceTable,
      String targetSchema,
      String targetTable,
      String maxId,
      int queueId,
      int jobPort)
      throws SQLException {
    String method = "createExternalTable";
    int location = 1000;

    try {
      location = 2000;
      String externalTable = getExternalTableName(targetSchema, targetTable);

      location = 2100;
      Statement stmt = conn.createStatement();

      String createSQL =
          "CREATE EXTERNAL TABLE \"" + externalSchema + "\".\"" + externalTable + "\" \n (";

      location = 2309;
      String strSQL =
          "SELECT c.column_name, \n"
              + "       CASE WHEN c.data_type = 'character' THEN c.data_type || '(' || c.character_maximum_length || ')' ELSE c.data_type END AS data_type \n"
              + "FROM INFORMATION_SCHEMA.COLUMNS c \n"
              + "WHERE table_schema = '"
              + targetSchema
              + "' \n"
              + "       AND table_name = '"
              + targetTable
              + "' \n"
              + "ORDER BY ordinal_position";

      location = 2400;
      ResultSet rs = stmt.executeQuery(strSQL);

      location = 2500;
      while (rs.next()) {
        location = 2600;
        if (rs.getRow() == 1) {
          location = 2700;
          createSQL = createSQL + "\"" + rs.getString(1) + "\" " + rs.getString(2);
        } else {
          location = 2800;
          createSQL = createSQL + ", \n \"" + rs.getString(1) + "\" " + rs.getString(2);
        }
      }

      location = 2900;
      createSQL = createSQL + ") \n";

      ////////////////////////////////////////////
      // Create location for External Table
      ////////////////////////////////////////////
      location = 3000;
      // replace space in the maxId because this could now be a date
      maxId = maxId.replace(" ", "SPACE");

      location = 3100;
      String extLocation =
          "LOCATION ('gpfdist://"
              + osServer
              + ":"
              + jobPort
              + "/config.properties+"
              + queueId
              + "+"
              + maxId
              + "+"
              + refreshType
              + "+"
              + sourceTable
              + "#transform=externaldata"
              + "')";
      location = 3400;
      extLocation = extLocation + "\n" + "FORMAT 'TEXT' (delimiter '|' null 'null' escape '\\\\')";

      ////////////////////////////////////////////
      // Add createSQL with Java Command to exec.
      ////////////////////////////////////////////
      location = 3500;
      createSQL = createSQL + extLocation;

      ////////////////////////////////////////////
      // Create new external web table
      ////////////////////////////////////////////
      location = 4000;
      if (debug) Logger.printMsg("Creating External Table: " + createSQL);

      stmt.executeUpdate(createSQL);

    } catch (SQLException ex) {
      throw new SQLException(
          "(" + myclass + ":" + method + ":" + location + ":" + ex.getMessage() + ")");
    } catch (Exception e) {
      throw new SQLException(
          "(" + myclass + ":" + method + ":" + location + ":" + e.getMessage() + ")");
    }
  }