mssqlgroupdao.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.ArrayList;
import java.util.Date;

import com.blue.web.common.exceptions.DAOSysException;
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.client.Groups;
import com.eline.vod.exceptions.GroupDAOSysException;
import com.eline.vod.exceptions.ResourceDAOSysException;
import com.eline.vod.model.Group;
import com.eline.vod.model.Movie;
import com.eline.vod.model.MovieCondition;
public class MSSqlGroupDAO extends DBSqlManager implements GroupDAO {
    public int addGroup(Group group) throws DAOSysException, SQLException {
        int index = 1;
        Connection conn = null;
        PreparedStatement stmt = null;
        String sql = "INSERT INTO GROUPS (GroupName, UserLastUpdate, DateLastUpdate, Description, IsApproved) VALUES (?, ?, ?, ?, ?)";
        AppLogger.debug("MSSqlGroupDAO.addGroup().sql=" + sql);
        try {
            conn = super.getDBConnection();
            stmt = conn.prepareStatement(sql);
            stmt.setString(index++, group.getName());
            // stmt.setString(2, group.getUserLastUpdate());
            stmt.setString(index++, group.getUserLastUpdate());
            stmt.setString(index++, StringUtils.toString(new Date()));
            stmt.setString(index++, group.getDescription());
            stmt.setBoolean(index++, group.isApproved());
            int rowCount = stmt.executeUpdate();
            if (rowCount != 1) {
                throw new GroupDAOSysException("effected row count must be 1");
            } else {
                return rowCount;
            }
        } catch (Exception e) {
            AppLogger.debug("MSSqlGroupDAO.addGroup().e.getMessage()=" + e.getMessage());
            throw new GroupDAOSysException(e.getMessage());
        } finally {
            try {
                super.closeStatement(stmt);
                super.closeConnection(conn);
            } catch (Exception e) {
                throw new GroupDAOSysException(e.getMessage());
            }
        }
    }
    public void upateGroup(Group group) throws DAOSysException, SQLException {
        int index = 1;
        Connection conn = null;
        PreparedStatement stmt = null;
        String sql = "UPDATE GROUPS SET GroupName = ?, UserLastUpdate = ?, DateLastUpdate = ?, Description = ?, IsApproved = ? WHERE IndexID = ?";
        AppLogger.debug("MSSqlGroupDAO.upateGroup().sql=" + sql);
        try {
            conn = super.getDBConnection();
            stmt = conn.prepareStatement(sql);
            stmt = conn.prepareStatement(sql);
            stmt.setString(index++, group.getName());
            stmt.setString(index++, group.getUserLastUpdate());
            stmt.setString(index++, StringUtils.toString(new Date()));
            stmt.setString(index++, group.getDescription());
            stmt.setBoolean(index++, group.isApproved());
            stmt.setInt(index++, group.getIndexID());
            int rowCount = stmt.executeUpdate();
            if (rowCount != 1) throw new GroupDAOSysException("effected row count must be 1");
        } catch (Exception e) {
            e.printStackTrace();
            AppLogger.debug("MSSqlCategoryDAO.updateHotlink().e.getMessage()=" + e.getMessage());
            throw new GroupDAOSysException(e.getMessage());
        } finally {
            try {
                super.closeStatement(stmt);
                super.closeConnection(conn);
            } catch (Exception e) {
                throw new GroupDAOSysException(e.getMessage());
            }
        }
    }
    public void deleteGroup(int groupID) throws DAOSysException, SQLException {
        int index = 1;
        Connection conn = null;
        PreparedStatement stmt = null;
        String sql = "DELETE GROUPS WHERE IndexID = ?";
        String sql1 = "DELETE FROM MoviesInGroups WHERE GroupId = ?";
        
        AppLogger.debug("MSSqlGroupDAO.deleteGroup().sql=" + sql);
        try {
            conn = super.getDBConnection();
            // 从组中删除所有影片引用
            stmt = conn.prepareStatement(sql1);
            stmt.setInt(index, groupID);
            stmt.executeUpdate();
            // 删除组
            stmt = conn.prepareStatement(sql);
            stmt.setInt(index, groupID);
            int rowCount = stmt.executeUpdate();
            if (rowCount != 1) throw new GroupDAOSysException("effected row count must be 1");
        } catch (Exception e) {
            AppLogger.debug("MSSqlGroupDAO.deleteGroup().e.getMessage()=" + e.getMessage());
            throw new GroupDAOSysException(e.getMessage());
        } finally {
            try {
                super.closeStatement(stmt);
                super.closeConnection(conn);
            } catch (Exception e) {
                throw new GroupDAOSysException(e.getMessage());
            }
        }
    }
    public void addMovieToGroup(int movieID, int groupID) {
        int index = 1;
        Connection conn = null;
        PreparedStatement stmt = null;
        String sql = "INSERT INTO MoviesInGroups (MovieId, GroupId, SortOrder) VALUES (?, ?, 0)";
        AppLogger.debug("MSSqlGroupDAO.addMovieToGroup().sql=" + sql);
        try {
            conn = super.getDBConnection();
            stmt = conn.prepareStatement(sql);
            stmt.setInt(index++, movieID);
            stmt.setInt(index++, groupID);
            int rowCount = stmt.executeUpdate();
            if (rowCount != 1) {
                throw new GroupDAOSysException("effected row count must be 1");
            }
        } catch (Exception e) {
            AppLogger.debug("MSSqlGroupDAO.addMovieToGroup().e.getMessage()=" + e.getMessage());
            throw new GroupDAOSysException(e.getMessage());
        } finally {
            try {
                super.closeStatement(stmt);
                super.closeConnection(conn);
            } catch (Exception e) {
                throw new GroupDAOSysException(e.getMessage());
            }
        }
    }
    public void removeMovieFromGroup(int movieID, int groupID) {
        int index = 1;
        Connection conn = null;
        PreparedStatement stmt = null;
        String sql = "DELETE MoviesInGroups WHERE MovieId = ? and GroupId = ?";
        AppLogger.debug("MSSqlGroupDAO.removeMovieFromGroup().sql=" + sql);
        try {
            conn = super.getDBConnection();
            stmt = conn.prepareStatement(sql);
            stmt.setInt(index++, movieID);
            stmt.setInt(index++, groupID);
            int rowCount = stmt.executeUpdate();
            if (rowCount != 1) throw new GroupDAOSysException("effected row count must be 1");
        } catch (Exception e) {
            AppLogger.debug("MSSqlGroupDAO.deleteGroup().e.getMessage()=" + e.getMessage());
            throw new GroupDAOSysException(e.getMessage());
        } finally {
            try {
                super.closeStatement(stmt);
                super.closeConnection(conn);
            } catch (Exception e) {
                throw new GroupDAOSysException(e.getMessage());
            }
        }
    }
    public void removeMovieFromGroup(int movieID) {
        int index = 1;
        Connection conn = null;
        PreparedStatement stmt = null;
        String sql = "DELETE MoviesInGroups WHERE MovieId = ?";
        AppLogger.debug("MSSqlGroupDAO.removeMovieFromGroup().sql=" + sql);
        try {
            conn = super.getDBConnection();
            stmt = conn.prepareStatement(sql);
            stmt.setInt(index++, movieID);
            int rowCount = stmt.executeUpdate();
            if (rowCount != 1) throw new GroupDAOSysException("effected row count must be 1");
        } catch (Exception e) {
            AppLogger.debug("MSSqlGroupDAO.deleteGroup().e.getMessage()=" + e.getMessage());
            throw new GroupDAOSysException(e.getMessage());
        } finally {
            try {
                super.closeStatement(stmt);
                super.closeConnection(conn);
            } catch (Exception e) {
                throw new GroupDAOSysException(e.getMessage());
            }
        }
    }
    public ArrayList getMoviesInGroup(int groupID, int sortBy, boolean approvedOnly) {
        int index = 1;
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rst = null;
        ArrayList array = new ArrayList();
        String sql = "SELECT * FROM MOVIES WHERE IndexId IN (SELECT MovieId FROM MoviesInGroups WHERE GroupId = ?)";
        if (approvedOnly == true) {
            sql = sql + " AND IsApproved = 1";
        }
        /*
         * else { sql = sql + "AND IsApproved = 0"; }
         */
        if (sortBy == Groups.SortBy_AccessCount) {
            sql = sql + " ORDER BY AccessCount";
        } else if (sortBy == Groups.SortBy_IndexID) {
            sql = sql + " ORDER BY IndexID";
        } else if (sortBy == Groups.SortBy_LastUpdate) {
            sql = sql + " ORDER BY DateLastUpdate";
        }
        /*
         * else if (sortBy == Groups.SortBy_SortOrder) { sql = sql + "ORDER BY SortOrder"; }
         */
        AppLogger.debug("MSSqlGroupDAO.getMoviesInGroup().sql=" + sql);
        try {
            conn = super.getDBConnection();
            stmt = conn.prepareStatement(sql);
            stmt.setInt(index, groupID);
            rst = stmt.executeQuery();
            while (rst.next()) {
                Movie item = new Movie();
                item.setIndexID(rst.getInt("IndexId"));
                item.setTitle(rst.getString("Title"));
                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"));
                array.add(item);
            }
            return array;
        } catch (Exception e) {
            AppLogger.debug("MSSqlGroupDAO.getMoviesInGroup().e.getMessage()=" + e.getMessage());
            throw new GroupDAOSysException(e.getMessage());
        } finally {
            try {
                super.closeResultSet(rst);
                super.closeStatement(stmt);
                super.closeConnection(conn);
            } catch (Exception e) {
                throw new GroupDAOSysException(e.getMessage());
            }
        }
    }
    public Page getMoviesInGroup(int groupID, int sortBy, int pageIndex, int pageSize, boolean approvedOnly) {
        int index = 1;
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rst = null;
        Page page = new Page();
        int totalRecords = 0;
        String sql = "SELECT M.*, (SELECT COUNT(IndexID) FROM MovieChapters MC WHERE MC.MovieID = M.IndexID) AS TotalChapters FROM Movies M INNER JOIN MoviesInGroups MIG ON M.IndexId = MIG.MovieId WHERE (MIG.GroupId = ?)";
        if (approvedOnly == true) {
            sql = sql + " AND IsApproved = 1";
        }
        /*
         * else { sql = sql + "AND IsApproved = 0"; }
         */
        if (sortBy == Groups.SortBy_AccessCount) {
            sql = sql + " ORDER BY M.AccessCount DESC";
        } else if (sortBy == Groups.SortBy_IndexID) {
            sql = sql + " ORDER BY M.IndexID";
        } else if (sortBy == Groups.SortBy_LastUpdate) {
            sql = sql + " ORDER BY M.DateLastUpdate DESC";
        } else if (sortBy == Groups.SortBy_SortOrder) {
        	sql += " ORDER BY MIG.SortOrder";
        }
        /*
         * else if (sortBy == Groups.SortBy_SortOrder) { sql = sql + "ORDER BY SortOrder"; }
         */
        AppLogger.debug("MSSqlGroupDAO.getMoviesInGroup().sql=" + sql);
        try {
            conn = super.getDBConnection();
            stmt = conn.prepareStatement(sql);
            stmt.setInt(index, groupID);
            rst = stmt.executeQuery();