28. Develop a program to perform the database driven operation like insert, Delete, Update and select. To perform the above operations create one table named Employee.
Create Table in MySql [ Employee ]
CREATE TABLE `wtad`.`Employee` (
`EmpId` INT( 4 ) NOT NULL ,
`Empname` VARCHAR( 15 ) NOT NULL ,
`Emp_desig` VARCHAR( 20 ) NOT NULL ,
`Emp_J_Date` VARCHAR( 20 ) NOT NULL ,
`Emp_Salary` INT( 8 ) NOT NULL
) ENGINE = MYISAM ;
Create Table in Oracle [ Employee ]
CREATE TABLE Employee (
EmpId NUMBER( 4 ) NOT NULL ,
Empname VARCHAR( 15 ) NOT NULL ,
Emp_desig VARCHAR( 20 ) NOT NULL ,
Emp_J_Date VARCHAR( 20 ) NOT NULL ,
Emp_Salary NUMBER( 8 ) NOT NULL
)
index.jsp
<html>
<head>
<title> Ex-28 </title>
<LINK REL="STYLESHEET" HREF="myCss.css" TYPE="text/css">
<script type="text/javascript" src="myJs.js">
</script>
</head>
<%@page import="java.sql.*"%>
<%!
String className= "com.mysql.jdbc.Driver";
String url= "jdbc:mysql://localhost:3306/wtad";
String user= "root";
String password= "admin";
String n;
%>
<body>
<%
Connection con;
Statement stm;
ResultSet rs;
try
{
Class.forName(className);
con= DriverManager.getConnection(url, user, password);
String sql= "SELECT max(EmpId)+1 FROM employee";
stm=con.createStatement();
rs= stm.executeQuery(sql);
rs.next();
n=String.valueOf(rs.getInt(1));
}
catch(Exception e)
{
out.println(""+e);
}
%>
<center>
<div class="abc" id="f1" align="center">
<br>
<form method="POST" action="CRUD.bsr" onSubmit="return frmValidate(this)">
<table align="center">
<tr>
<td colspan=2 align=center>
Employee
<hr>
</td>
</tr>
<tr>
<td>Id : </td>
<td><input type="text" name="txtID" value=<%=n%> readonly></td>
</tr>
<tr>
<td>Name : </td>
<td><input type="text" name="txtName"></td>
</tr>
<tr>
<td>Designation : </td>
<td><input type="text" name="txtDesig"></td>
</tr>
<tr>
<td>Date of Joining : </td>
<td><input type="text" name="txtDOJ"></td>
</tr>
<tr>
<td>Salary : </td>
<td><input type="text" name="txtSalary" onkeypress='return isNum(event)'></td>
</tr>
<tr>
<td colspan=2 align=center>
<hr>
<input type="hidden" name="action" value="add">
<input type="submit" value="Insert">
<input type="reset" value="Clear">
<input type="button" value="Display" onclick="doDisplay();">
<input type="button" value="Search" onclick="doSearch();">
</td>
</tr>
</table>
</form>
</div>
</center>
</body>
</html>
dispRec.jsp
<HTML>
<HEAD>
<TITLE>Ex-28 : www.BipinRupadiya.blogspot.in</TITLE>
<LINK REL="STYLESHEET" HREF="myCss.css" TYPE="text/css">
</HEAD>
<BODY>
<div class="abc" id="f1">
<br>
<center>
<h3>
<%@ page import="java.sql.*"%>
<FORM METHOD="get" ACTION="CRUD.bsr">
<%
try
{
Class.forName("com.mysql.jdbc.Driver");
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/wtad", "root","admin");
Statement statement = connection.createStatement();
// Send query to database and store results.
ResultSet resultSet = statement.executeQuery("select * from employee where 1=2");
ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
int columnCount = resultSetMetaData.getColumnCount();
out.println("search by : <select name='fldName'>");
for(int i=1; i <= columnCount; i++) //create Table heading with column name
{
out.print("<option value='"+resultSetMetaData.getColumnName(i) +"' >" + resultSetMetaData.getColumnName(i)+"</option>");
}
out.println("</select>");
}
catch(Exception ex)
{
out.println("Error with input: " + ex);
}
%>
<br>
value : <input type=TEXT name = "fldVal" >
<input type="hidden" name = "action" value="display">
<BR><BR><input type="submit" value="Show Result">
</FORM>
</div>
</BODY>
</HTML>
editForm.jsp
<html>
<head>
<title> Ex-28</title>
<LINK REL="STYLESHEET" HREF="myCss.css" TYPE="text/css">
<script type="text/javascript" src="myJs.js" >
</script>
</head>
<%@page import="java.sql.*"%>
<%!
String className= "com.mysql.jdbc.Driver";
String url= "jdbc:mysql://localhost:3306/wtad";
String user= "root";
String password= "admin";
String f1,f2,f3,f4,f5;
Connection con;
Statement stm;
ResultSet rs;
%>
<body>
<%
try
{
Class.forName(className);
con= DriverManager.getConnection(url, user, password);
String sql= "SELECT * FROM employee where EmpId="+request.getParameter("eId");
// out.println(sql);
stm=con.createStatement();
rs= stm.executeQuery(sql);
rs.next();
f1=String.valueOf(rs.getInt(1));
f2=rs.getString(2);
f3=rs.getString(3);
f4=rs.getString(4);
f5=String.valueOf(rs.getInt(5));
}
catch(Exception e)
{
out.println(""+e);
}
%>
<center>
<div class="abc" id="f1">
<br>
<form method="POST" action="CRUD.bsr">
<table align="center">
<tr>
<td colspan=2 align=center>
Employee
<hr>
</td>
</tr>
<tr>
<td>Id : </td>
<td><input type="text" name="txtID" value=<%=f1%> readonly></td>
</tr>
<tr>
<td>Name : </td>
<td><input type="text" name="txtName" value=<%=f2%> ></td>
</tr>
<tr>
<td>Designation : </td>
<td><input type="text" name="txtDesig" value=<%=f3%> ></td>
</tr>
<tr>
<td>Date of Joining : </td>
<td><input type="text" name="txtDOJ" value=<%=f4%> ></td>
</tr>
<tr>
<td>Salary : </td>
<td><input type="text" name="txtSalary" value=<%=f5%> ></td>
</tr>
<tr>
<td colspan=2 align=center>
<hr>
<input type="hidden" name="action" value="update">
<input type="submit" value="Update">
<input type="button" value="Display" onclick="doDisplay();">
<input type="button" value="Search" onclick="doSearch();">
<input type="button" value="Home" onclick="doHome();">
</td>
</tr>
</table>
</form>
</div>
</center>
</body>
</html>
CRUD.java
package bsr;
import java.io.*;
import java.sql.*;
import javax.servlet.*;
import javax.servlet.http.*;
import javax.servlet.annotation.WebServlet;
/*
Ex-28
Developer : Bipin Rupadiya
*/
@WebServlet("/CRUD.bsr")
public class CRUD extends HttpServlet
{
String className= "com.mysql.jdbc.Driver";
String url= "jdbc:mysql://localhost:3306/wtad";
String user= "root";
String password= "admin";
Connection con;
Statement ps;
String sql;
PrintWriter out;
ServletContext context;
RequestDispatcher rd;
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException
{
doPost(request, response);
}
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException
{
//doGet(request, response);
response.setContentType("text/html");
out= response.getWriter();
out.println("<html><head><LINK REL='STYLESHEET' HREF='myCss.css' TYPE='text/css'>");
out.println("<script type='text/javascript' src='myJs.js'></script></head><body>");
String action;
try
{
Class.forName(className);
con= DriverManager.getConnection(url, user, password);
context= getServletContext();
action=request.getParameter("action").toString();
if(action.equals("add"))
{
doAdd(request,response);
}
else if(action.equals("update"))
{
doUpdate(request,response);
}
else if(action.equals("delete"))
{
doDelete(request,response);
}
else if(action.equals("display"))
{
doDisplay(request,response);
}
else
{
response.sendRedirect("index.jsp");
}
ps.close();
con.close();
}
catch(Exception e)
{
action="";
out.println("error for doPost : "+e);
}
out.println("</body></html>");
}
public void doAdd(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException
{
String txtID= request.getParameter("txtID");
String txtName= request.getParameter("txtName");
String txtDesig= request.getParameter("txtDesig");
String txtDOJ= request.getParameter("txtDOJ");
String txtSalary= request.getParameter("txtSalary");
try
{
sql= "INSERT INTO employee VALUES("+txtID+",'"+txtName+"','"+txtDesig+"','"+txtDOJ+"',"+txtSalary+")";
ps=con.createStatement();
int ans=ps.executeUpdate(sql);
if(ans!=0)
{
out.println("<div class=errorG>Record Successfully added</div>");
}
else
{
out.println("<div class=errorR>Problem in insert</div>");
}
rd= context.getRequestDispatcher("/index.jsp");
rd.include(request, response);
}
catch(Exception sx)
{
out.println(sx);
}
}//doAdd
public void doUpdate(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException
{
String txtID= request.getParameter("txtID");
String txtName= request.getParameter("txtName");
String txtDesig= request.getParameter("txtDesig");
String txtDOJ= request.getParameter("txtDOJ");
String txtSalary= request.getParameter("txtSalary");
try
{
sql= "UPDATE employee set Empname='"+txtName+"', Emp_desig='"+txtDesig+"', Emp_J_Date='"+txtDOJ+"', Emp_Salary="+txtSalary+" where EmpId="+txtID;
ps=con.createStatement();
int ans=ps.executeUpdate(sql);
if(ans!=0)
{
out.println("<div class=errorG>Record Successfully Updated</div>");
}
else
{
out.println("<div class=errorR>Update Fail ...</div>");
}
rd= context.getRequestDispatcher("/index.jsp");
rd.include(request, response);
}
catch(Exception sx)
{
out.println(sx);
}
}//doUpdate
public void doDelete(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException
{
String txtID= request.getParameter("eId");
try
{
sql= "DELETE FROM employee WHERE EmpId="+txtID;
ps=con.createStatement();
int ans=ps.executeUpdate(sql);
if(ans!=0)
{
out.println("<div class=errorG>Record Successfully Deleted</div>");
}
else
{
out.println("<div class=errorR>Delete Fail ...</div>");
}
rd= context.getRequestDispatcher("/index.jsp");
rd.include(request, response);
}
catch(Exception sx)
{
out.println(sx);
}
}//doDelete
public void doDisplay(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException
{
String fldn=request.getParameter("fldName");
String fldv=request.getParameter("fldVal");
String query;
if( (fldn==null ||fldn.equals(' ') ) || (fldv==null ||fldv.equals(' ') ) )
{
query = "select * from employee order by EmpId";
}
else
{
query = "select * from employee where "+fldn+"='"+fldv+"' order by EmpId";
}
//out.println("SQL Query-> : "+query);
showTable( query, out);
}
public void showTable(String query, PrintWriter out)
{
try
{
Statement statement = con.createStatement();
// Send query to database and store results.
ResultSet resultSet = statement.executeQuery(query);
ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
int columnCount = resultSetMetaData.getColumnCount();
// Print results.
out.println("<center>");
out.println("<TABLE BORDER=0 cellpadding='5px'>");
// Column index starts at 1 (a la SQL), not 0 (a la Java).
out.println("<tr><th colspan="+(columnCount+2)+"><h1>Employee</h1><hr></th></tr>");
for(int i=1; i <= columnCount; i++) //create Table heading with column name
{
out.print("<TH bgcolor='orange'>" + resultSetMetaData.getColumnName(i));
}
out.println();
int row=0;
String rowCol="";
// Step through each row in the result set.
while(resultSet.next())
{
out.println("<TR>");
// Step across the row, retrieving the data in each
// column cell as a String.
row+=1;
for(int i=1; i <= columnCount; i++)
{
if(row%2==0)
{
rowCol="#ababcc";
}
else
{
rowCol="#ababaz";
}
out.print("<TD bgcolor='"+rowCol+"'>" + resultSet.getString(i));
}
out.print("<TD bgcolor='"+rowCol+"'><a href='editForm.jsp?action=edit&eId=" + resultSet.getString(1)+"'>Edit</a>");
String aHref="<TD bgcolor='"+rowCol+"'><a href='CRUD.bsr?action=delete&eId=" + resultSet.getString(1) + "' onClick='return doConfirmDel();'>Delete</a>";
out.print(aHref);
out.println();
}
if(row==0)
{
out.println("<tr><th colspan="+(columnCount+2)+"><h1>No Record Found...!</h1></th></tr>");
}
out.println("<TR><TD colspan="+(columnCount+2)+" align='center'>");
out.println("<hr><form><input type='button' value='Home' onclick='doHome();'></form></td></tr>");
out.println("</TABLE>");
out.println("</center>");
}
catch(Exception ex)
{
System.err.println("Error with input: " + ex);
}
}//showTable
} // end of class
myJs.js
function frmValidate(frm)
{
if(frm.txtName.value.trim()=="")
{
alert("Enter Name : ");
frm.txtName.focus();
return false;
}
if(frm.txtDesig.value.trim()=="")
{
alert("Enter Designation");
frm.txtDesig.focus();
return false;
}
if(frm.txtDOJ.value.trim()=="")
{
alert("Enter Date of Joining");
frm.txtDOJ.focus();
return false;
}
if(frm.txtSalary.value.trim()=="")
{
alert("Enter Salary");
frm.txtSalary.focus();
return false;
}
if(isNaN(frm.txtSalary.value))
{
alert("Enter Numeric Value");
frm.txtSalary.focus();
return false;
}
return true;
}
function isNum(evt)
{
var charCode = (evt.which) ? evt.which : event.keyCode
if(charCode==46)
{
return true;
}
else if(! (charCode >= 48 &&charCode <= 57))
{
return false;
}
return true;
}
function doDisplay()
{
document.forms[0].action="CRUD.bsr?action=display";
document.forms[0].submit();
}
function doSearch()
{
document.forms[0].action="dispRec.jsp";
document.forms[0].submit();
}
function doHome()
{
document.forms[0].action="index.jsp";
document.forms[0].submit();
}
function doConfirmDel()
{
if(confirm("Are you sure...? You want to delete this record...?"))
{
return(true);
}
else
{
return(false);
}
}
myCss.css
.abc
{
background:#ababff;
position:absolute;
top:2in;
--height : 200px;
width:300px;
left:5in;
border-style:solid;
}
.errorG
{
background:#00Fa00;
position:absolute;
top:1in;
height:30px;
width:300px;
left:5in;
padding-top:10px;
color:#000000;
border-color:#000000;
border-style:dotted;
font-size: 13pt ;
text-align:center ;
}
.errorR
{
background:#Fa0000;
position:absolute;
top:1in;
height:30px;
width:300px;
left:5in;
padding-top:10px;
color:#000000;
border-color:#000000;
border-style:dotted;
font-size: 13pt ;
text-align:center ;
}