Home Sending a sql query from one jsp file to another
Reply: 0

Sending a sql query from one jsp file to another

noob
1#
noob Published in 2017-12-07 07:40:10Z

I am a student who is new to programming in JS and HTML. I am trying to create a table where the rows are queries featuring inputs that allow the user to choose the parameters for each query. The problem is, I have no idea how to transfer this data to another jsp file, which would ideally display the results of the query. I envisioned that I could construct the query in the current jsp file after the "submit" button is pressed, and pass it to the second jsp. Here is my code:

<%@page language="java" import="java.sql.*,java.util.*"%>

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body>

<%
    Driver DriverRecordset1 = (Driver) Class.forName("org.postgresql.Driver").newInstance();
    Properties props = new Properties();
    props.setProperty("user", "postgres");
    props.setProperty("password", "123456");
    String url = "jdbc:postgresql://localhost/projectdb";
    Connection Conn = DriverManager.getConnection(url, props);

    //Create a dropdown menu of all restaurants
    Statement st = Conn.createStatement();
    ResultSet rs = st.executeQuery("select res_name from Restaurant"); 
    HttpSession sess = request.getSession(true);
    String query = "select * from restaurant";
    sess.setAttribute("query", query);
%>


    <div align="center">
        <table border="1" cellpadding="5">
            <caption><h2>Queries</h2></caption>


              <tr>
                <td>Find restaurants that have a review with taste, delivery, and customer service ratings greater than
                    <form name="f0" action="server.jsp" >
                    <select>
                    <%  for(int i = 0; i < 5; i++){ %>
                        <option><%= i%></option>
                    <% } %>
                    </select>

                    <input type="submit" name="submit" value="submit">
                    </form>
                </td>
              </tr>

              <tr>
                <td>Find restaurants with more than 
                <form name="f1" action="server.jsp" >
                    <select>
                    <%  rs = st.executeQuery("select max(x.num) as y from (select count(*) as num from review group by res_id) as x"); 
                        rs.next();
                        for(int i = 0; i <= rs.getInt("y"); i++){ %>
                        <option><%= i%></option>
                    <% } %>
                    </select>
                    reviews

                    <input type="submit" name = "submit" value="submit">
                </form>
                </td>
              </tr>

              <tr>
                <td>Find users who've spent more than 
                <form name="f2" action="server.jsp" >
                    <input type="text" name="dollars" min=0 size=4>
                    dollars on a single order.

                    <input type="submit" name = "submit" value="submit">
                </form>
                </td>
              </tr>

              <tr>
                <td>Find customers who have eaten in at least 
                <form name="f3" action="server.jsp" >
                    <select>
                    <%  rs = st.executeQuery("select count(*) as y from restaurant"); 
                        rs.next();
                        for(int i = 0; i <= rs.getInt("y"); i++){ %>
                        <option><%= i%></option>
                    <% } %>
                    </select>
                    restaurants

                    <input type="submit" name = "submit" value="submit">
                </form>
                </td>
              </tr>

              <tr>
                <td>Find customers who have only eaten at restaurants that serve
                <form name="f4" action="server.jsp" >
                    <select>
                    <%  rs = st.executeQuery("select distinct dish_name as y from dish"); 
                        rs.next();
                        while (rs.next()){ %>
                        <option><%= rs.getString("y")%></option>
                    <% } %>
                    </select>

                    <input type="submit" name = "submit" value="submit">
                </form>
                </td>
              </tr>

              <tr>
                <td>Find dishes that cost less than
                <form name="f5" action="server.jsp" >
                        <input type="text" name="dollars" min=0 size=4>
                    dollars

                    <input type="submit" name = "submit" value="submit">
                </form>
                </td>
              </tr>

              <tr>
                <td>Find all customers who've ordered
                <form name="f6" action="server.jsp" >
                    <select>
                    <%  rs = st.executeQuery("select distinct dish_name as y from dish"); 
                        rs.next();
                        while (rs.next()){ %>
                        <option><%= rs.getString("y")%></option>
                    <% } %>
                    </select>
                    in every order.

                    <input type="submit" name = "submit" value="submit">
                </form>
                </td>
              </tr>

              <tr>
                <td>Find dishes with a wait time of less than
                <form name="f7" action="server.jsp" >
                    <input type="text" name="dollars" min=0 size=4>
                    minutes

                    <input type="submit" name = "submit" value="submit">
                </form>
                </td>
              </tr>

              <tr>
                <td>Find customers who did not get discounts in all orders made between
                <form name="f8" action="server.jsp" >

                    <input type="date" name="day1">
                    and
                    <input type="date" name="day2">

                    <input type="submit" name="submit" value="submit" onclick="myFunction()">
                </form>
                </td>
              </tr>

        </table>

    </div>
</body>
</html>

Could someone recommend a method for constructing and executing the query?

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.332874 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO