Searching Records using Jsp and Servlet in Mysql database:
Table data:
Step1 :develop a search.jsp page .
This form will sent a action attribute value when
you are clicking submit button .
<form method="post"
action="search">
<table border="0" width="300"
align="center" bgcolor="#e9fh">
<tr><td colspan=2 style="font-size:12pt;"
align="center">
<h3>Search Details</h3></td></tr>
<tr><td ><b>Employee ID:</b></td>
<td>: <input
type="text" name="eid" id="eid">
</td></tr>
<tr><td colspan=2 align="center">
<input type="submit"
value="Search" ></td></tr>
</table>
</form>
Step2: develop a
servlet controller and it is dispatching to the view page
Here ,two queries are there .First,you get a
specific record or no records ,when you are entering a value.Otherwise,you get
a total records(when you are not enetering a value).
package controller;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.*;
import java.util.ArrayList;
import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
public class Search extends HttpServlet {
protected void doPost(HttpServletRequest request, HttpServletResponse
response)
throws ServletException, IOException {
response.setContentType("text/html");
PrintWriter out = response.getWriter();
Connection conn = null;
String url = "jdbc:mysql://localhost:3306/";
String dbName = "ashok";
String driver = "com.mysql.jdbc.Driver";
String userName = "root";
String password = "root";
Statement st=null;
try
{
Class.forName(driver).newInstance();
conn = DriverManager.getConnection(url + dbName, userName, password);
System.out.println("connected!.....");
String eid = request.getParameter("eid");
ArrayList al = null;
ArrayList pid_list = new ArrayList();
String query = "select * from emp";
if(eid!=null && !eid.equals("")){
query = "select * from emp where eid='" + eid + "'
";
}
System.out.println("query " + query);
st = conn.createStatement();
ResultSet rs = st.executeQuery(query);
while (rs.next()) {
al = new ArrayList();
al.add(rs.getString(1));
al.add(rs.getString(2));
al.add(rs.getString(3));
al.add(rs.getString(4));
System.out.println("al :: " + al);
pid_list.add(al);
}
request.setAttribute("piList", pid_list);
RequestDispatcher view =
request.getRequestDispatcher("view.jsp");
view.forward(request, response);
conn.close();
System.out.println("Disconnected!");
}
catch (Exception e) {
e.printStackTrace();
}
}
@Override
public
String getServletInfo() {
return "getting records from database through servlet
controller";
}//
</editor-fold>
}
Step3: Develop a view.jsp form for
displaying dabase records
<%@ page import="java.util.*"
%>
<%@page
contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE HTML>
<html>
<head>
</head>
<body>
<table width="700px" align="center"
style="border:1px solid
#000000;">
<tr>
<td colspan=4
align="center"
style="background-color:teal">
<b>Employee
Records</b></td>
</tr>
<tr style="background-color:lightgrey;">
<td><b>EID</b></td>
<td><b>NAME</b></td>
<td><b>SALARY</b></td>
<td><b>ADDRESS</b></td>
</tr>
<%
int count = 0;
String color = "#F9EBB3";
if
(request.getAttribute("piList") != null) {
ArrayList al = (ArrayList)
request.getAttribute("piList");
System.out.println(al);
Iterator itr = al.iterator();
while (itr.hasNext()) {
if ((count % 2) == 0) {
color =
"#eeffee";
}
count++;
ArrayList pList =
(ArrayList) itr.next();
%>
<tr style="background-color:<%=color%>;">
<td><%=pList.get(0)%></td>
<td><%=pList.get(1)%></td>
<td><%=pList.get(2)%></td>
<td><%=pList.get(3)%></td>
</tr>
<%
}
}
if (count == 0) {
%>
<tr>
<td colspan=4
align="center"
style="background-color:#eeffee"><b>No
Record Found..</b></td>
</tr>
<% }
%>
</table>
</body>
</html>
Output:
case1: when you are not entering any value get all the records
case2: when you are enter a existing value we can get a praticular record only.
case3: when you are entering any value,there is not exist in db.
servlet showing error...
ReplyDeleteHTTP Status 405 - HTTP method GET is not supported by this URL
please tell us how to remove that
check your servlet replace ProcessRequest or doPost by doGet and also check jsp page page its method is same as servelt....
ReplyDeleteNice dude..... its really helpful 4 me !
ReplyDeletewhy should i click the search button then only show the details i need page onloading show all the details please help me!!!!!!!
ReplyDeletehello....I tried running this code but then i got ( org.springframework.web.servlet.PageNotFound.noHandlerFound No mapping found for HTTP request with URI [/search] in DispatcherServlet with name 'mvc-dispatcher')error after pressing the search button.
ReplyDeleteA little help here!! Thank you....
Thank you so muchhhhh
ReplyDelete