Search method involving Java and SQL Server

Question

Search method involving Java and SQL Server

Navigation

#1 by (4 votes)

2

How to do a search in java passing an attribute of the (SQL) as parameter?

I decided to type an outline of the 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

}

sql java

asked by anonymous 15.05.2014 / 03:28

1 answer

Access variable contained in a BPL How to make synchronous queries with Sequelize in Node.js

score 4 · Accepted Answer

PreparedStatement is a class whose one object your stores a precompiled value of an SQL statement. One of its utilities is to do searches safely, because the SQL statement is set by setting the parameters represented by ? , thus avoiding SQL injection.

The result of the prepared statement query is stored in an object of type ResultSet. Walk through this result set with the next() method by looking for the records returned by the query. At each iteration of next() , the result object 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 represented in text, 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 ( ? ) that has the sql statement, that is, if there are more parameters ( ? ) within sql then write the same amount of stmt.setTipo() so that the sql statement is constructed correctly, replacing all ? with values.

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

The while(rs.next()) is what we commonly use to get all records within the result set , however as in your case it is cpf it is possible that only a single record is returned in the query, so you can also treat 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 the above only if you are sure that there will not be more than one record with the same cpf, if there is only one record will be considered.

References:
PreparedStatement - Java SE7 > ResultSet - Java SE7