mssqlmoviedao.java

一个用struts tiles的在线影院web系统

						item.setApproved(rst.getBoolean("IsApproved"));
	                    page.getItems().add(item);
	                }
	            }
	            page.setTotalRecords(totalRecords);
	        } catch (Exception e) {
	            AppLogger.debug("MSSqlMovieDAO.searchMovie().e.getMessage()=" + e.getMessage());
	            throw new ResourceDAOSysException(e.getMessage());
	        } finally {
	            try {
	                super.closeResultSet(rst);
	                super.closeStatement(stmt);
	                super.closeConnection(conn);
	            } catch (Exception e) {
	                throw new ResourceDAOSysException(e.getMessage());
	            }
	    }
	    return page;
	}
	
	public void MovieInGroup(int movieID,int GroupID) throws SQLException,ResourceException{
		   Connection conn = null;
	       PreparedStatement stmt = null;
	       String sql = "INSERT INTO MoviesInGroups (MovieId, GroupId) VALUES (?, ?)";
	       AppLogger.debug("MSSqlMovieInGroupDAO.MovieInGroup().sql=" + sql);
	       try {
	           conn = super.getDBConnection();
	           stmt = conn.prepareStatement(sql);
	           stmt.setInt(1,movieID);
	           stmt.setInt(2,GroupID);
	           int rowCount = stmt.executeUpdate();
	           if (rowCount != 1) throw new ResourceDAOSysException("effected row count must be 1");
	       } catch (Exception e) {
	           AppLogger.debug("MSSqlMovieInGroupDAO.MovieInGroup().e.getMessage()=" + e.getMessage());
	           throw new ResourceDAOSysException(e.getMessage());
	       } finally {
	           try {
	               super.closeStatement(stmt);
	               super.closeConnection(conn);
	           } catch (Exception e) {
	               throw new ResourceDAOSysException(e.getMessage());
	           }
	       }
	   }
	
	public void createHotMovie(int movieId) throws SQLException,ResourceException{
		 Connection conn = null;
	     PreparedStatement stmt = null;
	     String sql = "INSERT INTO HotMovies (movieId) VALUES (?)";
	     try {
	         conn = super.getDBConnection();
	         stmt = conn.prepareStatement(sql);
	         stmt.setInt(1,movieId);
	         int rowCount = stmt.executeUpdate();
	         if (rowCount != 1) throw new ResourceDAOSysException("effected row count must be 1");
	     } catch (Exception e) {
	         AppLogger.debug("MSSqlMovieDAO.createMovie().e.getMessage()=" + e.getMessage());
	         throw new ResourceDAOSysException(e.getMessage());
	     } finally {
	         try {
	             super.closeStatement(stmt);
	             super.closeConnection(conn);
	         } catch (Exception e) {
	             throw new ResourceDAOSysException(e.getMessage());
	         }
	     }
	 }
	
	public Page searchHotMovie(MovieCondition condition, int start, int count) throws SQLException,ResourceException{
	    Connection conn = null;
	    PreparedStatement stmt = null;
	    ResultSet rst = null;
	    Page page = new Page();
	    String sql = "SELECT M.IndexId, M.Title, M.translated, M.publishDate , M.director, M.playwright, M.player, M.commend, M.Intro, M.coverImageUrl, M.Author, M.DateCreated, M.UserLastUpdate, M.DateLastUpdate, M.AccessCount, M.IsApproved, M.area, (SELECT COUNT(IndexID) FROM MovieChapters MC WHERE MC.MovieID = M.IndexID) AS TotalChapters FROM Movies M INNER JOIN HotMovies H ON M.IndexId = H.MovieId ORDER BY H.SortOrder";  
	    System.out.println("searchHotMovie().sql="+sql);
	        try {
	            conn = super.getDBConnection();
	            stmt = conn.prepareStatement(sql);
	
	            rst = stmt.executeQuery();
	            int totalRecords = 0;
	            for (totalRecords = 0; rst.next(); totalRecords++) {
	                if ((totalRecords >= start) && (totalRecords < (start + count))) {
	                    Movie item = new Movie();
	                    item.setIndexID(rst.getInt("IndexId"));
	                    item.setTotalChapters(rst.getInt("TotalChapters"));
						item.setTitle(rst.getString("Title"));
						item.setOriginName(rst.getString("translated"));
						item.setPublishDate(rst.getString("publishDate"));
						item.setDirector(rst.getString("director"));
						item.setPlaywright(rst.getString("playwright"));
						item.setActors(rst.getString("player"));
						item.setRecommend(rst.getInt("commend"));
						item.setIntro(rst.getString("Intro"));
						item.setOrigionFile(rst.getString("coverImageUrl"));
						item.setAuthor(rst.getString("Author"));
						item.setDateCreated(StringUtils.getDate(rst.getString("DateCreated")));
						item.setUserLastUpdate(rst.getString("UserLastUpdate"));
						item.setDateLastUpdate(StringUtils.getDate(rst.getString("DateLastUpdate")));
						item.setAccessCount(rst.getInt("AccessCount"));
						item.setApproved(rst.getBoolean("IsApproved"));
						item.setArea(rst.getString("area"));
	                    page.getItems().add(item);
	                }
	            }
	            page.setTotalRecords(totalRecords);
	        } catch (Exception e) {
	            AppLogger.debug("MSSqlHotMovieDAO.searchHotMovie().e.getMessage()=" + e.getMessage());
	            throw new ResourceDAOSysException(e.getMessage());
	        } finally {
	            try {
	                super.closeResultSet(rst);
	                super.closeStatement(stmt);
	                super.closeConnection(conn);
	            } catch (Exception e) {
	                throw new ResourceDAOSysException(e.getMessage());
	            }
	        }
	    return page;
	}
	
	public Page searchHot2Movie(MovieCondition condition, int start, int count) throws SQLException,ResourceException{
	    Connection conn = null;
	    PreparedStatement stmt = null;
	    ResultSet rst = null;
	    Page page = new Page();
	    String sql = "SELECT * FROM Movies WHERE (IndexId NOT IN (SELECT H.MovieId FROM HotMovies H INNER JOIN Movies M ON M.IndexId = H.MovieId))";  
	    System.out.println("searchHot2Movie().sql="+sql);
	        try {
	            conn = super.getDBConnection();
	            stmt = conn.prepareStatement(sql);
	
	            rst = stmt.executeQuery();
	            int totalRecords = 0;
	            for (totalRecords = 0; rst.next(); totalRecords++) {
	                if ((totalRecords >= start) && (totalRecords < (start + count))) {
	                    Movie item = new Movie();
	                    item.setIndexID(rst.getInt("IndexId"));
						item.setTitle(rst.getString("Title"));
						item.setOriginName(rst.getString("translated"));
						item.setPublishDate(rst.getString("publishDate"));
						item.setDirector(rst.getString("director"));
						item.setPlaywright(rst.getString("playwright"));
						item.setActors(rst.getString("player"));
						item.setRecommend(rst.getInt("commend"));
						item.setIntro(rst.getString("Intro"));
						item.setOrigionFile(rst.getString("coverImageUrl"));
						item.setAuthor(rst.getString("Author"));
						item.setDateCreated(StringUtils.getDate(rst.getString("DateCreated")));
						item.setUserLastUpdate(rst.getString("UserLastUpdate"));
						item.setDateLastUpdate(StringUtils.getDate(rst.getString("DateLastUpdate")));
						item.setAccessCount(rst.getInt("AccessCount"));
						item.setApproved(rst.getBoolean("IsApproved"));
	                    page.getItems().add(item);
	                }
	            }
	            page.setTotalRecords(totalRecords);
	        } catch (Exception e) {
	            AppLogger.debug("MSSqlHotMovieDAO.searchHotMovie().e.getMessage()=" + e.getMessage());
	            throw new ResourceDAOSysException(e.getMessage());
	        } finally {
	            try {
	                super.closeResultSet(rst);
	                super.closeStatement(stmt);
	                super.closeConnection(conn);
	            } catch (Exception e) {
	                throw new ResourceDAOSysException(e.getMessage());
	            }
	        }
	    return page;
	}
	
	public void deleteHotMovie(int movieId) throws SQLException,ResourceException{
		 Connection conn = null;
	    PreparedStatement stmt = null;
	    String sql = "DELETE HotMovies WHERE movieId = ?";
	    try {
	        conn = super.getDBConnection();
	        stmt = conn.prepareStatement(sql);
	        stmt.setInt(1,movieId);
	        int rowCount = stmt.executeUpdate();
	        if (rowCount != 1) throw new ResourceDAOSysException("effected row count must be 1");
	    } catch (Exception e) {
	        AppLogger.debug("MSSqlHotMovieDAO.deleteHotMovie().e.getMessage()=" + e.getMessage());
	        throw new ResourceDAOSysException(e.getMessage());
	    } finally {
	        try {
	            super.closeStatement(stmt);
	            super.closeConnection(conn);
	        } catch (Exception e) {
	            throw new ResourceDAOSysException(e.getMessage());
	        }
	    }
	}
	
	public void setHotSortOrder(int sortOrder,int IndexId) throws SQLException,ResourceException {
	    Connection conn = null;
	    PreparedStatement stmt = null;
	    String sql = "UPDATE HotMovies SET SortOrder = ? WHERE movieId = ?";
	    try {
	        conn = super.getDBConnection();
	        stmt = conn.prepareStatement(sql);
	        stmt.setInt(1, sortOrder);
	        stmt.setInt(2, IndexId);
	        int rowCount = stmt.executeUpdate();
	        if (rowCount != 1) throw new ResourceDAOSysException("invalid effected row count");
	    } catch (Exception e) {
	    	e.printStackTrace();
	        AppLogger.debug("MSSqlResourceDAO.setHotSortOrder().e.getMessage()=" + e.getMessage());
	        throw new ResourceDAOSysException(e.getMessage());
	    } finally {
	        try {
	            super.closeStatement(stmt);
	            super.closeConnection(conn);
	        } catch (Exception e) {
	            throw new ResourceDAOSysException(e.getMessage());
	        }
	    }
	}
	
	public void setGroupMovieSortOrder(int sortOrder,int IndexId,int groupId) throws SQLException,ResourceException {
	    Connection conn = null;
	    PreparedStatement stmt = null;
	    String sql = "UPDATE MoviesInGroups SET SortOrder = ? WHERE MovieId = ? AND GroupId = ?";
	    try {
	        conn = super.getDBConnection();
	        stmt = conn.prepareStatement(sql);
	        stmt.setInt(1, sortOrder);
	        stmt.setInt(2, IndexId);
	        stmt.setInt(3,groupId);
	        int rowCount = stmt.executeUpdate();
	        if (rowCount != 1) throw new ResourceDAOSysException("invalid effected row count");
	    } catch (Exception e) {
	    	e.printStackTrace();
	        AppLogger.debug("MSSqlResourceDAO.setHotSortOrder().e.getMessage()=" + e.getMessage());
	        throw new ResourceDAOSysException(e.getMessage());
	    } finally {
	        try {
	            super.closeStatement(stmt);
	            super.closeConnection(conn);
	        } catch (Exception e) {
	            throw new ResourceDAOSysException(e.getMessage());
	        }
	    }
	}
	
	public void deleteMovieIn(int groupId, int movieId) throws SQLException,ResourceException{
	   Connection conn = null;
	   PreparedStatement stmt = null;
	   String sql = "DELETE MoviesInGroups WHERE groupId = ? AND movieId = ?";
	   try {
	       conn = super.getDBConnection();
	       stmt = conn.prepareStatement(sql);
	       stmt.setInt(1,groupId);
	       stmt.setInt(2,movieId);
	       int rowCount = stmt.executeUpdate();
	       if (rowCount != 1) throw new ResourceDAOSysException("effected row count must be 1");
	   } catch (Exception e) {
	       AppLogger.debug("MSSqlMovieInDAO.deleteMovieIn().e.getMessage()=" + e.getMessage());
	       throw new ResourceDAOSysException(e.getMessage());
	   } finally {
	       try {
	           super.closeStatement(stmt);
	           super.closeConnection(conn);
	       } catch (Exception e) {
	           throw new ResourceDAOSysException(e.getMessage());
	       }
	   }
	}
	public void updateAccessCount(int movieId) throws SQLException,
			ResourceException {
		Connection conn = null;
		PreparedStatement stmt = null;
		String sql = "UPDATE Movies SET AccessCount = (AccessCount + 1) WHERE IndexID = ?";
		try {
			conn = super.getDBConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, movieId);
			int rowCount = stmt.executeUpdate();
			if (rowCount != 1)
				throw new ResourceDAOSysException(
						"effected row count should be 1");
	} catch (Exception e) {
			AppLogger.debug("MSSqlMovieInDAO.updateAccessCount().e.getMessage()="
							+ e.getMessage());
			throw new ResourceDAOSysException(e.getMessage());
		} finally {
			super.closeStatement(stmt);
			super.closeConnection(conn);
		}
		return;
	}
}