sqlbuilder.java~6~

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

package com.redmoon.forum;

import cn.js.fan.util.StrUtil;
import com.redmoon.forum.person.UserDb;

/**
 * <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(long rootid, String userId) {
        String sql;
        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 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 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 isBlog=1 ORDER BY lydate desc";
        return sql;
    }

    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;
    }

    public static String getUserByIP(String ip){
        String sql = "select name from sq_user where ip=" + StrUtil.sqlstr("" + ip) + " order by RegDate";
        return sql;
    }
}