Instead of returning the whole table, how can I just return the row they searched for?

Question

New to programming, and this site. My current .jsp is

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
pageEncoding="ISO-8859-1"%>
<%@ taglib prefix="s" uri="/struts-tags" %>
<%@ page import="java.sql.*" 
         import="action.*"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"             "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Here is your videogame!</title>
</head>

<BODY>
    <H1>The Videogame Database Table </H1>

    <%
        Connection c = DriverManager.getConnection(
            "jdbc:mysql://localhost:3306/videogamesdb", "root", "password");

        //instantiating a SQL statement
        Statement statement = c.createStatement();
        String sql = "SELECT * FROM videogamedetails";
        ResultSet resultset = 
            statement.executeQuery(sql) ;
    %>

    <TABLE BORDER="1">
        <TR>
            <TH>Name</TH>
            <TH>Genre</TH>
            <TH>Developer</TH>
            <TH>Rating</TH>
        </TR>
        <% while(resultset.next()) {%>
        <TR>
            <TD> <%= resultset.getString("vidgameName") %></TD>
            <TD> <%= resultset.getString("vidgameGenre") %></TD>
            <TD> <%= resultset.getString("vidgameDev") %></TD>
            <TD> <%= resultset.getInt("vidgameRating") %></TD>
        </TR>
        <% } %>
    </TABLE>
</BODY>
</HTML>

I'm fairly certain I need to use "SELECT * FROM videogamedetails WHERE vidgameName = p_Name", but I'm not sure how to have p_Name be what the user searched. Thanks!

The value of `p_Name` should be in a request parameter. Also, it will be better that instead of sending the name of the game you send the id. Since you're learning, please do it right and stop using scriptlets, it's heavily discouraged, see [here](http://stackoverflow.com/q/3177733/1065197) for the explanation. Also, it will be a great oportunity to learn about MVC pattern and layered applications. — Luiggi Mendoza, May 03 '13 at 19:55

score 0 · Answer 1 · answered May 03 '13 at 19:54

0

You want to change to a PreparedStatement so it can take a parameter.

PreparedStatement statement = c.prepareStatement("select * from videogamedetails where vidgamename = ?");
statement.setString(1, "nameOfVideoGame");

Also, two general tips:

It is a best practice to use EL/JSTL in JSPs instead of scriptlets
It is a best practice to keep Java code out of a JSP. Search for MVC online to see how to separate the two.

answered May 03 '13 at 19:54

Jeanne Boyarsky

12,156
2
49
59

OP's question is about how to get this *nameOfVideoGame* in its JSP. – Luiggi Mendoza May 03 '13 at 19:55
Missed that. So it would be statement.setString(1, request.getParameter("searchParamName"). – Jeanne Boyarsky May 03 '13 at 19:59
Maybe, maybe not. We're not even sure if OP's passing this as request parameter or just retrieving all the data from a Servlet or whatever he/she has done. – Luiggi Mendoza May 03 '13 at 20:00

NullPointerException · Answer 2 · 2013-05-03T20:01:30.600

0

I am not sure how you are passing the search parameter p_Name to this JSP.

One way is to set the p_Name as request attribute from where your redirecting to this page and then retrieve it on this jsp.

  String p_Name = (String) request.getAttribute("someVariable");

Use prepared statement to pass the value to your query.

  PreparedStatement statement = c.prepareStatement("select * from videogamedetails where vidgamename = ?");
  statement.setString(1,  p_Name);

  rs = statement.executeQuery();

edited May 03 '13 at 20:01

answered May 03 '13 at 19:56

NullPointerException

3,732
5
28
62

*I'm not sure how to have p_Name be what the user searched* did you read that? If so, where in your answer talk about retrieving *p_Name* in the JSP? – Luiggi Mendoza May 03 '13 at 19:57

Instead of returning the whole table, how can I just return the row they searched for?

2 Answers2