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

Next Recommended Readings