sqladapter.java

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

package com.hongshee.ejforum.data;

/**
 * <p>Title: SqlAdapter.java</p>
 * <p>Description: Standard sql stamement wrapper</p>
 * <p>Copyright: Hongshee Software (c) 2007</p>
 * @author jackie du
 * @version 1.0
 */

public abstract class SqlAdapter 
{
    protected SqlAdapter(){}

    /**
     * Do initialization according to different database
     * @param none
     * @return none
     * @throws Exception
     * @since 1.0
     */
    public abstract void init() throws Exception;

    /**
     * Append paging statement to query sql
     * @param
     *      sqlBuf - Original query sql
     *      pageNo - page number
     *      pageRows - each page size
     *      totalCount - total result count
     * @return 
     *      Paging query sql 
     * @throws none
     * @since 1.0
     */
    public abstract String getPageQuerySql(StringBuilder sqlBuf,
                                           int pageNo, int pageRows, int totalCount);

    /**
     * Get paging statement query parameters
     * @param
     *      pageNo - page number
     *      pageRows - each page size
     *      totalCount - total result count
     * @return 
     *      An int[2] result array which has following meaning:
     *      result[0] - 1st page parameter relative to getPageQuerySql function 
     *      result[1] - 2nd page parameter relative to getPageQuerySql function 
     * @throws none
     * @since 1.0
     */
    protected abstract int[] getPageQueryParams(int pageNo, int pageRows, int totalCount);
    
    // Group
    public String Group_Insert = 
        "insert into ejf_group(groupID,groupName,groupType,minCredits," +
        "stars,rights,createTime) values(?,?,?,?,?,?,NOW())";
    
    public String Group_Update = 
        "update ejf_group set groupName=?,minCredits=?,stars=?," +
        "updateTime=NOW() where groupID=?";
    
    public String Group_ModRights = 
        "update ejf_group set rights=?,updateTime=NOW() where groupID=?";
    
    public String Group_Delete = 
        "delete from ejf_group where groupID=?";
    
    public String Group_RemoveMembers = 
        "delete from ejf_group where groupType='M'";
    
    public String Group_SelectAll = 
        "select * from ejf_group order by groupType DESC,minCredits ASC,stars DESC";
    
    // User
    public String User_Insert = 
        "insert into ejf_user(userID,nickname,pwd,email,icq,webpage,gender,birth,city," +
        "remoteIP,brief,isMailPub,groupID,credits,state,lastVisited,createTime) " +
        "values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,NOW(),NOW())";
    
    public String User_Update = 
        "update ejf_user set nickname=?,email=?,icq=?,webpage=?,gender=?,birth=?," + 
        "city=?,isMailPub=?,updateTime=NOW() where userID=?";
    
    public String User_ModSpecInfo = 
        "update ejf_user set avatar=?,brief=?,updateTime=NOW() " + 
        "where userID=?";
    
    public String User_Login = 
        "select pwd,state,loginCount,updateTime," +
        "nickname,posts,unreadSMs,credits,groupID,lastVisited " +
        "from ejf_user where userID=?";
    
    public String User_QuickLogin = 
        "select loginExpire,nickname,posts,unreadSMs,credits,groupID,lastVisited,state " +
        "from ejf_user where userID=?";
    
    public String User_ModPasswd = 
        "update ejf_user set pwd=?,updateTime=NOW() where userID=?";
    
    public String User_ModLastVisited = 
        "update ejf_user set visitCount=visitCount+1,lastVisited=NOW()," +
        "updateTime=NOW() where userID=?";
    
    public String User_ModLoginCount = 
        "update ejf_user set loginCount=?,updateTime=NOW() where userID=?";
    
    public String User_ModLoginExpire = 
        "update ejf_user set loginCount=?,loginExpire=?,visitCount=visitCount+1," +
        "lastVisited=NOW(),updateTime=NOW() where userID=?";
    
    public String User_ModSetpwdExpire = 
        "update ejf_user set setpwdExpire=? where userID=?";
    
    public String User_IncPosts = 
        "update ejf_user set posts=posts+1 where userID=?";

    public String User_DecPostsAndCredits = 
        "update ejf_user set posts=posts-1,credits=credits-? where posts>0 and userID=?";

    public String User_IncCredits = 
        "update ejf_user set credits=credits+? where userID=?";

    public String User_DecCredits = 
        "update ejf_user set credits=credits-? where userID=?";
    
    public String User_IncUnreadSMs = 
        "update ejf_user set unreadSMs=unreadSMs+1 where userID=?";
    
    public String User_DecUnreadSMs = 
        "update ejf_user set unreadSMs=unreadSMs-1 where unreadSMs>0 and userID=?";
    
    public String User_StatUnreadSMs = 
        "update ejf_user set unreadSMs=? where userID=?";

    public String User_Delete = 
        "delete from ejf_user where userID=?";

    public String User_CleanExpired = 
        "delete from ejf_user where lastVisited < ? and posts = 0";
    
    public String User_Select = 
        "select * from ejf_user where userID=?";
    
    public String User_IsExistedID = 
        "select COUNT(*) from ejf_user where userID=?";
    
    public String User_IsExistedMail = 
        "select COUNT(*) from ejf_user where email=?";
    
    public String User_GetMailFromID = 
        "select email from ejf_user where userID=?";
    
    public String User_GetIDFromMail = 
        "select userID from ejf_user where email=?";
    
    public String User_GetSetpwdExpire = 
        "select setpwdExpire from ejf_user where userID=?";
    
    public String User_QueryInfo = 
        "select userID,nickname,posts,credits,groupID,state from ejf_user";

    public String User_GetList = 
        "select userID,nickname,gender,credits,posts,groupID,state,lastVisited," +
        "createTime from ejf_user where state<>'A' and state<>'S'";

    public String User_GetAuditHandler = 
        "select userID,email from ejf_user " +
        "where state<>'A' and state<>'S' and groupID in";

    public String User_GetReportHandler = 
        "select userID,email from ejf_user " +
        "where state<>'A' and state<>'S' and (groupID='A'";
    
    public String User_GetCount = 
        "select COUNT(*) from ejf_user where state<>'A' and state<>'S'";
    
    public String User_ModGroupID = 
        "update ejf_user set groupID=?,updateTime=NOW() where userID=?";

    public String User_ModState = 
        "update ejf_user set state=?,updateTime=NOW()";

    public String User_GetAuditing = 
        "select userID,remoteIP,email,createTime from ejf_user where state='A'";
    
    // Section
    public String Section_Insert = 
        "insert into ejf_section(sectionName,seqno,cols,createTime) values(?,?,?,NOW())";
    
    public String Section_Update = 
        "update ejf_section set sectionName=?,cols=?,updateTime=NOW() where sectionID=?";
    
    public String Section_ModSeqno = 
        "update ejf_section set seqno=?,updateTime=NOW() where sectionID=?";
    
    public String Section_ModModerator = 
        "update ejf_section set moderator=?,updateTime=NOW() where sectionID=?";
    
    public String Section_Delete = 
        "delete from ejf_section where sectionID=?";
    
    public String Section_SelectAll = 
        "select sectionID,sectionName,seqno,cols,moderator " + 
        "from ejf_section order by seqno ASC";
    
    public String Section_SelectBoards = 
        "select a.sectionID,a.seqno as s_seqno," + 
        "b.boardID,b.boardName,b.highColor,b.seqno,b.brief,b.keywords,b.moderator," +
        "b.viewStyle,b.sortField,b.isImageOK,b.isMediaOK,b.isGuestPostOK,b.allowGroups," +
        "b.acl,b.headAdCode,b.footAdCode,b.state from ejf_section a,ejf_board b " +
        "where b.sectionID = a.sectionID order by s_seqno ASC,b.seqno ASC";
    
    // Board
    public String Board_Insert = 
        "insert into ejf_board(sectionID,boardName,seqno,createTime) values(?,?,?,NOW())";
    
    public String Board_Update = 
        "update ejf_board set sectionID=?,boardName=?,highColor=?,brief=?,keywords=?," +
        "viewStyle=?,sortField=?,isImageOK=?,isMediaOK=?,isGuestPostOK=?,allowGroups=?," +
        "acl=?,headAdCode=?,footAdCode=?,state=?,updateTime=NOW() where boardID=?";

    public String Board_CopyInfo = 
        "update ejf_board set viewStyle=?,sortField=?,isImageOK=?,isMediaOK=?,isGuestPostOK=?," +
        "allowGroups=?,acl=?,headAdCode=?,footAdCode=?,updateTime=NOW() where boardID=?";
    
    public String Board_ModSeqno = 
        "update ejf_board set seqno=?,updateTime=NOW() where boardID=?";
    
    public String Board_ModModerator = 
        "update ejf_board set moderator=?,updateTime=NOW() where boardID=?";
    
    public String Board_Delete = 
        "delete from ejf_board where boardID=?";
    
    public String Board_StatTopics = 
        "select boardID,COUNT(topicID) as topics,SUM(replies) as posts," +
        "MAX(topicID) as lastTopicID from ejf_topic " +
        "where state = 'N' group by boardID";

    public String Board_StatTodayTopics = 
        "select boardID,COUNT(topicID) as topics " +
        "from ejf_topic where createTime > ? group by boardID";

    public String Board_StatTodayReplies = 
        "select a.boardID,COUNT(b.replyID) as replies from ejf_topic a, ejf_reply b " +
        "where a.topicID = b.topicID and b.createTime > ? group by a.boardID";
    
    public String Board_GetLastTopic = 
        "select boardID,topicID,title,userID,nickname,createTime from ejf_topic " +
        "where topicID in";
    
    public String Board_RemoveTopics = 
        "delete from ejf_topic where boardID=?";
    
    public String Board_RemoveTrashs = 
        "delete from ejf_trash_box where boardID=?";
    
    public String Board_RemoveBookmarks = 
        "delete from ejf_bookmark where boardID=?";
    
    public String Board_MoveTopics = 
        "update ejf_topic set sectionID=?,boardID=? where boardID=?";
    
    public String Board_MoveTrashs = 
        "update ejf_trash_box set boardID=? where boardID=?";
    
    public String Board_MoveBookmarks = 
        "update ejf_bookmark 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,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_ModNickname = 
        "update ejf_topic set 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_GetList = 
        "select sectionID,boardID,topicID,userID,nickname,title,reward,visits,replies," +
        "isDigest,isHidePost,isSolved,attachIcon,topScope,topExpireDate,highColor," +
        "highExpireDate,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.title,a.content,a.replies,a.reward,a.isReplyNotice,a.isHidePost," +
        "a.attaches,a.topScope,a.createTime,a.state,a.updateUser,a.updateTime," +
        "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 title,userID,replies,reward,isReplyNotice,state " +
        "from ejf_topic where topicID = ?";
    
    public String Topic_ModLastPost =