Books Store Application

JSP Servlet JDBC MySQL Create Read Update Delete (CRUD) Example

In this Java tutorial, we’re going to help you understand the process of coding a basic Java web application that manages a collection of books with the basic feature: list, insert, update, delete (or CURD operations — Create, Update, Read and Delete). The application looks something like this:

You will learn to how to build this application using the following technologies:

• Java Servlets and Java Server Pages (JSP)
• JSP Standard Tag Library (JSTL)
• Java Database Connectivity (JDBC)
• MySQL database
• Apache Tomcat Server

1. Creating MySQL Database

For simplicity, we have only one table. Execute the following MySQL script to create a database named Bookstore and a table named Book :

CREATE DATABASE 'Bookstore'; USE Bookstore; CREATE TABLE `book` ( `book_id` int(11) NOT NULL AUTO_INCREMENT, `title` varchar(128) NOT NULL, `author` varchar(45) NOT NULL, `price` float NOT NULL, PRIMARY KEY (`book_id`), UNIQUE KEY `book_id_UNIQUE` (`book_id`), UNIQUE KEY `title_UNIQUE` (`title`) ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1

You can use either MySQL Command Line Client or MySQL Workbench tool to create the database.

2. Creating Eclipse Project with Maven

In Eclipse IDE, click File > New > Dynamic Web Project to create a new Java dynamic web project. Name the project as Bookstore:

Remember to choose Target runtime as Apache Tomcat v8.0 and Dynamic web module version as 3.1 (this is the Java servlet version).

Click Finish. Then convert this project to a Maven project by right click on the project, select Configure > Convert to Maven Project, as shown below:

You need to enter information to create Maven POM file, such as group ID, artifact ID, etc. Then add the following dependencies to the pom.xml file:

  javax.servlet javax.servlet-api 3.1.0 provided  javax.servlet.jsp javax.servlet.jsp-api 2.3.1 provided  jstl jstl 1.2  mysql mysql-connector-java 5.1.30   

As you can see, the dependencies here are for Servlet, JSP, JSTL and MySQL connector Java (a JDBC driver for MySQL).

And remember to create a Java package for the project, here we use the package name net.codejava.javaee.bookstore .

3. Writing Model Class

Next, create a Java class named Book.java to model a book entity in the database with the following code:

package net.codejava.javaee.bookstore; /** * Book.java * This is a model class represents a book entity * @author www.codejava.net * */ public class Book < protected int id; protected String title; protected String author; protected float price; public Book() < >public Book(int id) < this.id = id; >public Book(int id, String title, String author, float price) < this(title, author, price); this.id = id; >public Book(String title, String author, float price) < this.title = title; this.author = author; this.price = price; >public int getId() < return id; >public void setId(int id) < this.id = id; >public String getTitle() < return title; >public void setTitle(String title) < this.title = title; >public String getAuthor() < return author; >public void setAuthor(String author) < this.author = author; >public float getPrice() < return price; >public void setPrice(float price) < this.price = price; >>

As you can see, this class has 4 fields according to 4 columns in the table book in database: id, title, author and price.

4. Coding DAO class

Next, we need to implement a Data Access Layer (DAO) class that provides CRUD (Create, Read, Update, Delete) operations for the table book in database. Here’s the full source code of the BookDAO class:

package net.codejava.javaee.bookstore; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.List; /** * AbstractDAO.java * This DAO class provides CRUD database operations for the table book * in the database. * @author www.codejava.net * */ public class BookDAO < private String jdbcURL; private String jdbcUsername; private String jdbcPassword; private Connection jdbcConnection; public BookDAO(String jdbcURL, String jdbcUsername, String jdbcPassword) < this.jdbcURL = jdbcURL; this.jdbcUsername = jdbcUsername; this.jdbcPassword = jdbcPassword; >protected void connect() throws SQLException < if (jdbcConnection == null || jdbcConnection.isClosed()) < try < Class.forName("com.mysql.jdbc.Driver"); >catch (ClassNotFoundException e) < throw new SQLException(e); >jdbcConnection = DriverManager.getConnection( jdbcURL, jdbcUsername, jdbcPassword); > > protected void disconnect() throws SQLException < if (jdbcConnection != null && !jdbcConnection.isClosed()) < jdbcConnection.close(); >> public boolean insertBook(Book book) throws SQLException < String sql = "INSERT INTO book (title, author, price) VALUES (?, ?, ?)"; connect(); PreparedStatement statement = jdbcConnection.prepareStatement(sql); statement.setString(1, book.getTitle()); statement.setString(2, book.getAuthor()); statement.setFloat(3, book.getPrice()); boolean rowInserted = statement.executeUpdate() >0; statement.close(); disconnect(); return rowInserted; > public List listAllBooks() throws SQLException < ListlistBook = new ArrayList<>(); String sql = "SELECT * FROM book"; connect(); Statement statement = jdbcConnection.createStatement(); ResultSet resultSet = statement.executeQuery(sql); while (resultSet.next()) < int title = resultSet.getString("title"); String author = resultSet.getString("author"); float price = resultSet.getFloat("price"); Book book = new Book(id, title, author, price); listBook.add(book); >resultSet.close(); statement.close(); disconnect(); return listBook; > public boolean deleteBook(Book book) throws SQLException < String sql = "DELETE FROM book where book_id = ?"; connect(); PreparedStatement statement = jdbcConnection.prepareStatement(sql); statement.setInt(1, book.getId()); boolean rowDeleted = statement.executeUpdate() >0; statement.close(); disconnect(); return rowDeleted; > public boolean updateBook(Book book) throws SQLException < String sql = "UPDATE book SET title = ?, author = ?, price = ?"; sql += " WHERE book_id = ?"; connect(); PreparedStatement statement = jdbcConnection.prepareStatement(sql); statement.setString(1, book.getTitle()); statement.setString(2, book.getAuthor()); statement.setFloat(3, book.getPrice()); statement.setInt(4, book.getId()); boolean rowUpdated = statement.executeUpdate() >0; statement.close(); disconnect(); return rowUpdated; > public Book getBook(int id) throws SQLException < Book book = null; String sql = "SELECT * FROM book WHERE book_id = ?"; connect(); PreparedStatement statement = jdbcConnection.prepareStatement(sql); statement.setInt(1, id); ResultSet resultSet = statement.executeQuery(); if (resultSet.next()) < String title = resultSet.getString("title"); String author = resultSet.getString("author"); float price = resultSet.getFloat("price"); book = new Book(id, title, author, price); >resultSet.close(); statement.close(); return book; > >

As you can see, the JDBC connection information is injected to this class via its constructor. And the following methods are for CRUD operations:

• Create: insertBook(Book) — this inserts a new row into the table book.
• Read: listAllBooks() — this retrieves all rows; and getBook(id) — returns a specific row based on the primary key value (ID).
• Update: updateBook(Book) — this updates an existing row in the database.
• Delete: deleteBook(Book) — this removes an existing row in the database based on the primary key value (ID).

5. Writing Book Listing JSP Page

Next, create a JSP page for displaying all books from the database. The following is code of the BookList.jsp page under the WebContent directory in the project:

       
Books Management
 
Add New Book     List All Books 
  
  
List of Books
 
ID
 
Title
 
Author
 
Price
 
Actions
 
 
" />
 
" />
 
" />
 
" />
 
' />">Edit      ' />">Delete 
 
  
 
  

In this JSP page, we use JSTL to display records of the table book from database. The listBook object will be passed from a servlet which we will create later.

On running, this page looks something like this:

As you can see, on this page we have two hyperlinks at the top menu for creating a new book (Add New Book) and showing all books (List All Books). In addition, for each individual book there are two links for editing (Edit) and deleting (Delete).

6. Writing Book Form JSP Page

Next, we create a JSP page for creating a new book called BookForm.jsp . Here’s its full source code:

       
Books Management
 
Add New Book     List All Books 
  
   ' />" />
 
 
 Edit Book  Add New Book 
 
  
Title:
 
' />" />
 
 
Author:
 
' />" />
 
 
Price:
 
' />" />
 
 
 
 
 
 
  
  

This page will be served for both creating a new and editing an existing book. In editing mode, the servlet will pass a Book object to the request and we use the JSTL’s tag to determine whether this object is available or not. If available (not null) the form is in editing mode, otherwise it is in creating mode.

On running, this page shows new form like this:

We’ll see how to connect the DAO class with the JSP pages based on user’s requests in the next section: creating the servlet class.

7. Coding Controller Servlet Class

Now, the most difficult but interesting part is implement a Java Servlet that acts as a page controller to handle all requests from the client. Let’s look at the code first:

package net.codejava.javaee.bookstore; import java.io.IOException; import java.sql.SQLException; import java.util.List; import javax.servlet.RequestDispatcher; import javax.servlet.ServletException; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; /** * ControllerServlet.java * This servlet acts as a page controller for the application, handling all * requests from the user. * @author www.codejava.net */ public class ControllerServlet extends HttpServlet < private static final long serialVersionUID = 1L; private BookDAO bookDAO; public void init() < String jdbcURL = getServletContext().getInitParameter("jdbcURL"); String jdbcUsername = getServletContext().getInitParameter("jdbcUsername"); String jdbcPassword = getServletContext().getInitParameter("jdbcPassword"); bookDAO = new BookDAO(jdbcURL, jdbcUsername, jdbcPassword); >protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException < doGet(request, response); >protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException < String action = request.getServletPath(); try < switch (action) < case "/new": showNewForm(request, response); break; case "/insert": insertBook(request, response); break; case "/delete": deleteBook(request, response); break; case "/edit": showEditForm(request, response); break; case "/update": updateBook(request, response); break; default: listBook(request, response); break; >> catch (SQLException ex) < throw new ServletException(ex); >> private void listBook(HttpServletRequest request, HttpServletResponse response) throws SQLException, IOException, ServletException < ListlistBook = bookDAO.listAllBooks(); request.setAttribute("listBook", listBook); RequestDispatcher dispatcher = request.getRequestDispatcher("BookList.jsp"); dispatcher.forward(request, response); > private void showNewForm(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException < RequestDispatcher dispatcher = request.getRequestDispatcher("BookForm.jsp"); dispatcher.forward(request, response); >private void showEditForm(HttpServletRequest request, HttpServletResponse response) throws SQLException, ServletException, IOException < int existingBook = bookDAO.getBook(id); RequestDispatcher dispatcher = request.getRequestDispatcher("BookForm.jsp"); request.setAttribute("book", existingBook); dispatcher.forward(request, response); >private void insertBook(HttpServletRequest request, HttpServletResponse response) throws SQLException, IOException < String title = request.getParameter("title"); String author = request.getParameter("author"); float price = Float.parseFloat(request.getParameter("price")); Book newBook = new Book(title, author, price); bookDAO.insertBook(newBook); response.sendRedirect("list"); >private void updateBook(HttpServletRequest request, HttpServletResponse response) throws SQLException, IOException < int title = request.getParameter("title"); String author = request.getParameter("author"); float price = Float.parseFloat(request.getParameter("price")); Book book = new Book(id, title, author, price); bookDAO.updateBook(book); response.sendRedirect("list"); >private void deleteBook(HttpServletRequest request, HttpServletResponse response) throws SQLException, IOException < int book = new Book(id); bookDAO.deleteBook(book); response.sendRedirect("list"); >>

First, look at the init() method which instantiates an instance of the BookDAO class when the servlet is instantiated for the first time. The JDBC connection information will be read from Servlet’s context parameters. This method is invoked only one time during life cycle of the servlet so it’s reasonable to put the DAO instantiation code here:

Next, we can see this servlet handles both GET and POST requests as the doPost() method invokes the doGet() which handles all the request:

protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException < String action = request.getServletPath(); try < switch (action) < case "/new": showNewForm(request, response); break; case "/insert": insertBook(request, response); break; case "/delete": deleteBook(request, response); break; case "/edit": showEditForm(request, response); break; case "/update": updateBook(request, response); break; default: listBook(request, response); break; >> catch (SQLException ex) < throw new ServletException(ex); >>

Based on the request URL (starts with /edit, /list, /new, etc) the servlet calls the corresponding methods. Here we examine one method for example:

private void listBook(HttpServletRequest request, HttpServletResponse response) throws SQLException, IOException, ServletException < ListlistBook = bookDAO.listAllBooks(); request.setAttribute("listBook", listBook); RequestDispatcher dispatcher = request.getRequestDispatcher("BookList.jsp"); dispatcher.forward(request, response); >

This method uses the DAO class to retrieve all books from the database, and then forward to the BookList.jsp page for displaying the result. Similar logic is implemented for the rest methods.

I recommend you to read this famous Servlet and JSP book to master Java servlet and JSP.

8. Configuring Web.xml

To make the ControllerServlet intercepts all requests, we have to configure its mapping in the web deployment descriptor web.xml file. Open the web.xml file under WebContent\WEB-INF directory and update it with the following code:

  Books Management Web Application jdbcURL jdbc:mysql://localhost:3306/bookstore  jdbcUsername root  jdbcPassword P@ssw0rd  ControllerServlet net.codejava.javaee.bookstore.ControllerServlet  ControllerServlet /  java.lang.Exception /Error.jsp  

As you can see, the elements specify JDBC connection information (URL, username and password) for the DAO class.

The and elements declare and specify URL mapping for the ControllerServlet class. The URL pattern / means this is the default servlet to handle all requests.

The page elements specify error handling page for all kind of exceptions ( java.lang.Exception ) which may occur during the life of the application.

For details about error handling in Java web application, read this tutorial.

9. Writing Error JSP page

10. Deploying and Testing the Application

So far we have completed the code of the project. It’s time to deploy and test the application to see how it works. Follow this tutorial in case you don’t know how to add Apache Tomcat server in Eclipse.

Type the following URL in your web browser to access the Bookstore application:

http://localhost:8080/Bookstore

First time, the list is empty because there hasn’t any books yet:

Click on the hyperlink Add New Book to begin adding a new book:

Enter book information (title, author and price) and click Save. The application saves the book and shows the list, as shown below:

In this list, you can click on the Edit and Delete hyperlinks to edit and delete a specific book.

That’s how a simple Java web application with Serlvet, JSP, JDBC and MySQL is built. We hope you find this tutorial helpful and you can download the whole project under the Attachments section below.

If you want to have a full video training, I recommend you to take my course on Udemy Java Servlet, JSP and Hibernate: Build a Complete Website.

Related Tutorials:

Other Java Servlet Tutorials:

About the Author:

Nam Ha Minh is certified Java programmer (SCJP and SCWCD). He started programming with Java in the time of Java 1.4 and has been falling in love with Java since then. Make friend with him on Facebook and watch his Java videos you YouTube.

Источник