Code with JDBC fails with exception

First, Dependency Reversal Principle says it should be coded for abstractions, not implementations. A very straightforward application of this principle would be to use List instead of ArrayList .

Second, you are already using try-with-resources in your Connection , but it is important to use it properly in PreparedStatement and ResultSet .

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 ? in quotes.

Sixth, or you should treat the exceptions properly or relaunch. Doing neither is always a bad programming practice that will hide your mistakes.

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

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

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

Here is your reviewed 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);
    }
}

One possible improvement would be to revise how to instantiate Pessoa . Read this answer for more information about this.

Also, I'm sure this new MySql().conecta() could be done somehow better.

Do not use SQLException in catch because it will only display exceptions of type SQLException and hide all others, it's only worth choosing the exception type if you're going to treat it.

Take a test and give yourself an exception again behind 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 one: #

I recommend reading for a better understanding.