TechTutorial

TechTutorial

Menu
Java Web Application JDBC Connection JSP & Servelet MYSQL Database Web development

CRUD example in JSP, Servlet, and MySQL

Techtutorial

CRUD operation in JSP and Servlet

In this post, we will learn Java CRUD examples using JSP, Servlet, and MySQL. Also, we will use an advanced JSTL Tag for convenient work. Let’s start!!

What is CRUD?

CRUD is the basic four-function in the database storage system which are Create, Read, Update and Delete. Using an interface, we can store our data or retrieve our data from the database. In this post, we will learn how to perform CRUD operations to store data in a database, retrieve data from the database, update our data and delete our data if necessary. For the interface, we will use the JSP form, Servlet, and MySQL database to store data.

CRUD operation in JSP and Servlet

Prerequisite of CRUD Application

  • NetBeans or Eclipse IDE.
  • Java EE.
  • MySQL Workbench.
  • JDBC Connector.
  • Knowledge in Java, Servlet, and SQL.
  • JSTL Tag Library, jstl-1.2.jar file.
  • Tomcat Server.

I hope you have set up your programming environment. If you don’t or any problem feels free to comment we will show you in the next tutorial.

Step 1: Project Setup

This is the basic setup of our project. That will help us to better understand what we are doing.

  • Open your NetBeans IDE and create a new project.
  • Right-click on the Web Pages folder to create two JSP page names index.jsp and editbook.jsp
  • If NetBeans doesn’t create a web.xml file, then right-click on the WEB-INF page>New>Other>web.xml to create a new web.xml file.
  • Now create a package for our Web Project. Right-click on the Sources Packages folder New>Java Package> package name and click finish.
  • Right-click Libraries folder>Add JAR/Folder, then select the mysql-connector-java-8.0.18.jar file and jstl-1.2.jar file.

Step 2: Design the index.jsp and editbook.jsp

For designing, we will use Bootstrap to make it simple. Now copy the below code and paste to your index.jsp page.

<%@page import="java.util.List"%>
<%@page import="com.crudtest.books"%>
<%@page import="com.crudtest.ConnectionDao"%>
<%@page import="com.crudtest.BooksDao"%>
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<%
BooksDao bookData = new BooksDao(ConnectionDao.getCon());
List<books> book = bookData.getAllBooks();
request.setAttribute("BOOKS_LIST", book);
%>


<!DOCTYPE html>
<html>
<head>
<!-- Required meta tags -->
<meta charset="utf-8">
<meta name="viewport"
	content="width=device-width, initial-scale=1, shrink-to-fit=no">

<!-- Bootstrap CSS -->
<link rel="stylesheet"
	href="https://stackpath.bootstrapcdn.com/bootstrap/4.4.1/css/bootstrap.min.css">

<title>CRUD Application</title>

<style>
.inner {
	margin: 15px 0;
}
</style>
</head>
<body>
	<div class="container-fluid">
		<nav class="navbar navbar-light">
			<a class="navbar-brand">Book Store</a>
			<form class="form-inline">
				<input class="form-control mr-sm-2" type="search"
					placeholder="Search" aria-label="Search">
				<button class="btn btn-outline-success my-2 my-sm-0" type="submit">Search</button>
			</form>
		</nav>
	</div>
	<div class="container">
		<div class="inner">
			<div class="row">
				<div class="col-md-3">
					<h3>Input Book Information</h3>
					<form action="AddBooksServlet" method="post">
						<div class="form-group">
							<label>Book Name</label> <input class="form-control"
								name="bkname" place-holder="Book Name" required>
						</div>
						<div class="form-group">
							<label>Description</label> <input class="form-control"
								name="bkdes" place-holder="Book Name" required>
						</div>
						<div class="form-group">
							<label>Author Name</label> <input class="form-control"
								name="authname" place-holder="Book Name" required>
						</div>
						<div class="form-group">
							<label>Category</label> <select id="inputState"
								class="form-control" name="category" required>
								<option selected disabled>Choose Category</option>
								<option value="Novel">Novel</option>
								<option value="Science Fiction">Science Fiction</option>
								<option value="Drama">Drama</option>
								<option value="Programming & Development">Programming &
									Development</option>
							</select>
						</div>
						<button type="submit" class="btn btn-primary">Submit</button>
						<button type="reset" class="btn btn-primary">Reset</button>
					</form>
				</div>
				<div class="col-md-9">
					<h3>Book Information From Database</h3>
					<table class="table">
						<thead class="bg-light">
							<tr>
								<th scope="col">Book Name</th>
								<th scope="col">Description</th>
								<th scope="col">Author</th>
								<th scope="col">Category</th>
								<th scope="col">Action</th>
							</tr>
						</thead>
						<tbody>
							<c:forEach var="tempBook" items="${BOOKS_LIST}">
								<tr>
									<td>${tempBook.bookName }</td>
									<td>${tempBook.bookDesc }</td>
									<td>${tempBook.authName }</td>
									<td>${tempBook.cat}</td>
									<td><a href="editbook.jsp?id=${tempBook.id }">Edit</a> <a
										href="DeleteBookServlet?id=${tempBook.id}">Delete</a></td>
								</tr>
							</c:forEach>
						</tbody>
					</table>
				</div>
			</div>
		</div>
	</div>
	<!-- Optional JavaScript -->
	<!-- jQuery first, then Popper.js, then Bootstrap JS -->
	<script src="https://code.jquery.com/jquery-3.4.1.slim.min.js"></script>
	<script
		src="https://cdn.jsdelivr.net/npm/popper.js@1.16.0/dist/umd/popper.min.js"></script>
	<script
		src="https://stackpath.bootstrapcdn.com/bootstrap/4.4.1/js/bootstrap.min.js"></script>
</body>
</html>

Now copy the below code and paste into the editbook.jsp

<%@page import="com.crudtest.books"%>
<%@page import="com.crudtest.BooksDao"%>
<%@page import="com.crudtest.ConnectionDao"%>
<%@page contentType="text/html" pageEncoding="UTF-8"%>

<%
int id = Integer.parseInt(request.getParameter("id"));
BooksDao bkd = new BooksDao(ConnectionDao.getCon());
books bk = bkd.getSingleBook(id);
request.setAttribute("edit_books", bk);
%>
<!DOCTYPE html>
<html>
<head>
<!-- Required meta tags -->
<meta charset="utf-8">
<meta name="viewport"
	content="width=device-width, initial-scale=1, shrink-to-fit=no">

<!-- Bootstrap CSS -->
<link rel="stylesheet"
	href="https://stackpath.bootstrapcdn.com/bootstrap/4.4.1/css/bootstrap.min.css">

<title>Edit Book Data</title>
<style>
.inner {
	position: relative;
	margin: 0 auto;
	width: 650px;
	display: block;
	padding: 50px 0;
}

h3 {
	text-align: center;
	border-bottom: 2px solid midnightblue;
	margin-bottom: 20px;
}

nav li a {
	font-size: 25px;
	font-weight: 500;
}

a {
	color: #fff;
	text-decoration: none;
}
</style>
</head>
<body>
	<div class="container-fluid">
		<nav class="navbar navbar-light">
			<a class="navbar-brand">Book Store</a>
			<ul class="navbar-nav ml-auto mt-3 mx-5">
				<li class="nav-item"><a class="nav-link active"
					href="index.jsp">Home</a></li>
			</ul>
		</nav>
	</div>
	<div class="inner">
		<div class=" container">
			<div class="row">
				<div class="col-12">
					<h3>Edit Book Details</h3>
					<form action="EditBooksServlet" method="post">
						<div class="form-group">
							<label>Book ID</label> <input class="form-control" name="bkid"
								value="${edit_books.id }" required>
						</div>
						<div class="form-group">
							<label>Book Name</label> <input class="form-control"
								name="bkname" value="${edit_books.bookName }" required>
						</div>
						<div class="form-group">
							<label>Description</label> <input class="form-control"
								name="bkdes" value="${edit_books.BookDesc}" required>
						</div>
						<div class="form-group">
							<label>Author Name</label> <input class="form-control"
								name="authname" value="${edit_books.authName}" required>
						</div>
						<div class="form-group">

							<label>Category</label> <select id="inputState"
								class="form-control" name="category" required>
								<option selected disabled>Choose Category</option>
								<option value="Novel">Novel</option>
								<option value="Science Fiction">Science Fiction</option>
								<option value="Drama">Drama</option>
								<option value="Programming & Development">Programming &
									Development</option>
							</select>
						</div>
						<button type="submit" class="btn btn-primary">Submit</button>
						<button class="btn btn-primary">
							<a href="index.jsp">Cancel</a>
						</button>
					</form>
				</div>
			</div>
		</div>
	</div>
	<!-- jQuery first, then Popper.js, then Bootstrap JS -->
	<script src="https://code.jquery.com/jquery-3.4.1.slim.min.js"></script>
	<script
		src="https://cdn.jsdelivr.net/npm/popper.js@1.16.0/dist/umd/popper.min.js"></script>
	<script
		src="https://stackpath.bootstrapcdn.com/bootstrap/4.4.1/js/bootstrap.min.js"></script>

</body>
</html>

Step 3: Create a Database and Table in MySQL Workbench

To store our data, we need a database and data table.  Below are the database and table values.

create database curd_project;

CREATE TABLE `curd_project`.`books` (`id` INT NOT NULL AUTO_INCREMENT, `bName` VARCHAR(45) NOT NULL, `aDesc` VARCHAR(45) NOT NULL, `aName` VARCHAR(45) NOT NULL, `cat` VARCHAR(45) NOT NULL, PRIMARY KEY (`id`), UNIQUE INDEX `bName_UNIQUE` (`bName` ASC) VISIBLE);

Step 4: Create a JDBC Connection

Right-click your source packages and create a new java class. Name it ConnectionDao.java Copy the below code and paste it.

package com.crudtest;
import java.sql.*;

public class ConnectionDao {
    private static Connection con;

    public static Connection getCon() throws SQLException {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            con=DriverManager.getConnection("jdbc:mysql://localhost:3306/curd_project","root","almamun@123");
        }catch (Exception ex) {
            ex.printStackTrace();
        } 
        return con;
    }
}

Step 5: Create a books.java bean

public class books {
    int id;
    String bookName;
    String bookDesc;
    String authName;
    String cat;

    public books() {
    }

    public books(int id, String bookName, String bookDesc, String authName, String cat) {
        this.id = id;
        this.bookName = bookName;
        this.bookDesc = bookDesc;
        this.authName = authName;
        this.cat = cat;
    }

    public books(String bookName, String bookDesc, String authName, String cat) {
        this.bookName = bookName;
        this.bookDesc = bookDesc;
        this.authName = authName;
        this.cat = cat;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getBookName() {
        return bookName;
    }

    public void setBookName(String bookName) {
        this.bookName = bookName;
    }

    public String getBookDesc() {
        return bookDesc;
    }

    public void setBookDesc(String bookDesc) {
        this.bookDesc = bookDesc;
    }

    public String getAuthName() {
        return authName;
    }

    public void setAuthName(String authName) {
        this.authName = authName;
    }

    public String getCat() {
        return cat;
    }

    public void setCat(String cat) {
        this.cat = cat;
    }

    @Override
    public String toString() {
        return "books{" + "id=" + id + ", bookName=" + bookName + ", bookDesc=" + bookDesc + ", authName=" + authName + ", cat=" + cat + '}';
    }
    
    
}

Step 6: Create a BooksDao.java class

In this BooksDao class, we will write SQL code for CRUD operation.

*Please take care of the imports.

package com.crudtest;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class BooksDao {
    Connection con;

    public BooksDao(Connection con) {
        this.con = con;
    }
    
    
    //add books information to database
    public boolean addBook(books book){
        boolean test = false;
        
        try{
            String query =  "insert into books (bName,bDesc,aName,cat) values(?,?,?,?)";
            PreparedStatement pst = this.con.prepareStatement(query);
            pst.setString(1, book.getBookName());
            pst.setString(2, book.getBookDesc());
            pst.setString(3, book.getAuthName());
            pst.setString(4, book.getCat());
            pst.executeUpdate();
            test= true;

        }catch(Exception e){
            e.printStackTrace();
        }
        return test;
    }
    
//    retrieve the book details from databse
    public List<books> getAllBooks(){
        List<books> book = new ArrayList<>();
        
        try{
            
            String query = "select * from books";
            PreparedStatement pt = this.con.prepareStatement(query);
            ResultSet rs = pt.executeQuery();
            
            while(rs.next()){
                int id = rs.getInt("id");
                String bname = rs.getString("bName");
                String des = rs.getString("bDesc");
                String aname = rs.getString("aName");
                String cat = rs.getString("cat");
                
                books row = new books(id,bname,des,aname,cat);
                book.add(row);
            }
            
        }catch(Exception e){
            e.printStackTrace();;
        }
        return book;
    }
    
    
//    eidt book information
    public void editBookInfo(books book){
        
        try{
            String query = "update books set bName=?, bDesc=?, aName=?, cat=? where id=?";
            PreparedStatement pt = this.con.prepareStatement(query);
            pt.setString(1, book.getBookName());
            pt.setString(2, book.getBookDesc());
            pt.setString(3, book.getAuthName());
            pt.setString(4, book.getCat());
            pt.setInt(5, book.getId());
            
            pt.executeUpdate();
        }catch(Exception ex){
            ex.printStackTrace();;
        }
        
        
    }
    
//    get single book information in edit page
    public books getSingleBook(int id){
        books bk = null;
        
        try{
            String query = "select * from books where id=? ";
            
            PreparedStatement pt = this.con.prepareStatement(query);
            pt.setInt(1, id);
            ResultSet rs= pt.executeQuery();
            
            while(rs.next()){
                int bid = rs.getInt("id");
                String bnm = rs.getString("bName");
                String bdes = rs.getString("bDesc");
                String anm = rs.getString("aName");
                String cat  = rs.getString("cat");
                bk = new books(bid,bnm,bdes,anm,cat);
            }
        }catch(Exception ex){
            ex.printStackTrace();;
        }
        return bk;
    }
    
    
//    delete books from database
    
    
    public void deleteBook(int id){
        try{
            
           String query= "delete from books where id=?";
           PreparedStatement pt = this.con.prepareStatement(query);
           pt.setInt(1, id);
           pt.execute();
            
        }catch(Exception ex){
            ex.printStackTrace();;
        }
    }
}

Step 7: Creating the Controller Servlet for our project

*Please take care of the imports.

1. AddBooksServlet.java and paste the below code to your servlet body.

            String bname = request.getParameter("bkname");
            String bdesc = request.getParameter("bkdes");
            String athname = request.getParameter("authname");          
            String cat = request.getParameter("category");
            books book = new books(bname, bdesc, athname, cat);
            try{
                BooksDao bkdao = new BooksDao(ConnectionDao.getCon());
                if(bkdao.addBook(book)){
                    response.sendRedirect("index.jsp");
                }else{
                    out.print("wrong cre3dential");
                }
                
            }catch(Exception e){
                e.printStackTrace();
            }

2. EditBooksServlet.java paste the below code to your servlet body.

            String bname = request.getParameter("bkname");
            String bdesc = request.getParameter("bkdes");
            String athname = request.getParameter("authname");          
            String cat = request.getParameter("category");
            books book = new books(bname, bdesc, athname, cat);
            try{
                BooksDao bkdao = new BooksDao(ConnectionDao.getCon());
                if(bkdao.addBook(book)){
                    response.sendRedirect("index.jsp");
                }else{
                    out.print("wrong cre3dential");
                }
                
            }catch(Exception e){
                e.printStackTrace();
            }

3. DeleteBooksServlet.java paste the below code to your servlet body.

            int bid = Integer.parseInt(request.getParameter("id"));
            
            try{
                BooksDao bkd = new BooksDao(ConnectionDao.getCon());
                bkd.deleteBook(bid);
                response.sendRedirect("index.jsp");
            }catch(Exception e){
                e.printStackTrace();
            }

Step 8: Run the project

This is the final step to test our project. If you write those codes properly, I hope it’s working properly.

Best of luck to complete this project. If you have any questions, please feel free to comment. And also share it with your friends. 

Similar Posts

10 thoughts on “CRUD example in JSP, Servlet, and MySQL”

  6. Hi, after following the part 1, I got an error. the details are not going into the database. Pls hlp

    Type Status Report

    Message HTTP method POST is not supported by this URL

    Description The method received in the request-line is known by the origin server but not supported by the target resource.

Leave a Reply