-----------------------------------------------------------------------------------------------------------------------------------------
使用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;
}
}
------------------------------------------------------------------------------------------------------------------------------------------------
速度非常快