Online-Academy
Look, Read, Understand, Apply

CRUD Using Servlet

Performing CRUD operation

CRUD means create, read, update and delete. CRUD operation is performed on database. Following program shows how to performe CRUD operation using Java Servlet.

Steps
  1. Create Connection with database: In this program user defined connection class is created. The constructor of the connnection class takes database name, host name, user name and password as arguments. This connection class has methods to return Connection object, to return Statement object, to return PerparedStatement object and to close the Connection.
  2. A page with three links is created: each link points to separate a page:
    • First link references to page for inserting data
    • Second link references to page to display records from database table
    • Third link references to page to delete records from database
  3. A simple HTML form is created to insert data. Data from the form will go to servlet name insert.
  4. Servlet named show is used to display contains of the database table, each record is attached with edit and delete links. If delete link is clicked that record will be deleled. And if edit link is clicked that records can be edited, a form to edit data will be displayed.
  5. A simple HTML form is created which takes a string as input, that string is sent to Servlet named delete. There if the string sent is found in database, respective record will be deleted
  6. A simple HTML form is created which takes a string as input, that string is passed to delete servlet using post method, if that passed string is found in database, record associated with it will be deleted.
Home page
<!DOCTYPE html>
<html>
<head>
<meta charset="ISO-8859-1">
<title>Insert title here</title>
</head>
<body>
<ul>
	<li><a href='Input.html'>Insert New Record!!!</a></li>
	<li><a href='/web_crud/show'>Show Records!!!</a></li>
	<li><a href='Delete.html'>Delete Record!!!</a></li>
</ul>
</body>
</html>
HTML from to Insert Record
<!DOCTYPE html>
<html>
<head>
<meta charset="ISO-8859-1">
<title>Database Operation</title>
</head>
<body>
<form method='post' action='/web_crud/insert'>
<p>Name</p>
<p><input type='text' name='cname'></p>
<p>Address</p>
<p><input type='text' name='caddress'></p>
<p><input type='submit' name='submit'></p>
</form>
</body>
</html>
HTML from to Delete Record
<!DOCTYPE html>
<html>
<head>
<meta charset="ISO-8859-1">
<title>Insert title here</title>
</head>
<body>
<form method='post' action='/web_crud/delete'>
	<p>Enter Name <input type='text' name='cname'></p>
	<p><input type='submit'></p>
</form>
</body>
</html>
Servlet to insert record

import java.io.PrintWriter;
import java.io.IOException;
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.PreparedStatement;
import java.sql.SQLException;

import web_crud.connection;

public class insert extends HttpServlet {
	private static final long serialVersionUID = 1L;
    connection c; 
	public insert() {
        super();       
    }
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		response.getWriter().append("Served at: ").append(request.getContextPath());
		doPost(request,response);
	}
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		//doGet(request, response);
		String name = request.getParameter("cname");
		String address = request.getParameter("caddress");
		String sql = "insert into customer(cname,caddress) values(?,?)";
		c = new connection("test","jdbc:mysql://localhost:3306/","root",""); 
		PreparedStatement pstmt = c.getPreparedStatement(sql);
		PrintWriter pw = response.getWriter();
		pw.write("Hello Insert!!!");
		try {
			pstmt.setString(1, name);
			pstmt.setString(2, address);
			pstmt.execute();
			pw.write("Inserted!!! <p><a href='Home.html'>Back To Home!!!</a></p>");
			pstmt.close();
			c.closeConnection();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}
Servlet to Display Records
import java.io.PrintWriter;
import java.io.IOException;
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.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import web_crud.connection;

public class show extends HttpServlet {
	connection c=null;
	PreparedStatement pstmt = null;
	ResultSet rs = null; 
	private static final long serialVersionUID = 1L;
    public show() {
        super();   
    }
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		response.getWriter().append("Served at: ").append(request.getContextPath());
		String sql = "select cname,caddress from customer";
		c = new connection("test","jdbc:mysql://localhost:3306/","root","");
		PreparedStatement pstmt = c.getPreparedStatement(sql);
		PrintWriter pw = response.getWriter();
		try {
			ResultSet rs = pstmt.executeQuery();
			while(rs.next()) {
				pw.write("<p>"+rs.getString(1)+"  "+rs.getString(2)+
						" <a href='/web_crud/delete?cname="+rs.getString(1)+"'>delete</a>"+
						" <a href='/web_crud/edit?cname="+rs.getString(1)+"&caddress="+rs.getString(2)+"'>edit</a>");
			}
			pstmt.close();
			pw.write("<p><a href='Home.html'>Back To Home!!!</a></p>");
		c.closeConnection();
		} catch (SQLException e) {
			e.printStackTrace();
		} 
	}
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		doGet(request, response);
	}
}
Servlet to Delete Records
import java.io.IOException;
import java.io.PrintWriter;
import web_crud.connection;
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.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class delete extends HttpServlet {
	private static final long serialVersionUID = 1L;
	connection c=null;
	PreparedStatement pstmt = null;
	ResultSet rs = null; 

    public delete() {
        super();
    }
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		response.getWriter().append("Served at: ").append(request.getContextPath());
		PrintWriter pw = response.getWriter();
		String sql = "delete from customer where cname=?";
		c = new connection("test","jdbc:mysql://localhost:3306/","root","");
		PreparedStatement pstmt = c.getPreparedStatement(sql);
		try {
			pstmt.setString(1, request.getParameter("cname"));
			pw.write("Bye: "+request.getParameter("cname"));
			if(pstmt.executeUpdate()==1)
				pw.write("Deleted!!! <p><a href='Home.html'>Back to Home</a></p>");
			else {pw.write("<p><b>Something is wrong!!!</b><a href='Home.html'>Back to Home</a></p>");}
			pstmt.close();
			c.closeConnection();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		doGet(request, response);
		PrintWriter pw = response.getWriter();
		String sql = "delete from customer where cname=?";
		c = new connection("test","jdbc:mysql://localhost:3306/","root","");
		PreparedStatement pstmt = c.getPreparedStatement(sql);
		try {
			pstmt.setString(1, request.getParameter("cname"));
			if(pstmt.execute()==true)
				pw.write("<a href='Home.html'>Back to Home</a>");
			else {pw.write("<b>Something is wrong!!!</b><p><a href='Home.html'>Back to Home</a></p>");}
			pstmt.close();
			c.closeConnection();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}
Servlet to Create edit form
import java.io.PrintWriter;
import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
public class edit extends HttpServlet {
	private static final long serialVersionUID = 1L;
    public edit() {
        super();
    }
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		response.getWriter().append("Served at: ").append(request.getContextPath());
		PrintWriter pw = response.getWriter();
		pw.write("<html><body><form method='post' action='/web_crud/edit_store'>");
		pw.write("<p><input type='text' name='cname' value='"+request.getParameter("cname")+"'></p>");
		pw.write("<p><input type='text' name='caddress' value='"+request.getParameter("caddress")+"'></p>");
		pw.write("<input type='submit'></body></html>");
		pw.write("<p><a href='Home.html'>Back To Home!!!</a></p>");
	}
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		doGet(request, response);
	}

}
Servlet to edit and store data
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import web_crud.connection;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
public class edit_store extends HttpServlet {
	private static final long serialVersionUID = 1L;
	connection c; 
    public edit_store() {
        super();
    }
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
	}
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		doGet(request, response);
		String name = request.getParameter("cname");
		String address = request.getParameter("caddress");
		String sql = "update customer set cname = ?,caddress= ? where cname=?";
		c = new connection("test","jdbc:mysql://localhost:3306/","root",""); 
		PreparedStatement pstmt = c.getPreparedStatement(sql);
		PrintWriter pw = response.getWriter();
		try {
			pstmt.setString(1, name);
			pstmt.setString(2, address);
			pstmt.setString(3, name);
			pstmt.execute();
			pw.write("Updated!!! <a href='Home.html'>Back To home!!!</a>");
			pstmt.close();
			c.closeConnection();
		} catch (SQLException e) {
			e.printStackTrace();
		}	
	}
}
Deployment Descriptor:web.xml
<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://xmlns.jcp.org/xml/ns/javaee" xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_4_0.xsd" id="WebApp_ID" version="4.0">
  <display-name>web_crud</display-name>
  <servlet>
  	<servlet-name>insert</servlet-name>
  	<servlet-class>insert</servlet-class>
  </servlet>
  <servlet-mapping>
  <servlet-name>insert</servlet-name>
  <url-pattern>/insert</url-pattern>
  </servlet-mapping>
  <servlet>
  	<servlet-name>show</servlet-name>
  	<servlet-class>show</servlet-class>
  </servlet>
  <servlet-mapping>
  <servlet-name>show</servlet-name>
  <url-pattern>/show</url-pattern>
  </servlet-mapping>
  <servlet>
  	<servlet-name>delete</servlet-name>
  	<servlet-class>delete</servlet-class>
  </servlet>
  <servlet-mapping>
  <servlet-name>delete</servlet-name>
  <url-pattern>/delete</url-pattern>
  </servlet-mapping>
  <servlet>
  	<servlet-name>edit</servlet-name>
  	<servlet-class>edit</servlet-class>
  </servlet>
  <servlet-mapping>
  <servlet-name>edit</servlet-name>
  <url-pattern>/edit</url-pattern>
  </servlet-mapping>
  <servlet>
  	<servlet-name>edit_store</servlet-name>
  	<servlet-class>edit_store</servlet-class>
  </servlet>
  <servlet-mapping>
  <servlet-name>edit_store</servlet-name>
  <url-pattern>/edit_store</url-pattern>
  </servlet-mapping>
</web-app>