0

I'm new to jsp. Here's what I'm trying to do: ->take a keyword input from user(From a jsp form) ->search for tuples from a particular relation in a postgres relational database containing given keyword in any column.

I'm using JDBC for this. I thought everything is going fine until I discovered that some tuples having the given keyword are not being displayed.

About the database: Database name is 'university'. I have a relation in my database 'course' having parameters (course_id, title, dept_name, credits)

Here is the code for the jsp page where I'll take input keyword from user:

      <html>
     <head>
     <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
     <title>search courses</title>
      </head>
       <body>
      <h1>Search for courses</h1>
      <FORM ACTION="coursesearch.jsp" METHOD="POST">
        Type the keyword to search in course
        <br> <input type="text" name="keyword"   />
         <br> <INPUT TYPE="SUBMIT" value="Submit">
      </FORM>
    </body>
   </html>

and here is the code of 'coursesearch.jsp' which displays the rows from course having given keyword:

<%@ page import="java.sql.*" %>
<% Class.forName("org.postgresql.Driver"); %>
<html>
    <head>
        <title>Question2</title>
    </head>
    <body>
        <h1>Courses Containing the given keyword : </h1>
        <%
            Connection connection = DriverManager.getConnection(
                    "jdbc:postgresql://localhost:5432/university","postgres","password");

            Statement statement1 = connection.createStatement();

            String keyword = request.getParameter("keyword");
            String sql = "select course_id,title from course where LOWER(title)  LIKE LOWER(?)";
          PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, "%" + keyword + "%");
            ResultSet resultset1 = preparedStatement.executeQuery();
        if(!resultset1.next()){
            out.println("Sorry no course found having given keyword");
        }
        else {

        %>
        <p>
            Course IDs containing the given keyword 
        </p>
        <table border="1">

                <tr>
                    <th>Course ID</th>
                    <th>Course title </th>

                    <% while(resultset1.next()){ %>

                    <tr>
                       <td> <%=resultset1.getString(1)%> </td>
                         <td> <%=resultset1.getString(2)%> </td>
                    </tr>
                <% 
                resultset1.next();
                    } %>
            </table>
            <br>
              <% } 
            connection.close();
              %>
        </body>
     </html>

Everything looks fine to me, but the table doesn't display all valid tuples. Seriously, I need help from you guys. (sorry for my bad English).

Luiggi Mendoza
  • 85,076
  • 16
  • 154
  • 332
Prathmesh Deshmukh
  • 259
  • 3
  • 14

2 Answers2

1

You're consuming rows from the ResultSet without noticing.

Here:

if(!resultset1.next()){
//here you move from row 0 to row 1

Then here:

while(resultset1.next()){

And at last, here:

resultset1.next();

Basically, your code looks like this:

if(!resultset1.next()) {
    //advanced to the first row, but not consumed
    while(resultset1.next()){
        //advanced to the following row
        //consumed this one...

        resultset1.next();
        //yet again advancing to the next row
        //and not consuming it
    }
}

Related: ResultSet.getString(1) throws java.sql.SQLException: Invalid operation at current cursor position

Last but not least, do yourself a favor and avoid using scriptlets.

Community
  • 1
  • 1
Luiggi Mendoza
  • 85,076
  • 16
  • 154
  • 332
-1
    <%

        try{
        ResultSet rs = Database.getData("select * from event");
        while(rs.next()){
    %>      

<tr>
    <td><%= rs.getString("id")  %></td>


    <td><%= rs.getString("time") %></td>

    <td><%= rs.getString("event") %></td>

    <td><a href="#" class="btn btn-xs btn-warning"><span class="glyphicon glyphicon-trash"></span> Remove</a></td>


</tr>
RZK Technology
  • 1
  • 1