home *** CD-ROM | disk | FTP | other *** search
/ Symantec Visual Cafe for Java 2.5 / symantec-visual-cafe-2.5-database-dev-edition.iso / DBServ / SAMPLES / SAMPLES.ZIP / Employees / DBFUNCTIONS.JAVA next >
Encoding:
Text File  |  1997-02-21  |  30.6 KB  |  683 lines
  1. /**********************************************************************************************************************
  2. *                                                                                                                     *
  3. *                                                                                                                     *
  4. *                                                                                                                     *
  5. *  Class name:  dbFunctions()                                                                                         *
  6. *     Purpose:  This class was designed to encapsulate database navigation and related methods.                       *
  7. *                                                                                                                     *
  8. *     Imports:  java.awt.*                                                                                            *
  9. *               symjava.sql.*                                                                                         *
  10. *               java.util.Properties                                                                                  *
  11. *                                                                                                                     *
  12. *  Methods include:   private boolean establishDBConnection()                                                         *
  13. *                     public String getNextID()                                                                       *
  14. *                     public boolean goFirstRecord(String, String, String)                                            *
  15. *                     public boolean goLastRecord(String, String)                                                     *
  16. *                     public void gotoRecord(TextField[], Checkbox[], List[], Button[])                               *
  17. *                     public int idLookup(String, String)                                                             *
  18. *                     public int insertNewValues(TextField[], Checkbox[], List[], Button[])                           *
  19. *                     public boolean nextRecord(ResultSet)                                                            *
  20. *                     public void populateMgrList(int, List)                                                          *
  21. *                     public boolean populateScreen(TextField[], Checkbox[], List[], TextField ltfStatusBar)          *
  22. *                     private ResultSet sendSQLStatement(String, Statement)                                           *
  23. *                     public int updateRecord(TextField[], Checkbox[], List[])                                        *
  24. *                                                                                                                     *
  25. *                                                                                                                     *
  26. *  Variable naming convention:  Variable beginning with lower case g indicate global variables                        *
  27. *                               First letter of variable indicates the type of object (ie. String svar, boolean bvar) *
  28. *                                                                                                                     *
  29. * Additional Notes:                                                                                                   *
  30. * THIS SOFTWARE HAS BEEN COMPILED AND EXECUTED SUCCESSFULLY IN SPECIFIC SYMANTEC                                      *
  31. * ENVIRONMENTS, AND IS BEING PROVIDED ONLY AS SAMPLE CODE.                                                            *
  32. *                                                                                                                     *
  33. * SYMANTEC MAKES NO REPRESENTATIONS OR WARRANTIES ABOUT THE SUITABILITY OF THE SOFTWARE,                              *
  34. * EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF                                   *
  35. * MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE, OR NON-INFRINGEMENT.  SYMANTEC SHALL NOT                         *
  36. * BE LIABLE FOR ANY DAMAGES SUFFERED BY LICENSEE AS A RESULT OF USING, MODIFYING, OR DISTRIBUTING                     *
  37. * THIS SOFTWARE OR ITS DERIVATIVES.                                                                                   *
  38. *                                                                                                                     *
  39. *  Please see the Sample Code Configuration  item in the Read Me file for additional                                  *
  40. *  information about the sample database used in this code.                                                           *
  41. *                                                                                                                     *
  42. * Copyright (c) 1996 Symantec.  All Rights Reserved.                                                                  *
  43. *                                                                                                                     *
  44. **********************************************************************************************************************/
  45.  
  46. import java.awt.*;
  47. import symjava.sql.*;
  48. import java.util.Properties;
  49.  
  50. class dbFunctions {
  51. //  DB engine connection variables...
  52.     Driver dvrdbDriver;
  53.     Connection conserverConnection;
  54.  
  55. //  Global ResultSets and Statements
  56.     Statement gstemployee = null, gstdescSelect = null;
  57.     PreparedStatement gpsdept = null, gpsmgr = null;
  58.     ResultSet grsemployee;
  59.  
  60. //  Global Misc. variables defined
  61.     int girecordCount = 0, gicurrentRecord = 0;
  62.     String gssearchClause = new String();
  63.     String gsand = new String();
  64.     String gswhere = new String();
  65.     boolean gbexecuteSearch = false, gbInsert = false;
  66.  
  67.     public dbFunctions() {
  68.         if (!establishDBConnection())
  69.             System.out.println("Failed to establish connection!  Check status of server, server URL, and ODBC DSN configuration.");
  70.         else
  71.             if (!goFirstRecord(gssearchClause, gswhere, gsand))
  72.                 System.out.println("Failed retrieving first record on startup");
  73.     }
  74.  
  75. /*  Delete method intentionally omitted due to referential integrity contraints that may be imposed on the engine.
  76.     If included, such a method might look similiar to the following:
  77.  
  78.     public boolean deleteRecord(TextField tfempId) {
  79.         Statement stdelete;
  80.         ResultSet rsdelete;
  81.         try {
  82.             stdelete = conserverConnection.createStatement();
  83.         }
  84.         catch (SQLException e) {
  85.             System.out.println(e.getMessage());
  86.             return false;
  87.         }
  88.         String sdelete = new String("Delete from employee where emp_id = '" + tfempId.getText() + "'");
  89.         rsdelete = sendSQLStatement(sdelete, stdelete);
  90.         if (rsdelete.equals(null)) {
  91.             System.out.println("Delete select statement failed!");
  92.             return false;
  93.         }
  94.         else {
  95.             if (!nextRecord(rsdelete)){
  96.                 System.out.println("Failure selecting record to delete!");
  97.                 return false;
  98.             }
  99.             try {
  100.                 stdelete.executeUpdate(sdelete);
  101.                 stdelete.close();
  102.             }
  103.             catch (SQLException e) {
  104.                 System.out.println(e.getMessage());
  105.                 return false;
  106.             }
  107.         return true;
  108.         }
  109.     }
  110. */
  111.  
  112.     private boolean establishDBConnection() {
  113.         String serverURL = "jdbc:dbaw://localhost:8889/watcom/SQL Anywhere 5.0 Sample/SQL Anywhere";
  114.         String dbawDriver = "symantec.itools.db.jdbc.Driver";
  115.         Properties pprops = new Properties();
  116.         pprops.put("user","dba");
  117.         pprops.put("password","sql");
  118.         try {
  119.             dvrdbDriver = (Driver)Class.forName(dbawDriver).newInstance();
  120.             conserverConnection = dvrdbDriver.connect(serverURL, pprops);
  121.         }
  122.         catch (SQLException e) {
  123.             System.out.println(e.getMessage());
  124.             return false;
  125.         }
  126.         catch (ClassNotFoundException e) {
  127.             System.out.println(e.getMessage());
  128.             return false;
  129.         }
  130.         catch (IllegalAccessException e) {
  131.             System.out.println(e.getMessage());
  132.             return false;
  133.         }
  134.         catch (InstantiationException e) {
  135.             System.out.println(e.getMessage());
  136.             return false;
  137.         }
  138. //      Initialize regular and prepared statements....
  139.         try {
  140.             gstemployee = conserverConnection.createStatement();
  141.             gpsdept = conserverConnection.prepareStatement("Select dept_name from department order by dept_name");
  142.             gpsmgr = conserverConnection.prepareStatement("Select emp_fname, emp_lname from employee where dept_id = ? order by emp_lname");
  143.         }
  144.         catch (SQLException e) {
  145.             System.out.println(e.getMessage());
  146.             return false;
  147.         }
  148.         return true;
  149.     }
  150.  
  151.     public String getNextID() {
  152.         Statement stnextId = null;
  153.         int iempId = 0;
  154.         try {
  155.             stnextId = conserverConnection.createStatement();
  156.         }
  157.         catch (SQLException e) {
  158.             System.out.println(e.getMessage());
  159.             return null;
  160.         }
  161.         String sdescSelect = new String("Select emp_id from employee order by emp_id desc");
  162.         ResultSet rsemployee = sendSQLStatement(sdescSelect, stnextId);
  163.         if (rsemployee.equals(null)) {
  164.             System.out.println("Descending Select statement failed!");
  165.             return null;
  166.         }
  167.         else {
  168.             if (!nextRecord(rsemployee)) {
  169.                 System.out.println("Failure finding last record!");
  170.                 return null;
  171.             }
  172.         }
  173.         try {
  174.             iempId = rsemployee.getInt(1);
  175.             stnextId.close();
  176.         }
  177.         catch (SQLException e) {
  178.             System.out.println(e.getMessage());
  179.             return null;
  180.         }
  181.         String sempId = Integer.toString(iempId + 1);
  182.         return sempId;
  183.     }
  184.  
  185.     public boolean goFirstRecord(String ssearchClause, String swhere, String sand) {
  186.         Statement stcount = null;
  187.         String semployee = new String();
  188.         try {
  189.             stcount = conserverConnection.createStatement();
  190.         }
  191.         catch (SQLException e) {
  192.             System.out.println(e.getMessage());
  193.             return false;
  194.         }
  195.         String sselectCount = new String("Select count(e1.emp_id) from employee e1 " + swhere + " " + ssearchClause);
  196.         ResultSet rscount = sendSQLStatement(sselectCount, stcount);
  197.         if (rscount.equals(null)) {
  198.             System.out.println("Count statement failed!");
  199.             return false;
  200.         }
  201.         else {
  202.             if (!nextRecord(rscount)) {
  203.                 System.out.println("Failure counting records!");
  204.                 return false;
  205.             }
  206.             else {
  207.                 try {
  208.                     girecordCount = rscount.getInt(1);
  209.                     stcount.close();
  210.                     gicurrentRecord = 1;
  211.                 }
  212.                 catch (SQLException e) {
  213.                     System.out.println(e.getMessage());
  214.                     return false;
  215.                 }
  216.             }
  217.             semployee = "Select e1.emp_id, e1.emp_lname, e1.emp_fname, e1.street, e1.city, e1.state, e1.zip_code, e1.phone, " +
  218.                          "e1.ss_number, e1.status, e1.sex, e1.salary, e1.bene_health_ins, e1.bene_life_ins, e1.bene_day_care, " +
  219.                          "e1.start_date, e1.termination_date, e1.dept_id, department.dept_name, e2.emp_lname, e2.emp_fname " +
  220.                          "from employee e1, department, employee e2 where e1.dept_id = department.dept_id and e1.manager_id = e2.emp_id " +
  221.                          sand + " " + ssearchClause + " order by e1.emp_id";
  222.             grsemployee = sendSQLStatement(semployee, gstemployee);
  223.             if (grsemployee.equals(null)) {
  224.                 System.out.println("Initial Select statement failed!");
  225.                 return false;
  226.             }
  227.             else {
  228.                 if (!nextRecord(grsemployee)) {
  229.                     System.out.println("Failure finding first record!");
  230.                     return false;
  231.                 }
  232.             }
  233.             return true;
  234.         }
  235.     }
  236.  
  237.     public boolean goLastRecord(String ssearchClause, String sand) {
  238.         String sdescEmployee = new String();
  239.         sdescEmployee = "Select e1.emp_id, e1.emp_lname, e1.emp_fname, e1.street, e1.city, e1.state, e1.zip_code, e1.phone, " +
  240.                          "e1.ss_number, e1.status, e1.sex, e1.salary, e1.bene_health_ins, e1.bene_life_ins, e1.bene_day_care, " +
  241.                          "e1.start_date, e1.termination_date, e1.dept_id, department.dept_name, e2.emp_lname, e2.emp_fname " +
  242.                          "from employee e1, department, employee e2 where e1.dept_id = department.dept_id and e1.manager_id = e2.emp_id " +
  243.                          sand + " " + ssearchClause + " order by e1.emp_id desc";
  244.         grsemployee = sendSQLStatement(sdescEmployee, gstemployee);
  245.         if (grsemployee.equals(null)) {
  246.             System.out.println("Descending Select statement failed!");
  247.             return false;
  248.         }
  249.         else {
  250.             if (!nextRecord(grsemployee)) {
  251.                 System.out.println("Failure finding first descending record!");
  252.                 return false;
  253.             }
  254.         }
  255.         gicurrentRecord = girecordCount;
  256.         return true;
  257.     }
  258.  
  259.     public void gotoRecord(TextField[] tfList, Checkbox[] cbList, List[] lList, Button[] bList) {
  260.         int imaxLoops, iloopCnt = 0;
  261.         if (!gbexecuteSearch) {
  262.             imaxLoops = tfList.length;
  263.             for(iloopCnt = 2; iloopCnt < imaxLoops; iloopCnt++)
  264.                 tfList[iloopCnt].setEditable(false);
  265.             imaxLoops = lList.length;
  266.             iloopCnt = 0;
  267.             for(iloopCnt = 0; iloopCnt < imaxLoops; iloopCnt++)
  268.                 lList[iloopCnt].disable();
  269.             imaxLoops = cbList.length;
  270.             iloopCnt = 0;
  271.             for(iloopCnt = 0; iloopCnt < imaxLoops; iloopCnt++)
  272.                 cbList[iloopCnt].disable();
  273.             imaxLoops = bList.length;
  274.             iloopCnt = 0;
  275.             for(iloopCnt = 0; iloopCnt < imaxLoops; iloopCnt++) {
  276.                 if (iloopCnt != 4) //The Goto button...
  277.                     bList[iloopCnt].disable();
  278.                 else
  279.                     bList[iloopCnt].setLabel("Execute");
  280.             }
  281.             tfList[1].requestFocus();
  282.             gbexecuteSearch = true;
  283.         }
  284.         else {
  285.             gsand = "and";
  286.             gswhere = "where";
  287.             if (tfList[1].getText().indexOf("%") == -1)
  288.                 gssearchClause = new String("e1.emp_lname = '" + tfList[1].getText() + "'");
  289.             else
  290.                 gssearchClause = new String("e1.emp_lname like '" + tfList[1].getText() + "'");
  291.             if (!goFirstRecord(gssearchClause, gswhere, gsand)) {
  292.                 System.out.println("Failed retrieving first record of result set");
  293.                 return;
  294.             }
  295.             if (girecordCount == 0) {
  296.                 gbexecuteSearch = true;
  297.                 return;
  298.             }
  299.             imaxLoops = tfList.length;
  300.             for(iloopCnt = 2; iloopCnt < imaxLoops; iloopCnt++)
  301.                 tfList[iloopCnt].setEditable(true);
  302.             imaxLoops = lList.length;
  303.             iloopCnt = 0;
  304.             for(iloopCnt = 0; iloopCnt < imaxLoops; iloopCnt++)
  305.                 lList[iloopCnt].enable();
  306.             imaxLoops = cbList.length;
  307.             iloopCnt = 0;
  308.             for(iloopCnt = 0; iloopCnt < imaxLoops; iloopCnt++)
  309.                 cbList[iloopCnt].enable();
  310.             imaxLoops = bList.length;
  311.             iloopCnt = 0;
  312.             for(iloopCnt = 0; iloopCnt < imaxLoops; iloopCnt++) {
  313.                 if (iloopCnt != 4) //The Goto button...
  314.                     bList[iloopCnt].enable();
  315.                 else
  316.                     bList[iloopCnt].setLabel("Search");
  317.             }
  318.             gbexecuteSearch = false;
  319.         }
  320.     }
  321.  
  322.     public int idLookup(String deptName, String deptmgr) {
  323.         String sUpdate = new String();
  324.         int ideptId = 0;
  325.         Statement stUpdate = null;
  326.         ResultSet rsUpdate = null;
  327.         try {
  328.             stUpdate = conserverConnection.createStatement();
  329.         }
  330.         catch (SQLException e) {
  331.             System.out.println(e.getMessage());
  332.             return 0;
  333.         }
  334.         if (deptmgr.equals("dept"))
  335.             sUpdate = new String("Select dept_id from department where dept_name = '" + deptName + "'");
  336.         else
  337.             sUpdate = new String("Select emp_id from employee where emp_fname || ' ' || emp_lname = '" + deptName + "'");
  338.         rsUpdate = sendSQLStatement(sUpdate, stUpdate);
  339.         if (rsUpdate.equals(null)) {
  340.             System.out.println("Department lookup failed!");
  341.             return 0;
  342.         }
  343.         else {
  344.             if (!nextRecord(rsUpdate)){
  345.                 System.out.println("Failure selecting department record!");
  346.                 return 0;
  347.             }
  348.             try {
  349.                 ideptId = rsUpdate.getInt(1);
  350.                 stUpdate.close();
  351.             }
  352.             catch (SQLException e) {
  353.                 System.out.println(e.getMessage());
  354.             }
  355.         }
  356.         return ideptId;
  357.     }
  358.  
  359.     public int insertNewValues(TextField[] tfList, Checkbox[] cbList, List[] lList, Button[] bList) {
  360.         Statement stinsert = null;
  361.         int irowsInserted = 0, iloopCnt, imaxLoops, ideptId, imgrId;
  362.         String sinsertStatement = new String();
  363.         String stermDate = new String();
  364.         String dept = new String();
  365.         char chealth, clife, cday_care;
  366.         if (!cbList[0].getState())
  367.             chealth = 'N';
  368.         else
  369.             chealth = 'Y';
  370.         if (!cbList[1].getState())
  371.             clife = 'N';
  372.         else
  373.             clife = 'Y';
  374.         if (!cbList[2].getState())
  375.             cday_care = 'N';
  376.         else
  377.             cday_care = 'Y';
  378.         if (tfList[13].getText().equals(""))
  379.             stermDate = null;
  380.         else
  381.             stermDate = "'" + tfList[13].getText() + "'";
  382.         dept = "dept";
  383.         ideptId = idLookup(lList[0].getSelectedItem(), dept);
  384.         if (ideptId == 0)
  385.             irowsInserted = 0;
  386.         dept = "";
  387.         imgrId = idLookup(lList[1].getSelectedItem(), dept);
  388.         if (imgrId == 0)
  389.             irowsInserted = 0;
  390.         sinsertStatement = "Insert into employee " +
  391.                               "(emp_id, emp_lname, emp_fname, street, city, state, zip_code, phone, " +
  392.                                "ss_number, status, sex, salary, bene_health_ins, bene_life_ins, bene_day_care, " +
  393.                                "start_date, termination_date, dept_id, manager_id) " +
  394.                                "values " +
  395.                                "('" + tfList[0].getText() + "', " +
  396.                                "'" + tfList[1].getText() + "', " +
  397.                                "'" + tfList[2].getText() + "', " +
  398.                                "'" + tfList[3].getText() + "', " +
  399.                                "'" + tfList[4].getText() + "', " +
  400.                                "'" + tfList[5].getText() + "', " +
  401.                                "'" + tfList[6].getText() + "', " +
  402.                                "'" + tfList[7].getText() + "', " +
  403.                                "'" + tfList[8].getText() + "', " +
  404.                                "'" + tfList[9].getText() + "', " +
  405.                                "'" + tfList[10].getText() + "', " +
  406.                                "'" + tfList[11].getText() + "', " +
  407.                                "'" + chealth + "', " +
  408.                                "'" + clife + "', " +
  409.                                "'" + cday_care + "', " +
  410.                                "'" + tfList[12].getText() + "', " +
  411.                                stermDate + ", '" + ideptId + "', '" + imgrId + "')";
  412.         try {
  413.             stinsert = conserverConnection.createStatement();
  414.             irowsInserted = stinsert.executeUpdate(sinsertStatement);
  415.         }
  416.         catch (SQLException e) {
  417.             System.out.println(e.getMessage());
  418.             return 0;
  419.         }
  420.         imaxLoops = bList.length;
  421.         iloopCnt = 0;
  422.         for(iloopCnt = 0; iloopCnt < imaxLoops; iloopCnt++) {
  423.             if (iloopCnt != 5) //The new_insert button...
  424.                 bList[iloopCnt].enable();
  425.             else
  426.                 bList[iloopCnt].setLabel("New");
  427.         }
  428.         girecordCount++;
  429.         gicurrentRecord = girecordCount;
  430.         try {
  431.             stinsert.close();
  432.         }
  433.         catch (SQLException e) {
  434.             System.out.println(e.getMessage());
  435.             irowsInserted = 0;
  436.         }
  437.         return irowsInserted;
  438.     }
  439.  
  440.     public boolean nextRecord(ResultSet rsTemp) {
  441.         try {
  442.             rsTemp.next();
  443.         }
  444.         catch (SQLException e) {
  445.             System.out.println(e.getMessage());
  446.             return false;
  447.         }
  448.         return true;
  449.     }
  450.  
  451.     public void populateMgrList(int deptId, List mgrNames) {
  452.         ResultSet rsmgr = null;
  453.         try {
  454.             gpsmgr.setInt(1, deptId);
  455.             rsmgr = gpsmgr.executeQuery();
  456.         }
  457.         catch (SQLException e) {
  458.             System.out.println(e.getMessage());
  459.         }
  460.         if (rsmgr.equals(null)) {
  461.             System.out.println("Manager lookup failed!");
  462.         }
  463.         else {
  464.             String smgrFromTable = new String();
  465.             String smgrName = new String();
  466.             try {
  467.                 smgrName = grsemployee.getString(21) + " " + grsemployee.getString(20);
  468.             }
  469.             catch (SQLException e) {
  470.                 System.out.println(e.getMessage());
  471.             }
  472.             int iindex = 0;
  473.             try {
  474.                 while (nextRecord(rsmgr)) {
  475.                     smgrFromTable = rsmgr.getString("emp_fname") + " " + rsmgr.getString("emp_lname");
  476.                     mgrNames.addItem(smgrFromTable);
  477.                     if (smgrFromTable.equals(smgrName))
  478.                         mgrNames.select(iindex);
  479.                     iindex++;
  480.                 }
  481.             }
  482.             catch (SQLException e) {
  483.             }
  484.         }
  485.     }
  486.  
  487.     public boolean populateScreen(TextField[] tfList, Checkbox[] cbList, List[] lList, TextField ltfStatusBar) {
  488.         int imaxLoops = 0, iloopCnt = 0, iempId = 0, ilistLoop = 0;
  489.         String stestVar, sstatusBarMessage;
  490.         Character vchar = new Character('Y');
  491.         Date dtTemp;
  492.         Number nnumTemp;
  493.         try {
  494.             iempId = grsemployee.getInt("emp_id");
  495.             tfList[0].setText(Integer.toString(iempId));
  496.             imaxLoops = 11;
  497.             for (iloopCnt = 1; iloopCnt < imaxLoops; iloopCnt++)
  498.                 tfList[iloopCnt].setText(grsemployee.getString(iloopCnt+1));
  499.             int icolumnOffset = 13;
  500.             imaxLoops = cbList.length;
  501.             for (iloopCnt = 0; iloopCnt < imaxLoops; iloopCnt++) {
  502.                 stestVar = grsemployee.getString(iloopCnt+icolumnOffset);
  503.                 if (stestVar.equals(vchar.toString()))
  504.                     cbList[iloopCnt].setState(true);
  505.                 else
  506.                     cbList[iloopCnt].setState(false);
  507.             }
  508.             nnumTemp = grsemployee.getBigDecimal("salary", 2);
  509.             tfList[11].setText("$" + nnumTemp.toString());
  510.             dtTemp = grsemployee.getDate("start_date");
  511.             tfList[12].setText(dtTemp.toString());
  512.             dtTemp = grsemployee.getDate("termination_date");
  513.             if (!grsemployee.wasNull())
  514.                 tfList[13].setText(dtTemp.toString());
  515.             else
  516.                 tfList[13].setText("None");
  517.  
  518. //          Populate department name list
  519.             ResultSet rsdept = null;
  520.             try {
  521.                 rsdept = gpsdept.executeQuery();
  522.             }
  523.             catch (SQLException e) {
  524.                 System.out.println(e.getMessage());
  525.                 return false;
  526.             }
  527.             String sdeptFromTable = new String();
  528.             String sdeptName = new String(grsemployee.getString("dept_name"));
  529.             if (rsdept.equals(null)) {
  530.                 System.out.println("Department lookup failed!");
  531.                 return false;
  532.             }
  533.             else {
  534.                 lList[0].clear();
  535.                 lList[1].clear();
  536.                 int iindex = 0;
  537.                 try {
  538.                     while (nextRecord(rsdept)) {
  539.                         sdeptFromTable = rsdept.getString("dept_name");
  540.                         lList[0].addItem(sdeptFromTable);
  541.                         if (sdeptFromTable.equals(sdeptName))
  542.                             lList[0].select(iindex);
  543.                         iindex++;
  544.                     }
  545.                 }
  546.                 catch (SQLException e) {
  547.                 }
  548.                 try {
  549.                     populateMgrList(grsemployee.getInt("dept_id"), lList[1]);
  550.                 }
  551.                 catch (SQLException e) {
  552.                     System.out.println(e.getMessage());
  553.                 }
  554.             }
  555. //          End
  556.  
  557.         }
  558.         catch (SQLException e) {
  559.             System.out.println(e.getMessage());
  560.             return false;
  561.         }
  562.         sstatusBarMessage = "Record " + gicurrentRecord + " of " + girecordCount;
  563.         ltfStatusBar.setText(sstatusBarMessage);
  564.         return true;
  565.     }
  566.  
  567.     private ResultSet sendSQLStatement(String sqlStatement, Statement stgeneric) {
  568.         ResultSet rsgeneric;
  569.         try {
  570.             rsgeneric = stgeneric.executeQuery(sqlStatement);
  571.         }
  572.         catch (SQLException e) {
  573.             System.out.println(e.getMessage());
  574.             return null;
  575.         }
  576.         return rsgeneric;
  577.     }
  578.  
  579.     public int updateRecord(TextField[] tfList, Checkbox[] cbList, List[] lList) {
  580.         Statement stupdate = null;
  581.         ResultSet rsupdate = null;
  582.         char chealth, clife, cday_care;
  583.         int irowsUpdated = 0, ideptId = 0, imgrId = 0;
  584.         Integer iempId;
  585.         String supdateStatement = new String();
  586.         String stermDate = new String();
  587.         String supdate = new String();
  588.         String ssalary = new String();
  589.         String dept = new String("dept");
  590.         if (!cbList[0].getState())
  591.             chealth = 'N';
  592.         else
  593.             chealth = 'Y';
  594.         if (!cbList[1].getState())
  595.             clife = 'N';
  596.         else
  597.             clife = 'Y';
  598.         if (!cbList[2].getState())
  599.             cday_care = 'N';
  600.         else
  601.             cday_care = 'Y';
  602.         iempId = Integer.valueOf(tfList[0].getText());
  603.         if (tfList[13].getText().equals("None"))
  604.             stermDate = null;
  605.         else
  606.             stermDate = "'" + tfList[13].getText() + "'";
  607.         if (tfList[11].getText().substring(0,1).equals("$"))
  608.             ssalary = tfList[11].getText().substring(1, tfList[11].getText().length());
  609.         else
  610.             ssalary = tfList[11].getText().substring(0, tfList[11].getText().length());
  611.         ideptId = idLookup(lList[0].getSelectedItem(), dept);
  612.         if (ideptId == 0)
  613.             irowsUpdated = 0;
  614.         dept = "";
  615.         imgrId = idLookup(lList[1].getSelectedItem(), dept);
  616.         if (imgrId == 0)
  617.             irowsUpdated = 0;
  618.         try {
  619.             stupdate = conserverConnection.createStatement();
  620.         }
  621.         catch (SQLException e) {
  622.             System.out.println(e.getMessage());
  623.             return 0;
  624.         }
  625.         supdate = new String("Select dept_id from department where dept_name = '" + lList[0].getSelectedItem() + "'");
  626.         rsupdate = sendSQLStatement(supdate, stupdate);
  627.         if (rsupdate.equals(null)) {
  628.             System.out.println("Department lookup failed!");
  629.             return 0;
  630.         }
  631.         else {
  632.             if (!nextRecord(rsupdate)){
  633.                 System.out.println("Failure selecting department record!");
  634.                 return 0;
  635.             }
  636.         }
  637.         supdateStatement = "Update employee " +
  638.                               "set emp_lname = '" + tfList[1].getText() + "', " +
  639.                                     "emp_fname = '" + tfList[2].getText() + "', " +
  640.                                     "street = '" + tfList[3].getText() + "', " +
  641.                                     "city = '" + tfList[4].getText() + "', " +
  642.                                     "state = '" + tfList[5].getText() + "', " +
  643.                                     "zip_code = '" + tfList[6].getText() + "', " +
  644.                                     "phone = '" + tfList[7].getText() + "', " +
  645.                                     "ss_number = '" + tfList[8].getText() + "', " +
  646.                                     "status = '" + tfList[9].getText() + "', " +
  647.                                     "sex = '" + tfList[10].getText() + "', " +
  648.                                     "salary = '" + ssalary + "', " +
  649.                                     "start_date = '" + tfList[12].getText() + "', " +
  650.                                     "termination_Date = " + stermDate + ", " +
  651.                                     "bene_health_ins = '" + chealth + "', " +
  652.                                     "bene_life_ins = '" + clife + "', " +
  653.                                     "bene_day_care = '" + cday_care + "', " +
  654.                                     "dept_id = '" + ideptId + "', " +
  655.                                     "manager_id = '" + imgrId + "' " +
  656.                                     "where emp_id = " + iempId.intValue();
  657.         supdate = "Select emp_id, emp_lname, emp_fname, dept_id, street, city, state, zip_code, phone, " +
  658.                          "ss_number, status, sex, salary, bene_health_ins, bene_life_ins, bene_day_care, " +
  659.                          "start_date, termination_date from employee " +
  660.                          "where emp_id = " + iempId.intValue();
  661.         rsupdate = sendSQLStatement(supdate, stupdate);
  662.         if (rsupdate.equals(null)) {
  663.             System.out.println("Update select statement failed!");
  664.             return 0;
  665.         }
  666.         else {
  667.             if (!nextRecord(rsupdate)){
  668.                 System.out.println("Failure selecting record to update!");
  669.                 return 0;
  670.             }
  671.             try {
  672.                 irowsUpdated = stupdate.executeUpdate(supdateStatement);
  673.                 stupdate.close();
  674.             }
  675.             catch (SQLException e) {
  676.                 System.out.println(e.getMessage());
  677.                 return 0;
  678.             }
  679.         return irowsUpdated;
  680.         }
  681.     }
  682. }
  683.