mssqlmoviedao.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.Group;
import com.eline.vod.model.GroupCondition;
import com.eline.vod.model.Movie;
import com.eline.vod.model.MovieCondition;

public class MSSqlMovieDAO extends DBSqlManager implements MovieDAO{
	
	public Movie getMovie(int movieId) throws SQLException,ResourceException{
		String sql = "SELECT IndexId, Title, translated, publishDate, director, playwright, player, commend, Intro, coverImageUrl, Author, DateCreated, UserLastUpdate, DateLastUpdate, AccessCount, IsApproved, area FROM Movies WHERE IndexId = ? ORDER BY IndexId DESC";
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rst = null;
		Movie item = new Movie();
		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.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(rst.getDate("DateCreated"));
					item.setUserLastUpdate(rst.getString("UserLastUpdate"));
					item.setDateLastUpdate(rst.getDate("DateLastUpdate"));
					item.setAccessCount(rst.getInt("AccessCount"));
					item.setApproved(rst.getBoolean("IsApproved"));
					item.setArea(rst.getString("area"));
				}
			} 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 AccessCount DESC", " ORDER BY DateLastUpdate DESC" };
	public Page searchMovie(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 IndexId, Title, translated, publishDate, director, playwright, player, commend, Intro, coverImageUrl, Author, DateCreated, UserLastUpdate, DateLastUpdate, AccessCount, IsApproved, area, (SELECT COUNT(IndexID) FROM MovieChapters MC WHERE MC.MovieID = M.IndexID) AS TotalChapters FROM Movies M WHERE Deleted <> 1 ";
        if (condition != null) {
            if (condition.getTitle() != null) {
                sql += "AND Title LIKE ? OR translated LIKE ?";
            }
            if (condition.isApprovedOnly()) {
                sql += "AND IsApproved = 1 ";
            }
	        sql += SEARCH_MOVIE_STATEMENT_ORDERS[condition.getSortBy()];
	        System.out.println("searchMovie().sql="+sql);
	        try {
	            conn = super.getDBConnection();
	            stmt = conn.prepareStatement(sql);
	            // Set condition parameters
	            if (condition != null) {
	                int index = 1;
	                if (condition.getTitle() != null) {
	                	stmt.setString(index++, "%" + condition.getTitle() + "%");
	                	stmt.setString(index++, "%" + condition.getTitle() + "%");
	                }

	            }
	            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("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 createMovie(Movie item) throws SQLException,ResourceException{
		 Connection conn = null;
	        PreparedStatement stmt = null;
	        String sql = "INSERT INTO Movies (Title, translated, publishDate, director, playwright, player, commend, Intro,coverImageUrl, Author, UserLastUpdate,DateLastUpdate,AccessCount,isApproved,area) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
	        AppLogger.debug("MSSqlMovieDAO.createMovie().sql=" + sql);
	        try {
	            conn = super.getDBConnection();
	            stmt = conn.prepareStatement(sql);
	            stmt.setString(1,item.getTitle());
	            stmt.setString(2,item.getOriginName());
	            stmt.setString(3,item.getPublishDate());
	            stmt.setString(4,item.getDirector());
	            stmt.setString(5,item.getPlaywright());
	            stmt.setString(6,item.getActors());
	            stmt.setInt(7,item.getRecommend());
				stmt.setString(8,item.getIntro());
				stmt.setString(9,item.getOrigionFile());
				stmt.setString(10,item.getAuthor());
				stmt.setString(11,item.getUserLastUpdate());
				String dateLastUpdate = StringUtils.toString(new Date());
				stmt.setString(12,dateLastUpdate);
				stmt.setInt(13,6);
				stmt.setBoolean(14,item.isApproved());
				stmt.setString(15,item.getArea());
	            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 void updateMovie(Movie item) throws SQLException,ResourceException{
		Connection conn = null;
        PreparedStatement stmt = null;
        String sql = "UPDATE Movies SET Title = ?, translated = ?, publishDate = ?, director = ?, playwright = ?, player = ?, commend = ?,Intro = ?,coverImageUrl = ?, Author = ?, UserLastUpdate = ?,DateLastUpdate = ?,AccessCount = ?,isApproved = ?, area = ?"+ "WHERE IndexID = ?";
        try {
            conn = super.getDBConnection();
            stmt = conn.prepareStatement(sql);
            stmt.setString(1,item.getTitle());
            stmt.setString(2,item.getOriginName());
            stmt.setString(3,item.getPublishDate());
            stmt.setString(4,item.getDirector());
            stmt.setString(5,item.getPlaywright());
            stmt.setString(6,item.getActors());
            stmt.setInt(7, item.getRecommend());
			stmt.setString(8,item.getIntro());
			stmt.setString(9,item.getOrigionFile());
			stmt.setString(10,item.getAuthor());
			stmt.setString(11,item.getUserLastUpdate());
			String dateLastUpdate = StringUtils.toString(new Date());
			stmt.setString(12,dateLastUpdate);
			stmt.setInt(13,6);
			stmt.setBoolean(14,item.isApproved());
			stmt.setString(15,item.getArea());
			stmt.setInt(16,item.getIndexID());
            int rowCount = stmt.executeUpdate();
            if (rowCount != 1) throw new ResourceDAOSysException("invalid effected row count");
        } 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 deleteMovie(int movieId) throws SQLException,ResourceException{
		 Connection conn = null;
	        PreparedStatement stmt = null;
	        // 不是真正从数据库删除，而是设置删除标记为1
	        String sql = "DELETE Movies WHERE IndexID = ?";
	        String sql1 = "DELETE MovieChapters WHERE MovieID = ?";
	        String sql2 = "DELETE FROM MoviesInGroups WHERE MovieID = ?";
	        System.out.println("deleteMovie().sql=" + sql);
	        try {
	        	 conn = super.getDBConnection();
	        	 //delete MoviesInGroups
	        	 stmt = conn.prepareStatement(sql2);
	             stmt.setInt(1, movieId);
	             stmt.executeUpdate();
	             //delete MovieChapters
	        	 stmt = conn.prepareStatement(sql1);
	             stmt.setInt(1, movieId);
	             stmt.executeUpdate();
	             // delete Movies
	             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 Movies 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) {
            AppLogger.debug("MSSqlResourceDAO.setSortOrder().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 searchGroup(GroupCondition condition, int start, int count, int movieId) throws SQLException,ResourceException{
	    Connection conn = null;
	    PreparedStatement stmt = null;
	    ResultSet rst = null;
	    Page page = new Page();
	    String sql = "SELECT G.* FROM Groups G WHERE (IndexId NOT IN (SELECT MIG.GroupId FROM Movies M INNER JOIN MoviesInGroups MIG ON M.IndexId = MIG.MovieId WHERE (M.IndexId = ?)))";
	         System.out.println("searchGroup().sql="+sql);
	        try {
	            conn = super.getDBConnection();
	            stmt = conn.prepareStatement(sql);
	            stmt.setInt(1,movieId);
	            rst = stmt.executeQuery();
	            int totalRecords = 0;
	            for (totalRecords = 0; rst.next(); totalRecords++) {
	                if ((totalRecords >= start) && (totalRecords < (start + count))) {
	                    Group item = new Group();
	                    item.setIndexID(rst.getInt("IndexId"));
	                    item.setName(rst.getString("GroupName"));
						item.setDateCreated(StringUtils.getDate(rst.getString("DateCreated")));
						item.setDateLastUpdate(StringUtils.getDate(rst.getString("DateLastUpdate")));
						item.setDescription(rst.getString("Description"));