When i am registering members with a memberId, how to check that the user id is already exists in the members table or not ? I have tried in below method. Primary key is the MemberID
MemberDbController.java
public static boolean checkMemberID(int memberId){ boolean memberIdExists = false; Connection conn=DBConnection.getDBConnection().getConnection(); Statement stm = conn.createStatement(); ResultSet rst = stm.executeQuery("SELECT * FROM members WHERE memberId='"+memberId+"'"); String id; if (rst.next()){ id = rst.getString("memberId"); if(id.equals(memberId)){ memberIdExists = true; } } return memberIdExists; }
MemberUiController.java
@FXML void addMember(ActionEvent event) { //Getting Value from User Input int memberId = Integer.parseInt(memberIdField.getText()); String name = nameField.getText(); String doa = doaField.getText(); RadioButton selectedRadioButton = (RadioButton) Gender.getSelectedToggle(); //Getting Selected Radio Button String gender = selectedRadioButton.getText(); String email = emailField.getText(); String phone = phoneField.getText(); try { if(MemberDbController.checkMemberID(memberId)){ Member member = new Member(memberId,name,doa,gender,email,phone); int i = MemberDbController.AddMember(member); if (i > 0) { //Insert Success Alert } else{ //Insert Failed Alert } }else{ //Member ID Already Exists Alert..! } } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); }
}
Its Throws an Already Exists Alert for both Duplicate & New Entry.
Advertisement
Answer
You’re compaing a String
to an Integer
object. This always yields false
:
public static boolean checkMemberID(int memberId) ... { int memberId ... String id; ... if(id.equals(memberId)){
The compiled version
id.equals(memberId)
is the same as id.equals(Integer.valueOf(memberId))
Checking the column value shouldn’t be necessary anyways, since your WHERE
clause wouldn’t have accepted rows with different values in this column. (Comparing with a string literal here seems odd to me. Are you sure the column’s type is a text type?)
The following should be sufficient:
public static boolean checkMemberID(int memberId) throws SQLException, ClassNotFoundException { Connection conn=DBConnection.getDBConnection().getConnection(); Statement stm = conn.createStatement(); ResultSet rst = stm.executeQuery("SELECT * FROM members WHERE memberId='"+memberId+"'"); return rst.next(); }
Furthermore in addMember
you treat the result as if it returns true
if the value does not exist, but the current.
Note that it may actually be better to try the insertion query immediately catch a SQLIntegrityConstraintViolationException
, assuming this is the only constraint in place for the table. Otherwise you may need to inspect the error which is problematic, since the error message depends on the db used.