Data problem in mysql and java

Asked

Viewed 83 times

1

I’m trying to use the date of a jYearChoser and a jMonthChoser to search for a full month sale, already tested at the Mysql prompt and worked perfectly with the command below:

SELECT FORMAT(SUM(vendas.total_pagar),2), FORMAT(SUM(desconto_venda),2), FORMAT(SUM(orcamentos.valor_orcamento),2), FORMAT(AVG(valor_venda),2), COUNT(vendas.id), COUNT(DISTINCT(vendas.id_cliente))
FROM genius.vendas
LEFT JOIN genius.orcamentos ON genius.orcamentos.id = id_orcamento
WHERE EXTRACT(YEAR_MONTH FROM `data_venda`) = (EXTRACT(YEAR_MONTH FROM '2016-08-01' - INTERVAL 0 MONTH)) AND vendas.Id_funcionario = 5;

But when I try to use in Java the way below it works, but the account goes wrong:

//abaixo preenche o painel cálculo de pagamento
    sql = "SELECT SUM(vendas.total_pagar), FORMAT(SUM(desconto_venda),2), FORMAT(SUM(orcamentos.valor_orcamento),2), FORMAT(AVG(valor_venda),2), COUNT(vendas.id), COUNT(DISTINCT(vendas.id_cliente))\n" +
                 "FROM genius.vendas\n" +
                 "LEFT JOIN genius.orcamentos ON genius.orcamentos.id = id_orcamento\n" +
                 "WHERE EXTRACT(YEAR_MONTH FROM `data_venda`) = (EXTRACT(YEAR_MONTH FROM ?'-1' - INTERVAL 0 MONTH)) AND vendas.Id_funcionario = ?";
    try {
        int setar = tblFuncionarios.getSelectedRow();
        String id = (tblFuncionarios.getModel().getValueAt(setar, 0).toString());
        pst = conexao.prepareStatement(sql);
        // passando o Id para o "?
        pst.setString(1,Integer.toString(jYearChooser1.getYear()) + "-" + Integer.toString(jMonthChooser1.getMonth()));
        pst.setString(2, id);
        rs = pst.executeQuery();
        if(rs.next()) {
            double vendas = (rs.getDouble(1));
            lblVendas.setText(Double.toString(vendas));
            lblDescontos.setText(rs.getString(2));
            lblOrcamentos.setText(rs.getString(3));
            lblVMC.setText(rs.getString(4));
            lblVendas_quant.setText(rs.getString(5));
            lblClientes.setText(rs.getString(6));

            if ("sim".equals(lblCG.getText())) { // a condição abaixo calcula a porcentagem para comissão gradativa
                double inicioCG = Double.parseDouble(lblCGInicio.getText());
                double intervaloCG = Double.parseDouble(lblCGIntervalo.getText());
                double maxPorcCG = Double.parseDouble(lblCGLimite.getText());
                int fator = (int) ((vendas - inicioCG) / intervaloCG + 1);
                lblCGPorc.setText(Integer.toString(fator));
            }
        }
    } catch (Exception e) {
        JOptionPane.showMessageDialog(null, e);
    }

Does anyone have any tips?

java mysql

  • irrelevant parts of the code have been omitted*

  • 1

    I wish I could replicate this problem. Can you edit your question to let a code that plays the problem and is also compileable and executable? Also, what is your connection string with Mysql?

  • Please check the value of jYearChooser1.getYear(), the value of jMonthChooser1.getMonth(), the exit of query in Java and output directly in SQL.

  • jYearChooser1.getYear() is "2016" and jMonthChooser1.getMonth() is "08"

1 answer

1

I managed to fix the code, I realized I was calculating the value of the previous month, I solved the problem by adding 1 in jMonthChooser1.getMonth(), the problem was that the first month of the Calendar class starts at 0, already in Mysql the first month starts at 1, so I was giving difference

Browser other questions tagged java mysql

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