I’m currently having to do a code where, when ran, connects to a database and displays all the grades of a student after putting in their appropriate SSN. The main obstacle I’m running into is that once I put in the SSN it’ll only bring one of the grades rather than all. Is there something wrong with my logic? Am I just utilizing incorrect parts to do this? I’ve tried putting more than one lblstatus but that didn’t work. Here’s the code:
import javafx.application.Application; import javafx.scene.Scene; import javafx.scene.control.Button; import javafx.scene.control.Label; import javafx.scene.control.TextField; import javafx.scene.layout.HBox; import javafx.scene.layout.VBox; import javafx.stage.Stage; import java.sql.*; public class Week13 extends Application { private TextField tfSSN = new TextField(); private Label lblStatus = new Label(); private PreparedStatement preparedStatement; @Override public void start(Stage primaryStage) { initializeDB(); Button btShowGrade = new Button("Show Grade"); HBox hBox = new HBox(5); hBox.getChildren().addAll(new Label("SSN"), tfSSN, (btShowGrade)); VBox vBox = new VBox(10); vBox.getChildren().addAll(hBox, lblStatus); tfSSN.setPrefColumnCount(6); btShowGrade.setOnAction(e -> showGrade()); Scene scene = new Scene(vBox, 420, 80); primaryStage.setTitle("FindGrade"); primaryStage.setScene(scene); primaryStage.show(); } private void initializeDB() { try { Class.forName("oracle.jdbc.driver.OracleDriver"); System.out.println("Driver loaded"); Connection connection = DriverManager.getConnection("blank for the purpose of posting this"); System.out.println("Database connected"); String queryString = "select firstName, mi, lastName, title, grade " + "from Student, Enrollment, Course where Student.ssn = ? and " + "Enrollment.ssn = Student.ssn and Enrollment.courseId = " + "Course.courseId"; preparedStatement = connection.prepareStatement(queryString); } catch (Exception ex) { ex.printStackTrace(); } } private void showGrade() { String ssn = tfSSN.getText(); try { preparedStatement.setString(1, ssn); ResultSet rset = preparedStatement.executeQuery(); if (rset.next()) { String lastName = rset.getString(1); String mi = rset.getString(2); String firstName = rset.getString(3); String title = rset.getString(4); String grade = rset.getString(5); lblStatus.setText(firstName + " " + mi + " " + lastName + "'s grade on course " + title + " is " + grade); } else { lblStatus.setText("Not found"); } } catch (SQLException ex) { ex.printStackTrace(); } } }
Advertisement
Answer
You have used if
which is getting executed only once and therefore, you are getting only one record.
Replace
if (rset.next()) { String lastName = rset.getString(1); String mi = rset.getString(2); String firstName = rset.getString(3); String title = rset.getString(4); String grade = rset.getString(5); lblStatus.setText(firstName + " " + mi + " " + lastName + "'s grade on course " + title + " is " + grade); } else { lblStatus.setText("Not found"); }
with
StringBuilder s = new StringBuilder(); int c = 0; while (rset.next()) { String lastName = rset.getString(1); String mi = rset.getString(2); String firstName = rset.getString(3); String title = rset.getString(4); String grade = rset.getString(5); s.append(firstName).append(" ").append(mi).append(" ").append(lastName).append("'s grade on course ").append(title).append(" is ").append(grade).append("n"); c++; } if(c>0) { lblStatus.setText(s.toString()); } else { lblStatus.setText("Not found"); }
Note that using while
, you create a loop which will keep iterating until the condition, rset.next()
remains true
i.e. until the resultset has more elements.