Search by date range

Question

Search by date range

Asked 9 years, 5 months ago

Viewed 1,412 times

0

inserir a descrição da imagem aqui

How to create a query for the Oracle database by returning a list of orders according to a date range?

Follows the code currently used:

        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");

1

Could you please ask a little better? I don’t quite understand.

– PauloHDSousa

2015/01/12 at 17:46
1

Data is a reserved word in multiple SQL dialects. Never use this as a field name. Also, post more code details. How you declare variables?

– Victor Stafusa

2015/01/12 at 17:51
User has to enter Date 1 and Date 2 so I can have the request number of the bank that corresponds to that period

– Wesley Costa

2015/01/12 at 18:01
How do you declare variables? What is the type of DataIni, DataFim, RS and stmt? You use a try-catch somewhere? Give the code of a method that is compilable.

– Victor Stafusa

2015/01/12 at 18:03
That does not compile: int BuscaCodigo = Integer.parseInt(entBusca.getText()); ResultSet RS = null;
int BuscaCodigo = Integer.parseInt(DataIni.getText()); ResultSet RS = null;. Variables are duplicated. Also, because you are reading a date with parseInt?

– Victor Stafusa

2015/01/13 at 13:25

1 answer

Browser other questions tagged java sql

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

by Victor Stafusa • **63,338** points · Answer 1 · 2015-01-12T18:04:24+00:00

The first thing that popped out of my eyes was your query:

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

So, let’s assume that the initial date is 01/01/2015 and the final 12/01/2015, your query is 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!

Keeping this in mind, let’s assume I arrange the query. All you’ll need is with the ResultSet read the field:

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

Finally, do not concretely Strings to mount your query. This practice is dangerous and enables a very dangerous security breach 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, don’t forget to close the PreparedStatement and the ResultSet within a block finally.