Display Sum of Table Column Using In JSP

In this section, you will learn how to display sum of column from database using jsp.

In this section, you will learn how to display sum of column from database using jsp.

Display Sum of Table Column Using In JSP

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>&nbsp;</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>&nbsp;</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>&nbsp;</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.

Here is the code of sumcount:

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.

Download the application

Ads