sqlbuilder.java~14~

云网论坛CWBBS 源码,内容丰富,学习,参考,教学的好资料,具体见内说明,

package com.redmoon.forum;

import cn.js.fan.util.StrUtil;
import com.redmoon.forum.person.UserDb;
import javax.servlet.http.HttpServletRequest;
import cn.js.fan.util.ParamUtil;
import com.redmoon.forum.plugin.PluginMgr;
import com.redmoon.forum.plugin.PluginUnit;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.jsp.JspWriter;
import com.redmoon.forum.plugin.base.IPluginUI;
import com.redmoon.forum.plugin.base.IPluginViewShowMsg;

/**
 * <p>Title: </p>
 *
 * <p>Description: </p>
 *
 * <p>Copyright: Copyright (c) 2005</p>
 *
 * <p>Company: </p>
 *
 * @author not attributable
 * @version 1.0
 */
public class SQLBuilder {
    public SQLBuilder() {
    }

    public static String getShowtopictreeSql(long rootid) {
        String sql = "select id from sq_message where rootid=" + rootid + " and check_status=" + MsgDb.CHECK_STATUS_PASS + " ORDER BY orders";
        return sql;
    }

    public static String getShowtopicSql(HttpServletRequest request,
                                         HttpServletResponse response,
                                         JspWriter out, MsgDb rootMsgDb,
                                         String userId) {
        String sql;
        String pluginCode = ParamUtil.get(request, "pluginCode");
        if (!pluginCode.equals("")) {
            PluginMgr pm = new PluginMgr();
            PluginUnit pu = pm.getPluginUnit(pluginCode);
            IPluginUI ipu = pu.getUI(request, response, out);
            IPluginViewShowMsg pv = ipu.getViewShowMsg(rootMsgDb.getboardcode(),
                    rootMsgDb);
            sql = pv.getShowtopicSql(request, rootMsgDb, userId);
            if (!sql.equals(""))
                return sql;
        }

        long rootid = rootMsgDb.getId();
        if (userId.equals(""))
            sql = "select id from sq_message where rootid=" + rootid +
                  " and check_status=" + MsgDb.CHECK_STATUS_PASS +
                  " ORDER BY lydate asc"; // orders"; 这样会使得顺序上不按时间，平板式时会让人觉得奇怪
        else {
            sql = "select id from sq_message where rootid=" + rootid +
                  " and check_status=" + MsgDb.CHECK_STATUS_PASS +
                  " and name=" + StrUtil.sqlstr(userId) +
                  " ORDER BY lydate asc";
        }

        return sql;
    }

    public static String getNewMsgOfBlog(String userName) {
        return "select id from sq_thread where name=" + StrUtil.sqlstr(userName) + " and check_status=" + MsgDb.CHECK_STATUS_PASS + " and isBlog=1 ORDER BY lydate desc";
    }

    public static String getMyblogSql(String blogUserDir,
                                      String userName) {
        String sql = "";
        if (blogUserDir.equals(""))
            // sql = "select id from sq_message where replyid=-1 and name=" + StrUtil.sqlstr(userName) + " and isBlog=1 ORDER BY lydate desc";
            sql = "select id from sq_thread where name=" +
                  StrUtil.sqlstr(userName) +
                  " and check_status=" + MsgDb.CHECK_STATUS_PASS + " and isBlog=1 ORDER BY lydate desc";
        else
            // sql = "select id from sq_message where replyid=-1 and name=" + StrUtil.sqlstr(userName) + " and blogUserDir=" + StrUtil.sqlstr(blogUserDir) + " and isBlog=1 ORDER BY lydate desc";
            sql = "select id from sq_thread where name=" +
                  StrUtil.sqlstr(userName) + " and blogUserDir=" +
                  StrUtil.sqlstr(blogUserDir) +
                  " and check_status=" + MsgDb.CHECK_STATUS_PASS + " and isBlog=1 ORDER BY lydate desc";
        return sql;
    }

    /**
     * 用于blog/user/listtopic.jsp页面
     * @return String
     */
    public static String getListtopicSqlOfBlog(String userName, String blogUserDir) {
        String sql;
        if (blogUserDir.equals(""))
                sql = "select id from sq_thread where name=" + StrUtil.sqlstr(userName) + " and check_status=" + MsgDb.CHECK_STATUS_PASS + " and isBlog=1 ORDER BY lydate desc";
        else
                sql = "select id from sq_thread where name=" + StrUtil.sqlstr(userName) + " and blogUserDir=" + StrUtil.sqlstr(blogUserDir) + " and check_status=" + MsgDb.CHECK_STATUS_PASS + " and isBlog=1 ORDER BY lydate desc";
        return sql;
    }

    public static String getNewReplySqlOfBlog(String userName, String blogUserDir) {
        return "select id from sq_message where replyRootName=" + StrUtil.sqlstr(userName) + " and check_status=" + MsgDb.CHECK_STATUS_PASS + " and isBlog=1 order by lydate desc";
    }

    public static String getListtopicSql(String boardcode, String op, String timelimit, int threadType) {
        String sql = "";
        if (timelimit.equals("all")) {
            if (op.equals("showelite")) {
                if (threadType != ThreadTypeDb.THREAD_TYPE_NONE) {
                    sql = "select id from sq_thread where boardcode=" +
                          StrUtil.sqlstr(boardcode) +
                          " and check_status=" + MsgDb.CHECK_STATUS_PASS +
                          " and thread_type=" + threadType +
                          " and iselite=1 ORDER BY msg_level desc,redate desc";
                } else {
                    sql = "select id from sq_thread where boardcode=" +
                          StrUtil.sqlstr(boardcode) +
                          " and check_status=" + MsgDb.CHECK_STATUS_PASS +
                          " and iselite=1 ORDER BY msg_level desc,redate desc";
                }
            }
            else {
                if (threadType != ThreadTypeDb.THREAD_TYPE_NONE) {
                    sql = "select id from sq_thread where boardcode=" +
                          StrUtil.sqlstr(boardcode) + " and check_status=" +
                          MsgDb.CHECK_STATUS_PASS + " and thread_type=" +
                          threadType + " and msg_level<=" +
                          MsgDb.LEVEL_TOP_BOARD +
                          " ORDER BY msg_level desc,redate desc";
                }
                else {
                    sql = "select id from sq_thread where boardcode=" +
                          StrUtil.sqlstr(boardcode) + " and check_status=" +
                          MsgDb.CHECK_STATUS_PASS + " and msg_level<=" +
                          MsgDb.LEVEL_TOP_BOARD +
                          " ORDER BY msg_level desc,redate desc";
                }
            }
        } else {
            /*
            if (op.equals("showelite"))
                sql = "select id from sq_thread where boardcode=" +
                      StrUtil.sqlstr(boardcode) + "and msg_level<=" +
                      MsgDb.LEVEL_TOP_BOARD +
                      " and TO_DAYS(NOW()) - TO_DAYS(lydate) <=" + timelimit +
                      " and iselite=1 ORDER BY msg_level desc,redate desc";
            else
                sql = "select id from sq_thread where boardcode=" +
                      StrUtil.sqlstr(boardcode) + "and msg_level<=" +
                      MsgDb.LEVEL_TOP_BOARD +
                      " and TO_DAYS(NOW()) - TO_DAYS(lydate) <=" + timelimit +
                      " ORDER BY msg_level desc,redate desc";
             */
            long cur = System.currentTimeMillis();
            long dlt = Long.parseLong(timelimit)*24*60*60000;
            long afterDay = cur - dlt;

            if (op.equals("showelite")) {
                if (threadType != ThreadTypeDb.THREAD_TYPE_NONE) {
                    sql = "select id from sq_thread where boardcode=" +
                          StrUtil.sqlstr(boardcode) + " and check_status=" +
                          MsgDb.CHECK_STATUS_PASS + " and thread_type=" + threadType +
                          " and msg_level<=" +
                          MsgDb.LEVEL_TOP_BOARD +
                          " and lydate>" + StrUtil.sqlstr("" + afterDay) +
                          " and iselite=1 ORDER BY msg_level desc,redate desc";
                }
                else {
                    sql = "select id from sq_thread where boardcode=" +
                          StrUtil.sqlstr(boardcode) + " and check_status=" +
                          MsgDb.CHECK_STATUS_PASS + " and msg_level<=" +
                          MsgDb.LEVEL_TOP_BOARD +
                          " and lydate>" + StrUtil.sqlstr("" + afterDay) +
                          " and iselite=1 ORDER BY msg_level desc,redate desc";
                }
            }
            else {
                if (threadType != ThreadTypeDb.THREAD_TYPE_NONE) {
                    sql = "select id from sq_thread where boardcode=" +
                          StrUtil.sqlstr(boardcode) + " and check_status=" +
                          MsgDb.CHECK_STATUS_PASS + " and thread_type=" +
                          threadType + " and msg_level<=" +
                          MsgDb.LEVEL_TOP_BOARD +
                          " and lydate>" + StrUtil.sqlstr("" + afterDay) +
                          " ORDER BY msg_level desc,redate desc";
                }
                else {
                    sql = "select id from sq_thread where boardcode=" +
                          StrUtil.sqlstr(boardcode) + " and check_status=" +
                          MsgDb.CHECK_STATUS_PASS + " and msg_level<=" +
                          MsgDb.LEVEL_TOP_BOARD +
                          " and lydate>" + StrUtil.sqlstr("" + afterDay) +
                          " ORDER BY msg_level desc,redate desc";
                }
            }
        }
        return sql;
    }

    public static String getRankExperience(){
        String sql = "select name from sq_user order by experience desc";
        return sql;
    }

    public static String getRankCredit() {
        String sql = "select name from sq_user order by credit desc";
        return sql;
    }

    public static String getRankGold() {
        String sql = "select name from sq_user order by gold desc";
        return sql;
    }

    public static String getRankAddCount() {
        String sql = "select name from sq_user order by addCount desc";
        return sql;
    }

}