JSP CRUD Application

This tutorial explains you how you can write CRUD application in JSP. CRUD means create, read, update and delete. This example explains you about all the steps in creating JSP CRUD application.

JSP CRUD Application

Create JSP CRUD Application and run on Tomcat 7

In this section we will discuss about how to create a simple crud application in JSP using Eclipse IDE. We will use Eclipse to compile and deploy the application on Tomcat 7 server.

CRUD is basically used in the context of database driven application where it Creates, Reads, Updates and Deletes the data of database. CRUD is also know as create, read, update and delete. The example given here will teach you how easily you can create application that insert, query, update and delete the data. Create means to insert a record into the database table, Update means edit/make changes into the existing record, and the Delete means deleting of record from the table. We can also create a CRUD application using JSP, Servlet, And the JDBC.

Here is the video tutorial of: "JSP CRUD application example"

Example

Here we will create a CRUD application using Java Servlet, JSP, and the JDBC. We will understand here the basic purpose of creating a CRUD application by a registration form where the user will registered by providing the id, first name, and last name. Update/Edit their records, and can delete their record also. For this application we will required some front end interfaces for the registering a user and updating the record. For creating the interfaces I have created the JSP pages such as user.jsp page for adding new user this page contains a form and the respective input text fields for providing their information and the submit button as well as created edit.jsp page where the selected record's ID will be showed in the readonly textfield and the other textfields for updating the respective fields. Then I have created a Java Beans named UserBean.java which contains some data members (id, fName, lName) and their setter getter methods. These setter getter methods are used for setting and getting the corresponding value respectively. Then I have created a ConnectionProvider class to connect with the existing database (in my case I am using MySQL). Then I have created a DAO class where I have written the code for adding the user, editing the user record, deleting the user record, getting the all user record, and for getting user record by ID. Then I have created a Servlet where written the code to handle insert, edit, and delete records. Finally I have created a listUser.jsp page for displaying all records and the link for updating and deleting the records. In this application you can insert, edit, or delete application one by one.

Directory Structure for this application

Source Code

Create Database table

CREATE TABLE `users` (                   
          `userid` int(5) NOT NULL,              
          `firstname` varchar(20) DEFAULT NULL,  
          `lastname` varchar(20) DEFAULT NULL,   
          PRIMARY KEY (`userid`)                 
        )

UserBean.java

package net.roseindia.bean;

public class UserBean {

    private int id;
    private String fName;
    private String lName;
        
    
    public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getfName() {
		return fName;
	}
	public void setfName(String fName) {
		this.fName = fName;
	}
	public String getlName() {
		return lName;
	}
	public void setlName(String lName) {
		this.lName = lName;
	}	
}

ConnectionProvider.java 

package net.roseindia.dbconnection;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class ConnectionProvider {

    private static Connection con = null;

    public static Connection getConnection() {
        if (con != null)
            return con;
        else {
            try {
                
                String driver = "com.mysql.jdbc.Driver";
                String url = "jdbc:mysql://localhost:3306/record";
                String user = "root";
                String password = "root";
                Class.forName(driver);
                con = DriverManager.getConnection(url, user, password);
            } catch (ClassNotFoundException cnfe) {
                System.out.println(cnfe);
            } catch (SQLException sqe) {
                System.out.println(sqe);
            } 
            return con;
        }

    }
}

UserDao.java

package net.roseindia.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import net.roseindia.bean.UserBean;
import net.roseindia.dbconnection.ConnectionProvider;

public class UserDao {

    private Connection conn;

    public UserDao() {
    	conn = ConnectionProvider.getConnection();
    }

    public void addUser(UserBean userBean) {
        try {
        	String sql = "INSERT INTO users(userid, firstname,lastname)" +
    		" VALUES (?, ?, ? )";
            PreparedStatement ps = conn.prepareStatement(sql);
            
            ps.setInt(1, userBean.getId());
            ps.setString(2, userBean.getfName());
            ps.setString(3, userBean.getlName());            
            ps.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void removeUser(int userId) {
        try {
        	String sql = "DELETE FROM users WHERE userid=?";
            PreparedStatement ps = conn
                    .prepareStatement(sql);
            ps.setInt(1, userId);
            ps.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }
      }

    public void editUser(UserBean userBean) {    	
    	try {
    		String sql = "UPDATE users SET firstname=?, lastname=?" +
            " WHERE userid=?";
            PreparedStatement ps = conn
                    .prepareStatement(sql);
            ps.setString(1, userBean.getfName());
            ps.setString(2, userBean.getlName());            
            ps.setInt(3, userBean.getId());
            ps.executeUpdate();            

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    
    public List getAllUsers() {
        List users = new ArrayList();
        try {
        	String sql = "SELECT * FROM users";
            PreparedStatement ps = conn.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                UserBean userBean = new UserBean();
                userBean.setId(rs.getInt("userid"));
                userBean.setfName(rs.getString("firstname"));
                userBean.setlName(rs.getString("lastname"));                             
                users.add(userBean);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return users;
    }

    public UserBean getUserById(int userId) {
    	UserBean userBean = new UserBean();
        try {
        	String sql = "SELECT * FROM users WHERE userid=?";
            PreparedStatement ps = conn.
                    prepareStatement(sql);
            ps.setInt(1, userId);
            ResultSet rs = ps.executeQuery();

            if (rs.next()) {
            	userBean.setId(rs.getInt("userid"));
            	userBean.setfName(rs.getString("firstname"));
            	userBean.setlName(rs.getString("lastname"));                           
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return userBean;
    }
}

UserHandler.java

package net.roseindia.handler;

import java.io.IOException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import net.roseindia.dao.UserDao;
import net.roseindia.bean.UserBean;

public class UserHandler extends HttpServlet {    
    private static String INSERT = "/user.jsp";
    private static String Edit = "/edit.jsp";
    private static String UserRecord = "/listUser.jsp";
    private UserDao dao;

    public UserHandler() {
        super();
        dao = new UserDao();
    }

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        String redirect="";
        String uId = request.getParameter("userid");        
        String action = request.getParameter("action");
        if(!((uId)== null) && action.equalsIgnoreCase("insert"))
        {
        	int id = Integer.parseInt(uId);
        	UserBean user = new UserBean();
        	user.setId(id);
            user.setfName(request.getParameter("firstName"));
            user.setlName(request.getParameter("lastName"));
        	dao.addUser(user);
        	redirect = UserRecord;
            request.setAttribute("users", dao.getAllUsers());    
           	System.out.println("Record Added Successfully");
        }
        else if (action.equalsIgnoreCase("delete")){
            String userId = request.getParameter("userId");
            int uid = Integer.parseInt(userId);
            dao.removeUser(uid);
            redirect = UserRecord;
            request.setAttribute("users", dao.getAllUsers());
            System.out.println("Record Deleted Successfully");
        }else if (action.equalsIgnoreCase("editform")){        	
        	redirect = Edit;            
        } else if (action.equalsIgnoreCase("edit")){
        	String userId = request.getParameter("userId");
            int uid = Integer.parseInt(userId);            
            UserBean user = new UserBean();
        	user.setId(uid);
            user.setfName(request.getParameter("firstName"));
            user.setlName(request.getParameter("lastName"));
            dao.editUser(user);
            request.setAttribute("user", user);
            redirect = UserRecord;
            System.out.println("Record updated Successfully");
         } else if (action.equalsIgnoreCase("listUser")){
            redirect = UserRecord;
            request.setAttribute("users", dao.getAllUsers());
         } else {
            redirect = INSERT;
        }

        RequestDispatcher rd = request.getRequestDispatcher(redirect);
        rd.forward(request, response);
    }

    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        doGet(request, response);
    }
}

user.jsp 

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
pageEncoding="ISO-8859-1"%>
<!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>Add New User</title>
</head>
<body>
<form method="POST" action='UserHandler' name="frmAddUser"><input
type="hidden" name="action" value="insert" />
<p><b>Add New Record</b></p>
<table>
<tr>
<td>User ID</td>
<td><input type="text" name="userid" /></td>
</tr>
<tr>
<td>First Name</td>
<td><input type="text" name="firstName" /></td>
</tr>
<tr>
<td>Last Name</td>
<td><input type="text" name="lastName" /></td>
</tr>
<tr>
<td></td>
<td><input type="submit" value="Submit" /></td>
</tr>
</table>
</form>
<p><a href="UserHandler?action=listUser">View-All-Records</a></p>
</body>
</html>

edit.jsp

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
pageEncoding="ISO-8859-1"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<%@ page import="net.roseindia.bean.UserBean"%>
<%@ page import="net.roseindia.dao.UserDao"%>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Edit User</title>
</head>
<body>
<%
UserBean user = new UserBean();
%>
<%
UserDao dao = new UserDao();
%>
<form method="POST" action='UserHandler' name="frmEditUser"><input
type="hidden" name="action" value="edit" /> <%
String uid = request.getParameter("userId");
if (!((uid) == null)) {
int id = Integer.parseInt(uid);
user = dao.getUserById(id);
%>
<table>
<tr>
<td>User ID</td>
<td><input type="text" name="userId" readonly="readonly"
value="<%=user.getId()%>"></td>
</tr>
<tr>
<td>First Name</td>
<td><input type="text" name="firstName" /></td>
</tr>
<tr>
<td>Last Name</td>
<td><input type="text" name="lastName" /></td>
</tr>
<tr>
<td></td>
<td><input type="submit" value="Update" /></td>
</tr>
</table>
<%
} else
out.println("ID Not Found");
%>
</form>
</body>
</html>

listUser.jsp

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
pageEncoding="ISO-8859-1"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<%@ page import="net.roseindia.bean.UserBean"%>
<%@ page import="net.roseindia.dao.UserDao"%>
<%@ page import="java.util.*"%>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>All User</title>
</head>
<body>
<%
//UserBean user = new UserBean();
UserDao dao = new UserDao();
List<UserBean> userList = dao.getAllUsers();
//Iterator<UserBean> itr = userList.iterator();
%>
<table border="1">
<tr>
<th>Id</th>
<th>First Name</th>
<th>Last Name</th>
</tr>
<tr>
<%
/*while(itr.hasNext())
{
System.out.println(user.getId());*/
for (UserBean user : userList) {
%>
<td><%=user.getId()%></td>
<td><%=user.getfName()%></td>
<td><%=user.getlName()%></td>
<td><a
href="UserHandler?action=editform&userId=<%=user.getId()%>">Update</a></td>
<td><a
href="UserHandler?action=delete&userId=<%=user.getId()%>">Delete</a></td>

</tr>
<%
}
//}
%>
</table>
<p><a href="UserHandler?action=insert">Add User</a></p>
</body>
</html>

web.xml 

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee" xmlns:web="http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" id="WebApp_ID" version="2.5">
<display-name>jspServletJdbcExample</display-name>
<welcome-file-list>
<welcome-file>user.jsp</welcome-file>
</welcome-file-list>
<servlet>
<description></description>
<servlet-name>UserHandler</servlet-name>
<servlet-class>net.roseindia.handler.UserHandler</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>UserHandler</servlet-name>
<url-pattern>/UserHandler</url-pattern>
</servlet-mapping>
</web-app>

Output

When you will execute the above example you will get the output as follows :

1. Database table before inserting the record

2. After executing this example the user.jsp page will be opened from here you can insert the new record and view the record's available in the table.

3. After providing the fields value ( I have inserted two records) when you will click on the submit button, in the above image, the page will be forwarded to the listUser.jsp as follows :

In the above image you can see a hyperlink "Add User", this is for adding more new user as well as there are two more hyperlinks "Update" and "Delete" update is used for editing the existing record (discussed later) and the delete is used for deleting the existing record (discussed later).

And when you will see the database table you will see these two records are also added into the table as follows :

4. Updating record : When you will click on the hyperlink Update shown at the listUser.jsp page an interface for updating the corresponding record, such as I want to edit the record with user ID 1, will be opened as follows : 0

5. Here You can alter the record of user ID 1, such as I have changed the Last Name from Singh to Roy and keep the Deepak as it is then the output will be as follows :

1

And the database table after update will be look like as follows :

6. Now I want to delete the record with of User ID = 2 So, I have click on the hyperlink delete in the second row whose id is 2 the record is deleted and then after the remaining list is as follows : 2

And the database table will be look like as follows :

3

Click here to Download Source Code in Eclipse project format

Download Source Code (WAR File)

After downloading this war file simply rename it to the jspServletJdbcExample and import it into your eclipse and run this example. 4

Tutorials

  1. Creating a String
  2. Introduction to JSP
  3. Reading Request Information
  4. Introduction to the JSP Java Server Pages
  5. JSP FUNDAMENTALS
  6. Using Taglib in JSP. A brief introduction to taglibs and taglibs programing.
  7. JSP date example
  8. Reading Request Information
  9. Using Beans in JSP. A brief introduction to JSP and Java Beans.
  10. JSP 2.0 - New Features
  11. JSP Excel Tutorial
  12. Create Excel Sheet Using JSP
  13. Inserting Image In Excel Sheet Using JSP
  14. How to Create Excel Page Using JSP
  15. How to Create New Excel Sheet Using JSP
  16. Create Excel Sheet Using JSP
  17. Passing Various Data Types into Cells
  18. Assing Date into Cells
  19. Reading And Writing Excel File
  20. Selecting Excel Sheet File
  21. Zoom in Excel
  22. Zoom Out Excel
  23. Merging Two Cells
  24. Working With Fonts
  25. Working With Alignment Using JSP for Excel
  26. Shifting Row Using JSP
  27. Freeze And Split Pane
  28. Repeating Rows And Column
  29. different borders
  30. Create Shape in Excel Using JSP
  31. Creating Oval in Excel Using JSP
  32. Create Box in Excel Sheet Using JSP
  33. Filling Color in Excel Using JSP
  34. Set Font into TextBox String Using JSP
  35. Fit Hight Ad Wdth of Ecel Seet Uing JSP
  36. Setting Line Style
  37. Finding a Factorial using while loop
  38. JSP Simple Examples
  39. Calculate factorial Using Recursion
  40. Multiple Methods in Jsp