forumsearch.java

BBS论坛设计JSP＋MYSQL

package com.bcxy.bbs.forum;

/**
 * Title:
 * Description:
 * Copyright:
 * Company: www.liyunet.com
 * 
 * @author lishujiang	
 * @version 1.0
 */
import java.sql.ResultSet;
import java.util.Vector;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.bcxy.bbs.database.DBConnect;
import com.bcxy.bbs.util.ParamUtil;
import com.bcxy.bbs.util.StringUtils;

public class ForumSearch {
	int searchTopicNum = 0;

	String orderName = "";

	public ForumSearch() {
	}

	public int getSearchTopicNum() {
		return searchTopicNum;
	}

	public String getOrderName() {
		return orderName;
	}

	public Vector getResult(HttpServletRequest request,
			HttpServletResponse response) throws Exception {
		Vector searchResult = new Vector();
		DBConnect dbc = null;
		try {
			int forumID = ParamUtil.getInt(request, "forumID", 0);
			int sType = ParamUtil.getInt(request, "sType");

			int pSearch = ParamUtil.getInt(request, "pSearch", 0);
			int nSearch = ParamUtil.getInt(request, "nSearch", 0);
			String keyword = ParamUtil.getString(request, "keyword", "");
			String searchForum = "";
			if (sType < 1 || sType > 3)
				throw new Exception("<li>对不起,请您选择搜索的类型");
			String searchDate = "";
			if (sType < 3) {
				if (keyword == null || "".equals(keyword)) {
					throw new Exception("<li>对不起，请您输入搜索得关键字");
				}
				if (forumID != 0)
					searchForum = " b.boardid=" + forumID + " and ";
				searchDate = ParamUtil.getString(request, "SearchDate");
				if (searchDate == null || "".equals(searchDate))
					throw new Exception("<li>请您选择搜索的日期");
			}

			int searchDateLimit = 30;
			String searchDay = "";
			if ("ALL".equals(searchDate)) {
				// searchDay = " date_add(dateandtime,interval " +
				// searchDateLimit
				// + " DAY) > now() and ";
			} else {
				searchDay = "  date_add(dateandtime,interval "
						+ ParamUtil.getInt(request, "SearchDate", 0)
						+ " DAY) > now()  and ";
			}

			String guestlist = "";
			String sql = "";
			dbc = new DBConnect(2, 0);

			switch (sType) {
			case 1:
				switch (nSearch) {
				// 搜索主题帖子作者
				case 1:
					sql = "select b.locktopic,b.boardid,b.rootid,b.announceid,b.body,b.Expression,b.topic,b.username,b.child,b.hits,b.dateandtime,board.lockboard from bbs1 b inner join board on b.boardid=board.boardid where b.username=? and  "
							+ searchForum
							+ " "
							+ searchDay
							+ " b.parentid=0 ORDER BY b.announceID desc";
					orderName = "搜索主题作者帖子";

					dbc.prepareStatement(sql);
					dbc.setString(1, keyword);
					break;
				// '搜索回复帖子作者
				case 2:
					sql = "select b.locktopic,b.boardid,b.rootid,b.announceid,b.body,b.Expression,b.topic,b.username,b.child,b.hits,b.dateandtime,board.lockboard from bbs1 b inner join board on b.boardid=board.boardid where b.username=? and "
							+ guestlist
							+ " "
							+ searchForum
							+ " "
							+ searchDay
							+ " b.parentid>0 ORDER BY b.announceID desc";
					orderName = "搜索回复作者帖子";
					dbc.prepareStatement(sql);
					dbc.setString(1, keyword);

					break;
				// '两者都搜索
				case 3:
					sql = "select b.locktopic,b.boardid,b.rootid,b.announceid,b.body,b.Expression,b.topic,b.username,b.child,b.hits,b.dateandtime,board.lockboard from bbs1 b inner join board on b.boardid=board.boardid where "
							+ guestlist
							+ " "
							+ searchForum
							+ " "
							+ searchDay
							+ " b.username=? ORDER BY b.announceID desc";
					orderName = "搜索主题和回复作者帖子";
					dbc.prepareStatement(sql);
					dbc.setString(1, keyword);

					break;
				}
				break;
			case 2:
				switch (pSearch) {
				// '搜索主题关键字
				case 1:
					sql = "select b.locktopic,b.boardid,b.rootid,b.announceid,b.body,b.Expression,b.topic,b.username,b.child,b.hits,b.dateandtime,board.lockboard from bbs1 b inner join board on b.boardid=board.boardid where  "
							+ guestlist
							+ " "
							+ searchForum
							+ " "
							+ searchDay
							+ " ( topic like ? ) ORDER BY b.announceID desc";
					// '搜索内容关键字
					orderName = "搜索主题";
					dbc.prepareStatement(sql);
					dbc.setString(1, ("%" + keyword + "%"));

					break;
				case 2:
					sql = "select b.locktopic,b.boardid,b.rootid,b.announceid,b.body,b.Expression,b.topic,b.username,b.child,b.hits,b.dateandtime,board.lockboard from bbs1 b inner join board on b.boardid=board.boardid where  "
							+ guestlist
							+ " "
							+ searchForum
							+ " "
							+ searchDay
							+ " ("
							+ StringUtils.getTranslateStr(keyword, "body")
							+ ") ORDER BY b.announceID desc";
					// '两者都搜索
					orderName = "搜索内容";
					dbc.prepareStatement(sql);
					// dbc.setString(1,keyword);

					break;
				case 3:
					sql = "select b.locktopic,b.boardid,b.rootid,b.announceid,b.body,b.Expression,b.topic,b.username,b.child,b.hits,b.dateandtime,board.lockboard from bbs1 b inner join board on b.boardid=board.boardid where  "
							+ guestlist
							+ " "
							+ searchForum
							+ " "
							+ searchDay
							+ " ("
							+ StringUtils.getTranslateStr(keyword, "topic")
							+ " or "
							+ StringUtils.getTranslateStr(keyword, "body")
							+ ") ORDER BY b.announceID desc";
					orderName = "搜索主题和内容";
					dbc.prepareStatement(sql);
					// dbc.setString(1,keyword);

					break;
				}
				break;
			case 3:
				sql = "select   b.locktopic,b.boardid,b.rootid,b.announceid,b.body,b.Expression,b.topic,b.username,b.child,b.hits,b.dateandtime,board.lockboard from bbs1 b,board where "
						+ guestlist
						+ " b.boardid=board.boardid ORDER BY b.announceID desc limit 0,50";
				orderName = "最新50帖";
				dbc.prepareStatement(sql);
				// dbc.setString(1,keyword);

				break;
			}

			if (sql.equals(""))
				throw new Exception("<li>对不起，sql语句出错！");

			int perPage = ParamUtil.getInt(request, "perPage", 10);
			int Page = ParamUtil.getInt(request, "Page", 1);
			int startPage = ((Page - 1) / 10) * 10 + 1;
			int start = (Page - 1) * perPage + 1;

			ResultSet rs = dbc.executeQuery();
			if (rs.last())
				searchTopicNum = rs.getRow();
			if (start > searchTopicNum)
				return searchResult;
			rs.absolute(start);
			int i = 0;

			do {

				ForumTopic theTopic = new ForumTopic();

				theTopic.setLockTopic(rs.getInt(1));
				theTopic.setForumID(rs.getInt(2));
				theTopic.setRootID(rs.getInt(3));
				theTopic.setAnnounceID(rs.getInt(4));
				theTopic.setBody(rs.getString(5));
				theTopic.setExpression(rs.getString(6));
				theTopic.setTopic(rs.getString(7));
				theTopic.setUserName(rs.getString(8));
				theTopic.setChildNum(rs.getInt(9));
				theTopic.setHits(rs.getInt(10));
				theTopic.setDateAndTime(rs.getString(11));

				searchResult.add(theTopic);
				i++;
				if (i == perPage)
					break;
			} while (rs.next());
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception("查询数据库出错，请检查查询条件！");
		} finally {
			if (dbc != null)
				dbc.close();
		}
		return searchResult;
	}

}