package dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import entity.Comments;
import entity.Member;


/**
 * @author Administrator
 *评论的数据库处理类
 * 对应数据库COMMENTS表
 */
public class CommentsDao {
	DBconn conn = new DBconn();
	//在数据库中添加一条评论
	public boolean add(Comments com){
		return conn.update("insert into Comments(COMMODITYID,MEMBERID,COMMENTSTITLE,COMMENTSSATISFIED,COMMENTSIP,COMMENTSCONTENT,ORDERSID,COMMENTSTIME) values(?,?,?,?,?,?,?,sysdate)", 
				new String[]{
							String.valueOf(com.getCommodityId()),
				             String.valueOf(com.getMemberId()),
				             com.getCommentsTitle(),
				             String.valueOf(com.getCommentsSatisfied()),
				             com.getCommentsIP(),
				             com.getCommentsContent(),
				             String.valueOf(com.getOrdersId())})>0;
	} 
	//根据评论id来删除评论
	public boolean deleteById(int commentsId){
		return conn.update("delete from Comments where commentsId=?", 
				new String[]{String.valueOf(commentsId)})>0;
	}
	//根据评论对象来更新评论
	public boolean updateByComments(Comments com){
		return conn.update("update Comments set COMMODITYID=?,MEMBERID=?,COMMENTSTITLE=?,COMMENTSSATISFIED=?,COMMENTSTIME=?,COMMENTSIP=?,COMMENTSCONTENT=?,ORDERSID=? where commentsId=?", 
				new String[]{String.valueOf(com.getCommodityId()),
				             String.valueOf(com.getMemberId()),
				             com.getCommentsTitle(),
				             String.valueOf(com.getCommentsSatisfied()),
				             com.getCommentsTime(),
				             com.getCommentsIP(),
				             com.getCommentsContent(),
				             String.valueOf(com.getOrdersId()),
				             String.valueOf(com.getCommentsId())})>0;
	}
	
	
	
	/** 查找所有已被禁用的用户的数量
	 * 
	 */
	public int getCommentCount(){
		int count=0;
		ResultSet rs=conn.query("select count(*) as rcount from COMMENTS",null);
		try {
			while(rs.next()){
				count=rs.getInt("rcount");
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			conn.closeAll();
		}
		return count;
	}
	
	/**
	 * @param commentsId
	 * @return根据评论的id来在获取评论数据
	 */
	public Comments queryById(int commentsId){
		Comments com=null;
		ResultSet rs=conn.query("select * from Comments where commentsId=?", new String[]{String.valueOf(com.getCommentsId())});
		try {
			while(rs.next()){
				com = new Comments();
				com.setCommentsId(rs.getInt("commentsId"));                 //评论ID
				com.setOrdersId(rs.getInt("ordersId"));                     //订单编号
				com.setCommodityId(rs.getInt("commodityId"));				//商品编号
				com.setMemberId(rs.getInt("memberId"));						//会员id
				com.setCommentsTitle(rs.getString("commentsTitle"));		//标题
				com.setCommentsSatisfied(rs.getInt("commentsSatisfied"));	//满意度
				com.setCommentsTime(rs.getString("commentsTime"));			//评价时间
				com.setCommentsIP(rs.getString("commentsIP"));				//IP
				com.setCommentsContent(rs.getString("commentsContent"));	//内容
				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			conn.closeAll();		
		}
		return com;
	}
	
	/**
	 * @return
	 * 返回所有的评论数据
	 */
	public ArrayList<Comments> queryAll(){
		ArrayList<Comments> list=new ArrayList<Comments>();
		ResultSet rs=conn.query("select * from Comments", null);
		try {
			while(rs.next()){
				Comments com=new Comments(rs.getInt("commentsId"),
						                  rs.getInt("ordersId"),
						                  rs.getInt("commodityId"),
						                  rs.getInt("memberId"),
						                  rs.getString("commentsTitle"),
						                  rs.getInt("commentsSatisfied"),
						                  rs.getString("commentsTime"),
						                  rs.getString("commentsIP"),
						                  rs.getString("commentsContent"));
				list.add(com);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			conn.closeAll();
		}
		return list;
	}
	
	/**根据商品Id查询商品所对应的所有评论
	 * @param commodityId 评论的商品id
	 * @return对应商品的所有评论
	 */
	public ArrayList<Comments> queryByCommodityId(String commodityId){
		ArrayList<Comments> commentsList = new ArrayList<Comments>();
		Comments com=null;
		ResultSet rs=conn.query("select * from Comments where commodityId=?", new String[]{commodityId});
		try {
			while(rs.next()){
				com = new Comments();
				com.setCommentsId(rs.getInt("commentsId"));                 //评论ID
				com.setOrdersId(rs.getInt("ordersId"));                     //订单编号
				com.setCommodityId(rs.getInt("commodityId"));				//商品编号
				com.setMemberId(rs.getInt("memberId"));						//会员id
				com.setCommentsTitle(rs.getString("commentsTitle"));		//标题
				com.setCommentsSatisfied(rs.getInt("commentsSatisfied"));	//满意度
				com.setCommentsTime(rs.getString("commentsTime").substring(0,10));			//评价时间
				com.setCommentsIP(rs.getString("commentsIP"));				//IP
				com.setCommentsContent(rs.getString("commentsContent"));	//内容
				commentsList.add(com);
				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			conn.closeAll();		
		}
		return commentsList;
	}
	
	
	
	/**
	 * 得到所有评论的数量
	 * @return
	 */
	public int getCount(){
		int count=0;
		ResultSet rs=conn.query("select count(*) as rcount from Comments",null);
		try {
			while(rs.next()){
				count=rs.getInt("rcount");
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			conn.closeAll();
		}
		return count;
	}
	
	/**通过商品id分页查询评论
	 * @param commodityId
	 * @param page
	 * @param size
	 * @return
	 */
	public ArrayList<Comments> queryByCommodityIdPage(String commodityId,int page,int size){
		ArrayList<Comments> commentsList = new ArrayList<Comments>();
		Comments com=null;
		String sql="select b.* from (select a.*,rownum rn from(select * from comments where commodityId=?  order by commentsId desc)a  where rownum<="+page*size+" )b where b.rn>"+(page-1)*size;
		ResultSet rs=conn.query(sql, new String[]{commodityId});
		try {
			while(rs.next()){
				com = new Comments();
				com.setCommentsId(rs.getInt("commentsId"));                 //评论ID
				com.setOrdersId(rs.getInt("ordersId"));                     //订单编号
				com.setCommodityId(rs.getInt("commodityId"));				//商品编号
				com.setMemberId(rs.getInt("memberId"));						//会员id
				com.setCommentsTitle(rs.getString("commentsTitle"));		//标题
				com.setCommentsSatisfied(rs.getInt("commentsSatisfied"));	//满意度
				com.setCommentsTime(rs.getString("commentsTime").substring(0,10));			//评价时间
				com.setCommentsIP(rs.getString("commentsIP"));				//IP
				com.setCommentsContent(rs.getString("commentsContent"));	//内容
				commentsList.add(com);
				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			conn.closeAll();		
		}
		return commentsList;
	}
	
	/**通过商品id查询对应商品的评论数目
	 * @param commodityId
	 * @return
	 */
	public int getCountByCommodityId(String commodityId){
		int count=0;
		ResultSet rs=conn.query("select count(*) as rcount from Comments where commodityId=?",new String[]{commodityId});
		try {
			while(rs.next()){
				count=rs.getInt("rcount");
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			conn.closeAll();
		}
		return count;
	}
	public ArrayList<Comments> queryAllbymemberid(String memberemail){
		Comments com=null;
		ArrayList<Comments> list = new ArrayList<Comments>();
		ResultSet rs=conn.query("select * from Comments where memberId=(select memberid from member where memberemail=?)", new String[]{memberemail});
		try {
			while(rs.next()){
				com = new Comments();
				com.setCommentsId(rs.getInt("commentsId"));                 //评论ID
				com.setOrdersId(rs.getInt("ordersId"));                     //订单编号
				com.setCommodityId(rs.getInt("commodityId"));				//商品编号
				com.setMemberId(rs.getInt("memberId"));						//会员id
				com.setCommentsTitle(rs.getString("commentsTitle"));		//标题
				com.setCommentsSatisfied(rs.getInt("commentsSatisfied"));	//满意度
				com.setCommentsTime(rs.getString("commentsTime"));			//评价时间
				com.setCommentsIP(rs.getString("commentsIP"));				//IP
				com.setCommentsContent(rs.getString("commentsContent"));//内容
				list.add(com);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			conn.closeAll();		
		}
		return list;
	}
	

	public boolean isComment(String oid,String commodityId) {
		// TODO Auto-generated method stub
		ResultSet rs=conn.query("select * from comments where ordersid=? and commodityid=?",new String[]{oid,commodityId});
		try {
			return rs.next();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}finally{
			conn.closeAll();
		}
	}
	public ArrayList<Comments> queryTop10() {
		// TODO Auto-generated method stub
		
		Comments com=null;
		MemberDao memberDao = new MemberDao();
		CommodityDao commodityDao = new CommodityDao();
		ArrayList<Comments> list = new ArrayList<Comments>();
		ResultSet rs=conn.query("select * from (select a.*,rownum as id from Comments a order by COMMENTSID desc) where id between 0 and 10", null);
		try {
			while(rs.next()){
				
				com = new Comments();
				com.setCommentsId(rs.getInt("commentsId"));                 //评论ID
				com.setOrdersId(rs.getInt("ordersId"));                     //订单编号
				com.setCommodityId(rs.getInt("commodityId"));				//商品编号
				com.setMemberId(rs.getInt("memberId"));						//会员id
				com.setCommentsTitle(rs.getString("commentsTitle"));		//标题
				com.setCommentsSatisfied(rs.getInt("commentsSatisfied"));	//满意度
				com.setCommentsTime(rs.getString("commentsTime"));			//评价时间
				com.setCommentsIP(rs.getString("commentsIP"));				//IP
				com.setCommentsContent(rs.getString("commentsContent"));//内容
				Member member = new Member();
				 member = memberDao.queryByMemberId(rs.getString("memberId"));
				com.setMember(member);
				com.setCommodityPicture(commodityDao.queryByCommodityId(rs.getString("commodityId")).getCommodityPicture());
				list.add(com);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			conn.closeAll();		
		}
		return list;
	}
}
最近下载更多
wanglinddad  LV51 4月15日
lishangchen  LV3 1月27日
sovy030201  LV5 1月11日
765105637  LV9 1月11日
八七  LV8 1月3日
北有深秋  LV3 2021年12月21日
adminsan  LV2 2021年12月15日
chenchen479  LV6 2021年11月28日
xiaohe229  LV8 2021年8月9日
最近浏览更多
chj1234  LV4 9月19日
lsglsg9  LV20 9月11日
49202046  LV7 8月1日
liu2022  LV14 7月31日
豆崽啾啾 6月27日
暂无贡献等级
暂无贡献等级
dasdascccf  LV10 6月16日
邬松桥  LV8 6月14日
gaohuajian 6月10日
暂无贡献等级
顶部 客服 微信二维码 底部
>扫描二维码关注最代码为好友扫描二维码关注最代码为好友