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.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.
Ads