TableEditor.java

Go to the documentation of this file.

import java.util.Vector;
import java.sql.SQLException;
import java.sql.PreparedStatement;

public class TableEditor extends Widget
{ 
        public static class Table
        {
          public String name;
          public int autoColumn;
          public String[] columns;
          public String select, update, delete, insert;
          public Table(String name, int autoColumn, String[] columns)
          {
            this.name = name;
            this.autoColumn = autoColumn;
            this.columns = columns;
            
            delete = "DELETE FROM " + name + " WHERE oid = ?;\n";
            
            StringBuffer select = new StringBuffer("SELECT oid");
            StringBuffer insert = new StringBuffer("INSERT INTO " + name + " (");
            StringBuffer insertLast = new StringBuffer("VALUES (");
            StringBuffer update = new StringBuffer("UPDATE " + name + " SET ");
            
            boolean first = true, firstna = true;
            for (int i = 0; i < columns.length; ++i)
            {
              if (first) first = false; else update.append(", ");
              update.append(columns[i]);
              update.append(" = ?");

              select.append(", ");
              select.append(columns[i]);
              
              if (autoColumn != i)
              {
                if (firstna)
                  firstna = false;
                else
                {
                  insert.append(", ");
                  insertLast.append(", ");
                }
                insert.append(columns[i]);
                insertLast.append("?");
              }
            }
            insert.append(") ");
            insert.append(insertLast);
            insert.append(")");
            select.append(" FROM " + name);
            update.append(" WHERE oid = ?");
            
            this.update = update.toString();
            this.select = select.toString();
            this.insert = insert.toString();
          }
        }
        
        static Table[] tables = new Table[10];
        static
        {
    String [] a = {"service_id", "appointment_id"};
          tables[0] = new Table("appointment_services", -1, a);
          
          String [] b = {"appointment_id", "begintime", "endtime", "doctor_id", "patient_id"};
          tables[1] = new Table("appointments", 0, b);

          String [] c = {"diagnosis_id", "appointment_id", "name", "description"};
          tables[2] = new Table("diagnoses", 0, c);

          String [] d = {"doctor_id", "practice_id", "name"};
          tables[3] = new Table("doctors", 0, d);

          String [] e = {"insurance_company_id", "name"};
          tables[4] = new Table("insurance_companies", 0, e);

          String [] f = {"insurance_policy_id", "insurance_company_id", "description"};
          tables[5] = new Table("insurance_policies", 0, f);

          String [] g = {"patient_id", "insurance_policy_id", "name", "address", "phone"};
          tables[6] = new Table("patients", 0, g);

          String [] h = {"practice_id", "name", "address", "phone"};
          tables[7] = new Table("practices", 0, h);

          String [] i = {"referral_id", "appointment_id", "doctor_id", "type"};
          tables[8] = new Table("referrals", 0, i);

          String [] j = {"service_id", "name", "description", "price"};
          tables[9] = new Table("services", 0, j);      
        }
        
        public static final int CHOOSE = 1;
        public static final int EDIT = 2;
        public static final int DELETE = 3;
        public static final int SAVE = 4;
        
        public int tableIdx = 0;
        public int oid = 0;
        public ActionButton action;
        public TextBox[] fields;
        public String message = "";
        public TableEditor(String prefix, Form form)
        {
          super(prefix, form);
          action = new ActionButton(n("action"), form);
          addChild(action);
        }
        
        public void loadValues()
        {
          super.loadValues();
          tableIdx = toInt(loadAttribute("tableIdx"),0);
          int o = toInt(loadAttribute("oid"), 0);

    if (action.action == EDIT)
      oid = action.param;
    else
      oid = o;

    if (oid != 0)
    {
      fields = new TextBox[tables[tableIdx].columns.length];
      int cols = 100 / fields.length;
      if (cols > 20) cols = 20;
      for(int i = 0; i < fields.length; ++i)
      {
        TextBox t = new TextBox(0, cols, n("field" + i), form);
        if (oid == o) t.loadValues();
        fields[i] = t;
        addChild(t);
      }
    }
    
    switch (action.action)
    {
      case SAVE:
        if (saveRow()) oid = 0;
        break;
      case DELETE:
        deleteRow(action.param);
        break;
    }
        }
        
        public boolean saveRow()
        {
          Table t = tables[tableIdx];
          form.connectDb();
          try
          {
            PreparedStatement s = form.conn.prepareStatement(oid == -1 ? t.insert : t.update);
            try
            {
              int f = 0;
              for (int i = 0; i < fields.length; ++i)
              {
                if (oid != -1 || i != t.autoColumn)
                {
                  ++f;
                  s.setString(f, fields[i].text);
                }
              }
              if (oid != -1)
              {
                ++f;
                s.setInt(f, oid);
              }
              
              s.executeUpdate();
            }
            finally
            {
              s.close();
            }
          }
          catch (SQLException e)
          {
            message = "<p><b><font color=red>Unable to save row. Postgres gives this error message:</font></b></p>\n"
              + "<blockquote style=\"background-color: #EEEEEE\">" + e.toString() + "</blockquote>\n";
            
            // XXX: due to what is apparently a JDBC bug, the connection goes bad
            // if the exception is caused by a referrential integrity violation.
            // when this happens subsequent queries always throw this exception:
      //     
      //   No results were returned by the query.
      //        at org.postgresql.jdbc2.Statement.executeQuery(Statement.java:58)
      //
      // My workaround is just to always close the connection.
            form.disconnectDb();
            return false;
          }
          message = "<p><b><font color=blue>Row saved successfully</font></b></p>\n";
          return true;
        }
        
        public void deleteRow(int row)
        {
          Table t = tables[tableIdx];
          form.connectDb();
          try
          {
            PreparedStatement s = form.conn.prepareStatement(t.delete);
            try
            {
              s.setInt(1,row);
              s.executeUpdate();
            }
            finally
            {
              s.close();
            }
          }
          catch (SQLException e)
          {
            message = "<p><b><font color=red>Unable to delete row. Postgres gives this error message:</font></b></p>\n"
              + "<blockquote style=\"background-color: #EEEEEE\">" + e.toString() + "</blockquote>\n";
            form.disconnectDb(); // XXX: see above
            return;
          }
          message = "<p><b><font color=blue>Row deleted successfully</font></b></p>\n";
        }
        
        public void display(boolean hidden)
        {
          p(message);
          
          super.display(hidden);
          printAttribute("oid", "" + oid);        
          if (hidden || modalChild != null)
          {
            printAttribute("tableIdx", "" + tableIdx);
            return;
          }
          
          p("<select name=" + n("tableIdx") + " rows=1 onchange=\"this.form.submit()\">\n");
          for (int i = 0; i < tables.length; ++i)
          {
            String selected = i == tableIdx ? " selected " : "";
            p("  <option value=" + i + selected + ">" + tables[i].name + "</option>\n");
          }
          p("</select>\n");
          action.display("Go", CHOOSE);
          
          if (oid != -1)
          {
            p("<p align=center>");
            action.display("Insert new row...", EDIT, -1);
            p("</p>");
          }
          
          Table t = tables[tableIdx];
          p("<table border=1>\n<tr>\n");
          for(int i = 0; i < t.columns.length; ++i)
          {
            p("  <td><strong>" + t.columns[i] + "</strong></td>\n");
          }
          p("  <td>&nbsp;</td>\n</tr>\n");
          
          if (oid == -1)
          {
            p("<tr>\n");
            for (int i = 0; i < t.columns.length; ++i)
            {
              p("  <td>");
              if (i == t.autoColumn)
                p("<i>auto</i>");
              else
          fields[i].display();
        p("</td>\n");
            }
            p("  <td>");
            action.display("Save", SAVE);
            p("</td>\n</tr>\n");          
          }
          
          Query q = new Query(form.connectDb(), form.pw);
          try
          {
            q.query(t.select);
            if (q.r != null)
            while(q.r.next())
            {
              int oid = q.r.getInt(1);
              
              if (oid == this.oid)
              {
                p("<tr>\n");
                for (int i = 0; i < t.columns.length; ++i)
                {
                  p("  <td>");
                  if (fields[i].text == null)
                    fields[i].text = q.r.getString(i+2);
                  fields[i].display();
                  p("</td>\n");
                }
                p("  <td>");
                action.display("Save", SAVE);
                p("</td>\n</tr>\n");
              }
              else
              {
                p("<tr>\n");
                for (int i = 0; i < t.columns.length; ++i)
                  p("  <td>" + htmlencode(q.r.getString(i+2)) + "</td>\n");
                p("  <td>");
                action.display("Edit", EDIT, oid);
                action.display("Delete", DELETE, oid);
                p("</td>\n</tr>\n");
              }
            }
          }
          catch (SQLException e)
          {
            p(e);
          }
          finally
          {
            q.close();
          }
          p("</table>");
        }
};

---