package cn.lanz.beans;
public class Buyer {
private Integer buy_id;
private String buy_name;//买家姓名
private Integer buy_age;//年龄
private String buy_sex;//性别
private String buy_addr;//地址
private Integer tel;//电话
public Integer getBuy_id() {
return buy_id;
}
public void setBuy_id(Integer buy_id) {
this.buy_id = buy_id;
}
public String getBuy_name() {
return buy_name;
}
public void setBuy_name(String buy_name) {
this.buy_name = buy_name;
}
public Integer getBuy_age() {
return buy_age;
}
public void setBuy_age(Integer buy_age) {
this.buy_age = buy_age;
}
public String getBuy_sex() {
return buy_sex;
}
public void setBuy_sex(String buy_sex) {
this.buy_sex = buy_sex;
}
public String getBuy_addr() {
return buy_addr;
}
public void setBuy_addr(String buy_addr) {
this.buy_addr = buy_addr;
}
public Integer getTel() {
return tel;
}
public void setTel(Integer tel) {
this.tel = tel;
}
}
package cn.lanz.beans;
public class Deal {
private Integer deal_id;
private Integer deal_goods_id;
private Integer deal_buy_id;//买家ID
private Integer deal_number;//数量
public Integer getDeal_id() {
return deal_id;
}
public void setDeal_id(Integer deal_id) {
this.deal_id = deal_id;
}
public Integer getDeal_goods_id() {
return deal_goods_id;
}
public void setDeal_goods_id(Integer deal_goods_id) {
this.deal_goods_id = deal_goods_id;
}
public Integer getDeal_buy_id() {
return deal_buy_id;
}
public void setDeal_buy_id(Integer deal_buy_id) {
this.deal_buy_id = deal_buy_id;
}
public Integer getDeal_number() {
return deal_number;
}
public void setDeal_number(Integer deal_number) {
this.deal_number = deal_number;
}
}
package cn.lanz.beans;
public class Goods {
private Integer goods_id;
private String goods_name;
private Integer goods_price;
private Integer selling_price;
private String manufacturer;
public Integer getGoods_id() {
return goods_id;
}
public void setGoods_id(Integer goods_id) {
this.goods_id = goods_id;
}
public String getGoods_name() {
return goods_name;
}
public void setGoods_name(String goods_name) {
this.goods_name = goods_name;
}
public String getManufacturer() {
return manufacturer;
}
public void setManufacturer(String manufacturer) {
this.manufacturer = manufacturer;
}
public Integer getGoods_price() {
return goods_price;
}
public void setGoods_price(Integer goods_price) {
this.goods_price = goods_price;
}
public Integer getSelling_price() {
return selling_price;
}
public void setSelling_price(Integer selling_price) {
this.selling_price = selling_price;
}
}
package cn.lanz.dao;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
public class DB {
private static String url="jdbc:mysql:///statement";
private static String drv="com.mysql.jdbc.Driver";
private static String user="root";
private static String pwd="";
public static Connection conn;
public static PreparedStatement pps;
public static ResultSet rs;
public static Statement st;
public static Connection getConnection() throws Exception{
Class.forName(drv);
conn=DriverManager.getConnection(url, user, pwd);
return conn;
}
}
package cn.lanz.service;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import cn.lanz.dao.DB;
import cn.lanz.utils.Profit;
public class Services {
private Connection conn;
private Statement st,st1,st2;
private ResultSet rs,rs1,rs2;
private String sql;
private List list;
private Profit pf;
public List getProfit() throws Exception{
list=new ArrayList();
conn=DB.getConnection();
st=conn.createStatement();//连接数据库
st1=conn.createStatement();//连接数据库
st2=conn.createStatement();//连接数据库
sql="select g.goods_name goodsName,g.selling_price sellingPrice,g.goods_price goodsPrice,g.goods_id goodsId from goods g,deal d " +
"where d.deal_goods_id=g.goods_id " +
"GROUP BY g.goods_name,g.selling_price,g.goods_price,g.goods_id";
rs=st.executeQuery(sql);
int temp;
while(rs.next()){
pf=new Profit();
pf.setGoodsName(rs.getString("goodsName"));
pf.setCostPrice(rs.getInt("goodsPrice"));
pf.setSellingPrice(rs.getInt("sellingPrice"));
pf.setGoodsId(rs.getInt("goodsId"));
temp=0;
temp=pf.getSellingPrice()-pf.getCostPrice();
sql="select sum(d.deal_number) dealunm from deal d where d.deal_goods_id="+pf.getGoodsId();
rs1=st1.executeQuery(sql);
while(rs1.next()){
pf.setTredNum(rs1.getInt("dealunm"));
}
pf.setPorfit(temp*pf.getTredNum());
sql="select count(d.deal_buy_id) dealId from deal d where d.deal_goods_id="+pf.getGoodsId();
rs2=st2.executeQuery(sql);
while(rs2.next()){
pf.setTimes(rs2.getInt("dealId"));
}
list.add(pf);
}
return list;
}
}
package cn.lanz.servlet;
import java.io.IOException;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import cn.lanz.service.Services;
public class ShowReport extends HttpServlet {
private static final long serialVersionUID = 1L;
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException {
// TODO Auto-generated method stub
this.doPost(req, resp);
}
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException {
Services services=new Services();
try {
List list=services.getProfit();
req.getSession().setAttribute("lists", list);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
resp.sendRedirect("index.jsp");
}
}
package cn.lanz.utils;
public class Profit {
private String goodsName;
private int goodsId;
private int costPrice;
private int sellingPrice;
private int tredNum;
private int times;
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 getTredNum() {
return tredNum;
}
public void setTredNum(int tredNum) {
this.tredNum = tredNum;
}
public int getTimes() {
return times;
}
public void setTimes(int times) {
this.times = times;
}
public int getPorfit() {
return porfit;
}
public void setPorfit(int porfit) {
this.porfit = porfit;
}
private int porfit;
}
<%@page import="cn.lanz.utils.Profit"%>
<%@page import="java.util.List"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
</head>
<body>
<form action="ShowReport" method="post">
<input type="submit" value="生成报表"/>
</form>
<table cellpadding="0" cellspacing="0" border="1">
<tr>
<th colspan="5">利润表</th>
</tr>
<tr>
<th>序号</th>
<th>商品名称</th>
<th>卖出数量</th>
<th>交易笔数</th>
<th>盈利额</th>
</tr>
<%
List list=null;
if(session.getAttribute("lists")!=null){
list=(List)session.getAttribute("lists");
int temp=0;
int temp1 =0;
int temp2=0;
int temp3=0;
Profit pf;
if(list.size()>0){
for(int i=0;i<list.size();i++){
pf=new Profit();
pf=(Profit)list.get(i);
temp1+=pf.getTredNum();
temp2+=pf.getTimes();
temp3+=pf.getPorfit();
%>
<tr>
<td><%= temp+=1 %></td>
<td><%=pf.getGoodsName() %></td>
<td><%=pf.getTredNum() %></td>
<td><%=pf.getTimes() %></td>
<td><%=pf.getPorfit() %></td>
</tr>
<%
}
%>
<tr>
<td colspan="2">合计</td>
<td><%=temp1%></td>
<td><%=temp2%></td>
<td><%=temp3%></td>
</tr>
<%
}
}
%>
</table>
</body>
</html>
<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee" xmlns:web="http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd" id="WebApp_ID" version="3.0">
<display-name>statements</display-name>
<welcome-file-list>
<welcome-file>index.html</welcome-file>
<welcome-file>index.htm</welcome-file>
<welcome-file>index.jsp</welcome-file>
<welcome-file>default.html</welcome-file>
<welcome-file>default.htm</welcome-file>
<welcome-file>default.jsp</welcome-file>
</welcome-file-list>
<servlet>
<servlet-name>ShowReport</servlet-name>
<servlet-class>cn.lanz.servlet.ShowReport</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>ShowReport</servlet-name>
<url-pattern>/ShowReport</url-pattern>
</servlet-mapping>
</web-app>
-- 商品表
create table goods(
goods_id int(11) not null auto_increment,
goods_name varchar(50) not null COMMENT "商品名称",
goods_price float not null COMMENT "成本价",
selling_price float not null COMMENT "售价",
manufacturer varchar(50) not null COMMENT "制造商",
primary key(goods_id)
)DEFAULT CHARSET=utf8;
-- //
-- 买家信息
create table buyer(
buy_id int not null auto_increment,
buy_name varchar(20) not null COMMENT "买家姓名",
buy_age int(3) not null COMMENT "年龄",
buy_sex char(2) not null COMMENT "性别",
buy_addr varchar(50) not null COMMENT "地址",
tel int not null COMMENT "电话",
primary key(buy_id)
)DEFAULT CHARSET=utf8;
-- //
-- 交易信息
create table deal(
deal_id int not null auto_increment,
deal_goods_id int not null,
deal_buy_id int not null COMMENT "外键",
deal_number int not null COMMENT "数量",
primary key(deal_id),
FOREIGN key(deal_goods_id) REFERENCES goods(goods_id),
FOREIGN key(deal_buy_id) REFERENCES buyer(buy_id)
)DEFAULT CHARSET=utf8;
http://localhost:8080/statements/index.jsp