goodsmanage.java

网上商城代码

package com.fendou.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import com.fendou.exception.GoodsException;
import com.fendou.platform.DBConnection;
import com.fendou.util.PageSplitUtil;
import com.fendou.vo.Goods;
import com.fendou.vo.GoodsStat;

public class GoodsManage {
	private Connection conn;
	private PreparedStatement pstm;	
	private Statement stm;	
	private ResultSet rs;	
	private DBConnection db=DBConnection.getInstance();
	
	public List<Goods> getNewGoods()throws GoodsException{
		String sql = "select * from T_Goods where ";       
        try{
        	conn=db.getConnection();
        	stm = conn.prepareStatement(sql);		
        	
			
        }catch(SQLException e){
        	System.out.print(e);
        }finally{
        	db.closeConnection(conn, stm, rs);        	
        }
		return null;
	}
	
	public int getAllGoodsCount(String subType,String key) throws GoodsException{
		int count=0;
		StringBuffer sql = new StringBuffer("select count(GoodsID) from T_Goods where 1=1");
		if(subType!=null && subType.length()>0){
			sql.append(" and TypeCode='"+subType+"'");
		}
		if(key!=null && key.length()>0){
			sql.append(" and GoodsName like '%"+key+"%'");
		}			
        try{
        	conn=db.getConnection();
        	stm = conn.createStatement();
        	rs=stm.executeQuery(sql.toString());
        	while(rs.next()){
        		count=rs.getInt(1);
        	}       	
        }catch(SQLException e){        	
        	throw new GoodsException("getAllGoodsCount error:"+e.getMessage());
        }finally{
        	db.closeConnection(conn, stm, rs);        	
        }
        return count;
	}
	public List<Goods> getAllGoods(String subType,String key,PageSplitUtil pageSplit) throws GoodsException{
		StringBuffer sql = new StringBuffer("select top "+pageSplit.getStartIndex()+" * from (select top "+pageSplit.getEndIndex()+" * from T_Goods where 1=1");
		if(subType!=null && subType.length()>0){
			sql.append(" and TypeCode='"+subType+"'");
		}
		if(key!=null && key.length()>0){
			sql.append(" and GoodsName like '%"+key+"%'");
		}	
		sql.append("  order by GoodsID asc) as a order by GoodsID desc");		
		Goods goods=null;
		List<Goods> list=new ArrayList<Goods>();
        try{
        	conn=db.getConnection();
        	stm = conn.createStatement();
        	rs=stm.executeQuery(sql.toString());
        	while(rs.next()){
        		goods =new Goods();	
        		goods.setGoodsID(rs.getLong(1));
        		goods.setGoodsName(rs.getString(2));
        		
        		
        		list.add(goods);
        	}	
        	
        }catch(SQLException e){        	
        	throw new GoodsException("getAllGoods error:"+e.getMessage());
        }finally{
        	db.closeConnection(conn, stm, rs);        	
        }
        return list;
	}
	
	public void addGoods(Goods goods)throws GoodsException{
		String sql = "insert into T_Goods(GoodsName,GoodsIntroduce,GoodsNormalPrice,"
		      + "GoodsMemberPrice,GoodsRebate,TypeCode,isSale,isValid,GoodsPicture,"
		      + "Creater,CreateDate,Updater,UpdateDate) values(?,?,?,?,?,?,?,?,?,?,?,?,?)"; 
		try{
			conn=db.getConnection();
			pstm =conn.prepareStatement(sql);
			pstm.setString(1, goods.getGoodsName());
			pstm.setString(1, goods.getGoodsName());
        	pstm.setString(2, goods.getGoodsIntroduce());
        	pstm.setDouble(3, goods.getNormalPrice());
        	pstm.setDouble(4, goods.getMemberPrice());
        	pstm.setFloat(5, goods.getGoodsRebate());
        	pstm.setString(6, goods.getTypeCode());
        	pstm.setString(7, goods.getSale());
        	pstm.setString(8, goods.getValid());
        	pstm.setBinaryStream(9, goods.getGoodsPicture(), goods.getBinaryLength());
            pstm.setLong(10, goods.getCreater());
            pstm.setTimestamp(11, new Timestamp(goods.getCreateDate().getTime()));
        	pstm.setLong(12, goods.getUpdater());
        	pstm.setTimestamp(13, new Timestamp(goods.getUpdateDate().getTime()));
        	pstm.execute();
		}catch(SQLException e){        	
        	throw new GoodsException("addGoods error:"+e.getMessage());
        }finally{
        	db.closeConnection(conn, pstm, rs);        	
        }
	}
	
	public Goods getGoods(long goodsID)throws GoodsException{
		String sql = "select * from T_Goods where GoodsID="+goodsID;   
		Goods goods=new Goods();
        try{
        	conn=db.getConnection();
        	stm = conn.createStatement();
        	rs=stm.executeQuery(sql);
        	while(rs.next()){
        		goods.setGoodsID(rs.getLong("GoodsID"));
        	    goods.setGoodsName(rs.getString("GoodsName"));
        	    goods.setGoodsIntroduce(rs.getString("GoodsIntroduce"));
        	}
        }catch(SQLException e){
        	throw new GoodsException("getGoods error:"+e.getMessage());
        }finally{
        	db.closeConnection(conn, stm, rs);        	
        }
		return goods;
	}
	public Goods getGoodsPicture(long goodsID)throws GoodsException{
		String sql = "select GoodsPicture from T_Goods where GoodsID="+goodsID;   
		Goods goods=new Goods();
        try{
        	conn=db.getConnection();
        	stm = conn.createStatement();
        	rs=stm.executeQuery(sql);
        	while(rs.next()){
        		goods.setGoodsPicture(rs.getBinaryStream("GoodsPicture"));
        	}
        }catch(SQLException e){
        	throw new GoodsException("getGoodsPicture error:"+e.getMessage());
        }finally{
        	db.closeConnection(conn, stm, rs);        	
        }
		return goods;
	}
	
	public List<Goods> getListGoods() throws GoodsException{
		String sql="select top 10 GoodsID,GoodsName,GoodsIntroduce,GoodsNormalPrice,GoodsMemberPrice," +
			"GoodsRebate,goods.TypeCode,GoodsNumber,GoodsGrade,isSale,isValid," +
			"goods.Creater,goods.CreateDate,goods.Updater,goods.UpdateDate,TypeDesc from T_Goods " +
			"as goods inner join T_GoodsType as goodsType on goods.typeCode=goodsType.typeCode " +
			"order by goods.UpdateDate desc";
		
		List<Goods> listGoods=new ArrayList<Goods>();		
		try{
			conn=db.getConnection();
	    	stm = conn.createStatement();
	    	rs=stm.executeQuery(sql);
			while(rs.next()){
				Goods goods=new Goods();
				goods.setGoodsID(rs.getLong(1));
				goods.setGoodsName(rs.getString(2));
				goods.setGoodsIntroduce(rs.getString(3));
				goods.setNormalPrice(rs.getFloat(4));
				goods.setMemberPrice(rs.getFloat(5));
				goods.setGoodsRebate(rs.getFloat(6));
				goods.setTypeCode(rs.getString(7));
				goods.setGoodsCount(rs.getInt(8));
				goods.setGoodsGrade(rs.getInt(9));
				goods.setSale(rs.getString(10));
				goods.setValid(rs.getString(11));
				listGoods.add(goods);
			}
		}catch(SQLException e){
			throw new GoodsException("getListGoods error:"+e.getMessage());
		}finally{
			db.closeConnection(conn, stm, rs);
		}
		return listGoods;
	}
	
	public List<GoodsStat> statGoods(String goodsID,String goodsType,String startDate,String endDate) throws GoodsException{
		StringBuffer sql = new StringBuffer("select top 20 g.GoodsID,g.GoodsName,d.GoodsPrice,d.GoodsCount ");
		sql.append("from T_OrderDetail as d inner join T_Goods  as g on g.GoodsID=d.GoodsID ");
		sql.append("inner join T_Order as o on d.OrderID=o.OrderID where o.OrderStatus='8' ");	
		
		if(startDate!=null && startDate.length()>0 && endDate!=null){
			sql.append(" and  (o.CreateDate between '"+startDate+"' and '"+endDate+"') ");
		}
		if(goodsID!=null && goodsID.length()>0){
			sql.append(" and g.GoodsID = "+goodsID);
		}	
		if(goodsType!=null && goodsType.length()>0){
			sql.append(" and g.TypeCode = "+goodsType);
		}
		sql.append(" order by d.GoodsCount desc");		
		GoodsStat goods=null;
		List<GoodsStat> list=new ArrayList<GoodsStat>();
        try{
        	conn=db.getConnection();
        	stm = conn.createStatement();
        	rs=stm.executeQuery(sql.toString());
        	while(rs.next()){
        		goods =new GoodsStat();	
        		goods.setGoodsID(rs.getLong(1));
        		goods.setGoodsName(rs.getString(2));
        		goods.setSaleAmount(rs.getDouble(3));
        		goods.setSaleCount(rs.getInt(4));        		
        		list.add(goods);
        	}	
        	
        }catch(SQLException e){        	
        	throw new GoodsException("statGoods error:"+e.getMessage());
        }finally{
        	db.closeConnection(conn, stm, rs);        	
        }
        return list;
	}
}