managerdao.java

北大青鸟ACCP5.0课程项目 青鸟论坛

/*
Author：青鸟学子
QQ：64578820
Msn：[e-mail]
*/
package com.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.entity.SectionInfo;

public class ManagerDAO {

	private BaseDAO dao = new BaseDAO();
	private ResultSet rs = null;
	private List<SectionInfo> cList = new ArrayList<SectionInfo>();// 已对象格式保存所有版块
	private List<String> sign = new ArrayList<String>();// 保存分割符号图片
	private List<String> allList = new ArrayList<String>();// 以字符串格式保存所有版块的名字
	/*
	 * 树形列表图片文件
	 */
	// 根节点图片
	private final String rootImage = "<img src=\"image/tplus.gif\" width=\"19\" height=\"20\" align=\"absmiddle\">"
			+ "<img src=\"image/folders.gif\" width=\"16\" height=\"16\" align=\"absmiddle\">";
	// 第一个根节点图片
	private final String firstRootImage = "<img src=\"image/rplus.gif\" width=\"19\" height=\"20\" align=\"absmiddle\">"
			+ "<img src=\"image/folders.gif\" width=\"16\" height=\"16\" align=\"absmiddle\">";
	// 最后一个根节点图片
	private final String lastRootImage = "<img src=\"image/lplus.gif\" width=\"19\" height=\"20\" align=\"absmiddle\">"
			+ "<img src=\"image/folders.gif\" width=\"16\" height=\"16\" align=\"absmiddle\">";

	// 子节点图片
	private final String leafImage = "<img src=\"image/tminus.gif\" width=\"19\" height=\"20\" align=\"absmiddle\">"
			+ "<img src=\"image/folder.gif\" width=\"16\" height=\"16\" align=\"absmiddle\">";
	// 最后一个子节点图片
	private final String lastLeafImage = "<img src=\"image/lminus.gif\" width=\"19\" height=\"20\" align=\"absmiddle\">"
			+ "<img src=\"image/folder.gif\" width=\"16\" height=\"16\" align=\"absmiddle\">";

	// 空白图片
	private final String noexpandImage = "<img src=\"image/noexpand.gif\" width=\"19\" height=\"20\" align=\"absmiddle\">";
	// 竖线图片
	private final String iImage = "<img src=\"image/i.gif\" width=\"19\" height=\"20\" align=\"absmiddle\">";
	// 小箭头图片
	private final String arrow = "<img src=\"image/cal_nextMonth.gif\" width=\"5\" height=\"9\">";

	/* ---------------四个按钮--------------------- */

	// 添加按钮
	private final String addBt = "<input onClick=\"javascript:window.location.href=\'addSection.jsp\'\" type=\"button\" value=\"  添加\" style=\"background-image:url(image/add.gif); background-repeat:no-repeat; width:50px; height:18px;\">";
	// 编辑按钮
	private final String sEditBt = "&nbsp;<input onClick=\"javascript:window.location.href=\'editSection.jsp?sid=";
	private final String eEditBt = "\'\" type=\"button\" value=\"  编辑\" style=\"background-image:url(image/submit.gif); background-repeat:no-repeat; width:50px; height:18px;\">";
	// 删除按钮
	private final String sDelBt = "&nbsp;<input onClick=\"javascript:if(confirm(\'删除该版块所有主题\\n是否要删除？\')){window.location.href=\'../ServletManager?action=del&sid=";
	private final String eDelBt = "\';}else{return false;}\" type=\"button\" value=\"  删除\" style=\"background-image:url(image/del.gif); background-repeat:no-repeat; width:50px; height:18px;\">";
	// 移动按钮
	private final String sMoveBt = "&nbsp;<input onClick=\"javascript:window.location.href=\'moveSection.jsp?sid=";
	private final String eMoveBt = "\'\" type=\"button\" value=\"  移动\" style=\"background-image:url(image/del.gif); background-repeat:no-repeat; width:50px; height:18px;\">";

	/**
	 * 根据id，获得它的父版块信息
	 * 
	 * @param id
	 *            版块编号
	 * @return SectionInfo 返回一个对象
	 */
	public SectionInfo getPSectionById(Integer id) {
		SectionInfo section = null;
		String sql = "select * from sectionInfo where sId = ?";
		try {
			rs = dao.executeQuery(sql, new Object[] { id });
			if (rs != null && rs.next()) {
				section = new SectionInfo();

				section.setSid(rs.getInt("sId"));
				section.setSmasterid(rs.getInt("sMasterId"));
				section.setSname(rs.getString("sName"));
				section.setSparentid(rs.getInt("sParentId"));
				section.setStopiccount(rs.getInt("sTopicCount"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			dao.closeResultSet();
			dao.closeStatement();
			dao.closeConnection();
		}
		return section;
	}

	/**
	 * 根据版块id，获得所有子版块
	 * 
	 * @param sId
	 *            版块编号
	 * @return List<SectionInfo> 返回一个集合
	 */
	public List<SectionInfo> getSectionById(Integer sId) {
		List<SectionInfo> list = new ArrayList<SectionInfo>();
		String sql = "select * from sectionInfo where sparentId = ?";
		try {
			rs = dao.executeQuery(sql, new Object[] { sId });
			while (rs != null && rs.next()) {
				SectionInfo section = new SectionInfo();

				section.setSid(rs.getInt("sId"));
				section.setSmasterid(rs.getInt("sMasterId"));
				section.setSname(rs.getString("sName"));
				section.setSparentid(rs.getInt("sParentId"));
				section.setStopiccount(rs.getInt("sTopicCount"));
				list.add(section);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			dao.closeResultSet();
			dao.closeStatement();
			dao.closeConnection();
		}
		return list;
	}

	/**
	 * 判断对象是否在所有同级版块中的最后一个
	 * 
	 * @param obj
	 *            要比较的SectionInfo对象
	 * @return Boolean 返回一个布尔型 true：是 false：否
	 */
	public Boolean isLastNode(SectionInfo obj) {
		SectionInfo section = null;
		String sql = "select top 1 * from sectionInfo where sparentId = ? order by sid desc";
		try {
			rs = dao.executeQuery(sql, new Object[] { obj.getSparentid() });
			if (rs != null && rs.next()) {
				section = new SectionInfo();

				section.setSid(rs.getInt("sId"));
				section.setSmasterid(rs.getInt("sMasterId"));
				section.setSname(rs.getString("sName"));
				section.setSparentid(rs.getInt("sParentId"));
				section.setStopiccount(rs.getInt("sTopicCount"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			dao.closeResultSet();
			dao.closeStatement();
			dao.closeConnection();
		}
		// 如果是最后一个节
		if (obj.getSid() == section.getSid()) {
			return true;
		}
		return false;
	}

	/**
	 * 根据id，递归获得根节点
	 * 
	 * @param id
	 */
	private void traverseRootNodeById(Integer id) {//
		// 带前导图片的
		SectionInfo parent = this.getPSectionById(id);// 获得当前节点的父节点
		if (parent != null && parent.getSparentid() == 0) {
			this.sign.add(iImage);// 添加竖线
		} else if (parent != null && parent.getSparentid() != 0) {// 如果是根节点，就跳出递归
			Integer result = this.getParentCount(parent.getSparentid());// 获得同级父节点的个数
			if (this.isLastNode(parent) || result == 1) {
				this.sign.add(noexpandImage);// 添加空格
			} else if (result > 1) {
				this.sign.add(iImage);// 添加竖线
			}

			id = parent.getSparentid();

			traverseRootNodeById(id);// 递归
		}
		return;
	}

	/**
	 * 根据id，递归获得空格符号
	 * 
	 * @param id
	 *            版块编号
	 * @param blank
	 *            HTML格式的空格符
	 * @return String 返回空格字符串
	 */
	public String traverseRootNodeById(Integer id, String blank) {//
		// 带前导&nbsp;的
		String str = "";
		SectionInfo parent = this.getPSectionById(id);// 获得当前节点的父节点
		if (parent != null && parent.getSparentid() == 0) {
			str += blank;// 添加竖线
		} else if (parent != null && parent.getSparentid() != 0) {
			str += blank;
			id = parent.getSparentid();
			return str + traverseRootNodeById(id, blank);// 递归
		}
		return str;
	}

	/**
	 * 根据id，获得同级节点的个数
	 * 
	 * @param id
	 *            版块编号
	 * @return Integer 返回一个整型
	 */
	private Integer getParentCount(Integer id) {
		String sql = "select count(*) as tt from sectionInfo where sparentId = ?";
		Integer result = 0;
		try {
			rs = dao.executeQuery(sql, new Object[] { id });
			if (rs != null && rs.next()) {
				result = rs.getInt("tt");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			dao.closeResultSet();
			dao.closeStatement();
			dao.closeConnection();
		}
		return result;
	}

	/**
	 * 根据根版块的结果集list，按层级关系获得所有子版块
	 * 
	 * @param listParent
	 *            父节点的结果集
	 * @return List<String> 返回一个集合
	 */
	public List<String> getAllSectionByList(List<SectionInfo> listParent) {
		// 带前导图片的
		int sParentId = -1;
		Boolean flag = false;// 为最后一个根节点做记号
		String image = "";
		String name = "";

		for (int i = 0; i < listParent.size(); i++) {// 获得根节点对象（父版块）

			sParentId = listParent.get(i).getSid();

			System.out.println("根节点：" + listParent.get(i).getSname());
			if (i == 0) {// 第一个根节点
				image = firstRootImage;
			} else if (i == listParent.size() - 1) {// 最后一个根节点
				flag = true;
				image = lastRootImage;
			} else {
				image = rootImage;
			}
			name = "<a href=\"../servletListPage?sid="
					+ listParent.get(i).getSid() + "\" target=\"_blank\">"
					+ listParent.get(i).getSname() + "</a>";
			allList
					.add("<div onMouseOver=\"this.style.backgroundColor=\'#7DDFFF\'\" onMouseOut=\"this.style.backgroundColor=\'#FFFFFF\'\">"
							+ image
							+ "&nbsp;"
							+ name
							+ "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"
							+ arrow
							+ addBt
							+ sEditBt
							+ listParent.get(i).getSid()
							+ eEditBt
							+ sDelBt
							+ listParent.get(i).getSid()
							+ eDelBt
							+ sMoveBt
							+ listParent.get(i).getSid() + eMoveBt + "</div>");// 块节点放入泛型
			this.getAllLeafById(sParentId, flag);// 获得当前根节点下的所有子节点

		}

		return allList;
	}

	/**
	 * 根据根版块的结果集list，按层级关系获得所有子版块对象
	 * 
	 * @param listParent
	 *            父节点的结果集
	 * @return List<SectionInfo> 返回一个结果集
	 */
	public List<SectionInfo> getAllSectionObjByList(List<SectionInfo> listParent) {
		// 带前导&nbsp;的
		int sParentId = -1;

		for (int i = 0; i < listParent.size(); i++) {// 获得根节点对象（父版块）

			sParentId = listParent.get(i).getSid();

			// System.out.println("根节点：" + listParent.get(i).getSname());