java - java 和 jsp 中的搜索查询不起作用

Question

我正在尝试在 jsp 文件中获取一个输入框来搜索特定作者并将我的数据库中的结果显示为表格。

我使用这段代码列出了我数据库中的所有内容，并将其显示在 jsp 上。但现在我只想显示所有具有相同作者姓名的结果。

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

}

我收到错误:

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)

任何建议

Answer 1

基本上 NullpointerException 可能是因为你的

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

需要一个参数作为占位符，但在执行查询之前你必须替换占位符

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

你需要做类似的事情

ps.setString(1,"yourAuthor");