mssqlmoviechapterdao.java

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

package com.eline.vod.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;

import com.blue.web.common.jdbc.DBSqlManager;
import com.blue.web.common.model.Page;
import com.blue.web.common.util.AppLogger;
import com.blue.web.common.util.StringUtils;
import com.eline.vod.exceptions.ResourceDAOSysException;
import com.eline.vod.exceptions.ResourceException;
import com.eline.vod.model.MovieChapter;
import com.eline.vod.model.MovieChapterCondition;

public class MSSqlMovieChapterDAO extends DBSqlManager implements MovieChapterDAO{
	
	public MovieChapter getMovieChapter(int movieId) throws SQLException,ResourceException{
		String sql = "SELECT IndexID, MovieID, Title, StreamURL, DownloadURL, DateLastUpdate, UserLastUpdate, SortOrder, AccessCount, IsApproved FROM MovieChapters WHERE IndexID = ? ORDER BY IndexId DESC";
		System.out.println("sql="+sql);
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rst = null;
		MovieChapter item = new MovieChapter();
		try{
			conn = super.getDBConnection();
			try{
				stmt = conn.prepareStatement(sql);
				 stmt.setInt(1, movieId);
				rst = stmt.executeQuery();
				while(rst.next()){
					item.setIndexId(rst.getInt("IndexId"));
					item.setMovieId(rst.getInt("MovieID"));
					item.setTitle(rst.getString("Title"));
					item.setStreamUrl(rst.getString("StreamURL"));
					item.setDownloadUrl(rst.getString("DownloadURL"));
					item.setDateLastUpdate(rst.getDate("DateLastUpdate"));
					item.setUserLastUpdate(rst.getString("UserLastUpdate"));
					item.setSortOrder(rst.getInt("SortOrder"));
					item.setAccessCount(rst.getInt("AccessCount"));
					item.setApproved(rst.getBoolean("IsApproved"));
				}
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
						rst.close();
						stmt.close();
						conn.close();
				}	
		}catch(Exception e){
			e.printStackTrace();
		}
		return item;
	}
	protected static String[] SEARCH_MOVIE_STATEMENT_ORDERS = {
		" ORDER BY SortOrder", " ORDER BY AccessCount DESC", " ORDER BY DateLastUpdate DESC" };
	public Page searchMovieChapter(MovieChapterCondition condition, int start, int count) throws SQLException,ResourceException{
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rst = null;
        Page page = new Page();
        String sql = "SELECT IndexID, MovieID, Title, StreamURL, DownloadURL, DateLastUpdate, UserLastUpdate, SortOrder, AccessCount, IsApproved FROM MovieChapters WHERE MovieId = ?";
      if (condition != null) {
            if (condition.isApprovedOnly()) {
               	sql += " AND IsApproved = 1 ";
            }
      }
	        sql += SEARCH_MOVIE_STATEMENT_ORDERS[condition.getSortBy()];
	        System.out.println("sql="+sql);
	        try {
	            conn = super.getDBConnection();
	            stmt = conn.prepareStatement(sql);
	            stmt.setInt(1, condition.getMovieId());
	            rst = stmt.executeQuery();
	            int totalRecords = 0;
	            for (totalRecords = 0; rst.next(); totalRecords++) {
	                if ((totalRecords >= start) && (totalRecords < (start + count))) {
	                    MovieChapter item = new MovieChapter();
	                    item.setIndexId(rst.getInt("IndexID"));
						item.setMovieId(rst.getInt("MovieID"));
						item.setTitle(rst.getString("Title"));
						item.setStreamUrl(rst.getString("StreamURL"));
						item.setDownloadUrl(rst.getString("DownloadURL"));
						item.setDateLastUpdate(StringUtils.getDate(rst.getString("DateLastUpdate")));
						item.setUserLastUpdate(rst.getString("UserLastUpdate"));
						item.setSortOrder(rst.getInt("SortOrder"));
						item.setAccessCount(rst.getInt("AccessCount"));
						item.setApproved(rst.getBoolean("IsApproved"));
	                    page.getItems().add(item);
	                }
	            }
	            page.setTotalRecords(totalRecords);
	         } catch (Exception e) {
	        	e.printStackTrace();
	            AppLogger.debug("MSSqlMovieChapterDAO.searchMovieChapter().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 createMovieChapter(MovieChapter item) throws SQLException,ResourceException{
		 Connection conn = null;
	        PreparedStatement stmt = null;
	        String sql = "INSERT INTO MovieChapters (MovieID, Title, StreamURL, DownloadURL, DateLastUpdate, UserLastUpdate, SortOrder, AccessCount, IsApproved) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
	        AppLogger.debug("MSSqlMovieChapterDAO.createMovieChapter().sql=" + sql);
	        try {
	            conn = super.getDBConnection();
	            stmt = conn.prepareStatement(sql);
	            stmt.setInt(1,item.getMovieId());
				stmt.setString(2,item.getTitle());
				stmt.setString(3,item.getStreamUrl());
				stmt.setString(4,item.getDownloadUrl());
				String dateLastUpdate = StringUtils.toString(new Date());
				stmt.setString(5,dateLastUpdate);
				stmt.setString(6,item.getUserLastUpdate());
				stmt.setInt(7,item.getSortOrder());
				stmt.setInt(8,6);
				stmt.setBoolean(9,item.isApproved());
	            int rowCount = stmt.executeUpdate();
	            if (rowCount != 1) throw new ResourceDAOSysException("effected row count must be 1");
	        } catch (Exception e) {
	            AppLogger.debug("MSSqlMovieChapterDAO.createMovieChapter().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 updateMovieChapter(MovieChapter item) throws SQLException,ResourceException{
		Connection conn = null;
        PreparedStatement stmt = null;
        String sql = "UPDATE MovieChapters SET MovieID = ?, Title = ?, StreamURL = ?, DownloadURL = ?, DateLastUpdate = ?, UserLastUpdate = ?, SortOrder = ?, AccessCount = ?, IsApproved = ?"+ " WHERE IndexID = ?";
        //TODO 
        System.out.println("sql"+sql);
        try {
            conn = super.getDBConnection();
            stmt = conn.prepareStatement(sql);
            stmt.setInt(1,item.getMovieId());
			stmt.setString(2,item.getTitle());
			stmt.setString(3,item.getStreamUrl());
			stmt.setString(4,item.getDownloadUrl());
			String dateLastUpdate = StringUtils.toString(new Date());
			stmt.setString(5,dateLastUpdate);
			stmt.setString(6,item.getUserLastUpdate());
			stmt.setInt(7,item.getSortOrder());
			stmt.setInt(8,6);
			stmt.setBoolean(9,item.isApproved());
			stmt.setInt(10,item.getIndexId());
			stmt.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
            throw new ResourceDAOSysException(e.getMessage());
        } finally {
            try {
                super.closeStatement(stmt);
                super.closeConnection(conn);
            } catch (Exception e) {
                throw new ResourceDAOSysException(e.getMessage());
            }
        }
    }

	public void deleteMovieChapter(int movieId) throws SQLException,ResourceException{
		 Connection conn = null;
	        PreparedStatement stmt = null;
	        // 不是真正从数据库删除，而是设置删除标记为1
	        String sql = "DELETE MovieChapters WHERE IndexID = ?";
	        System.out.println("sql=" + sql);
	        try {
	            conn = super.getDBConnection();
	            stmt = conn.prepareStatement(sql);
	            stmt.setInt(1, movieId);
	            int rowCount = stmt.executeUpdate();
	            if (rowCount != 1) throw new ResourceDAOSysException("invalid effected row count");
	        } catch (Exception e) {
	            throw new ResourceDAOSysException(e.getMessage());
	        } finally {
	            try {
	                super.closeStatement(stmt);
	                super.closeConnection(conn);
	            } catch (Exception e) {
	                throw new ResourceDAOSysException(e.getMessage());
	            }
	        }
	    }
	
	public void setSortOrder(int sortOrder,int IndexId) throws SQLException,ResourceException {
        Connection conn = null;
        PreparedStatement stmt = null;
        String sql = "UPDATE MovieChapters SET SortOrder = ? WHERE IndexID = ?";
        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.setSortOrder().e.getMessage()=" + e.getMessage());
            throw new ResourceDAOSysException(e.getMessage());
        } finally {
            try {
                super.closeStatement(stmt);
                super.closeConnection(conn);
            } catch (Exception e) {
            	e.printStackTrace();
                throw new ResourceDAOSysException(e.getMessage());
            }
        }
    }
}