shopcardao.java

在线购物 商品管理 用户管理 购物车 订单管理 权限控制

/**
 * 
 */
package com.qrsx.shop.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.qrsx.shop.model.Book;
import com.qrsx.shop.model.ShopCar;

/**
 *@Author:李世海	
 *@Address:青岛软件园
 *@Date: Mar 31, 2009
 */

public class ShopCarDAO extends BaseDAO {
	
	/**
	 * 创建购物车
	 * @param shopCar
	 * @throws SQLException
	 */
	public void create(ShopCar shopCar) throws SQLException{
		String sql = "insert into shopcar (userId,bookId) values(?,?)";
		ps = conn.prepareStatement(sql);
		ps.setInt(1, shopCar.getUserId());
		ps.setInt(2, shopCar.getBookId());
		ps.executeUpdate();
	}
	
	/**
	 * 删除购物车
	 * @throws SQLException 
	 */
	public void delete(Integer shopCarId) throws SQLException {
		String sql = "delete from shopcar where id=?";
		ps = conn.prepareStatement(sql);
		ps.setInt(1, shopCarId);
		ps.executeUpdate();
	}
	/**
	 * 提交订单后删除购物车里的信息
	 * @throws SQLException 
	 *
	 */
	public void deleteAll(ShopCar shopCar) throws SQLException{
		String sql = "delete from shopcar where userId=?";
		ps = conn.prepareStatement(sql);
		if(shopCar.getUserId()!=null){
		ps.setInt(1, shopCar.getUserId());
		}
		ps.executeUpdate();
	}
	
	/**
	 * 检验图书是否存在
	 * @throws SQLException 
	 */
	public ShopCar findByBookId(Integer bookId) throws SQLException{
		String sql = "select * from shopcar where bookId=?";
		ps = conn.prepareStatement(sql);
		ps.setInt(1, bookId);
		ResultSet rs = ps.executeQuery();
		ShopCar shopCar = new ShopCar();
		if(rs.next()){
			 shopCar.setBookId(rs.getInt("bookId"));
		     shopCar.setUserId(rs.getInt("userId"));
		     shopCar.setId(rs.getInt("id"));
		}
		return shopCar;
	}
	
	/**
	 * 根据图书编号和用户编号检索数据
	 * @throws SQLException 
	 */
	public ShopCar findBy(ShopCar shopCar) throws SQLException{
		String sql = "select * from shopcar where userId=? and bookId=?";
		ps = conn.prepareStatement(sql);
		ps.setInt(1, shopCar.getUserId());
		ps.setInt(2, shopCar.getBookId());
		ResultSet rs = ps.executeQuery();
		ShopCar s = new ShopCar();
		if(rs.next()){
			 s.setBookId(rs.getInt("bookId"));
		     s.setUserId(rs.getInt("userId"));
		     s.setId(rs.getInt("id"));
		}
		return s;
	}
	/**
	 * 根据主键检索图书
	 * @throws SQLException 
	 */
	
	
	public List<ShopCar> findByUserId(Integer shopCarId) throws SQLException {
		String sql = "select * from shopcar where userId=?";
		//设置参数并执行查询
		ps = conn.prepareStatement(sql);
		ps.setInt(1, shopCarId);
		ResultSet rs = ps.executeQuery();
		BookDAO bookDAO = new BookDAO();
		List<ShopCar> list = new ArrayList<ShopCar>();
		 ShopCar shopCar =null;
		while(rs.next()){
	        shopCar = new ShopCar();
	        shopCar.setBookId(rs.getInt("bookId"));
	        shopCar.setUserId(rs.getInt("userId"));
	        shopCar.setId(rs.getInt("id"));
			Book book = new Book(); 
			book = bookDAO.findById(shopCar.getBookId());
			if(book!=null){
				shopCar.setBook(book);
			}
			list.add(shopCar);
	     }
	     return list;
	}
	/**
	 * 形成初步订单时提取图书信息
	 */
	public List<Book> listBook(Integer userId) throws SQLException {
		String sql = "select * from shopcar where userId=?";
		//设置参数并执行查询
		ps = conn.prepareStatement(sql);
		ps.setInt(1, userId);
		ResultSet rs = ps.executeQuery();
		BookDAO bookDAO = new BookDAO();
		List<Book> list = new ArrayList<Book>();
		 ShopCar shopCar =null;
		while(rs.next()){
	        shopCar = new ShopCar();
	        shopCar.setBookId(rs.getInt("bookId"));
			Book book = new Book(); 
			book = bookDAO.findById(shopCar.getBookId());
			
			list.add(book);
	     }
	     return list;
	}
	/**
	 * 检索图书
	 * @throws SQLException 
	 */
	public List<ShopCar> findAll() throws SQLException{
		String sql = "select * from shopcar ";
		//执行查询
		ps=conn.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        BookDAO bookDAO = new BookDAO();
        ShopCar shopCarNew = null;
        List<ShopCar> list = new ArrayList<ShopCar>();
        while(rs.next()){
        	shopCarNew = new ShopCar();
        	shopCarNew.setBookId(rs.getInt("bookId"));
        	shopCarNew.setUserId(rs.getInt("userId"));
        	shopCarNew.setId(rs.getInt("id"));
			Book book = bookDAO.findById(shopCarNew.getId());
			shopCarNew.setBook(book);
			list.add(shopCarNew);
        }
        return list;
	}
}