Doubt in query database, error in query

3

I'm doing a work on Java Web ticket sales system and I'm having a question in my code to bring this example data: No select query 3 fields from my table: source, destination and date the problem that is not bringing any data, was tested directly in the bank and worked.

Practical Example:

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

I query these data for a form and then play on a table. SQLFIDLE from my table: link

Inquiry:

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;
}

Database table:

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 that I look for 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 do I get the data and shows it 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 [/ road] 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'm using netbeans with the Glafissh server and Oracle Database. In detail of the date is in String, when I query I use the form there above that I pass a type="text" I use the Jquery datepicker, making the separate queries work normal, the problem is to do together: returns blank and the glafish log gives the error above, if anyone has any solutions to this problem.

    
asked by anonymous 20.08.2015 / 21:13

4 answers

0

I was able to solve the problem, thank you all for the attention and try to help me, problem very noob, the error was in the Insert, I do not know why else is not accepting search Origin and Destination with accentuation, eg: , í, ô, I made new inserts without stress and it worked normal.

Failed to 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"/>
    
22.08.2015 / 03:28
1

Jéferson Bueno's answer is correct!

You want to find the records that

E (at the same time) DATE = 'DATA'

So you should use the AND

The problem is that you are doing pstm.setString(3, p.getData()) . You are trying to make setString by passing a date :

THIS DOES NOT WORK!

To solve the problem, you can try converting 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()) to pstm.setDate(3, p.getData()) .

If it still does not work, the problem is that Timezone of your computer (or what Java is using) is different from the database

    
20.08.2015 / 21:31
0

Friend first change the OR to AND

PreparedStatement pstm = connection.prepareStatement ( "SELECT * FROM WHERE Pass" + "origin =?" + "AND destination =?" + "AND data =?");

As Jepherson quoted. And as Peter quoted.

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

You're probably typing in the Brazilian DD / MM / YYYY format

Save the value entered by the user into a string and convert the string to the American standard before performing the query as I did on a my site.

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

    
21.08.2015 / 14:05
0

If I understand your question, you only need to use AND instead of OR in your where clause.

Ex:

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