commentbean.java

jsp + servlete + jdbc开的管理系统

package hbu.david.cmc.work;

import java.util.ArrayList;
import java.util.List;

import hbu.david.cmc.bean.*;
import hbu.david.cmc.dao.*;
import hbu.david.cmc.util.*;

import java.sql.*;

public class CommentBean {

	public CommentBean() {
		// TODO Auto-generated constructor stub
	}
	
	/**
	 * 添加评论
	 */
	public static boolean addComment(Comment comment){
		boolean ok=true;
		Connection conn=null;
		PreparedStatement prepstmt=null;
		String sql="insert into comment(addUser,content,photoId,addTime) values(?,?,?,?)";
		try{
			conn=DatabaseBean.getConnection();
			conn.setAutoCommit(false);
			prepstmt =conn.prepareStatement(sql);
			prepstmt.setString(1, comment.getAddUser());
			prepstmt.setString(2, comment.getContent());
			prepstmt.setInt(3, comment.getPhotoId());
			prepstmt.setTimestamp(4, new Timestamp(System.currentTimeMillis()));
			ok=(1==prepstmt.executeUpdate());
			conn.commit();
			conn.setAutoCommit(true);			
		}catch(Exception e){
			try {
				conn.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			e.printStackTrace();
			ok=false;
		}finally{
			DatabaseBean.close(conn, prepstmt, null);
		}
		return ok;
	}

	/**
	 * 获取照片ID对应的start开始的num条评论
	 */
	public static List<Comment> getComments(int start,int num,int photoId,int userId){
		List<Comment> commentList=new ArrayList<Comment>();
		Connection conn=null;
		PreparedStatement prepstmt=null;
		ResultSet rs=null;
		String sql;
		try{
			conn=DatabaseBean.getConnection();
			conn.setAutoCommit(false);
			if(userId==0){
				//通过照片ID获取评论
				sql="select * from comment where photoid=? order by addtime desc limit ?,?";
				prepstmt=conn.prepareStatement(sql);
				prepstmt.setInt(1, photoId);
				prepstmt.setInt(2, start);
				prepstmt.setInt(3, num);
				rs=prepstmt.executeQuery();
				while(rs.next()){
					Comment comment=getCommentById(rs.getInt("id"));
					commentList.add(comment);
				}
			}else if(photoId==0){
				sql="select comment.id,comment.addUser,comment.content,comment.addTime,comment.photoId from comment,photo,category,userinfo where comment.photoId=photo.id and photo.categoryid=category.id and category.userid=userinfo.id and userinfo.id=? order by addTime desc limit ?,?";
				prepstmt=conn.prepareStatement(sql);
				prepstmt.setInt(1, userId);
				prepstmt.setInt(2, start);
				prepstmt.setInt(3, num);
				rs=prepstmt.executeQuery();
				while(rs.next()){
					Comment comment=getCommentById(rs.getInt(1));
					commentList.add(comment);
				}
			}
			conn.commit();
			conn.setAutoCommit(true);
		}catch(Exception e){
			try {
				conn.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			e.printStackTrace();
			commentList=null;
		}finally{
			DatabaseBean.close(conn, prepstmt, rs);
		}
		return commentList;
	}
	/**
	 * 通过id获取comment
	 */
	public static Comment getCommentById(int id){
		Comment comment=new Comment();
		Connection conn=null;
		PreparedStatement prepstmt=null;
		ResultSet rs=null;
		String sql="select * from comment where id=?";
		try{
			conn=DatabaseBean.getConnection();
			conn.setAutoCommit(false);
			prepstmt =conn.prepareStatement(sql);
			prepstmt.setInt(1, id);
			rs=prepstmt.executeQuery();
			while(rs.next()){
				comment.setId(rs.getInt("id"));
				comment.setAddUser(rs.getString("addUser"));
				comment.setContent(rs.getString("content"));
				comment.setAddTime(StringUtil.changeTimestamp(rs.getTimestamp("addTime")));
				comment.setPhotoId(rs.getInt("photoId"));
				comment.setPhoto(PhotoBean.getPhoto(rs.getInt("photoId")));
			}
			conn.commit();
			conn.setAutoCommit(true);			
		}catch(Exception e){
			try {
				conn.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			e.printStackTrace();
			comment =null;
		}finally{
			DatabaseBean.close(conn, prepstmt, rs);
		}
		return comment;
	}
	/**
	 * 获取photoId对应的评论数目
	 */
	public static int getCommentNumByPhotoId(int photoId){
		int num=0;
		Connection conn=null;
		PreparedStatement prepstmt=null;
		ResultSet rs=null;
		String sql="select count(*) from comment where photoId=?";
		try{
			conn=DatabaseBean.getConnection();
			conn.setAutoCommit(false);
			prepstmt=conn.prepareStatement(sql);
			prepstmt.setInt(1, photoId);
			rs=prepstmt.executeQuery();
			while(rs.next()){
				num=rs.getInt(1);
			}
			conn.commit();
			conn.setAutoCommit(true);
			
		}catch(Exception e){
			try {
				conn.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			num=0;
		}finally{
			DatabaseBean.close(conn, prepstmt, rs);
		}
		return num;
	}
	/**
	 * 根据userId获取评论书
	 */
	public static int getCommentNumByUserId(int userId){
		int num=0;
		Connection conn=null;
		PreparedStatement prepstmt=null;
		ResultSet rs=null;
		String sql="select count(*) from comment,photo,category,userinfo where comment.photoId=photo.id and photo.categoryid=category.id and category.userid=userinfo.id and userinfo.id=?";
		try{
			conn=DatabaseBean.getConnection();
			conn.setAutoCommit(false);
			prepstmt=conn.prepareStatement(sql);
			prepstmt.setInt(1, userId);
			rs=prepstmt.executeQuery();
			while(rs.next()){
				num=rs.getInt(1);
			}
			conn.commit();
			conn.setAutoCommit(true);
			
		}catch(Exception e){
			try {
				conn.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			num=0;
		}finally{
			DatabaseBean.close(conn, prepstmt, rs);
		}
		return num;
	}
	/**
	 * 根据Id删除
	 */
	public static boolean deleteCommentById(int id){
		boolean ok=true;
		Connection conn=null;
		PreparedStatement prepstmt=null;
		String sql="delete from comment where id=?";
		try{
			conn=DatabaseBean.getConnection();
			conn.setAutoCommit(false);
			prepstmt=conn.prepareStatement(sql);
			prepstmt.setInt(1, id);
			//rs=prepstmt.executeQuery();
			ok=(1==prepstmt.executeUpdate());
			conn.commit();
			conn.setAutoCommit(true);
			
		}catch(Exception e){
			try {
				conn.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			ok=false;
		}finally{
			DatabaseBean.close(conn, prepstmt, null);
		}
		return ok;
	}
	/**
	 * @param args
	 */
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		System.out.print(getCommentNumByUserId(2));
	}

}