// Fees update method
  private void updateFeesData(long id) {
    String columns[] = {"Course", "Fees Payed", "Total fees", "Installments"};
    try {
      Database db = new Database();

      panel_7.removeAll();

      feestablemodel = new MyTableModel(db.getFeeData(id), columns);

      feestable = new JTable(feestablemodel);
      feestable.setAutoResizeMode(JTable.AUTO_RESIZE_OFF);
      feestable.getSelectionModel().addListSelectionListener(this);

      feesscrollpane = new JScrollPane(feestable);
      panel_7.add(feesscrollpane);

      // change fees payed label
      feespayedlabel.setText("Fees Payed");
      feesduelabel.setText("Fees Due");
      totalfeeslabel.setText("Total Fees");

      panel_7.revalidate();
    } catch (Exception e) {
      JOptionPane.showMessageDialog(this, e.getMessage(), null, JOptionPane.ERROR_MESSAGE);
    }
  }
Ejemplo n.º 2
0
  /**
   * opens the chosen file, reads in the file, and prints out a receipt
   *
   * @param chosenFile
   */
  private void readSource(File chosenFile) {
    String chosenFileName = chosenFile.getName();
    TextFileInput inFile = new TextFileInput(chosenFileName);
    Container myContentPane = jframe.getContentPane();
    // chosenFile TextArea myTextArea = new TextArea();
    myContentPane.add(myTextArea);

    int count = 0;
    float priceTotal = 0.0f;
    Database db = new Database("database2.txt");
    String[] transaction = new String[100];
    String line = inFile.readLine();
    DecimalFormat df = new DecimalFormat("#00.00");
    while (line != null) {
      StringTokenizer tokenized = new StringTokenizer(line, ",");
      String code = tokenized.nextToken();
      float weight = Float.parseFloat(tokenized.nextToken());
      String name;
      float price;

      try {
        name = db.getName(code);

      } catch (ItemNotFoundException e) {
        name = JOptionPane.showInputDialog(null, "Item " + code + " not found. Enter Name: ");
      }
      try {
        price = db.getPrice(code);
      } catch (ItemNotFoundException e) {
        price =
            Float.valueOf(
                JOptionPane.showInputDialog(
                    null, "Price for " + name + " not found. Enter price: "));
      }
      float itemTotal = weight * price;
      priceTotal += itemTotal;
      transaction[count] =
          name + "\t" + price + "\t" + df.format(weight) + "\t $" + df.format(itemTotal);
      count++;
      line = inFile.readLine();
    } // while
    myTextArea.setText("ITEM: \t PRICE\\LB: \t POUNDS: \t TOTAL:");
    myTextArea.append("\n");
    for (int i = 0; i < count; i++) {
      myTextArea.append(transaction[i]);
      myTextArea.append("\n");
    }
    myTextArea.append("\t\t   TOTAL: $" + df.format(priceTotal));
    jframe.setVisible(true);
  } // openFile
  // Add fee method
  private void addFees() {
    try {
      float feesadded = Float.parseFloat(addfeestextfield.getText());

      int id = table.getSelectionModel().getMinSelectionIndex();
      long studentid = (long) table.getValueAt(id, 0);

      int index = feestable.getSelectionModel().getMinSelectionIndex();
      String coursename = (String) feestable.getValueAt(index, 0);

      Database db = new Database();
      db.addFees(feesadded, studentid, coursename);

      updateFeesData(studentid);

      addfeestextfield.setText("");
    } catch (Exception e) {
      JOptionPane.showMessageDialog(this, e.getMessage(), null, JOptionPane.ERROR_MESSAGE);
    }
  }
  // Add course method
  public void addCourse() {
    try {
      Database db = new Database();

      String coursename = (String) coursecombobox.getSelectedItem();
      if (coursecombobox.getSelectedIndex() == 0) {
        throw new Exception("No course selected");
      }

      float fees = db.getCoursefees(coursename);
      float totalfees = fees + (ims.main.Settings.getInstallment() * (int) spinner.getValue());

      long id = (long) table.getValueAt((int) table.getSelectionModel().getMinSelectionIndex(), 0);

      db.addCourseToCurrentStudent(id, totalfees, (int) spinner.getValue(), coursename);

      updateFeesData(id);
      courseReset();
    } catch (Exception e) {
      JOptionPane.showMessageDialog(this, e.getMessage(), null, JOptionPane.ERROR_MESSAGE);
    }
  }
Ejemplo n.º 5
0
  /**
   * Helper function to display a Swing window with a tree representation of the specified list of
   * joins. See {@link #orderJoins}, which may want to call this when the analyze flag is true.
   *
   * @param js the join plan to visualize
   * @param pc the PlanCache accumulated whild building the optimal plan
   * @param stats table statistics for base tables
   * @param selectivities the selectivities of the filters over each of the tables (where tables are
   *     indentified by their alias or name if no alias is given)
   */
  private void printJoins(
      Vector<LogicalJoinNode> js,
      PlanCache pc,
      HashMap<String, TableStats> stats,
      HashMap<String, Double> selectivities) {

    JFrame f = new JFrame("Join Plan for " + p.getQuery());

    // Set the default close operation for the window,
    // or else the program won't exit when clicking close button
    f.setDefaultCloseOperation(WindowConstants.DISPOSE_ON_CLOSE);

    f.setVisible(true);

    f.setSize(300, 500);

    HashMap<String, DefaultMutableTreeNode> m = new HashMap<String, DefaultMutableTreeNode>();

    // int numTabs = 0;

    // int k;
    DefaultMutableTreeNode root = null, treetop = null;
    HashSet<LogicalJoinNode> pathSoFar = new HashSet<LogicalJoinNode>();
    boolean neither;

    System.out.println(js);
    for (LogicalJoinNode j : js) {
      pathSoFar.add(j);
      System.out.println("PATH SO FAR = " + pathSoFar);

      String table1Name = Database.getCatalog().getTableName(this.p.getTableId(j.t1Alias));
      String table2Name = Database.getCatalog().getTableName(this.p.getTableId(j.t2Alias));

      // Double c = pc.getCost(pathSoFar);
      neither = true;

      root =
          new DefaultMutableTreeNode(
              "Join "
                  + j
                  + " (Cost ="
                  + pc.getCost(pathSoFar)
                  + ", card = "
                  + pc.getCard(pathSoFar)
                  + ")");
      DefaultMutableTreeNode n = m.get(j.t1Alias);
      if (n == null) { // never seen this table before
        n =
            new DefaultMutableTreeNode(
                j.t1Alias
                    + " (Cost = "
                    + stats.get(table1Name).estimateScanCost()
                    + ", card = "
                    + stats.get(table1Name).estimateTableCardinality(selectivities.get(j.t1Alias))
                    + ")");
        root.add(n);
      } else {
        // make left child root n
        root.add(n);
        neither = false;
      }
      m.put(j.t1Alias, root);

      n = m.get(j.t2Alias);
      if (n == null) { // never seen this table before

        n =
            new DefaultMutableTreeNode(
                j.t2Alias == null
                    ? "Subplan"
                    : (j.t2Alias
                        + " (Cost = "
                        + stats.get(table2Name).estimateScanCost()
                        + ", card = "
                        + stats
                            .get(table2Name)
                            .estimateTableCardinality(selectivities.get(j.t2Alias))
                        + ")"));
        root.add(n);
      } else {
        // make right child root n
        root.add(n);
        neither = false;
      }
      m.put(j.t2Alias, root);

      // unless this table doesn't join with other tables,
      // all tables are accessed from root
      if (!neither) {
        for (String key : m.keySet()) {
          m.put(key, root);
        }
      }

      treetop = root;
    }

    JTree tree = new JTree(treetop);
    JScrollPane treeView = new JScrollPane(tree);

    tree.setShowsRootHandles(true);

    // Set the icon for leaf nodes.
    ImageIcon leafIcon = new ImageIcon("join.jpg");
    DefaultTreeCellRenderer renderer = new DefaultTreeCellRenderer();
    renderer.setOpenIcon(leafIcon);
    renderer.setClosedIcon(leafIcon);

    tree.setCellRenderer(renderer);

    f.setSize(300, 500);

    f.add(treeView);
    for (int i = 0; i < tree.getRowCount(); i++) {
      tree.expandRow(i);
    }

    if (js.size() == 0) {
      f.add(new JLabel("No joins in plan."));
    }

    f.pack();
  }
Ejemplo n.º 6
0
  /**
   * Return true if field is a primary key of the specified table, false otherwise
   *
   * @param tableAlias The alias of the table in the query
   * @param field The pure name of the field
   */
  private boolean isPkey(String tableAlias, String field) {
    int tid1 = p.getTableId(tableAlias);
    String pkey1 = Database.getCatalog().getPrimaryKey(tid1);

    return pkey1.equals(field);
  }
Ejemplo n.º 7
0
  /**
   * This is a helper method that computes the cost and cardinality of joining joinToRemove to
   * joinSet (joinSet should contain joinToRemove), given that all of the subsets of size
   * joinSet.size() - 1 have already been computed and stored in PlanCache pc.
   *
   * @param stats table stats for all of the tables, referenced by table names rather than alias
   *     (see {@link #orderJoins})
   * @param filterSelectivities the selectivities of the filters over each of the tables (where
   *     tables are indentified by their alias or name if no alias is given)
   * @param joinToRemove the join to remove from joinSet
   * @param joinSet the set of joins being considered
   * @param bestCostSoFar the best way to join joinSet so far (minimum of previous invocations of
   *     computeCostAndCardOfSubplan for this joinSet, from returned CostCard)
   * @param pc the PlanCache for this join; should have subplans for all plans of size
   *     joinSet.size()-1
   * @return A {@link CostCard} objects desribing the cost, cardinality, optimal subplan
   * @throws ParsingException when stats, filterSelectivities, or pc object is missing tables
   *     involved in join
   */
  @SuppressWarnings("unchecked")
  private CostCard computeCostAndCardOfSubplan(
      HashMap<String, TableStats> stats,
      HashMap<String, Double> filterSelectivities,
      LogicalJoinNode joinToRemove,
      Set<LogicalJoinNode> joinSet,
      double bestCostSoFar,
      PlanCache pc)
      throws ParsingException {

    LogicalJoinNode j = joinToRemove;

    Vector<LogicalJoinNode> prevBest;

    if (this.p.getTableId(j.t1Alias) == null)
      throw new ParsingException("Unknown table " + j.t1Alias);
    if (this.p.getTableId(j.t2Alias) == null)
      throw new ParsingException("Unknown table " + j.t2Alias);

    String table1Name = Database.getCatalog().getTableName(this.p.getTableId(j.t1Alias));
    String table2Name = Database.getCatalog().getTableName(this.p.getTableId(j.t2Alias));
    String table1Alias = j.t1Alias;
    String table2Alias = j.t2Alias;

    Set<LogicalJoinNode> news = (Set<LogicalJoinNode>) ((HashSet<LogicalJoinNode>) joinSet).clone();
    news.remove(j);

    double t1cost, t2cost;
    int t1card, t2card;
    boolean leftPkey, rightPkey;

    if (news.isEmpty()) { // base case -- both are base relations
      prevBest = new Vector<LogicalJoinNode>();
      t1cost = stats.get(table1Name).estimateScanCost();
      t1card = stats.get(table1Name).estimateTableCardinality(filterSelectivities.get(j.t1Alias));
      leftPkey = isPkey(j.t1Alias, j.f1PureName);

      t2cost = table2Alias == null ? 0 : stats.get(table2Name).estimateScanCost();
      t2card =
          table2Alias == null
              ? 0
              : stats.get(table2Name).estimateTableCardinality(filterSelectivities.get(j.t2Alias));
      rightPkey = table2Alias == null ? false : isPkey(table2Alias, j.f2PureName);
    } else {
      // news is not empty -- figure best way to join j to news
      prevBest = pc.getOrder(news);

      // possible that we have not cached an answer, if subset
      // includes a cross product
      if (prevBest == null) {
        return null;
      }

      double prevBestCost = pc.getCost(news);
      int bestCard = pc.getCard(news);

      // estimate cost of right subtree
      if (doesJoin(prevBest, table1Alias)) { // j.t1 is in prevBest
        t1cost = prevBestCost; // left side just has cost of whatever
        // left
        // subtree is
        t1card = bestCard;
        leftPkey = hasPkey(prevBest);

        t2cost = j.t2Alias == null ? 0 : stats.get(table2Name).estimateScanCost();
        t2card =
            j.t2Alias == null
                ? 0
                : stats
                    .get(table2Name)
                    .estimateTableCardinality(filterSelectivities.get(j.t2Alias));
        rightPkey = j.t2Alias == null ? false : isPkey(j.t2Alias, j.f2PureName);
      } else if (doesJoin(prevBest, j.t2Alias)) { // j.t2 is in prevbest
        // (both
        // shouldn't be)
        t2cost = prevBestCost; // left side just has cost of whatever
        // left
        // subtree is
        t2card = bestCard;
        rightPkey = hasPkey(prevBest);

        t1cost = stats.get(table1Name).estimateScanCost();
        t1card = stats.get(table1Name).estimateTableCardinality(filterSelectivities.get(j.t1Alias));
        leftPkey = isPkey(j.t1Alias, j.f1PureName);

      } else {
        // don't consider this plan if one of j.t1 or j.t2
        // isn't a table joined in prevBest (cross product)
        return null;
      }
    }

    // case where prevbest is left
    double cost1 = estimateJoinCost(j, t1card, t2card, t1cost, t2cost);

    LogicalJoinNode j2 = j.swapInnerOuter();
    double cost2 = estimateJoinCost(j2, t2card, t1card, t2cost, t1cost);
    if (cost2 < cost1) {
      boolean tmp;
      j = j2;
      cost1 = cost2;
      tmp = rightPkey;
      rightPkey = leftPkey;
      leftPkey = tmp;
    }
    if (cost1 >= bestCostSoFar) return null;

    CostCard cc = new CostCard();

    cc.card = estimateJoinCardinality(j, t1card, t2card, leftPkey, rightPkey, stats);
    cc.cost = cost1;
    cc.plan = (Vector<LogicalJoinNode>) prevBest.clone();
    cc.plan.addElement(j); // prevbest is left -- add new join to end
    return cc;
  }
Ejemplo n.º 8
0
  public void setContent(String cat) {
    cat = cat.trim();
    selectAllCB.setVisible(false);
    selectAllCB.setSelected(false);
    deleteBut.setVisible(false);
    restoreBut.setVisible(false);
    refreshBut.setVisible(true);
    Object columns[] = null;
    int count = 0;
    switch (cat) {
      case "Inbox":
        columns = new Object[] {"", "From", "Date", "Subject", "Content"};
        count = Database.getCount("Inbox");
        workingSet = db.getData("SELECT * FROM messages WHERE tag='inbox' ORDER BY msg_id desc");
        ;
        break;
      case "SentMail":
        columns = new Object[] {"", "To", "Date", "Subject", "Content"};
        count = Database.getCount("Sentmail");
        workingSet = db.getData("SELECT * FROM messages WHERE tag='sentmail' ORDER BY msg_id desc");
        break;
      case "Draft":
        columns = new Object[] {"", "To", "Date", "Subject", "Content"};
        count = Database.getCount("Draft");
        workingSet = db.getData("SELECT * FROM messages WHERE tag='draft' ORDER BY msg_id desc");
        break;
      case "Outbox":
        columns = new Object[] {"", "To", "Date", "Subject", "Content"};
        count = Database.getCount("Outbox");
        workingSet = db.getData("SELECT * FROM messages WHERE tag='outbox' ORDER BY msg_id desc");
        break;
      case "Trash":
        //                restoreBut.setVisible(true);
        columns = new Object[] {"", "To/From", "Date", "Subject", "Content"};
        count = Database.getCount("Trash");
        workingSet =
            db.getData(
                "SELECT * FROM messages,trash WHERE messages.tag='trash' and messages.msg_id=trash.msg_id ORDER BY deleted_at desc");
        break;
      default:
        System.out.println("in default case");
    }
    if (count > 0) {
      selectAllCB.setVisible(true);
      rows = new Object[count][];
      msgID = new int[count];
      try {
        workingSet.beforeFirst();
        for (int i = 0; i < count && workingSet.next(); i++) {
          msgID[i] = workingSet.getInt(1);
          rows[i] =
              new Object[] {
                false,
                workingSet.getString(2),
                workingSet.getDate(3),
                workingSet.getString(4),
                workingSet.getString(5)
              };
        }
      } catch (SQLException sqlExc) {
        JOptionPane.showMessageDialog(null, sqlExc, "EXCEPTION", JOptionPane.ERROR_MESSAGE);
        sqlExc.printStackTrace();
      }

      tableModel = new MyDefaultTableModel(rows, columns);
      table = new JTable(tableModel);
      table.getSelectionModel().addListSelectionListener(this);
      table.addMouseListener(this);
      table.getTableHeader().setOpaque(true);
      table.getTableHeader().setReorderingAllowed(false);
      //            table.getTableHeader().setBackground(Color.blue);
      table.getTableHeader().setForeground(Color.blue);
      //        table.setRowSelectionAllowed(false);
      //            table.setColumnSelectionAllowed(false);
      table.setFont(new Font(Font.SANS_SERIF, Font.PLAIN, 14));
      table.setRowHeight(20);
      table.setFillsViewportHeight(true);

      TableColumn column = null;
      for (int i = 0; i < 5; i++) {
        column = table.getColumnModel().getColumn(i);
        if (i == 0) {
          column.setPreferredWidth(6);
        } else if (i == 3) {
          column.setPreferredWidth(250);
        } else if (i == 4) {
          column.setPreferredWidth(450);
        } else {
          column.setPreferredWidth(40);
        }
      }
      table.setAutoResizeMode(JTable.AUTO_RESIZE_SUBSEQUENT_COLUMNS);

      remove(contentPan);
      contentPan = new JScrollPane(table);
      contentPan.setBackground(Color.orange);
      contentPan.setOpaque(true);
      contentPan.setBorder(BorderFactory.createEmptyBorder(0, 0, 0, 0));
      add(contentPan, "Center");
      Home.home.homeFrame.setVisible(true);
    } else {
      JPanel centPan = new JPanel(new GridBagLayout());
      centPan.setBackground(new Color(52, 86, 70));
      JLabel label = new JLabel("No Messages In This Category");
      label.setFont(new Font(Font.SANS_SERIF, Font.BOLD, 22));
      label.setForeground(Color.orange);
      centPan.add(label);
      remove(contentPan);
      contentPan = new JScrollPane(centPan);
      contentPan.setBorder(BorderFactory.createEmptyBorder(0, 0, 0, 0));
      add(contentPan, "Center");
      contentPan.repaint();
    }
  }
Ejemplo n.º 9
0
  @Override
  public void actionPerformed(ActionEvent axnEve) {
    Object obj = axnEve.getSource();

    if (obj == selectAllCB) {
      Boolean state;
      if (selectAllCB.isSelected()) {
        state = true;
        deleteBut.setVisible(true);
        if (Home.titlePan.getTitle().equals("Trash")) {
          restoreBut.setVisible(true);
        }
      } else {
        state = false;
        deleteBut.setVisible(false);
        if (Home.titlePan.getTitle().equals("Trash")) {
          restoreBut.setVisible(false);
        }
      }
      for (int i = 0; i < table.getRowCount(); i++) {
        table.setValueAt(state, i, 0);
      }
    } else if (obj == refreshBut || obj == backBut) {
      setContent(Home.titlePan.getTitle());
      backBut.setVisible(false);
    } else if (obj == deleteBut) {
      ArrayList selectedMessages = getSelectedMessages();
      if (selectedMessages.isEmpty()) {
        FootPan.setMessage(FootPan.NO_SELECTION_MESSAGE);
      } else {
        int option =
            JOptionPane.showConfirmDialog(
                Home.home.homeFrame,
                "Are You Sure?",
                "DELETE",
                JOptionPane.YES_NO_OPTION,
                JOptionPane.WARNING_MESSAGE);
        if (option == 0) {
          Database.deleteMessages(selectedMessages, Home.titlePan.getTitle());
          setContent(Home.titlePan.getTitle());
        }
      }
    } else if (obj == restoreBut) {
      ArrayList selectedMessages = getSelectedMessages();
      if (selectedMessages.isEmpty()) {
        FootPan.setMessage(FootPan.NO_SELECTION_MESSAGE);
      } else {
        int option =
            JOptionPane.showConfirmDialog(
                Home.home.homeFrame,
                "Are You Sure?",
                "RESTORE",
                JOptionPane.YES_NO_OPTION,
                JOptionPane.WARNING_MESSAGE);
        if (option == 0) {
          Database.restoreMessages(selectedMessages);
          setContent(Home.titlePan.getTitle());
        }
      }
    }
  }
Ejemplo n.º 10
0
 public void update_table() {
   Database.showDB("transaction", transaction_column, info_data, info_table);
 }
Ejemplo n.º 11
0
 public void update_table() {
   Database.showDB("member", member_column, info_data, info_table);
 }
  @SuppressWarnings({"unchecked", "rawtypes"})
  private void initializeComponent() {
    // Initialize panels
    panel_1 = new JPanel();
    panel_2 = new JPanel();
    panel_3 = new JPanel();
    panel_4 = new JPanel();
    panel_5 = new JPanel();
    panel_6 = new JPanel();
    panel_7 = new JPanel();

    // Initialize spinner
    spinnermodel = new SpinnerNumberModel(0, 0, 10, 1);
    spinner = new JSpinner(spinnermodel);

    // Initialize combo box
    searchcombobox = new JComboBox();
    searchcombobox.addItem("All");
    searchcombobox.addItem("By Name");
    searchcombobox.addItem("By Guardian Name");
    searchcombobox.addItem("By Course");
    searchcombobox.addItem("Due Fees");
    searchcombobox.setSelectedIndex(0);

    coursecombobox = new JComboBox();
    coursecombobox.addItem("Select Course");
    Database db = new Database();
    String courses[] = db.getCourses();

    if (courses != null) {
      for (String x : courses) {
        coursecombobox.addItem(x);
      }
    }

    // Initialize text field
    searchtextfield = new JTextField(10);
    addfeestextfield = new JTextField(8);

    // Initialize button
    searchbutton = new JButton("Search");
    addfeesbutton = new JButton("Add fees");
    addcoursebutton = new JButton("Add Course");

    // Initialize check box
    allfieldcheckbox = new JCheckBox("Show all fields");
    allfieldcheckbox.setSelected(true);

    // Initialize label
    totalstudentlabel = new JLabel("Total Student");
    feespayedlabel = new JLabel("Fees Payed");
    feesduelabel = new JLabel("Fees due");
    totalfeeslabel = new JLabel("Total fees");

    // Add Item listener to check box
    allfieldcheckbox.addItemListener(this);

    // Add Action Listener to button
    searchbutton.addActionListener(this);
    addfeesbutton.addActionListener(this);
    addcoursebutton.addActionListener(this);

    // Add Key Listener to button
    searchbutton.addKeyListener(this);
    addfeesbutton.addKeyListener(this);
    addcoursebutton.addKeyListener(this);
  }
  // Method to update Table and related components
  private void update() {
    Database db = new Database();
    String column[] = {
      "ID",
      "NAME",
      "GENDER",
      "GUARDIAN_ROLE",
      "GUARDIAN_NAME",
      "PRESENT_ADDRESS",
      "PRESENT_CITY",
      "PRESENT_PHONE"
    };

    try {
      // Update table
      if (searchcombobox.getSelectedIndex() == 0 && allfieldcheckbox.isSelected()) {
        mytablemodel = new MyTableModel(db.getAllStudent(), db.getColumnNameFromStudent());
      } else if (searchcombobox.getSelectedIndex() == 0 && !allfieldcheckbox.isSelected()) {
        mytablemodel = new MyTableModel(db.getSomeFieldsFromStudent(), column);
      } else if (searchcombobox.getSelectedIndex() == 1 && allfieldcheckbox.isSelected()) {
        String query =
            "SELECT * FROM Student WHERE NAME = \'" + searchtextfield.getText().trim() + "\'";
        mytablemodel = new MyTableModel(db.getData(query), db.getColumnNameFromStudent());
      } else if (searchcombobox.getSelectedIndex() == 1 && !allfieldcheckbox.isSelected()) {
        String query =
            "SELECT ID, NAME, GENDER, GUARDIAN_ROLE, GUARDIAN_NAME, PRESENT_ADDRESS, PRESENT_CITY, PRESENT_PHONE FROM Student WHERE NAME = \'"
                + searchtextfield.getText().trim()
                + "\'";
        mytablemodel = new MyTableModel(db.getData(query), column);
      } else if (searchcombobox.getSelectedIndex() == 2 && allfieldcheckbox.isSelected()) {
        String query =
            "SELECT * FROM Student WHERE GUARDIAN_NAME = \'"
                + searchtextfield.getText().trim()
                + "\'";
        mytablemodel = new MyTableModel(db.getData(query), db.getColumnNameFromStudent());
      } else if (searchcombobox.getSelectedIndex() == 2 && !allfieldcheckbox.isSelected()) {
        String query =
            "SELECT ID, NAME, GENDER, GUARDIAN_ROLE, GUARDIAN_NAME, PRESENT_ADDRESS, PRESENT_CITY, PRESENT_PHONE FROM Student WHERE GUARDIAN_NAME = \'"
                + searchtextfield.getText().trim()
                + "\'";
        mytablemodel = new MyTableModel(db.getData(query), column);
      } else if (searchcombobox.getSelectedIndex() == 3 && allfieldcheckbox.isSelected()) {
        String query =
            "SELECT * FROM Student WHERE ID = ANY(SELECT SID FROM Fee WHERE CID = ANY(SELECT ID FROM CourseInfo WHERE NAME = \'"
                + searchtextfield.getText().trim()
                + "\'))";
        mytablemodel = new MyTableModel(db.getData(query), db.getColumnNameFromStudent());
      } else if (searchcombobox.getSelectedIndex() == 3 && !allfieldcheckbox.isSelected()) {
        String query =
            "SELECT ID, NAME, GENDER, GUARDIAN_ROLE, GUARDIAN_NAME, PRESENT_ADDRESS, PRESENT_CITY, PRESENT_PHONE FROM Student WHERE ID = ANY(SELECT SID FROM Fee WHERE CID = ANY(SELECT ID FROM CourseInfo WHERE NAME = \'"
                + searchtextfield.getText().trim()
                + "\'))";
        mytablemodel = new MyTableModel(db.getData(query), column);
      } else if (searchcombobox.getSelectedIndex() == 4 && allfieldcheckbox.isSelected()) {
        String query =
            "SELECT * FROM Student WHERE ID = ANY(SELECT SID FROM Fee WHERE TOTAL_FEES - FEES_PAYED >= 0)";
        mytablemodel = new MyTableModel(db.getData(query), db.getColumnNameFromStudent());
      } else {
        String query =
            "SELECT ID, NAME, GENDER, GUARDIAN_ROLE, GUARDIAN_NAME, PRESENT_ADDRESS, PRESENT_CITY, PRESENT_PHONE FROM Student WHERE ID = ANY(SELECT SID FROM Fee WHERE TOTAL_FEES - FEES_PAYED >= 0)";
        mytablemodel = new MyTableModel(db.getData(query), column);
      }

      table = new JTable(mytablemodel);
      table.setAutoResizeMode(JTable.AUTO_RESIZE_OFF);
      // Add list selection listener to table
      table.getSelectionModel().addListSelectionListener(this);

      TableColumn col = null;
      for (int i = 3; i < table.getColumnCount(); i++) {
        col = table.getColumnModel().getColumn(i);
        col.setPreferredWidth(200);
      }

      scrollpane = new JScrollPane(table);

      panel_3.removeAll();
      panel_3.add(scrollpane);

      // Update total student label
      int total = db.getTotalStudent();
      totalstudentlabel.setText("Total Student = " + total);

      // Clear search combo box
      searchtextfield.setText("");

      this.revalidate();
    } catch (Exception e) {
      JOptionPane.showMessageDialog(this, e.getMessage(), "Error", JOptionPane.ERROR_MESSAGE);
    }
  }
Ejemplo n.º 14
0
  /**
   * This is a helper method that computes the cost and cardinality of joining a LogicalJoinNode j
   * to the current greedy plan we have built up.
   *
   * @param j the join to try adding to our plan
   * @param plan the current plan we have built so far from the greedy algorithm, a Vector of
   *     LogicalJoinNodes that we've so far chosen.
   * @param planCardinalities given the join order from plan, we also keep track of how large joined
   *     tables are, so we can help estimate the cardinality and cost of this next join
   * @param planCosts given the join order from plan, we also keep track of how expensive executing
   *     some joins are, so we can help estimate the cardinality and cost of this next join
   * @param stats table stats for all of the tables, referenced by table names rather than alias
   *     (see {@link #orderGreedyJoins(HashMap, HashMap)})
   * @param filterSelectivities the selectivities of the filters over each of the tables (where
   *     tables are indentified by their alias or name if no alias is given)
   * @return A {@link CostCard} objects desribing the cost, cardinality, optimal subplan
   * @throws ParsingException when stats, filterSelectivities, or pc object is missing tables
   *     involved in join
   */
  private CostCard costGreedyJoin(
      LogicalJoinNode j,
      Vector<LogicalJoinNode> plan,
      Vector<Integer> planCardinalities,
      Vector<Double> planCosts,
      HashMap<String, TableStats> stats,
      HashMap<String, Double> filterSelectivities)
      throws ParsingException {

    if (this.p.getTableId(j.t1Alias) == null)
      throw new ParsingException("Unknown table " + j.t1Alias);
    if (this.p.getTableId(j.t2Alias) == null)
      throw new ParsingException("Unknown table " + j.t2Alias);

    String table1Name = Database.getCatalog().getTableName(this.p.getTableId(j.t1Alias));
    String table2Name = Database.getCatalog().getTableName(this.p.getTableId(j.t2Alias));
    String table1Alias = j.t1Alias;
    String table2Alias = j.t2Alias;

    double t1cost, t2cost;
    int t1card, t2card;
    boolean leftPkey, rightPkey;

    // estimate cost of right subtree
    if (doesJoin(plan, table1Alias)) { // j.t1 is in plan already
      CostCard c = getCostCard(plan, planCardinalities, planCosts, table1Alias);
      t1cost = c.cost; // left side just has cost of whatever left subtree is
      t1card = c.card;
      leftPkey = hasPkey(plan);

      t2cost = j.t2Alias == null ? 0 : stats.get(table2Name).estimateScanCost();
      t2card =
          j.t2Alias == null
              ? 0
              : stats.get(table2Name).estimateTableCardinality(filterSelectivities.get(j.t2Alias));
      rightPkey = j.t2Alias == null ? false : isPkey(j.t2Alias, j.f2PureName);
    } else if (doesJoin(plan, j.t2Alias)) { // j.t2 is in plan
      // (else if since both j.t1 and j.t2 shouldn't both be)
      CostCard c = getCostCard(plan, planCardinalities, planCosts, table2Alias);
      t2cost = c.cost;
      t2card = c.card;
      rightPkey = hasPkey(plan);

      t1cost = stats.get(table1Name).estimateScanCost();
      t1card = stats.get(table1Name).estimateTableCardinality(filterSelectivities.get(j.t1Alias));
      leftPkey = isPkey(j.t1Alias, j.f1PureName);

    } else { // Neither is a plan, both are just single tables
      t1cost = stats.get(table1Name).estimateScanCost();
      t1card = stats.get(table1Name).estimateTableCardinality(filterSelectivities.get(j.t1Alias));
      leftPkey = isPkey(j.t1Alias, j.f1PureName);

      t2cost = table2Alias == null ? 0 : stats.get(table2Name).estimateScanCost();
      t2card =
          table2Alias == null
              ? 0
              : stats.get(table2Name).estimateTableCardinality(filterSelectivities.get(j.t2Alias));
      rightPkey = table2Alias == null ? false : isPkey(table2Alias, j.f2PureName);
    }

    double cost1 = estimateJoinCost(j, t1card, t2card, t1cost, t2cost);

    LogicalJoinNode j2 = j.swapInnerOuter();
    double cost2 = estimateJoinCost(j2, t2card, t1card, t2cost, t1cost);
    if (cost2 < cost1) {
      boolean tmp;
      j = j2;
      cost1 = cost2;
      tmp = rightPkey;
      rightPkey = leftPkey;
      leftPkey = tmp;
    }

    CostCard cc = new CostCard();
    cc.card = estimateJoinCardinality(j, t1card, t2card, leftPkey, rightPkey, stats);
    cc.cost = cost1;
    return cc;
  }