How can I check if field exists in a ResultSet?

Question

I want to find user by ID entered into a textfield, If the ID is not found I would like to display "User Not Found".

This is what I have so far, but it is only displaying an error if the entered textfield is greater than MAX id in the table:

Statement stmt7 = connection.createStatement();
String query2 = "SELECT MAX(empid) AS MaxId FROM employee";
ResultSet rs2 = stmt7.executeQuery(query2);

while(rs2.next())
{
    if(Integer.parseInt(searchEmployeeFld.getText()) > rs2.getInt("MaxId"))
    {
        // create a alert
        Alert a = new Alert(AlertType.NONE);
        
        // set alert type
        a.setAlertType(AlertType.ERROR);
        
        // set content text
        a.setContentText("User Not Found");

        // show the dialog
        a.show();
    }
    else
    {
        Statement stmt6 = connection.createStatement();
        
        System.out.println("Executing a Query...");
        
        String query = "SELECT empname, empgrsal "
                + "FROM employee WHERE empid = " + searchEmployeeFld.getText();
        ResultSet rs1 = stmt6.executeQuery(query);
        
        while(rs1.next())
        {
        NameFld.setText(rs1.getString("empname"));
        
        String test1 = Double.toString(rs1.getDouble("empgrsal"));
        
        grossSalaryFld.setText(test1);
        
        // calculate net salary
        double grossSal = rs1.getDouble("empgrsal");
        grossSal -= (grossSal * 0.3);
        
        String test2 = Double.toString(grossSal);
        netSalaryFld.setText(test2);
        }
        
        rs1.close();
    }
}

rs2.close();

Please learn about prepared statements and parameterized queries. Your code is vulnerable to SQL injection as you're concatenating string values into your query string. As to your problem: you should query if there is a record with the specified ID in the database (or if you have suitable constraints, just try to insert and handle the primary key constraint violation). — Mark Rotteveel, Aug 19 '21 at 09:03
Why go through the extra trouble to first execute a query to find the max id? Just execute the second query and handle the case when it doesn’t return any rows. — Joakim Danielson, Aug 19 '21 at 09:05
Does this answer your question? [Fastest way to determine if record exists](https://stackoverflow.com/questions/18114458/fastest-way-to-determine-if-record-exists) — DevilsHnd - 退職した, Aug 19 '21 at 13:03

score 1 · Answer 1 · answered Aug 19 '21 at 14:49

Here is a modified and annotated version of your code that should do what you are looking for.

    // Use a prepared statement, with a ? in the place of empid
    // This will protect from SQL injection attacks, and more, it will
    // prevent your database SGA from getting choked full of thousands of
    // similar SQL statements. This is much more efficient from a DB perspective.
    String query = "SELECT empname, empgrsal FROM employee WHERE empid = ?";

    // A flag to see if we got the user
    boolean found = false;

    // Using "try with resources" makes life much easier
    try (PreparedStatement ps1 = connection.prepareStatement(query)){

      // You have to set the value for the "?" in the SQL statement.
      // If an employee ID is a number, then you really should convert
      // the text to a number and use "setLong" instead of "setString" 
      ps1.setString(1,searchEmployeeFld.getText());
      try(ResultSet rs1 = ps1.executeQuery()){

        // No need to loop. Your code is designed to find one user by key.
        if(rs1.next()){
          found = true;
          NameFld.setText(rs1.getString("empname"));

          String test1 = Double.toString(rs1.getDouble("empgrsal"));

          grossSalaryFld.setText(test1);
          
          // calculate net salary
          double grossSal = rs1.getDouble("empgrsal");
          grossSal -= (grossSal * 0.3);
          
          String test2 = Double.toString(grossSal);
          netSalaryFld.setText(test2);
        }
      }
    } // Should catch SQLException somewhere and do something about it

    if(!found){
      // Didn't find the ID...do something about it
    }

A few more notes...

You are (probably) executing SQL within the Swing GUI thread, which is not optimal. For fast queries it's not so bad, but any delay will be felt by the user as a frozen UI. And if you aren't in the GUI thread there are other concerns (calls to Swing objects should be in the GUI thread).

The "try with resources" technique will automatically close the prepared statement and result set.

How can I check if field exists in a ResultSet?

1 Answers1