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:
x
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.