Home Jsp Display Sum of Table Column Using In JSP

Related Tutorials


 
 

Share on Google+Share on Google+

Display Sum of Table Column Using In JSP

Advertisement
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

Advertisement

If you enjoyed this post then why not add us on Google+? Add us to your Circles



Liked it!  Share this Tutorial


Follow us on Twitter, or add us on Facebook or Google Plus to keep you updated with the recent trends of Java and other open source platforms.

Posted on: July 26, 2008

Related Tutorials

Discuss: Display Sum of Table Column Using In JSP   View All Comments

Post your Comment


Your Name (*) :
Your Email :
Subject (*):
Your Comment (*):
  Reload Image
 
 
Comments:3
Sudhir Kumar Pandey
April 5, 2011
PHP

Program
vikas
May 24, 2012
Regarding error in the given tutorial

Hello Sir, I followed ur tutorial entitled "Display Sum Of table using in jsp" but it comes out with following error while showing the total salary .any help will be heartly appreciated.Entering salary is working fine.thnx Connected to the database query select sum(emp_salary) from sumcount where date>='2012-05-01' and date<='2012-05-30' org.apache.jasper.JasperException: Unable to compile class for JSP: An error occurred at line: 18 in the jsp file: /result_count.jsp sumcount cannot be resolved to a variable 15: 16: <td width="100%" style="font-size:14pt;color:blue;padding-left:70px;" align="left"> <b>Total Salary From : </b> 17: <jsp:useBean id="sum" class="com.SumCountBean" scope="request"> 18: <jsp:setProperty name="sum" property="sumCount" value="<%=request.getAttribute(sumcount)%>"/> 19: <jsp:setProperty name="sum" property="sdate" value="<%=request.getAttribute(sdate)%>"/> 20: <jsp:setProperty name="sum" property="edate" value="<%=request.getAttribute(edate)%>"/> 21: </jsp:useBean> An error occurred at line: 19 in the jsp file: /result_count.jsp sdate cannot be resolved to a variable 16: <td width="100%" style="font-size:14pt;color:blue;padding-left:70px;" align="left"> <b>Total Salary From : </b> 17: <jsp:useBean id="sum" class="com.SumCountBean" scope="request"> 18: <jsp:setProperty name="sum" property="sumCount" value="<%=request.getAttribute(sumcount)%>"/> 19: <jsp:setProperty name="sum" property="sdate" value="<%=request.getAttribute(sdate)%>"/> 20: <jsp:setProperty name="sum" property="edate" value="<%=request.getAttribute(edate)%>"/> 21: </jsp:useBean> 22: An error occurred at line: 20 in the jsp file: /result_count.jsp edate cannot be resolved to a variable 17: <jsp:useBean id="sum" class="com.SumCountBean" scope="request"> 18: <jsp:setProperty name="sum" property="sumCount" value="<%=request.getAttribute(sumcount)%>"/> 19: <jsp:setProperty name="sum" property="sdate" value="<%=request.getAttribute(sdate)%>"/> 20: <jsp:setProperty name="sum" property="edate" value="<%=request.getAttribute(edate)%>"/> 21: </jsp:useBean> 22: 23: Stacktrace: at org.apache.jasper.compiler.DefaultErrorHandler.javacError(DefaultErrorHandler.java:92) at org.apache.jasper.compiler.ErrorDispatcher.javacError(ErrorDispatcher.java:330) at org.apache.jasper.compiler.JDTCompiler.generateClass(JDTCompiler.java:439) at org.apache.jasper.compiler.Compiler.compile(Compiler.java:356) at org.apache.jasper.compiler.Compiler.compile(Compiler.java:334) at org.apache.jasper.compiler.Compiler.compile(Compiler.java:321) at org.apache.jasper.JspCompilationContext.compile(JspCompilationContext.java:592) at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:328) at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:313) at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:260) at javax.servlet.http.HttpServlet.service(HttpServlet.java:717) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) at org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDispatcher.java:646) at org.apache.catalina.core.ApplicationDispatcher.processRequest(ApplicationDispatcher.java:436) at org.apache.catalina.core.ApplicationDispatcher.doForward(ApplicationDispatcher.java:374) at org.apache.catalina.core.ApplicationDispatcher.forward(ApplicationDispatcher.java:302) at sumcount.doPost(sumcount.java:45) at javax.servlet.http.HttpServlet.service(HttpServlet.java:637) at javax.servlet.http.HttpServlet.service(HttpServlet.java:717) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293) at org.apache.coyote.http11.Http11AprProcessor.process(Http11AprProcessor.java:877) at org.apache.coyote.http11.Http11AprProtocol$Http11ConnectionHandler.process(Http11AprProtocol.java:594) at org.apache.tomcat.util.net.AprEndpoint$Worker.run(AprEndpoint.java:1675) at java.lang.Thread.run(Thread.java:619)
inayat
December 17, 2012
database

hello sir..i need the db for this application..plz provide db asap thanks
DMCA.com