Results 1 to 2 of 2
  1. #1
    blink16j is offline Member
    Join Date
    Jan 2013
    Posts
    9
    Rep Power
    0

    Default Application with ODBC connection- HELP

    I have a simple application here which needs to connect to the data source and clear, read, load, and write to the database by clicking the buttons. All of my database connectivity must be located in a separate DbWork class. My problem is when I click the buttons I get error messages. I'm not able to call the methods (clearDB(), readEmployee(), loadDB(), writeEmployee()) in the Employee7 class because they are not static and I'm trying to reference them from a static context.

    Anyone, please help?



    EmployeeGUI7.java:
    Java Code:
    package Lab7484;
    import java.awt.*;
    import javax.swing.*;
    import java.awt.event.*;
    import javax.swing.event.*;
    
    public class EmployeeGUI7 extends JFrame implements ActionListener {
        private JButton clearButton = new JButton("1) Clear Database");
        private JButton readButton = new JButton("2) Read File");
        private JButton loadButton = new JButton("3) Load DB");
        private JButton writeButton = new JButton("4) Write Report");
        private JLabel label = new JLabel(" Lab 7 Database Process ");
    
        Font font1 = new Font("SansSerif", Font.BOLD, 18);
        
      
        
    
        public EmployeeGUI7() {
            
            // Create panel p1
    
            JPanel p1 = new JPanel();
            p1.setLayout(new GridLayout(4, 1));
            ImageIcon picture = new ImageIcon("src\\Lab7484\\dukedog.jpg");
    
            p1.add(clearButton);
            clearButton.setBackground(Color.yellow);
            clearButton.setMnemonic('e');
            clearButton.setToolTipText("Click this to clear the database.");
            clearButton.addActionListener(this);
            
            p1.add(readButton);
            readButton.setBackground(Color.lightGray);
            readButton.setMnemonic('R');
            readButton.setToolTipText("Click this to read the database file.");
            readButton.addActionListener(this);
            
            p1.add(loadButton);
            loadButton.setBackground(Color.pink);
            loadButton.setForeground(Color.black);
            loadButton.setMnemonic('L');
            loadButton.setToolTipText("Click this to load the database file");
            loadButton.addActionListener(this);
            
            p1.add(writeButton);
            writeButton.setBackground(Color.cyan);
            writeButton.setMnemonic('W');
            writeButton.setToolTipText("Click this to write the employee report.");
            writeButton.addActionListener(this);
    
    
            // Create panel p2 for text field and p1
            JPanel p2 = new JPanel(new BorderLayout());
            p2.add(p1, BorderLayout.CENTER);
    
            // Add the contents into the frame
            add(p2, BorderLayout.CENTER);
            add(label, BorderLayout.WEST);
            add(new JLabel(picture), BorderLayout.EAST);
            label.setFont(font1);
            label.setForeground(Color.gray);
    
            pack();
            setTitle("By James Doyle");
            setSize(850, 300);
            setLocationRelativeTo(null);
            setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
            setVisible(true);
    
            
        }
    
        public static void main(String[] args) {
            new EmployeeGUI7();
            
        }
    
        public void actionPerformed(ActionEvent e) {
    
            if (e.getSource() == clearButton) {
                
               
                if (Employee7.clearDB()) {
                    JOptionPane.showMessageDialog(null, "Clear Database");
                } else {
                    JOptionPane.showMessageDialog(null, "Error Clearing Database");
                }
            } else if (e.getSource() == readButton) {
               
                try {
                    Employee7.readEmployee();
                    JOptionPane.showMessageDialog(null, "Read Database");
                } catch (Exception f) {
                    JOptionPane.showMessageDialog(null, "Database Read Error");
                }
            } else if (e.getSource() == loadButton) {
                
                if (Employee7.loadDB()) {
                    JOptionPane.showMessageDialog(null, "Load Database");
                } else {
                    JOptionPane.showMessageDialog(null, "Error Loading Database");
                }
            } else if (e.getSource() == writeButton) {
               
                try {
                    Employee7.writeEmployee();
                    JOptionPane.showMessageDialog(null, "Write Report");
                } catch (Exception f) {
                    JOptionPane.showMessageDialog(null, "Report Write Error");
                }
            }
        }
    }
    Employee7.java:
    Java Code:
    package Lab7484;
    import java.io.*;
    import javax.swing.JOptionPane;
    import java.io.FileReader.*;
    import java.util.*;
    
    
    public class Employee7 {
        
        private String ID;
        private String firstName;
        private String lastName;
        private String gender;
        private String job;
        private double wage;
        protected static final int maxEmployee = 101;
        protected static Employee7 employee[] = new Employee7[maxEmployee];
        protected static int totEmployee = 0;
        
        DbWork dbw = new DbWork("EmpDb");
        
        // Default Employee Constructor
        public Employee7() {
            setID("0000");
            setFirstName("first");
            setLastName("last");
            setGender("unknown");
            setJob("administrative");
            setWage(0000);
        }
    
        // Overloaded Employee Constructor
        public Employee7(String eID, String first, String last, String gender, String job, double wage) {
            setID(eID);
            setFirstName(first);
            setLastName(last);
            setGender(gender);
            setJob(job);
            setWage(wage);
    ;
        }
    
        // Get methods for each instance variable
        public String getID() {
            return ID;
        }
        public String getFirstName() {
            return firstName;
        }
        public String getLastName() {
            return lastName;
        }
        public String getJob() {
            return job;
        }
        public double getWage() {
            return wage;
        }
        public String getGender() {
            return gender;
        }
        
        // Set methods for each instance variable
        public void setID(String newID) {
            boolean temp = true;
            
            if(newID.length() == 4){
                for (int i = 0; i < newID.length(); i++){
                    if ((int)newID.charAt(i) < 48 || (int)newID.charAt(i) > 57) {
                        temp = false;
                    }
                
            if(temp){
                ID = newID;
            }
                }
            }
            else
                ID = "0000";
            
            if(newID.length() > 4){
                ID = "0000";
            }
            if(newID.length() < 4){
                if(newID.length() == 1){
                ID = "000" + newID;
            }
            else if(newID.length() == 2){
                ID = "00" + newID;
            }
            else if(newID.length() == 3){
                ID = "0" + newID;
            }
            }
        }
        
    
        public void setFirstName(String newFirstName) { 
            if (newFirstName.equals("")){
                firstName = "First";
            }
            else if (newFirstName.length() < 2) {
                firstName = "First";
            }
            else{
                firstName = newFirstName.substring(0, 1).toUpperCase() + newFirstName.substring(1).toLowerCase();
            }
        }
        public void setLastName(String newLastName) {
            if (newLastName.equals("")){
                lastName = "Last";
            }
            else if (newLastName.length() < 2){
                lastName = "Last";
            }
            else{
                lastName = newLastName.substring(0, 1).toUpperCase() + newLastName.substring(1).toLowerCase();
            }
        }
        public void setJob (String newJob) {
            if (newJob.equalsIgnoreCase("Sales") || newJob.equalsIgnoreCase("Administrative") || newJob.equalsIgnoreCase("Technical"))
                job = newJob;
            else job = "Sales";
        }
        public void setWage (double newWage) {
            if (newWage < 0)
                wage = 0;
            else wage = newWage;
        }
        public void setGender (String newGender) {
            if (newGender == null) gender = "unknown";
            else gender = newGender;
        }
    
        // Return amount of employee info based on boolean parameter
        public String employeeInfo(boolean b){
            String infoString = "";
    
            if (b == true){
                infoString = toString() +
                        "\nEmployee ID: " + getID() +
                        "\nFirst Name: " + getFirstName() +
                        "\nLast Name: " + getLastName() +
                        "\nJob Type: " + getJob() +
                        "\nWage: " + getWage() +
                        "\nGender: " + getGender();
            }
            else{
                infoString += getFirstName() + " " + getLastName();
            }
            return infoString;
        }
    
        // Compare string paramter to Employee first and last name
        public boolean equals(String firstLast){
    
            boolean test = true;
            
            String testName = getFirstName() + " " + getLastName();
    
            if (firstLast.equalsIgnoreCase(testName)){
                test = true;
            }
            else test = false;
    
            return test;
        }
    
        // Compare integer parameter to Employee ID
        public boolean equals(int idNum){
    
            boolean test = true;
    
            if (Integer.parseInt(getID()) == idNum){
                test = true;
            }
            else test = false;
    
            return test;
        }
        
         // List names of employees in array
        public static String listEmployees() {
            String list = "";
            for (int i = 0; i < totEmployee; i++) {
                list += employee[i].getID() + " " + employee[i].getFirstName() + " " + employee[i].getLastName() + "\n";
            }
            return list;
        }
        
         // Find an employee in the array
         public static int findEmployee(String name) {
            for (int i = 0; i < totEmployee; i++) {
                String searchNames = employee[i].getFirstName() + " " + employee[i].getLastName();
                if (searchNames.equalsIgnoreCase(name)) {
                    return i;
                }
            }
            return -1;
        }
         
         // Create an employee object and add it to the array of employees
        static boolean addEmployee(String num, String first, String last, String gender, String job, double pay) {
            if (totEmployee != maxEmployee) {
                employee[totEmployee] = new Employee7(num, first, last, gender, job, pay);
                totEmployee++;
                return true;
            }
            return false;
        }
        
        // Calculate the average wage of all employees in the array
        public static double averageWage(){
            int i = 0;
            int total = 0;
            for (i = 0; i < totEmployee; i++){
                total += employee[i].getWage();
                    }
            double avg = total / totEmployee;
            return avg;
            }
        
        // Obtain a particular Employee from the array
        public static Employee7 getEmployee(int index){
            return employee[index];
            }
        
        // Return total employees
        public static int getTotEmployee(){
            return totEmployee;
            }
        
        // Return a string representation of object
        public String toString(){
            return firstName + " " + lastName;
        }
        
        
        
        // Reads input7 and puts into array
        public static void readEmployee() {
            String first;
            String last;
            double pay;
            String job;
            String num;
            String gender;
    
            String path = "C:/temp/";
            try {
                java.io.File file = new java.io.File(path + "input7.txt");
                java.util.Scanner employeeInput = new java.util.Scanner(file);
    
                while (employeeInput.hasNext()) {
                    first = employeeInput.next();
                    last = employeeInput.next();
                    pay = employeeInput.nextDouble();
                    job = employeeInput.next();
                    num = employeeInput.next();
                    gender = employeeInput.next();
                    Employee7.addEmployee(num, first, last, gender, job, pay);
                }
                employeeInput.close();
            } catch (IOException e) {
                System.exit(-1);
            }
        }
        
        // Writes employees in array to input7   
        public void writeEmployee() {
            
            String sql = "Select EmployeeId, GenderAbb, JobTypeId from Employee";
            int index = 0;
            String outputLine;
            String outputp;
    
            String genderJob[][] = new String[maxEmployee][2];
    
            
            for (int i = 0; i < maxEmployee; i++) {
                for (int j = 0; j < 2; j++) {
                    genderJob[i][j] = " ";
                }
            }
            try {
                if (!dbw.isConnected()) {
                    dbw.Constructor("EmpDb");
                }
    
                dbw.processQuery(sql);
    
                try {
    
                    File outputFile = new File("C:/temp/report7.txt");
                    PrintWriter PW = new PrintWriter(outputFile);
    
                    PW.println("Employee Gender and Job Description Report");
                    PW.println(" ");
                    PW.println("Employee" + " " + "ID" + "\t" + "Gender" + "\t\t" + "Job" + " " + "Type");
                    PW.println(" ");
    
                    while (dbw.nextRecord()) {
    
                        index = Integer.parseInt(dbw.getField("EmployeeID"));
                        genderJob[index][0] = dbw.getField("GenderAbb");
                        genderJob[index][1] = dbw.getField("JobTypeId");
                    }
    
                    for (int p = 1; p < maxEmployee; p++) {
                        if (p <= 0000 || p >= 9999) {
                            outputp = "0000";
                        } else if (p <= 9 && p >= 1) {
                            outputp = "000" + p;
                        } else if (p <= 99 && p >= 10) {
                            outputp = "00" + p;
                        } else if (p <= 999 && p >= 100) {
                            outputp = "0" + p;
                        } else {
                            outputp = Integer.toString(p);
                        }
                        outputLine = outputp;
                        for (int n = 0; n < 2; n++) {
                            outputLine = outputLine + "\t\t" + genderJob[p][n];
                        }
                        PW.println(outputLine);
                    }
                    PW.close();
                    
                } catch (Exception e) {
                    
                    JOptionPane.showMessageDialog(null, "Error");
                    System.exit(-1);
                }
            }
            
            catch (Exception z) {
            }
        }
        
        
        // Load DB
        public boolean loadDB() {
            
            if (!dbw.isConnected()) {
                dbw.Constructor("EmpDb");
            }
            
            if (totEmployee == 0) {
                return false;
            }
            
            else {
                try {
                    
                    for (int i = 0; i < totEmployee; i++) {
                        String sql = "insert into Employee (EmployeeId, " +
                                "FirstName, " +
                                "LastName, " +
                                "GenderAbb, " +
                                "JobTypeId, " +
                                "Wage) " +
                                "values('" + employee[i].ID + "', '" +
                                employee[i].firstName + "', '" +
                                employee[i].lastName + "', '" +
                                employee[i].gender + "', '" +
                                employee[i].job + "', " +
                                employee[i].wage + ")";
    
                        dbw.processUpdate(sql);
                    }
                }
                
                catch (Exception e) {
                    JOptionPane.showMessageDialog(null, "Database Load Error");
                    return false;
                }
                
                return true;
            }
               }
        
        // Clear DB
        public boolean clearDB() {
            
            if (!dbw.isConnected())
                dbw.Constructor("EmpDb");
            
            if (totEmployee != 0)
                return false;
            
            else
                try {
                    
                    String sql = "delete * from Employee";
                    dbw.processUpdate(sql);
                }
                catch (Exception e) {
                    JOptionPane.showMessageDialog(null, "Clear Database Error");
                }
            return true;
        }
    }
    DbWork.java:
    Java Code:
    package Lab7484;
    import java.sql.*;
    import javax.swing.JOptionPane;
    import java.util.*;
    import java.io.*;
    
    
    public class DbWork {
        
        // Set member class variables
        Connection connection;
        ResultSet rs;
        Statement stm;
        String error;
        String DNS;
        
        public DbWork(String DNS) {
            this.DNS = DNS ;
            Constructor(DNS);           
        }
        
        // Connect to the database
        public void Constructor(String EmpDb) {
            try {
                Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
                String URL = "jdbc:odbc:" + EmpDb;
                connection = DriverManager.getConnection(URL,"","");
     
            } catch (Exception e) {
                JOptionPane.showMessageDialog(null, "Error");
            }
        }
        
        // Check for connection
        public boolean isConnected() {
            try {
                if (connection == null || connection.isClosed()) {
                    return false;
                } else {
                    return true;
                }
            } catch (Exception e) {
                error = e.toString();
                return false;
            }
        }
      
        // Set sql select statement query to result set
        public boolean processQuery(String sqlQuery) {
            try {
                Statement stmt = connection.createStatement();
                rs = stmt.executeQuery(sqlQuery);
                return true;
            }
            
            catch (Exception e) {
                error = e.toString();
                rs = null;
                return false;
            }
        }
        
        // Set sql update, delete, or insert statement to result set
        public boolean processUpdate(String sqlUpdate) {
           
            try {
                Statement stmt = connection.createStatement();
                rs = stmt.executeQuery(sqlUpdate);
                return true; 
            
            } catch (Exception e) {
                error = e.toString();
                rs = null;
                return false;
            }
        } 
        
        // Go to next record in current result set
        public boolean nextRecord() {
            try { 
                
                if (rs.next()) {
                    rs.next();
                    return true;
                }
                else {
                    error = "end of file";
                    return true;
                }
            } catch (Exception e) {
                error = e.toString();
                return false;
            }
        }
        
        //
        public String getField(int z) {
            String field;
            try {
                field = rs.getString(z);
                return field;
            } catch (Exception e) {
                error = e.toString();
                return null;
            }
    
        }
        
        //
        public String getField(String z) {
            String field;
            try {
                field = rs.getString(z);
                return field;
            } catch (Exception e) {
                error = e.toString();
                return null;
            }
        } 
        
        // Close current connection to DB
        public void close() {
          try { 
              connection.close();          
        } catch (Exception e) {
                error = e.toString();
            }
        }
        
        // Return current error message
        public String getErrorMessage() {
            return error;
        }
        
     
        
    }

  2. #2
    Tolls is online now Moderator
    Join Date
    Apr 2009
    Posts
    11,446
    Rep Power
    18

    Default Re: Application with ODBC connection- HELP

    Your Employee7 class is doing too much.
    It should just represent a single Employee (as shown by its attributes).
    All the other stuff should be done by an EmployeeDAO (Data Access Object) that accesses the database and creates/saves Employee7 instances.
    Please do not ask for code as refusal often offends.

Similar Threads

  1. Replies: 0
    Last Post: 02-16-2011, 01:30 PM
  2. DSN less connection strings for ODBC
    By mssvv in forum New To Java
    Replies: 0
    Last Post: 09-30-2010, 09:23 AM
  3. Replies: 2
    Last Post: 06-30-2010, 02:00 PM
  4. Replies: 4
    Last Post: 06-27-2010, 06:03 PM
  5. Help me this error in JDBC-ODBC connection
    By lordelf in forum Java Servlet
    Replies: 3
    Last Post: 04-04-2009, 08:37 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •