How to validate when Resultset does not find any value?

Question

I’m trying to build a Java function pesquisarCliente where it must be consulted in the Database whether it exists or not. If there is more than one result you should return all records, if you do not find anything you should return a warning to the user.

Using the while it brings me all the records as the search, but I could not validate when it does not find anything. If I use only the if it works, validates if the record exists or not, but presents a single record. I tried to make the combination of the while and of if, unsuccessful.

public String pesquisarCliente(String pesquisarCliente) {

    Connection conn = null;
    Statement stmt = null;

    try {


        Class.forName(JDBC_DRIVER).newInstance();

        conn = DriverManager.getConnection(DB_URL, USER, PASS);

        stmt = conn.createStatement();

        Scanner ler = new Scanner(System.in);

        System.out.println("Nome Cliente: ");
        pesquisarCliente = ler.nextLine();

        String sql = "SELECT cpf_cnpj,nome_rs,ativo FROM tb_cliente WHERE (cpf_cnpj ='" + pesquisarCliente + "' AND ativo = 1) OR (nome_rs LIKE '%" + pesquisarCliente + "%' AND ativo = 1)";

        ResultSet result = stmt.executeQuery(sql);

        if(result.next()){ // Dúvida <===

        while (result.next()) {

            String cpfclient = result.getString("cpf_cnpj");
            int status = result.getInt("ativo");
            String nomeclient = result.getString("nome_rs");


            // Display valores
            System.out.print("CPF/CNPJ: " + cpfclient);
            System.out.print("\t Nome: " + nomeclient);

            System.out.println("\n=====================\n");

            PreparedStatement upgradeTempGeralCliente = conn.prepareStatement("UPDATE tb_temp_geral SET [cpf_cnpj_Cliente] = '"+cpfclient+"', [nome_client]= '"+nomeclient+"' WHERE [id] = 1;");
    upgradeTempGeralCliente.executeUpdate();

            this.setNomeCliente(nomeclient);
            this.setCpfCliente(cpfclient);


        }
        }else{

            System.out.println("NÃO achei o cliente!");
        }

        result.close();
        stmt.close();
        conn.close();
        //ler.close();
    } catch (Exception e) {



    }

    return null;

}

I realized that on the line if(result.next()){ // Dúvida <=== if I replace result.next() by anything that is true (for example, if(funciona == true)) he enters the while normally.

I think the mistake is in using the result.next() within the if. But how to get the search return to return the message "I DIDN’T FIND THE CLIENT!"?

Answer 1

My answer will be very similar to what I answered in this question.

The method rs.next() will walk in the ResultSet, and if there is a record will return true.

Recalling that the if( rs.next() ) will only check if there is a next line as a result, if you want to iterate over multiple lines you should use the while( rs.next() ).

In this case, both have the same effect, the difference is that using if( rs.next() ) and the while( rs.next() ) in sequence will result in the loss of the first returned record, since you have already walked a position using the rs.next()

Answer 2

Well, in my projects, in case I really need to know if this resultset is empty or not, I do the following:

rs.first(); //tenta mover o ponteiro ate a primeira linha caso exista
if (!rs.first()){ //se nao existir uma primeira linha então o resultSet esta nulo.
    return null;
}

Soon after, to return the resultSet to start (in case to use a while(rs.next())), you can use the command:

rs.beforeFirst();

This returns the resultSet for the line "Previous to first"... That is, when executing the while(rs.next()) it will automatically go to the first line (which in case it got here, is because there is at least one line)...

Answer 3

As answered by @Cantoni in this question.

Another option is to use the method isBeforeFirst() also of class ResultSet.

This method will return true if the cursor is before the first record and false if the cursor is in any position or if there are no records.

Therefore, if this method is called right after doing the query and it returns false, then it is the indicative that no records were returned for the query performed.

Example:

if (!resultSet.isBeforeFirst()) System.out.println("Não há registros."); 

Answer 4

Thank you all!

It worked using the answer from @Rodrigo Santiago in another question.

if (resultSet.next()) {
            //Se passar ele vai estar na posicao 1 , já pronto para usar os getters
            do {
                //codigo
            } while (resultSet.next());

 } else {
            //Se nao passar significa que não houve resultados
 }

Answer 5

I don’t know result.next() but by the context I would say that when you use the if and in the while you are actually seeking the next result 2 times that I believe is not what you want.

You can say var resultado = result.next() and then do if (result != null) or if (result.Count > 0) depending on what the result.next() return to see if you returned any results. If not you return the message you did not find the client.

There inside the While you do while (resultado != null) and at the end of it (still inside the while) ago resultado = result.next() again. So, in the next iteration of while you are already checking the next value.