albumkeydao.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.AlbumKey;

public class AlbumKeyDAO extends BaseDAO{
	/**
	 * 专辑关键词关联表保存方法
	 * @param album_key
	 * @return
	 */
      public static AlbumKey save(AlbumKey album_key){
    	  PreparedStatement pstmt=null;
    	  String sql="insert into album_key (albumId,keyId,userId) values (?,?,?)";
    	  try {
			pstmt=connection.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
			
			pstmt.setInt(1,album_key.getAlbumId());
			pstmt.setInt(2,album_key.getKeyId());
			pstmt.setInt(3,album_key.getUserId());
			
			if(pstmt.executeUpdate()==1){
				ResultSet rs=pstmt.getGeneratedKeys();
				if(rs!=null){
					rs.next();
					album_key.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_key;   	  
      }
      /**
       * 专辑关键词关联表删除方法
       * @param id
       */
      public static void delete(int id){
    	  PreparedStatement pstmt=null;
    	  String sql="delete from album_key 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_key
       */
      public static void update(AlbumKey album_key){
    	  PreparedStatement pstmt=null;
    	  String sql="update album_key set albumId=?,keyId=?,userId=? where id=? ";
    	  try {
			pstmt=connection.prepareStatement(sql);
			
			pstmt.setInt(1,album_key.getAlbumId());
			pstmt.setInt(2,album_key.getKeyId());
			pstmt.setInt(3,album_key.getUserId());
			pstmt.setInt(4,album_key.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 AlbumKey findById(int id){
    	  PreparedStatement pstmt=null;
    	  ResultSet rs=null;
    	  AlbumKey album_key=null;
    	  String sql="select * from album_key where id=?";
    	  try {
			pstmt=connection.prepareStatement(sql);
			pstmt.setInt(1,id);
			rs=pstmt.executeQuery();
			album_key=new AlbumKey();
			while(rs.next()){
				
				album_key.setId(rs.getInt(1));
				album_key.setAlbumId(rs.getInt(2));
				album_key.setKeyId(rs.getInt(3));
				album_key.setUserId(rs.getInt(4));								
			}
			
		} 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_key;   	  
      }
      /**
       * 专辑关键词关联表 查询全部 无参数方法
       * @return
       */
      public static List<AlbumKey> findAll(){
    	  PreparedStatement pstmt=null;
    	  ResultSet rs=null;
    	  List<AlbumKey> rtnList=null;
    	  String sql="select * from album_key";
    	  try {
			pstmt=connection.prepareStatement(sql);
			rs=pstmt.executeQuery();
			rtnList=new ArrayList<AlbumKey>();
			while(rs.next()){
				AlbumKey album_key=new AlbumKey();
				
				album_key.setId(rs.getInt(1));
				album_key.setAlbumId(rs.getInt(2));
				album_key.setKeyId(rs.getInt(3));
				album_key.setUserId(rs.getInt(4));		
				
				rtnList.add(album_key);
			}
			
		} 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<AlbumKey> findAll(int first,int max){
    	  
    	  PreparedStatement pstmt=null;
    	  ResultSet rs=null;
    	  List<AlbumKey> rtnList=null;
    	  String sql="select * from album_key limit ?,?";
    	  try {
			pstmt=connection.prepareStatement(sql);
			pstmt.setInt(1, first);
			pstmt.setInt(2, max);
			rs=pstmt.executeQuery();
			rtnList=new ArrayList<AlbumKey>();
			while(rs.next()){
				AlbumKey album_key=new AlbumKey();
				
				album_key.setId(rs.getInt(1));
				album_key.setAlbumId(rs.getInt(2));
				album_key.setKeyId(rs.getInt(3));
				album_key.setUserId(rs.getInt(4));		
				
				rtnList.add(album_key);
			}
			
		} 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;   	       	  
        }
      /**
       *     判断 albumId 和keyId 的是否唯一性
       */
      public static AlbumKey findByAlbumKeyId(int albumId,int keyId){
    	  PreparedStatement pstmt=null;
  	      ResultSet rs=null;
  	      AlbumKey  albumkey=null;
  	      String sql="select * from album_key where albumId=? and keyId=?"; 	      
  		  try {
			pstmt=connection.prepareStatement(sql);
			pstmt.setInt(1, albumId);
		    pstmt.setInt(2, keyId);
		    rs=pstmt.executeQuery();
		    while(rs.next()){
		    	 albumkey=new AlbumKey();
		    	 
		    	 albumkey.setId(rs.getInt(1));
				 albumkey.setAlbumId(rs.getInt(2));
				 albumkey.setKeyId(rs.getInt(3));
				 albumkey.setUserId(rs.getInt(4));			
		    }
		} 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 albumkey; 	  
      }   
}