db.java

用于BBS交流的网站

			close(rs);
		}
			return result;	
	}
	
	/////////////////////////////////////////////////////manager use those methods as follows
	/////////////////////////////////////////////////////Please note those!!!!!!!!!!!!!!!!!!!
	/**
	 * 增加一个软件信息到数据库downloadtlb以供下载
	 * @param sofeware
	 */
	public boolean addSoftwareItem(Software software) {
		String sql = "insert into downloadtlb values(?,?,?,?,?)";
		PreparedStatement preStatement = createPreStatment(sql);
		try {
			conn.setAutoCommit(false);
			preStatement.setString(1, null);
			preStatement.setString(2, software.getSoftwareType());
			preStatement.setString(3, software.getSoftwareName());
			preStatement.setString(4, software.getSoftwarePath());
			preStatement.setInt(5, software.getSoftwareSize());
			preStatement.execute();
			conn.commit();
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			try {
				conn.setAutoCommit(true);
			} catch (SQLException e) {
				e.printStackTrace();
			}
			close(preStatement);
		}
		return true;
	}
	
	/**
	 * 在数据库downloadtlb中更新指定软件的信息
	 * @param software
	 * @return true成功更新，否则false
	 */
	public boolean updateSoftwareItem(Software software) {
		String sql = "update downloadtlb set softwareType=?,softwareName=?,softwarePath=?,softwareSize=? where softwareID=" + "'" + software.getSoftwareID() + "'";
		PreparedStatement preStatement = createPreStatment(sql);
		try {
			conn.setAutoCommit(false);
			preStatement.setString(1, software.getSoftwareType());
			preStatement.setString(2, software.getSoftwareName());
			preStatement.setString(3, software.getSoftwarePath());
			preStatement.setInt(4, software.getSoftwareSize());
			preStatement.executeUpdate();
			conn.commit();
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			try {
				conn.setAutoCommit(true);
			} catch (SQLException e) {
				e.printStackTrace();
			}
			close(preStatement);
		}
		return true;
	}
	
	
	/**
	 * 根据指定的信息在数据库downloadtlb种删除软件信息
	 * @param searchType
	 * @param softwareXXXX
	 * @return 删除的软件的数目
	 */
	public int removeSoftwareItems(String removeType, String softwareXXXX) {
		int changedItemsCount = 0;
		if (removeType.equals("softwareID")) {
			String sql = "delete from downloadtlb where softwareID=" + "'" + softwareXXXX + "'";
			changedItemsCount = doDelete(getStatement(), sql);
		} else if (removeType.equals("softwareType")) {
			String sql = "delete from downloadtlb where softwareType=" + "'" + softwareXXXX + "'";
			changedItemsCount = doDelete(getStatement(), sql);
		} else if (removeType.equals("softwareName")) {
			String sql = "delete from downloadtlb where softwareName=" + "'" + softwareXXXX + "'";
			changedItemsCount = doDelete(getStatement(), sql);
		} else {

		}
		return changedItemsCount;
	}
	
	/**
	 * 册除一个记录
	 * @param statement
	 * @param sql
	 * @return
	 */
	private int doDelete(Statement statement, String sql) {
		int changedItemsCount = 0;
		try {
			changedItemsCount = statement.executeUpdate(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return changedItemsCount;
	}

	/**
	 * 以指定方式查找软件信息
	 * @param searchType
	 * @param softwareXXXX （XXXX可以是ID、Type、Name）
	 * @return
	 */
	public String searchSoftwareItems(String searchType, String softwareXXXX) {
		String result = "";
		if (searchType.equals("softwareID")) {
			String sql = "select * from downloadtlb where softwareID=" + "'" + softwareXXXX + "'";
			result = getSoftwareItems(sql);
		} else if (searchType.equals("softwareType")) {
			String sql = "select * from downloadtlb where softwareType=" + "'" + softwareXXXX + "'";
			result = getSoftwareItems(sql);
		} else if (searchType.equals("softwareName")) {
			String sql = "select * from downloadtlb where softwareName=" + "'" + softwareXXXX + "'";
			result = getSoftwareItems(sql);
		} else {

		}
		return result;
	}
	

	/**
	 * 以指定的sql查找信息
	 * @param sql
	 * @return
	 */
	private String getSoftwareItems(String sql) {
		String result = "";
		ResultSet rs = getResultSet(getStatement(),sql);
		try {
			int count = 0;//用于计入节点数目
			while (rs.next()) {
				count ++;
				if (count%2 == 0) {
					result = result + "<tr bgcolor='#999999'>";
				} else {
					result = result + "<tr bgcolor='#666666'>";
				}
				result = result + "<td ><div align='center'>" + rs.getString("softwareID")+"</div></td>";
				result = result + "<td ><div align='center'>" + rs.getString("softwareType")+"</div></td>";
				result = result + "<td ><div align='center'>" + rs.getString("softwareName")+"</div></td>";
				result = result + "<td ><div align='center'>" + rs.getString("softwarePath")+"</div></td>";
				result = result + "<td ><div align='center'>" + rs.getInt("softwareSize")+"</div></td>";
				result = result + "</tr>";
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close(rs);
		}
		return result;
	}
///////////////////////////////////
///////////////////////////////////
////////////////////////////////////
	/**
	 * @param digitalSrc
	 * @return
	 */
	public boolean addDigitalSrcItem(DigitalSrc digitalSrc) {
		String sql = "insert into digitaltlb values(?,?,?,?,?,?)";
		PreparedStatement preStatement = createPreStatment(sql);
		try {
			conn.setAutoCommit(false);
			preStatement.setString(1, null);
			preStatement.setString(2, digitalSrc.getSrcType());
			preStatement.setString(3, digitalSrc.getDigitalType());
			preStatement.setString(4, digitalSrc.getDigitalName());
			preStatement.setString(5, digitalSrc.getDigitalPath());
			preStatement.setInt(6, digitalSrc.getDigitalSize());
			preStatement.execute();
			conn.commit();
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			try {
				conn.setAutoCommit(true);
			} catch (SQLException e) {
				e.printStackTrace();
			}
			close(preStatement);
		}
		return true;
	}

	/**
	 * @param digitalSrc
	 * @return
	 */
	public boolean updateDigitalSrcItem(DigitalSrc digitalSrc) {
		String sql = "update digitaltlb set srcType=?,digitalType=?,digitalName=?,digitalPath=?,digitalSize=? where digitalID=" + "'" + digitalSrc.getDigitalID() + "'";
		PreparedStatement preStatement = createPreStatment(sql);
		try {
			conn.setAutoCommit(false);
			preStatement.setString(1, digitalSrc.getSrcType());
			preStatement.setString(2, digitalSrc.getDigitalType());
			preStatement.setString(3, digitalSrc.getDigitalName());
			preStatement.setString(4, digitalSrc.getDigitalPath());
			preStatement.setInt(5, digitalSrc.getDigitalSize());
			preStatement.executeUpdate();
			conn.commit();
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			try {
				conn.setAutoCommit(true);
			} catch (SQLException e) {
				e.printStackTrace();
			}
			close(preStatement);
		}
		return true;
	}

	/**
	 * @param searchType
	 * @param digitalXXXX
	 * @return
	 */
	public String searchDigitalSrcItems(String searchType, String digitalXXXX) {
		String result = "";
		if (searchType.equals("digitalID")) {
			String sql = "select * from digitaltlb where digitalID=" + "'" + digitalXXXX + "'";
			result = getDigitalSrcItems(sql);
		} else if (searchType.equals("digitalType")) {
			String sql = "select * from digitaltlb where digitalType=" + "'" + digitalXXXX + "'";
			result = getDigitalSrcItems(sql);
		} else if (searchType.equals("digitalName")) {
			String sql = "select * from digitaltlb where digitalName=" + "'" + digitalXXXX + "'";
			result = getDigitalSrcItems(sql);
		} else {

		}
		return result;
	}

	/**
	 * @param sql
	 * @return
	 */
	private String getDigitalSrcItems(String sql) {
		String result = "";
		ResultSet rs = getResultSet(getStatement(),sql);
		try {
			int count = 0;//用于计入节点数目
			while (rs.next()) {
				count ++;
				if (count%2 == 0) {
					result = result + "<tr bgcolor='#999999'>";
				} else {
					result = result + "<tr bgcolor='#666666'>";
				}
				result = result + "<td ><div align='center'>" + rs.getString("digitalID")+"</div></td>";
				result = result + "<td ><div align='center'>" + rs.getString("srcType")+"</div></td>";
				result = result + "<td ><div align='center'>" + rs.getString("digitalType")+"</div></td>";
				result = result + "<td ><div align='center'>" + rs.getString("digitalName")+"</div></td>";
				result = result + "<td ><div align='center'>" + rs.getString("digitalPath")+"</div></td>";
				result = result + "<td ><div align='center'>" + rs.getInt("digitalSize")+"</div></td>";
				result = result + "</tr>";
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close(rs);
		}
		return result;
	}

	/**
	 * @param removeType
	 * @param digitalXXXX
	 * @return
	 */
	public int removeDigitalSrcItems(String removeType, String digitalXXXX) {
		int changedItemsCount = 0;
		if (removeType.equals("digitalID")) {
			String sql = "delete from digitaltlb where digitalID=" + "'" + digitalXXXX + "'";
			changedItemsCount = doDelete(getStatement(), sql);
		} else if (removeType.equals("digitalType")) {
			String sql = "delete from digitaltlb where digitalType=" + "'" + digitalXXXX + "'";
			changedItemsCount = doDelete(getStatement(), sql);
		} else if (removeType.equals("digitalName")) {
			String sql = "delete from digitaltlb where digitalName=" + "'" + digitalXXXX + "'";
			changedItemsCount = doDelete(getStatement(), sql);
		} else {

		}
		return changedItemsCount;
	}
	
////////////////////////////////////////
////////////////////////////////////////
////////////////////////////////////////
	public List<Article> getAllTopics() {
		return getAllTopic(new ArrayList<Article>(), 0, 0);
	}
	/**
	 * 获取所有主贴
	 * @param articles
	 * @param id
	 * @param grade
	 * @return
	 */
	public List<Article> getAllTopic(List<Article> articles, int id, int grade) {
		String sql = "select * from articletlb where pid=" + id;
		ResultSet rs = getResultSet(getStatement(), sql);
		try {
			while (rs.next()) {
				Article article = new Article();
				article.setId(rs.getInt("id"));
				article.setPid(rs.getInt("pid"));
				article.setRootid(rs.getLong("rootid"));
				article.setTitle(rs.getString("title"));
				article.setContent(rs.getString("content"));
				article.setPublishdate(rs.getDate("publishdate"));
				article.setLeaf(rs.getBoolean("leaf"));
				article.setUsername(rs.getString("username"));
				article.setGrade(grade);
				articles.add(article);//把一个帖子加入到容器中
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return articles;
	}
	
	
	
	/**
	 * 获取指定rootid的所有帖子
	 * @param rootid
	 * @return
	 */
	public List<Article> getOneTopics(long rootid) {
		return getOneTopic(new ArrayList<Article>(), 0, rootid, 0);
	}
	
	
	/**
	 * 获取一个主贴及其他的所有子贴
	 * @param articles
	 * @param id
	 * @param grade
	 * @return
	 */
	public List<Article> getOneTopic(List<Article> articles, int pid, long rootid, int grade) {
		String sql = "select * from articletlb where pid=" + pid + " and " + "rootid=" + rootid;
		ResultSet rs = getResultSet(getStatement(), sql);
		try {
			while (rs.next()) {
				Article article = new Article();
				article.setId(rs.getInt("id"));
				article.setPid(rs.getInt("pid"));
				article.setRootid(rs.getLong("rootid"));
				article.setTitle(rs.getString("title"));
				article.setContent(rs.getString("content"));
				article.setPublishdate(rs.getDate("publishdate"));
				article.setLeaf(rs.getBoolean("leaf"));
				article.setUsername(rs.getString("username"));
				article.setGrade(grade);
				articles.add(article);//把一个帖子加入到容器中
				if (!article.isLeaf()) {
					getOneTopic(articles, article.getId(), rootid, grade+1);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return articles;
	}
	
	/**
	 * 插入了个帖子到数据库articletlb
	 * @param article
	 */
	public void addArticle(Article article) {
		String sql = "insert into articletlb values(?,?,?,?,?,now(),?,?)";
		PreparedStatement preStatement = createPreStatment(sql);
		try {
			conn.setAutoCommit(false);
			preStatement.setString(1, null);
			preStatement.setInt(2, article.getPid());
			preStatement.setLong(3, article.getRootid());
			preStatement.setString(4, article.getTitle());
			preStatement.setString(5, article.getContent());

			preStatement.setBoolean(6, article.isLeaf());
			preStatement.setString(7, article.getUsername());
			preStatement.execute();
			conn.commit();
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			try {
				conn.setAutoCommit(true);
			} catch (SQLException e) {
				e.printStackTrace();
			}
			close(preStatement);
		}
	}
	
	/**
	 * 根据id更新帖子的状态
	 * @param id
	 * @return
	 */
	public int updatePArticle(int id) {
		String sql = "update articletlb set leaf=" + false + " where id=" + id;
		int changedCount = 0;
		changedCount = getUpdateArticle(getStatement(), sql);
		return changedCount;
	}
	
	public int getUpdateArticle(Statement statement, String sql) {
		int changedCount = 0;
		try {
			changedCount = statement.executeUpdate(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return changedCount;
	}

}