Day: August 28, 2020

Watch “Servlet Part 21 | CRUD operation in Servlet Using JDBC (Gujarati)” on YouTube

by
#CRUD #Servlet #JDBC #MySql #Eclipse #MySqlWorkbench #ApacheTomcat #Jdk #Java

This video features the CRUD operation using JDBC via servlet only, no JSP page is used to perform any database operation.
CRUD is Create, Read, Update & Delete.

Index.html

<!DOCTYPE html>
<html>
<head>
<meta charset="ISO-8859-1">
<title>Servlet with JDBC</title>
</head>
<body>
<form action="AddEmp" method="post">
<h1> Employee Management</h1>
<p> Enter Emp. No : <input type="text" name="txtEno"> </p>
<p> Enter Emp. Name : <input type="text" name="txtEname"> </p>
<p> Enter Emp. Salary : <input type="text" name="txtEsalary"> </p>

<p> DB Operation :
<select name="op">
    <option value="1">Add</option>
    <option value="2">Update</option>
    <option value="3">Delete</option>
    <option value="4">Display</option>
</select>
</p>

<p> <input type="submit" value="Submit"> </p>
</form>
</body>
</html>

AddEmp.java

package servletWithJDBC;

import java.io.IOException;
import java.io.PrintWriter;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.sql.*;

@WebServlet("/AddEmp")
public class AddEmp extends HttpServlet 
{
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException 
	{			
		response.setContentType("text/html");
		PrintWriter pw = response.getWriter();
		String op = request.getParameter("op");
		Connection conn;
		PreparedStatement pst=null; 
		String url,username,password,qry="";
		url = "jdbc:mysql://localhost:3306/raviroza";
		username="root";
		password="ravi";	
		
		int eno;
		String ename="";
		float esalary;
		try 
		{
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection(url,username,password);			
			
			if(op.equals("1"))
			{
				eno = Integer.parseInt(request.getParameter("txtEno"));
				ename = request.getParameter("txtEname");
				esalary = Float.parseFloat(request.getParameter("txtEsalary"));
				
				qry = "insert into emp values (?,?,?)";
				pst = conn.prepareStatement(qry);
				pst.setInt(1, eno);
				pst.setString(2, ename);
				pst.setFloat(3, esalary);
				pst.executeUpdate();
			}
			else if(op.equals("2"))
			{
				eno = Integer.parseInt(request.getParameter("txtEno"));
				ename = request.getParameter("txtEname");
				esalary = Float.parseFloat(request.getParameter("txtEsalary"));
				
				qry = "update emp set ename=?,esalary=? where eno=?";
				pst = conn.prepareStatement(qry);				
				pst.setString(1, ename);
				pst.setFloat(2, esalary);
				pst.setInt(3, eno);
				pst.executeUpdate();
			}
			else if(op.equals("3"))
			{
				eno = Integer.parseInt(request.getParameter("txtEno"));
				qry = "delete from emp where eno=?";	
				pst = conn.prepareStatement(qry);								
				pst.setInt(1, eno);
				pst.executeUpdate();
			}								
						
			qry = "select * From Emp Order by eno desc";
			ResultSet rs = conn.prepareStatement(qry).executeQuery();
			pw.println("<table border=1>");
			pw.println("<th>Eno.</th>");
			pw.println("<th>Emp. Name</th>");
			pw.println("<th>Emp. Salary</th>");
			while(rs.next())
			{
				pw.println("<tr>");
				pw.println("<td>"+rs.getString("eno")+"</td>");
				pw.println("<td>"+rs.getString("ename")+"</td>");
				pw.println("<td>"+rs.getString("esalary")+"</td>");
				pw.println("</tr>");
			}
						
			pw.println("<h2> Employee Info. updated Successfully ! </h2>");
			pw.println("<a href='index.html'>Back</a>");
			
			pw.close();
			pst.close();
			conn.close();		
		}
		catch (ClassNotFoundException | SQLException e) 
		{
			pw.println("<h2> Driver|SQL Error "+e.toString() +"</h2>");
		}
	}
}

Overall process of example.

It is generally the table operation to create, access, manipulate, and delete the data from a database table.
Here, only a single Servlet is performing all the CRUD operation. Operation is decided by the client by selecting a proper option from list of choices.

When a client/user select the “Add” option, the internal value of that option is checked to perform the add operation, like wise when user/client select the “Update” operation the record is updated with given details.

If user/client decide to delete the record the valid number is given and the delete operation is carried.

Finally, if user decides to display all the record there is an option called “Display” to view all the details of the table.

OS : Windows 10
Jdk : Version 8
IDE : Eclipse Mars
Database : MySQL using MySql Workbench 5.2 CE
Server : Apache Tomcat 7

follow me @
https://raviroza.wordpress.com/
https://twitter.com/raviozaIT
https://www.facebook.com/ravi.oza.it

Subscribe my channel to get latest video notification https://www.youtube.com/user/ravioza101