Search by date range

0

HowtocreateaqueryfortheOracledatabasebyreturningalistofrequestsaccordingtoadaterange?

Followthecodecurrentlyused:

if(opcaoBusca.getSelectedIndex()==0){JOptionPane.showMessageDialog(null,"Escolha uma Opção de Busca!");

        } else if (opcaoBusca.getSelectedIndex() == 1) {
            RS = stmt.executeQuery("select numped FROM PCPEDC WHERE DATA > '10/01/2014' and numped =  " + BuscaCodigo);
            while (RS.next()) {


                int Num = RS.getInt("numped");

                consulta = false;
                JOptionPane.showMessageDialog(null, "Dados Encontrado!!!!");
            }

        } else if (opcaoBusca.getSelectedIndex() == 2) {
            RS = stmt.executeQuery("SELECT Data FROM PCPEDC WHERE Data BETWEEN " + Dtincial + "AND" + Dtfinal);


            while (RS.next()) {


                int Num = RS.getInt("numped");
    
asked by anonymous 12.01.2015 / 18:44

1 answer

3

The first thing that struck me was your query:

"SELECT Data FROM PCPEDC WHERE DATA BETWEEN '10/09/2010'" + DataIni +  "'AND '" + DataFim +"'"

So, let's assume that the start date is 01/01/2015 and the end 12/01/2015, your query looks like this:

SELECT Data FROM PCPEDC WHERE DATA BETWEEN '10/09/2010'01/01/2015'AND '12/01/2015'

What is not a well-formed query!

With this in mind, let's assume that I'll sort the query. All you will need is ResultSet read field:

Date d = RS.getDate("Data");

Finally, do not concatenate String s to mount your query. This practice is dangerous and enables a very dangerous security flaw known as SQL Injection. Instead, use parameters in your PreparedStatement :

PreparedStatement ps = connection.prepareStatement("SELECT Data FROM PCPEDC WHERE Data BETWEEN ? AND ?");
ps.setDate(1, DataIni);
ps.setDate(2, DataFim);
ResultSet rs = ps.executeQuery();

Finally, be sure to close PreparedStatement and ResultSet within a block finally .

    
12.01.2015 / 19:04