I have created a GUI that uses checkboxes to pull columns from an SQL query. Depending on the checkboxes that the user has checked certain columns SHOULD show up within a text area. However, when I run my program only one column shows up within the text area at a time. I need to be able to display multiple columns preferably next to each other similar to a table.
I believe the problem stems from how my if-statements work with the Result Sets, but I am unsure how specifically this is causing the problem if it is. I believe this to be true because outside of the if-statements the query and Result Sets work like they are supposed to.
Here is the part that I believe is the offending code.
if (cbVin.isSelected()) {
try {
if (rbAscending.isSelected()) {
while (rsetAsc.next()) {
vinAsc = rsetAsc.getString(1);
displayArea.appendText("VIN: " + vinAsc + "\n");
}
}
if (rbDescending.isSelected()) {
while (rsetDesc.next()) {
vinDesc = rsetDesc.getString(1);
displayArea.appendText("VIN: " + vinDesc + "\n");
}
}
} catch (SQLException e1) {
e1.printStackTrace();
}
}
if (cbMake.isSelected()) {
try {
if (rbAscending.isSelected()) {
while (rsetAsc.next()) {
makeAsc = rsetAsc.getString(2);
displayArea.appendText("Make: " + makeAsc + "\n");
}
}
if (rbDescending.isSelected()) {
while (rsetDesc.next()) {
makeDesc = rsetDesc.getString(2);
displayArea.appendText("Make: " + makeDesc + "\n");
}
}
} catch (SQLException e1) {
e1.printStackTrace();
}
}
if (cbModel.isSelected()) {
try {
if (rbAscending.isSelected()) {
while (rsetAsc.next()) {
modelAsc = rsetAsc.getString(3);
displayArea.appendText("Model: " + modelAsc + "\n");
}
}
if (rbDescending.isSelected()) {
while (rsetDesc.next()) {
modelDesc = rsetDesc.getString(3);
displayArea.appendText("Model: " + modelDesc + "\n");
}
}
} catch (SQLException e1) {
e1.printStackTrace();
}
}
if (cbYear.isSelected()) {
try {
if (rbAscending.isSelected()) {
while (rsetAsc.next()) {
yearAsc = rsetAsc.getString(4);
displayArea.appendText("Year: " + yearAsc + "\n");
}
}
if (rbDescending.isSelected()) {
while (rsetDesc.next()) {
yearDesc = rsetDesc.getString(4);
displayArea.appendText("Year: " + yearDesc + "\n");
}
}
} catch (SQLException e1) {
e1.printStackTrace();
}
}
if (cbColor.isSelected()) {
try {
if (rbAscending.isSelected()) {
while (rsetAsc.next()) {
colorAsc = rsetAsc.getString(5);
displayArea.appendText("Color: " + colorAsc + "\n");
}
}
if (rbDescending.isSelected()) {
while (rsetDesc.next()) {
colorDesc = rsetDesc.getString(5);
displayArea.appendText("Color: " + colorDesc + "\n");
}
}
} catch (SQLException e1) {
e1.printStackTrace();
}
}
if (cbPrice.isSelected()) {
try {
if (rbAscending.isSelected()) {
while (rsetAsc.next()) {
priceAsc = rsetAsc.getString(6);
displayArea.appendText("Price: " + priceAsc + "\n");
}
}
if (rbDescending.isSelected()) {
while (rsetDesc.next()) {
priceDesc = rsetDesc.getString(1);
displayArea.appendText("Price: " + priceDesc + "\n");
}
}
} catch (SQLException e1) {
e1.printStackTrace();
}
}
Also I will post the full code for reference.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javafx.application.Application;
import javafx.geometry.Insets;
import javafx.scene.Scene;
import javafx.scene.control.Button;
import javafx.scene.control.CheckBox;
import javafx.scene.control.Label;
import javafx.scene.control.RadioButton;
import javafx.scene.control.TextArea;
import javafx.scene.control.TextField;
import javafx.scene.control.ToggleGroup;
import javafx.scene.layout.BorderPane;
import javafx.scene.layout.HBox;
import javafx.scene.layout.VBox;
import javafx.stage.Stage;
public class ProductGUI extends Application {
private String cssLayout = "-fx-border-color: black;\n" + "-fx-border-width: 1;\n";
private int clickCount = 0;
private Connection connection;
private ResultSet rsetAsc;
private ResultSet rsetDesc;
private PreparedStatement preparedStatement;
private String queryStringAsc;
private String queryStringDesc;
private String vinAsc;
private String vinDesc;
private String makeAsc;
private String makeDesc;
private String modelAsc;
private String modelDesc;
private String yearAsc;
private String yearDesc;
private String colorAsc;
private String colorDesc;
private String priceAsc;
private String priceDesc;
private TextArea displayArea = new TextArea();
private TextField tfwhere = new TextField();
@Override
public void start(Stage primaryStage) {
// The GUI is created here within a try catch block to catch exceptions.
/*
* This GUI will be placed within the BorderPane, this will contain a TableList
* to output the parts of the product table that the user desires. This will be
* regulated using CheckBoxes and if statements. The Query button will be used
* to query the database with a lambda expression pointing to a custom method.
*/
try {
// Creating all the UI controls.
// Placement controls.
BorderPane root = new BorderPane();
VBox vbox = new VBox();
HBox hbox = new HBox();
// Buttons
Button btQuery = new Button("Query");
Button btReset = new Button("Reset");
// Labels
Label lbChooseField = new Label("Choose Your Fields");
Label lbOrdered = new Label("Order by Price");
Label lbWhere = new Label("Specify a Where Statement");
Label lbWarning = new Label("Please Reset before your next Query!");
// CheckBoxes
CheckBox cbVin = new CheckBox("VIN");
CheckBox cbMake = new CheckBox("Make");
CheckBox cbModel = new CheckBox("Model");
CheckBox cbYear = new CheckBox("Year");
CheckBox cbColor = new CheckBox("Color");
CheckBox cbPrice = new CheckBox("Price");
// RadioButtons
RadioButton rbAscending = new RadioButton("Ascending");
RadioButton rbDescending = new RadioButton("Descending");
ToggleGroup tRadio = new ToggleGroup();
// Placing the controls.
root.setCenter(displayArea);
displayArea.setEditable(false);
displayArea.setMouseTransparent(true);
displayArea.setFocusTraversable(false);
root.setPadding(new Insets(12, 12, 12, 12));
root.setBottom(hbox);
hbox.setPadding(new Insets(12, 12, 12, 12));
root.setRight(vbox);
vbox.setPadding(new Insets(12, 12, 12, 12));
vbox.setStyle(cssLayout);
// Placing within HBox.
hbox.getChildren().addAll(btQuery, btReset);
// Placing within VBox.
vbox.getChildren().addAll(lbChooseField, cbVin, cbMake, cbModel, cbYear, cbColor, cbPrice, lbOrdered,
rbAscending, rbDescending, lbWhere, tfwhere);
rbAscending.setToggleGroup(tRadio);
rbAscending.setSelected(true);
rbDescending.setToggleGroup(tRadio);
// Lambda expression for btQuery will create columns based on CheckBox
// selections and fill with
// appropriate data.
btQuery.setOnAction(e -> {
// The purpose of clickCount is to make sure that the button does not output
// multiple repeat columns.
if (clickCount < 1) {
// Initialize a connection to the example database.
initializeDB();
if (cbVin.isSelected()) {
try {
if (rbAscending.isSelected()) {
while (rsetAsc.next()) {
vinAsc = rsetAsc.getString(1);
displayArea.appendText("VIN: " + vinAsc + "\n");
}
}
if (rbDescending.isSelected()) {
while (rsetDesc.next()) {
vinDesc = rsetDesc.getString(1);
displayArea.appendText("VIN: " + vinDesc + "\n");
}
}
} catch (SQLException e1) {
e1.printStackTrace();
}
}
if (cbMake.isSelected()) {
try {
if (rbAscending.isSelected()) {
while (rsetAsc.next()) {
makeAsc = rsetAsc.getString(2);
displayArea.appendText("Make: " + makeAsc + "\n");
}
}
if (rbDescending.isSelected()) {
while (rsetDesc.next()) {
makeDesc = rsetDesc.getString(2);
displayArea.appendText("Make: " + makeDesc + "\n");
}
}
} catch (SQLException e1) {
e1.printStackTrace();
}
}
if (cbModel.isSelected()) {
try {
if (rbAscending.isSelected()) {
while (rsetAsc.next()) {
modelAsc = rsetAsc.getString(3);
displayArea.appendText("Model: " + modelAsc + "\n");
}
}
if (rbDescending.isSelected()) {
while (rsetDesc.next()) {
modelDesc = rsetDesc.getString(3);
displayArea.appendText("Model: " + modelDesc + "\n");
}
}
} catch (SQLException e1) {
e1.printStackTrace();
}
}
if (cbYear.isSelected()) {
try {
if (rbAscending.isSelected()) {
while (rsetAsc.next()) {
yearAsc = rsetAsc.getString(4);
displayArea.appendText("Year: " + yearAsc + "\n");
}
}
if (rbDescending.isSelected()) {
while (rsetDesc.next()) {
yearDesc = rsetDesc.getString(4);
displayArea.appendText("Year: " + yearDesc + "\n");
}
}
} catch (SQLException e1) {
e1.printStackTrace();
}
}
if (cbColor.isSelected()) {
try {
if (rbAscending.isSelected()) {
while (rsetAsc.next()) {
colorAsc = rsetAsc.getString(5);
displayArea.appendText("Color: " + colorAsc + "\n");
}
}
if (rbDescending.isSelected()) {
while (rsetDesc.next()) {
colorDesc = rsetDesc.getString(5);
displayArea.appendText("Color: " + colorDesc + "\n");
}
}
} catch (SQLException e1) {
e1.printStackTrace();
}
}
if (cbPrice.isSelected()) {
try {
if (rbAscending.isSelected()) {
while (rsetAsc.next()) {
priceAsc = rsetAsc.getString(6);
displayArea.appendText("Price: " + priceAsc + "\n");
}
}
if (rbDescending.isSelected()) {
while (rsetDesc.next()) {
priceDesc = rsetDesc.getString(1);
displayArea.appendText("Price: " + priceDesc + "\n");
}
}
} catch (SQLException e1) {
e1.printStackTrace();
}
}
close();
clickCount++;
} else {
// Part will tell the user that a reset of the GUI via btReset is required to
// use again.
root.setTop(lbWarning);
}
});
// The Lambda for btReset which will bring the GUI to it's original state,
// allowing for another query.
btReset.setOnAction(e -> {
clickCount = 0;
if (cbVin.isSelected()) {
cbVin.setSelected(false);
}
if (cbMake.isSelected()) {
cbMake.setSelected(false);
}
if (cbModel.isSelected()) {
cbModel.setSelected(false);
}
if (cbYear.isSelected()) {
cbYear.setSelected(false);
}
if (cbColor.isSelected()) {
cbColor.setSelected(false);
}
if (cbPrice.isSelected()) {
cbPrice.setSelected(false);
}
displayArea.clear();
tfwhere.clear();
root.setTop(null);
rbAscending.setSelected(true);
});
// Scene created by default Main method from JavaFX project.
Scene scene = new Scene(root, 900, 700);
scene.getStylesheets().add(getClass().getResource("application.css").toExternalForm());
primaryStage.setScene(scene);
primaryStage.setTitle("Product Table GUI");
primaryStage.show();
} catch (
Exception e) {
e.printStackTrace();
}
}
// This method initializes the database and creates the connection. It is
// located within a try catch block to catch exceptions.
private void initializeDB() {
try {
// Load the JDBC driver.
Class.forName("com.mysql.cj.jdbc.Driver");
System.out.println("Driver loaded");
// Create the connection.
connection = DriverManager.getConnection("jdbc:mysql://localhost/example", "scott", "tiger");
System.out.println("Database Connected.");
queryStringAsc = "select * from product " + tfwhere.getText().toLowerCase() + " order by price asc;";
preparedStatement = connection.prepareStatement(queryStringAsc);
rsetAsc = preparedStatement.executeQuery();
queryStringDesc = "select * from product " + tfwhere.getText().toLowerCase() + " order by price desc;";
preparedStatement = connection.prepareStatement(queryStringDesc);
rsetDesc = preparedStatement.executeQuery();
} catch (Exception ex) {
ex.printStackTrace();
}
}
private void close() {
try {
if (rsetAsc != null) {
rsetAsc.close();
}
if (rsetDesc != null) {
rsetDesc.close();
}
if (preparedStatement != null) {
preparedStatement.close();
}
if (connection != null) {
connection.close();
}
} catch (Exception e) {
}
}
public static void main(String[] args) {
launch(args);
}
}
Aucun commentaire:
Enregistrer un commentaire