Search by date range

Asked

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.

  • 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?

  • User has to enter Date 1 and Date 2 so I can have the request number of the bank that corresponds to that period

  • 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.

  • 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?

1 answer

3

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.

  • 1

    Not use Date as field name for being reserved word is not useful advice. You can use this in several banks (Mysql, Oracle, MS SQL Server, Firebird...) without any problem.

  • 1

    @Caffé Mantenho. I have had a lot of headache with this and I would not like others to suffer what I have suffered because of it. In addition, the OP did not mention what his database is and whether there is a possibility of migration.

  • 2

    With which database did you have a headache? It might be useful if you inform the database and the type of problem encountered.

  • @Caffé https://www.drupal.org/node/141051 - In SQL standard 1999, DATA is a reserved word. At the time I had problems, in 2006, I was working with Oracle, Access, Mysql and Postgresql. It was a tool for multiple databases and had one of them that gave error.

  • @Caffé I took from the text the question of the keyword Data.

  • @Victor brother I still can’t understand this procedure. I’m going to re-edit and show you what my doubt is. Now I wanted to search by date and get the order number for this search.

  • Thanks @Fernando

Show 2 more comments

Browser other questions tagged

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