Search by date does not work

Asked

Viewed 112 times

-1

I am trying to do a search in the mysql database doing a filter by date and is not bringing any results. On the screen below I was able to search for the other criteria

inserir a descrição da imagem aqui

This is the code that’s doing the searches.

public void montaTabelaBusca() {

    String criterio = " WHERE CLIENTE LIKE '" + inpClienteBusca.getText() + "%'"
            + " AND CARRO LIKE '" + inpCarroBusca.getText() + "%'"
            + " AND DESPESA LIKE '" + inpDespesaBusca.getText() + "%'"
            + " AND DATA LIKE '" + inpDataInicialBusca.getText() + "%'";

    ArrayList<Venda> Vendas = VendaDao.getResultadoDaVenda(criterio);
    String[] cabecalhoColunas = {"Id", "Data", "Cliente", "Carro", "Valor","Observação", "Despesa","Valor da despesa"};
    modeloTabela = new DefaultTableModel(cabecalhoColunas, 0);

    for (Venda v : Vendas) {
        String[] novaLinha = {String.valueOf(v.getId()),Formatar("dd/MM/yyyy", v.getData()),String.valueOf(v.getCliente()),String.valueOf(v.getCarro()), Numeros.Formatar("#0.00", v.getValor()),String.valueOf(v.getObservacao()),String.valueOf(v.getDespesa()),Numeros.Formatar("#0.00", v.getValorDespesa())};
        modeloTabela.addRow(novaLinha);
        jtResultadoDaBusca.setModel(modeloTabela);
    }
}

//getResulted

 public static ArrayList<Venda> getResultadoDaVenda(String condicao) {
    ResultSet resultado = ObjectFactory.getConexao().buscaSql("SELECT * FROM VENDA " + condicao);

    try {
        ArrayList<Venda> listagem = new ArrayList<>();
        while (resultado.next()) {
            Venda vendaPreenchida = preencheVenda(resultado);
            listagem.add(vendaPreenchida);
        }
        return listagem;
    } catch (SQLException ex) {
        System.out.println("não eoncontrado!");
    }
    return null;
}
java mysql date

  • The result of this search is just a sale? If not, you cannot apply the model within the loop, otherwise nothing will result. Another thing, you say, "it doesn’t work", please, when drawing up a question, be more specific in the problem.

  • Yes, the search result is a listing of that date. Thanks for the tip !

  • How is this method getResultadoDaVenda? Why don’t you pass the parameters of the fields to him, instead of the query? That violates the responsibility of each class.

  • Yeah. I was trying, but it didn’t work

  • Dude, don’t add big chunk of code in comment, see how it looks, you can’t read it like that.

  • I’ll edit the question and put the code there

Show 1 more comment

1 answer

1


From what I understand, your problem is with the date format. The default format that Mysql uses to save date (DATE) is yyyy-mm-dd, that is, if you do the search by playing directly in the query what is written in the search field, it will search for something like "13/04/2017" which is stored in the bank as "2017-04-13". If you want to do only full-date searches, containing day, month and year, you can use the function STR_TO_DATE Mysql to transform the search string into DATE query:

"AND DATA = STR_TO_DATE(" + inpDataInicialBusca.getText() + ", '%d/%m/%Y')"

Now, if you want to use the LIKE to search for string’s of incomplete dates, you can use the function DATE_FORMAT Mysql to format the "DATA" column to suit the format it is using in the query, thus:

"AND DATE_FORMAT(DATA, '%d/%m/%Y') LIKE '" + inpDataInicialBusca.getText() + "%'"

Browser other questions tagged java mysql date

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