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

2023-12-27 09:28| 来源: 网络整理| 查看: 265

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

原生态Java生成报表 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; } 利润表序号商品名称卖出数量交易笔数盈利额 0){ int temp=0; int temp1=0; int temp2=0; int temp3=0; Profit pf; for(int i=0;i 合计

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

ShowReport servlet.ShowReport ShowReport /ShowReport

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

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

【本文地址】

公司简介

联系我们