GTU WTAD Practical 28 : jsp select query example

GTU WTAD Practical 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. Field Name   Field Type EmpId Integer Empname Varchar Emp_desig Varchar Emp_J_Date Varchar Emp_Salary Numeric

GTU WTAD Practical 28This Servlet Program Explains Database Operations (JDBC Connectivity) Insert Update Delete. Here I have created different Servlet for Insert using JDBC, Servlet for Update using JDBC and Delete using JDBC.

Jump to WTAD Practical 29

File Name : index.jsp

File Location : Tomcat_Apache\webapps\gtu28\

<%@ page import="java.sql.*;" %>
<html>
	<head>
		<title>GTU WTAD Practical 28</title>
	</head>
	<body>
	
	<h2 align="center"> GTU WTAD Practical 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. Field Name   Field Type EmpId Integer Empname Varchar Emp_desig Varchar Emp_J_Date Varchar Emp_Salary Numeric </h2>
	<br><br>
	
	<form method="post" action="InsertServlet">
		<table>
		<tr><td colspan="2"> Employee Details </td></tr>
		<tr><td colspan="2"> &nbsp; </td></tr>
		<tr><td>Employee Id</td> <td> <input type="text" name="EmpId"></td></tr>
		<tr><td>Employee Name</td> <td> <input type="text" name="Empname"></td></tr>
		<tr><td>Employee Designation</td> <td> <input type="text" name="Emp_desig"></td></tr>
		<tr><td>Employee Joining Date</td> <td> <input type="text" name="Emp_J_Date"></td></tr>
		<tr><td>Employee Salary</td> <td> <input type="text" name="Emp_Salary"></td></tr>
		<tr><td colspan="2"> &nbsp; </td></tr>
		<tr><td colspan="2"> <input type="submit" value="Insert"> </td></tr>
		</table>
		
		<%
			String s = (String) session.getAttribute("msg");
			
			if(s!=null)
			{
				out.println("<div align=\"left\"><font color=\"blue\">"+s+"</font></div>");
				session.removeAttribute("msg");
			}
			
			
			Class.forName("com.mysql.jdbc.Driver");
            Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/mydatabase","root","");
            Statement st=con.createStatement();
            
            ResultSet rs=st.executeQuery("Select * from employee");
			out.println("<center><table border=1>");
			out.println("<tr><th>Emp ID </th><th>Employee Name</th><th>Designation</th><th>Joinnig Date</th><th>Salary</th><th>Edit</th><th>Delete</th></tr>");
			
			while(rs.next())
			{
				 out.println("<tr><td>"+rs.getInt(1)+" </td>");
				 out.println("<td>"+rs.getString(2)+ "</td>");
				 out.println("<td>"+rs.getString(3)+ "</td>");
				 out.println("<td>"+rs.getString(4)+ "</td>");
				 out.println("<td>"+rs.getFloat(5)+ "</td>");
				 out.println("<td> <a href=edit.jsp?id="+rs.getInt(1)+ "> Edit </a> </td>");
				 out.println("<td> <a href=./DeleteServlet?id="+rs.getInt(1)+ "> Delete </a> </td></tr>");
			}
			out.println("</table></center>");			
		%>
	</form>
		
	</body>
</html>

 

File Name : edit.jsp

File Location : Tomcat_Apache\webapps\gtu28\

<%@ page import="java.sql.*;" %>
<html>
	<head>
		<title>GTU WTAD Practical 28</title>
	</head>
	<body>
	
	<h2 align="center"> GTU WTAD Practical 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. Field Name   Field Type EmpId Integer Empname Varchar Emp_desig Varchar Emp_J_Date Varchar Emp_Salary Numeric </h2>
	<br><br>
		
		<form method="post" action="./UpdateServlet">
		
		<%
			
			
			Class.forName("com.mysql.jdbc.Driver");
            Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/mydatabase","root","");
            Statement st=con.createStatement();
            
            ResultSet rs=st.executeQuery("Select * from employee where EmpId=" + request.getParameter("id") );
			out.println("<center><table border=1>");
			out.println("<tr><th>Field</th><th>Details</th></tr>");
			
			if(rs.next())
			{
				 out.println("<tr><td>Employee ID</td><td> <input name=\"EmpId\" readonly type=\"text\" value="+rs.getInt(1)+"> </td></tr>");
				 out.println("<tr><td>Employee Name</td><td> <input name=\"Empname\" type=\"text\" value="+rs.getString(2)+ "> </td></tr>");
				 out.println("<tr><td>Employee Designation</td><td> <input name=\"Emp_desig\" type=\"text\" value="+rs.getString(3)+ "></td></tr>");
				 out.println("<tr><td>Employee Joining Date</td><td> <input name=\"Emp_J_Date\" type=\"text\" value="+rs.getString(4)+ "></td></tr>");
				 out.println("<tr><td>Employee Salary</td><td> <input name=\"Emp_Salary\" type=\"text\" value="+rs.getFloat(5)+ "></td></tr>");
			}
			
			out.println("</table>");			
		%>
		
		<tr><td colspan="2"> <input type="submit" value="Update"> </td></tr>
		</table>
		
	</form>
		
	</body>
</html>

 

File Name : InsertServlet.java

File Location : Tomcat_Apache\webapps\gtu28\WEB-INF\classes

/*
		GTU WTAD Practical 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. Field Name   Field Type EmpId Integer Empname Varchar Emp_desig Varchar Emp_J_Date Varchar Emp_Salary Numeric
*/

import java.io.*;
import java.util.*;
import javax.servlet.*;
import javax.servlet.http.*;
import java.sql.*;

public class InsertServlet extends HttpServlet
{
   
	
	public void doPost(HttpServletRequest request,HttpServletResponse response) throws ServletException,IOException
	{
		
		PrintWriter out=response.getWriter();
		response.setContentType("text/html");      
		
		out.println("<html><head><title>GTU WTAD Practical 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. Field Name   Field Type EmpId Integer Empname Varchar Emp_desig Varchar Emp_J_Date Varchar Emp_Salary Numeric </title></head><body></h1></center><br><br>");
 
		HttpSession session = request.getSession();
		
		try
		{
 
            Class.forName("com.mysql.jdbc.Driver");
            Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/mydatabase","root","");
            Statement st=con.createStatement();
			
			String EmpId = request.getParameter("EmpId");
			String Empname = request.getParameter("Empname");
			String Emp_desig = request.getParameter("Emp_desig");
			String Emp_J_Date = request.getParameter("Emp_J_Date");
			String Emp_Salary = request.getParameter("Emp_Salary");
			
			 
			String insertSQL = "insert into employee values("+ EmpId + ",'" + Empname + "','"+ Emp_desig +"','" + Emp_J_Date+ "'," + Emp_Salary+ ")";
			
			int return_value = st.executeUpdate(insertSQL);
			
			if(return_value>0)
			{
				session.setAttribute("msg","Data Inserted Succesfully");
				response.sendRedirect("./");
			}
			else
			{
				session.setAttribute("msg","Data Insersion Error");
				response.sendRedirect("./");
			}
			
			 
			
			    
       }
       catch (Exception e)
       {
          out.println("The error is:" + e.getMessage());
       }
		
	}
}

 

File Name : UpdateServlet.java

File Location : Tomcat_Apache\webapps\gtu28\WEB-INF\classes

/*
		GTU WTAD Practical 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. Field Name   Field Type EmpId Integer Empname Varchar Emp_desig Varchar Emp_J_Date Varchar Emp_Salary Numeric
*/

import java.io.*;
import java.util.*;
import javax.servlet.*;
import javax.servlet.http.*;
import java.sql.*;

public class UpdateServlet extends HttpServlet
{
	public void doPost(HttpServletRequest request,HttpServletResponse response) throws ServletException,IOException
	{
		PrintWriter out=response.getWriter();
		response.setContentType("text/html");      
 
		HttpSession session = request.getSession();
		
		try
		{
 
            Class.forName("com.mysql.jdbc.Driver");
            Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/mydatabase","root","");
            Statement st=con.createStatement();
			
			String EmpId = request.getParameter("EmpId");
			String Empname = request.getParameter("Empname");
			String Emp_desig = request.getParameter("Emp_desig");
			String Emp_J_Date = request.getParameter("Emp_J_Date");
			String Emp_Salary = request.getParameter("Emp_Salary");
			 
			String updateSQL = "update employee set Empname='" + Empname + "',Emp_desig='" + Emp_desig + "',Emp_J_Date='" + Emp_J_Date + "',Emp_Salary=" + Emp_Salary + " where EmpId=" + EmpId;
			
			int return_value = st.executeUpdate(updateSQL);
			
			if(return_value>0)
			{
				session.setAttribute("msg","Data Updated Succesfully");
				response.sendRedirect("./");
			}
			else
			{
				session.setAttribute("msg","Data Updation Failed, Try Again");
				response.sendRedirect("./");
			}
       }
       catch (Exception e)
       {
          out.println("The error is:" + e.getMessage());
       }
	}
}

 

File Name : DeleteServlet.java

File Location : Tomcat_Apache\webapps\gtu28\WEB-INF\classes

/*
		GTU WTAD Practical 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. Field Name   Field Type EmpId Integer Empname Varchar Emp_desig Varchar Emp_J_Date Varchar Emp_Salary Numeric
*/

import java.io.*;
import java.util.*;
import javax.servlet.*;
import javax.servlet.http.*;
import java.sql.*;

public class DeleteServlet extends HttpServlet
{
	public void doGet(HttpServletRequest request,HttpServletResponse response) throws ServletException,IOException
	{
		//PrintWriter out=response.getWriter();
		response.setContentType("text/html");      
 
		HttpSession session = request.getSession();
		
		try
		{
 
            Class.forName("com.mysql.jdbc.Driver");
            Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/mydatabase","root","");
            Statement st=con.createStatement();
			String EmpId = request.getParameter("id");
			 
			String deleteSQL = "delete from employee where EmpId=" + EmpId;
			int return_value = st.executeUpdate(deleteSQL);
			
			if(return_value>0)
			{
				session.setAttribute("msg","Data Deleted Succesfully");
				response.sendRedirect("./");
			}
			else
			{
				session.setAttribute("msg","Data Deletion Failed, Try Again");
				response.sendRedirect("./");
			}
			
		
       }
       catch (Exception e)
       {
          //out.println("The error is:" + e.getMessage());
       }
	}
}

 

File Name : web.xml

File Location : Tomcat_Apache\webapps\gtu28\WEB-INF\

<?xml version="1.0" encoding="ISO-8859-1"?>
<web-app xmlns="http://java.sun.com/xml/ns/javaee"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://java.sun.com/xml/ns/javaee
                      http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd"
  version="3.0"
  metadata-complete="true">  
  
  <display-name>GTU WTAD Practical 28  </display-name>
  <description>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. Field Name   Field Type EmpId Integer Empname Varchar Emp_desig Varchar Emp_J_Date Varchar Emp_Salary Numeric </description>
  
  <servlet>
    <servlet-name>InsertServlet</servlet-name>
    <servlet-class>InsertServlet</servlet-class>
  </servlet>

  <servlet-mapping>
    <servlet-name>InsertServlet</servlet-name>
      <url-pattern>/InsertServlet</url-pattern>
  </servlet-mapping> 
  
  <servlet>
    <servlet-name>DeleteServlet</servlet-name>
    <servlet-class>DeleteServlet</servlet-class>
  </servlet>

  <servlet-mapping>
    <servlet-name>DeleteServlet</servlet-name>
      <url-pattern>/DeleteServlet</url-pattern>
  </servlet-mapping> 
    
  <servlet>
    <servlet-name>UpdateServlet</servlet-name>
    <servlet-class>UpdateServlet</servlet-class>
  </servlet>

  <servlet-mapping>
    <servlet-name>UpdateServlet</servlet-name>
      <url-pattern>/UpdateServlet</url-pattern>
  </servlet-mapping> 
  
</web-app>

 

Output 1

GTU WTAD Practical 28

 

Output 2

GTU WTAD Practical 28

 

Output 3

GTU WTAD Practical 28

 

Output 4

GTU WTAD Practical 28

 

Leave a Reply