postheadericon JSP JDBC Example

Develop a JSP Page to perform database driven operations like insert, Delete, Update and selection with table named Student having fields like StudId, Name, Address, result.



Create Table in Oracle / MySQL [ student ]

CREATE TABLE   student (
StudId NUMBER( 4 ) NOT NULL ,
Name VARCHAR( 15 ) NOT NULL ,
Address VARCHAR( 20 ) NOT NULL ,
result NUMBER( 8 ) NOT NULL
)





index.jsp

<html>
<head>
<title> Ex-34</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(StudId)+1 FROM student";
 
   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="doAdd.jsp" onSubmit="return frmValidate(this)">
 <table align="center">
 <tr>
  <td colspan=2 align=center>
   student
   <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>Address : </td>
  <td><input type="text" name="txtAddress"></td>
 </tr>
 <tr>
  <td>Result : </td>
  <td><input type="text" name="txtResult" 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>


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 ;
}



myJs.js

function frmValidate(frm)
{
  if(frm.txtName.value.trim()=="")
  {
   alert("Enter Name : ");
   frm.txtName.focus();
   return false;
  }
  if(frm.txtAddress.value.trim()=="")
  {
   alert("Enter Address");
   frm.txtAddress.focus();
   return false;
  }
 
  if(frm.txtResult.value.trim()=="")
  {
   alert("Enter Result");
   frm.txtResult.focus();
   return false;
  }
  if(isNaN(frm.txtResult.value))
  {
   alert("Enter Numeric Value");
   frm.txtResult.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="doDisplay.jsp";
 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);
 }

}




doAdd.jsp

<html>
<head>
<title> Ex-34</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";

Connection con;
PreparedStatement ps;
String sql;
  ServletContext context;
RequestDispatcher rd;


%>
<%
Class.forName(className);
con= DriverManager.getConnection(url, user, password);
context= getServletContext();

String txtID= request.getParameter("txtID");
 String txtName= request.getParameter("txtName");
 String txtAddress= request.getParameter("txtAddress");
 String txtDOJ= request.getParameter("txtDOJ");
 String txtResult= request.getParameter("txtResult");

 try
 {
  //sql= "INSERT INTO student VALUES("+txtID+",'"+txtName+"','"+txtAddress+"','"+txtDOJ+"',"+txtResult+")";    
sql= "INSERT INTO student VALUES(?,?,?,?)";    
ps=con.prepareStatement(sql);
ps.setInt(1, Integer.parseInt(txtID));
ps.setString(2,txtName);
ps.setString(3,txtAddress);
ps.setInt(4,Integer.parseInt(txtResult));
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);
 }

%>

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="doDisplay.jsp">
<%
  try
  {
   Class.forName("com.mysql.jdbc.Driver");
   Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/wtad", "root","admin");
 
PreparedStatement statement=connection.prepareStatement("select * from student where 1=2");
   //Statement statement = connection.createStatement();
 
   // Send query to database and store results.
   //ResultSet resultSet = statement.executeQuery("select * from student 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>



doDisplay.jsp

<html>
<head>
<title> Ex-34</title>
<LINK REL="STYLESHEET" HREF="myCss.css" TYPE="text/css">

<script type="text/javascript" src="myJs.js">
</script>

</head>
<body>
<%@ page import="java.sql.*"%>

<%!

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;
  ServletContext context;
RequestDispatcher rd;



%>
<%
Class.forName(className);
con= DriverManager.getConnection(url, user, password);
context= getServletContext();
String fldn=request.getParameter("fldName");
  String fldv=request.getParameter("fldVal");

  String query;
  if( (fldn==null ||fldn.equals(' ') ) || (fldv==null ||fldv.equals(' ') ) )
  {
   query = "select * from student order by StudId";
  }
  else
  {
   query = "select * from student where "+fldn+"='"+fldv+"' order by StudId";
  }
  //out.println("SQL Query-> : "+query);

   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>student</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='doDelete.jsp?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);
  }

%>

</body>



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 student where StudId=?";
   //String sql= "SELECT * FROM student where StudId="+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=String.valueOf(rs.getInt(4));
 
  }
  catch(Exception e)
  {
   out.println(""+e);
  }
%>
<center>
<div class="abc"  id="f1">
<br>
<form method="POST" action="doUpdate.jsp">
 <table align="center">
 <tr>
  <td colspan=2 align=center>
   student
   <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>Address: </td>
  <td><input type="text" name="txtAddress" value=<%=f3%> ></td>
 </tr>

 <tr>
  <td>Result : </td>
  <td><input type="text" name="txtResult" value=<%=f4%> ></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>

doDelete.jsp

<html>
<head>
<title> Ex-34</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";

Connection con;
PreparedStatement ps;
String sql;
  ServletContext context;
RequestDispatcher rd;


%>
<%
Class.forName(className);
con= DriverManager.getConnection(url, user, password);
context= getServletContext();

String txtID= request.getParameter("eId");

 try
 {
  //sql= "DELETE FROM student WHERE StudId="+txtID;
  sql= "DELETE FROM student WHERE StudId=?";
  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);
 }

%>



doUpdate.jsp

<html>
<head>
<title> Ex-34</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";

Connection con;
PreparedStatement ps;
String sql;
  ServletContext context;
RequestDispatcher rd;


%>
<%
Class.forName(className);
con= DriverManager.getConnection(url, user, password);
context= getServletContext();

 String txtID= request.getParameter("txtID");
 String txtName= request.getParameter("txtName");
 String txtAddress= request.getParameter("txtAddress");

 String txtResult= request.getParameter("txtResult");

 try
 {

  //sql= "UPDATE student set Name='"+txtName+"', Address='"+txtAddress+"', Emp_J_Date='"+txtDOJ+"', Emp_Salary="+txtResult+" where StudId="+txtID;
sql= "UPDATE student set Name=?, Address=?,  result=? where StudId=?";



ps=con.prepareStatement(sql);

ps.setString(1,txtName);
ps.setString(2,txtAddress);

ps.setInt(3,Integer.parseInt(txtResult));
ps.setInt(4, 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);
 }
%>



Blog Archive

Total Pageviews

© BipinRupadiya.com. Powered by Blogger.