Search method involving Java and SQL Server

Question

How to do a java search by passing an (SQL) attribute as parameter?

I decided to type a sketch of Cod:

MAIN

static private void PesquisarUsuario(Connection con) throws SQLException
{
    int cpf;
    Scanner s = new Scanner(System.in);
    Pesquisar pes = new Pesquisar();

    System.out.println("Informe o CPF a ser pesquisado:");
    pes.cpf = s.nextInt();
    System.out.println();

    pes.PesquisarUsu(pes, con);



    s.close();
}

CLASS SEARCH

public void PesquisarUsu(Pesquisar pes, Connection con) throws SQLException {
    String sql = null;
    PreparedStatement stmt;
    //Não sei apartir daqui de como efetuar a pesquisa

}

Answer 1

Preparedstatement is a class whose object stores a pre-compiled value of an SQL sentence. One of its utilities is to make searches in a safe way, because the SQL sentence is mounted setting the parameters represented by ?, thus avoiding SQL injection.

The result of the Prepared statement is stored in an object of type Resultset. Scroll through this result set with the method next() searching the records returned by query. With each iteration of next(), the object of result set will be referencing the next record of the result.

Example:

public void PesquisarUsu(Pesquisar pes, Connection con) throws SQLException {
    String sql = "SELECT * FROM Usuarios WHERE cpf=?";
    PreparedStatement stmt = conn.prepareStatement(sql);
    stmt.setString(1, pes.getCpf());    //coloca a String de pes.getCpf() no 
                                        //lugar do ? na sentença SQL
    ResultSet rs = stmt.executeQuery(); //executa query e armazena resultado em rs
    while(rs.next()) {   //enquanto tiver resultados, anda para o próxima
        System.out.println("Nome: " + rs.getString("nome")); 
        System.out.println("Idade: " + rs.getInt("idade"));
        System.out.println("Aprovado: " + rs.getBoolean("aprovado"));
    }
    rs.close();
    stmt.close();
}

In the above example the table must have the fields nome text-based, idade represented in number and aprovado represented in Boolean, as they must be compatible with the type defined in rs.getTipo().

The amount of sentences stmt.setTipo() must be exactly equal to the number of parameters (?) which has the sql sentence, that is, if there are more parameters (?) within sql should write the same amount of stmt.setTipo() to construct the sql sentence correctly, replacing all the ? by values.

As in the result set, the tipo in stmt.setTipo() must be exactly the same type as in the second parameter of the method, i.e. stmt.setString(1, pes.getCpf()); the method pes.getCpf() must return a String. The first parameter of the method stmt.setTipo() is the position of the parameter (?) within String sql, i.e., 1, 2, 3...

The while(rs.next()) is what we commonly use to pick up all records within the result set, however as in your case it is possible that only a single record in the query will be returned, so you can also treat the rs as follows:

    if(rs.next()) {   //se houver ao menos um resultados
        System.out.println("Nome: " + rs.getString("nome")); 
        System.out.println("Idade: " + rs.getInt("idade"));
        System.out.println("Aprovado: " + rs.getBoolean("aprovado"));
    }
    else {
        System.out.println("Nenhum usuario encontrado para cpf: " + pes.getCpf()); 
    }      

Do as above only if you are sure that there will be no more than one record with the same Cpf, if there is only the first will be considered.

References:
Preparedstatement - Java SE7
Resultset - Java SE7