booksaledao.java

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

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

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

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

/**
 *@Author:李世海	
 *@Address:青岛软件园
 *@Date: Mar 31, 2009
 */
public class BookSaleDAO extends BaseDAO{
	/**
	 * 创建BookSale
	 * @throws SQLException 
	 */
	public void create(BookSale bookSale,Integer addAmount) throws SQLException {
		String sql = "insert into booksale (bookId,amount,inDate) values (?,?,?)";
		ps = conn.prepareStatement(sql);
		ps.setInt(1, bookSale.getBookId());
		ps.setInt(2, bookSale.getAmount());
		Date date = new Date();
		SimpleDateFormat form = new SimpleDateFormat("yyyy-MM-dd");
		String time = form.format(date);
		ps.setString(3, time);
		ps.executeUpdate();
	}
	/**
	 * 删除图书
	 * @throws SQLException 
	 */
	public int delete(Integer bookSaleId) throws SQLException {
		String sql = "delete from booksale where id=?";
		ps = conn.prepareStatement(sql);
		ps.setInt(1, bookSaleId);
		return ps.executeUpdate();
	}
	/**
	 * 更新图书
	 * @throws SQLException 
	 */
	public void update(BookSale bookSale ,Integer amount ) throws SQLException {
		String sql = "update booksale set bookId=?, amount=?, inDate=? where id=?";
		
		ps = conn.prepareStatement(sql);
		ps.setInt(1, bookSale.getBookId());
		ps.setInt(2, bookSale.getAmount()+ amount);
		Date date = new Date();
		SimpleDateFormat form = new SimpleDateFormat("yyyy-MM-dd");
		String time = form.format(date);
		ps.setString(3, time);
		ps.setInt(4, bookSale.getId());
		ps.executeUpdate();
	}
	/**
     * 根据主键检索图书
     * @param bookSaleId 要检索的图书主键
     * @return 检索到的图书
     */
    public BookSale findById( Integer bookSaleId ) throws SQLException{
        String sql = "Select * from booksale where id=?";

        //设置参数值并执行查询
        ps=conn.prepareStatement(sql);
        ps.setInt(1, bookSaleId);
        ResultSet rs = ps.executeQuery();

        BookDAO bookDao = new BookDAO();
        
        BookSale bookSale = null;
        if( rs.next() ){
            bookSale = new BookSale();
            bookSale.setId( rs.getInt("id") );
            bookSale.setBookId( rs.getInt("bookId") );
            bookSale.setAmount( rs.getInt("amount") );
            bookSale.setInDate( rs.getString("inDate") );
            
            //检索级联信息
            Book book = bookDao.findById(bookSale.getBookId());
            bookSale.setBook(book);
        }
        
        return bookSale;
    }

    /**
     * 检索图书
     * @param bookSale
     * @return
     */
    public List<BookSale> findAll() throws SQLException{
        String sql = "Select * from booksale";
        ps=conn.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        BookDAO bookDao = new BookDAO();
        
        //将查询结果组装至Storage实体对象
        BookSale bookSaleNew = null;
        List<BookSale> list = new ArrayList<BookSale>();
        List<Book> listBook = new ArrayList<Book>();
        while( rs.next() ){
            bookSaleNew = new BookSale();
            bookSaleNew.setId( rs.getInt("id") );
            bookSaleNew.setBookId( rs.getInt("bookId") );
            bookSaleNew.setAmount( rs.getInt("amount") );
            bookSaleNew.setInDate( rs.getString("inDate") );

            //检索级联信息
            Book book = bookDao.findById(bookSaleNew.getBookId());
            bookSaleNew.setBook(book);
            listBook.add(book);
            list.add(bookSaleNew);
        }
        
        return list;
    }
    /**
     * 检索购物广场的图书
     */
    public List<Book> listGoods() throws SQLException{
        String sql = "Select * from booksale";
        ps=conn.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        BookDAO bookDao = new BookDAO();
        
        //将查询结果组装至Storage实体对象
        BookSale bookSaleNew = null;
       
        List<Book> listBook = new ArrayList<Book>();
        while( rs.next() ){
            bookSaleNew = new BookSale();
            bookSaleNew.setId( rs.getInt("id") );
            bookSaleNew.setBookId( rs.getInt("bookId") );
            bookSaleNew.setAmount( rs.getInt("amount") );
            bookSaleNew.setInDate( rs.getString("inDate") );

            //检索级联信息
            Book book = bookDao.findById(bookSaleNew.getBookId());
            bookSaleNew.setBook(book);
            listBook.add(book);
          
        }
        
        return listBook;
    }
    
    /**
     * 动态检索图书
     * @param bookSale
     * @return
     */
    public List<BookSale> list( Book b ) throws SQLException{
        String sql = "Select s.*,b.* from booksale s inner join book b on s.bookId=b.id where 1=1 ";
        
        String name = b.getName();
        String author =b.getAuthor();
        String publisher = b.getPublisher();
        //根据书名检索
        if( name!=null && !name.equals("") ){
            sql = sql + " and b.name like '%" + name + "%'";
        }
        
        //根据作者检索
        if( author!=null && !author.equals("") ){
            sql = sql + " and b.author like '%" + author + "%'";
        }
        
        //根据出版社检索
        if( publisher!=null && !publisher.equals("") ){
            sql = sql + " and b.publisher like '%" + publisher + "%'";
        }

        //执行查询
        ps=conn.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();

        //将查询结果组装至Storage实体对象
        BookSale bookSaleNew = null;
        List<BookSale> list = new ArrayList<BookSale>();
        while( rs.next() ){
            bookSaleNew = new BookSale();
            bookSaleNew.setId( rs.getInt("s.id") );
            bookSaleNew.setBookId( rs.getInt("s.bookId") );
            bookSaleNew.setAmount( rs.getInt("s.amount") );
            bookSaleNew.setInDate( rs.getString("s.inDate") );

            //检索级联信息
            Book book = new Book();
            book.setId( rs.getInt("b.id") );
            book.setName( rs.getString("b.name") );
            book.setAuthor( rs.getString("b.author") );
            book.setPublisher( rs.getString("b.publisher") );
            book.setPrice( rs.getDouble("b.price") );
            
            bookSaleNew.setBook(book);
            
            list.add(bookSaleNew);
        }
        
        return list;
    }
    /**
     * 顾客动态检索购物广场图书
     */
    public List<Book> listu( Book b ) throws SQLException{
        String sql = "Select s.*,b.* from booksale s inner join book b on s.bookId=b.id where 1=1 ";
        
        String name = b.getName();
        String author =b.getAuthor();
        String publisher = b.getPublisher();
        //根据书名检索
        if( name!=null && !name.equals("") ){
            sql = sql + " and b.name like '%" + name + "%'";
        }
        
        //根据作者检索
        if( author!=null && !author.equals("") ){
            sql = sql + " and b.author like '%" + author + "%'";
        }
        
        //根据出版社检索
        if( publisher!=null && !publisher.equals("") ){
            sql = sql + " and b.publisher like '%" + publisher + "%'";
        }

        //执行查询
        ps=conn.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();

        //将查询结果组装至Storage实体对象
        BookSale bookSaleNew = null;
        List<Book> list = new ArrayList<Book>();
        while( rs.next() ){
            bookSaleNew = new BookSale();
            bookSaleNew.setId( rs.getInt("s.id") );
            bookSaleNew.setBookId( rs.getInt("s.bookId") );
            bookSaleNew.setAmount( rs.getInt("s.amount") );
            bookSaleNew.setInDate( rs.getString("s.inDate") );

            //检索级联信息
            Book book = new Book();
            book.setId( rs.getInt("b.id") );
            book.setName( rs.getString("b.name") );
            book.setAuthor( rs.getString("b.author") );
            book.setPublisher( rs.getString("b.publisher") );
            book.setPrice( rs.getDouble("b.price") );
            list.add(book);
        }
        
        return list;
    }
    /**
     * 根据图书编号来检索上架图书
     * @throws SQLException 
     */
    public BookSale findByBookId(Integer bookId) throws SQLException {
    		String sql = "select * from booksale where bookId=?";
    		ps = conn.prepareStatement(sql);
    		ps.setInt(1,bookId);
    		ResultSet rs = ps.executeQuery();
    		BookDAO bookDAO = new BookDAO();
    		BookSale bookSale = null;
    		if(rs.next()){
    			bookSale = new BookSale();
    			bookSale.setId( rs.getInt("id") );
                bookSale.setBookId( rs.getInt("bookId") );
                bookSale.setAmount( rs.getInt("amount") );
                bookSale.setInDate( rs.getString("inDate") );
                
                //检索级联信息
                Book book = bookDAO.findById(bookId);
                bookSale.setBook(book);
            }
            
            return bookSale;
    		
    }
}