bookinfodao.java

java开发购物车+网上书城

package com.xaccp.aj3q8073.dao;

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

import com.xaccp.aj3q8073.db.DBManager;
import com.xaccp.aj3q8073.db.RowMapper;
import com.xaccp.aj3q8073.vo.BookInfoVo;
import com.xaccp.aj3q8073.vo.BookShelfVo;
import com.xaccp.aj3q8073.vo.BookTypeVo;
import com.xaccp.aj3q8073.vo.PageBean;

/**
 * 图书信息管理dao
 * 
 * @author Administrator
 * 
 */
public class BookInfoDao {
	DBManager db = new DBManager();

	/**
	 * 查找图书所有信息
	 * 
	 * @return
	 */
	public List findAllBook() {
		String sql = "select * from bookinfo";
//		Object[] obj=new Object[]{0,pagesize};
		List list = db.queryForList(sql, null, new BookMapper());
		return list;
	}
	

	/**
	 * 根据图书名查找图书信息
	 * 
	 * @param name
	 * @return
	 */
	public List findBookByName(String name) {
		String sql = "select * from bookinfo where bookName like ?";
		Object[] obj = new Object[] { "%" + name + "%" };
		List list = db.queryForList(sql, obj, new BookMapper());
		return list;
	}

	/**
	 * 查找一级目录下的所有图书信息
	 * 
	 * @param pid
	 * @return
	 */
	public List findBookByOneId(int pId) {
		String sql = "select * from bookinfo where shelfId in(select shelfId from bookshelf where parentShelfId=?)";
		Object[] obj = new Object[] { pId };
		List list = db.queryForList(sql, obj, new BookMapper());
		return list;
	}

	/**
	 * 查找二级目录下的所有信息
	 * 
	 * @param sId
	 * @return
	 */
	public List findBookByTwoId(int sId) {
		String sql = "select * from bookinfo where shelfId=?";
		Object[] obj = new Object[] { sId };
		List list = db.queryForList(sql, obj, new BookMapper());
		return list;
	}

	/**
	 * 根据图书名和二级目录id查找图书
	 * 
	 * @param sId
	 * @param bookName
	 * @return
	 */
	public List findBookByTwoIdAndName(int sId, String bookName) {
		String sql = "select * from bookinfo where bookName=? and shelfId=?";
		Object[] obj = new Object[] { bookName, sId };
		List list = db.queryForList(sql, obj, new BookMapper());
		return list;
	}

	/**
	 * 在添加图书时 查找所有的二级目录
	 * 
	 * @return
	 */
	public List findTwoContent() {
		String sql = "select * from bookshelf where parentShelfId!=?";
		Object[] obj = new Object[] { 0 };
		List list = db.queryForList(sql, obj, new BookshelfMapper());
		return list;
	}
	
	/**
	 * 查找所有未上架的图书
	 * @return
	 */
	public List findAllNotUp(){
		String sql="select * from bookinfo where bookShow=?";
		Object[] obj=new Object[]{0};
		List list= db.queryForList(sql, obj, new BookMapper());
		return list;
	}
	
	/**
	 * 查找所有已上架的图书
	 * @return
	 */
	public List findAllUp(){
		String sql="select * from bookinfo where bookShow=?";
		Object[] obj=new Object[]{1};
		List list= db.queryForList(sql, obj, new BookMapper());
		return list;
	}
	/**
	 * 根据shelfId查找未上架的图书
	 * @return
	 */
	public List findNotUpByShelfId(int shelfId){
		String sql="select * from bookinfo where bookShow=? and shelfId=?";
		Object[] obj=new Object[]{0,shelfId};
		List list= db.queryForList(sql, obj, new BookMapper());
		return list;
	}
	
	/**
	 * 图书上架
	 * @param shelfId
	 * @return
	 */
	public int editState(int shelfId,int bookId){
		String sql="update bookinfo set bookshow=? where shelfId=? and bookId=?";
		Object[] obj=new Object[]{1,shelfId,bookId};
		int i= db.executeUpdate(sql, obj);
		return i;
	}
	
	/**
	 * 添加图书信息
	 * 
	 * @param vo
	 * @return
	 */
	public int saveBook(BookInfoVo vo) {
		String sql = "insert into bookinfo values(default,?,?,?,?,?,?,?,?,?,?)";
		Object[] obj = new Object[] { vo.getBookName(), vo.getBookAuthor(),
				vo.getBookPrice(), vo.getBookDiscount(), vo.getBookPublish(),
				vo.getBookContent(), vo.getBookImgPath(), vo.getShelfId(), 0,
				vo.getBookShow() };
		int i = db.executeUpdate(sql, obj);
		return i;
	}

	/**
	 * 根据bookId查询图书信息
	 * 
	 * @param bookId
	 * @return
	 */
	public Object findBookInfoByBookId(int bookId) {
		String sql = "select * from bookinfo where bookId=?";
		Object[] obj = new Object[] { bookId };
		Object ob = db.queryForObject(sql, obj, new BookMapper());
		return ob;
	}

	/**
	 * 根据ID修改图书信息
	 * @param vo
	 * @return
	 */
	public int editBook(BookInfoVo vo) {
		String sql = "update bookInfo set bookName=?,bookAuthor=?,bookPublish=?,bookPrice=?,bookDiscount=?,bookContent=?,bookImgPath=?,shelfId=?,clickCount=?,bookShow=? where bookId=?";
		Object[] obj = new Object[] { vo.getBookName(), vo.getBookAuthor(),
				vo.getBookPublish(), vo.getBookPrice(), vo.getBookDiscount(),vo.getBookContent(),
				vo.getBookImgPath(), vo.getShelfId(), vo.getClickCount(),
				vo.getBookShow(), vo.getBookId() };
		int i= db.executeUpdate(sql, obj);
		return i;
	}
	
	/**
	 * 添加推荐图书
	 * @param vo
	 * @return
	 */
	public int saveBookType(BookInfoVo vo){
		String sql="insert into bookType values(default,?,?)";
		Object[] obj=new Object[]{vo.getBookName(),vo.getBookId()};
		int i= db.executeUpdate(sql, obj);
		return i;
	}
	/**
	 * 查找推荐表中有没有已经推荐过的书
	 * @param bookId
	 * @return
	 */
	public List findBookType(int bookId){
		String sql="select * from bookType where bookId=?";
		Object[] obj=new Object[]{bookId};
		List list= db.queryForList(sql, obj, new BookTypeMapper());
		return list;
	}
	/**
	 * 根据Id删除推荐表中重复的图书
	 * @param bookId
	 * @return
	 */
	public int removeBookType(int bookId){
		String sql="delete from bookType where bookId=?";
		Object[] obj=new Object[]{bookId};
		int i= db.executeUpdate(sql, obj);
		return i;
	}
	
	

	class BookMapper implements RowMapper {

		public Object rowMapper(ResultSet rt) throws SQLException {
			BookInfoVo vo = new BookInfoVo();
			vo.setBookId(rt.getInt("bookId"));
			vo.setBookName(rt.getString("bookName"));
			vo.setBookAuthor(rt.getString("bookAuthor"));
			vo.setBookPublish(rt.getString("bookPublish"));
			vo.setBookPrice(rt.getDouble("bookPrice"));
			vo.setBookDiscount(rt.getDouble("bookDiscount"));
//			if (rt.getString("bookContent").length() <= 10) {
//				vo.setBookContent(rt.getString("bookContent"));
//			} else {
//				vo.setBookContent(rt.getString("bookContent").substring(0, 10));
//			}
			vo.setBookContent(rt.getString("bookContent"));
			vo.setBookImgPath(rt.getString("bookImgPath"));
			vo.setShelfId(rt.getInt("shelfId"));
			vo.setClickCount(rt.getInt("clickCount"));
			vo.setBookShow(rt.getInt("bookShow"));
			return vo;
		}

	}

}
class BookTypeMapper implements RowMapper{

	public Object rowMapper(ResultSet rt) throws SQLException {
		BookTypeVo vo=new BookTypeVo();
		vo.setTypeId(rt.getInt("typeId"));
		vo.setBookId(rt.getInt("bookId"));
		vo.setTypeName(rt.getString("typeName"));
		return vo;
	}
	
}