I cannot run a query with jdbc java

Question

public List<Sistema> buscarUsuarioSistemaPor(Long cpf) throws SQLException {
    List<Sistema> listSistemas = new ArrayList<>();

    String sql = "select s.* from sistema as s\r\n" + 
            "inner join usuariosistema as u on (u.idsistema = s.idsistema)\r\n" + 
            "where u.usuario_cpf = @cpf";
    connect();

    PreparedStatement prepareStatement = jdbcConnection.prepareStatement(sql);
    prepareStatement.setLong(1, cpf);

    ResultSet resultSet = prepareStatement.executeQuery(sql);

    while (resultSet.next()) {
        Long idSistema = resultSet.getLong("idSistema");
        String nome = resultSet.getString("Nome");

        Sistema Sistema = new Sistema(idSistema, nome);

        listSistemas.add(Sistema);
    }

    resultSet.close();
    prepareStatement.close();

    disconnect();

    return listSistemas;
}

The following error is occurring: Parameter index out of range (1 > number of Parameters, which is 0). Parameter index out of range (1 > number of Parameters, which is 0). Parameter index out of range (1 > number of Parameters, which is 0). Parameter index out of range (1 > number of Parameters, which is 0). Parameter index out of range (1 > number of Parameters, which is 0).

I am using mysql connector version:

Citation

<dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.17</version>
    </dependency>

Doing the parameter in the query this other way:

public List<Sistema> buscarUsuarioSistemaPor(Long cpf) throws SQLException {
    List<Sistema> listSistemas = new ArrayList<>();

    String sql = "select s.* from sistema as s\r\n" + 
            "inner join usuariosistema as u on (u.idsistema = s.idsistema)\r\n" + 
            "where u.usuario_cpf = ?";
    connect();

    PreparedStatement prepareStatement = jdbcConnection.prepareStatement(sql);
    prepareStatement.setLong(1, cpf);

    ResultSet resultSet = prepareStatement.executeQuery(sql);

    while (resultSet.next()) {
        Long idSistema = resultSet.getLong("idSistema");
        String nome = resultSet.getString("Nome");

        Sistema Sistema = new Sistema(idSistema, nome);

        listSistemas.add(Sistema);
    }

    resultSet.close();
    prepareStatement.close();

    disconnect();

    return listSistemas;
}

The following error occurs:

java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?' at line 3
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
at com.mysql.cj.jdbc.StatementImpl.executeQuery(StatementImpl.java:1218)
at br.com.sefaz.dao.Usuario_SistemaDAO.buscarUsuarioSistemaPor(Usuario_SistemaDAO.java:101)
at br.com.sefaz.controller.Usuario_SistemaController.buscarUsuarioSistemaPor(Usuario_SistemaController.java:37)
at br.com.sefaz.dao.UsuarioDAO.listAllUsuarios(UsuarioDAO.java:100)
at br.com.sefaz.controller.UsuarioController.listAllUsuarios(UsuarioController.java:52)
at br.com.sefaz.main.Teste.main(Teste.java:35)

I don’t know what else to do, somebody help me please?

Answer 1

Friend change your code

ResultSet resultSet = prepareStatement.executeQuery(sql);

To

ResultSet resultSet = prepareStatement.executeQuery();

You do not need to pass the query as a parameter of the executaeQuery when it is called by Preparestatement. In your preparestatement you have already informed select, do not need to pass again.

[Edited]
I found the documentation where you talk about it: https://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#executeQuery(java.lang.String)

Answer 2

select s.* from sistema as <--- you need to put the same reference where you put it ( s.* ) Review the SQL spaces.

String sql = "select s.* from sistema as s\r\n" + 
        "inner join usuariosistema as u on (u.idsistema = s.idsistema)\r\n" + 
        "where u.usuario_cpf = @cpf";

Try to do separately that will help you much used

StringBuilder sql = new StringBuilder(); 
sql.append(" SELECT * FROM TABELA "):
sql.append(" WHERE ID = ?");

sql.toString;