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 ;
30. Write a Java application to invoke a stored procedure using a CallableStatement. For this a stored procedure called incrementSalary may be developed to increase all the employees salary by a percentage specified in the parameter.
Create Procedure in Oracle
create or replace procedure incrementSalary(percent IN NUMBER)
is
BEGIN
UPDATE Employee
SET Emp_Salary = Emp_Salary + (Emp_Salary *( percent/100));
commit;
END incrementSalary;
index.jsp
<html>
<head>
<title> Ex-30 : www.BipinRupadiya.blogspot.in</title>
<LINK REL="STYLESHEET" HREF="myCss.css" TYPE="text/css">
<script>
function frmValidate(frm)
{
if(frm.txtPer.value.trim()=="")
{
alert("Enter Percentage : ");
frm.txtPer.focus();
return false;
}
if(isNaN(frm.txtPer.value))
{
alert("Invalid Percentage : ");
frm.txtPer.focus();
return false;
}
return true;
}
</script>
</head>
<body>
<center>
<div class="abc" id="f1" align="center">
<br>
<form method="POST" action="incSal.jsp" onSubmit="return frmValidate(this)">
<table align="center">
<tr>
<td colspan=2 align=center>
<h3>Employee </h3>
<hr>
</td>
</tr>
<tr>
<td>Increment Salary by Percentage : </td>
<td><input type="text" name="txtPer" size="3" onkeypress='return isNum(event)'>%</td>
</tr>
<tr>
<td colspan=2 align=center>
<hr>
<input type="hidden" name="action" value="incSal">
<input type="submit" value="Increment Salary">
<input type="reset" value="Clear">
</td>
</tr>
</table>
</form>
</div>
</center>
</body>
</html>
incSal.jsp
<%@ page import="java.sql.*" %>
<%!
String className= "oracle.jdbc.driver.OracleDriver";
String url= "jdbc:oracle:thin:@localhost:1521:xe";
String user= "scott";
String password= "tiger";
String action="x";
%>
<%
try
{
Class.forName(className);
Connection con= DriverManager.getConnection(url, user, password);
CallableStatement cs=con.prepareCall("{ call incrementSalary(?)}");
cs.setDouble(1,Double.parseDouble(request.getParameter("txtPer")));
int ans=cs.executeUpdate();
ServletContext context= getServletContext();
RequestDispatcher rd= context.getRequestDispatcher("/index.jsp");
if(ans!=0)
{
out.println("<div class=errorG>Salary Successfully updated</div>");
}
else
{
out.println("<div class=errorR>Problem in update</div>");
}
rd.include(request, response);
cs.close();
con.close();
}
catch(Exception e)
{
out.println(""+e);
}
%>
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 ;
}
Subscribe to:
Posts (Atom)