bbsmsgmgr.java

是用java和jsp实现的bbs论坛系统。 有注册登陆模块

		ResultSet rs = null;
		try {
			// 创建数据记录集对象：
			stmt = conn.createStatement();

			// sql语句：
			String sql = "select * from bbsitems where item_id='" + id + "'";

			// 执行sql语句，返回一个记录集到rs：
			rs = stmt.executeQuery(sql);
			BBSMsg msg = null;
			if (rs.next()) {
				msg = new BBSMsg();
				msg.setAuthor(rs.getString("author"));
				msg.setBrowsed_times(rs.getInt("browsed_times"));
				msg.setFathers_id(rs.getInt("fathers_id"));
				msg.setIs_origional(rs.getInt("is_origional"));
				msg.setItem_content(rs.getString("item_content"));
				msg.setItem_id(rs.getInt("item_id"));
				msg.setItem_title(rs.getString("item_title"));
				msg
						.setLatest_replication_id(rs
								.getInt("latest_replication_id"));
				msg.setReplyed_times(rs.getInt("replyed_times"));
				msg.setStrCompose_date(rs.getString("compose_date"));
				msg.setStrModify_date(rs.getString("modify_date"));
				msg.setFace(rs.getInt("face"));
			}
			return msg;
		} catch (SQLException sqlExc) {
			sqlExc.printStackTrace();
			return null;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		} finally {
			// 关闭连接，释放数据库资源：
			try {
				if (rs != null) {
					rs.close();
				}
				if (stmt != null) {
					stmt.close();
				}
				connPool.freeConnection(conn);
			} catch (SQLException sqlExc) {
				sqlExc.printStackTrace();
			}
		}
	}

	public Collection getAllMessages(int pagesize, int page) {
		// 创建数据库连接对象：
		ConnectionPool connPool = DB.getConnPool();
		Connection conn = connPool.getConnection();
		Statement stmt = null;
		ResultSet rs = null;
		try {
			// 创建数据记录集对象：
			stmt = conn.createStatement();

			// sql语句：
			String sql = "select bbsitems.*,bbsitems1.item_id item_id1,bbsitems1.author replyer,"
					+ "bbsitems1.compose_date reply_latest_date from bbsitems "
					+ "inner join bbsitems bbsitems1 on bbsitems.item_id="
					+ "bbsitems1.fathers_id and bbsitems.latest_replication_id="
					+ "bbsitems1.item_id where bbsitems.is_origional = 1 "
					+ "order by item_id desc limit "
					+ (page - 1)
					* pagesize
					+ "," + pagesize;

			// 执行sql语句：
			// 执行sql语句，返回一个记录集到rs：
			rs = stmt.executeQuery(sql);
			Collection c = new ArrayList();
			BBSMsg msg = null;
			while (rs.next()) {
				msg = new BBSMsg();
				msg.setAuthor(rs.getString("author"));
				msg.setBrowsed_times(rs.getInt("browsed_times"));
				msg.setFathers_id(rs.getInt("fathers_id"));
				msg.setIs_origional(rs.getInt("is_origional"));
				msg.setItem_content(rs.getString("item_content"));
				int itemid = rs.getInt("item_id");
				msg.setItem_id(itemid);
				msg.setItem_title(rs.getString("item_title"));
				msg
						.setLatest_replication_id(rs
								.getInt("latest_replication_id"));
				msg.setReplyed_times(rs.getInt("replyed_times"));
				msg.setStrCompose_date(rs.getString("compose_date"));
				msg.setStrModify_date(rs.getString("modify_date"));
				if (rs.getInt("item_id") == rs.getInt("item_id1")) {
					msg.setLatest_replyer("");
					msg.setStrLatest_reply_date("");
				} else {
					msg.setLatest_replyer(rs.getString("replyer"));
					msg.setStrLatest_reply_date(rs
							.getString("reply_latest_date"));
				}
				msg.setLevel(0);
				c.add(msg);
				Collection temp = getMessages(getFamilyLevel(itemid, 0));
				if (temp != null) {
					c.addAll(temp);
				}
				msg.setFace(rs.getInt("face"));
				msg = null;
			}
			return c;
		} catch (SQLException sqlExc) {
			sqlExc.printStackTrace();
			return null;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		} finally {
			// 关闭连接，释放数据库资源：
			try {
				if (rs != null) {
					rs.close();
				}
				if (stmt != null) {
					stmt.close();
				}
				connPool.freeConnection(conn);
			} catch (SQLException sqlExc) {
				sqlExc.printStackTrace();
			}
		}
	}

	public boolean hasChildren(int id) {
		// 创建数据库连接对象：
		ConnectionPool connPool = DB.getConnPool();
		Connection conn = connPool.getConnection();
		Statement stmt = null;
		ResultSet rs = null;
		try {
			// 创建数据记录集对象：
			stmt = conn.createStatement();

			// sql语句：
			String sql = "select item_id from bbsitems where fathers_id='" + id
					+ "' and item_id!='" + id + "'";

			// 执行sql语句，返回一个记录集到rs：
			rs = stmt.executeQuery(sql);

			if (rs.next()) {
				return true;
			}
			return false;
		} catch (SQLException sqlExc) {
			sqlExc.printStackTrace();
			return false;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			// 关闭连接，释放数据库资源：
			try {
				if (rs != null) {
					rs.close();
				}
				if (stmt != null) {
					stmt.close();
				}
				connPool.freeConnection(conn);
			} catch (SQLException sqlExc) {
				sqlExc.printStackTrace();
			}
		}
	}

	public Collection getChildren(int id) {
		// 创建数据库连接对象：
		ConnectionPool connPool = DB.getConnPool();
		Connection conn = connPool.getConnection();
		Statement stmt = null;
		ResultSet rs = null;
		try {
			// 创建数据记录集对象：
			stmt = conn.createStatement();

			// sql语句：
			String sql = "select * from bbsitems where fathers_id='" + id
					+ "'or item_id='" + id + "' order by item_id asc";

			// 执行sql语句，返回一个记录集到rs：
			rs = stmt.executeQuery(sql);
			Collection c = new ArrayList();
			BBSMsg msg = null;
			while (rs.next()) {
				msg = new BBSMsg();
				msg.setAuthor(rs.getString("author"));
				msg.setBrowsed_times(rs.getInt("browsed_times"));
				msg.setFathers_id(rs.getInt("fathers_id"));
				msg.setIs_origional(rs.getInt("is_origional"));
				msg.setItem_content(rs.getString("item_content"));
				int itemid = rs.getInt("item_id");
				msg.setItem_id(itemid);
				msg.setItem_title(rs.getString("item_title"));
				msg
						.setLatest_replication_id(rs
								.getInt("latest_replication_id"));
				msg.setReplyed_times(rs.getInt("replyed_times"));
				msg.setStrCompose_date(rs.getString("compose_date"));
				msg.setStrModify_date(rs.getString("modify_date"));
				if (itemid==id) {
					msg.setLevel(0);
				}else if(hasChildren(itemid)){
					msg.setLevel(1);
				}else {
					msg.setLevel(2);
				}
				msg.setFace(rs.getInt("face"));
				c.add(msg);
				msg = null;
			}
			return c;
		} catch (SQLException sqlExc) {
			sqlExc.printStackTrace();
			return null;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		} finally {
			// 关闭连接，释放数据库资源：
			try {
				if (rs != null) {
					rs.close();
				}
				if (stmt != null) {
					stmt.close();
				}
				connPool.freeConnection(conn);
			} catch (SQLException sqlExc) {
				sqlExc.printStackTrace();
			}
		}
	}

	public int getCount() {
		// 创建数据库连接对象：
		ConnectionPool connPool = DB.getConnPool();
		Connection conn = connPool.getConnection();
		Statement stmt = null;
		ResultSet rs = null;
		try {
			// 创建数据记录集对象：
			stmt = conn.createStatement();

			// sql语句：
			String sql = "select count(item_id) from bbsitems where IS_ORIGIONAL=1";
			// 执行sql语句：
			// 执行sql语句，返回一个记录集到rs：
			rs = stmt.executeQuery(sql);
			if (rs.next()) {
				return rs.getInt(1);
			}
			return 0;
		} catch (SQLException sqlExc) {
			sqlExc.printStackTrace();
			return 0;
		} catch (Exception e) {
			e.printStackTrace();
			return 0;
		} finally {
			// 关闭连接，释放数据库资源：
			try {
				if (rs != null) {
					rs.close();
				}
				if (stmt != null) {
					stmt.close();
				}
				connPool.freeConnection(conn);
			} catch (SQLException sqlExc) {
				sqlExc.printStackTrace();
			}
		}
	}

	public Collection getMessages(Collection ids) {
		if ((ids == null) || (ids.size() == 0)) {
			return null;
		}
		// 创建数据库连接对象：
		ConnectionPool connPool = DB.getConnPool();
		Connection conn = connPool.getConnection();
		Statement stmt = null;
		ResultSet rs = null;
		try {
			// 创建数据记录集对象：
			stmt = conn.createStatement();
			Collection c = new ArrayList();
			BBSMsg msg = null;
			Iterator iterator = ids.iterator();
			while (iterator.hasNext()) {
				Integer[] array = (Integer[]) iterator.next();
				String sql = "select * from bbsitems where item_id='"
						+ array[0].intValue() + "'";
				// 执行sql语句，返回一个记录集到rs：
				rs = stmt.executeQuery(sql);
				msg = new BBSMsg();
				if (rs.next()) {
					msg.setLevel(array[1].intValue());
					msg.setAuthor(rs.getString("author"));
					msg.setBrowsed_times(rs.getInt("browsed_times"));
					msg.setFathers_id(rs.getInt("fathers_id"));
					msg.setIs_origional(rs.getInt("is_origional"));
					msg.setItem_content(rs.getString("item_content"));
					msg.setItem_id(rs.getInt("item_id"));
					msg.setItem_title(rs.getString("item_title"));
					msg.setLatest_replication_id(rs
							.getInt("latest_replication_id"));
					msg.setReplyed_times(rs.getInt("replyed_times"));
					msg.setStrCompose_date(rs.getString("compose_date"));
					msg.setStrModify_date(rs.getString("modify_date"));
					msg.setFace(rs.getInt("face"));
					c.add(msg);
				}
				msg = null;
			}
			return c;
		} catch (SQLException sqlExc) {
			sqlExc.printStackTrace();
			return null;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		} finally {
			// 关闭连接，释放数据库资源：
			try {
				if (rs != null) {
					rs.close();
				}
				if (stmt != null) {
					stmt.close();
				}
				connPool.freeConnection(conn);
			} catch (SQLException sqlExc) {
				sqlExc.printStackTrace();
			}
		}
	}

	public static void main(String args[]) {
		BBSMsgMgr mgr = new BBSMsgMgr();
		Collection c = mgr.getFamilyLevel(3, 0);
		if (c != null) {
			Iterator iterator = c.iterator();
			while (iterator.hasNext()) {
				Integer[] array = (Integer[]) iterator.next();
				System.out.println(array[0] + "," + array[1]);
			}
		}
	}
}