コード例 #1
0
  private void execute(String query) {
    Connection dbConnection = ConnectionManager.getConnection("LoadSQLData execute()");

    if (dbConnection == null) return;

    try {
      SQLQuery sqlQuery = new SQLQuery(query);

      if (sqlQuery.executeSQL(dbConnection) == 1) loadData(sqlQuery, dbConnection);
    } catch (SQLException sqle) {
      ConnectionManager.displaySQLErrors(((SQLException) sqle), "LoadSQLData execute()");
    }
    ConnectionManager.closeConnection(dbConnection, "LoadSQLData execute()");
  }
コード例 #2
0
  private void loadData(SQLQuery sqlQuery, Connection dbConnection) throws SQLException {
    // Class Method Instances
    String chartType;
    FX_Series fx_series;

    Object[] content;
    ProgressBar loadProgressBar;

    Iterator<String> columnNamesIterator;

    String columnName, columnClass, columnType;
    int columnSize;
    int rowsCount;
    int currentRow;
    int fieldPosition;
    boolean validInsert;
    boolean isContentNull;

    Object currentContentData;

    String sqlStatementString;
    Statement sqlStatement;
    ResultSet db_resultSet;

    // Set the type of series to be used.

    chartType = fxcharts.getChartType();

    if (chartType.equals(JavaFX_Charts.LINE_CHART)
        || chartType.equals(JavaFX_Charts.AREA_CHART)
        || chartType.equals(JavaFX_Charts.BUBBLE_CHART)
        || chartType.equals(JavaFX_Charts.SCATTER_CHART)) fx_series = new LineChartSeries("Data 0");
    else if (chartType.equals(JavaFX_Charts.CATEGORY_LINE_CHART)
        || chartType.equals(JavaFX_Charts.BAR_CHART)) fx_series = new BarChartSeries("Data 0");
    else if (chartType.equals(JavaFX_Charts.HORIZONTAL_BAR_CHART))
      fx_series = new HBarChartSeries("Data 0");
    else if (chartType.equals(JavaFX_Charts.PIE_CHART)) fx_series = new PieChartSeries("Data 0");
    else return;

    // Collect the row count of the table and setting
    // up a progress bar for tracking/canceling.

    currentRow = 0;
    fieldPosition = 0;
    validInsert = true;
    isContentNull = false;

    loadProgressBar = new ProgressBar("Load SQL Data");
    rowsCount = sqlQuery.getRowCount(dbConnection, dataSourceType);

    if (showProgressBar) {
      loadProgressBar.setTaskLength(rowsCount);
      loadProgressBar.pack();
      loadProgressBar.center();
    }
    loadProgressBar.setVisible(showProgressBar);

    // Setup the chart axis names and temporary content
    // storage.

    if (sqlQuery.getColumnNames().size() == 1) {
      fxcharts.getChartPane().setChartXAxisLabel("Line Index");
      fxcharts.getChartPane().setChartYAxisLabel(sqlQuery.getColumnNames().get(0));
    } else {
      fxcharts.getChartPane().setChartXAxisLabel(sqlQuery.getColumnNames().get(0));
      fxcharts.getChartPane().setChartYAxisLabel(sqlQuery.getColumnNames().get(1));
    }

    if (sqlQuery.getColumnNames().size() <= 2) content = new Object[2];
    else content = new Object[3];

    // Ok now ready so beginning by creating a SQL Statement
    // for data and proceeding with building the data to load.

    sqlStatement = null;
    db_resultSet = null;

    try {
      sqlStatement = dbConnection.createStatement();

      // Setting up to begin collecting information
      // on the given SQL statement.

      sqlStatementString = sqlQuery.getSQLQuery();

      // System.out.println(sqlStatementString);
      db_resultSet = sqlStatement.executeQuery(sqlStatementString);

      // Begin loading the data into the plot series.

      while (db_resultSet.next() && !loadProgressBar.isCanceled() && validInsert) {
        loadProgressBar.setCurrentValue(currentRow++);
        columnNamesIterator = sqlQuery.getColumnNames().iterator();

        while (columnNamesIterator.hasNext() && fieldPosition < content.length) {
          columnName = columnNamesIterator.next();
          columnClass = sqlQuery.getColumnClassHashMap().get(columnName);
          columnType = sqlQuery.getColumnTypeNameHashMap().get(columnName);
          columnSize = sqlQuery.getColumnSizeHashMap().get(columnName).intValue();

          // System.out.println(columnName + " " + columnClass + " " + columnType
          //                    + " " + columnSize + " ");

          // DATE Type Field
          if (columnType.equals("DATE")) {
            currentContentData = db_resultSet.getDate(columnName);

            if (currentContentData == null) {
              if (ASSIGN_NULL_ZERO)
                setNullData(sqlQuery.getColumnNames().size(), content, fieldPosition, currentRow);
              else isContentNull = true;
            } else {
              String dateString;

              if ((fieldPosition == 0
                      && (chartType.equals(JavaFX_Charts.BAR_CHART)
                          || chartType.equals(JavaFX_Charts.CATEGORY_LINE_CHART)
                          || chartType.equals(JavaFX_Charts.PIE_CHART)))
                  || (fieldPosition == 1 && chartType.equals(JavaFX_Charts.HORIZONTAL_BAR_CHART))
                  || (fieldPosition == 0
                      && sqlQuery.getColumnNames().size() == 1
                      && chartType.equals(JavaFX_Charts.HORIZONTAL_BAR_CHART)))
                dateString =
                    Utils.convertDBDateString_To_ViewDateString(
                        currentContentData + "",
                        DBTablesPanel.getGeneralDBProperties().getViewDateFormat());
              else {
                dateString = (currentContentData + "").replaceAll("-", "");
                dateString = dateString.substring(0, 4) + "." + dateString.substring(4);
              }

              if (sqlQuery.getColumnNames().size() == 1) {
                content[fieldPosition] = currentRow + "";
                content[1] = dateString;
              } else content[fieldPosition] = dateString;
            }
          }

          // TIME Type Fields
          else if (columnType.equals("TIME")
              || columnType.equals("TIMETZ")
              || columnType.equals("TIME WITH TIME ZONE")) {
            currentContentData = db_resultSet.getTime(columnName);

            if (currentContentData == null) {
              if (ASSIGN_NULL_ZERO)
                setNullData(sqlQuery.getColumnNames().size(), content, fieldPosition, currentRow);
              else isContentNull = true;
            } else {
              String timeString;

              if ((fieldPosition == 0
                      && (chartType.equals(JavaFX_Charts.BAR_CHART)
                          || chartType.equals(JavaFX_Charts.CATEGORY_LINE_CHART)
                          || chartType.equals(JavaFX_Charts.PIE_CHART)))
                  || (fieldPosition == 1 && chartType.equals(JavaFX_Charts.HORIZONTAL_BAR_CHART))
                  || (fieldPosition == 0
                      && sqlQuery.getColumnNames().size() == 1
                      && chartType.equals(JavaFX_Charts.HORIZONTAL_BAR_CHART)))
                timeString = currentContentData + "";
              else timeString = (currentContentData + "").replaceAll(":", "");

              if (sqlQuery.getColumnNames().size() == 1) {
                content[fieldPosition] = currentRow + "";
                content[1] = timeString;
              } else content[fieldPosition] = timeString;
            }
          }

          // DATETIME & TIMESTAMP Type Fields
          else if (columnType.equals("DATETIME") || columnType.indexOf("TIMESTAMP") != -1) {
            if (columnType.equals("TIMESTAMPLTZ"))
              currentContentData = db_resultSet.getString(columnName);
            else currentContentData = db_resultSet.getTimestamp(columnName);

            if (currentContentData == null) {
              if (ASSIGN_NULL_ZERO)
                setNullData(sqlQuery.getColumnNames().size(), content, fieldPosition, currentRow);
              else isContentNull = true;
            } else {
              String timestampString =
                  new SimpleDateFormat("yyyy-MM-DD HH:mm:ss").format(currentContentData);

              if ((fieldPosition == 0
                      && (chartType.equals(JavaFX_Charts.BAR_CHART)
                          || chartType.equals(JavaFX_Charts.CATEGORY_LINE_CHART)
                          || chartType.equals(JavaFX_Charts.PIE_CHART)))
                  || (fieldPosition == 1 && chartType.equals(JavaFX_Charts.HORIZONTAL_BAR_CHART))
                  || (fieldPosition == 0
                      && sqlQuery.getColumnNames().size() == 1
                      && chartType.equals(JavaFX_Charts.HORIZONTAL_BAR_CHART)))
                timestampString =
                    Utils.convertDBDateString_To_ViewDateString(
                            timestampString.substring(0, timestampString.indexOf(" ")),
                            DBTablesPanel.getGeneralDBProperties().getViewDateFormat())
                        + timestampString.substring(timestampString.indexOf(" "));
              else {
                timestampString = timestampString.replace("-", "");
                timestampString = timestampString.replace(":", "");
                timestampString = timestampString.replace(" ", "");
                timestampString =
                    timestampString.substring(0, 4) + "." + timestampString.substring(4);
                timestampString = timestampString.substring(0, 14);
              }

              if (sqlQuery.getColumnNames().size() == 1) {
                content[fieldPosition] = currentRow + "";
                content[1] = timestampString;
              } else content[fieldPosition] = timestampString;
            }
          }

          // BLOB/BYTEA/BINARY Type Field
          else if (Utils.isBlob(columnClass, columnType)) {
            Exception noBlobException = new Exception("BLOB");
            throw noBlobException;
          }

          // BIT Type Field
          else if (columnType.equals("BIT")) {
            currentContentData = db_resultSet.getByte(columnName);

            if (currentContentData == null) {
              if (ASSIGN_NULL_ZERO)
                setNullData(sqlQuery.getColumnNames().size(), content, fieldPosition, currentRow);
              else isContentNull = true;
            } else {
              String byteString = Byte.toString((byte) currentContentData);

              // Gets bit string.
              // Integer.toBinaryString(Integer.parseInt(byteString))

              if (sqlQuery.getColumnNames().size() == 1) {
                content[fieldPosition] = currentRow + "";
                content[1] = byteString;
              } else content[fieldPosition] = byteString;
            }
          }

          // TEXT/LONGTEXT Type Fields
          else if (Utils.isText(columnClass, columnType, true, columnSize)
              && !dataSourceType.equals(ConnectionManager.SQLITE)) {
            Exception noTextException = new Exception("TEXT");
            throw noTextException;
          }

          // Default Content. A normal table entry should
          // fall through here, to set content.
          else {
            currentContentData = db_resultSet.getString(columnName);

            if (currentContentData == null) {
              if (ASSIGN_NULL_ZERO)
                setNullData(sqlQuery.getColumnNames().size(), content, fieldPosition, currentRow);
              else isContentNull = true;
            } else {
              if (sqlQuery.getColumnNames().size() == 1) {
                content[fieldPosition] = currentRow + "";
                content[1] = currentContentData;
              } else content[fieldPosition] = currentContentData;
            }
          }
          fieldPosition++;
        }

        // Test Output
        /*
        for (int o = 0; o < content.length; o++)
           System.out.print("content[" + 0 + "] = " + content[o] + ", ");
        System.out.println();
        */

        // Add the current collected row data into
        // series.
        if (!isContentNull) {
          validInsert = insertData(fx_series, chartType, content);

          if (!validInsert) {
            loadProgressBar.setVisible(false);
            String message =
                resourceBundle.getResourceString(
                    "LoadSQLData.dialogmessage.InvalidPlotData,Row", "Invalid Plot Data, Row");

            JOptionPane.showMessageDialog(
                null,
                message + ": " + (currentRow - 1),
                resourceTitleAlert,
                JOptionPane.ERROR_MESSAGE);
          }
        }
        fieldPosition = 0;
        isContentNull = false;
      }

      // Add the series
      if (!loadProgressBar.isCanceled() && validInsert)
        (fxcharts.getChartPane()).addSeries(fx_series);
      else {
        fx_series.clear();
        // Runtime.getRuntime().gc();
      }

      // Closing out
      db_resultSet.close();
    } catch (Exception e) {
      loadProgressBar.setVisible(false);

      if (e instanceof SQLException)
        ConnectionManager.displaySQLErrors(((SQLException) e), "LoadSQLData loadData()");
      else {
        String message = resourceBundle.getResourceString("LoadSQLData.dialogmessage.Data", "Data");

        JOptionPane.showMessageDialog(
            null,
            e.toString() + " " + message + "!",
            resourceTitleAlert,
            JOptionPane.ERROR_MESSAGE);
      }
    } finally {
      try {
        if (db_resultSet != null) db_resultSet.close();
      } catch (SQLException sqle) {
        ConnectionManager.displaySQLErrors(((SQLException) sqle), "LoadSQLData loadData()");
      } finally {
        if (sqlStatement != null) sqlStatement.close();
      }
      loadProgressBar.dispose();
    }
  }