MySQL Record does not save

Question

I have a problem with inserting a record into mySQL database, the query simply does not get executed and I don't understand why it does not print any errors when catching an exception. Any ideas please? I have checked the spelling many times and the table itself, the connection also works because it is used in my Login Window which appears before the one shown below.

public class RegisterStudent implements Initializable{

@FXML
TextField txtID;
@FXML
TextField txtFirstName;
@FXML
TextField txtSecondName;
@FXML
TextField txtGender;
@FXML
TextField txtDOB;
@FXML
TextField txtAddress;
@FXML
TextField txtPostCode;
@FXML
TextField txtMobileNumber;
@FXML
Button btnRegister;
@FXML
Button btnClear;

Connection connection = null;
PreparedStatement preparedStatement = null;

// Set Clear Button.
@FXML
private void setBtnClear() {
    txtID.clear();
    txtFirstName.clear();
    txtSecondName.clear();
    txtGender.clear();
    txtDOB.clear();
    txtAddress.clear();
    txtPostCode.clear();
    txtMobileNumber.clear();
}
// Set Register Button - Saves student record to database.
@FXML
private void setBtnRegister(ActionEvent event) {
    try {
        if(txtFirstName.getText().trim().isEmpty() || 
        txtSecondName.getText().trim().isEmpty() || 
        txtGender.getText().trim().isEmpty() || 
        txtDOB.getText().trim().isEmpty() || txtAddress.getText().trim().isEmpty()
                || txtPostCode.getText().trim().isEmpty() || txtMobileNumber.getText().trim().isEmpty()) {
            DBUtilities.showErrorMsg("Error:", "All fields must be populated!");
        }else {
            connection = DBUtilities.getConnection();
            String SQLQuery = "INSERT INTO student_details ('First_Name', 'Second_Name', 'Gender', 'Date_Of_Birth', 'Address', 'Post_Code', 'Mobile_Number')" + " VALUES (?, ?, ? ,? ,? ,? ,?)";
            preparedStatement = connection.prepareStatement(SQLQuery);
            preparedStatement.setString(1, txtFirstName.getText().trim());
            preparedStatement.setString(2, txtSecondName.getText().trim());
            preparedStatement.setString(3, txtGender.getText().trim());
            preparedStatement.setString(4, txtDOB.getText().trim());
            preparedStatement.setString(5, txtAddress.getText().trim());
            preparedStatement.setString(6, txtPostCode.getText().trim());
            preparedStatement.setString(7, 
            txtMobileNumber.getText().trim());
            preparedStatement.executeUpdate();
            DBUtilities.showInforMsg("Record Saved", "Record has been saved 
    successfully!");
        }
    }catch (Exception exception) {
        //DBUtilities.showErrorMsg("Error:", "Record could not be saved!");
        exception.printStackTrace();
    }finally {
        DBUtilities.closePreparedStatement(preparedStatement);
        DBUtilities.closeConnection(connection);
    }
}
@Override
public void initialize(URL location, ResourceBundle resources) {
    btnRegister.setOnAction(this::setBtnRegister);
}
}

You certainly have to give at least some sort of error message. If nothing else, put a breakpoint in the catch block and check manually for the exception type and exception message. — Aleksandar Stojadinovic, Apr 15 '17 at 22:16
I got rid of the brackets and nothing. What do you mean by breakpoint? — cris22tian, Apr 15 '17 at 22:18
You should be catching [SQLException](https://docs.oracle.com/javase/7/docs/api/java/sql/SQLException.html) rather than Exception to be able to find out exactly what's going wrong. See answers on [Difference between Exception and SQLException](http://stackoverflow.com/q/22944978/205233). — Filburt, Apr 15 '17 at 22:42

score 0 · Answer 1 · answered Apr 15 '17 at 22:26

0

Try, connection.commit; after preparedStatement.executeUpdate(); this statement. May be the autocommit is false.

answered Apr 15 '17 at 22:26

ProgrammerBoy

876
6
19

The table has ID column which is set to auto increment could this possibly be the problem? – cris22tian Apr 15 '17 at 22:35
try upgrading the driver and check if it helps! – ProgrammerBoy Apr 16 '17 at 07:25

Mr Morgan · Answer 2 · 2017-04-16T14:28:06.527

I have had issues like this before. So assuming that your table has an integer-based primary key, which should be set to auto-increment, the failure to insert the record might be due to statement:

preparedStatement = connection.prepareStatement(SQLQuery);

Try replacing this with:

preparedStatement = connection.prepareStatement(query, preparedStatement.RETURN_GENERATED_KEYS);

And when your code performs the insert, test for the key generated by MySQL:

 try {
     int noRows = ps.executeUpdate();            
     if (noRows == 1) {
         ResultSet rs = ps.getGeneratedKeys();
         if (rs.next()) {

             // Get the newly generated primary key from MySQL.            
             int id = (int) keyResultSet.getInt(1);  

             // Call setter method for primary key's attribute in Java student_details object.
         }           
     }
 }
 catch(SQLException ex) {                  
     ex.printstackTrace();
 }

This has solved the problem for me in the past. And, make sure that you have a recent copy of MySQLConnector-X.X.X.jar in your project's build path or folder.

MySQL Record does not save

2 Answers2