mssqlgroupdao.java

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

            for (totalRecords = 0; rst.next(); totalRecords++) {
                if ((totalRecords >= pageIndex) && (totalRecords < (pageIndex + pageSize))) {
                    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"));
                    page.getItems().add(item);
                }
            }
            page.setTotalRecords(totalRecords);
            return page;
        } catch (Exception e) {
            AppLogger.debug("MSSqlGroupDAO.getGroups().e.getMessage()=" + e.getMessage());
            e.printStackTrace();
            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 Group getGroup(int groupID) {
        int index = 1;
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rst = null;
        Group group = new Group();
        String sql = "SELECT * FROM Groups WHERE IndexId = ?";
        AppLogger.debug("MSSqlGroupDAO.getGroup().sql=" + sql);
        try {
            conn = super.getDBConnection();
            stmt = conn.prepareStatement(sql);
            stmt.setInt(index, groupID);
            rst = stmt.executeQuery();
            while (rst.next()) {
                group.setIndexID(groupID);
                group.setName(rst.getString("GroupName"));
                group.setDateCreated(rst.getDate("DateCreated"));
                group.setUserLastUpdate(rst.getString("UserLastUpdate"));
                group.setDateLastUpdate(rst.getDate("DateLastUpdate"));
                group.setDescription(rst.getString("Description"));
                group.setApproved(rst.getBoolean("IsApproved"));
            }
            return group;
        } catch (Exception e) {
            AppLogger.debug("MSSqlGroupDAO.getGroup().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 ArrayList getGroups(boolean approvedOnly) {
        int index = 1;
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rst = null;
        ArrayList array = new ArrayList();
        String sql = "SELECT * FROM Groups WHERE IsApproved = ?";
        AppLogger.debug("MSSqlGroupDAO.getGroups().sql=" + sql);
        try {
            conn = super.getDBConnection();
            stmt = conn.prepareStatement(sql);
            stmt.setBoolean(index, approvedOnly);
            rst = stmt.executeQuery();
            while (rst.next()) {
                Group group = new Group();
                group.setIndexID(rst.getInt("IndexId"));
                group.setName(rst.getString("GroupName"));
                group.setDateCreated(StringUtils.getDate(rst.getString("DateCreated")));
                group.setUserLastUpdate(rst.getString("UserLastUpdate"));
                group.setDateLastUpdate(StringUtils.getDate(rst.getString("DateLastUpdate")));
                group.setDescription(rst.getString("Description"));
                group.setApproved(rst.getBoolean("IsApproved"));
                array.add(group);
            }
            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 getGroups(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 * FROM Groups WHERE IsApproved = ?";
        System.out.println("MSSqlGroupDAO.getGroups().sql=" + sql);
        try {
            conn = super.getDBConnection();
            stmt = conn.prepareStatement(sql);
            stmt.setBoolean(index, approvedOnly);
            rst = stmt.executeQuery();
            for (totalRecords = 0; rst.next(); totalRecords++) {
                if ((totalRecords >= pageIndex) && (totalRecords < (pageIndex + pageSize))) {
                    Group group = new Group();
                    group.setIndexID(rst.getInt("IndexId"));
                    group.setName(rst.getString("GroupName"));
                    group.setDateCreated(rst.getDate("DateCreated"));
                    group.setUserLastUpdate(rst.getString("UserLastUpdate"));
                    group.setDateLastUpdate(rst.getDate("DateLastUpdate"));
                    group.setDescription(rst.getString("Description"));
                    group.setApproved(rst.getBoolean("IsApproved"));
                    page.getItems().add(group);
                }
            }
            page.setTotalRecords(totalRecords);
            return page;
        } catch (Exception e) {
            AppLogger.debug("MSSqlGroupDAO.getGroups().e.getMessage()=" + e.getMessage());
            e.printStackTrace();
            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 ArrayList getGroupsForMovie(int movieID, boolean approvedOnly) {
        int index = 1;
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rst = null;
        ArrayList array = new ArrayList();
        String sql = "SELECT * FROM Groups WHERE IndexId in (SELECT GroupId FROM MoviesInGroups WHERE MovieId = ?)";
        if (approvedOnly == true) {
            sql = sql + " AND IsApproved = 1";
        }
        AppLogger.debug("MSSqlGroupDAO.getGroupsForMovie().sql=" + sql);
        try {
            conn = super.getDBConnection();
            stmt = conn.prepareStatement(sql);
            stmt.setInt(index++, movieID);
            rst = stmt.executeQuery();
            while (rst.next()) {
                Group group = new Group();
                group.setIndexID(rst.getInt("IndexId"));
                group.setName(rst.getString("GroupName"));
                group.setDateCreated(rst.getDate("DateCreated"));
                group.setUserLastUpdate(rst.getString("UserLastUpdate"));
                group.setDateLastUpdate(rst.getDate("DateLastUpdate"));
                group.setDescription(rst.getString("Description"));
                group.setApproved(rst.getBoolean("IsApproved"));
                array.add(group);
            }
            return array;
        } 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 Page searchMovie(MovieCondition condition, int indexId, int start, int count){
    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,(SELECT COUNT(IndexID) FROM MovieChapters MC WHERE MC.MovieID = M.IndexID) AS TotalChapters FROM MoviesInGroups MI INNER JOIN Movies M ON MI.MovieId = M.IndexId WHERE (MI.GroupId = ?) ORDER BY MI.SortOrder";
    System.out.println("sadfkljhsadfl;kjasdf;lksadf;ljksadf"+sql);
        try {
            conn = super.getDBConnection();
            stmt = conn.prepareStatement(sql);
            stmt.setInt(1,indexId);
            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"));
                    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 boolean isGroupEmpty(int groupId) throws SQLException, DAOSysException {
        Connection conn = null;
        PreparedStatement stmt = null;
        String sql = "SELECT COUNT(*) FROM MoviesInGroups WHERE GroupID = ?";
        ResultSet rst = null;
        AppLogger.debug("MSSqlGroupDAO.isGroupEmpty().sql=" + sql);
        try {
            conn = super.getDBConnection();
            stmt = conn.prepareStatement(sql);
            stmt.setInt(1, groupId);
            rst = stmt.executeQuery();
            int count = 0;
            if (rst.next()) {
            	count = rst.getInt(1);
            }
            return count > 0 ? false : true;
        } catch (Exception e) {
            AppLogger.debug("MSSqlMovieDAO.isGroupEmpty().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());
            }
        }
	}
}