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,表的设计已经在前面给出。
谈不上什么原创,换汤不换药,在这里,我也是看了很多博客,大体上都差不多,唯一不同的是他们所陈述的着重点不同,因而并不是一个完整的分享,以上内容为博客转载+慕课网学习所得一个完整课运行的项目(以上就足以运行出来,如还需源码请私聊我),如有侵权,请及时联系我。