jdbccontrolpanel.java

一个用Java实现的小型

    
    optionApply.setText("Refresh");
    optionApply.addActionListener(new java.awt.event.ActionListener() {
      public void actionPerformed(java.awt.event.ActionEvent evt) {
        applyOptions(evt);
      }
    });
    
    optionButtonPanel.add(optionApply);
    
    optionPanel.add(optionButtonPanel, java.awt.BorderLayout.SOUTH);
    
    tabbedPane.addTab("Options", optionPanel);
    
    getContentPane().add(tabbedPane);
    
    pack();
  }//GEN-END:initComponents

  private void applyOptions(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_applyOptions
    projectDatabase = projDBField.getText();
    projectTable = projTableField.getText();
    int projFieldIndex = projFieldComboBox.getSelectedIndex();
    projectField = (String)projFieldComboBox.getItemAt(projFieldIndex);
    String[] fieldNames = getProjectFieldNames();
    DefaultComboBoxModel boxModel;
    if(fieldNames == null) {
      boxModel = new DefaultComboBoxModel(new String[] {"No Fields Found"});
      projFieldComboBox.setEnabled(false);
    } else {
      boxModel = new DefaultComboBoxModel(fieldNames);
      projFieldComboBox.setEnabled(true);
    }
    projFieldComboBox.setModel(boxModel);
    optionInputPanel.repaint();
  }//GEN-LAST:event_applyOptions

  private void toggleValidateProject(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_toggleValidateProject
    toggleValidateProject();
  }//GEN-LAST:event_toggleValidateProject

  private void toggleValidateProject() {
    if(projValidateCheckBox.isSelected()) {
      projDBField.enable();
      projTableField.enable();
    } else {
      projDBField.disable();
      projTableField.disable();
    }
    optionInputPanel.repaint();
  }
  
  private void showDriverPanel(java.awt.event.ComponentEvent evt) {//GEN-FIRST:event_showDriverPanel
    getRootPane().setDefaultButton(driverOK);
  }//GEN-LAST:event_showDriverPanel

  private void showOptionPanel(java.awt.event.ComponentEvent evt) {//GEN-FIRST:event_showOptionPanel
    getRootPane().setDefaultButton(optionOK);
  }//GEN-LAST:event_showOptionPanel

  private void toggleODBC(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_toggleODBC
    toggleODBC();
  }//GEN-LAST:event_toggleODBC
  private void toggleODBC() {
    if(odbcCheckBox.isSelected()) {
      nameField.setText(odbcDriverName);
      nameField.disable();
      urlLabel.setText("Data Source");
      int lastColon = url.lastIndexOf(':')+1;
      urlField.setText(url.substring(lastColon));
    } else {
      nameField.setText(name);
      nameField.enable();
      urlLabel.setText("URL");
      urlField.setText(url);
    }
    driverInputPanel.repaint();
  }
  private void refreshFieldMap(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_refreshFieldMap
      try {
        tableMap.init();
        fieldMapping.setModel(tableMap.toTableModel());
        fieldMapping.repaint();
      } catch (SQLException e) {
        System.err.println("Couldn't initialize table mapping");
      }
  }//GEN-LAST:event_refreshFieldMap
  
  private void showFieldMap(java.awt.event.ComponentEvent evt) {//GEN-FIRST:event_showFieldMap
    getRootPane().setDefaultButton(fieldOK);
    if(tableMap.size() == 0) {
      try {
        tableMap.init();
        fieldMapping.setModel(tableMap.toTableModel());
        fieldMapping.repaint();
      } catch (SQLException e) {
        System.err.println("Couldn't initialize table mapping");
      }
    }
  }//GEN-LAST:event_showFieldMap
  
  private void testDriverSettings(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_testDriverSettings
    name = nameField.getText();
    url = urlField.getText();
    try {
      if(name.equals(odbcDriverName)) url = "jdbc:odbc:"+url;
      database = dbField.getText();
      table = tableField.getText();
      Connection conn = openConnection();
      if(conn != null) {
        DatabaseMetaData dbmeta = conn.getMetaData();
        ResultSet cols = dbmeta.getColumns(null, database, table, null);
        if((cols == null) || ! cols.next())
          JOptionPane.showMessageDialog(this, "Table "+database+"."+table+" cannot be found.", "Table Not Found", JOptionPane.ERROR_MESSAGE);
        else
          JOptionPane.showMessageDialog(this, "Driver connection verified.", "Driver verified", JOptionPane.INFORMATION_MESSAGE);
        if(cols != null) cols.close();
        conn.close();
      }
    } catch (SQLException e) {
      System.err.println("Uncaught SQL error during test: "+e);
    }
  }//GEN-LAST:event_testDriverSettings
  
  private void cancel(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_cancel
    exitForm();
  }//GEN-LAST:event_cancel
  
  private void saveDriverSettings(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_saveDriverSettings
    name = nameField.getText();
    url = urlField.getText();
    if(name.equals(odbcDriverName)) url = "jdbc:odbc:"+url;
    database = dbField.getText();
    table = tableField.getText();
    projectDatabase = projDBField.getText();
    projectTable = projTableField.getText();
    if(projFieldComboBox.getItemCount() != 0) {
      int projFieldIndex = projFieldComboBox.getSelectedIndex();
      projectField = (String)projFieldComboBox.getItemAt(projFieldIndex);
    }
    projectValidate = projValidateCheckBox.isSelected();
    hourFormat = hourComboBox.getSelectedIndex();
    dateFormat = dateComboBox.getSelectedIndex();
    projectCase = projectCaseCheckBox.isSelected();
    for(int i=0; i < tableMap.size(); i++) {
      String value = (String)fieldMapping.getValueAt(i, 2);
      FieldMap record = (FieldMap)tableMap.elementAt(i);
      record.valueExpression = value;
    }
    savePrefs();
    exitForm();
  }//GEN-LAST:event_saveDriverSettings
      private void exitForm(java.awt.event.WindowEvent evt) {//GEN-FIRST:event_exitForm
        exitForm();
    }//GEN-LAST:event_exitForm
    
    private void exitForm() {
      setVisible(false);
    }
    
    private void savePrefs() {
      File prefs = new File(CsltComm.prefsDir, "JDBCConnection.def");
      try {
        DocumentBuilderFactory docBuilderFactory = DocumentBuilderFactory.newInstance();
        DocumentBuilder docBuilder = docBuilderFactory.newDocumentBuilder();
        Document doc = docBuilder.newDocument();
        Element rootNode = doc.createElement("jdbccontrolpanel");
        rootNode.setAttribute("version", "0.1");
        doc.appendChild(rootNode);
        
        Element newNode = doc.createElement("driver");
        newNode.setAttribute("name", name);
        newNode.setAttribute("url", url);
        newNode.setAttribute("username", userName);
        newNode.setAttribute("database", database);
        newNode.setAttribute("table", table);
        rootNode.appendChild(newNode);
        
        newNode = doc.createElement("options");
        newNode.setAttribute("hourFormat", ""+hourFormat);
        newNode.setAttribute("dateFormat", ""+dateFormat);
        newNode.setAttribute("projectCase", ""+projectCase);
        newNode.setAttribute("projectValidate", ""+projectValidate);
        newNode.setAttribute("projectDatabase", projectDatabase);
        newNode.setAttribute("projectTable", projectTable);
        newNode.setAttribute("projectField", projectField);
        rootNode.appendChild(newNode);
        
        //Save field mappings
        for(int i=0; i<tableMap.size(); i++){
          FieldMap record = tableMap.elementAt(i);
          newNode = doc.createElement("fieldmap");
          newNode.setAttribute("name", record.dbFieldName);
          newNode.setAttribute("type", ""+record.sqlType);
          newNode.setAttribute("index", ""+record.dbFieldIndex);
          newNode.setAttribute("value", record.valueExpression);
          rootNode.appendChild(newNode);
        }
        
        doc.getDocumentElement().normalize();
        TransformerFactory fac = TransformerFactory.newInstance();
        Transformer trans = fac.newTransformer();
        trans.transform(new DOMSource(doc.getDocumentElement()), new StreamResult(prefs));
      } catch (ParserConfigurationException e) {
        System.err.println("Error writing prefs file: "+e);
        e.printStackTrace(System.out);
      } catch (Exception e) {
        System.err.println("Cannot write prefs file: "+e);
        e.printStackTrace(System.out);
      }
    }
    
    private void readPrefs() {
      File prefs = new File(CsltComm.prefsDir, "JDBCConnection.def");
      if (prefs.exists()) {
        try {
          DocumentBuilderFactory docBuilderFactory = DocumentBuilderFactory.newInstance();
          DocumentBuilder docBuilder = docBuilderFactory.newDocumentBuilder();
          Document doc = docBuilder.parse(prefs);
          doc.getDocumentElement().normalize();
          
          NamedNodeMap attributes = null;
          
          NodeList drivers = doc.getElementsByTagName("driver");
          Node driver = drivers.item(0);
          attributes = driver.getAttributes();
          name = attributes.getNamedItem("name").getNodeValue();
          url = attributes.getNamedItem("url").getNodeValue();
          if(attributes.getNamedItem("username") != null)
            userName = attributes.getNamedItem("username").getNodeValue();
          if(attributes.getNamedItem("database") != null)
            database = attributes.getNamedItem("database").getNodeValue();
          if(attributes.getNamedItem("table") != null)
            table = attributes.getNamedItem("table").getNodeValue();

          NodeList options = doc.getElementsByTagName("options");
          Node option = options.item(0);
          attributes = option.getAttributes();
          hourFormat = Integer.parseInt(attributes.getNamedItem("hourFormat").getNodeValue());
          dateFormat = Integer.parseInt(attributes.getNamedItem("dateFormat").getNodeValue());
          if(attributes.getNamedItem("projectCase") != null)
            projectCase = Boolean.valueOf(attributes.getNamedItem("projectCase").getNodeValue()).booleanValue();
          if(attributes.getNamedItem("projectValidate") != null)
            projectValidate = Boolean.valueOf(attributes.getNamedItem("projectValidate").getNodeValue()).booleanValue();
          if(attributes.getNamedItem("projectDatabase") != null)
            projectDatabase = attributes.getNamedItem("projectDatabase").getNodeValue();
          if(attributes.getNamedItem("projectTable") != null)
            projectTable = attributes.getNamedItem("projectTable").getNodeValue();
          if(attributes.getNamedItem("projectField") != null)
            projectField = attributes.getNamedItem("projectField").getNodeValue();
          else
            projectField = "No Fields Found";

          NodeList fieldMaps = doc.getElementsByTagName("fieldmap");
          tableMap.fieldMaps.clear();
          for(int i=0; i<fieldMaps.getLength(); i++){
            Node fieldMap = fieldMaps.item(i);
            attributes = fieldMap.getAttributes();
            Node nameNode = attributes.getNamedItem("name");
            String fieldName = nameNode.getNodeValue();
            Node typeNode = attributes.getNamedItem("type");
            short sqlType = Short.parseShort(typeNode.getNodeValue());
            Node indexNode = attributes.getNamedItem("index");
            int fieldIndex = Integer.parseInt(indexNode.getNodeValue());
            Node valueNode = attributes.getNamedItem("value");
            String valueExpression = valueNode.getNodeValue();
            FieldMap record = new FieldMap(fieldName, sqlType, fieldIndex, valueExpression);
            tableMap.fieldMaps.addElement(record);
          }
        } catch (SAXParseException e) {
          System.err.println("Error parsing prefs file, line "+e.getLineNumber()+": "+e.getMessage());
        } catch (SAXException e) {
          System.err.println("Error reading prefs file: "+e);
          e.printStackTrace(System.out);
        } catch (Exception e) {
          System.err.println("Cannot read prefs file: "+e);
          e.printStackTrace(System.out);
        }
      }
    }
    
    public Connection openConnection() {
      Connection conn = null;
      errorList = new Vector();
      
      try{
/* This code won't work... though I wish it would. It was supposed to
 * dynamically load a JAR file with a JDBC driver from an arbitrary path.
 * Unfortunately this can't be done because of how the methods
 * getCallerClassLoader() and getCallerClass(ClassLoader, String) in
 * the DriverManager class are written. They expect to be able to do a
 * straight Class.forName(String, true, ClassLoader) which only works if the
 * JAR was present in the classpath when the JVM started. I leave the code
 * here in case this starts working in 1.4. Until then the solution is to
 * use the extension mechanism in JRE 1.2+ and place the necessary JAR files
 * in the JRE's ext/ directory.
        char[] fileSeperator = System.getProperty("file.separator").toCharArray();
        String jarPath = jar.replace(fileSeperator[0], '/');
        URL[] jarURL = {new URL("file://"+jarPath)};
        URLClassLoader jarLoader = new URLClassLoader(jarURL, this.getClass().getClassLoader());
        Driver driver = (Driver)jarLoader.loadClass(name).newInstance();
        DriverManager.registerDriver(driver);
 */
        Class.forName(name);
        if(! validated) { //Send login dialog box
          LoginDialog prompt = new LoginDialog(this, userName);
          prompt.pack();
          prompt.setLocationRelativeTo(this);
          prompt.setVisible(true);
        }
        Properties properties = new Properties();
        properties.put("password", password);
        properties.put("user", userName);
        properties.put("prompt", "false");
        conn = DriverManager.getConnection(url, properties);
        if(conn.isClosed()) {
          errorList.addElement("Cannot open connection");
          conn = null;
        } else if(conn.isReadOnly()) {
          errorList.addElement("Connection is read only");
          conn = null;
        }
        if(conn != null) validated = true;
      } catch (ClassNotFoundException e) {
        String extdir = System.getProperty("java.ext.dirs");
        String msgString = "Could not find JDBC driver "+name+".\n"+
        "Make sure you have the correct driver files and that they\n"+
        "are installed in "+extdir+",\n then restart ConsultComm.";
        errorList.addElement(msgString);
      } catch (SQLException e) {
        errorList.addElement("Could not build JDBC connection: "+e);
      } catch (NullPointerException e) {
        errorList.addElement("One or more arguments are null");
      } catch (Exception e) {
        errorList.addElement(e.toString());
      }
      if(errorList.size() > 0)
        JOptionPane.showMessageDialog(this, errorList.elementAt(0), "Database Connection Error", JOptionPane.ERROR_MESSAGE);
      return conn;
    }
    
    public boolean exportTimeRecordSet(TimeRecordSet times) {
      Connection conn = openConnection();
      PreparedStatement insert = null;
      boolean worked = false;
      
      try {
        if(tableMap.size() == 0) return false;
        String queryString = "?";
        for(int i=1; i<tableMap.size(); i++) queryString += " ,?";
        insert = conn.prepareStatement("INSERT INTO "+database+"."+table+" VALUES ("+queryString+")");
        
        // Okay... we've got a problem. We want to test for errors
        // before committing changes to the database but we also
        // can't depend on the rollback() method working (not all
        // db's we want to use support transaction management).
        // So we first load everything into a two dimensional array
        // then we insert the records into the database.
        Vector statements = new Vector();
        for(int j=0; j < times.size(); j++) {
          TimeRecord record = times.elementAt(j);
          FieldMap hourTest = new FieldMap("TEST", java.sql.Types.DECIMAL, 0, "$HOURS"); //Find out how many hours exist
          java.math.BigDecimal hours = (java.math.BigDecimal)hourTest.getValue(record);
          if((hours.compareTo(new java.math.BigDecimal(0.0)) <= 0) || ! record.billable) continue;
          Object[] statement = new Object[tableMap.size()];
          for(int i=0; i < statement.length; i++) {
            FieldMap fieldMap = tableMap.elementAt(i);
            statement[i] = fieldMap.getValue(record);
          }
          statements.addElement(statement);
        }