Java JDBC CRUD Example + Using JSP & Servlet + MySQL

Hi All,

Greetings of the day!

Today we will perform CRUD operations using JDBC, Servlet & JSP. I am assuming you have a basic idea of the servlet and its working if not visit my post.

What Is Servlet In Java? Servlet With Example + Servlet Lifecycle

So let's begin,

Create database test_db using cmd create database test_db;
Select test_db as the current database with the command use test_db;.
Create a table in MySQL which we will be used to perform CRUD. Below is a query for the same.

CREATE  TABLE `test_db`.`Recipe` (
  `Recipe_Id` INT NOT NULL AUTO_INCREMENT ,
  `Recipe_Name` VARCHAR(45) NOT NULL ,
  `Recipe_Price` INT NOT NULL ,
  PRIMARY KEY (`Recipe_Id`) );

As we are using mysql we will add mysql dependency,also we will add jstl dependency in pom.xml.

    <project xmlns="http://maven.apache.org/POM/4.0.0" 
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <groupId>com.recipe.mgt</groupId>
  <artifactId>RecipeMgt</artifactId>
  <packaging>war</packaging>
  <version>0.0.1-SNAPSHOT</version>
  <name>RecipeMgt Maven Webapp</name>
  <url>http://maven.apache.org</url>
  <dependencies>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>3.8.1</version>
      <scope>test</scope>
    </dependency>
    	<dependency>
	    <groupId>mysql</groupId>
	    <artifactId>mysql-connector-java</artifactId>
	    <version>5.1.47</version>
	</dependency>
    
    <dependency>
	    <groupId>javax.servlet</groupId>
	    <artifactId>jstl</artifactId>
	    <version>1.2</version>
	</dependency>
	
	<dependency>
		<groupId>taglibs</groupId>
		<artifactId>standard</artifactId>
		<version>1.1.2</version>
	</dependency>
	
	<dependency>
	    <groupId>log4j</groupId>
	    <artifactId>log4j</artifactId>
	    <version>1.2.17</version>
	</dependency>
    
  </dependencies>
  <build>
    <finalName>RecipeMgt</finalName>
  </build>
</project>

Now create POJO Recipe.java with fields we have added in the table, also add getter, and setter. It will be used to pass data in the application.

    package com.recipe.mgt.model;

public class Recipe {

	private int recipeId;
	
	private String recipeName;
	
	private int recipePrice;

	public Recipe(int recipeId, String recipeName, int recipePrice) {
		super();
		this.recipeId = recipeId;
		this.recipeName = recipeName;
		this.recipePrice = recipePrice;
	}

	public int getRecipeId() {
		return recipeId;
	}

	public void setRecipeId(int recipeId) {
		this.recipeId = recipeId;
	}

	public String getRecipeName() {
		return recipeName;
	}

	public void setRecipeName(String recipeName) {
		this.recipeName = recipeName;
	}

	public int getRecipePrice() {
		return recipePrice;
	}

	public void setRecipePrice(int recipePrice) {
		this.recipePrice = recipePrice;
	}
	
	public Recipe() {
	
	}

	@Override
	public String toString() {
		return "Recipe [recipeId=" + recipeId +
    		", recipeName=" + recipeName + ",
    recipePrice=" + recipePrice + "]";
	}
}

Next, we will be adding RecipeDAO class where we will be adding methods to interact with the database.
After creating the class we will add the below method to add the recipe to the Recipe table. Here we have used PreparedStatement to execute the query.

    public void addRecipe(Recipe recipe) throws ClassNotFoundException, SQLException {
		Class.forName("com.mysql.jdbc.Driver");
		Connection connection = DriverManager
            .getConnection("jdbc:mysql://localhost:3306/test_db", "admin", "admin");
		String sql = "INSERT INTO RECIPE (RECIPE_NAME,RECIPE_PRICE) values (?,?)";
		PreparedStatement pstmt = connection.prepareStatement(sql);
		pstmt.setString(1, recipe.getRecipeName());
		pstmt.setInt(2, recipe.getRecipePrice());
		pstmt.executeUpdate();
	}

Now add method to fetch all recipe from table.Here we have used ResultSet to iterate and create List of Recipe

      public List<Recipe> findAllRecipe() throws ClassNotFoundException, SQLException{
		Class.forName("com.mysql.jdbc.Driver");
		Connection connection = DriverManager
          .getConnection("jdbc:mysql://localhost:3306/test_db", "admin", "admin");
		String sql = "Select * from RECIPE";
		PreparedStatement pstmt = connection.prepareStatement(sql);
		ResultSet resultSet=pstmt.executeQuery(sql);
		List<Recipe> recipes=new ArrayList<Recipe>();
		while(resultSet.next()) {
			Recipe recipe=new Recipe();
			recipe.setRecipeId(resultSet.getInt(1));
			recipe.setRecipeName(resultSet.getString(2));
			recipe.setRecipePrice(resultSet.getInt(3));
			resultSet.getString(3);
			recipes.add(recipe);
		}
		return recipes;
}

Now add a method to fetch a single recipe using recipe id which will be used while we will be editing the recipe.

      public Recipe findRecipe(int recipeId) throws ClassNotFoundException, SQLException{
		Class.forName("com.mysql.jdbc.Driver");
		Connection connection = DriverManager
          .getConnection("jdbc:mysql://localhost:3306/test_db", "admin", "admin");
		
        String sql = "Select * from RECIPE where Recipe_Id = "+ recipeId;
		Statement pstmt = connection.createStatement();
		ResultSet resultSet=pstmt.executeQuery(sql);
		Recipe recipe=new Recipe();
		
		while(resultSet.next()) {
			recipe.setRecipeId(resultSet.getInt(1));
			recipe.setRecipeName(resultSet.getString(2));
			recipe.setRecipePrice(resultSet.getInt(3));
			resultSet.getString(3);
			
		}
		return recipe;
	}

Now add a method to update & delete the recipe.

      public void updateRecipe(Recipe recipe) throws ClassNotFoundException, SQLException {
		Class.forName("com.mysql.jdbc.Driver");
		Connection connection = DriverManager
         .getConnection("jdbc:mysql://localhost:3306/test_db", "admin", "admin");
		
        String sql = "UPDATE RECIPE SET RECIPE_NAME=?,RECIPE_PRICE=? where Recipe_Id=?";
		PreparedStatement pstmt = connection.prepareStatement(sql);
		pstmt.setString(1, recipe.getRecipeName());
		pstmt.setInt(2, recipe.getRecipePrice());
		pstmt.setInt(3, recipe.getRecipeId());
		pstmt.executeUpdate();
	}
	
	public void deleteRecipe(int recipeId) throws ClassNotFoundException, SQLException {
		Class.forName("com.mysql.jdbc.Driver");
		Connection connection = DriverManager
         .getConnection("jdbc:mysql://localhost:3306/test_db", "admin", "admin");
		
        String sql = "Delete from Recipe where Recipe_Id=?";
		PreparedStatement pstmt = connection.prepareStatement(sql);
		pstmt.setInt(1, recipeId);
		pstmt.executeUpdate();
	}

Now we have our repository layer ready, now let's create a controller class (Servlet for this example) to handle CRUD requests.

We will be using the same form for adding & editing recipes and we will handle either adding a new recipe or updating existing recipes in the controller using recipe id.

For displaying data on jsp we will be setting recipes in the request attribute after fetching from the database. Then we will forward a request to jsp & render data using JSTL.

package com.recipe.mgt.controller;

import java.io.IOException;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

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

import com.recipe.mgt.dao.RecipeDAO;
import com.recipe.mgt.model.Recipe;

public class RecipeController extends HttpServlet {
	private static final long serialVersionUID = 1L;

	public RecipeController() {
		super();
		// TODO Auto-generated constructor stub
	}

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

		String flag = request.getParameter("flag");
		RecipeDAO recipeDAO = new RecipeDAO();

		if ("editRecipe".equalsIgnoreCase(flag)) {
			int recipeId = Integer.parseInt(request.getParameter("recipeId"));
			try {
				Recipe recipe = recipeDAO.findRecipe(recipeId);
				request.setAttribute("recipe", recipe);
				request.getRequestDispatcher("add_recipe.jsp").forward(request, response);
			} catch (ClassNotFoundException e) {
				e.printStackTrace();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		} else if ("deleteRecipe".equalsIgnoreCase(flag)) {
			int recipeId = Integer.parseInt(request.getParameter("recipeId"));
			try {
				recipeDAO.deleteRecipe(recipeId);
			} catch (ClassNotFoundException e) {
				e.printStackTrace();
			} catch (SQLException e) {
				e.printStackTrace();
			}

			getAllBooks(request, response, recipeDAO);

		} else {
			getAllBooks(request, response, recipeDAO);
		}
	}

	private void getAllBooks(HttpServletRequest request, HttpServletResponse response, RecipeDAO recipeDAO)
			throws ServletException, IOException {
		List<Recipe> recipes = new ArrayList<Recipe>();

		try {
			recipes = recipeDAO.findAllRecipe();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		request.setAttribute("recipes", recipes);
		request.getRequestDispatcher("view_recipe.jsp").forward(request, response);
	}

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

		String recipeName = request.getParameter("recipeName");
		int recipePrice = Integer.valueOf(request.getParameter("recipePrice"));
		String recipeId = request.getParameter("recipeId");

		Recipe recipe = new Recipe();
		recipe.setRecipeName(recipeName);
		recipe.setRecipePrice(recipePrice);

		RecipeDAO recipeDAO = new RecipeDAO();
		try {
			if (recipeId != "") {
				recipe.setRecipeId(Integer.parseInt(recipeId));
				recipeDAO.updateRecipe(recipe);
			} else {
				recipeDAO.addRecipe(recipe);
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		getAllBooks(request, response, recipeDAO);
	}

}

We have called getAllBooks after delete, add & update so we can display recipes after each operation.

We are done with the back-end code. Let's add jsp code to create a recipe in add_recipe.jsp

add_recipe.jsp

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>
<%@ taglib uri="http://java.sun.com/jstl/core_rt" prefix="c"%>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Add Recipe</title>
</head>
<body>

	<form action="<%=request.getContextPath()%>/RecipeController" method="post">
		Recipe Name <input type="text" name="recipeName" value="${recipe.recipeName}"><br>
		Recipe Price<input type="number" name="recipePrice" value="${recipe.recipePrice}"><br>
		<input type="hidden" name="recipeId" value="${recipe.recipeId}">
		<input type="submit" value="Add/Update Recipe">
	
	</form>

</body>
</html>

Now create view_recipe.jsp to render all recipes, also here we will add links for edit & delete.

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>
<%@ taglib uri="http://java.sun.com/jstl/core_rt"  prefix="c"%>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Insert title here</title>
</head>
<body>
<a href="add_recipe.jsp">Add Recipe</a>
<table border="2px">
	<tr>
		<th>Recipe Name</th>
		<th>Recipe Price</th>
	</tr>
	<c:forEach items="${recipes}" var="recipe">
		<tr>
			<td>${recipe.recipeName}</td>
			<td>${recipe.recipePrice}</td>
			<td><a href="<%=request.getContextPath()%>/RecipeController?flag=editRecipe&recipeId=${recipe.recipeId}">EDIT</a></td>
			<td><a href="<%=request.getContextPath()%>/RecipeController?flag=deleteRecipe&recipeId=${recipe.recipeId}">DELETE</a></td>
		</tr>
	</c:forEach>
	
</table>
</body>
</html>

Inside index.jsp we will add links to add.jsp & view.jsp

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>
<%@ taglib uri="http://java.sun.com/jstl/core_rt" prefix="c"%>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Manage Recipe</title>
</head>
<body>

<a href="add_recipe.jsp">Add Recipe</a>

<a href="<%=request.getContextPath()%>/RecipeController?flag=viewBooks">View Recipe</a>

</body>
</html>

We have completed the crud example.

I have attached the project structure for reference