postheadericon example of preparedstatement in java


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-29</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;
  PreparedStatement stm;
  ResultSet rs;
  try
  {
   Class.forName(className);
   con= DriverManager.getConnection(url, user, password);
 
   String sql= "SELECT max(EmpId)+1 FROM employee";
 
   stm=con.prepareStatement(sql);
   rs= stm.executeQuery();
 
   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");
 
PreparedStatement statement=connection.prepareStatement("select * from employee where 1=2");
   //Statement statement = connection.createStatement();
 
   // Send query to database and store results.
   //ResultSet resultSet = statement.executeQuery("select * from employee where 1=2");
   ResultSet resultSet = statement.executeQuery();
 
   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;
  PreparedStatement stm;
  ResultSet rs;
%>
<body>

<%


  try
  {
   Class.forName(className);
   con= DriverManager.getConnection(url, user, password);
 
String sql= "SELECT * FROM employee where EmpId=?";
   //String sql= "SELECT * FROM employee where EmpId="+request.getParameter("eId");
  // out.println(sql);

   stm=con.prepareStatement(sql);
   stm.setInt(1, Integer.parseInt(request.getParameter("eId")));
   rs= stm.executeQuery();
 
   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;
 PreparedStatement 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+")";    
    sql= "INSERT INTO employee VALUES(?,?,?,?,?)";    
    ps=con.prepareStatement(sql);
    ps.setInt(1, Integer.parseInt(txtID));
ps.setString(2,txtName);
ps.setString(3,txtDesig);
ps.setString(4,txtDOJ);
ps.setInt(5,Integer.parseInt(txtSalary));
    int ans=ps.executeUpdate();
 
   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;
    sql= "UPDATE employee set Empname=?, Emp_desig=?, Emp_J_Date=?, Emp_Salary=? where EmpId=?";
 

 
    ps=con.prepareStatement(sql);
 
ps.setString(1,txtName);
ps.setString(2,txtDesig);
ps.setString(3,txtDOJ);
ps.setInt(4,Integer.parseInt(txtSalary));
ps.setInt(5, Integer.parseInt(txtID));
    int ans=ps.executeUpdate();
 

   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;
   sql= "DELETE FROM employee WHERE EmpId=?";
   ps=con.prepareStatement(sql);
 
ps.setInt(1,Integer.parseInt(txtID));
    int ans=ps.executeUpdate();
 
 
   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 ;
}




Follow by Email

Blog Archive

Total Pageviews

© BipinRupadiya.com. Powered by Blogger.