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

/**
 *@Author:李世海	
 *@Address:青岛软件园
 *@Date: Apr 3, 2009
 */
public class BookDAO extends BaseDAO{
	/**
	 * 创建图书
	 * 要创建的图书对象
	 * @throws SQLException 
	 */
	public void create (Book book) throws SQLException{
		String sql = "Insert Into book(name,author,publisher,price) Values(?,?,?,?)";
		//设置预备参数
		ps = conn.prepareStatement(sql);
		ps.setString(1,book.getName());
		ps.setString(2,book.getAuthor());
		ps.setString(3,book.getPublisher());
		ps.setDouble(4,book.getPrice());
		ps.executeUpdate();
	}
	
	/**
	 * 删除制定的图书
	 * @throws SQLException 
	 * 
	 */
	public void delete (Integer id) throws SQLException {
		String sql = "delete from book where id=?";
		
		//设置预备参数
		ps = conn.prepareStatement(sql);
		ps.setInt(1, id);
		ps.executeUpdate();;
	}
	
	/**
	 * 修改图书信息
	 * 要修改的图书对象
	 * @throws SQLException 
	 */
	public void update(Book book) throws SQLException {
		String sql = "Update book set name=?,author=?,publisher=?,price=? where id=?";
		
		//设置预备参数
		ps = conn.prepareStatement(sql);
		ps.setString(1, book.getName());
		ps.setString(2, book.getAuthor());
		ps.setString(3, book.getPublisher());
		ps.setDouble(4, book.getPrice());
		ps.setInt(5,book.getId());
	}
	
	/**
	 * 根据主键检索图书
	 * @throws SQLException 
	 */
	public Book findById(Integer id) throws SQLException {
		String sql = "select * from book where id=?";
		//设置预备参数
		ps = conn.prepareStatement(sql);
		ps.setInt(1, id);
		ResultSet rs = ps.executeQuery();
		Book book = null;
		if(rs.next()){
			book = new Book();
			book.setId(rs.getInt("id"));
			book.setName(rs.getString("name"));
			book.setAuthor(rs.getString("author"));
			book.setPublisher(rs.getString("publisher"));
			book.setPrice(rs.getDouble("price"));
		}
		return book;
	}
	
	/**
	 * 检索图书列表
	 * @throws SQLException 
	 */
	public List<Book> findAll() throws SQLException {
		String sql = "select * from book";
		
		ps = conn.prepareStatement(sql);
		ResultSet rs = ps.executeQuery();
		List<Book> list = new ArrayList<Book>();
		while(rs.next()){
			Book book = new Book();
			book.setId(rs.getInt("id"));
			book.setName(rs.getString("name"));
			book.setAuthor(rs.getString("author"));
			book.setPublisher(rs.getString("publisher"));
			book.setPrice(rs.getDouble("price"));
			list.add(book);
		}
		return list;
	}
	
	/**
	 * 动态检索图书
	 * @throws SQLException 
	 */
	public List<Book> list(Book book) throws SQLException{
		String sql ="select * from book where 1=1";
		//根据书名检索
		String name = book.getName();
		if(name != null && name.trim().length()>0){
			sql = sql +" and name like '%" + name + "%'";
		}
		//根据作者检索
		String author = book.getAuthor();
		if(author != null && author.trim().length()>0){
			sql = sql + " and author like '%" + author + "%'";
		}
		//根据出版社检索
		String publisher = book.getPublisher();
		if(publisher != null && publisher.trim().length()>0){
			sql = sql + " and publisher like '%" + publisher + "%'";
		}
		//预设值参数，并执行查询。
		ps = conn.prepareStatement(sql);
		ResultSet rs = ps.executeQuery();
		List<Book> list = new ArrayList<Book>();
		while(rs.next()){
			Book b = new Book();
			b.setId(rs.getInt("id"));
			b.setName(rs.getString("name"));
			b.setAuthor(rs.getString("author"));
			b.setPublisher(rs.getString("publisher"));
			b.setPrice(rs.getDouble("price"));
			list.add(b);
		}
		return list;
	}
	/**
	 * 根据图书名称查询图书信息
	 * @param book
	 * @return
	 * @throws SQLException 
	 */
	public Book findByName(String name) throws SQLException{
		String sql = "select * from book where name=?";
		ps = conn.prepareStatement(sql);
		ps.setString(1, name);
		ResultSet rs = ps.executeQuery();
		Book bookMessage = new Book();
		if(rs.next()){
			bookMessage.setId(rs.getInt("id"));
			bookMessage.setName(rs.getString("name"));
			bookMessage.setAuthor(rs.getString("author"));
			bookMessage.setPublisher(rs.getString("publisher"));
			bookMessage.setPrice(rs.getDouble("price"));
		}
		return bookMessage;
	}
	
	/**
	 *根据图书名字查找图书编号
	 * @throws SQLException 
	 */
	public Object getId(Book book) throws SQLException{
		String sql = "select id from book where name=?";
		ps = conn.prepareStatement(sql);
		ps.setString(1, book.getName());
		ResultSet rs =  ps.executeQuery();
		return null;
	}
	
	public int test() throws SQLException{
		String sql = "select count(*) as i from book";
		ps = conn.prepareStatement(sql);
		ResultSet rs = ps.executeQuery();
		int i = 0;
		if(rs.next()){
			i = rs.getInt("i");
		}
		return i;
	}
}