利用jsp和servlet，MySQL实现简易报表

时间：2022-03-14 15:51

技术分享

beans包和jdbc包代码不放了，麻烦

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,sql1,sql2;
   private List list;
   private Profit pf;

   public List getProfit(){
       list=new ArrayList();
       dbconnection=JdbcConn.getCon();
       try {
           st=(Statement)dbconnection.createStatement();
           st1=(Statement)dbconnection.createStatement();
           st2=(Statement)dbconnection.createStatement();
           sql="SELECT g.GOODS_NAME goodsName,g.SELLING_PRICE selling,g.COST_PRICE costPrice,g.GOODS_ID goodsId FROM t_goods g,t_trading t WHERE t.TRADING_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();

               sql1="SELECT SUM(t.TRADING_NUMBER) sunNum FROM t_trading t WHERE t.TRADING_GOODS_ID="+pf.getGoodsId();
               rs1=st1.executeQuery(sql1);
               while(rs1.next()){
                   pf.setTradingNum(rs1.getInt("sunNum"));
               }

               pf.setProfit(temp*pf.getTradingNum());
               sql2="SELECT COUNT(t.TRADING_GOODS_ID) times FROM t_trading t WHERE t.TRADING_GOODS_ID="+pf.getGoodsId();
               rs2=st2.executeQuery(sql2);
               while(rs2.next()){
                   pf.setTimes(rs2.getInt("times"));
               }

               list.add(pf);
           }
       } catch (SQLException e) {
           e.printStackTrace();
       }

       return list;
   }
}

ShowReport.java:

package servlet;

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

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import service.Service;

public class ShowReport extends HttpServlet {
   public void doPost(HttpServletRequest request, HttpServletResponse response)
           throws ServletException, IOException {
       List list;
       Service service=new Service();
       list=service.getProfit();

       request.getSession().setAttribute("PROFIT",list);
       response.sendRedirect("index.jsp");
   }

   @Override
   protected void doGet(HttpServletRequest request, HttpServletResponse response)
           throws ServletException, IOException {
       this.doPost(request, response);
   }


}

index.jsp:

<%@ 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>
    <style type="text/css">
    table.hovertable{
       font-size:13px;
       color:#333333;
       border-width:1px;
       border-color:#999999;
       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.getTradingNum();
                   temp2+=pf.getTimes();
                   temp3+=pf.getProfit();
                   %>
                       <tr onmouseover="this.style.backgroundColor=‘#ffff66‘;"
                           onmouseout="this.style.backgroundColor=‘#d4e3e5‘;">
                           <td><%=temp+=1 %></td>
                           <td><%=pf.getGoodsName() %></td>
                           <td><%=pf.getTradingNum() %></td>
                           <td><%=pf.getTimes() %></td>
                           <td><%=pf.getProfit() %></td>
                       </tr>
                   <%
               }%>
                       <tr onmouseover="this.style.backgroundColor=‘#ffff66‘;"
                           onmouseout="this.style.backgroundColor=‘#d4e3e5‘;">
                           <td colspan="2">合计</td>
                           <td><%=temp1 %></td>
                           <td><%=temp2 %></td>
                           <td><%=temp3 %></td>
                       </tr>
               <%
           }
       }
   %>
   </table>
</body>
</html>