JDBC code fails with exception

Asked

Viewed 92 times

0

I’m trying to perform a database search through java.

I am using this code, but this entering the exception:

public ArrayList<Pessoas> pesquisar(String nome) {
    ArrayList<Pessoas> lista = new ArrayList<Pessoas>();
    try(Connection con = new MySql().conecta()) {

        String sql = "select *"+
        " from pessoas"+
        "where nome like '%?%'";

        PreparedStatement stmt = con.prepareStatement(sql);

        stmt.setString(1, nome);

        java.sql.ResultSet rs = stmt.executeQuery(sql);


        while (rs.next()){

        Pessoas pesquisa = new Pessoas();
        pesquisa.setNome(rs.getString("nome"));
        pesquisa.setDataNasc(rs.getString("dataNasc"));
        pesquisa.setTipo(TIPO.valueOf(rs.getString("sexo")));
        pesquisa.setEmail(rs.getString("email"));
        pesquisa.setCelular(rs.getString("celular"));
        pesquisa.setTelefone(rs.getString("telefone"));
        pesquisa.setEndereco(rs.getString("endereco"));
        pesquisa.setNumCasa(rs.getString("numCasa"));
        pesquisa.setBairro(rs.getString("bairro"));
        pesquisa.setCidade(rs.getString("cidade"));
        pesquisa.setCep(rs.getString("cep"));
        pesquisa.setEstado(rs.getString("estado"));
        pesquisa.setObservacao(rs.getString("observacao"));

        // adicionando o objeto à lista
        lista.add(pesquisa);


        }

        rs.close();
        stmt.execute();
        stmt.close();

    } catch(SQLException e) {
        System.out.println("teste15");

    }
    return lista;

}
java database jdbc arraylist

  • That one System.out.println("teste15"); does not serve for nothing in this code, does not help to understand why fell in the catch. So, remove this, and exchange for e.printStackTrace(); which is what will really show the reason.

  • So take out the try-catch to see the exception happen. I often say that my code rarely exceeds half a dozen try-catch, i don’t understand why people fill it up in code, catch exception almost always leaves code less robust and no more.

  • @Maniero the exception is checked, and the damn Ides indicate this solution to "solve the problem" of the checked exception. With a good intention, the IDE ends up inducing those who are learning to do something without having any idea what they are doing.

  • Anyway, if you were a little more careful and put a space after the table name or a space before the where you would not have generated error while preparing query

2 answers

3

First of all, the principle of inversion of dependencies says it should code for abstractions, not implementations. A very direct application of this principle would be to use List instead of ArrayList.

Second, you’re already using the Try-with-Resources in his Connection, but it is important to use it properly in the PreparedStatement and in the ResultSet also.

Third, use the diamond syntax whenever possible.

Fourth, prefer to use class names always in the singular.

Fifth, your SQL syntax is wrong. You should not put the ? in quotes.

Sixth, either you should treat the exceptions properly or relaunch. Not doing either is always a bad programming practice that will hide you the mistakes.

Seventh, you have a stmt.execute(); leftover silly at the end.

Eighth, you were running SQL in two different places, with con.prepareStatement(sql) and with stmt.executeQuery(sql). Replace the second form by stmt.executeQuery().

Ninth, class names should not be written in uppercase letters (TIPO). Use Tipo. See more about this in that reply.

Here’s your revised code:

private static final String SELECT_SQL = "SELECT * FROM Pessoas WHERE nome LIKE ?";

public List<Pessoa> pesquisar(String nome) {
    try (
        Connection con = new MySql().conecta();
        PreparedStatement stmt = con.prepareStatement(SELECT_SQL))
    {
        stmt.setString(1, "%" + nome + "%");

        try (ResultSet rs = stmt.executeQuery()) {
            List<Pessoa> lista = new ArrayList<>();
            while (rs.next()) {
                Pessoa pesquisa = new Pessoa();
                pesquisa.setNome(rs.getString("nome"));
                pesquisa.setDataNasc(rs.getString("dataNasc"));
                pesquisa.setTipo(Tipo.valueOf(rs.getString("sexo")));
                pesquisa.setEmail(rs.getString("email"));
                pesquisa.setCelular(rs.getString("celular"));
                pesquisa.setTelefone(rs.getString("telefone"));
                pesquisa.setEndereco(rs.getString("endereco"));
                pesquisa.setNumCasa(rs.getString("numCasa"));
                pesquisa.setBairro(rs.getString("bairro"));
                pesquisa.setCidade(rs.getString("cidade"));
                pesquisa.setCep(rs.getString("cep"));
                pesquisa.setEstado(rs.getString("estado"));
                pesquisa.setObservacao(rs.getString("observacao"));

                // adicionando o objeto à lista
                lista.add(pesquisa);
            }
            return lista;
        }
    } catch (SQLException e) {
        throw new RuntimeException(e);
    }
}

A possible improvement would be to review how to instantiate Pessoa. Read that answer for more information about this.

Besides, I’m sure this one new MySql().conecta() could be done some other way better.

0

Do not use Sqlexception in catch as it will display only exceptions of type Sqlexception and hide all others, only worth choosing the type of exception if you are going to treat it.

Do a test and if you give Exception again behind the ROOT CAUSE here!

public ArrayList<Pessoas> pesquisar(String nome) {
    ArrayList<Pessoas> lista = new ArrayList<Pessoas>();

    try {
        Connection con = new MySql().conecta()

        String sql = "select *"+
        " from pessoas"+
        "where nome like '%?%'";

        PreparedStatement stmt = con.prepareStatement(sql);

        stmt.setString(1, nome);

        // trabalhe com PreparedStatement assim!
        stmt.executeQuery();
        java.sql.ResultSet rs = stmt.getResultSet();


        while (rs.next()){

        Pessoas pesquisa = new Pessoas();
        pesquisa.setNome(rs.getString("nome"));
        pesquisa.setDataNasc(rs.getString("dataNasc"));
        pesquisa.setTipo(TIPO.valueOf(rs.getString("sexo")));
        pesquisa.setEmail(rs.getString("email"));
        pesquisa.setCelular(rs.getString("celular"));
        pesquisa.setTelefone(rs.getString("telefone"));
        pesquisa.setEndereco(rs.getString("endereco"));
        pesquisa.setNumCasa(rs.getString("numCasa"));
        pesquisa.setBairro(rs.getString("bairro"));
        pesquisa.setCidade(rs.getString("cidade"));
        pesquisa.setCep(rs.getString("cep"));
        pesquisa.setEstado(rs.getString("estado"));
        pesquisa.setObservacao(rs.getString("observacao"));

        // adicionando o objeto à lista
        lista.add(pesquisa);


        }

        rs.close();
        //stmt.execute(); não precisa disso
        stmt.close();

    } catch(Exception e) {
        e.printStackTrace();
    }
    return lista;

}

This question/answer is associated with this other: Exceptions in Java

I recommend reading for a better understanding.

  • Missing space between table name pessoas and the keyword where keeps generating syntax error in SQL, the String being prepared is the absurd "select * from pessoaswhere nome like '%?%'"

Browser other questions tagged java database jdbc arraylist

You are not signed in. Login or sign up in order to post.