salesmanagedao.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;

public class SalesManageDAO implements SalesDAO {
	
	DbOperatorHandle doh=null;
	public SalesManageDAO() {
		// TODO Auto-generated constructor stub
		doh=new DbOperatorHandle();
	}
	
	public void add(Sales s) {
		try{
			PreparedStatement ps=doh.getCon().prepareStatement("insert sales values(null,?,?,?,?,null)");
			ps.setInt(1, s.getSales_productId());
			ps.setInt(2, s.getSales_productNum());
			ps.setInt(3, s.getSales_userId());
			ps.setInt(4, s.getSales_state());
			ps.execute();
			ps.close();
		}catch (Exception e) {			
			// TODO: handle exception
			System.out.println("添加消费信息错误");
			System.out.println(e.getMessage());
			e.printStackTrace();
		}
		finally{
			doh.closeCon();
		}
	}

	public void delete(int id) {
		// TODO Auto-generated method stub
		try{
			Statement st=doh.getCon().createStatement();
			String sql="delete from sales where sales_id="+id;
			System.out.print(sql);
			st.executeUpdate(sql);
			st.close();
		}
		catch (Exception e) {
			// TODO: handle exception
			System.out.println("删除sales失败");
			e.printStackTrace();
		}
		finally{
			doh.closeCon();
		}
	}

	public List<Sales> getAllSales() {
		// TODO Auto-generated method stub
		List<Sales> sals=null;
		try{
			sals=new ArrayList<Sales>();
			Statement st=doh.getCon().createStatement();
			String sql="select * from sales";
			ResultSet rs=st.executeQuery(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 Sales getSalesById(int id) {
		// TODO Auto-generated method stub
		Sales sales=null;
		try{
			PreparedStatement ps=doh.getCon().prepareStatement("select * from sales 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 int getTotalSales() {
		// TODO Auto-generated method stub
		int total=0;
		try{
			Statement st=doh.getCon().createStatement();
			String sql="select count(*) from sales";
			ResultSet rs=st.executeQuery(sql);
			if(rs.next()){
				total=rs.getInt(1);
			}
		}
		catch (Exception e) {
			// TODO: handle exception
			System.out.println("得到用户总数wrong");
			e.printStackTrace();
		}
		finally{
			doh.closeCon();
		}
		return total;
	}
	

	public ArrayList<Sales> getSalesBySQL(String SQL) {
		// TODO Auto-generated method stub
		ArrayList<Sales> sals=null;
		try{
			sals=new ArrayList<Sales>();
			Statement st=doh.getCon().createStatement();
			ResultSet rs=st.executeQuery(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 ArrayList<Sales> getSalesByState(int state,boolean flag){
		ArrayList<Sales> sals=null;
		try{
			sals=new ArrayList<Sales>();
			Statement st=doh.getCon().createStatement();
			String SEL_SQL = "select * from sales where sales_state= '"+state+"' order by sales_id";
			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 sales 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
		SalesDAO dao = new SalesManageDAO();
//		Sales s = new Sales(12,32,65,32);
//		dao.add(s);
//		dao.delete(4);
//		System.out.println(dao.getAllSales());
//		System.out.println(dao.getSalesById(2).getSales_date());
//		System.out.println(dao.getTotalSales());
//		Sales sales = dao.getSalesById(2);
//		sales.setSales_productNum(6);
//		dao.update(sales);
		System.out.println(dao.getSalesByState(0, false));
	}



}