albumvideodao.java

模拟的土豆网视频网站

package cn.myvideosite.data.model.dao;

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

import cn.myvideosite.data.model.bean.AlbumVideo;

public class AlbumVideoDAO extends BaseDAO{
	/**
	 * 专辑视频关联表 保存方法
	 * @param album_video
	 * @return
	 */
      public static AlbumVideo save(AlbumVideo album_video){
    	  PreparedStatement pstmt=null;
    	  String sql="insert into album_video (albumId,videoId) values (?,?)";
    	  try {
			pstmt=connection.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
			
			pstmt.setInt(1, album_video.getAlbumId());
			pstmt.setInt(2, album_video.getVideoId());
			
			if(pstmt.executeUpdate()==1){
				ResultSet rs=pstmt.getGeneratedKeys();
				if(rs!=null){
					rs.next();
					album_video.setId(rs.getInt(1));
				}
			}
		} catch (SQLException e) {
		
			e.printStackTrace();
		}finally{
			if(pstmt!=null){
				try {
					pstmt.close();
					pstmt=null;
				} catch (SQLException e) {
					
					e.printStackTrace();
				}
			
			}
		}   	  
		return album_video;    	  
      }
      /**
       * 专辑视频关联表 删除方法
       * @param id
       */
      public static void delete(int id){
    	  PreparedStatement pstmt=null;
    	  String sql="delete from album_video where id=?";
    	  try {
			pstmt=connection.prepareStatement(sql);
			pstmt.setInt(1, id);
			pstmt.executeUpdate();			
			
		} catch (SQLException e) {
			
			e.printStackTrace();
		}finally{
			try {
				if(pstmt!=null){
					pstmt.close();
					pstmt=null;
				}			
			} catch (SQLException e) {				
				e.printStackTrace();
			}
		}    	  
    	  
      }
      /**
       * 专辑视频关联表 更新方法
       * @param album_video
       */
      public static void update(AlbumVideo album_video){
    	  PreparedStatement pstmt=null;
    	  String sql="update album_video set albumId=?,videoId=? where id=?";
    	  try {
			pstmt=connection.prepareStatement(sql);
			
			pstmt.setInt(1, album_video.getAlbumId());
			pstmt.setInt(2, album_video.getVideoId());
			pstmt.setInt(3, album_video.getId());
			
			pstmt.executeUpdate();
			
		} catch (SQLException e) {
			
			e.printStackTrace();
		}finally{
			try {
				if(pstmt!=null){
				pstmt.close();
				pstmt=null;
				}
			} catch (SQLException e) {
				
				e.printStackTrace();
			}
		}		
      }
      /**
       * 专辑视频关联表 查找方法 单条记录
       * @param id
       * @return
       */
      public static AlbumVideo findById(int id){
    	  PreparedStatement pstmt=null;
    	  ResultSet rs=null;
    	  AlbumVideo album_video=null;
    	  String sql="select * from album_video where id=?";
    	  try {
			pstmt=connection.prepareStatement(sql);
			pstmt.setInt(1, id);
			rs=pstmt.executeQuery();
			album_video=new AlbumVideo();
			while(rs.next()){
				
				album_video.setId(rs.getInt(1));
				album_video.setAlbumId(rs.getInt(2));
				album_video.setVideoId(rs.getInt(3));
				
			}
			
		} catch (SQLException e) {
			
			e.printStackTrace();
		}finally{
			try {
				if(rs!=null){
				rs.close();
				rs=null;
				}
				if(pstmt!=null){
				pstmt.close();
				pstmt=null;
				}
			} catch (SQLException e) {
				
				e.printStackTrace();
			}
		}
		return album_video;
    	  
      }
      /**
       * 专辑视频关联表 查询全部 无参数方法
       * @return
       */
      public static List<AlbumVideo> findAll(){
    	  PreparedStatement pstmt=null;
    	  ResultSet rs=null;
    	  List<AlbumVideo> rtnList=null;
    	  String sql="select * from album_video";
    	  try {
			pstmt=connection.prepareStatement(sql);
		
			rs=pstmt.executeQuery();
			rtnList=new ArrayList<AlbumVideo>();
			while(rs.next()){
				AlbumVideo album_video=new AlbumVideo();
				 
				album_video.setId(rs.getInt(1));
				album_video.setAlbumId(rs.getInt(2));
				album_video.setVideoId(rs.getInt(3));
				rtnList.add(album_video);
			}
			
		} catch (SQLException e) {
			
			e.printStackTrace();
		}finally{
			try {
				if(rs!=null){
				rs.close();
				rs=null;
				}
				if(pstmt!=null){
				pstmt.close();
				pstmt=null;
				}
			} catch (SQLException e) {
				
				e.printStackTrace();
			}
		}  
		return rtnList;
    	  
      }
      /**
       * 专辑视频关联表 查询全部 有参数方法
       * @param first
       * @param max
       * @return
       */
      public static List<AlbumVideo> findAll(int first,int max){
    	  PreparedStatement pstmt=null;
    	  ResultSet rs=null;
    	  List<AlbumVideo> rtnList=null;
    	  String sql="select * from album_video limit ?,?";
    	  try {
			pstmt=connection.prepareStatement(sql);
		    pstmt.setInt(1, first);
		    pstmt.setInt(2,max);
			rs=pstmt.executeQuery();
			rtnList=new ArrayList<AlbumVideo>();
			while(rs.next()){
				AlbumVideo album_video=new AlbumVideo();
				 
				album_video.setId(rs.getInt(1));
				album_video.setAlbumId(rs.getInt(2));
				album_video.setVideoId(rs.getInt(3));
				
				rtnList.add(album_video);
			}
			
		} catch (SQLException e) {
			
			e.printStackTrace();
		}finally{
			try {
				if(rs!=null){
				rs.close();
				rs=null;
				}
				if(pstmt!=null){
				pstmt.close();
				pstmt=null;
				}
			} catch (SQLException e) {
				
				e.printStackTrace();
			}
		}  
		return rtnList;
      }
      /**
       *   判断视频Id 和 专辑Id是否唯一性  albumId,videoId
       */
      public static AlbumVideo findByAlbumVideoId(int albumId,int videoId){
    	    PreparedStatement pstmt=null;
    	    ResultSet rs=null;
    	    AlbumVideo albumvideo=null;
    	    String sql="select * from album_video where albumId=? and videoId=?";
    	    try {
				pstmt=connection.prepareStatement(sql);
				pstmt.setInt(1, albumId);
				pstmt.setInt(2, videoId);
				rs=pstmt.executeQuery();
				while(rs.next()){
					 albumvideo=new AlbumVideo();
					 
					 albumvideo.setId(rs.getInt(1));
					 albumvideo.setAlbumId(rs.getInt(2));
					 albumvideo.setVideoId(rs.getInt(3));
				}								
			} catch (SQLException e) {				
				e.printStackTrace();
			} finally{
				try {
					if(rs!=null){
					rs.close();
					rs=null;
					}
					if(pstmt!=null){
					pstmt.close();
					pstmt=null;
					}
				} catch (SQLException e) {
					
					e.printStackTrace();
				}
			}  	    
		return albumvideo;   	  
      }                              
}