mssqlbookdao.java

一个免费wap站

				super.closeConnection(conn);
			} catch (Exception e) {
				throw new ResourceDAOSysException(e.getMessage());
			}
		}
	}

	
	protected Book populateBookFormResultSet(ResultSet rst) throws SQLException {
		Book item = new Book();
		
		// Fill section properties.
		DBCommonUtils.populateSectionFormResultSet(rst, item);
		
		return item;
	}
	
	protected void fillBookParameters(PreparedStatement stmt, Book item, int type) throws SQLException {
		int index = 1;
		try {
			if (type == TYPE_ADD) {
				stmt.setInt(index++, item.getParentId());
				stmt.setInt(index++, item.getSortOrder());
				stmt.setString(index++, StringUtils.toString(item.getDateCreated()));
				stmt.setString(index++, StringUtils.toString(item.getLastUpdate()));
				stmt.setBoolean(index++, item.isActive());
				stmt.setBoolean(index++, item.isSearchable());
				stmt.setString(index++, item.getSearchKey());

				// Serialize datas
				SerializeData data = item.deserialize();
				stmt.setString(index++, data.getKeys());
				stmt.setString(index++, data.getValues());

			} else if (type == TYPE_UPDATE) {
				stmt.setInt(index++, item.getSortOrder());
				stmt.setString(index++, StringUtils.toString(item.getLastUpdate()));
				stmt.setBoolean(index++, item.isActive());
				stmt.setBoolean(index++, item.isSearchable());
				stmt.setString(index++, item.getSearchKey());

				// Serialize datas
				SerializeData data = item.deserialize();
				stmt.setString(index++, data.getKeys());
				stmt.setString(index++, data.getValues());

				stmt.setInt(index++, item.getIndexId());
			}
		} catch (SQLException e) {
			throw e;
		}
	}

	//////////////////////// BOOK CHAPTER ///////////////////////////////

	public void createBookChapter(BookChapter item) throws ResourceDAOSysException {
		Connection conn = null;
		PreparedStatement stmt = null;

		String sql = "INSERT INTO TAB_BOOKCHAPTER (BookID, Title, SortOrder, Intro, Content) VALUES (?, ?, ?, ?, ?)";

		AppLogger.debug("MSSqlBookDAO.createBookChapter().sql=" + sql);

		try {
			conn = super.getDBConnection();
			stmt = conn.prepareStatement(sql);

			stmt.setInt(1, item.getBookId());
			stmt.setString(2, item.getTitle());
			stmt.setInt(3, item.getSortOrder());
			stmt.setString(4, item.getIntro());
			stmt.setString(5, item.getContent());

		    int rowCount = stmt.executeUpdate();
		    
		    if (rowCount != 1)
		    	throw new ResourceDAOSysException("effected row count must be 1");


		} catch (Exception e) {
			AppLogger.debug("MSSqlCategoryDAO.createBookChapter().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 updateBookChapter(BookChapter item) throws ResourceDAOSysException {
		Connection conn = null;
		PreparedStatement stmt = null;

		String sql = "UPDATE TAB_BOOKCHAPTER SET Title = ?, SortOrder = ?, Intro = ?, Content = ? WHERE IndexID = ?";

		AppLogger.debug("MSSqlBookDAO.updateBookChapter().sql=" + sql);

		try {
			conn = super.getDBConnection();
			stmt = conn.prepareStatement(sql);

			stmt.setString(1, item.getTitle());
			stmt.setInt(2, item.getSortOrder());
			stmt.setString(3, item.getIntro());
			stmt.setString(4, item.getContent());
			stmt.setInt(5, item.getIndexId());

		    int rowCount = stmt.executeUpdate();
		    
		    if (rowCount != 1)
		    	throw new ResourceDAOSysException("effected row count must be 1");


		} catch (Exception e) {
			AppLogger.debug("MSSqlCategoryDAO.updateBookChapter().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 deleteBookChapter(int chapterId) throws ResourceDAOSysException {
		Connection conn = null;
		PreparedStatement stmt = null;

		String sql = "UPDATE TAB_BOOKCHAPTER SET Deleted = 1 WHERE IndexID = ?";

		AppLogger.debug("MSSqlBookDAO.deleteBookChapter().sql=" + sql);

		try {
			conn = super.getDBConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, chapterId);

		    int rowCount = stmt.executeUpdate();
		    
		    if (rowCount != 1)
		    	throw new ResourceDAOSysException("effected row count must be 1");


		} catch (Exception e) {
			AppLogger.debug("MSSqlCategoryDAO.deleteBookChapter().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 setBookChapterSortOrder(int chapterId, int sortOrder) throws ResourceDAOSysException {
		Connection conn = null;
		PreparedStatement stmt = null;

		String sql = "UPDATE TAB_BOOKCHAPTER SET SortOrder = ? WHERE (IndexID = ?)";
		
		try {
			conn = super.getDBConnection();
			stmt = conn.prepareStatement(sql);

			stmt.setInt(1, sortOrder);
			stmt.setInt(2, chapterId);
			
			int rowCount = stmt.executeUpdate();
		    if (rowCount != 1)
		    	throw new CatalogDAOSysException("invalid effected row count");

		} catch (Exception e) {
			AppLogger.debug("MSSqlBookDAO.setBookChapterSortOrder().e.getMessage()=" + e.getMessage());

			throw new CatalogDAOSysException(e.getMessage());
		} finally {
			try {
				super.closeStatement(stmt);
				super.closeConnection(conn);
			} catch (Exception e) {
				throw new CatalogDAOSysException(e.getMessage());
			}
		}
	}

	public Page getBookChapters(int bookId, int start, int count) throws ResourceDAOSysException {
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rst = null;
		
		Page page = new Page();
		// 因为content可能数据量太大，所以列表的情况下获取其中一部分
		String sql = "SELECT IndexID, BookID, Title, SortOrder, Intro, SUBSTRING(Content, 0, 250) AS Content FROM TAB_BOOKCHAPTER WHERE Deleted <> 1 AND BookID = ? ORDER BY SortOrder";
		AppLogger.debug("MSSqlBookDAO.getBookChapters().sql=" + sql);
		
		try {
			conn = super.getDBConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, bookId);

			rst = stmt.executeQuery();

        	int totalRecords = 0;
        	
        	// 获取所需要的数据
        	for (totalRecords = 0; rst.next(); totalRecords ++) {
        		if ((totalRecords >= start) && (totalRecords < (start + count))) {
        			BookChapter item = new BookChapter();

        			item.setIndexId(rst.getInt("IndexID"));
        			item.setBookId(rst.getInt("BookID"));
        			item.setTitle(rst.getString("Title"));
        			item.setSortOrder(rst.getInt("SortOrder"));
        			item.setIntro(rst.getString("Intro"));
        			item.setContent(rst.getString("Content"));
        			
        			page.getItems().add(item);
        		}
        	}
        	page.setTotalRecords(totalRecords);
		} catch (Exception e) {
			e.printStackTrace();
			AppLogger.debug("MSSqlCategoryDAO.getBookChapters().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());
			}
		}

		return page;
	}

	public BookChapter getBookChapter(int chapterId/*, int start, int count*/) throws ResourceDAOSysException {
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rst = null;
		
		BookChapter chapter = null;
		
		//String sql = "SELECT IndexID, BookID, Title, SortOrder, Intro, SUBSTRING(Content, ?, ?) AS Content "
		String sql = "SELECT IndexID, BookID, Title, SortOrder, Intro, Content "
			+ "FROM TAB_BOOKCHAPTER WHERE (IndexID = ?)";
		
		try {
			conn = super.getDBConnection();
			stmt = conn.prepareStatement(sql);

			//stmt.setInt(1, start);
			//stmt.setInt(2, count);
			//stmt.setInt(3, chapterId);
			stmt.setInt(1, chapterId);

			rst = stmt.executeQuery();
			
			if (rst.next()) {
    			chapter = new BookChapter();

    			chapter.setIndexId(rst.getInt("IndexID"));
    			chapter.setBookId(rst.getInt("BookID"));
    			chapter.setTitle(rst.getString("Title"));
    			chapter.setSortOrder(rst.getInt("SortOrder"));
    			chapter.setIntro(rst.getString("Intro"));
    			chapter.setContent(rst.getString("Content"));
			}
		} catch (Exception e) {
			e.printStackTrace();
			AppLogger.debug("MSSqlCategoryDAO.getBookChapter().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());
			}
		}

		return chapter;
	}
}