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

最新推荐文章于 2025-07-25 19:36:03 发布

zjwfisheep

最新推荐文章于 2025-07-25 19:36:03 发布

阅读量3k

点赞数

CC 4.0 BY-SA版权

分类专栏： Esper JAVA 文章标签：数据库 null string collation exception properties

本文链接：https://blog.youkuaiyun.com/zjwfisheep/article/details/5809189

JAVA 同时被 2 个专栏收录

13 篇文章

订阅专栏

Esper

4 篇文章

订阅专栏

本文展示了如何使用Esper引擎从MySQL数据库中实时查询登录日志。通过创建EPL语句，结合数据库查询，实现特定条件下的事件监听和处理，例如当同一IP出现在不同用户登录记录中时进行统计。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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

使用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;