Post
Ask Question

Add, Edit, Update and Delete in JSP

In this article we will learn how to insert, edit, update and delete records from the database using jsp. Here we use JavaScript for form validation. We have a folder named include where we have three jsp pages: common.jsp, database.jsp and system_date_time.jsp, which contain variables declaration with datatype, database connection and datetime. We only have to include these jsp files where we need.

Table creation in Mysql database

-- phpMyAdmin SQL Dump
-- version 3.2.4
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Oct 09, 2011 at 09:37 PM
-- Server version: 5.1.41
-- PHP Version: 5.3.1

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `resturant`
--

-- --------------------------------------------------------

--
-- Table structure for table `ingredients`
--

CREATE TABLE IF NOT EXISTS `ingredients` (
`ingredient_slno` bigint(30) NOT NULL AUTO_INCREMENT,
`ingredient_code` varchar(10) COLLATE latin1_general_ci NOT NULL,
`ingredient_name` varchar(25) COLLATE latin1_general_ci NOT NULL,
`ingredient_category` varchar(25) COLLATE latin1_general_ci NOT NULL,
`ingredient_price` double NOT NULL,
`quantity` double NOT NULL,
`date_added` date NOT NULL,
PRIMARY KEY (`ingredient_slno`),
UNIQUE KEY `ingredient_code` (`ingredient_code`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=12 ;

--
-- Dumping data for table `ingredients`
--

INSERT INTO `ingredients` (`ingredient_slno`, `ingredient_code`, `ingredient_name`, `ingredient_category`, `ingredient_price`, `quantity`, `date_added`) VALUES
(1, 'A1', 'Curry', 'Veg', 100, 1, '2011-10-09'),
(2, 'A2', 'Masala Dosa', 'Veg', 200, 2, '2011-10-09'),
(3, 'A3', 'Tandoor Chicken', 'Non-veg', 300, 3, '2011-10-09'),
(4, 'A4', 'Prawn Curry', 'Non-veg', 500, 4, '2011-10-09');

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Add_ingredient.jsp

<%@ include file="include/common.jsp" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
<title></title>
<link rel="stylesheet" href="images/style.css" type="text/css" charset="utf-8" />

<script language="javascript">
function validate(ingredient)
{
if(ingredient.ingredient_code.value.length==0)
{
alert("Please enter code!");
ingredient.ingredient_code.focus();
return false;
}
if(ingredient.ingredient_name.value.length==0)
{
alert("Please enter Name!");
ingredient.ingredient_name.focus();
return false;
}
if(ingredient.ingredient_category.value.length==0)
{
alert("Please enter category!");
ingredient.ingredient_category.focus();
return false;
}
if(ingredient.ingredient_price.value.length==0)
{
alert("Please enter price!");
ingredient.ingredient_price.focus();
return false;
}
if(ingredient.quantity.value.length==0)
{
alert("Please enter quantity!");
ingredient.quantity.focus();
return false;
}
return true;
}
</script>
</head>
<body>

<h2>ADD INGREDIENTS </h2>
<form name="ingredient" id="ingredient" action="db_add_ingredient.jsp" method="post" onSubmit="return validate(this)">
<table width="332" height="252" border="0" align="center" cellpadding="2" cellspacing="2">
<tr>
<th height="33" colspan="2"><div align="center">
<%
ingredient=(String)session.getAttribute("ingredient");
session.removeAttribute("ingredient");
if(ingredient!=null)out.print(ingredient);
%>
</div></th>
</tr>
<tr>
<th>Ingredient Code </th>
<td><input name="ingredient_code" type="text" class="text" id="ingredient_code" value="" /></td>
</tr>
<tr>
<th>Ingredient Name </th>
<td><input name="ingredient_name" type="text" class="text" id="ingredient_name" value="" /></td>
</tr>
<tr>
<th>Ingredient Category </th>
<td><input name="ingredient_category" type="text" class="text" id="ingredient_category" value="" /></td>
</tr>
<tr>
<th>Ingredient Price</th>
<td><input name="ingredient_price" type="text" class="text" id="ingredient_price" value="" /></td>
</tr>
<tr>
<th>quantity</th>
<td><input name="quantity" type="text" class="text" id="quantity" value="" /></td>
</tr>
<tr>
<td class="submission" colspan="2"><div align="center">
<input name="s" type="submit" class="button" value="ADD" />
<input name="Reset" type="reset" class="button" value="RESET" />
</div></td>
</tr>
<tr>
<th class="submission" colspan="2"><div align="center"><a href="view_ingredients.jsp">View Ingredients</a> </div></th>
</tr>
</table>
</form>
</body>
</html>

Db_add_ingredient.jsp

<%@ include file="include/common.jsp" %>
<%
ingredient_code=request.getParameter("ingredient_code");
ingredient_name=request.getParameter("ingredient_name");
ingredient_category=request.getParameter("ingredient_category");

ingredient_price1=request.getParameter("ingredient_price");
if(ingredient_price1!=null || !"".equals(ingredient_price1))
ingredient_price=Double.parseDouble(ingredient_price1);

quantity1=request.getParameter("quantity");
if(quantity1!=null || !"".equals(quantity1))
quantity=Double.parseDouble(quantity1);


pstmt=con.prepareStatement("Insert into ingredients(ingredient_code,ingredient_name,ingredient_category,ingredient_price,quantity,date_added) values(?,?,?,?,?,?)");
pstmt.setString(1,ingredient_code);
pstmt.setString(2,ingredient_name);
pstmt.setString(3,ingredient_category);
pstmt.setDouble(4,ingredient_price);
pstmt.setDouble(5,quantity);
pstmt.setString(6,system_date);
pstmt.executeUpdate();

con.close();
session.setAttribute("ingredient","ingredient ("+ingredient_code+") added successfully");
response.sendRedirect("add_ingredient.jsp");
%>

View_ingredients.jsp

<%@ include file="include/common.jsp" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<script type="text/javascript">
function del()
{
if(confirm("Do You Want to Delete this Ingredient?"))
{
}
else
{
return false;
}
}
</script>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
<title></title>
<link rel="stylesheet" href="images/style.css" type="text/css" charset="utf-8" />
</head>
<body>
<%
ingredient_slno1=request.getParameter("ingredient_slno");
if(ingredient_slno1!=null)
ingredient_slno=Integer.parseInt(ingredient_slno1);
delete_ingredient=request.getParameter("delete_ingredient");

if("yes".equals(delete_ingredient))
{
ingredient_code=request.getParameter("ingredient_code");
x=stmt1.executeUpdate("Delete from ingredients where ingredient_slno="+ingredient_slno);
}
%>


<h2>VIEW INGREDIENTS</h2>

<table width="736" height="97" border="1">
<% if(x==1)
{
%> <tr bgcolor="#000000">
<th height="35" colspan="9"><div align="center">
Ingredient (<%=ingredient_code%>) deleted successfully
</div></th>
</tr>
<%}%>
<tr bgcolor="#000000">
<td><div align="center"><strong>SLNO</strong></div></td>
<td><div align="center"><strong>INGREDIENT CODE </strong></div></td>
<td><div align="center"><strong>INGREDIENT NAME </strong></div></td>
<td><div align="center"><strong>INGREDIENT CATEGORY </strong></div></td>
<td><div align="center"><strong>INGREDIENT PRICE </strong></div></td>
<td><div align="center"><strong>QUANTITY</strong></div></td>
<td><div align="center"><strong>DATE ADDED </strong></div></td>
<td colspan="2"><div align="center"><strong>ACTION</strong></div></td>
</tr>
<% int icount=0;
rs=stmt.executeQuery("select * from ingredients");
while(rs.next())
{ ingredient_slno=rs.getInt("ingredient_slno");
ingredient_code=rs.getString("ingredient_code");
%>
<tr>
<td><div align="center"><%=++icount%></div></td>
<td><%=ingredient_code%></td>
<td><%=rs.getString("ingredient_name")%></td>
<td><%=rs.getString("ingredient_category")%></td>
<td><%=rs.getDouble("ingredient_price")%></td>
<td><%=rs.getDouble("quantity")%></td>
<td><%=rs.getDate("date_added")%></td>
<td><div align="center"><a href="edit_ingredient.jsp?ingredient_slno=<%=ingredient_slno%>">Edit</a></div></td>
<td><div align="center"><a href="view_ingredients.jsp?delete_ingredient=yes&ingredient_slno=<%=ingredient_slno%>&ingredient_code=<%=ingredient_code%>" onclick="return del()">Delete</a></div></td>
</tr>
<% } %>
</table>
<a href="add_ingredient.jsp">Add Ingredient</a>
</body>
</html>

Edit_ingredient.jsp

<%@ include file="include/common.jsp" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
<title>Food Online</title>
<link rel="stylesheet" href="images/style.css" type="text/css" charset="utf-8" />
</head>
<body>
<%
ingredient_slno1=request.getParameter("ingredient_slno");
if(ingredient_slno1!=null)
ingredient_slno=Integer.parseInt(ingredient_slno1);

rs=stmt.executeQuery("Select * from ingredients where ingredient_slno="+ingredient_slno);
if(rs.next())
{
ingredient_code=rs.getString("ingredient_code");
ingredient_name=rs.getString("ingredient_name");
ingredient_category=rs.getString("ingredient_category");
ingredient_price=rs.getDouble("ingredient_price");
quantity=rs.getDouble("quantity");
}

%>
<h2>EDIT INGREDIENTS </h2>
<form name="ingredient" id="ingredient" action="update_ingredient.jsp" method="post" >
<table width="332" height="252" border="0" align="center" cellpadding="2" cellspacing="2">
<tr>
<th height="33" colspan="2"><div align="center">
<%
update_ingredient=(String)session.getAttribute("update_ingredient");
session.removeAttribute("update_ingredient");
if(update_ingredient!=null)out.print(update_ingredient);
%>
</div></th>
</tr>
<tr>
<th>Ingredient Code </th>
<td><input name="ingredient_code" type="text" class="text" id="ingredient_code" value="<%=ingredient_code%>" /></td>
</tr>
<tr>
<th>Ingredient Name </th>
<td><input name="ingredient_name" type="text" class="text" id="ingredient_name" value="<%=ingredient_name%>" /></td>
</tr>
<tr>
<th>Ingredient Category </th>
<td><input name="ingredient_category" type="text" class="text" id="ingredient_category" value="<%=ingredient_category%>" /></td>
</tr>
<tr>
<th>Ingredient Price</th>
<td><input name="ingredient_price" type="text" class="text" id="ingredient_price" value="<%=ingredient_price%>" /></td>
</tr>
<tr>
<th>quantity</th>
<td><input name="quantity" type="text" class="text" id="quantity" value="<%=quantity%>" /></td>
</tr>

<tr>
<td class="submission" colspan="6">
<input type="hidden" name="ingredient_slno" value="<%=ingredient_slno%>"/>
<input name="s" type="submit" class="button" value="UPDATE" />

</td>
</tr>
<tr>
<th class="submission" colspan="2"><div align="center"><a href="view_ingredients.jsp">View Ingredients</a> </div></th>
</tr>
</table>
</form>

</body>
</html>

Update_ingredient.jsp

<%@ include file="include/common.jsp" %>
<%
ingredient_slno1=request.getParameter("ingredient_slno");
if(ingredient_slno1!=null)
ingredient_slno=Integer.parseInt(ingredient_slno1);

ingredient_code=request.getParameter("ingredient_code");
ingredient_name=request.getParameter("ingredient_name");
ingredient_category=request.getParameter("ingredient_category");

ingredient_price1=request.getParameter("ingredient_price");
if(ingredient_price1!=null || !"".equals(ingredient_price1))
ingredient_price=Double.parseDouble(ingredient_price1);

quantity1=request.getParameter("quantity");
if(quantity1!=null || !"".equals(quantity1))
quantity=Double.parseDouble(quantity1);

pstmt=con.prepareStatement("Update ingredients set ingredient_code=?,ingredient_name=?,ingredient_category=?,ingredient_price=?,quantity=? where ingredient_slno="+ingredient_slno);

pstmt.setString(1,ingredient_code);
pstmt.setString(2,ingredient_name);
pstmt.setString(3,ingredient_category);
pstmt.setDouble(4,ingredient_price);
pstmt.setDouble(5,quantity);
pstmt.executeUpdate();

con.close();
session.setAttribute("update_ingredient","ingredient ("+ingredient_code+") updated successfully");
%>
<jsp:forward page="edit_ingredient.jsp">
<jsp:param name="ingredient_slno" value="<%=ingredient_slno%>" />
</jsp:forward>

Running the application

Store the files in a folder (Add_edit_update_delete) inside (E:\Program Files\Apache Software Foundation\Tomcat 6.0\webapps).

Start the tomcat and write the below link in the URL

http://localhost:8081/Add_edit_update_delete/

Click on add_ingredient.jsp link

JSP1.gif

JSP2.gif


Similar Articles