Edunge Edunge - 1 year ago 70
MySQL Question

If block statement it not executed when comparing data retrieved from MySql DB

am trying to update a student database by clicking the Button

JButton NewUpdate = new JButton("UPDATE");
NewUpdate.setFont(new Font("Lucida Grande", Font.BOLD, 14));
NewUpdate.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
String regNo = RegNoCombo.getSelectedItem().toString();
String subjectCode = SubjectCombo.getSelectedItem().toString(); //gets value selected from subject code JCombobox
double score = Double.parseDouble(scoreTextField.getText().toString());
String testType = AssesmentCombo.getSelectedItem().toString();//gets value selected from assessment code JCombobox
String queryFromScoresTable = "SELECT CONCAT(student_id,subject_id) FROM scores;";

The switch statement below determines the option selected by the teacher. and retrieves information from the DB, by using the "queryFromScoresTable" above

switch (testType) {
case "Class Work":
try {
conn3 = DriverManager.getConnection("jdbc:mysql://localhost:3306/resultchecker_db","%username%","%password%");
ps3 = conn3.prepareStatement(queryFromScoresTable);
rs5 = ps3.executeQuery();
} catch (SQLException e1) {
JOptionPane.showMessageDialog(null, e1);
try {;
String compareData = rs5.getString(1);

the query retrieved from the DB is converted to a string with the variable name "compareData"

The "if" statement below is supposed to check if the student "regNo" and "testType" exists, if it does the block is executed by updating the information on the table

if((regNo + subjectCode).equals(compareData)){
String queryUpdateStudRecord = "UPDATE scores SET class_work=? WHERE student_id=? and subject_id = ?;";
conn4 = DriverManager.getConnection("jdbc:mysql://localhost:3306/resultchecker_db","%username%","%password%");
ps4 = conn4.prepareStatement(queryUpdateStudRecord);
ps4.setDouble(1, score);
ps4.setString(2, regNo);
ps4.setString(3, subjectCode);

Else this block is executed by inserting the student regNo and the score for selected testType into the DB.

String queryInsertRecord = "INSERT INTO scores(student_id, subject_id, class_work) VALUES (?,?,?)";
conn4 = DriverManager.getConnection("jdbc:mysql://localhost:3306/resultchecker_db","%username%","%password%");
ps4 = conn4.prepareStatement(queryInsertRecord);
ps4.setString(1, regNo);
ps4.setString(2, subjectCode);
ps4.setDouble(3, score);

The problem however is that only the "else" statement executes without evaluating the "if" statement, which means that if there already exists a regNo, it cannot be updated. Since it will return an existing primary key error.

Assistance is needed, thank u

Answer Source

No idea why the if doesn't trigger, but you could just go with a single query:

ON DUPLICATE KEY UPDATE student_id=VALUES(student_id), subject_id=VALUES(subject_id)

Then you don't need to care whether you should do an update or an insert - let the DB decide for you automatically.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download