Search query in java and jsp not working

Question

I'm trying to get an input box in a jsp file to search for a specific author and display the results from my db as a table.

I've used this code to list everything from my database to display it on a jsp. But now I want to only display every result that shares the same author name.

SearchQuery.java

    package dbhelpers;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import model.Book;

public class SearchQuery {
    private Connection connection = null;
    private ResultSet results;

    public SearchQuery(String dbName, String uname, String pwd) {

        String url = "jdbc:mysql://localhost:3306/" + dbName;

        //setting up driver

        try {

            Class.forName("com.mysql.jdbc.Driver").newInstance();
            this.connection = DriverManager.getConnection(url, uname, pwd);

        } catch (InstantiationException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

}
public void doRead(){

        String query = "select title, author, pages from books where author =?";

        try {

            PreparedStatement ps = this.connection.prepareStatement(query);
            this.results = ps.executeQuery();

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

    }

    public String getHTMLTable(){
    String table ="";

    table += "<table border =1>";

    try {
        while(this.results.next()) {
            Book book = new Book();
            book.setBookID(this.results.getInt("bookID"));
            book.setTitle(this.results.getString("title"));
            book.setAuthor(this.results.getString("author"));
            book.setPages(this.results.getInt("pages"));

            table += "<tr>";
            table += "<td>";
            table +=    book.getTitle();
            table += "</td>";

            table += "<td>";
            table +=    book.getAuthor();
            table += "</td>";

            table += "<td>";
            table +=    book.getPages();
            table += "</td>";

            table += "<td>";
                table +="<a href = update?bookID=" +  book.getBookID() +">update</a> <a href = delete?bookID="+ book.getBookID() + ">delete</a>";

            table += "</td>";

            table += "</tr>";
        }
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

    table += "</table>";

    return table;



}

}

SearchServlet.java

    package controller;

import java.io.IOException;


import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import dbhelpers.SearchQuery;

/**
 * Servlet implementation class SearchServlet
 */
public class SearchServlet extends HttpServlet {
    private static final long serialVersionUID = 1L;

    /**
     * @see HttpServlet#HttpServlet()
     */
    public SearchServlet() {
        super();
        // TODO Auto-generated constructor stub
    }

    /**
     * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
     */
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        doPost(request, response);
    }

    /**
     * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
     */
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        SearchQuery sq = new SearchQuery("book_lib","root", "");

        //getting html table from read query object

        sq.doRead();
        String table;

            table = sq.getHTMLTable();


        request.setAttribute("table", table);
        String url ="/search.jsp";

        RequestDispatcher dispatcher = request.getRequestDispatcher(url);
        dispatcher.forward(request,response);
    }

}

I get the error:

java.lang.NullPointerException dbhelpers.SearchQuery.getHTMLTable(SearchQuery.java:65) controller.SearchServlet.doPost(SearchServlet.java:46) javax.servlet.http.HttpServlet.service(HttpServlet.java:646) javax.servlet.http.HttpServlet.service(HttpServlet.java:727) org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)

Any suggestions

what line at SearchQuery.java:65? – Braj Jul 24 '14 at 15:10 — Braj, Jul 24 '14 at 15:10

score 0 · Answer 1 · edited May 23 '17 at 11:50

Don't directly write HTML response in Servlet instead use a separate JSP file and pass List of Book as request attribute to display it in JSP.

Use JSTL Core c:forEach, c:forTokens Tag that is s a good alternative to embedding Java's for, while, or do-while loop.

Sample code: (change it as per your need)

Servlet:

List<Book> list = new ArrayList<Book>();
// populate list with the results got from database

while(this.results.next()) {
    Book book = new Book();
    book.setBookID(this.results.getInt("bookID"));
    book.setTitle(this.results.getString("title"));
    book.setAuthor(this.results.getString("author"));
    book.setPages(this.results.getInt("pages"));

    list.add(book);
}
// set the List of Book as request attribute
request.setAttribute("list",list);

// forward the request to the JSP page
RequestDispatcher view = request.getRequestDispatcher("view.jsp");
view.forward(request, response);

view:jsp:

<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql"%>

<table border="1">
    <c:forEach var="row" items="${list}">
        <tr>
            <td><c:out value="${row.bookID}"/></td>
            <td><c:out value="${row.title}"/></td>
            <td><c:out value="${row.author}"/></td>
            <td><c:out value="${row.pages}"/></td>
            <td><a href ="update?bookID=${row.bookID}">update</a></td>
            <td><a href ="delete?bookID=${row.bookID}">delete</a></td>
        </tr>
    </c:forEach>
</table>

Search query in java and jsp not working

2 Answers2