JSP+Servlet技术生成报表（详细版）

JSP+Servlet技术生成报表

报表：

以格式化的形式输出数据，对数据进行分组、汇总、计算等操作。通过报表、图表或嵌入式图像等形式来丰富数据的显示。

报表生成的关键要素：1）后台数据抽取；2）数据项逻辑运算；3）前台表格展现（对应三层）

报表在项目中的地位：

A.面向管理层和决策层：通过报表分析企业的未来的发展走向，预言一部分未来企业将要发生的的重大事件。

B.充分展现系统数据的价值：通过报表展现系统的价值，如现在火热的大数据，根据一套套报表，分析用户习惯、将要发生的事情、事物的必要联系。（总结历史，预测未来。）

统计信息的特征：1.数量性；2.综合性

表报的几种展现形式：

1）普通报表（二维）；2）图报表；3)嵌入式报表

一、报表生成原理及核心技术

后台数据抽取

数据项逻辑运算

前台表格展现

二、所需核心技术依赖

三、原生态Java生成报表实例

案例描述：一家经营数码产品的网店需要一张报表来分析经营情况

利润表

 

数据库共三张表分别为：

商品信息表

CREATE TABLE `goodslist` (
  `GOODS_ID` varchar(255) NOT NULL COMMENT '商品ID',
  `GOODS_NAME` varchar(255) DEFAULT NULL COMMENT ' 商品名称',
  `COST_PRICE` decimal(11,0) DEFAULT NULL COMMENT '成本价',
  `SELLING_PRICE` decimal(11,0) DEFAULT NULL COMMENT '售价',
  `MANUFACTURER` varchar(255) DEFAULT NULL COMMENT '制造商'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

买家信息表

CREATE TABLE `users` (
  `USERID` varchar(20) NOT NULL COMMENT '买家ID',
  `NAME` varchar(20) NOT NULL COMMENT '买家姓名',
  `AGE` decimal(3,0) DEFAULT NULL COMMENT '买家年龄',
  `SEX` varchar(2) DEFAULT NULL COMMENT '买家性别',
  `AREA` varchar(30) DEFAULT NULL COMMENT '买家所在地',
  `TEL` decimal(16,0) DEFAULT NULL COMMENT '买家电话',
  PRIMARY KEY (`USERID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

交易信息表

CREATE TABLE `tranding_information` (
  `TRANDING_ID` decimal(11,0) NOT NULL COMMENT '交易ID',
  `TRANDING_GOODS_ID` decimal(11,0) NOT NULL COMMENT '交易商家ID',
  `TRANDING_UER_ID` decimal(11,0) NOT NULL COMMENT '交易买家ID',
  `TRANDING_NUMBER` decimal(11,0) DEFAULT NULL COMMENT '交易数量'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 新建JavaWeb工程：Servlet控制层，beans数据模型，jdbc数据连接，service逻辑与数据库交换

package beans;
/*
商品信息表
*/
public class Goods {
    //商品ID
	private int goodsId;
	//商品名称
	private String goodsName;
	//成本价
	private int costPrice;
	//售价
	private int sellingPrice;
	//制造商
	private String manufacture;
	public int getGoodsId() {
		return goodsId;
	}
	public void setGoodsId(int goodsId) {
		this.goodsId = goodsId;
	}
	public String getGoodsName() {
		return goodsName;
	}
	public void setGoodsName(String goodsName) {
		this.goodsName = goodsName;
	}
	public int getCostPrice() {
		return costPrice;
	}
	public void setCostPrice(int costPrice) {
		this.costPrice = costPrice;
	}
	public int getSellingPrice() {
		return sellingPrice;
	}
	public void setSellingPrice(int sellingPrice) {
		this.sellingPrice = sellingPrice;
	}
	public String getManufacture() {
		return manufacture;
	}
	public void setManufacture(String manufacture) {
		this.manufacture = manufacture;
	}
}

package beans;
/*
利润
*/

public class Profit {
	
	private String goodsName;
	private int goodsId;
	private int costPrice;
	private int sellingPrice;
	private int trandingNum;
	private int times;
	private int profit;
	public String getGoodsName() {
		return goodsName;
	}
	public void setGoodsName(String goodsName) {
		this.goodsName = goodsName;
	}
	public int getGoodsId() {
		return goodsId;
	}
	public void setGoodsId(int goodsId) {
		this.goodsId = goodsId;
	}
	public int getCostPrice() {
		return costPrice;
	}
	public void setCostPrice(int costPrice) {
		this.costPrice = costPrice;
	}
	public int getSellingPrice() {
		return sellingPrice;
	}
	public void setSellingPrice(int sellingPrice) {
		this.sellingPrice = sellingPrice;
	}
	public int getTrandingNum() {
		return trandingNum;
	}
	public void setTrandingNum(int trandingNum) {
		this.trandingNum = trandingNum;
	}
	public int getTimes() {
		return times;
	}
	public void setTimes(int times) {
		this.times = times;
	}
	public int getProfit() {
		return profit;
	}
	public void setProfit(int profit) {
		this.profit = profit;
	}
	
}

package beans;
/*
交易信息表

*/
public class TradingInf {

	//交易ID
	private int trandingId;
	//交易商品ID
	private int trandingGoodsId;
	//交易买家ID
	private int trandingUserId;
	//交易数量
	private int trandingNumber;
	public int getTrandingId() {
		return trandingId;
	}
	public void setTrandingId(int trandingId) {
		this.trandingId = trandingId;
	}
	public int getTrandingGoodsId() {
		return trandingGoodsId;
	}
	public void setTrandingGoodsId(int trandingGoodsId) {
		this.trandingGoodsId = trandingGoodsId;
	}
	public int getTrandingUserId() {
		return trandingUserId;
	}
	public void setTrandingUserId(int trandingUserId) {
		this.trandingUserId = trandingUserId;
	}
	public int getTrandingNumber() {
		return trandingNumber;
	}
	public void setTrandingNumber(int trandingNumber) {
		this.trandingNumber = trandingNumber;
	}
	
}

package beans;
/*
买家信息表
*/
public class Users {
	//买家Id
	private String userId;
	//买家姓名
	private String userName;
	//买家年龄
	private int userAge;
	//买家性别
	private String userSex;
	//买家所在地区
	private String userArea;
	//买家电话
	private int userTel;
	public String getUserId() {
		return userId;
	}
	public void setUserId(String userId) {
		this.userId = userId;
	}
	public String getUserName() {
		return userName;
	}
	public void setUserName(String userName) {
		this.userName = userName;
	}
	public int getUserAge() {
		return userAge;
	}
	public void setUserAge(int userAge) {
		this.userAge = userAge;
	}
	public String getUserSex() {
		return userSex;
	}
	public void setUserSex(String userSex) {
		this.userSex = userSex;
	}
	public String getUserArea() {
		return userArea;
	}
	public void setUserArea(String userArea) {
		this.userArea = userArea;
	}
	public int getUserTel() {
		return userTel;
	}
	public void setUserTel(int userTel) {
		this.userTel = userTel;
	}
	

}

jdbc

package jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
/*
建立数据库连接
*/
public class jdbcConn {
	//
	private static String url="jdbc:mysql://localhost:3306/report";
	private static String user="root";
	private static String password="123456";
	
	public static Connection conn;
	public static PreparedStatement ps;
	public static ResultSet rs;
	public static Statement st;
	
	
	
	public static Connection getConnection(){
		try {
			Class.forName("com.mysql.jdbc.Driver");
			//获取连接，通过驱动管理
			conn=DriverManager.getConnection(url,user,password);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return conn;
	}
}

Service

package service;


import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import beans.Profit;
import jdbc.jdbcConn;

public class Service {
	private Connection daconnection;
	private Statement st,st1,st2;
	private ResultSet rs,rs1,rs2;
	private String sql;
	private List list;
	private Profit pf;
	
	public List getProfit(){
		list=new ArrayList();
		daconnection=jdbcConn.getConnection();
		try {
			st=(Statement)daconnection.createStatement();
			st1=(Statement)daconnection.createStatement();
			st2=(Statement)daconnection.createStatement();
			sql="SELECT g.GOODS_NAME goodsName,g.SELLING_PRICE selling,g.COST_PRICE costPrice,g.GOODS_ID goodsId FROM goodslist g,tranding_information t WHERE t.TRANDING_GOODS_ID=g.GOODS_ID GROUP BY g.GOODS_NAME";
			rs=st.executeQuery(sql);
			
			int temp;
			while(rs.next()){
				pf=new Profit();
				pf.setGoodsName(rs.getString("goodsName"));
				pf.setSellingPrice(rs.getInt("selling"));
				pf.setCostPrice(rs.getInt("costPrice"));
				pf.setGoodsId(rs.getInt("goodsId"));
				
				temp=0;
				temp=pf.getSellingPrice()-pf.getCostPrice();
				sql="SELECT SUM(t.TRANDING_NUMBER) sumNumber FROM tranding_information t WHERE t.TRANDING_GOODS_ID= "+pf.getGoodsId();
				rs1=st1.executeQuery(sql);
				while(rs1.next()){
					pf.setTrandingNum(rs1.getInt("sumNumber"));
				}
				
				pf.setProfit(temp*pf.getTrandingNum());
				sql="SELECT COUNT(t.TRANDING_ID) times FROM tranding_information t WHERE t.TRANDING_GOODS_ID="+pf.getGoodsId();
				rs2=st2.executeQuery(sql);
				while(rs2.next()){
					pf.setTimes(rs2.getInt("times"));
				}
				list.add(pf);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	
}

Servlet

package servlet;

import java.io.IOException;
import java.util.List;

import javax.jws.WebService;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import service.Service;
public class ShowReport extends HttpServlet {
	public ShowReport(){
		super();
	}
	public void init() throws ServletException{
	
	}

	protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		this.doPost(req, resp);
	}

	protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		
		List list;
		Service serivce=new Service();
		list=serivce.getProfit();
		req.getSession().setAttribute("PROFIT", list);
		resp.sendRedirect("index.jsp");
	}


}

 index

<%@ page language="java" import="java.util.*,beans.*" pageEncoding="utf-8"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
    <base href="<%=basePath%>">
    
    <title>原生态Java生成报表</title>
	<meta http-equiv="pragma" content="no-cache">
	<meta http-equiv="cache-control" content="no-cache">
	<meta http-equiv="expires" content="0">    
	<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
	<meta http-equiv="description" content="This is my page">
	<!--
	<link rel="stylesheet" type="text/css" href="styles.css">
	-->
	  <style type="text/css">
    table.hovertable{
        font-family:verdana,arial.sans-serif;
        font-size:13px;
        color:#333;
        border-width:1px;
        border-color:#999;
        border_collapse:collapse;
    }
    table.hovertable th{
        background-color:#c3dde0;
        border-width:1px;
        padding:8px;
        border-style:solid;
        border-color:#a9c6c9;
    }
    table.hovertable tr{
        background-color:#d4e3e5;
    }
    table.hovertable td{
        border-width:1px;
        padding:8px;
        border-style:solid;
        border-color:#a9c6c9;
    }
    </style>
  </head>
  
  <body>
    <form action="ShowReport" method="post"> 
    <input type="submit" value="生成报表">
    </form>
    <table class="hovertable">
          <tr>

              <th  colspan="5">利润表</th>
          </tr>
          <tr>
              <th>序号</th>
              <th>商品名称</th>
              <th>卖出数量</th>
              <th>交易笔数</th>
              <th>盈利额</th>
          </tr>
  <%
  List list=null;
  if(session.getAttribute("PROFIT")!=null){
  	list=(List)session.getAttribute("PROFIT");
  	if(list.size()>0){
  		int temp=0;
  		int temp1=0;
  		int temp2=0;
  		int temp3=0;
  		Profit pf;
  	for(int i=0;i<list.size();i++){
  		pf=new Profit();
  		pf=(Profit)list.get(i);
  		temp1+=pf.getTrandingNum();
        temp2+=pf.getTimes();
        temp3+=pf.getProfit();
  	%>
  <tr onmouseover="this.style.backgroundColor='#ff6';" onmouseout="this.style.backgroundColor='#d4e3e5';">
                          <td><%=temp+=1 %></td>
                          <td><%=pf.getGoodsName() %></td>
                          <td><%=pf.getTrandingNum() %></td>
                          <td><%=pf.getTimes() %></td>
                          <td><%=pf.getProfit() %></td>
                      </tr>
                      <% 
  		}
  		%>
  		   <tr >
          <td colspan="2">合计</td>
          <th><%=temp1%></th>
          <th><%=temp2%></th>
          <th><%=temp3%></th>
      </tr>
      <%
  	}
  }
  
   %>
   </table>
  </body>
</html>

注意：需要在web.xml里面配置（慕课网的朋友如果报错找不到“ShowReport”可能就是这里没有配置）可能根据版本不一样，所需配置不一样，可根据自己版本百度相对应的解决方法。

<servlet>
        <servlet-name>ShowReport</servlet-name>
        <servlet-class>servlet.ShowReport</servlet-class>
    </servlet>

    <servlet-mapping>
        <servlet-name>ShowReport</servlet-name>
        <url-pattern>/ShowReport</url-pattern>
    </servlet-mapping>

 在运行测试时，一定记得导入驱动包，我这里是mysql，表的设计已经在前面给出。

     谈不上什么原创，换汤不换药，在这里，我也是看了很多博客，大体上都差不多，唯一不同的是他们所陈述的着重点不同，因而并不是一个完整的分享，以上内容为博客转载+慕课网学习所得一个完整课运行的项目（以上就足以运行出来，如还需源码请私聊我），如有侵权，请及时联系我。