In this section, you will learn how to display sum of column from database using jsp.
Ads
Tutorials
In this section, we develop an application to Display the sum data
of a Table column for a specific Date. We created five file sum_count.jsp,
resultForm.jsp, result_count.jsp, sumcount.java
and
SumCountBean.java.
When a web page ("sum_count.jsp") run on browser then it will
insert the salary amount for a specific date and on this page having a link
"Click here To Show Total Salary" to show the total salary
between two specific date and result is display using Jsp
Bean..
Step 1: Create a web page(sum_count.jsp) to insert a Salary.
<%@ page import="java.sql.*" %>
<html>
<head>
<style>
A:hover {text-decoration: none;
border: 0px;
font-size:14pt;
color: #2d2b2b; }
</style>
<link rel="stylesheet" type="text/css" href="datepicker.css"/>
<script type="text/javascript" src="datepicker.js"></script>
<script>
function validate()
{
var sdate = document.frm.date.value;
var sdateAr=sdate.split("-");
if(document.frm.date.value=="")
{
alert("Please enter the date.");
document.frm.date.focus();
return false;
}
if(sdateAr.length!=3)
{
alert("Please enter valid Date in mm-dd-yyyy format.");
document.frm.date.value="";
document.frm.date.focus();
return false;
}
if(document.frm.salary.value=="")
{
alert("Please enter the salary.");
document.frm.salary.focus();
return false;
}
}
</script>
</head>
<body>
<%
String salary="";
String date="";
String success_msg="";
if(request.getParameter("salary")!=null && request.getParameter("date")!=null )
{
salary = request.getParameter("salary");
date = request.getParameter("date");
String strar[] = date.split("-");
if(salary !="" && date!="")
{
String cdate = strar[2]+"-" + strar[0]+"-"+ strar[1];
Connection conn = null;
String url = "jdbc:mysql://localhost:3306/";
String dbName = "sumcount";
String driver = "com.mysql.jdbc.Driver";
String userName = "root";
String password = "root";
int sumcount=0;
Statement st;
try {
Class.forName(driver).newInstance();
conn = DriverManager.getConnection(url+dbName,userName,password);
String query =
"insert into sumcount set emp_salary='"+salary+"',date='"+cdate+"'";
st = conn.createStatement();
int ir = st.executeUpdate(query);
if(ir>0)
{
success_msg ="You entered Successfully Salary.";
}
}
catch (Exception e) {
e.printStackTrace();
}
}
salary ="";
date ="";
}
%>
<br><br><br>
<form method="post" name="frm" onSubmit="validate();">
<table border="0" width="400px" align="center" bgcolor="#CDFFFF">
<tr>
<td colspan=2 style="font-size:10pt;color:#ff0000;" align="center"><%=success_msg%>
</td>
</tr>
<tr>
<td colspan=2 style="font-size:12pt;color:#00000;" align="center">
<h3>Enter Date and Salary</h3></td></tr>
<tr><td> </td></tr>
<tr><td ><b>Date:</b></td><td><input type="text" name="date" id="cdate">
<input type=button value="Select Date" onclick="displayDatePicker('date', this);">
</td></tr>
<tr><td><b>Salary:</b></td><td><input type="text" name="salary"></td></tr>
<tr><td> </td></tr>
<tr><td colspan=2 align="center">
<input type="submit" name="submit" value="Save"></td></tr>
</table>
</form>
<table border="0" width="100%" align="center">
<br>
<br>
<tr>
<td width="100%" align="center">
<a href="resultForm.jsp" style="font-size:14pt;color:blue;" >
Click here to Show Total Salary</a></td>
</tr>
</table>
</body>
</html>
Here is the output of this program:
Step 2:Create a web page (resultForm.jsp) to Select a Date.
<%@ page import="java.sql.*" %>
<html>
<head>
<style>
A:hover {text-decoration: none;
border: 0px;
font-size:14pt;
color: #2d2b2b; }
</style>
<link rel="stylesheet" type="text/css" href="datepicker.css"/>
<script type="text/javascript" src="datepicker.js"></script>
<script>
function validate()
{
var sdate = document.frm.date.value;
var sdateAr=sdate.split("-");
var ldate = document.frm.edate.value;
var ldateAr=ldate.split("-");
if(document.frm.date.value==""){
alert("Please enter Start Date.");
document.frm.date.focus();
return false;
}
if(document.frm.edate.value==""){
alert("Please enter End Date.");
document.frm.edate.focus();
return false;
}
if(sdateAr.length!=3){
alert("Please enter valid Start Date in mm-dd-yyyy format.");
document.frm.date.value="";
document.frm.date.focus();
return false;
}
if(ldateAr.length!=3){
alert("Please enter valid End Date in mm-dd-yyyy format.");
document.frm.edate.value="";
document.frm.edate.focus();
return false;
}
if(sdateAr[0]>ldateAr[0] || sdateAr[1]>ldateAr[1] || sdateAr[0]>ldateAr[0]){
alert("Start Date must be less then End date.");
document.frm.edate.focus();
return false;
}
return true;
}
</script>
</head>
<body>
<br><br><br>
<form method="post" name="frm" action="sumcount" onSubmit="return validate();">
<table border="0" width="400" align="center" bgcolor="#CDFFFF">
<tr>
<td colspan=2 style="font-size:12pt;color:#00000;" align="center">
<h3>Please Select Start and End Date</h3>
</td>
</tr>
<tr><td ><b>Start Date:</b></td><td><input type="text" name="date" id="cdate">
<input type=button value="Select Date" onclick="displayDatePicker('date', this);">
</td></tr>
<tr><td ><b>End Date:</b></td><td><input type="text" name="edate" id="edate">
<input type=button value="Select Date" onclick="displayDatePicker('edate', this);">
</td></tr>
<tr><td> </td></tr>
<tr><td colspan=2 align="center"><input type="submit" name="submit" value="Submit"></td></tr>
</table>
</form>
</body>
</html>
Step 2:Create a Servlet (sumcount.java) to retrieve data.
In this Servlet retrieve the data using mysql Query
select sum(emp_salary)
from sumcount where date>='"+cdate+"' and date<='"+ldate+"'";
The cdate and ldate are the posted data from "resultForm.jsp".
After result set in a attribute variable "sumcount"
request.setAttribute("sumcount",sumcount);
and forward to "result_count.jsp" to show the output.
import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;
import javax.sql.*;
import java.sql.*;
public class sumcount extends HttpServlet{
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException,IOException{
response.setContentType("text/html");
PrintWriter out = response.getWriter();
System.out.println("MySQL Connect Example.");
Connection conn = null;
String url = "jdbc:mysql://localhost:3306/";
String dbName = "sumcount";
String driver = "com.mysql.jdbc.Driver";
String userName = "root";
String password = "root";
int sumcount=0;
Statement st;
try {
Class.forName(driver).newInstance();
conn = DriverManager.getConnection(url+dbName,userName,password);
System.out.println("Connected to the database");
String date = request.getParameter("date");
String edate = request.getParameter("edate");
String strar[] = date.split("-");
String strar2[] = edate.split("-");
String cdate = strar[2]+"-" + strar[0]+"-"+ strar[1];
String ldate = strar2[2]+"-" + strar2[0]+"-"+ strar2[1];
String query =
"select sum(emp_salary)
from sumcount where date>='"+cdate+"' and date<='"+ldate+"'";
System.out.println("query " + query);
st = conn.createStatement();
ResultSet rs = st.executeQuery(query);
if(rs.next())
{
sumcount = rs.getInt(1);
}
request.setAttribute("sumcount",sumcount);
request.setAttribute("sdate",date);
request.setAttribute("edate",edate);
String nextJSP = "/result_count.jsp";
RequestDispatcher dispatcher =
getServletContext().getRequestDispatcher(nextJSP);
dispatcher.forward(request,response);
out.println(query);
conn.close();
System.out.println("Disconnected from database");
} catch (Exception e) {
e.printStackTrace();
}
}
}
Save the above file into "sum\WEB-INF\classes" directory.
Step 3: Mapping the servlet (sumcount.java) in to web.xml file:
<?xml version="1.0" encoding="ISO-8859-1"?>
<web-app xmlns="http://java.sun.com/xml/ns/javaee"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd"
version="2.5">
<display-name>Welcome to Tomcat</display-name>
<description>
Welcome to Tomcat
</description>
<servlet>
<servlet-name>sumcount</servlet-name>
<servlet-class>sumcount</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>sumcount</servlet-name>
<url-pattern>/sumcount</url-pattern>
</servlet-mapping>
</web-app>
Step 4: Now compile the java code using javac command from command prompt.
Step 5:Create a Bean File (SumCountBean.java) for mapping the result data.
package com;
public class SumCountBean {
private int sumCount;
private String sdate="";
private String edate="";
public void setSumCount(int sumCount)
{
this.sumCount = sumCount;
}
public int getSumCount() {
return sumCount;
}
public void setSdate(String sdate)
{
this.sdate = sdate;
}
public String getSdate() {
return sdate;
}
public void setEdate(String edate)
{
this.edate = edate;
}
public String getEdate() {
return edate;
}
}
Successful Output of the program:
Download the full web application shows here.