Esper(二) 数据库篇 3 从数据库中取数据

-----------------------------------------------------------------------------------------------------------------------------------------

使用mysql数据库    要查询的表结构   数据样例：100@aa.com   |   127.0.0.1   |   2010-07-20 15:57:15 0323

-----------------------------------------------------------------------------------------------------------------------------------------

/*Table: loginlog*/
-------------------

/*Column Information*/
----------------------

FIELD       TYPE         COLLATION        NULL    KEY     DEFAULT  Extra   PRIVILEGES                       COMMENT
----------  -----------  ---------------  ------  ------  -------  ------  -------------------------------  -------
name         VARCHAR(50)  utf8_general_ci  NO      PRI     (NULL)           SELECT,INSERT,UPDATE,REFERENCES        
ip          VARCHAR(15)  utf8_general_ci  NO      PRI     (NULL)           SELECT,INSERT,UPDATE,REFERENCES        
login_time  VARCHAR(25)  utf8_general_ci  NO      PRI     (NULL)           SELECT,INSERT,UPDATE,REFERENCES        

/*Index Information*/
---------------------

TABLE     Non_unique  Key_name  Seq_in_index  Column_name  COLLATION  Cardinality  Sub_part  Packed  NULL    Index_type  COMMENT
--------  ----------  --------  ------------  -----------  ---------  -----------  --------  ------  ------  ----------  -------
loginlog           0  PRIMARY              1  name        A               (NULL)    (NULL)  (NULL)          BTREE             
loginlog           0  PRIMARY              2  ip           A               (NULL)    (NULL)  (NULL)          BTREE             
loginlog           0  PRIMARY              3  login_time   A              1310003    (NULL)  (NULL)          BTREE             

/*DDL Information*/
-------------------

CREATE TABLE `loginlog` (
  `name` VARCHAR(50) NOT NULL,
  `ip` VARCHAR(15) NOT NULL,
  `login_time` VARCHAR(25) NOT NULL,
  PRIMARY KEY (`name`,`ip`,`login_time`)
) ENGINE=MYISAM DEFAULT CHARSET=utf8

-----------------------------------------------------------------------------------------------------------------------------------------

                                                                             源代码

----------------------------------------------------------------------------------------------------------------------------------------

import java.util.Properties;
import org.apache.commons.dbcp.BasicDataSourceFactory;
import org.junit.AfterClass;
import org.junit.BeforeClass;
import org.junit.Test;
import com.espertech.esper.client.Configuration;
import com.espertech.esper.client.ConfigurationDBRef;
import com.espertech.esper.client.EPServiceProvider;
import com.espertech.esper.client.EPServiceProviderManager;
import com.espertech.esper.client.EPStatement;
import com.espertech.esper.client.EventBean;
import com.espertech.esper.client.UpdateListener;

public class DemodbMemoryTest
{
    private  static EPServiceProvider epService;
   
    @BeforeClass
    public static void setUpBeforeClass() throws Exception {
        System.out.println("call before all tests...");
        //配置数据库链接
        Properties props = new Properties();
        props.put("username", "esper");
        props.put("password", "esper123");
        props.put("driverClassName", "com.mysql.jdbc.Driver");
        props.put("url", "jdbc:mysql://127.0.0.1:3306/esperdemo");
        props.put("initialSize",20);
        props.put("maxActive",20);
        props.put("validationQuery", "select 1 from dual");
        ConfigurationDBRef configDB = new ConfigurationDBRef();
        configDB.setDataSourceFactory(props, BasicDataSourceFactory.class.getName());
        configDB.setConnectionLifecycleEnum(ConfigurationDBRef.ConnectionLifecycleEnum.POOLED);
        Configuration config = new Configuration();
//      定义event
        config.addEventType("TransferAccount", TransferAccount.class);
        config.addDatabaseReference("esperdemo", configDB);
//      初始化Esper引擎
        //定义数据库查询语句
        String sql= " SELECT COUNT(a.ip) AS cnt " +
           " FROM(SELECT DISTINCT ip FROM loginlog WHERE name=${ta.nameSrc}) AS a " +
           " INNER JOIN (SELECT DISTINCT ip FROM loginlog WHERE name=${ta.nameDest}) AS b " +
           " ON a.ip=b.ip";
        epService = EPServiceProviderManager.getProvider("engineURI", config);
        //定义statment  EPL语句
        String statmentSQL = " select nameDest,beforeTime " +
                       " from TransferAccount(transferMoney/nameSrcMoney>=0.9).win:time(3 sec) as ta," +
                       "sql:esperdemo[/""+sql+"/"] as ld" +
                       " where ld.cnt>0 ";
        EPStatement st = epService.getEPAdministrator().createEPL(statmentSQL);
        //添加监听器
        st.addListener(new UpdateListener()
        {
         //得到符合EPL条件的事件（EPL处理的结果）
         public void update (EventBean[] newEvents,EventBean[] oldEvents) {
          long endTime=System.currentTimeMillis();
          if (newEvents!=null) {
           System.out.println("----new----end------->"+endTime+"------term:----->"+(endTime-Long.parseLong(newEvents[0].get("beforeTime").toString()))+"ms");
          }else if (oldEvents!=null)  {
           System.out.println("----old----end------->"+endTime+"------term:----->"+(endTime-Long.parseLong(oldEvents[0].get("beforeTime").toString()))+"ms");
           System.out.println("the old event is null.");
          } else {
           System.out.println("-------end--------->"+endTime);
          }
         }
     });
    }
    @Test
    public void testTimeIsolation() throws Exception
    {
//      发送事件
        for (int i = 0; i < 10000; i++) {
         long beforeTime=System.currentTimeMillis();
      epService.getEPRuntime().sendEvent(new TransferAccount("100@aa.com","1000@aa.com",100d,90d,beforeTime));
        }
    }
   
   
    @AfterClass
    public static void tearDownAfterClass() throws Exception {
     epService.destroy();
    }
   
    //要发送的事件
    public static class TransferAccount{
     TransferAccount(String nameSrc,String nameDest,Double nameSrcMoney,Double transferMoney,Long beforeTime){
      this.nameSrc=nameSrc;
      this.nameDest=nameDest;
      this.nameSrcMoney=nameSrcMoney;
      this.transferMoney=transferMoney;
      this.beforeTime=beforeTime;
     }
     Long beforeTime;
     
     String nameSrc;
     
     String nameDest;
     
     Double nameSrcMoney;
     
     Double transferMoney;

  public String getNameSrc() {
   return nameSrc;
  }

  public void setNameSrc(String nameSrc) {
   this.nameSrc = nameSrc;
  }

  public String getNameDest() {
   return nameDest;
  }

  public void setNameDest(String nameDest) {
   this.nameDest = nameDest;
  }

  public Double getNameSrcMoney() {
   return nameSrcMoney;
  }

  public void setNameSrcMoney(Double nameSrcMoney) {
   this.nameSrcMoney = nameSrcMoney;
  }

  public Double getTransferMoney() {
   return transferMoney;
  }

  public void setTransferMoney(Double transferMoney) {
   this.transferMoney = transferMoney;
  }

  public Long getBeforeTime() {
   return beforeTime;
  }

  public void setBeforeTime(Long beforeTime) {
   this.beforeTime = beforeTime;
  }

    }
   
 public static EPServiceProvider getEpService() {
  return epService;
 }

 public static void setEpService(EPServiceProvider epService) {
  DemodbMemoryTest.epService = epService;
 }
   
 

}
------------------------------------------------------------------------------------------------------------------------------------------------

 

速度非常快