photodao.java

来自「java带进度条上传尽量不要让站长把时间都花费在为您修正说明上」· Java 代码 · 共 370 行

package com.jmwl.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.xml.registry.DeleteException;

import com.jmwl.biz.PhotoService;
import com.jmwl.common.BlogException;
import com.jmwl.dto.PhotoDTO;
import com.jmwl.vo.PhotoVO;


public class PhotoDAO extends BasicDAO
{
	PreparedStatement ps;
	ResultSet rs;
	
	/**
	 * 显示人气最高的相册 
	 */
	public List getHighPhoto() throws BlogException
	{
		List list=new ArrayList();
		String sql="select * from photos order by visit_count desc limit 0,5";
		try {
			ps=this.getConn().prepareStatement(sql);
			rs=ps.executeQuery();
			while(rs.next())
			{
				PhotoVO pvo=new PhotoVO();
				pvo.setId(rs.getInt(1));
				pvo.setUserid(rs.getInt(2));
				pvo.setPhoto_state(rs.getInt(3));
				pvo.setPhoto_description(rs.getString(4));
				pvo.setCreate_time(rs.getString(5));
				pvo.setPhoto_name(rs.getString(6));
				pvo.setPhoto_password(rs.getString(7));
				pvo.setVisit_count(rs.getInt(8));
				pvo.setFirst_picture(rs.getString(9));
				pvo.setUsername(rs.getString(10));
				list.add(pvo);
			}
		} catch (SQLException e) {
			throw new BlogException("对不起!数据库出错!");
		}
		return list;
	}
	
	public List getHighPhoto2() throws BlogException
	{
		List list=new ArrayList();
		String sql="select * from photos order by visit_count desc limit 5,5";
		try {
			ps=this.getConn().prepareStatement(sql);
			rs=ps.executeQuery();
			while(rs.next())
			{
				PhotoVO pvo=new PhotoVO();
				pvo.setId(rs.getInt(1));
				pvo.setUserid(rs.getInt(2));
				pvo.setPhoto_state(rs.getInt(3));
				pvo.setPhoto_description(rs.getString(4));
				pvo.setCreate_time(rs.getString(5));
				pvo.setPhoto_name(rs.getString(6));
				pvo.setPhoto_password(rs.getString(7));
				pvo.setVisit_count(rs.getInt(8));
				pvo.setFirst_picture(rs.getString(9));
				pvo.setUsername(rs.getString(10));
				list.add(pvo);
			}
		} catch (SQLException e) {
			throw new BlogException("对不起!数据库出错!");
		}
		return list;
	}
	
	/**
	 * 显示个人所有相册 分页
	 * @throws BlogException 
	 */
	
	public List getOnePhotos(int pageNow,int rowCount,int userid) throws BlogException
	{
		List list=new ArrayList();
		String sql="select * from photos where userid=? limit ?,?";
		try {
			ps=this.getConn().prepareStatement(sql);
			ps.setInt(1, userid);
			ps.setInt(2, (pageNow-1)*rowCount);
			ps.setInt(3, rowCount);
			rs=ps.executeQuery();
			while(rs.next())
			{
				PhotoVO pvo=new PhotoVO();
				pvo.setId(rs.getInt("id"));
				pvo.setPhoto_description(rs.getString("photo_description"));
				pvo.setCreate_time(rs.getDate("create_time").toLocaleString());
				pvo.setPhoto_name(rs.getString("photo_name"));
				pvo.setPhoto_password(rs.getString("photo_password"));
				pvo.setPhoto_state(rs.getInt("photo_state"));
				pvo.setUserid(rs.getInt("userid"));
				pvo.setVisit_count(rs.getInt("visit_count"));
				pvo.setFirst_picture(rs.getString("first_picture"));
				list.add(pvo);
			}
		} catch (SQLException e) {
		    throw new BlogException("无法显示相册，数据库异常 ");
		}
		return list;
	}
	/**
	 * 显示个人所有相册，不分页
	 * @param pageNow
	 * @param rowCount
	 * @param userid
	 * @return
	 * @throws BlogException
	 */
	public List getOnePhotos(int userid) throws BlogException
	{
		List list=new ArrayList();
		String sql="select * from photos where userid=?";
		try {
			ps=this.getConn().prepareStatement(sql);
			ps.setInt(1, userid);
			rs=ps.executeQuery();
			while(rs.next())
			{
				PhotoVO pvo=new PhotoVO();
				pvo.setId(rs.getInt("id"));
				pvo.setPhoto_description(rs.getString("photo_description"));
				pvo.setCreate_time(rs.getDate("create_time").toLocaleString());
				pvo.setPhoto_name(rs.getString("photo_name"));
				pvo.setPhoto_password(rs.getString("photo_password"));
				pvo.setPhoto_state(rs.getInt("photo_state"));
				pvo.setUserid(rs.getInt("userid"));
				pvo.setVisit_count(rs.getInt("visit_count"));
				list.add(pvo);
			}
		} catch (SQLException e) {
		    throw new BlogException("无法显示相册，数据库异常 ");
		}
		return list;
	}
	
	/**
	 * 添加相册
	 * @throws BlogException 
	 */
	
	public boolean addPhoto(PhotoDTO pdto) throws BlogException
	{
		boolean flag=false;
		String sql="insert into photos(userid,photo_state,photo_description,create_time,photo_name,photo_password,visit_count,username) values(?,?,?,?,?,?,?,?)";
		try {
			ps=this.getConn().prepareStatement(sql);
			System.out.println(pdto.getPhoto_name());
			ps.setInt(1,pdto.getUserid());
			ps.setInt(2, pdto.getPhoto_state());
			ps.setString(3, pdto.getPhoto_description());
			ps.setString(4, pdto.getCreate_time());
			ps.setString(5, pdto.getPhoto_name());
			ps.setString(6, pdto.getPhoto_password());
			ps.setInt(7,pdto.getVisit_count());
			ps.setString(8, pdto.getUsername());
			if(ps.executeUpdate()==1)
				flag=true;
		} catch (SQLException e) {
			throw new BlogException("添加相册失败,数据库异常");
		}
		return flag;
	}
	
	/**
	 * 获得相册信息
	 */
//-----------
	
	/**
	 * 删除相册
	 * @throws BlogException 
	 */
	
	public boolean delPhoto(PhotoDTO pdto) throws BlogException
	{
		boolean flag=false;
		String sql = "delete from photos where id=?";
		try {
			ps=this.getConn().prepareStatement(sql);
			ps.setInt(1, pdto.getId());
			if(ps.executeUpdate()==1)
				flag=true;
		} catch (SQLException e) {
			throw new BlogException("删除失败，数据库出现异常");
		}
		return flag;
	}
	
	/**
	 *修改相册的名字或描述 
	 * @throws BlogException 
	 */
	
	public boolean editPhoto(PhotoDTO pdto) throws BlogException
	{
		boolean flag=false;
		String sql="update photos set photo_name=?,photo_description=?,photo_password=? where id=?";
		try {
			ps=this.getConn().prepareStatement(sql);
			ps.setString(1, pdto.getPhoto_name());
			ps.setString(2, pdto.getPhoto_description());
			ps.setString(3, pdto.getPhoto_password());
			ps.setInt(4, pdto.getId());
			if(ps.executeUpdate()==1)
				flag=true;
		} catch (SQLException e) {
			e.printStackTrace();
			throw new BlogException("修改失败，数据库出现异常");
		}
		return flag;
	}
	/**
	 * 删除某一用户的全部相册
	 * @param ct
	 * @param userID
	 * @return
	 * @throws BlogException 
	 */
	public boolean delOneUserPhotos(int userid) throws BlogException
	{
		boolean flag=false;
		String sql="delete from photos where userid=?";
		try {
			ps=this.getConn().prepareStatement(sql);
			ps.setInt(1, userid);
			if(ps.executeUpdate()>0)
				flag=true;
		} catch (SQLException e) {
			throw new BlogException("删除相册失败,数据库异常");
		}
		return flag;
	}
	
	/**
	 * 获取某用户的相册总数
	 * @param userid
	 * @return
	 * @throws BlogException
	 */
	public int getPhotosCount(int userid) throws BlogException
	{
		int allCount=0;
		String sql="select count(*) from photos where userid=?";
		try {
			ps=this.getConn().prepareStatement(sql);
			ps.setInt(1, userid);
			rs=ps.executeQuery();
			rs.next();
			allCount=rs.getInt(1);
		} catch (SQLException e) {
			throw new BlogException("显示相册失败，数据库异常");
		}
		return allCount;
	}
	/**
	 * 修改相册头照片
	 * @param id
	 * @param uri
	 * @throws BlogException
	 */
	public void changeFirstPicture(int id,String uri) throws BlogException
	{
		String sql="update photos set first_picture=? where id=?";
		try {
			ps=this.getConn().prepareStatement(sql);
			ps.setString(1, uri);
			ps.setInt(2, id);
			ps.executeUpdate();
		} catch (SQLException e) {
			throw new BlogException("修改相册首照片失败，数据库异常");
		}
	}
	
	/**
	 * 查询该相册被查看了多少次
	 * @param pid
	 * @return
	 * @throws BlogException
	 */
	public int photoCount(int pid) throws BlogException
	{
		int pCount=0;
		String sql="select visit_count from photos where id=?";
		try {
			ps = this.getConn().prepareStatement(sql);
			ps.setInt(1, pid);
			rs = ps.executeQuery();
			while(rs.next())
			{
				pCount = rs.getInt(1);
			}
		} catch (SQLException e) {
			throw new BlogException("查找相册被查看次数失败！");
		}
		return pCount;
	}
	
	/**
	 * 修改相册计数器
	 * @param pid
	 * @return
	 * @throws BlogException
	 */
	public boolean update_Phtot_Coutnt(int pid) throws BlogException
	{
		boolean b = true;
		String sql="update photos set visit_count=visit_count+1 where id=?";
		try {
			ps = this.getConn().prepareStatement(sql);
			ps.setInt(1, pid);
			ps.executeUpdate();
		} catch (SQLException e) {
			b = false;
			throw new BlogException("相册计数器修改失败，数据库异常！");
		}
		return b;
	}
	
	public String checkPassword(int pid) throws BlogException
	{
		String psw = "";
		String sql = "select photo_password from photos where id=?";
		try {
			ps = this.getConn().prepareStatement(sql);
			ps.setInt(1, pid);
			rs = ps.executeQuery();
			while(rs.next())
			{
				psw = rs.getString(1);
			}
		} catch (SQLException e) {
			throw new BlogException("查询密码失败，数据库异常！");
		}
		return psw;
	}
	
	public int getPhotoUserId(int photoid) throws BlogException
	{
		int uid = 0;
		String sql = "SELECT userid FROM photos WHERE id=?";
		try {
			ps = this.getConn().prepareStatement(sql);
			ps.setInt(1, photoid);
			rs = ps.executeQuery();
			while (rs.next()) {
				uid = rs.getInt(1);
			}
		} catch (SQLException e) {
			throw new BlogException("查询密码失败，数据库异常！");
		}
		return uid;
	}
	
}