/* On Problem #3, I assume user will first click on the button shown "3a-c" and leave the focus there. History: Reference: www.java.sun.com/projects/jkd/1.3/docs/api/index.html Date: 1/24/00 */ import java.sql.*; import java.awt.*; import java.awt.event.*; import javax.swing.*; import javax.swing.table.*; import java.util.*; import java.text.*; public class cse444 extends JFrame { private JTextArea msgout; private JTextArea result; private JLabel question; private JLabel space; private JLabel instruction; private JLabel instruction1; private Connection dbconn; private String USERNAME; private String DATABASE; private String PASSWORD; public cse444() { super( "cse444 programming assignment #1" ); // Set up GUI environment Container p = getContentPane(); question = new JLabel("-------- which question? ----------", 0); space = new JLabel(" The following area is for debugging purpose only. ",0); instruction = new JLabel("Note: Clicking button '3a-c' will display query result"); instruction1 = new JLabel("from Qtn 3 part a to c as you click. "); result = new JTextArea( 8, 40 ); msgout = new JTextArea( 8, 40 ); DATABASE = new String("444"); USERNAME = new String("cse444_f"); PASSWORD = new String("password"); // Set up database connection try { String url = "jdbc:odbc:" + DATABASE; Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver" ); dbconn = DriverManager.getConnection( url, USERNAME, PASSWORD); msgout.append( "Connection successful\n" ); } catch ( ClassNotFoundException cnfex ) { // process ClassNotFoundExceptions here cnfex.printStackTrace(); msgout.append( "Connection unsuccessful\n" + cnfex.toString() ); } catch ( SQLException sqlex ) { // process SQLExceptions here sqlex.printStackTrace(); msgout.append( "Connection unsuccessful\n" + sqlex.toString() ); } catch ( Exception excp ) { // process remaining Exceptions here excp.printStackTrace(); msgout.append( excp.toString() ); } ButtonPanel controls = new ButtonPanel( dbconn, question, result, msgout); p.setLayout(new FlowLayout() ); p.add( controls ); p.add(question); p.add(new JScrollPane(result)); p.add(space); p.add(new JScrollPane(msgout)); p.add(instruction); p.add(instruction1); setSize( 500, 500 ); } public static void main( String args[] ) { cse444 driver = new cse444(); driver.addWindowListener( new WindowAdapter() { public void windowClosing( WindowEvent e ) { System.exit( 0 ); } } ); driver.show(); } } //could have implement KeyListener also class ButtonPanel extends JPanel implements ActionListener, KeyListener{ private JButton Qtn1a; private JButton Qtn1b; private JButton Qtn2a; private JButton Qtn2b; private JButton Qtn2c; private JButton Qtn3; private JLabel qtn; private JTextArea result; private JTextArea msgout; private Connection dbconn; private String query1a; private String query1b; private String query2a; private String query2b; private String query2c; private String query3a; private String query3b; private String query3c; private String which; private int count; private boolean executed = false; private boolean doProcess = false; private ResultSet rs; private Statement statement; public ButtonPanel( Connection dbc, JLabel question, JTextArea results, JTextArea msg ) { setLayout( new GridLayout( 1, 6 ) ); msgout = msg; result = results; qtn = question; dbconn = dbc; count = -1; which = new String(); addKeyListener(this); query1a = new String("select distinct F_Name from Faculty f, Department d,Offering o "); query1a += "where f.F_SSN = d.F_SSN and f.F_SSN=o.F_SSN and d.F_SSN not in "; query1a += "( select d.F_SSN from Department d, Course c, Offering o, Faculty f "; query1a += "where d.F_SSN = o.F_SSN and o.CourseNo = c.CourseNo and c.Did = d.Did ); "; query1b = new String("update waiFaculty set F_Salary = F_Salary*1.1 "); query1b += "where F_Name in "; query1b += "( select F_Name from Faculty f, Department d,Offering o "; query1b += "where f.F_SSN = d.F_SSN f.F_SSN=o.F_SSN and d.F_SSN not in "; query1b += "(select d.F_SSN from Department d, Course c, Offering o, Faculty f "; query1b += "where d.F_SSN = o.F_SSN and o.CourseNo = c.CourseNo and c.Did = d.Did));"; query2a = new String("select distinct F_Name, f.F_SSN from Faculty as f, Department as d "); query2a += "where f.F_SSN = d.F_SSN and F_Salary > ( select avg(F_Salary) from Faculty as f1 "; query2a += "where f1.Did = d.Did group by d.Did ) and F_Salary < ( select max(F_Salary) "; query2a += "from Faculty as f2 where f2.Did = d.Did group by d.Did); "; query2b = new String("select f.F_SSN, f.F_Name, (f.F_Salary - ( select Avg ( f1.F_Salary*1.0) "); query2b += "from Faculty f1 group by f1.Did having f1.Did = f.Did )) as overamount, "; query2b += "(( f.F_Salary - ( select Avg(f1.F_Salary*1.0) from Faculty f1 group by f1.Did "; query2b += "having f1.Did = f.Did)) /d.D_Budget) as fraction "; query2b += "from Faculty f, Department d where f.F_SSN = d.F_SSN "; query2b +="and f.F_Salary>(select Avg ( f1.F_Salary*1.0) from Faculty f1 group by f1.Did having f1.Did = f.Did )"; query2b +="and f.F_Salary<(select Max( f1.F_Salary*1.0) from Faculty f1 group by f1.Did having f1.Did = f.Did )"; query2c = new String("update waiFaculty set waiFaculty.F_Salary = ( select max(F_Salary) from Faculty group by Faculty.Did "); query2c += "having waiFaculty.Did = Faculty.Did) where waiFaculty.F_SSN in (select distinct f.F_SSN from Faculty f, Department d "; query2c += "where f.F_SSN = d.F_SSN and F_Salary > (select avg(F_Salary) from Faculty as f1 where f1.Did = d.Did "; query2c += "group by d.Did ) and F_Salary < ( select max(F_Salary) from Faculty as f2 where f2.Did = d.Did group by d.Did )); "; query3a = new String("select distinct F_Name, F_Salary from Faculty f, Department d, Course c where ( f.F_SSN not in "); query3a += "( select F_SSN from Department )) and ( select count(CallNo) as Numofcourse from Offering o "; query3a += "group by o.F_SSN having o.F_SSN = f.F_SSN) >= ( select count(CallNo) as NumofcourseChair "; query3a += "from Offering o, Department d group by o.F_SSN, d.Did having (o.F_SSN = f.F_SSN) and (f.Did = d.Did)) order by F_Salary "; Qtn1a = new JButton( "1a" ); Qtn1a.addActionListener( this ); add( Qtn1a ); Qtn1b = new JButton( "1b" ); Qtn1b.addActionListener( this ); add( Qtn1b ); Qtn2a = new JButton( "2a" ); Qtn2a.addActionListener( this ); add( Qtn2a ); Qtn2b = new JButton( "2b" ); Qtn2b.addActionListener( this ); add( Qtn2b ); Qtn2c = new JButton( "2c" ); Qtn2c.addActionListener( this ); add( Qtn2c ); Qtn3 = new JButton( "3a-c" ); Qtn3.addActionListener( this ); add( Qtn3 ); } public void actionPerformed( ActionEvent e) { Object source = e.getSource(); if(source == Qtn1a) process(query1a, "1a"); else if(source == Qtn1b) processUpdate(query1b, "1b"); else if(source == Qtn2a) process(query2a, "2a"); else if(source == Qtn2b) process(query2b, "2b"); else if(source == Qtn2c) processUpdate(query2c, "2c"); else if(source == Qtn3) { count++; if( count == 0 ) msgout.append("\nPress enter or click this button again to see each part.\n"); else { int i = count % 3; if( 1 == i ) process(query3a, "3a"); else if( 2 == i ) process(query3a, "3b"); //3b is the same query, but view every other row else if( 0 == i ) process(query3a, "3c"); msgout.append("\nHey.. '3a-c' is clicked and count is: " + count + "\n" ); } } } public void process(String query, String whichQuestion) { which = whichQuestion; try { qtn.setText("---- This is Question " + which + " ---- " ); if(!which.equalsIgnoreCase( "3a" )&&!which.equalsIgnoreCase( "3b" )&& !which.equalsIgnoreCase( "3c" )) doProcess = true; else if (!executed) doProcess = true; else doProcess = false; if(doProcess) { if(which.equalsIgnoreCase("3a")) executed = true; statement = dbconn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); //do msgout for easy debug msgout.append( "\nSending query: " + dbconn.nativeSQL( query ) + "\n" ); rs = statement.executeQuery( query ); } try { rs.next(); //move to the first row and display the column only once ResultSetMetaData md = rs.getMetaData(); result.setText(" "); for( int i = 1; i <= md.getColumnCount();i++) result.append(md.getColumnLabel(i) + " | "); result.append("\n"); if( which.equalsIgnoreCase( "3c" ) ) { //move the the last row and display result in reverse order rs.last(); do { for ( int i = 1; i <= md.getColumnCount();i++) result.append(rs.getString(i) + " | "); result.append("\n"); }while(rs.previous()); }//end of if else if (which.equalsIgnoreCase( "3a" ) ) { rs.first(); do { for ( int i = 1; i <= md.getColumnCount();i++) result.append(rs.getString(i) + " | "); result.append("\n"); }while(rs.next()); } else { rs.beforeFirst(); while (rs.next()) { for ( int i = 1; i <= md.getColumnCount();i++) result.append(rs.getString(i) + " | "); result.append("\n"); } } } catch ( SQLException sqlex ) { msgout.append(sqlex.toString() + sqlex.getMessage() ); } //if(doProcess) // statement.close(); } catch ( SQLException sqlex ) { msgout.append( sqlex.toString() + sqlex.getMessage() ); } } // end of process() //Note: Tables to be updated are prefixed by waiXXX e.g. waiFaculty public void processUpdate(String query, String which) { try { qtn.setText(" ---- This is Question " + which + " ---- " ); Statement statement = dbconn.createStatement(); msgout.append( "\nSending query: " + dbconn.nativeSQL( query ) + "\n" ); int result = statement.executeUpdate( query ); if( result > 0 ) msgout.append("\nUpdate successful\n"); else msgout.append("\nUpdate unsuccessful, no rows qualifies, data unchanged"); statement.close(); } catch ( SQLException sqlex ) { msgout.append("Damn it... SQL statements error "); msgout.append( sqlex.toString() + sqlex.getMessage() ); } } // end of process() //declaring function relevant to interface KeyListener public boolean isFocusTraversable(){return true;} //to allow focus switch from JFrame to JPanel public void keyReleased(KeyEvent e){} //virtual, do nothing in my case public void keyTyped(KeyEvent e){} //virtual, do nothing in my case public void keyPressed(KeyEvent e) { msgout.append("get there enter "); if( KeyEvent.VK_ENTER == e.getKeyCode() ) { count++; msgout.append("\nHey.. enter is pressed and count is: " + count + "\n" ); if( count == 0 ) msgout.append("\nPress enter or click this button again to see each part.\n"); else { int i = count % 3; if( 1 == i ) process(query3a, "3a"); else if( 2 == i ) process(query3a, "3b"); //3b is the same query, but view every other row else if( 0 == i ) process(query3c, "3c"); msgout.append("\nHey.. enter is pressed and count is: " + count + "\n" ); } } } } //the following lines of codes are to be used if ButtonPanel is to implement KeyListener //but in my case, I can still achieve the same effect without using it. /* public boolean isFocusTraversable(){return true;} public void keyReleased(KeyEvent e){msgout.append("in keyreleased");} public void keyTyped(KeyEvent e){msgout.append("in keytyped");} public void keyPressed(KeyEvent e) { msgout.append("get there... "); if( KeyEvent.VK_ENTER == e.getKeyCode() ) //if( e.getKeyCode() > 0 ) { count++; msgout.append("\nHey.. enter is pressed and count is: " + count + "\n" ); } int i = count % 3; switch(i) { case 1: process(query3a, "3a"); case 2: process(query3b, "3b"); case 0: process(query3c, "3c"); } } */