jdbccontrolpanel.java

一个用Java实现的小型

        for(int j=0; j < statements.size(); j++) {
          Object[] statement = (Object[])statements.elementAt(j);
          for(int i=0; i < statement.length; i++) {
            insert.setObject(i+1, statement[i]);
          }
          insert.execute();
        }
        
        conn.commit();
        worked = true;
      } catch (ProjectInvalidException e) {
        JOptionPane.showMessageDialog(this, e.getMessage(), "Export Error", JOptionPane.ERROR_MESSAGE);
        worked = false;
      } catch (SQLException e) {
        JOptionPane.showMessageDialog(this, e.getMessage(), "Export Error", JOptionPane.ERROR_MESSAGE);
        System.err.println("Could not export timelist to database: "+e);
        worked = false;
      } finally {
        try {
          if(insert != null) insert.close();
          if(conn != null) conn.close();
        } catch (Exception ex) {}
      }
      return worked;
    }
    
    private String[] getProjectFieldNames() {
      Connection conn = openConnection();
      String[] names = null;
      ResultSet cols = null;
      
      try {
        Vector records = new Vector();
        DatabaseMetaData dbmeta = conn.getMetaData();
        cols = dbmeta.getColumns(null, projectDatabase, projectTable, null);
        while(cols.next())
          records.addElement(cols.getString(4));
        
        names = new String[records.size()];
        for(int i=0; i<names.length; i++)
          names[i] = (String)records.elementAt(i);
      } catch (SQLException e) {
        JOptionPane.showMessageDialog(this, e.getMessage(), "Project Error", JOptionPane.ERROR_MESSAGE);
        System.err.println("Could not get project list from database: "+e);
      } finally {
        try {
          if(cols != null) cols.close();
          if(conn != null) conn.close();
        } catch (Exception ex) {}
      }
      return names;
    }
    
    private boolean validateProject(String project) {
      Connection conn = openConnection();
      Statement stmt = null;
      ResultSet rs = null;
      boolean isValid = false;
      try {
        String queryString = "SELECT "+projectField+" FROM "+projectDatabase+"."+projectTable+
        " WHERE "+projectField+"='"+project+"'";
        stmt = conn.createStatement();
        rs = stmt.executeQuery(queryString);
        isValid = rs.next();
        rs.close();
        stmt.close();
        conn.close();
      } catch (SQLException e) {
        System.err.println("Couldn't attempt project validation: "+e);
      } finally {
        try {
          if(rs != null) rs.close();
          if(stmt != null) stmt.close();
          if(conn != null) conn.close();
        } catch (Exception ex) {}
      }
      return isValid;
    }
    
    public static String typeString(int sqlType) {
      switch(sqlType) {
        case java.sql.Types.ARRAY:
          return "ARRAY";
        case java.sql.Types.BIGINT:
          return "BIGINT";
        case java.sql.Types.BINARY:
          return "BINARY";
        case java.sql.Types.BIT:
          return "BIT";
        case java.sql.Types.BLOB:
          return "BLOB";
        case java.sql.Types.CHAR:
          return "CHAR";
        case java.sql.Types.CLOB:
          return "CLOB";
        case java.sql.Types.DATE:
          return "DATE";
        case java.sql.Types.DECIMAL:
          return "DECIMAL";
        case java.sql.Types.DOUBLE:
          return "DOUBLE";
        case java.sql.Types.FLOAT:
          return "FLOAT";
        case java.sql.Types.INTEGER:
          return "INTEGER";
        case java.sql.Types.LONGVARBINARY:
          return "LONGVARBINARY";
        case java.sql.Types.LONGVARCHAR:
          return "LONGVARCHAR";
        case java.sql.Types.NULL:
          return "NULL";
        case java.sql.Types.NUMERIC:
          return "NUMERIC";
        case java.sql.Types.SMALLINT:
          return "SMALLINT";
        case java.sql.Types.TIME:
          return "TIME";
        case java.sql.Types.TIMESTAMP:
          return "TIMESTAMP";
        case java.sql.Types.TINYINT:
          return "TINYINT";
        case java.sql.Types.VARBINARY:
          return "VARBINARY";
        case java.sql.Types.VARCHAR:
          return "VARCHAR";
        default:
          return "OTHER";
      }
    }
    // Variables declaration - do not modify//GEN-BEGIN:variables
    private javax.swing.JTabbedPane tabbedPane;
    private javax.swing.JPanel driverPanel;
    private javax.swing.JPanel driverInputPanel;
    private javax.swing.JLabel nameLabel;
    private javax.swing.JTextField nameField;
    private javax.swing.JLabel urlLabel;
    private javax.swing.JTextField urlField;
    private javax.swing.JLabel dbLabel;
    private javax.swing.JTextField dbField;
    private javax.swing.JLabel tableLabel;
    private javax.swing.JTextField tableField;
    private javax.swing.JCheckBox odbcCheckBox;
    private javax.swing.JPanel driverButtonPanel;
    private javax.swing.JButton driverOK;
    private javax.swing.JButton driverCancel;
    private javax.swing.JButton driverTest;
    private javax.swing.JPanel fieldPanel;
    private javax.swing.JScrollPane fieldScrollPane;
    private javax.swing.JTable fieldMapping;
    private javax.swing.JPanel fieldButtonPanel;
    private javax.swing.JButton fieldOK;
    private javax.swing.JButton fieldCancel;
    private javax.swing.JButton fieldRefresh;
    private javax.swing.JPanel optionPanel;
    private javax.swing.JPanel optionInputPanel;
    private javax.swing.JLabel hourLabel;
    private javax.swing.JComboBox hourComboBox;
    private javax.swing.JLabel dateLabel;
    private javax.swing.JComboBox dateComboBox;
    private javax.swing.JCheckBox projectCaseCheckBox;
    private javax.swing.JCheckBox projValidateCheckBox;
    private javax.swing.JLabel projDBLabel;
    private javax.swing.JTextField projDBField;
    private javax.swing.JLabel projTableLabel;
    private javax.swing.JTextField projTableField;
    private javax.swing.JLabel projFieldLabel;
    private javax.swing.JComboBox projFieldComboBox;
    private javax.swing.JPanel optionButtonPanel;
    private javax.swing.JButton optionOK;
    private javax.swing.JButton optionCancel;
    private javax.swing.JButton optionApply;
    // End of variables declaration//GEN-END:variables
    
    private class LoginDialog extends JDialog {
      private JOptionPane optionPane;
      
      LoginDialog(JFrame frame, String user) {
        super(frame, true);
        setTitle("Login to Server");
        
        final JTextField userField = new JTextField(user);
        userField.setColumns(10);
        final JPasswordField passField = new JPasswordField();
        passField.setColumns(10);
        JPanel loginPanel = new JPanel();
        loginPanel.setLayout(new java.awt.GridLayout(2, 2));
        loginPanel.add(new JLabel("Username: "));
        loginPanel.add(userField);
        loginPanel.add(new JLabel("Password: "));
        loginPanel.add(passField);
        
        optionPane = new JOptionPane(loginPanel, JOptionPane.PLAIN_MESSAGE, JOptionPane.OK_CANCEL_OPTION);
        setContentPane(optionPane);
        setDefaultCloseOperation(DO_NOTHING_ON_CLOSE);
        addWindowListener(new java.awt.event.WindowAdapter() {
          public void windowClosing(java.awt.event.WindowEvent we) {
            optionPane.setValue(new Integer(JOptionPane.CLOSED_OPTION));
          }
        });
        
        optionPane.addPropertyChangeListener(new java.beans.PropertyChangeListener() {
          public void propertyChange(java.beans.PropertyChangeEvent e) {
            String prop = e.getPropertyName();
            
            if (isVisible() && (e.getSource() == optionPane) && (prop.equals(JOptionPane.VALUE_PROPERTY) || prop.equals(JOptionPane.INPUT_VALUE_PROPERTY))) {
              String value = optionPane.getValue().toString();
              if (value == JOptionPane.UNINITIALIZED_VALUE) return;
              optionPane.setValue(JOptionPane.UNINITIALIZED_VALUE);
              
              if (value.equals("0")) {
                password = new String(passField.getPassword());
                userName = userField.getText();
                setVisible(false);
              } else { // user closed dialog or clicked cancel
                setVisible(false);
              }
            }
          }
        });
      }
    }
    
    private class TableMap {
      Vector fieldMaps;
      private final String[] titles = {"Field Name", "Type", "Value"};
      
      TableMap() {
        fieldMaps = new Vector();
      }
      
      protected void init() throws java.sql.SQLException {
        fieldMaps.clear();
        Connection conn = openConnection();
        DatabaseMetaData dbmeta = conn.getMetaData();
        ResultSet cols = dbmeta.getColumns(null, database, table, null);
        while(cols.next())
          fieldMaps.addElement(new FieldMap(cols));
        cols.close();
        conn.close();
      }

      public int size() {
        return fieldMaps.size();
      }
      
      public FieldMap elementAt(int i) {
        return (FieldMap)fieldMaps.elementAt(i);
      }

      public DefaultTableModel toTableModel(){
        DefaultTableModel model = new javax.swing.table.DefaultTableModel(
        //Set to three empty columns
        new Object [][][] {
        },
        titles
        ) {
          public boolean isCellEditable(int rowIndex, int columnIndex) {
            boolean[] editable = {false, false, true};
            return editable[columnIndex];
          }
        };
        
        Enumeration records = fieldMaps.elements();
        while (records.hasMoreElements()) {
          FieldMap record = (FieldMap)records.nextElement();
          model.addRow(new Object[] {record.dbFieldName, typeString(record.sqlType), record.valueExpression});
        }
        return model;
      }
    }
    
    private class FieldMap {
      int sqlType;
      int dbFieldIndex;
      String dbFieldName;
      String valueExpression = "";
      
      FieldMap(ResultSet rs) throws java.sql.SQLException {
        dbFieldName = rs.getString(4);
        sqlType = rs.getShort(5);
        dbFieldIndex = rs.getInt(17);
      }
      
      FieldMap(String name, int type, int index, String value) {
        dbFieldName = name;
        sqlType = type;
        dbFieldIndex = index;
        valueExpression = value;
      }
      
      public String toString() {
        return dbFieldName+"("+dbFieldIndex+"): "+valueExpression+" type "+typeString(sqlType);
      }
      
      protected Object getValue(TimeRecord record) throws ClassCastException, ProjectInvalidException {
        StringTokenizer toker = new StringTokenizer(valueExpression, "$ ", true);
        Object realValue = null;
        while(toker.hasMoreTokens()) {
          String value = toker.nextToken();
          if(value.equals("$")) {
            value = toker.nextToken();
            if(value.equals("PROJECT")) {
              if(sqlType != java.sql.Types.CHAR) throw new ClassCastException("Must be CHAR SQL type for project name");
              else {
                if(projectCase) realValue = record.projectName.toUpperCase();
                else realValue = record.projectName;
                if(projectValidate && ! validateProject((String)realValue))
                  throw new ProjectInvalidException("Project "+realValue+" not in table "+projectDatabase+"."+projectTable);
              }
            } else if(value.equals("USERNAME")) {
              if(sqlType != java.sql.Types.CHAR) throw new ClassCastException("Must be CHAR SQL type for username");
              else realValue = userName;
            } else if(value.equals("DATE")) {
              switch(dateFormat) {
                case DATE_SQLDATE:
                  if(sqlType != java.sql.Types.DATE) throw new ClassCastException("Must be DATE SQL type for entry date");
                  else realValue = new java.sql.Date(System.currentTimeMillis());
                  break;
                case DATE_SQLTIMESTAMP:
                  if(sqlType != java.sql.Types.TIMESTAMP) throw new ClassCastException("Must be TIMESTAMP SQL type for entry timestamp");
                  else realValue = new java.sql.Timestamp(System.currentTimeMillis());
                  break;
                case DATE_CCYYMMDD:
                  if(sqlType != java.sql.Types.DECIMAL) throw new ClassCastException("Must be DECIMAL type for entry date");
                  Calendar today = Calendar.getInstance();
                  int year = today.get(Calendar.YEAR);
                  int month = today.get(Calendar.MONTH)+1;
                  int date = today.get(Calendar.DATE);
                  double cymd = (year*10000)+(month*100)+date;
                  realValue = new java.math.BigDecimal(cymd);
                  break;
                default:
                  throw new ClassCastException("Unknown conversion for date");
              }
            }else if(value.equals("HOURS")) {
              if(sqlType != java.sql.Types.DECIMAL && sqlType != java.sql.Types.NUMERIC && sqlType != java.sql.Types.INTEGER)
                throw new ClassCastException("Must be DECIMAL SQL type for hours");
              switch(hourFormat) {
                case HOUR_FULL:
                  realValue = record.getHours(60, 2);
                  break;
                case HOUR_QUARTER:
                  realValue = record.getHours(60*15, 2);
                  break;
                case HOUR_TENTH:
                  realValue = record.getHours(60*6, 1);
                  break;
                default:
                  realValue = record.getHours(60, 2);
              }
            } else {
              System.err.println("Unknown expression variable: "+value);
            }
          } else {
            try {
              realValue = new Integer(value);
            } catch (NumberFormatException e) {
              try {
                realValue = new java.math.BigDecimal(value);
              } catch (NumberFormatException e2) {
                realValue = value;
              }
            }
          }
        }
        return realValue;
      }
    }
}