[pro34index.jsp]
<html>
<body>
<table align=center cellpadding=10>
<tr>
<td><img src="logo.jpg"/>
</table>
<table align=center width=100% cellpadding=5>
<tr>
<td colspan=2 align="center"><h2><i>DATABASE DRIVEN OPERATIONS</i></h2>
<tr>
<td align="right" colspan=2><a href="http://master-gtu.blogspot.com">www.master-gtu.blogspot.com</a>
<tr>
<td valign=top>
<form name="f1" action="pro34perform.jsp">
<table>
<tr>
<td>Student ID
<td><input type="text" name="studid">
<td><input type="submit" name="search" onclick="document.f1.flag.value=1;document.f1.fn.value='studid';document.f1.action='pro34index.jsp'">
<tr>
<td>NAME
<td><input type="text" name="name">
<td><input type="submit" name="search" onclick="document.f1.flag.value=2;document.f1.fn.value='name';document.f1.action='pro34index.jsp'">
<tr>
<td>ADDRESS
<td><input type="text" name="address">
<tr>
<td>RESULT
<td><input type="text" name="result">
<tr>
<td colspan=3>
<input type="submit" name="insert" value="INSERT" onclick="document.f1.flag.value=5">
<input type="submit" name="delete" value="DELETE" onclick="document.f1.flag.value=6">
<input type="submit" name="update" value="UPDATE" onclick="document.f1.flag.value=7">
<input type="submit" name="view" value="VIEW ALL" onclick="document.f1.action='pro34index.jsp'">
</table>
<input type="hidden" name="flag" value="">
<input type="hidden" name="fn" value="">
</form>
<%
String msg=request.getParameter("msg");
if(msg!=null)
out.println("<font color=red>"+msg+"</font>");
%>
<td>
<jsp:include page="/pro34showtable.jsp"/>
</table>
</body>
</html>
[pro34showtable.jsp]
<%@ page import="java.sql.*" %>
<table border=5><tr><td>
<table cellpadding=10>
<tr bgcolor="limegreen">
<th>StudId<th>Name<th>Address<th>Result
<%
String q;
boolean flag=false;
try
{
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","pankaj");
String fn=request.getParameter("fn");
String value=request.getParameter(fn);
if(fn=="" || fn==null)
{
q="select * from student34";
}
else
{
q="select * from student34 where "+fn+" = ?";
flag=true;
}
PreparedStatement ps=con.prepareStatement(q);
if(flag)
ps.setString(1,value);
ResultSet rs=ps.executeQuery();
while(rs.next())
{
%>
<tr>
<td><%= rs.getInt(1) %>
<td><%= rs.getString(2) %>
<td><%= rs.getString(3) %>
<td><%= rs.getString(4) %>
<%
}
rs.close();
ps.close();
con.close();
}catch(Exception e){ out.println("error found:"+e);}
%>
</table></table>
[pro34perform.jsp]
<%@ page import="java.sql.*,java.lang.*" %>
<%! int flag;
String q;
%>
<%
String msg="";
try{
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","pankaj");
PreparedStatement ps=con.prepareStatement("select * from student34");
Statement stmt=con.createStatement();
ResultSet rs=ps.executeQuery();
String studid=request.getParameter("studid");
String name=request.getParameter("name");
String address=request.getParameter("address");
String result=request.getParameter("result");
flag=Integer.parseInt(request.getParameter("flag"));
if(studid.equals(""))
{
msg="Enter EmpID first";
flag=1;
}
switch(flag)
{
case 5:
rs=stmt.executeQuery("select * from student34 where studid='"+studid+"'");
if(!rs.next())
{
q="insert into student34 values(?,?,?,?)";
ps=con.prepareStatement(q);
ps.setString(1,studid);
ps.setString(2,name);
ps.setString(3,address);
ps.setString(4,result);
}
else
{
msg="Employee Id Already Exist...";
}
break;
case 6:
rs=stmt.executeQuery("select * from student34 where studid='"+studid+"'");
if(rs.next())
{
q="delete from student34 where studid=?";
ps=con.prepareStatement(q);
ps.setString(1,studid);
}
else
{
msg="Employee Not Exist...";
}
break;
case 7:
rs=stmt.executeQuery("select * from student34 where studid='"+studid+"'");
if(rs.next())
{
q="update student34 set name=?, address=?, result=? where studid=?";
ps=con.prepareStatement(q);
ps.setString(1,name);
ps.setString(2,address);
ps.setString(3,result);
ps.setString(4,studid);
}
else
{
msg="Employee Not Exist...";
}
break;
}
ps.executeQuery();
rs.close();
ps.close();
con.close();
}
catch(Exception e)
{}
response.sendRedirect("pro34index.jsp?msg="+msg);
%>
DATABASE QUERIES
ReplyDelete=================
CREATE TABLE STUDENT34
(
STUDID VARCHAR2(4000),
NAME VARCHAR2(4000),
ADDRESS VARCHAR2(4000),
RESULT VARCHAR2(4000)
);
insert into student values('1','PANKAJ','SENANAGAR','99');
insert into student values('2','AMI','CHANDIBAZAR','98');
insert into student values('3','VIJAY','KOMALNAGAR','97');
can we perform search operation on a single jsp page
ReplyDelete