如何实现在线人数统计和登录限制？

关键词：minioa

目标：统计在线人数，5分钟内活动的连接视为在线。同一帐号不能同时登录，当某帐号登陆时，之前登录的人会被强制退出。

表结构，记录用户id,sessionId，创建时间，最后一次活动时间，ip地址，是否要强制退出。

    CREATE TABLE `core_user_session` (
      `ID_` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `CID_` int(10) DEFAULT NULL,
      `CDATE_` char(15) DEFAULT NULL,
      `MDATE_` char(15) DEFAULT NULL,
      `sessionId` varchar(100) DEFAULT NULL,
      `ip` varchar(45) DEFAULT NULL,
      `kickedout` tinyint(4) DEFAULT '0',
      PRIMARY KEY (`ID_`)
    ) ENGINE=InnoDB AUTO_INCREMENT=0DEFAULT CHARSET=utf8;

存储过程，先判断sessionId是否存在，存在即更新，不存在就插入一条记录。判断当前会话是否被强制退出，如果是的话就返回-1,如果不是，就将别的同帐号会话强制退出，同时返回最近5分钟活跃的在线人数。

    -- --------------------------------------------------------------------------------
    -- Routine DDL
    -- Note: comments before and after the routine body will not be stored by the server
    -- --------------------------------------------------------------------------------
    DELIMITER $

    CREATE DEFINER=`root`@`localhost` PROCEDURE `core_online`(in cId int, in sId varchar(45),in cTime char(15),in mTime char(15), in ipAddress varchar(30))
    BEGIN
      DECLARE isexists int;
      DECLARE iskickedout int;
      SELECT count(*) into isexists FROM core_user_session where sessionId=sId;
      IF isexists > 0 THEN
         update core_user_session set MDATE_ = mTime, CID_ = cId, ip = ipAddress where sessionId=sId;
      ELSE
        insert into core_user_session(CID_,CDATE_, sessionId, ip) values(cId, cTime, sId, ipAddress);
      END IF;

      SELECT kickedout into iskickedout FROM core_user_session where sessionId=sId;
      if iskickedout = 1 then
       select -1 from dual;
      else
       update core_user_session set kickedout = 1 where CID_ = cId and sessionId!=sId;
       select ifnull((select count(*) FROM core_user_session where kickedout = 0 and (MDATE_ + '300000') > mTime),0) from dual;
      end if;
    END

页面中，设定每分钟更新一下在线人数

    <a4j:region renderRegionOnly="true">
       <h:panelGrid columns="2" columnClasses="co1,co2">
          <h:outputText id="online" value="#{Lang.prop[MySession.l]['online']}:#{UserSession.online}" class="right_header" />
          <h:form>
             <a4j:poll id="poll" interval="600000" enabled="true" reRender="poll,online" />
          </h:form>
       </h:panelGrid>
    </a4j:region>

javabean中，显示在线人数，如果返回-1就销毁当前session，强制当前帐号退出。




    private String online;
    public String getOnline(){
       try {
          FacesContext context = FacesContext.getCurrentInstance();
          HttpServletRequest request = (HttpServletRequest) context.getExternalContext().getRequest();
          Query query = getSession().getNamedQuery("core.user.session.update");
          query.setParameter("cId", getMySession().getUserId());
          query.setParameter("sId", request.getSession().getId());
          query.setParameter("cTime", request.getSession().getCreationTime());
          query.setParameter("mTime", request.getSession().getLastAccessedTime());
          query.setParameter("ipAddress", getMySession().getIp());
          online = String.valueOf(query.list().get(0));
          if("-1".equals(online)){
             query = getSession().getNamedQuery("core.user.session.delete");
             query.setParameter("sessionId", request.getSession().getId());
             query.executeUpdate();
             HttpSession session = (HttpSession) context.getExternalContext().getSession(false);
             session.invalidate();
             FunctionLib.redirect(FunctionLib.getWebAppName());
          }
       } catch (Exception ex) {
          ex.printStackTrace();
       }
       return online;
    }

讨论地址：http://www.minioa.net/viewtopic.php?f=7&t=288