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()");
}
public LoadSQLData(
JavaFX_Charts fxcharts,
String query,
AResourceBundle resourceBundle,
boolean showProgressBar) {
this.fxcharts = fxcharts;
this.resourceBundle = resourceBundle;
this.showProgressBar = showProgressBar;
// Initalize & Execute.
dataSourceType = ConnectionManager.getDataSourceType();
resourceTitleAlert = resourceBundle.getResourceString("LoadSQLData.dialogtitle.Alert", "Alert");
execute(query);
}
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();
}
}
