JSP search bar function

java html jsp tomcat

23,534

Your train of thought on calling a function is not correct. Why? Because, a function would invoke JavaScript which executes at client-side while you want to retrieve data from a database which should happen at server-side just like you're doing it already using <% scriptlets %>^{*see below}

<form action = test()>

The simplest way to implement this would be to self-submit the JSP i.e. the HTML form would post the data to the same JSP it has been defined in. You do this by just removing the action attribute altogether.

Now, to differentiate whether the JSP should retrieve the data about all the students or a specific one, you would change the code to check for the Name request attribute as follows.

String name = request.getParameter("Name");
if (name != null && name.length() > 0) {
    rs = statement.executeQuery("SELECT * FROM students WHERE Name = '" + name + "'");
} else {
    rs = statement.executeQuery("SELECT * FROM students");
}

Since, the query above has now become parameterized, the use of PreparedStatement is highly recommended now.

if (name != null && name.length() > 0) {
    PreparedStatement ps = connection.prepareStatement(
                           "SELECT * FROM students WHERE Name = ?"); // ? = placeholder
    ps.setString(1, name); // Bind the value to the placeholder
    rs = ps.executeQuery(); // Execute the prepared statement and fetch results
}

A PreparedStatement helps avoid SQL injection attacks as well as does away with the clunky and error-prone string concatenation.

^*Scrptlets have been deprecated long ago. A much better approach would be to put a Servlet in-between that handles all the JDBC code, populates the request object with the results and then forwards to a JSP that then only handles how the results are presented to the user.

23,534

Author by

coder4lyf

Updated on April 22, 2020

Comments

coder4lyf about 4 years

I'm trying to make a web interface using JSP and tomcat. I have a table of students and their information, and I want the user to be able to search for a student and then I want to display all of that student's information (in a table). So far I have displayed the entire student table and created a search box, but now I am at a loss of what to do when the user clicks "search". I'm thinking of creating a function to search the database but I'm not sure how to do this because I'm new to JSP. How do I call the function? Here is my code thus far:

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

<%
String connectionURL =
"jdbc:postgresql://cop4715-postgresql.ucf.edu:8472/******?user=*******&password=******";

Connection connection = null;
Statement statement = null;
ResultSet rs = null;
%>
<html><body>
<h1>Student Table</h1>
<table border = "2">
    <thead>
        <tr>
            <th>ID</th>
            <th>Name</th>
            <th>Birthday</th>
            <th>Address</th>
            <th>Email</th>
            <th>Level</th>
        </tr>
    </thead>
<%
Class.forName("org.postgresql.Driver").newInstance();
connection = DriverManager.getConnection(connectionURL);
statement = connection.createStatement();
rs = statement.executeQuery("SELECT * FROM students");
ResultSetMetaData metadata = rs.getMetaData();

 while (rs.next()) { %>
    <tr>
    <%
    for(int i = 1; i <= metadata.getColumnCount(); i++){ %>
        <td>
        <%=rs.getString(i)%>
        </td>
    <%
       }
    %>
    </tr>
<%
 }
%>
</table>
<%
rs.close();
%>
<br>

<form action = test()>
Search By Name: <input type="text" name="Name">
<input type ="submit" value="Search">
</form>



</body></html>