CODEWITHSUNDEEP
javahtmljsptomcat
coder4lyf
coder4lyf

Reputation: 927

JSP search bar function

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>

Upvotes: 3

Views: 27684

Answers (3)

user4815418
user4815418

Reputation:

Rachelle, you can refer to an example that displays employee details as output from JSP. This example makes use of servlet, jsp, jdbc, dao and properties file wherever appropriate. You can go thru this example and later modify to display student details from your database. Example link - http://theopentutorials.com/tutorials/java/design-patterns/post-redirect-get-prg-pattern-in-servlet-jsp/ Going thru above tutorial you will be able to setup your code thru eclipse.

Upvotes: 0

Ravi K Thapliyal
Ravi K Thapliyal

Reputation: 51711

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.

Upvotes: 3

Annamalai Thangaraj
Annamalai Thangaraj

Reputation: 532

There is error in your form action.

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

Your form action must point to another JSP or Servlet

<html>
<body>
<form action="student.jsp" method="GET">
Search By Name: <input type="text" name="Name">
    <input type ="submit" value="Search"></form>
</body>
</html>

In student.jsp

  • Add the database codes it will return all the student values.

    If you like to filter the value based on search input then get input value from form submit using <% String name = (String)request.getParameter("Name")%> and apply the value in rs = statement.executeQuery("SELECT * FROM students WHERE your condition");

Upvotes: 0

Related Questions