sales2managedao.java

这是我们做的一个网上购物系统,包手用户和管理员两大功能模块,采用jsp+servlet+java bean技术.

package com.qyg.shop.sales;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import com.qyg.database.DbOperatorHandle;
import com.qyg.shop.product.*;

public class Sales2ManageDAO{
	
	DbOperatorHandle doh=null;
	public Sales2ManageDAO() {
		// TODO Auto-generated constructor stub
		doh=new DbOperatorHandle();
	}
	
	
	/*
	 * 功能：在sales2表里插入一条记录
	 */
	public void add(Sales s) {
		ArrayList<Sales> plist= new ArrayList<Sales>();
		int sid = s.getSales_id();
		ProductDAO dao = new ProductManageDAO();
		
		//先检查表中是否存在现在消费的商品
		if(this.getSalesByPid(s.getSales_productId())==null){ 
			
			//若不存在,则向表里插入一条消费记录
			try {
				PreparedStatement ppst=doh.getCon().prepareStatement("insert sales2 values(null,?,?,?,?,null)");
				ppst.setInt(1, s.getSales_productId());
				ppst.setInt(2, s.getSales_productNum());
				ppst.setInt(3, s.getSales_userId());
				ppst.setInt(4, s.getSales_state());
				ppst.execute();
				ppst.close();
			} catch (Exception e) {
				// TODO Auto-generated catch block
				System.out.println("添加信息错误");
				System.out.println(e.getMessage());
			}
		}
		
		//若存在,则将这条消费记录的商品数量与数据库中存在的这条消费记录的商品数量相加,修改数据库中这条消费记录的商品数量
		else{
			Sales sales = this.getSalesByPid(s.getSales_productId());
			int num = s.getSales_productNum()+sales.getSales_productNum();
			sales.setSales_productNum(num);
			this.update(sales);
		}
		
	}
	
	public Sales getSalesByPid(int product_id){
		Sales sales=null;
		try{
			PreparedStatement ps=doh.getCon().prepareStatement("select * from sales2 where sales_productId=?");
			ps.setInt(1, product_id);
			ResultSet rs=ps.executeQuery();
			if(rs.next()){
				sales=this.getSalesByRs(rs);
			}
			rs.close();
			ps.close();
		}
		catch (Exception e) {
			// TODO: handle exception
			System.out.println("根据product_id获得sales出错");
		}
		finally{
			doh.closeCon();
		}
		return sales;
	}
	
	//根据sales2表中消费信息获取畅销产品列表
	public ArrayList<Product> getProductsBySals(){
		ArrayList<Sales> sals = this.getAllSales("sales_productNum", true);
		ArrayList<Product> products = new ArrayList();
		ProductDAO pdao = new ProductManageDAO();
		int size = sals.size();
		if(size>5){
			size=5;
		}
		for(int i=0;i<size;i++){
			Sales sales = sals.get(i);
			Product product = pdao.getProductById(sales.getSales_productId());
			products.add(product);
		}
		return products;
	}


	public Sales getSalesById(int id) {
		// TODO Auto-generated method stub
		Sales sales=null;
		try{
			PreparedStatement ps=doh.getCon().prepareStatement("select * from sales2 where sales_id=?");
			ps.setInt(1, id);
			ResultSet rs=ps.executeQuery();
			if(rs.next()){
				sales=this.getSalesByRs(rs);
			}
			rs.close();
			ps.close();
		}
		catch (Exception e) {
			// TODO: handle exception
			System.out.println("根据Id获得sales出错");
		}
		finally{
			doh.closeCon();
		}
		return sales;
	}

	public Sales getSalesByRs(ResultSet rs) {
		// TODO Auto-generated method stub
		Sales sales=null;
		try{
			sales=new Sales();
			sales.setSales_id(rs.getInt("sales_id"));
			sales.setSales_productId(rs.getInt("sales_productId"));
			sales.setSales_productNum(rs.getInt("sales_productNum"));
			sales.setSales_userId(rs.getInt("sales_userId"));
			sales.setSales_state(rs.getInt("sales_state"));
			sales.setSales_date(rs.getTimestamp("sales_date"));
		}
		catch (Exception e) {
			// TODO: handle exception
			System.out.print("把rs中的值返回到Sales中");
		}
		return sales;
	}
	
	public ArrayList<Sales> getAllSales(String orderby,boolean flag) {
		// TODO Auto-generated method stub
		ArrayList<Sales> sals=null;
		try{
			sals=new ArrayList<Sales>();
			Statement st=doh.getCon().createStatement();
			String SEL_SQL = "select * from sales2 order by " + orderby;
			if (flag) {
				SEL_SQL += "  desc";
			} else {
				SEL_SQL += "  asc";
			}
			ResultSet rs=st.executeQuery(SEL_SQL);
			while(rs.next()){
				Sales sales=this.getSalesByRs(rs);
				sals.add(sales);
			}
			rs.close();
			st.close();
		}
		catch (Exception e) {
			// TODO: handle exception
			System.out.println("得到所有消费信息");
			e.printStackTrace();
		}
		finally{
			doh.closeCon();
		}
		return sals;
	}

	public void update(Sales sales) {
		// TODO Auto-generated method stub
		try{
			PreparedStatement ps=doh.getCon().prepareStatement("update sales2 set sales_productId=?," +
					"sales_productNum=?,sales_userId=?,sales_state=? where sales_id=?");
			ps.setInt(1, sales.getSales_productId());
			ps.setInt(2, sales.getSales_productNum());
			ps.setInt(3, sales.getSales_userId());
			ps.setInt(4, sales.getSales_state());
			ps.setInt(5, sales.getSales_id());
			ps.execute();
			ps.close();			
		}catch (Exception e) {
			// TODO: handle exception
			System.out.println("更新消费信息失败");
			e.printStackTrace();
		}
		finally{
			doh.closeCon();
		}
	}

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		Sales2ManageDAO dao = new Sales2ManageDAO();
//		Sales s = new Sales(12,12,12,12);
//		dao.add(s);
		System.out.println(dao.getProductsBySals());
	}



}