个回答

• sunxd

  2015-12-20 20:37:40

  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;

  @SuppressWarnings("rawtypes")

  private List list;

  private Profit profit;

  @SuppressWarnings({ "rawtypes", "unchecked" })

  public List getProfit(){

  list=new ArrayList();

  dbConnection=JdbcConn.getConnection();

  try {

  st=dbConnection.createStatement();

  st1=dbConnection.createStatement();

  st2=dbConnection.createStatement();

  sql="select g.goods_name goodsName,g.selling_price selling,g.cost_price costPrice, g.goods_id goodsId from goodslist g,trading_information t where t.trading_goods_id=g.goods_id group by g.goods_name,g.selling_price,g.cost_price, g.goods_id";

  rs=st.executeQuery(sql);

  int temp;

  while(rs.next()){

  profit=new Profit();

  profit.setGoodsName(rs.getString("goodsName"));

  profit.setSellingPrice(rs.getInt("selling"));

  profit.setCostPrice(rs.getInt("costPrice"));

  profit.setGoodsId(rs.getInt("goodsId"));

  temp=0;

  temp=profit.getSellingPrice()-profit.getCostPrice();

  sql="select sum(t.trading_number)sumNum from trading_information t where t.trading_goods_id="+profit.getGoodsId();

  rs1=st1.executeQuery(sql);

  while(rs1.next()){

  profit.setTradingNum(rs1.getInt("sumNum"));

  }

  profit.setProfit(temp*profit.getTradingNum());

  sql="select count(t.trading_id)times from trading_information t where trading_goods_id="+profit.getGoodsId();

  rs2=st2.executeQuery(sql);

  while(rs2.next()){

  profit.setTimes(rs2.getInt("times"));

  }

  list.add(profit);

  System.out.println(list);

  }

  } catch (SQLException e) {

  e.printStackTrace();

  }

  return list;

  }

  /**

  * 测试

  */

  // public static void main(String[] args) {

  // Service service = new Service();

  // service.getProfit();

  // }

  }

  
  

  0