CRUD Operations using JSP

In this article, we are going to learn about CRUD (CREATE, READ, UPDATE and DELETE Operations) operations using JSP, Bootstrap and MYSQL.
By: Vanka Manikanth, on 09 MAR 2017

As requested by the students we are providing the CRUD operations using JSP, Bootstrap and MYSQL.

The common operations that are done for any web application are CRUD, which is CREATE, READ UPDATE & DELETE. In order to develop, learn one should be very stronger to handle these operations. And it is better if one do this operations using JSP instead of Servlets. So the main difference between Servlet and JSP will be covered soon. Firstly let's get into the current one.

CRUD - CREATE, READ, UPDATE and DELETE Operations

CREATE - Here, CREATE in the sense to SAVE, the inputs which we initially want to store in the database from the User.

READ - Soon as we CREATE or SAVE the details in the database, we want them to access in our USER INTERFACE. So for that we have to read the saved values (which we done earlier, i.e CREATE). This READ can be achieved through a SELECT query.

Note: If you want only the detail which is saved at that point then you have to make sure that you change the SELECT QUERY to the current processing USERID.

UPDATE - To UPDATE the detail that is SAVED in the database, you have to CREATE a dummy page of your MAIN page with same details which are mentioned in the registration or a particular page and whenever the user clicks on EDIT button or link we should redirect to that dummy page with the values which are requested by the user to EDIT & UPDATE.

DELETE - DELETE operation delete the particular record which is requested from the database.

Index.jsp

Here, we are taking the inputs from the user and triggering the action to action.jsp.

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1" import="com.includehelp.*, java.util.*"%>
<!DOCTYPE html>
<html>
	<head>
		<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
		<title>CRUD</title>
		<link rel="stylesheet" href="css/bootstrap.css">
	</head>
	<body>
		<div class="container">
		<div class="panel-primary">
		<div class="panel-heading">
		<h3 class="panel-title">CRUD OPERATIONS IN JSP with Bootstrap Design</h3>
		</div>
		<div class="panel-body">
		<form action="action.jsp" class="form-horizontal" method="post">
		<div class="form-group col-md-6">
			<label class="control-label" for="UserID">USER ID </label>
			<input type="text" id="UserID" class="form-control" name="UserID" placeholder="ENTER YOUR UserID" required="required">
		</div>
		<div class="clearfix"></div>
		<div class="form-group col-md-6">
			<label for="email" class="control-label">EMAIL ID</label>
			<input type="email" id="email" class="form-control" name="email" placeholder="ENTER YOUR EMAIL" required="required">
		</div>
		<div class="clearfix"></div>
		<div class="form-group col-md-6">
			<label for="department" class="control-label">DEPARTMENT</label>
			<select id="department" required="required" class="form-control" name="department">
				<option value="">Select</option>
				<option value="MARKETING">MARKETING</option>
				<option value="TESTING">TESTING</option>
				<option value="DEVELOPMENT">DEVELOPMENT</option>
				<option value="TRAINING">TRAINING</option>
			</select>
		</div>
		<div class="clearfix"></div>
		<div class="form-group col-md-6">
			<label for="phone" class="control-label">PHONE</label>
			<input type="tel" id="phone" class="form-control" name="phone" placeholder="ENTER YOUR PHONE" required="required">
		</div>
		<div class="clearfix"></div>
		<div class="form-group col-md-6">
			<button class="btn btn-success">REGISTER</button>
		</div>
		</form>
		</div>
		</div>
		</div>

		<% 
		Operations opObject = new Operations();
	
		List<UserPOJO> list = new ArrayList();
		
		list = opObject.getListUsers();
		System.out.println(list.size());
		if(!(list.isEmpty())){
		%>
		<table class="table bordered">
		<thead><tr><th></th><th>USER ID</th><th>EMAIL</th><th>PHONE</th><th>DEPARTMENT</th></tr></thead>
		<% for(UserPOJO p : list){%>
		<tr><td><input type="radio" name="rdValues" id="rdId" value="<%=p.getUserid()+"~"+p.getEmail()+"~"+p.getDepartment()+"~"+p.getPhone()%>"></td><td><%=p.getUserid()%></td><td><%=p.getEmail()%></td><td><%=p.getPhone()%></td><td><%=p.getDepartment()%></td></tr>
		<% }%>
		</table>
		
		<button class="btn btn-primary" name="edit" value="edit" onclick="editThis()">EDIT</button>
		<button class ="btn btn-primary" name="delete" value="delete" onclick="deleteThis()">DELETE</button>
	
	<%}
	%>
	<script>
	function editThis(){
		var edit;
		edit = document.getElementsByName("rdValues");
		
		var selVal="";
		for(var c=0;c<edit.length;c++){
			if(edit[c].checked == true){
				selVal+=edit[c].value;
			break;		
			}
		}
		
		window.location.href="edit.jsp?values="+selVal;
	}
	function deleteThis(){
		
		var values;
		values = document.getElementsByName("rdValues");
		var selVal="";
		for(var c=0;c<values.length;c++){
			if(values[c].checked == true){
				selVal+=values[c].value;
			break;		
			}
		}
		window.location.href="delete.jsp?userid="+selVal;
		
	}
	</script>

		<script src="js/jquery.min.js"></script>
		<script src="js/bootstrap.js"></script>
	</body>
</html>

UserPOJO.java

This is a class providing the setter and getter methods.

package com.includehelp;

public class UserPOJO {
	private String userid;
	private String email;
	private long phone;
	private String department;
	
	public String getUserid() {
		return userid;
	}
	public void setUserid(String userid) {
		this.userid = userid;
	}
	public String getEmail() {
		return email;
	}
	public void setEmail(String email) {
		this.email = email;
	}
	public long getPhone() {
		return phone;
	}
	public void setPhone(long phone) {
		this.phone = phone;
	}
	public String getDepartment() {
		return department;
	}
	public void setDepartment(String department) {
		this.department = department;
	}
}

Operations.java

The service logic and database connection is created here.

package com.includehelp;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class Operations implements OperationsInterface {
	
	static String message ="";
	@Override
	public String save(UserPOJO uObject) {
		int flag =0;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/MYDB","root","new_password");	
			PreparedStatement ps = con.prepareStatement("INSERT INTO USERDETAILS VALUES(?,?,?,?);");
			ps.setString(1, uObject.getUserid());
			ps.setString(2, uObject.getEmail());
			ps.setString(3, uObject.getDepartment());
			ps.setLong(4, uObject.getPhone());
		
			flag =	ps.executeUpdate();
			if(flag!=0){
				message ="Saved SuccessFully";
			}
			
		} catch (Exception e) {
			System.out.println(e);
		}
		return message;
	}

	@Override
	public List<UserPOJO> getListUsers() {
		System.out.println("inside getlist");
		List <UserPOJO> list = new ArrayList<UserPOJO>();
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/MYDB","root","new_password");	
			PreparedStatement ps = con.prepareStatement("SELECT * FROM USERDETAILS;");
			ResultSet rs = ps.executeQuery();
			while(rs.next()){
				UserPOJO uObject = new UserPOJO();
				uObject.setUserid(rs.getString(1));
				uObject.setEmail(rs.getString(2));
				uObject.setDepartment(rs.getString(3));
				uObject.setPhone(rs.getLong(4));
							
				list.add(uObject);				
			}
			con.close();
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;		
	}
	
	public  String Update(UserPOJO uObject){
		 int status=0;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/MYDB","root","new_password");	
			PreparedStatement ps = con.prepareStatement("UPDATE USERDETAILS SET EMAIL=?,DEPARTMENT=?,PHONE=? WHERE USERID=?;");
			
			ps.setString(1, uObject.getEmail());
			ps.setString(2, uObject.getDepartment());
			ps.setLong(3, uObject.getPhone());
			ps.setString(4, uObject.getUserid());

			status =	ps.executeUpdate();
			if(status!=0){
				message ="Updated SuccessFully";
			}
			
		} catch (Exception e) {
			System.out.println(e);
		}
		
		return message;
	}
	
	public  String deleteUser(String userid){
		int status=0;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/MYDB","root","new_password");	
			PreparedStatement ps = con.prepareStatement("DELETE FROM USERDETAILS WHERE USERID=?;");
			
			
			ps.setString(1, userid);

			status =	ps.executeUpdate();
			if(status!=0){
				message ="Record Deleted SuccessFully";
			}
			
		} catch (Exception e) {
			System.out.println(e);
		}
		return message;
	}
}

Action.jsp

Here we are setting the input values to DTO (POJO) and sending the object to SERVICE method where we implements SAVE.

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1" import="com.includehelp.*, java.util.*"%>
<!DOCTYPE html>
<html>
	<head>
		<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
		<title>CRUD</title>
		<link rel="stylesheet" href="css/bootstrap.css">
	</head>
	
	<body>
		<%
		String userid = request.getParameter("UserID");
		String email = request.getParameter("email");
		String tel = request.getParameter("phone");
		String department = request.getParameter("department");
		long phone = Long.parseLong(tel);


		UserPOJO uObject = new UserPOJO();
		uObject.setUserid(userid);
		uObject.setEmail(email);
		uObject.setDepartment(department);
		uObject.setPhone(phone);


		Operations opObject = new Operations();
		String message = opObject.save(uObject);%>


		<%if(message!=null){%>
		<jsp:include page="index.jsp"></jsp:include>
		<% }
			%>
	</body>
</html>

Edit.jsp

When the user clicks on EDIT button we the values binded to the radio button are sent to edit.jsp and here we took another JSP because we have to get the values from the database and bind those values to the input boxes. USERID is the primarykey, so it cannot be editable.

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>
<!DOCTYPE html>
<html>
	<head>
		<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
		<title>Insert title here</title>
		<link rel="stylesheet" href="css/bootstrap.css">
	</head>

	<body>

	<%
	String values = request.getParameter("values");
	String split[] =values.split("~");
	String userid = split[0];
	String email =split[1];
	String department = split[2];
	String phone = split[3];
	%>
	<div class="container">
		<div class="panel-primary">
			<div class="panel-heading">
				<h3 class="panel-title">CRUD OPERATIONS IN JSP with Bootstrap Design</h3>
			</div>
			<div class="panel-body">
				<form action="update.jsp" class="form-horizontal" method="post">
				<div class="form-group col-md-6">
					<label class="control-label" for=UserID>USER ID</label>
					USER NAME CANNOT BE MODIFIED
					<input type="text" id="UserID" class="form-control" name="UserID" placeholder="ENTER YOUR USERID" readonly required="required" value="<%=userid%>">
				</div>
				<div class="clearfix"></div>
				<div class="form-group col-md-6">
					<label for="email" class="control-label">EMAIL ID</label>
					<input type="email" id="email" value ="<%=email%>"class="form-control" name="email" placeholder="ENTER YOUR EMAIL" required="required">
				</div>
				<div class="clearfix"></div>
				<div class="form-group col-md-6">
					<label for="department" class="control-label">DEPARTMENT</label>
					<select id="department" required="required" class="form-control" name="department">
						<option value="">Select</option>
						<option value="MARKETING">MARKETING</option>
						<option value="TESTING">TESTING</option>
						<option value="DEVELOPMENT">DEVELOPMENT</option>
						<option value="TRAINING">TRAINING</option>
					</select>
				</div>
				<div class="clearfix"></div>
				<div class="form-group col-md-6">
					<label for="phone" class="control-label">PHONE</label>
					<input type="tel" id="phone" class="form-control" name="phone" placeholder="ENTER YOUR PHONE" value ="<%=phone %>" required="required">
				</div>
				<div class="clearfix"></div>
				<div class="form-group col-md-6">
					<button class="btn btn-success">UPDATE</button>
				</div>
				</form>
			</div>
		</div>
	</div>
	<script src="js/jquery.min.js"></script>
	<script src="js/bootstrap.js"></script>
	</body>
</html>

Update.jsp

Here we are taking the values where the edit is done and sending the values with respect to userid, which is primary key.

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1" import="com.includehelp.*, java.util.*"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
	<head>
		<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
		<title>CRUD</title>
	</head>
	
	<body>
		<jsp:include page="index.jsp"></jsp:include>

		<%
		String userid = request.getParameter("UserID");
		String email = request.getParameter("email");
		String tel = request.getParameter("phone");
		String department = request.getParameter("department");
		long phone = Long.parseLong(tel);


		UserPOJO uObject = new UserPOJO();
		uObject.setUserid(userid);
		uObject.setEmail(email);
		uObject.setDepartment(department);
		uObject.setPhone(phone);

		Operations opObject = new Operations();
		String message = opObject.Update(uObject);
		if(message!=null){%>
			<div class="clearfix"></div>
			<div class="alert alert-success col-md-4">
			<%=message%></div>

		<%}
		%>
	</body>
</html>

Delete.jsp

Getting the record and sending it to the service logic and again the page is included at index.jsp.

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1" import="com.includehelp.*, java.util.*"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
	<head>
		<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
		<title>CRUD</title>
	</head>
	
	<body>
		<%
		String value = request.getParameter("userid");
		String split[] =value.split("~");
		String userid = split[0];

		Operations opObject = new Operations();
		String message = opObject.deleteUser(userid);
		if(message!=null){%>
		<div class="clearfix"></div>
		<jsp:include page="index.jsp"></jsp:include>
		<span class="alert alert-success col-md-4"><%=message%></span>
		<%}

		%>
	</body>
</html>

Output:

Comments and Discussions!

Load comments ↻