GTU WTAD Practical 29 : java insert update delete example

GTU WTAD Practical 29 : Develop a Java application to perform the database driven operation like insert, Delete, Update and selection using PreparedStatement. To perform the above operations use the table from above exercise.

GTU WTAD Practical 29

This Program Explains How you can connect with MySQL Database using JDBC Prepared Statements. Prepared Statements Example for Insert, Update, Delete.

Jump to GTU WTAD Practical 30

File Name : index.jsp

File Location : Tomcat_Apache\webapps\gtu29\

<%@ page import="java.sql.*;" %>
<html>
	<head>
		<title>GTU WTAD Practical 29</title>
	</head>
	<body>
	
	<h2 align="center"> GTU WTAD Practical 29 : Develop a Java application to perform the database driven 
operation like insert, Delete, Update and selection using PreparedStatement. To perform 
the above operations use the table from above exercise. </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\gtu29\

<%@ page import="java.sql.*;" %>
<html>
	<head>
		<title>GTU WTAD Practical 29</title>
	</head>
	<body>
	
	<h2 align="center"> GTU WTAD Practical 29 : Develop a Java application to perform the database driven 
operation like insert, Delete, Update and selection using PreparedStatement. To perform 
the above operations use the table from above exercise.</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>");
			
			while(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\gtu29\WEB-INF\classes

/*
		GTU WTAD Practical 29 : Develop a Java application to perform the database driven 
operation like insert, Delete, Update and selection using PreparedStatement. To perform 
the above operations use the table from above exercise.
*/

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(?,?,?,?,?)";
			PreparedStatement ps = con.prepareStatement(insertSQL);
			ps.setInt(1,Integer.parseInt(EmpId));
			ps.setString(2,Empname);
			ps.setString(3,Emp_desig);
			ps.setString(4,Emp_J_Date);
			ps.setInt(5, Integer.parseInt(Emp_Salary));
			ps.executeUpdate();
			
			session.setAttribute("msg","Data Inserted Succesfully");
			response.sendRedirect("./");
			 
       }
       catch (Exception e)
       {
          out.println("The error is:" + e.getMessage());
       }
	}
}

 

File Name : UpdateServlet.java

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

/*
		GTU WTAD Practical 29 : Develop a Java application to perform the database driven operation like insert, Delete, Update and selection using PreparedStatement. To perform the above operations use the table from above exercise.
*/

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 deleteSQL = "update employee set Empname=?,Emp_desig=?,Emp_J_Date=?,Emp_Salary=? where EmpId=?";
			PreparedStatement ps = con.prepareStatement(deleteSQL);
			ps.setString(1,Empname);
			ps.setString(2,Emp_desig);
			ps.setString(3,Emp_J_Date);
			ps.setFloat(4,Float.parseFloat(Emp_Salary));
			ps.setInt(5,Integer.parseInt(EmpId));
			ps.executeUpdate();
			
			session.setAttribute("msg","Data Updated Succesfully");
			response.sendRedirect("./");
       }
       catch (Exception e)
       {
          out.println("The error is:" + e.getMessage());
       }
	}
}

 

File Name : DeleteServlet.java

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

/*
		GTU WTAD Practical 29 : Develop a Java application to perform the database driven operation like insert, Delete, Update and selection using PreparedStatement. To perform the above operations use the table from above exercise.
*/

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=?";
			PreparedStatement ps = con.prepareStatement(deleteSQL);
			ps.setInt(1,Integer.parseInt(EmpId));
			ps.executeUpdate();
			
			session.setAttribute("msg","Data Deleted Succesfully");
			response.sendRedirect("./");
       }
       catch (Exception e)
       {
          //out.println("The error is:" + e.getMessage());
       }
	}
}

 

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