Doubt in database query, error in query

Asked

Viewed 369 times

3

I am doing a work on Java Web ticket sales system and I have a question in my code to bring this data example: In select I consult 3 fields of my table: origin, destination and date the problem that is not bringing any data, has been tested directly at the bank and I function.

Practical Example:

  • RIO DE JANEIRO - SÃO PAULO - 08/20/2015
  • RIO DE JANEIRO - SÃO PAULO - 23/08/2015
  • RIO DE JANEIRO - SANTA CATARINA - 20/08/2015

I consult this data by a form and then play in a table. SQLFIDLE of my table: http://sqlfiddle.com/#! 9/478f3/1

Consultation:

public ArrayList<Passagem> consultar(Passagem p) {
    try {
        Connection conexao = getConexao();
        PreparedStatement pstm = conexao.prepareStatement(
            "Select * from passagem where " +
            "origem = ? " +
            "AND destino = ? " +
            "AND data = ?");

        pstm.setString(1, p.getOrigem());
        pstm.setString(2, p.getDestino());
        pstm.setString(3, p.getData());

        ResultSet rs = pstm.executeQuery();

        ArrayList<Passagem> listaPassagens = new ArrayList<Passagem>();

        while (rs.next()) {
            p.setIdpassagem(rs.getInt("idpassagem"));
            p.setOrigem(rs.getString("origem"));
            p.setDestino(rs.getString("destino"));
            p.setData(rs.getString("data")); 
            p.setPartida(rs.getString("partida"));
            p.setChegada(rs.getString("chegada"));
            p.setValor(rs.getInt("valor"));                 

            listaPassagens.add(p);
        }

        pstm.close();
        conexao.close();
    } catch (Exception e) {
        e.printStackTrace();
    }

    return listaPassagens;
}

Table of the Database:

CREATE TABLE passagem(
idpassagem NUMBER(5,0),
origem VARCHAR2(50),
destino VARCHAR2(50),
data VARCHAR2(255),
partida VARCHAR2(255),
chegada VARCHAR2(255),
valor NUMBER(8,2),
PRIMARY KEY(idpassagem)
);

Form I search the data:

<form action="passagem.jsp" method="post">  
        Cidade Origem:
        <input type="text" name="origem">
        <br>
        <br>
        Cidade Destino:
        <input type="text" name="destino">
        <br>
        <br>
        Data:
        <input type="text" id="datepicker" name="data" > 
        <br>
        <br>
        <input type="submit" value="busca"/>
    </form>

Where I receive the data and show in a table:

<body>
    <form method="post" action="AdicionarCarrinho.jsp"> 
    <table border="1">
        <th>Selecione Passagem</th>
        <th>Cidade Origem</th>
        <th>Cidade Destino</th>
        <th>Data</th>
        <th>Partida</th>
        <th>Chegada</th>
        <th>Preço(R$)</th>
        <th>Id Onibus</th>
        <th>Quantidade</th>
        <%
         PassagemDAO pdao = new PassagemDAO();
         Passagem p = new Passagem();

         if(request.getParameter("origem") != null && request.getParameter("destino") != null 
                 && request.getParameter("data") != null){

            p.setOrigem(request.getParameter("origem"));  

            p.setDestino(request.getParameter("destino"));

            p.setData(request.getParameter("data"));   


            ArrayList<Passagem> lista = pdao.consultar(p);

         for(Passagem p2 : lista){
        %>      


              <tr>
                <td align="center">  <input type="checkbox" name="comprar_ <%= p2.getIdpassagem() %>" value="Sim"> </td>
                <td> <%= p2.getOrigem() %> </td>
                <td> <%= p2.getDestino() %> </td>
                <td> <%= p2.getData() %>" </td>
                <td> <%= p2.getPartida() %> </td>
                <td> <%= p2.getChegada() %> </td>                  
                <td> <%= p2.getValor() %></td>
                <td> <%= p2.getIdonibus() %> </td>
                <td align="center">  <input type="text" name="quantidade_<%= p2.getIdpassagem() %>" size="1" maxlength="3" value="1"> </td>

            </tr>

        <%  
          }  
         }
        %>
        <p> <input type="submit" value="Adicionar ao Carrinho" name="Submit">
            <input type="reset" name="Limpar" value="Limpar"> </p> 
    </table>
    </form>
</body>

The problem now that it does not return any data as it was said, is giving this error:

Warning: The web application [/highway] Registered the JDBC driver [oracle.jdbc.driver.Oracledriver] but failed to unregister it when the web application was stopped. To Prevent a memory Leak, the JDBC Driver has been forcibly unregistered.

I am using netbeans with Glafissh server and Oracle database. In detail of the date is in String, when I refer use the form up there that step a type="text" use the datepicker of Jquery, making the separate queries works normal, the problem is to do together: returns blank and in the glafish log of the above error, if anyone has any solution to this problem.

html java-ee jsp

  • did not understand the question... what you want to return from the query ?

  • I couldn’t understand anything, what you want her to query return?

  • I have a form, I do the search for Origin City, Destination City and Data, my query makes the search, however it is bringing the independent data, needed to bring the researched specific data.

  • What do you mean by dice independent?

  • Let me get this straight, you want to type in yours form one city of origin, one destination city and a date and bring only one of the records you have in your database?

  • rephrase the question

  • Even the example up there, I researched a ticket from São Paulo to Rio de Janeiro with date 20/08/15, right? , would have to bring only this data, but is bringing others, which has as its origin city 'São Paulo', etc..

  • Yes I have a database table with this data, need to consult and shows.

  • @Rafilds, edit the question and put these details that you spoke in the comments, otherwise all information is dispersed.

  • @Rafilds, is returning some error or simply does not bring any results?

  • has already been edited up there, is returning 1 error and the blank query.

Show 6 more comments

4 answers

1

Jéferson Bueno’s answer is correct!

You wish to find the records that

origin = 'ORIGIN' And (at the same time) destination = 'DESTINATION' And (at the same time) date = 'DATE'

So you should use the operator AND

The problem is you’re doing pstm.setString(3, p.getData()). Are you trying to make setString passing a date:

THAT DOESN’T WORK!

To solve the problem, you can try to convert your date to String as follows:

SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy"); 
// aqui use o formato de data compatível com o que está no banco,  
//conforme bem lembrado pelo @JonasCruvinel
String strData = formatter.format(p.getData());
...
pstmt.setString(3, strData);

Or else change pstm.setString(3, p.getData()) for pstm.setDate(3, p.getData()).

If it still doesn’t work, the problem is that the Timezone your computer (or what Java is using) is different from the database

  • then more face in my table as it was said, Date is as String and already step in the right format, and already get in the right format using type="text" form, use datepicker of jquery.

  • your.getData ( ) pass is a string ?

  • yes, I even have another project using date as Date, but this with the same problem as this, I do not believe that is the date problem.

  • then your problem is now with the database driver.. the query is correct! I think you should open another topic reporting the error with the driver

  • yes, the topic has been opened here: http://answall.com/questions/81926/erro-registered-the-jdbc-driver-oracle-jdbc-driver-oracledriver-but-failed-to, thank you, I am trying to resolve, any news I warn.

0


I was able to solve the problem, thank you all for your attention and try to help me, problem very Noob, the error was in Insert, I do not know why more is not accepting to search Origin and Destination with accent, example: â,é,í,ô, I made new Serts without accent and it worked normal.

Falto put in JSP:

<%@page language="java" contentType="text/html; charset=ISO-8859-1"%>

html  
<meta http-equiv="content-type" content="text/html; charset=iso-8859-1"/>

0

If I understand your doubt, you just need to use AND instead of OR on your clavicle where.

Ex.:

PreparedStatement pstm = conexao.prepareStatement(
"SELECT * FROM passagem WHERE " +
"origem = ? " +
"AND destino = ? " +
"AND data = ?");

  • i did it but no return data, which may be the problem?

  • The combination you’re looking for exists in the bank?

  • yes, I put my table up there.

  • Dude, that makes no difference. What matters is data from your table, make a sqlfiddle so I can see the data.

  • my sqlfidle to better understand: http://sqlfiddle.com/#! 9/478f3/1

  • What kind of return Passagem.getData()?

  • I just put the code up there, to better understand

  • returns the date I typed, I did test 1 by 1 separate, problem was when do query together do not know why, I’m trying to understand.

Show 3 more comments

0

Friend first change the OR to AND

Preparedstatement pstm = connection.prepareStatement( "SELECT * FROM passage WHERE " + "origin = ? " + "AND destination = ? " + "AND DATE = ?");

Just like Jeferson said. And as Pedro quoted.

The sql database works with date in American format. YYYY-MM-DD

You are probably typing in Brazilian DD/MM/YYYY format

Save the value entered by the user in a string and convert the string to the American standard before performing the query as I did on a website of mine.

data = Year(Txtdatasynchronization.Text) & "-" & Month(Txtdatasynchronization.Text) & "-" & Day(Txtdatasynchronization.Text)

  • well remembered... I added in my reply an observation on this

  • I did the test in the database itself, sql is correct, the date I pass as string and in the form I am using jquery datepicker, when I put AND, the following error appears in the netbeans exit, using glafish as server and oracle as database: Warning: The web application [/highway] Registered the JDBC driver [oracle.jdbc.driver.Oracledriver] but failed to unregister it when the web application was stopped. To Prevent a memory Leak, the JDBC Driver has been forcibly unregistered.

  • my date in the database as it is in the table above, to using String, and I have already done the Insert in this format: ** INSERT INTO passage(idpassagem, origem, destino, data, partida, chegada, valor, idonibus) VALUES(1, 'São Paulo', 'Rio de Janeiro, '05/10/2015', '11:00', '12:00', 15, 1); **

  • Have you checked the value that the datapicker returns when you select a date? publishes the sql query you are using

  • yes, returns the normal date, just noticed that it has times that returns the date = 08/20/2015" <--- with 1 quotes, already published.

Browser other questions tagged html java-ee jsp

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