Look, Read, Understand, Apply


Search data in database using JSP

Search data in database using JSP

HTML form to take search text from the user, searching user provided search text in the database, if the data is available then displaying additional information related with the search text.

Here, in example, a HTML search form is created with a text box, name given to the text box is cname. Method for the form is "POST", as the user clicks on the submit button search text provided in the form will be submitted to searchResult.jsp file.

In searchResult.jsp file, search text is accessed using getParameter() method of request object. Name of the text box "cname" is passed as argument to the getParameter() method and called like:


Following packages are imported in the searchResult.jsp file, these packages are required to connect and access database and to write output to browser window

  • <%@page import="java.sql.*"%>
  • <%@page import="java.sql.DriverManager"%>
  • <%@page import="java.sql.ResultSet"%>
  • <%@page import="java.sql.Statement"%>
  • <%@page import="java.sql.Connection"%>
  • <%@page import="*"%>

Connection with MySQL database is created using following statement

Connection c = DriverManager.getConnection("jdbc:mysql://localhost/test?user=root&password=abc@1");

  • Name of database: test
  • Name of database user: root
  • password of database user: abc@1
Corresponding jar file must be specified in the classpath variable

In the searchResult.jsp file, data send from the HTML form is accessed and stored to cname variable:

String cname = request.getParameter("cname");

Statement object "st" is created with the help of connection object c.

Statement st=(Statement) c.createStatement();

SQL select statement containing where condition is created and passed to executeQuery method of statement object "st", st executes select statement, returns data retrieved from databaes if found and the returned data is stored in the object "rs" of ResultSest class.

ResultSet rs=st.executeQuery("SELECT cname,address,gender FROM `csutomers` WHERE cname like '%"+cname+"%'");

The resultset object "rs" is iterated to display the records. HTML table is created to display the records




Search Form
<form method='post' action='searchResult.jsp'>
<p>Enter search text: </p>
<p><input type='text' name='cname'></p>
<p><input type='submit' name='submit' id='submit'></p>
<% Connection c = DriverManager.getConnection("jdbc:mysql://localhost/test?user=root&password="); //Connection c = DriverManager.getConnection(url,un,pw); //out.print("Connected!"); Statement st=(Statement) c.createStatement(); String cname = request.getParameter("cname"); ResultSet rs=st.executeQuery("SELECT cname,address,gender FROM customers WHERE cname like '%"+cname+"%'"); out.print("<table border='1'<"); out.print("<tr><th>Name</th><th>Address</th><th>Gender</th></tr>"); while({ out.println("<tr><td>"+rs.getString(1)+"</td><td>"+rs.getString(2)+"</td><td>"+rs.getString(3)+"</td></tr>"); } out.print("</table>"); %>