// 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);
}
}
/**
* 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);
}
}
/**
* 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();
}
/**
* 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);
}
/**
* 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;
}
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();
}
}
@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());
}
}
}
}
public void update_table() {
Database.showDB("transaction", transaction_column, info_data, info_table);
}
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);
}
}
/**
* 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;
}
