photobean.java

jsp + servlete + jdbc开的管理系统

/**
 * 
 */
package hbu.david.cmc.work;

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

import java.util.*;
import java.sql.*;
/**
 * @author Administrator
 *
 */
public class PhotoBean {
	/**
	 * 添加照片
	 */
	public static boolean addPhoto(Photo photo){
		boolean ok=true;
		Connection conn=null;
		PreparedStatement prepstmt=null;
		String sql="insert into photo(name,url,addTime,categoryId) values(?,?,?,?)";
		try{
			conn=DatabaseBean.getConnection();
			conn.setAutoCommit(false);
			prepstmt=conn.prepareStatement(sql);
			
			prepstmt.setString(1, photo.getName());
			prepstmt.setString(2, photo.getUrl());
			prepstmt.setTimestamp(3, new Timestamp(System.currentTimeMillis()));
			prepstmt.setInt(4, photo.getCategoryId());
			
			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;
	}
	/**
	 * 判断照片归属
	 * id和userid是否对应
	 */
	public static boolean isRightUser(int id,int userId){
		boolean ok=true;
		Connection conn=null;
		PreparedStatement prepstmt=null;
		ResultSet rs=null;
		String sql="select count(photo.id) from photo,category,userinfo where photo.id=? 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, id);
			prepstmt.setInt(2, userId);
			rs=prepstmt.executeQuery();
			while(rs.next()){
				if(rs.getInt(1)==0){
					//没有结果,不匹配
					ok=false;
				}
			}
			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, rs);
		}
		return false;
	}
	/*********************************************
	 * 获取照片列表
	 */
	public static List<Photo> getPhotos(int start,int num,int categoryId,int userId){
		List<Photo> list=new ArrayList<Photo>();
		Connection conn=null;
		PreparedStatement prepstmt=null;
		ResultSet rs=null;
		String sql="";
		try{
			conn=DatabaseBean.getConnection();
			conn.setAutoCommit(false);
			if(userId==0){
				sql="select * from photo where categoryid=? order by addTime desc limit ?,? ";
				prepstmt=conn.prepareStatement(sql);	
				prepstmt.setInt(1, categoryId);
				prepstmt.setInt(2, start);
				prepstmt.setInt(3, num);
				rs=prepstmt.executeQuery();
				Photo photo;
				while(rs.next()){
					photo=new Photo();
					photo.setId(rs.getInt("id"));
					photo.setName(rs.getString("name"));
					photo.setUrl(rs.getString("url"));
					photo.setAddTime(StringUtil.changeTimestamp(rs.getTimestamp("addTime")));
					photo.setCategoryId(rs.getInt("categoryId"));
					photo.setSkimNum(rs.getInt("skimNum"));
					photo.setCategory(CategoryBean.getCategoryById(categoryId));
					list.add(photo);
				}
			}else{
				sql="select photo.id,photo.name,photo.url,photo.addTime,photo.categoryId,photo.skimNum from photo,category,userinfo where 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();
				Photo photo;
				while(rs.next()){
					photo=new Photo();
					photo.setId(rs.getInt(1));
					photo.setName(rs.getString(2));
					photo.setUrl(rs.getString(3));
					photo.setAddTime(StringUtil.changeTimestamp(rs.getTimestamp(4)));
					photo.setCategoryId(rs.getInt(5));
					photo.setSkimNum(rs.getInt(6));
					photo.setCategory(CategoryBean.getCategoryById(rs.getInt(5)));
					/*
					photo.setId(rs.getInt("photo.id"));
					photo.setName(rs.getString("photo.name"));
					photo.setUrl(rs.getString("photo.url"));
					photo.setAddTime(StringUtil.changeTimestamp(rs.getTimestamp("photo.addTime")));
					photo.setCategoryId(rs.getInt("photo.categoryId"));
					photo.setSkimNum(rs.getInt("photo.skimNum"));
					photo.setCategory(CategoryBean.getCategoryById(rs.getInt("photo.categoryId")));
					*/
					list.add(photo);
				}
			}			
			conn.commit();
			conn.setAutoCommit(true);
		}catch(Exception e){
			try {
				conn.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			list=null;
		}finally{
			DatabaseBean.close(conn, prepstmt, rs);
		}
		return list;
	}
	
	public static List<Photo> getPhotos(int start,int num){
		List<Photo> list=new ArrayList<Photo>();
		Connection conn=null;
		PreparedStatement prepstmt=null;
		ResultSet rs=null;
		String sql="select * from photo order by addTime desc limit ?,? ";
		try{
			conn=DatabaseBean.getConnection();
			conn.setAutoCommit(false);
			prepstmt=conn.prepareStatement(sql);	
			prepstmt.setInt(1, start);
			prepstmt.setInt(2, num);
			rs=prepstmt.executeQuery();
			Photo photo;
			while(rs.next()){
				photo=new Photo();
				photo.setId(rs.getInt("id"));
				photo.setName(rs.getString("name"));
				photo.setUrl(rs.getString("url"));
				photo.setAddTime(StringUtil.changeTimestamp(rs.getTimestamp("addTime")));
				photo.setCategoryId(rs.getInt("categoryId"));
				photo.setSkimNum(rs.getInt("skimNum"));
				photo.setCategory(CategoryBean.getCategoryById(rs.getInt("categoryId")));
				list.add(photo);
			}
			conn.commit();
			conn.setAutoCommit(true);
		}catch(Exception e){
			try {
				conn.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			list=null;
		}finally{
			DatabaseBean.close(conn, prepstmt, rs);
		}
		return list;
	}
	
	/**
	 *  获取照片数量,根据categoryID和userID获取照片数量
	 */
	public static int getPhotoNum(int categoryId,int userId){
		int num=0;
		Connection conn=null;
		PreparedStatement prepstmt=null;
		ResultSet rs=null;
		String sql;
		/**
		if(categoryId==0){
			sql="select count(*) from photo";
		}else{
			sql="select count(*) from photo where category=?";
		}
		*/
		try{
			conn=DatabaseBean.getConnection();
			conn.setAutoCommit(false);
			
			if(categoryId==0){
				sql="select count(photo.id) from photo,category,userinfo where photo.categoryid=category.id and category.userid=userinfo.id and userinfo.id=?";
				prepstmt =conn.prepareStatement(sql);
				prepstmt.setInt(1, userId);
			}else{
				if(userId==0){
					//sql="select count(photo.id) from photo,category,userinfo where photo.categoryid=category.id and category.userid=userinfo.id and and category.id=?";
					sql="select count(id) from photo where categoryId=?";
					prepstmt=conn.prepareStatement(sql);
					prepstmt.setInt(1, categoryId);
				}else{
					sql="select count(photo.id) from photo,category,userinfo where photo.categoryid=category.id and category.userid=userinfo.id and userinfo.id=? and category.id=?";
					prepstmt =conn.prepareStatement(sql);
					prepstmt.setInt(1, userId);
					prepstmt.setInt(2, categoryId);
				}
			}
			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;
	}
	
	/**
	 * 删除照片
	 */
	public static boolean deletePhotoById(int photoId){
		boolean ok=true;
		Connection conn=null;
		PreparedStatement prepstmt=null;
		String sql="delete from photo where id=?";
		try{
			conn=DatabaseBean.getConnection();
			conn.setAutoCommit(false);
			prepstmt =conn.prepareStatement(sql);
			prepstmt.setInt(1, photoId);
			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获取Photo内容
	 * @param args
	 */
	public static Photo getPhoto(int id){
		Photo photo=new Photo();
		Connection conn=null;
		PreparedStatement prepstmt=null;
		ResultSet rs=null;
		String sql="select * from photo where id=?";
		try{
			conn=DatabaseBean.getConnection();
			conn.setAutoCommit(false);
			prepstmt =conn.prepareStatement(sql);
			prepstmt.setInt(1, id);
			rs=prepstmt.executeQuery();
			while(rs.next()){
				photo.setId(rs.getInt("id"));
				photo.setName(rs.getString("name"));
				photo.setUrl(rs.getString("url"));
				photo.setAddTime(StringUtil.changeTimestamp(rs.getTimestamp("addTime")));
				photo.setCategoryId(rs.getInt("categoryId"));
				photo.setSkimNum(rs.getInt("skimNum"));
				photo.setCategory(CategoryBean.getCategoryById(rs.getInt("categoryId")));
				//PhotoBean.updatePhotoSkim(photo.getId());
				//photo.setArrayComment(CommentBean.getComments(0, 5, rs.getInt("id"), 0));
			}
			conn.commit();
			conn.setAutoCommit(true);
		}catch(Exception e){
			try {
				conn.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			e.printStackTrace();
			photo=null;
		}finally{
			DatabaseBean.close(conn, prepstmt, rs);
		}
		return photo;
	}
	/**
	 * 修改照片浏览次数＋1
	 * @param args
	 */
	public static boolean updatePhotoSkim(int id){
		boolean ok=true;
		Connection conn=null;
		PreparedStatement prepstmt=null;
		String sql="update photo set skimNum=skimNum+1 where id=?";
		try{
			conn=DatabaseBean.getConnection();
			conn.setAutoCommit(false);
			prepstmt =conn.prepareStatement(sql);
			prepstmt.setInt(1, id);
			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;
	}
	
	/**
	 * 修改照片信息
	 * @param args
	 */
	public static boolean updatePhoto(Photo photo){
		boolean ok=true;
		Connection conn=null;
		PreparedStatement prepstmt=null;
		String sql="update photo set name=?,categoryId=? where id=?";
		try{
			conn=DatabaseBean.getConnection();
			conn.setAutoCommit(false);
			prepstmt=conn.prepareStatement(sql);
			prepstmt.setString(1, photo.getName());
			prepstmt.setInt(2, photo.getCategoryId());
			prepstmt.setInt(3, photo.getId());
			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;
	}
	public static void main(String[] args){
		//Photo photo=new Photo();
		//photo.setId(1);
		//photo.setCategoryId(24);
		//photo.setName("asd");
		System.out.print(getPhoto(39).getUrl());
	}
}