sqladapter.java

论坛系统EasyJForum 是一个基于 Java 技术的免费社区论坛软件系统

    
    public String Board_MoveTopics = 
        "update ejf_topic set sectionID=?,boardID=? where boardID=?";
    
    public String Board_MoveTrashs = 
        "update ejf_trash_box set boardID=? where boardID=?";
    
    // Topic
    public String Topic_Insert = 
        "insert into ejf_topic(sectionID,boardID,userID,nickname,remoteIP,title,content," +
        "reward,isReplyNotice,isHidePost,attaches,attachIcon,lastPostUser,lastNickname,topScope," +
        "lastPostTime,createTime) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,NOW(),NOW())";
    
    public String Topic_Update = 
        "update ejf_topic set reward=?,title=?,content=?,isReplyNotice=?,isHidePost=?," +
        "attaches=?,attachIcon=?,topScope=?,updateUser=?,updateTime=NOW() where topicID = ?";
    
    public String Topic_ModRemoteIP = 
        "update ejf_topic set remoteIP=?,nickname=? where topicID = ?";
    
    public String Topic_ModTopScope = 
        "update ejf_topic set topScope=?,topExpireDate=? where topicID = ?";

    public String Topic_ModIsDigest = 
        "update ejf_topic set isDigest=? where topicID = ?";
    
    public String Topic_ModState = 
        "update ejf_topic set state=? where topicID = ?";

    public String Topic_ModSection = 
        "update ejf_topic set sectionID=? where boardID=?";
    
    public String Topic_Delete = 
        "update ejf_topic set state='R' where topicID = ?";

    public String Topic_Remove = 
        "delete from ejf_topic where topicID = ?";

    public String Topic_Archive = 
        "insert into ejf_archive_topic select * from ejf_topic where topicID = ?";
    
    public String Topic_GetList = 
        "select sectionID,boardID,topicID,userID,nickname,title,reward,visits,replies," +
        "isDigest,isHidePost,isSolved,attachIcon,topScope,highColor," +
        "lastPostUser,lastNickname,lastPostTime,createTime,state " + 
        "from ejf_topic where ((boardID = ?) " +
        "or (topScope = '2' and boardID <> ? and sectionID = ?) " +
        "or (topScope = '1' and boardID <> ?)) and state <> 'R'";

    public String Topic_GetCount = 
        "select COUNT(*) from ejf_topic where ((boardID = ?) " +
        "or (topScope = '2' and boardID <> ? and sectionID = ?) " +
        "or (topScope = '1' and boardID <> ?)) and state <> 'R'";

    public String Topic_GetManageInfo = 
        "select isDigest,topScope,topExpireDate,highColor,highExpireDate,state " + 
        "from ejf_topic where topicID = ?";
    
    public String Topic_Index = 
        "select topicID,boardID,userID,nickname,title,content " + 
        "from ejf_topic where state <> 'R'";
    
    public String Topic_Search = 
        "select sectionID,boardID,topicID,userID,nickname,title,reward,visits,replies," +
        "isDigest,isHidePost,isSolved,attachIcon,createTime,state " + 
        "from ejf_topic where state <> 'R' and topicID in";

    public String Topic_GetListBySpec = 
        "select sectionID,boardID,topicID,userID,nickname,title,reward,visits,replies," +
        "topScope,isDigest,isHidePost,isSolved,attachIcon,lastPostTime,createTime,state " + 
        "from ejf_topic where state <> 'R'";

    public String Topic_GetFeedListBySpec = 
        "select sectionID,boardID,topicID,userID,nickname,title,reward,visits,replies," +
        "content,topScope,isDigest,isHidePost,attachIcon,lastPostTime,createTime,state " + 
        "from ejf_topic where state <> 'R'";
    
    public String Topic_GetCountBySpec = 
        "select COUNT(*) from ejf_topic where state <> 'R'";
    
    public String Topic_GetListByUser = 
        "select sectionID,boardID,topicID,userID,nickname,title,reward,visits,replies," +
        "isDigest,isSolved,attachIcon,lastPostUser,lastNickname,lastPostTime,createTime,state " + 
        "from ejf_topic where userID = ?";

    public String Topic_GetCountByUser = 
        "select COUNT(*) from ejf_topic where userID = ?";
    
    public String Topic_Select = 
        "select a.sectionID,a.boardID,a.title,a.content,a.replies,a.reward," +
        "a.isReplyNotice,a.isHidePost,a.isDigest,a.attaches," +
        "a.topScope,a.createTime,a.state,a.updateUser,a.updateTime,a.remoteIP," +
        "a.userID,b.nickname,b.avatar,b.posts,b.credits,b.groupID " + 
        "from ejf_topic a LEFT JOIN ejf_user b on a.userID = b.userID " +
        "where a.topicID = ?";

    public String Topic_GetLogInfo = 
        "select topicID,boardID,title,userID,attaches from ejf_topic where topicID in"; // (?)

    public String Topic_MoveTo = 
        "update ejf_topic set sectionID=?,boardID=? where topicID=?";

    public String Topic_Highlight = 
        "update ejf_topic set highColor=?,highExpireDate=? " +
        "where topicID=?";
    
    public String Topic_CheckHighExpireDate = 
        "update ejf_topic set highColor='' where highExpireDate < ?";

    public String Topic_CheckTopExpireDate = 
        "update ejf_topic set topScope='N' where topExpireDate < ?";
    
    public String Topic_CheckReplies = 
        "update ejf_topic set state='C' where state = 'N' and replies >= ?";

    public String Topic_GetTitle = 
        "select sectionID,boardID,title,userID,replies,reward,isReplyNotice," +
        "isDigest,topScope,state from ejf_topic where topicID = ?";
    
    public String Topic_ModLastPost = 
        "update ejf_topic set replies=replies+1,lastPostUser=?,lastNickname=?," +
        "lastPostTime=NOW() where topicID = ?";

    public String Topic_IncVisits = 
        "update ejf_topic set visits=visits+1 where topicID = ?";
    
    public String Topic_StatAllReplies = 
        "update ejf_topic set replies=(select COUNT(*) from ejf_reply " +
        "where ejf_reply.state <> 'R' and ejf_reply.topicID = ejf_topic.topicID)";
    
    public String Topic_StatReplies = 
        "select COUNT(replyID) as replies,MAX(replyID) as lastReplyID " +
        "from ejf_reply where topicID = ? and state <> 'R'";
    
    public String Topic_GetLastPost = 
        "select a.userID,b.nickname,a.createTime from ejf_reply a LEFT JOIN ejf_user b " +
        "on a.userID = b.userID where a.replyID = ?";
    
    public String Topic_ModReplies = 
        "update ejf_topic set replies=?,lastPostUser=?,lastNickname=?,lastPostTime=? " +
        "where topicID = ?";

    public String Topic_ResetReplies = 
        "update ejf_topic set replies=0,lastPostUser=userID,lastNickname=nickname,lastPostTime=createTime " +
        "where topicID = ?";
    
    public String Topic_SetIsSolved = 
        "update ejf_topic set isSolved=? where topicID = ?";
    
    public String Topic_RemoveByUser = 
        "update ejf_topic set state='R' where userID = ?";
    
    public String Topic_Query = 
        "select sectionID,boardID,topicID,title,userID,replies,visits,attaches," +
        "topScope,isDigest,highColor,state,createTime from ejf_topic";

    // Reply
    public String Reply_Insert = 
        "insert into ejf_reply(topicID,userID,remoteIP,title,content,isHidePost," +
        "attaches,createTime) values(?,?,?,?,?,?,?,NOW())";

    public String Reply_Update = 
        "update ejf_reply set title=?,content=?,isHidePost=?,attaches=?," +
        "updateTime=NOW() where replyID = ?";
    
//    public String Reply_GetCount = 
//        "select COUNT(*) from ejf_reply where topicID = ? and state <> 'R'";

    public String Reply_GetList = 
        "select a.replyID,a.title,a.content,a.isHidePost,a.attaches,a.isBest,a.state," +
        "a.createTime,a.remoteIP,a.userID,b.nickname,b.avatar,b.posts,b.credits,b.groupID " + 
        "from ejf_reply a LEFT JOIN ejf_user b on a.userID = b.userID " +
        "where a.topicID = ? and a.state <> 'R' order by a.isBest DESC, a.replyID ASC";

    public String Reply_GetAllCount = 
        "select COUNT(*) from ejf_reply where topicID = ?";

    public String Reply_GetAllList = 
        "select a.replyID,a.title,a.content,a.isHidePost,a.attaches,a.isBest,a.state," +
        "a.createTime,a.remoteIP,a.userID,b.nickname,b.avatar,b.posts,b.credits,b.groupID " + 
        "from ejf_reply a LEFT JOIN ejf_user b on a.userID = b.userID " +
        "where a.topicID = ? order by a.isBest DESC, a.replyID ASC";
    
    public String Reply_GetSeqno = 
        "select COUNT(*) from ejf_reply where topicID = ? and state <> 'R' " +
        "and (replyID < ? or isBest='T')";

    public String Reply_GetAllSeqno = 
        "select COUNT(*) from ejf_reply where topicID = ? " +
        "and (replyID < ? or isBest='T')";
    
    public String Reply_Index = 
        "select a.topicID,a.boardID,b.replyID,b.userID,b.title,b.content " + 
        "from ejf_topic a, ejf_reply b " +
        "where a.topicID = b.topicID and a.state <> 'R' and b.state <> 'R'";
    
    public String Reply_GetListByUser = 
        "select a.sectionID,a.boardID,a.topicID,a.title,b.replyID,b.userID," + 
        "b.state,b.createTime from ejf_topic a, ejf_reply b " +
        "where a.topicID = b.topicID and a.state <> 'R' and b.userID = ?";

    public String Reply_GetCountByUser = 
        "select COUNT(*) from ejf_topic a, ejf_reply b " + 
        "where a.topicID = b.topicID and a.state <> 'R' and b.userID = ?";
    
    public String Reply_Select = 
        "select * from ejf_reply where replyID = ?";

    public String Reply_GetLogInfo = 
        "select a.topicID,a.boardID,a.title,a.reward,b.attaches,b.userID,b.isBest " + 
        "from ejf_topic a, ejf_reply b where a.topicID = b.topicID and b.replyID = ?";

    public String Reply_Delete = 
        "update ejf_reply set state='R' where replyID = ?";

    public String Reply_Remove = 
        "delete from ejf_reply where replyID = ?";

    public String Reply_Archive = 
        "insert into ejf_archive_reply select * from ejf_reply where replyID = ?";
    
    public String Reply_RemoveByUser = 
        "update ejf_reply set state='R' where userID = ?";
    
    public String Reply_SetIsBest = 
        "update ejf_reply set isBest=? where replyID = ?";

    public String Reply_ModState = 
        "update ejf_reply set state=? where replyID = ?";
    
    public String Reply_ModRemoteIP = 
        "update ejf_reply set remoteIP=? where replyID = ?";
    
    public String Reply_Query = 
        "select b.sectionID,b.boardID,b.topicID,b.title,b.createTime," +
        "a.replyID,a.userID,a.attaches,a.createTime as replyTime " +
        "from ejf_reply a, ejf_topic b";
    
    // Attachment
    public String Attach_Insert = 
        "insert into ejf_attach(topicID,replyID,userID,localname,localID,filename," +
        "filesize,credits,title,state,createTime) values(?,?,?,?,?,?,?,?,?,?,NOW())";

    public String Attach_Update = 
        "update ejf_attach set credits=?,title=?,state=?,updateTime=NOW() " +
        "where attachID = ?";
    
    public String Attach_RemoveByPost = 
        "update ejf_attach set state = 'R' where topicID = ? and replyID = ?";

    public String Attach_RemoveByUser = 
        "update ejf_attach set state = 'R' where userID = ?";
    
    public String Attach_IncDownloads = 
        "update ejf_attach set downloads = downloads + 1 where attachID = ?";

    public String Attach_GetList = 
        "select * from ejf_attach where topicID = ? and state = 'N'";

    public String Attach_GetRecycledList = 
        "select attachID,localname from ejf_attach where state = 'R' and createTime < ?";

    public String Attach_GetAvatarList = 
        "select avatar from ejf_user";