Next record button GUI & database

[thomp2bl]

hello,

I have a GUI that connects to a database and in the GUI I have a file menu that has first, last, prev, next on it. I want the user to be able to select either one of these words or on the image of these that are in a tool bar. i have two classes. it is a lot of code but i just need to figure out how to get to the next record under the action performed. Thank you for your time!!!

Brian

package Assignment2;

import javax.swing.event.*;
import java.awt.event.*;
import javax.swing.*;
import java.awt.*;
import java.util.Vector;
import java.io.*;

public class GUIDatabase extends JFrame implements ListSelectionListener, ActionListener {

    private String path = "c:/temp/assn2Files/";
    private JButton first, last, next, prev, save;
    private JTextField fn, mi, ln, t, s, y;
    private String[] title = {"analyst", "executive", "programmer", "project leader                                                                            "};
    private JComboBox titleCombo = new JComboBox(title);
    private String[] department = {"Applications", "Payroll", "Accounting", "Marketing                                                                                   "};
    private JComboBox departmentCombo = new JComboBox(department);
    private JList pd;
    private String myArray[] = {""};
    private DbSource source = new DbSource("empdb", true);
    String action, selectedQName, selectedQDSN, selectedQString;
    private Vector vc, vc2, vc3, pdNames;
    private DbSource dbs = new DbSource("empdb", true);
    private DbSource dbs2 = new DbSource("empdb", true);
    private DbSource dbs3 = new DbSource("empdb", true);
    private DbSource dbs4 = new DbSource("empdb", true);

    public GUIDatabase() {
        super("Employee Interface");

        //set up menus
        JMenuBar menuBar = new JMenuBar();
        JMenu navMenu = new JMenu("Navigation");
        menuBar.add(navMenu);
        navMenu.add(ListeningMenuItem("First"));
        navMenu.add(ListeningMenuItem("Prev"));
        navMenu.add(ListeningMenuItem("Next"));
        navMenu.add(ListeningMenuItem("Last"));
        navMenu.add(new JSeparator());
        navMenu.add(ListeningMenuItem("Update employee record"));
        JMenu statsMenu = new JMenu("Statistics");
        menuBar.add(statsMenu);
        statsMenu.add(ListeningMenuItem("Employees per Department"));
        statsMenu.add(ListeningMenuItem("Employees per Project"));

        //menu goes on the root pane
        setJMenuBar(menuBar);

        //instantiates buttons with images instead of text
        first = new JButton(new ImageIcon(path + "First.gif"));
        last = new JButton(new ImageIcon(path + "Last.gif"));
        next = new JButton(new ImageIcon(path + "Next.gif"));
        prev = new JButton(new ImageIcon(path + "Prev.gif"));
        save = new JButton(new ImageIcon(path + "save.gif"));

        //tooltips for buttons
        first.setToolTipText("Go to First Record");
        last.setToolTipText("Go to Last Record");
        next.setToolTipText("Go to Next Record");
        prev.setToolTipText("Go to Prev Record");
        save.setToolTipText("Save File");

        //register frame to listen for buttons' action events
        first.addActionListener(this);
        last.addActionListener(this);
        next.addActionListener(this);
        prev.addActionListener(this);
        save.addActionListener(this);

        //set up toolbar and add componenets to it
        JToolBar myToolBar = new JToolBar();
        myToolBar.add(first);
        myToolBar.add(prev);
        myToolBar.add(next);
        myToolBar.add(last);
        myToolBar.add(save);
        add(myToolBar, BorderLayout.NORTH);

        //text fields
        JTextField fn = new JTextField(30);
        JTextField mi = new JTextField(30);
        JTextField ln = new JTextField(30);
        JTextField t = new JTextField(30);
        JTextField s = new JTextField(30);
        JTextField y = new JTextField(30);

        y.setEditable(false);

        //create the pd Jlist
        pdNames = new Vector();
        JList pd = new JList(pdNames);
        LabelComponent pdText = new LabelComponent("Project Description", pd);
        pd.addListSelectionListener(this);

        // pd = new JList(myArray);
        pd.setFixedCellHeight(20);
        pd.setFixedCellWidth(90);

        // pd.addListSelectionListener(this);


        //labels
        LabelComponent fnText = new LabelComponent("First Name", fn);
        LabelComponent miText = new LabelComponent("MI", mi);
        LabelComponent lnText = new LabelComponent("Last Name", ln);
        LabelComponent tCombo = new LabelComponent("Title", titleCombo);
        LabelComponent tText = new LabelComponent("Telephone", t);
        LabelComponent sText = new LabelComponent("Salary", s);
        LabelComponent dCombo = new LabelComponent("Department", departmentCombo);
        LabelComponent yText = new LabelComponent("Years in Service", y);
        //  LabelComponent pdText = new LabelComponent("Project Description", pd);


        JPanel p1 = new JPanel();
        p1.setLayout(new GridLayout(9, 2, 1, 15));


        p1.add(fnText);
        p1.add(miText);
        p1.add(lnText);
        p1.add(tCombo);
        p1.add(tText);
        p1.add(sText);
        p1.add(dCombo);
        p1.add(yText);


        pdText.add(pd);
       // pdText.setEditable(false);

        //set layout for frame
        setLayout(new BorderLayout());
        add(p1, BorderLayout.EAST);
        add(myToolBar, BorderLayout.NORTH);
        add(pdText, BorderLayout.SOUTH);

        //set up the frame properties
        setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
        setSize(470, 550);
        setLocationRelativeTo(null);
        setVisible(true);

        if (dbs2.isConnected()) {
            boolean two = dbs2.processQuery("select e.FirstName, e.middlename, e.lastname, e.title, e.workphone, format(e.salary, 'currency'), d.departmentname, e.yearsinservice from employees e, departments d", false);
            if (two) {
                vc = new Vector();
                while (dbs2.nextRecord()) {
                    vc.addElement(dbs2.getField(4));
                }
            }
            titleCombo = new JComboBox(vc);
        }

        if (dbs3.isConnected()) {
            boolean three = dbs3.processQuery("select distinct(departmentname) from departments", false);
            if (three) {
                vc2 = new Vector();
                while (dbs3.nextRecord()) {
                    vc2.addElement(dbs3.getField(1));
                }
            }
            departmentCombo = new JComboBox(vc2);
        }

        if (dbs4.isConnected()) {
            boolean four = dbs4.processQuery("select projectdescription from projects", false);
            if (four) {
                vc3 = new Vector();
                while (dbs4.nextRecord()) {
                    vc3.addElement(dbs4.getField(1));
                }
            }
            pd.setListData(vc3);
        }
        if (dbs.isConnected()) {
            boolean one = dbs.processQuery("select * from employees, departments", false);
            if (one) {
                while (dbs.nextRecord()) {

                    fn.setText(dbs.getField(2));
                    mi.setText(dbs.getField(3));
                    ln.setText(dbs.getField(4));
                    titleCombo.setSelectedItem(dbs.getField(5));
                    t.setText(dbs.getField(6));
                    s.setText(dbs.getField(7));
                    departmentCombo.setSelectedItem(dbs.getField(9));
                    y.setText(dbs.getField(8));
                  }
            }
        }
    }

    //project descriptions method
   /* public Vector getProjectDescription() {
    String pdSQL = "Select projectDescription from projects;";
    source.processQuery(pdSQL, true);
    //create new vector for list of project descriptions
    pdNames = new Vector();
    while (source.nextRecord()) {
    pdNames.addElement(source.getField(3));
    }
    return pdNames;
    }*/
    JMenuItem ListeningMenuItem(String label) {
        JMenuItem mi = new JMenuItem(label);
        mi.setActionCommand(label);
        mi.addActionListener(this);
        return mi;
    }

    public void valueChanged(ListSelectionEvent e) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    /*public void valueChanged(ListSelectionEvent e) {

        //set text boxes, state combo box
        String infoSQL = "select * from employees";
        if (source.processQuery(infoSQL, false)) {
            while (source.nextRecord()) {
                fn.setText(source.getField(1));
                mi.setText(source.getField(2));
                ln.setText(source.getField(3));
                t.setText(source.getField(4));
                //  s.setSelectedItem(source.getField(5));
                y.setText(source.getField(6));
                //  pd.setText(source.getField(7));
            }
        }
        throw new UnsupportedOperationException("Not supported yet.");
    }
    // class for a labeled component*/

    class LabelComponent extends JPanel {

        JLabel l;

        public LabelComponent(String s, Component c) {
            setLayout(new BorderLayout());
            l = new JLabel(s);
            l.setHorizontalAlignment(SwingConstants.CENTER);
            add(l, BorderLayout.WEST);
            add(c, BorderLayout.EAST);
        }
    }

    public void actionPerformed(ActionEvent e) {
//get command
       // String command = e.getActionCommand();
       // action = e.getActionCommand();

        try {
if (e.getSource() == first){
   // new dialog(this);
    
                 
   source.firstRecord();
}
            




            /*if (command.equals("Save")) {

                String updateSQL = "UPDATE Employees"
                        + " SET first_name = '" + fn.getText() + "',"
                        + " middle_name = '" + mi.getText() + "',"
                        + " last_name = '" + ln.getText() + "',"
                        + " title = '" + titleCombo.getSelectedItem().toString() + "',"
                        + " work_phone = '" + t.getText() + "'"
                        + " salary = '" + s.getText() + "'"
                        + " department = '" + departmentCombo.getSelectedItem().toString() + "'"
                        + " yearsinservice = '" + y.getText() + "'";
                //   + " projectdescription = '" + pd.getSelectedItem().toString() + "'"
                //   + " WHERE Customer_ID = " + IDText.getText();

                /*   source.processUpdate(updateSQL);
                pd.setListData(getProjectDescription());
                */
        } catch (Exception a) {
            System.out.println(a.toString());
        }

        throw new UnsupportedOperationException("Not supported yet.");
    }

    public static void main(String args[]) {
        DbSource dbs = new DbSource("empdb", true);

        if (!dbs.isConnected()) {
            System.out.println("Connection Error: " + dbs.getErrorMessage());
            System.exit(1);
        }

        GUIDatabase m = new GUIDatabase();
        m.setVisible(true);
        m.setLocationRelativeTo(null);
        m.setDefaultCloseOperation(EXIT_ON_CLOSE);
    }
}

package Assignment2;

import java.sql.*;

public class DbSource {

   public ResultSet rs;
    public ResultSetMetaData rsmd;
   public String error;
    public Connection con;

    public DbSource(String b, boolean a) {
        try {
            String dataSource;
            // connect to ODBC database
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            //Evaluate connection type
            if (a) {
                dataSource = "jdbc:odbc:" + b;
            } else {
                dataSource = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=" + b;
            }
            //get connection
            con = DriverManager.getConnection(dataSource, null, null);

        } catch (Exception e) {
            error = e.toString();
        }
    }

    public boolean isConnected() {
        //test for connection validity
        boolean x;
        try {
            if (con == null || con.isClosed()) {
                x = false;
            } else {
                x = true;
            }
            return x;
        } catch (SQLException e) {
            error = e.toString();
            return false;
        }
    }

    public boolean processQuery(String sqlSelect, boolean s) {
        //process sql statement passed along with whether to allow bidirectional scrolling
        try {
            Statement stmt;
            if (s) {
            stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
            ResultSet.CONCUR_UPDATABLE);
            }else{
            stmt = con.createStatement();
            }
            //Formulate ResultSet
            rs = stmt.executeQuery(sqlSelect);
            //ResultSet MetaData

            rsmd = rs.getMetaData();

        }


         catch (SQLException e) {
            error = (e.toString());
            rs = null;
            return false;
        }
        return true;
    }

    public int processUpdate(String sqlStatement) {
        // process Update
        try {
            int nbr = 0;
            Statement stmt = con.createStatement();
            nbr = stmt.executeUpdate(sqlStatement);
            return nbr;

        } catch (SQLException e) {
            error = e.toString();
            return -1;
        }
    }

    public boolean nextRecord() {
        //proceed to next record
        try {
            if (rs.next()) {
                return true;
            } else {
                return false;
            }

        } catch (SQLException e) {
            error = e.toString();
            return false;

        }
    }

    public boolean prevRecord() {
        //move cursor back one record
        boolean a = false;
        try {
            if (rs.previous()) {
                a = true;
            }
            return a;
        } catch (SQLException e) {
            error = e.toString();
            System.out.println("Start of File");
            return false;
        }
    }

    public boolean firstRecord() {
        // move cursor to first record
        try {
            rs.first();
            return true;
        } catch (SQLException e) {
            error = e.toString();
            System.out.println("End of File");
            return false;
        }
    }

    public boolean lastRecord() {
        //move cursor to last record in result set
        try {
            rs.last();
            return true;
        } catch (SQLException e) {
            error = e.toString();
            System.out.println("End of File");
            return false;
        }
    }

    public String getField(int x) {
        //get field from result set
        try {
            String a = rs.getString(x);
            return a;
        } catch (Exception e) {
            error = (e.toString());
            return null;
        }
    }

    public String getFieldName(int x) {
        //get field name in result set
        try {
            String a = rsmd.getColumnName(x);
            return a;
        } catch (SQLException e) {
            error = e.toString();
            return null;
        }
    }

    public String getFieldType(int x) {
        //get field type from meta data in result set
        try {
            String a = rsmd.getColumnTypeName(x);
            return a;
        } catch (SQLException e) {
            error = e.toString();
            return null;
        }
    }

    public int getFieldCount() {
        //get count of columns from metadata result set
        try {
            int a = rsmd.getColumnCount();
            return a;
        } catch (SQLException e) {
            System.out.println(e.toString());
        }
        return -1;


    }

    public void close() {
        //close connection
        try {
            con.close();
        } catch (SQLException e) {
        }
    }

    public String getErrorMessage() {
        return error;
    }

    public String[] getTables() {
        //get table metadata info
        try {
            DatabaseMetaData dbmd = con.getMetaData();
            String[] tables = {"TABLE"};
            rs = dbmd.getTables(null, null, null, tables);

            int nbrRows = 0;
            while (rs.next()) {
                nbrRows++;
            }

            String[] tblNames = new String[nbrRows];
            rs = dbmd.getTables(null, null, "%", tables);
            nbrRows = 0;
            while (rs.next()) {
                tblNames[nbrRows] = rs.getString(3);
                nbrRows++;
            }
            return tblNames;
        } catch (Exception e) {
            error = e.toString();
            return null;
        }
    }

    public String resultSetToXML() {
        //turn result set to XML formatting
        String rsToXML = "";
        try {

            rsmd = rs.getMetaData();

            // begin to write XML document
            rsToXML += ("<?xml version=\"1.0\"?>\r\n");

            // Root node
            rsToXML += ("<ResultSet>");

            // get metadata into XML document
            rsToXML += (" <MetaData>");
            for (int i = 1; i <= getFieldCount(); i++) {
                rsToXML += ("   <Column>" + getFieldName(i) + "</Column>");
            }
            rsToXML += (" </MetaData>");

            // get data into XML document
            rsToXML += (" <Data>");
            while (rs.next()) {
                rsToXML += ("  <Row>");
                for (int i = 1; i <= getFieldCount(); i++) {
                    rsToXML += ("     <" + getFieldName(i) + ">"
                            + rs.getString(i)
                            + "</" + getFieldName(i) + ">");
                }
                rsToXML += ("  </Row>");
            }
            rsToXML += (" </Data>");
            rsToXML += ("</ResultSet>");


        } catch (Exception e) {
            error = e.toString();
        }
        return rsToXML;
    }
}

[thomp2bl]

Still can't figure this one out. I know I need to have it under actionPerformed but I can't come up with the code to go to the next record and have the fields filled in with the new data.

[Fubarable]

That's a heck of a lot of code to go through without pay. Care to break the problem down for us?

[thomp2bl]

haha yeah

I have the fields populated from the database and just need to go to the next record when i click on a button. It should be something like this but I don't know what the value of query needs to be. What should i have query as?

public void actionPerformed(ActionEvent e) {
//get command
String command = e.getActionCommand();


if (query){ //not sure what query is supposed to be?
if command.equals("next"))
if (dbs.nextRecord)
getRecord()

[javaaddict02]

//imports
public class myclass extends Frame
{

static ResultSet res;
static Connection conn;
static Statement stat;

private JTextField txttitle,txtauthor; //textfields

//constructor
{


try{

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
conn = DriverManager.getConnection("jdbc:odbc:javadb");
stat=conn.createStatement();
res=stat.executeQuery("select * from books");
res.next();
txttitle.setText(res.getString("title"));
txtauthor.setText(res.getString("author"));


}
catch(Exception e)
{
System.out.println("there was some error in establishin gconnection : "+e);
}
//end try catch

}//end constructor:)

public void actionPerformed(ActionEvent e)

{

if(e.getSource()==bnext)//bnext > button name
{

try
{
if(res.next()==true)
{
txttitle.setText(res.getString("title"));
txtauthor.setText(res.getString("author"));


}
else
{
stat.close();
stat=conn.createStatement();
res=stat.executeQuery("Select * from books");
res.next();
txttitle.setText(res.getString("title"));
txtauthor.setText(res.getString("author"));



}
}
catch(Exception e2)
{

}
}