userdao.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.BookReviewVo;
import com.xaccp.aj3q8073.vo.MyFavoriteVo;
import com.xaccp.aj3q8073.vo.OrderDetailVo;
import com.xaccp.aj3q8073.vo.OrderInfoVo;
import com.xaccp.aj3q8073.vo.OrderListVo;
import com.xaccp.aj3q8073.vo.PageBean;
import com.xaccp.aj3q8073.vo.UserInfoVo;

public class UserDao {
	DBManager db = new DBManager();

	/**
	 * 查找最新推荐
	 * 
	 * @return
	 */
	public List findNewBook() {
		String sql = "select * from bookinfo where bookshow=? order by bookId desc limit ?,?";
		Object[] obj = new Object[] { 1, 0, 6 };
		List list = db.queryForList(sql, obj, new BookNewMapper());
		return list;
	}

	/**
	 * 查找点击率最高的前6本书
	 * 
	 * @return
	 */
	public List findBookByClick() {
		String sql = "select * from bookinfo where bookshow=? order by clickCount desc limit ?,?";
		Object[] obj = new Object[] { 1, 0, 6 };
		List list = db.queryForList(sql, obj, new BookNewMapper());
		return list;
	}

	/**
	 * 从订单信息表中查处销量最多的四本书的Id
	 * 
	 * @return
	 */
	public List findBookBySale() {
		String sql = "select bookId from (select bookId,sum(bookCount) as counter from orderlist group by bookId) t order by counter desc limit ?,?";
		Object[] obj = new Object[] { 0, 4 };
		List list = db.queryForList(sql, obj, new orderListMapper());
		return list;
	}

	/**
	 * 根据bookId从读书表中查询出图书信息
	 * 
	 * @param arg
	 * @return
	 */
	public List findBookByarg(int[] arg) {
		String sql = "select * from bookinfo where bookId in(?,?)";
		Object[] obj = new Object[] { arg[0], arg[1] };
		List list = db.queryForList(sql, obj, new BookNewMapper());
		return list;
	}

	/**
	 * 查找行家推荐的前两本书
	 * 
	 * @return
	 */
	public List findBookBycom() {
		String sql = "select * from bookInfo where bookid in(select bookId from booktype) limit ?,?";
		Object[] obj = new Object[] { 0, 2 };
		List list = db.queryForList(sql, obj, new BookNewMapper());
		return list;
	}

	/**
	 * 会员登陆验证
	 * 
	 * @param vo
	 * @return
	 */
	public Object findMemberInfo(UserInfoVo vo) {
		String sql = "select * from userInfo where userName=? and userPwd=?";
		Object[] obj = new Object[] { vo.getUserName(), vo.getUserPwd() };
		Object jt = db.queryForObject(sql, obj, new userMapper());
		return jt;
	}

	/**
	 * 根据作者和图书名称名称查询图书
	 * 
	 * @param id
	 * @param pageSize
	 * @param author
	 * @return
	 */
	public PageBean findBookInfoByAuthorAnd(int id, int pageSize,
			String author, String bookName) {
		String sql = "select * from bookInfo where bookAuthor like ? and bookName like ? limit ?,?";
		Object[] obj = new Object[] { "%" + author + "%", "%" + bookName + "%",
				(id - 1) * pageSize, pageSize };
		List list = db.queryForList(sql, obj, new BookNewMapper());

		PageBean pb = new PageBean();
		pb.setIndex(id);
		pb.setPageSize(pageSize);
		pb.setList(list);
		pb.setTotal(getTotalByAuthorAnd(author,bookName));

		return pb;
	}

	/**
	 * 根据作者名称和图书名称查询图书总数
	 * @param name
	 * @return
	 */
	public int getTotalByAuthorAnd(String author,String name) {
		String sql = "select count(*) from bookInfo where bookAuthor like ? and bookName like ?";
		Object[] obj = new Object[] { "%" + author + "%", "%" + name + "%" };
		Integer i=  (Integer) db.queryForObject(sql, obj, new RowMapper() {
			public Object rowMapper(ResultSet rt) throws SQLException {
				return rt.getInt(1);
			}
		});
		return i;
	}
	
	/**
	 * 根据shelfId对图书进行分页
	 * @param shelfId
	 * @param index
	 * @param pageSize
	 * @return
	 */
	public PageBean findBookById(int shelfId,int index,int pageSize){
		String sql="select * from bookInfo where shelfId=? limit ?,?";
		Object[] obj=new Object[]{shelfId,(index-1)*pageSize,pageSize};
		List list= db.queryForList(sql, obj, new BookNewMapper());
		
		PageBean pb = new PageBean();
		pb.setIndex(index);
		pb.setPageSize(pageSize);
		pb.setList(list);
		pb.setTotal(getTotalByShelfId(shelfId));

		return pb;
		
	}

	/**
	 * 根据shelfId查询图书总数
	 * @param name
	 * @return
	 */
	public int getTotalByShelfId(int shelfId) {
		String sql = "select count(*) from bookInfo where shelfId=?";
		Object[] obj = new Object[] {shelfId};
		Integer i=  (Integer) db.queryForObject(sql, obj, new RowMapper() {
			public Object rowMapper(ResultSet rt) throws SQLException {
				return rt.getInt(1);
			}
		});
		return i;
	}
	
	/**
	 * 界面上根据id查询图书信息
	 * @param bookId
	 * @return
	 */
	public Object findBookByBookId(int bookId){
		String sql="select * from bookInfo where bookId=?";
		Object[] obj=new Object[]{bookId};
		Object jt= db.queryForObject(sql, obj, new BookNewMapper());
		return jt;
	}
	
	/**
	 * 界面上根据id查询图书评论信息
	 * @param bookId
	 * @return
	 */
	public List findReviewByBookId(int bookId){
		String sql="select b.bookAuthor,r.reviewText,r.reviewContent,r.reviewTime from bookInfo as" +
				" b inner join bookreview as r on b.bookId=r.bookId where r.bookId=?";
		Object[] obj=new Object[]{bookId};
		List list=db.queryForList(sql, obj, new ReviewMapper());
		return list;
	}
	/**
	 * 添加会员
	 * @param vo
	 * @return
	 */
	public int saveUser(UserInfoVo vo){
		String sql="insert into userInfo values(default,?,?,?,?,?,?,?,?,?,sysdate(),?)";
		Object[] obj=new Object[]{vo.getUserName(),vo.getUserPost(),vo.getUserQuestion(),vo.getUserAnswer(),vo.getUserRname(),
				vo.getUserAge(),vo.getUserSex(),vo.getUserTel(),vo.getUserAddress(),vo.getUserPost()};
		int i= db.getIdentityByInsert(sql, obj);
		return i;
	}
	
	/**
	 * 收藏图书
	 * @param vo
	 * @return
	 */
	public int saveFavorite(MyFavoriteVo vo){
		String sql="insert into myfavorite values(default,?,?,sysdate())";
		Object[] obj=new Object[]{vo.getBookId(),vo.getUserId(),};
		int i= db.executeUpdate(sql, obj);
		return i;
	}
	
	/**
	 * 根据id查找用户收藏的图书
	 * @param userId
	 * @return
	 */
	public List findFavByUserId(int userId){
		String sql="select * from bookInfo where bookId in(select bookId from myfavorite where userId=?)";
		Object[] obj=new Object[]{userId};
		List list= db.queryForList(sql, obj, new BookNewMapper());
		return list;
	}
	
	/**
	 * 根据id删除收藏夹中的图书
	 * @param bookId
	 * @return
	 */
	public int delFavByBookId(int bookId){
		String sql="delete from myfavorite where bookId=?";
		Object[] obj=new Object[]{bookId};
		int i= db.executeUpdate(sql, obj);
		return i;
	}
	/**
	 * 添加评论信息
	 * @param vo
	 * @return
	 */
	public int saveReview(BookReviewVo vo){
		String sql="insert into bookreview values(default,?,?,?,?,sysdate())";
		Object[] obj=new Object[]{vo.getBookId(),vo.getUserId(),vo.getReviewText(),vo.getReviewContent()};
		int i= db.executeUpdate(sql, obj);
		return i;
	}
	/**
	 * 添加订单人的信息并且返回但前的自动增长值
	 * @param vo
	 * @return
	 */
	public int saveOrderInfo(OrderInfoVo vo){
		String sql="insert into orderInfo values(default,?,?,?,?,?,?,?,?,?,?,sysdate())";
		Object[] obj=new Object[]{vo.getUserId(),vo.getCustmorName(),vo.getCustmorTel(),vo.getCustmorSex(),vo.getCustmorPostman(),
				vo.getCustmorAddress(),vo.getCustmorEmail(),vo.getSendStyle(),vo.getPayStyle(),1};
		int i= db.getIdentityByInsert(sql, obj);
		return i;
	}
	/**
	 * 添加订单的内容
	 * @param vo
	 * @return
	 */
	public int saveOrderList(OrderListVo vo)
	{
		String sql="insert into orderList values(default,?,?,?,?)";
		Object[] obj=new Object[]{vo.getOrderId(),vo.getBookId(),vo.getBookCount(),vo.getFinalPrice()};
		int i= db.executeUpdate(sql, obj);
		return i;
	}
	
	/**
	 * 根据userId查找所有的订单信息
	 * @param userId
	 * @return
	 */
	public PageBean findOrderByMemberId(int userId,int index,int pageSize){
		String sql="select o.orderId,o.orderTime,o.custmorName,d.finalPrice,o.orderState from orderInfo " +
				"as o inner join orderList as d on o.orderId=d.orderId where o.userId=? limit ?,?";
		Object[] obj=new Object[]{userId,(index-1)*pageSize,pageSize};
		List list= db.queryForList(sql, obj, new orderMapper() );
		
		PageBean pk=new PageBean();
		pk.setIndex(index);
		pk.setPageSize(pageSize);
		pk.setList(list);
		pk.setTotal(getTotal(userId));
		
		return pk;
	}
	/**
	 * 根据userId查询总的订单数
	 * @param userId
	 * @return
	 */
	public int getTotal(int userId){
		String sql="select count(*) from orderInfo where userid=? ";
		Object[] obj=new Object[]{userId};
		Integer i=  (Integer) db.queryForObject(sql, obj, new RowMapper() {
			public Object rowMapper(ResultSet rt) throws SQLException {
				return rt.getInt(1);
			}
		});
		return i;
	}
	/**
	 * 删除收藏表中的图书
	 * @param bookId
	 * @return
	 */
	public int removeFayByBookId(int bookId){
		String sql="delete from myfavorite where bookId=?";
		Object[] obj=new Object[]{bookId};
		int i= db.executeUpdate(sql, obj);
		return i;
	}
	/**
	 * 修改用户信息
	 * @param pass
	 * @param userId
	 * @return
	 */
	public int editPwd(String pass,int userId){
		String sql="update userInfo set userPwd=? where userId=?";
		Object[] obj=new Object[]{pass,userId};
		int i= db.executeUpdate(sql, obj);
		return i;
	}
	
	class orderMapper implements RowMapper{

		public Object rowMapper(ResultSet rt) throws SQLException {
			OrderInfoVo vo=new OrderInfoVo();
			vo.setOrderId(rt.getInt("orderId"));
			vo.setOrderTime(rt.getString("orderTime"));
			vo.setCustmorName(rt.getString("custmorName"));
			vo.setFinalPrice(rt.getDouble("finalPrice"));
			vo.setOrderState(rt.getInt("orderState"));
			return vo;
		}
		
	}
	
	class ReviewMapper implements RowMapper{
		
		public Object rowMapper(ResultSet rt) throws SQLException {
			BookReviewVo vo=new BookReviewVo();
			vo.setReviewText(rt.getString("reviewText"));
			vo.setReviewContent(rt.getString("reviewContent"));
			vo.setReviewTime(rt.getString("reviewTime"));
			vo.setBookAuthor(rt.getString("bookAuthor"));
			return vo;
		}
		
	}
	
	
	class userMapper implements RowMapper {

		public Object rowMapper(ResultSet rt) throws SQLException {
			UserInfoVo vo = new UserInfoVo();
			vo.setUserId(rt.getInt("userId"));
			vo.setUserName(rt.getString("userName"));
			vo.setUserPwd(rt.getString("userPwd"));
			vo.setUserQuestion(rt.getString("userQuestion"));
			vo.setUserAnswer(rt.getString("userAnswer"));
			vo.setUserRname(rt.getString("userRname"));
			vo.setUserAge(rt.getInt("userAge"));
			vo.setUserSex(rt.getInt("userSex"));
			vo.setUserTel(rt.getString("userTel"));
			vo.setUserAddress(rt.getString("userAddress"));
			vo.setUserTime(rt.getDate("userTime"));
			vo.setUserPost(rt.getString("userPost"));
			return vo;
		}

	}

	class orderListMapper implements RowMapper {

		public Object rowMapper(ResultSet rt) throws SQLException {
			OrderListVo vo = new OrderListVo();
			vo.setBookId(rt.getInt("bookId"));
			return vo;
		}

	}

	class BookNewMapper 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.setBookImgPath(rt.getString("bookImgPath"));
			vo.setShelfId(rt.getInt("shelfId"));
			vo.setClickCount(rt.getInt("clickCount"));
			vo.setBookShow(rt.getInt("bookShow"));
			return vo;
		}

	}

}