Query MySql confusing results

Question

Query MySql confusing results

Navigation

#1 by (2 votes)

1

I have to do a mysql query through a Java method. This is the sql I did to test if it works:

select * from servicos S, clientes C, motores M where C.nome = "Claudio Fernando Pires" group by ordemServico

Theoretically it should bring only the services whose client was "Claudio Fernando Pires" right? Correct me if you're wrong.

But it brings all the services, even if it is a golden client like "Claudio Fernando Pires", the same happens with any other parameter of the client or engine.

This is my method in Java:

public List<Servico> pesquisarServicos(String nomeCliente, String endereco, String marcaMotor, String modeloMotor) {

    String sql = "SELECT * FROM clientes C, motores M, servicos S "
            + "WHERE  C.nome LIKE ? AND C.endereco  LIKE ? AND M.marcaMotor LIKE ? AND M.modeloMotor LIKE ?"
            + "GROUP BY ordemServico";

    ResultSet listaResultados = null;
    List<Servico> servicos = new ArrayList();


    try {
        PreparedStatement comando = BD.conection.prepareStatement(sql);
            comando.setString(1, nomeCliente+"%");
            comando.setString(2, "%"+endereco+"%");
            comando.setString(3, marcaMotor+"%");
            comando.setString(4, modeloMotor+"%");

        listaResultados = comando.executeQuery();
        Servico servico;

        while (listaResultados.next()) {
            servico = new Servico();
            servico.setCliente(ControleCliente.buscarCliente(listaResultados.getString("CPF")));
            servico.setDataServico(listaResultados.getString("dataServico"));
            servico.setDescricaoServico(listaResultados.getString("observacao"));
            servico.setFormaPagamento(Servico.getFormaPagamento(listaResultados.getInt("formaPagamento")));
            servico.setMotor(ControleMotor.buscarMotor(listaResultados.getString("numMotor")));
            servico.setOrdemServico(listaResultados.getString("ordemServico"));
            servico.setTipoSevico(Servico.getTipoServico(listaResultados.getInt("tipoServico")));
            servico.setValorServico(listaResultados.getDouble("valorServico"));
            servicos.add(servico);
        }
        listaResultados.close();
        comando.close();
        System.out.println("Lista Resultante="+servicos.size());
    } catch (SQLException excessaoSQL) {
        excessaoSQL.printStackTrace();
    }
    return servicos;
}

It shows the same problem in my view written in Java.

Look at the result by filling in the name filter:

IfIchangethefilterName: Anysuggestionsonwhat'sgoingonandhowtomakethesearchworkforbothcases?

ThesearetherecordsintheServicestable:

IshouldbringtherecordsfortheclienttowhichIgavesomeattribute:

In this case I used an attribute from the Services table itself, but that's the idea.

mysql java

asked by anonymous 25.10.2015 / 22:36

1 answer

How to open, via intent, the Google Maps app in certain Latitude / Longitude? Infinite loop with data stream in PHP

score 2 · Accepted Answer

I would use JOIN in this case, more reliable for what you are trying to do:

SELECT s.atributo_servico1, s.atributo_servico2 FROM servicos s
JOIN clientes c ON c.codigo = s.cliente
WHERE c.nome LIKE "Claudio Fernando Pires"
GROUP BY s.ordem_servico;

If you need a JOIN with another table to redeem the values of this other table just put below the client JOIN and so on.

Tips

For standardization in the database the fields must be ordem_servico instead of ordemServico . It's okay to declare a ordemServico variable in Java, but we can hardly see this in relational databases.

I also recommend instead of using * directly in SELECT you mention the fields of the c.codigo, c.nome_cliente tables and so on to give you greater control of the fields that will be returned.