javaWeb之jsp+servlet生成报表
数据库:
-- Table: good
-- DROP TABLE good;
create sequence usr_id_seq start 1;
CREATE TABLE good
(
good_id integer NOT NULL DEFAULT nextval('usr_id_seq'::regclass),
good_name character varying(255) NOT NULL,
good_cost_price integer NOT NULL,
good_selling_price integer NOT NULL,
good_maker character varying(255) NOT NULL,
CONSTRAINT good_pkey PRIMARY KEY (good_id)
)
WITH (
OIDS=FALSE
);
ALTER TABLE good
OWNER TO fzw;
-- Table: buyer
-- DROP TABLE buyer;
CREATE TABLE buyer
(
buyer_id integer NOT NULL DEFAULT nextval('usr_id_seq'::regclass),
buyer_name character varying(255) NOT NULL,
buyer_age integer NOT NULL,
buyer_sex character varying(255) NOT NULL,
buyer_area character varying(255) NOT NULL,
buyer_tel integer NOT NULL,
CONSTRAINT buyer_pkey PRIMARY KEY (buyer_id)
)
WITH (
OIDS=FALSE
);
ALTER TABLE buyer
OWNER TO fzw;
-- Table: transaction
-- DROP TABLE transaction;
CREATE TABLE transaction
(
transaction_id integer NOT NULL DEFAULT nextval('usr_id_seq'::regclass),
transaction_good_id integer NOT NULL,
transaction_buyer_id integer NOT NULL,
transaction_number integer NOT NULL,
CONSTRAINT transaction_pkey PRIMARY KEY (transaction_id)
)
WITH (
OIDS=FALSE
);
ALTER TABLE transaction
OWNER TO fzw;
Service.java
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 dbconnection;
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();
dbconnection=jdbcConn.getConnection();
try {
st=(Statement)dbconnection.createStatement();
st1=(Statement)dbconnection.createStatement();
st2=(Statement)dbconnection.createStatement();
sql="select g.good_name goodsName,g.good_selling_price selling,g.good_cost_price costPrice,g.good_id goodsId"+
"from goodlist g ,trading_information t"+
"where t.trading_good_id=g.good_id "
+ "group by g.good_name,g.good_selling_price,g.good_cost_price,g.good_id";
rs=st.executeQuery(sql);
int temp;
while(rs.next()){
pf=new Profit();
pf.setGoodsName(rs.getString("goodsName"));
pf.setGoodsSellingPrice(rs.getInt("selling"));
pf.setGoodsCostPrice(rs.getInt("costPrice"));
pf.setGoodsId(rs.getInt("goodsId"));
temp=0;
temp=pf.getGoodsSellingPrice()-pf.getGoodsCostPrice();
sql="select sum(t.trading_number) sumNum from trading_information t where t.trading_good_id="+pf.getGoodsId();
rs1= st1.executeQuery(sql);
while(rs1.next()){
pf.setTransactionNumber(rs1.getInt("sumNum"));
}
pf.setProfit(temp*pf.getTransactionNumber());
sql="select count(t.trading_id) times from trading_information t where t.trading_good_id="+pf.getGoodsId();
rs2= st2.executeQuery(sql);
while(rs1.next()){
pf.setTimes(rs2.getInt("times"));
}
list.add(pf);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return list;
}
}
ShowReport.java:
package servlet;
import java.io.IOException;
import java.util.List;
import javax.servlet.ServletConfig;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.rowset.serial.SerialException;
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 service=new Service();
list=service.getProfit();
req.getSession().setAttribute("PROFIT", list);
resp.sendRedirect("index.jsp");
}
}
jdbcConn.java
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:postgresql://localhost:5432/table";
private static String user="fzw";
private static String password="root";
public static Connection conn;
public static PreparedStatement ps;
public static ResultSet rs;
public static Statement st;
public static Connection getConnection(){
try {
Class.forName("org.postgresql.Driver");
conn=DriverManager.getConnection(url, user, password);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return conn;
}
}
index.jsp
<%@ page language="java" import="java.util.*,beans.*" contentType="text/html; charset=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);
%>
<tr onmouseover="this.style.backgroundColor='#ff6';" onmouseout="this.style.backgroundColor='#d4e3e5';">
<td><%=temp+=1 %></td>
<td><%=pf.getGoodsName() %>></td>
<td><%=pf.getTransactionNumber() %></td>
<td><%=pf.getTimes() %></td>
<td><%=pf.getProfit() %></td>
</tr>
<%
}
}
}
%>
</table>
</body>
</html>
beans层:
package beans;
public class Buyer {
private int buyerId;
private String buyerName;
private int buyerAge;
private String buyerSex;
private String buyerArea;
private int buyerTel;
public int getBuyerId() {
return buyerId;
}
public void setBuyerId(int buyerId) {
this.buyerId = buyerId;
}
public String getBuyerName() {
return buyerName;
}
public void setBuyerName(String buyerName) {
this.buyerName = buyerName;
}
public int getBuyerAge() {
return buyerAge;
}
public void setBuyerAge(int buyerAge) {
this.buyerAge = buyerAge;
}
public String getBuyerSex() {
return buyerSex;
}
public void setBuyerSex(String buyerSex) {
this.buyerSex = buyerSex;
}
public String getBuyerArea() {
return buyerArea;
}
public void setBuyerArea(String buyerArea) {
this.buyerArea = buyerArea;
}
public int getBuyerTel() {
return buyerTel;
}
public void setBuyerTel(int buyerTel) {
this.buyerTel = buyerTel;
}
}
package beans;
public class Goods {
private int goodsId;
private String goodsName;
private int goodsCostPrice;
private int goodsSellingPrice;
private String goodsMaker;
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 getGoodsCostPrice() {
return goodsCostPrice;
}
public void setGoodsCostPrice(int goodsCostPrice) {
this.goodsCostPrice = goodsCostPrice;
}
public int getGoodsSellingPrice() {
return goodsSellingPrice;
}
public void setGoodsSellingPrice(int goodsSellingPrice) {
this.goodsSellingPrice = goodsSellingPrice;
}
public String getGoodsMaker() {
return goodsMaker;
}
public void setGoodsMaker(String goodsMaker) {
this.goodsMaker = goodsMaker;
}
}
package beans;
public class Profit {
private String goodsName;
private int goodsId;
private int goodsCostPrice;
private int goodsSellingPrice;
private int transactionNumber;
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 getGoodsCostPrice() {
return goodsCostPrice;
}
public void setGoodsCostPrice(int goodsCostPrice) {
this.goodsCostPrice = goodsCostPrice;
}
public int getGoodsSellingPrice() {
return goodsSellingPrice;
}
public void setGoodsSellingPrice(int goodsSellingPrice) {
this.goodsSellingPrice = goodsSellingPrice;
}
public int getTransactionNumber() {
return transactionNumber;
}
public void setTransactionNumber(int transactionNumber) {
this.transactionNumber = transactionNumber;
}
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 Transaction {
private int transactionId;
private int transactionGoodId;
private int transactionBuyerId;
private int transactionNumber;
public int getTransactionId() {
return transactionId;
}
public void setTransactionId(int transactionId) {
this.transactionId = transactionId;
}
public int getTransactionGoodId() {
return transactionGoodId;
}
public void setTransactionGoodId(int transactionGoodId) {
this.transactionGoodId = transactionGoodId;
}
public int getTransactionBuyerId() {
return transactionBuyerId;
}
public void setTransactionBuyerId(int transactionBuyerId) {
this.transactionBuyerId = transactionBuyerId;
}
public int getTransactionNumber() {
return transactionNumber;
}
public void setTransactionNumber(int transactionNumber) {
this.transactionNumber = transactionNumber;
}
}
注:web.xml需要自行进行配置