Processing SQL statements with JDBC

Question

Processing SQL statements with JDBC

Asked 8 years ago

Viewed 128 times

3

I am trying to use JDBC, but having difficulties with the proper use of language. I would like to move the sql code down to java, and I need help with SELECT.

Regarding the variables sql1, sql2, sql3 and the use of the respective Where...(for me they are wrong). I did it this way having the clear impression that it is wrong, but I put them there to show what I intended with my code.

select * 
from voo, voo_comissarios 
where voo.voo_id=voo_comissarios.voo_id and 
voo_comissarios.com_cpf=580069359 and 
voo.voo_data between '1000-1-1' and '2019-1-1';

static ResultSet vooComissario() {
        ResultSet res = null;
        String sql = "SELECT * FROM voo",
               sql1 = sql+  "WHERE voo.voo_data > ?"
               sql2 = sql1+ "WHERE voo.voo_data < ?"
               sql3 = sql2+ "WHERE voo.voo_id=voo_comissarios.voo_id";
               sql4 = sql3+ "WHERE voo_comissarios.com_cpf= ?"

        Connect();
        try {
            PreparedStatement stm;
                stm = con.prepareStatement(sql4);
                stm.setInt(1, serial);
                res = stm.executeQuery();
        } catch (SQLException e) {
            JOptionPane.showMessageDialog(null, "Não foi possível recuperar os dados", "Erro", JOptionPane.OK_OPTION);
        }
        CloseConnection();
        return res;
    }
}

And what is the doubt?

– user28595

2016/11/19 at 00:01
As for the use of sql1, sql2, sql3 and the use of the respective wheres...(for me they are wrong) .

– Marcos Pinheiro Moura

2016/11/19 at 00:10
Which database are you using? What are these variables sql1, 2 and 3? What do you want to compare? I honestly don’t understand anything this query does.

– user28595

2016/11/19 at 00:36
Next, I’m using the various sql, to try to replicate the query I did in sql (above the java code), as I don’t understand of jdbc was in this kind of strange code that I arrived. I’m trying to make the Where constraints line by line, with each sql variable getting the previous query and restricting.

– Marcos Pinheiro Moura

2016/11/19 at 00:46
See the answer, with what was presented, is what I understood.

– user28595

2016/11/19 at 00:48
1

Do you really need all the data from both tables? Do you have a flight class or flight? Do you want the parameters to be optional or may vary? What database?

– Sorack

2016/11/19 at 00:55

Show 1 more comment

1 answer

Browser other questions tagged java jdbc

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

by user28595 · Answer 1 · 2016-11-19T00:46:54+00:00

You did not explain the problem well, but from what you could understand, if it is to adapt the query within the method, I believe it should stay like this:

static ResultSet vooComissario() {
        ResultSet res = null;
        String sql = "select * from voo, voo_comissarios " +
                      "where voo.voo_id = voo_comissarios.voo_id and " + 
                      "voo_comissarios.com_cpf = ? and " +
                      "voo.voo_data between ? and ?";

        Connect();
        try {
                PreparedStatement stm = con.prepareStatement(sql);;

                //se for usar números em cpf, 
                //utilize o tipo LONG no lugar de INT
                stm.setLong(1 , variavelCpf);
                //passe as datas no formato java.sql.Date
                setm.setDate(2, dataInicial);
                setm.setDate(3, dataFinal);

                res = stm.executeQuery();
        } catch (SQLException e) {
            JOptionPane.showMessageDialog(null, "Não foi possível recuperar os dados", "Erro", JOptionPane.OK_OPTION);
        }
        CloseConnection();
        return res;
    }
}