现实中,经常碰到对多个不同的表进行转移处理，并且转移的过程中还要进行合并或者其他操作。标准的ETL工具的性能或者功能受限，因此自己开发一套灵活定制版的。
类结构如下：
1 服务类
package waf.db.combine;
import waf.convert.Conv;
import waf.datatype.DateTime;
import waf.db.combine.util.CombineResult;
import waf.lang.StringUtil;
/**
*
* @author waf.wang
*
*/
public abstract class BaseCombineService
{
protected BaseCombineStrategy combineStrategy=null;
protected String month="";
protected String beginDate="";
protected String endDate="";
public BaseCombineService()
{
init();
}
public BaseCombineService(String month)
{
this.month=month;
beginDate=month.substring(0, 4)+"-"+month.substring(4, 6)+"-01";
endDate=DateTime.addMonth(beginDate, 1);
//endDate="2016-02-18";
init();
}
private void init()
{
combineStrategy=createCombine();
combineStrategy.setEndDate(endDate);
configCombine();
}
public void setSrcFilter(String filter)
{
combineStrategy.setFilter(filter);
}
public CombineResult execute()
{
CombineResult result= combineStrategy.execute();
if(result.getRows()>0)
{
String rowsLog="";
if(result.getInsertList().size()>0)
{
rowsLog+=" insertRows="+String.format("%2d",result.getInsertList().size())+"";
}
else
{
rowsLog+=" ";
}
if(result.getUpdateList().size()>0)
{
rowsLog+=" updateRows="+String.format("%2d",result.getUpdateList().size());
}
else
{
rowsLog+=" ";
}
rowsLog=StringUtil.removeLastChar(rowsLog, ",");
String idLog="";
if(result.getInsertList().size()>0)
{
idLog+=" insertId:"+Conv.ary2str(result.getInsertList())+" ";
}
else
{
idLog+=" ";
}
if(result.getUpdateList().size()>0)
{
idLog+=" updateId:"+Conv.ary2str(result.getUpdateList());
}
else
{
idLog=StringUtil.removeLastChar(idLog, ",");
idLog+=" ";
}
idLog=StringUtil.removeLastChar(idLog, ",");
String log=new DateTime().getDateTimeString()
+" "+this.getClass().getSimpleName()
+" rows="+String.format("%2d", result.getRows())+""
+rowsLog
+" lastId="+String.format("%5d", result.getLastId())
+" lastTime="+result.getLastTime().substring(0, 19)
+idLog
+"";
System.out.println(log);
//System.out.println(new DateTime().getDateTimeString()+" "+this.getClass().getSimpleName()+" insert:"+Converter.ary2str(result.getInsertList()));
//System.out.println(new DateTime().getDateTimeString()+" "+this.getClass().getSimpleName()+" update:"+Converter.ary2str(result.getUpdateList()));
}
return result;
}
public void start()
{
while(true)
{
CombineResult result=execute();
if(!result.isSuc())
{
System.out.println(new DateTime().getDateTimeString()+" "+this.getClass().getSimpleName()+" sync error,exit!");
break;
}
if(result.getRows()>0)
{
System.out.println(new DateTime().getDateTimeString()+" "+this.getClass().getSimpleName()+" rows="+result.getRows()+",lastId="+result.getLastId()+",lastTime="+result.getLastTime());
waf.lang.Thread.sleep(10);
}
else
{
System.out.println(new DateTime().getDateTimeString()+" "+this.getClass().getSimpleName()+" rows="+result.getRows());
break;
//waf.lang.Thread.sleep(1000*5);
}
}
}
public abstract void configCombine();
public abstract BaseCombineStrategy createCombine();
//
}
2 合并策略抽象基类
package waf.db.combine;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import waf.db.Database;
import waf.db.combine.util.CombineResult;
/**
*
* @author waf.wang
*
*/
public abstract class BaseCombineStrategy
{
protected String srcConn="";
protected String srcTable="";
protected String srcProductId="";
protected String srcIncrPrimaryKey="";
protected String srcBizPrimaryKey="";
protected String srcTableSyncLastId="";
protected String srcTimestampFieldName="";
protected String srcLastSyncTimeFieldName="";
protected String dstConn="";
protected String dstTable="";
protected String dstIncrPrimaryKey="";
protected String dstBizPrimaryKey="";
protected String dstSrcIdFieldName="";
//protected String dstTimestampFieldName="";
protected String dstLastSyncTimeFieldName="";
protected String dstProductIdFieldName="";
protected String dstProductIdPrefix="";
protected String srcTimeFieldName="";
protected String endDate="";
//private Map<String, String> extendDestField=new LinkedHashMap<String,String>();
protected List<String> srcFields=new ArrayList<String>();
protected List<String> dstFields=new ArrayList<String>();
protected List<String> destExtendFieldName=new ArrayList<String>();
protected List<String> destExtendFieldValue=new ArrayList<String>();
protected Map<String,String> destExtendField=new HashMap<String,String>();
/**
* 只在一行的处理中，使用一次的动态目的字段
*/
protected List<String> destDynamicFieldList=new ArrayList<String>();
protected Map<String, Boolean> destDynamicFieldMap=new HashMap<String, Boolean>();
protected String filter="";
protected int pagesize=0;
private boolean isEnableInsert=true;
private boolean isEnableUpdate=true;
public void setSrc(String srcConn,String srcTable,String srcTableSyncLastId,String incrPrimaryKey,String timeFieldName,String filter,int pagesize)
{
this.srcConn=srcConn;
this.srcTable=srcTable;
this.srcTableSyncLastId=srcTableSyncLastId;
this.srcIncrPrimaryKey=incrPrimaryKey;
this.srcTimeFieldName=timeFieldName;
this.filter=filter;
this.pagesize=pagesize;
if(this.pagesize<=0)
{
this.pagesize=1;
}
}
public void setSrc(String srcConn,String srcTable,String srcProductId,
String srcTimestampFieldName,String srcLastSyncTimeFieldName,
String srcIncrPrimaryKey,String srcTimeFieldName,
String filter,int pagesize)
{
setSrc(srcConn, srcTable, srcProductId, srcTimestampFieldName, srcLastSyncTimeFieldName, srcIncrPrimaryKey, srcIncrPrimaryKey, srcTimeFieldName, filter, pagesize);
// this.srcConn=srcConn;
// this.srcProductId=srcProductId;
// this.srcTable=srcTable;
// this.srcTimestampFieldName=srcTimestampFieldName;
// this.srcLastSyncTimeFieldName=srcLastSyncTimeFieldName;
//
// this.srcIncrPrimaryKey=incrPrimaryKey;
// this.timeFieldName=timeFieldName;
// this.filter=filter;
// this.pagesize=pagesize;
// if(this.pagesize<=0)
// {
// this.pagesize=1;
// }
}
public void setSrc(String srcConn,String srcTable,String srcProductId,
String srcTimestampFieldName,String srcLastSyncTimeFieldName,
String srcIncrPrimaryKey,String srcBizPrimaryKey,String srcTimeFieldName,
String filter,int pagesize)
{
this.srcConn=srcConn;
this.srcProductId=srcProductId;
this.srcTable=srcTable;
this.srcTimestampFieldName=srcTimestampFieldName;
this.srcLastSyncTimeFieldName=srcLastSyncTimeFieldName;
this.srcIncrPrimaryKey=srcIncrPrimaryKey;
this.srcBizPrimaryKey=srcBizPrimaryKey;
this.srcTimeFieldName=srcTimeFieldName;
this.filter=filter;
this.pagesize=pagesize;
if(this.pagesize<=0)
{
this.pagesize=1;
}
}
public void setDst(String dstConn,String dstTable,
String dstProductIdFieldName,String dstProductIdPrefix,
String dstIncrPrimaryKey,String dstBizPrimaryKey,
String dstSrcIdFieldName,String dstLastSyncTimeFieldName
)
{
this.dstConn=dstConn;
this.dstTable=dstTable;
this.dstIncrPrimaryKey=dstIncrPrimaryKey;
this.dstBizPrimaryKey=dstBizPrimaryKey;
this.dstProductIdFieldName=dstProductIdFieldName;
this.dstProductIdPrefix=dstProductIdPrefix;
this.dstSrcIdFieldName=dstSrcIdFieldName;
this.dstLastSyncTimeFieldName=dstLastSyncTimeFieldName;
}
public void setDst(String dstConn,String dstTable,
String dstProductIdFieldName,String dstProductIdPrefix,
String dstIncrPrimaryKey,
String dstSrcIdFieldName,String dstLastSyncTimeFieldName
)
{
setDst(dstConn, dstTable, dstProductIdFieldName, dstProductIdPrefix, dstIncrPrimaryKey, dstIncrPrimaryKey, dstSrcIdFieldName,dstLastSyncTimeFieldName);
// this.dstConn=dstConn;
// this.dstTable=dstTable;
// this.dstIncrPrimaryKey=dstIncrPrimaryKey;
// this.dstProductIdFieldName=dstProductIdFieldName;
// this.dstProductIdPrefix=dstProductIdPrefix;
// this.dstLastSyncTimeFieldName=dstLastSyncTimeFieldName;
}
public void setDst(String dstConn,String dstTable)
{
this.dstConn=dstConn;
this.dstTable=dstTable;
}
public void addField(String field)
{
addField(field,"");
}
public void addField(String src,String dst)
{
srcFields.add(src);
if(dst!=null && dst.length()==0)
{
dstFields.add(src);
}
else
{
dstFields.add(dst);
}
}
public void setFieldOnlyRead(String field)
{
destDynamicFieldMap.put(field, true);
}
public boolean isDestDynamicField(String field)
{
boolean ret=false;
Boolean readOnly=destDynamicFieldMap.get(field);
if(readOnly!=null && readOnly==true)
{
ret=true;
}
else
{
ret=false;
}
return ret;
}
public void addDestDynamicField(String field)
{
addField(field);
destDynamicFieldMap.put(field, true);
}
public String getEndDate()
{
return endDate;
}
public void setEndDate(String endDate)
{
this.endDate = endDate;
}
public void addDestExtendField(String dstFieldName,String value)
{
destExtendFieldName.add(dstFieldName);
destExtendFieldValue.add(value);
destExtendField.put(dstFieldName, value);
}
//
//
/**
* 有些源表是key value结构，但是目的表是基于业务模型的。因此形成的dest sql是动态的。
* 通过这样的方式来实现：
* 在一个行处理中，根据key的值，产生不同的dest字段，执行sql之后，再清除掉动态产生的字段信息。
*/
protected void addDestDynamicField(String dstFieldName,String value)
{
addDestExtendField(dstFieldName, value);
destDynamicFieldList.add(dstFieldName);
}
protected void clearDestDynamicFields()
{
for (String fieldToDelete : destDynamicFieldList)
{
int idxToDelete=-1;
for (int i = 0; i < destExtendFieldName.size(); i++)
{
if(destExtendFieldName.get(i).equalsIgnoreCase(fieldToDelete))
{
idxToDelete=i;
break;
}
}
if(idxToDelete>=0)
{
destExtendFieldName.remove(idxToDelete);
destExtendFieldValue.remove(idxToDelete);
destExtendField.remove(fieldToDelete);
}
}
destDynamicFieldList.clear();
}
public String getDstProductIdValue()
{
String ret="";
if(dstProductIdPrefix.length()>0)
{
ret+=this.dstProductIdPrefix+"-";
}
if(this.srcProductId.length()>0)
{
ret+=this.srcProductId;
}
return ret;
}
public void init()
{
// 如果自增是业务主键，自增Id就跟src_id发生关系
//if(isCheckDestByIncrPrimaryKeyValue)
{
addField(this.srcIncrPrimaryKey, this.dstSrcIdFieldName);
}
// 如果自增不是业务主键，业务主键就跟src_id发生关系
// else
// {
// addField(this.srcBizPrimaryKey, this.dstSrcIdFieldName);
// }
this.addDestExtendField(this.dstProductIdFieldName, getDstProductIdValue());
if(this instanceof CombineByTimestampStrategy)
{
}
else
{
String sql="";
sql="select last_id from "+srcTableSyncLastId;
if(!Database.executeSQL("mypc1", sql))
{
System.out.println("create table "+srcTableSyncLastId);
sql="create table "+srcTableSyncLastId+" (last_id bigint(20) not null,last_time datetime,last_try_delete_id bigint(20) not null,last_try_delete_time datetime,PRIMARY KEY (last_id))";
Database.executeSQL("mypc1", sql);
//sql="insert into "+srcTableSyncLastId+" values(0)";
//Database.executeSQL("mypc1", sql);
}
else
{
}
}
}
public abstract CombineResult execute();
public static void main(String[] args)
{
}
public String getFilter()
{
return filter;
}
public void setFilter(String filter)
{
this.filter = filter;
}
public String getSrcConn()
{
return srcConn;
}
public void setSrcConn(String srcConn)
{
this.srcConn = srcConn;
}
public String getSrcTable()
{
return srcTable;
}
public void setSrcTable(String srcTable)
{
this.srcTable = srcTable;
}
public String getDstConn()
{
return dstConn;
}
public void setDstConn(String dstConn)
{
this.dstConn = dstConn;
}
public String getDstTable()
{
return dstTable;
}
public void setDstTable(String dstTable)
{
this.dstTable = dstTable;
}
public List<String> getSrcFields()
{
return srcFields;
}
public void setSrcFields(List<String> srcFields)
{
this.srcFields = srcFields;
}
public String getSrcTableSyncLastId()
{
return srcTableSyncLastId;
}
public String getSrcIncrPrimaryKey()
{
return srcIncrPrimaryKey;
}
public void setSrcIncrPrimaryKey(String incrPrimaryKey)
{
this.srcIncrPrimaryKey = incrPrimaryKey;
}
public String getSrcTimeFieldName()
{
return srcTimeFieldName;
}
public void setSrcTimeFieldName(String timeFieldName)
{
this.srcTimeFieldName = timeFieldName;
}
private boolean isCheckDestByIncrPrimaryKeyValue=true;
public boolean setCheckDestByIncrPrimaryKeyValue()
{
isCheckDestByIncrPrimaryKeyValue =true;
return isCheckDestByIncrPrimaryKeyValue;
}
public boolean enableCheckDestByBizPrimaryKeyValue()
{
isCheckDestByIncrPrimaryKeyValue =false;
return isCheckDestByIncrPrimaryKeyValue;
}
public boolean isCheckDestByIncrPrimaryKeyValue()
{
return isCheckDestByIncrPrimaryKeyValue;
}
public boolean isEnableInsert()
{
return isEnableInsert;
}
public void setEnableInsert(boolean isEnableInsert)
{
this.isEnableInsert = isEnableInsert;
}
public boolean isEnableUpdate()
{
return isEnableUpdate;
}
public void setEnableUpdate(boolean isEnableUpdate)
{
this.isEnableUpdate = isEnableUpdate;
}
}
3 根据时间戳合并的类
package waf.db.combine;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import waf.convert.Conv;
import waf.db.Database;
import waf.db.combine.util.CombineResult;
/**
*
* @author waf.wang
*
*/
public class CombineByTimestampStrategy extends BaseCombineStrategy
{
@Override
public CombineResult execute()
{
int count = 0;
String sql = "";
boolean executeSuc = true;
long srcIncrPrimaryKeyValue=0;
String srcBizPrimaryKeyValue="";
String lastTime = "";
sql=" select *"+
//Converter.ary2str(srcFields)+" \n"+
" from "+srcTable+" \n" +
" where 1=1 \n";
if(filter.trim().length()>0)
{
sql+=filter+" \n";
}
sql+=" and " +
"(" +
" "+srcTimestampFieldName+">"+srcLastSyncTimeFieldName+" " +
" or "+srcLastSyncTimeFieldName+" is null " +
" or "+srcLastSyncTimeFieldName+" ='' " +
")\n";
sql+=" order by "+srcIncrPrimaryKey+"\n"+
" limit "+pagesize;
String extendValues = "";
String dstFieldNames=Conv.ary2str(dstFields);
if(destExtendFieldName.size()>0)
{
dstFieldNames+=","+Conv.ary2str(destExtendFieldName);
}
// if(extendDestFieldValue.size()>0)
// {
// extendValues= Converter.ary2str(extendDestFieldValue, "'");
// }
CombineResult result = new CombineResult();
ArrayList<HashMap<String, String>> srcRows = null;
srcRows=Database.getRows(srcConn, sql);
// 为了形成同步时间与时间戳的不同，故意延时2秒一下
//waf.lang.Thread.sleep(2000);
//String values = "";
boolean outtime = false;
if (srcRows.size() > 0)
{
String lineValueList = "";
String lastMonth = "";
pluginBegin();
String currDstTable=dstTable+lastMonth.replace("-", "");
for (HashMap<String, String> srcRow : srcRows)
{
pluginLoopBegin(srcRow);
String syncTime="";
srcIncrPrimaryKeyValue=Conv.str2Int(srcRow.get(srcIncrPrimaryKey), 0);
srcBizPrimaryKeyValue=Conv.str2Str(srcRow.get(srcBizPrimaryKey));
//String dstBizPrimaryKeyValue=Converter.str2Str(srcRow.get(dstBizPrimaryKey));
String dstBizPrimaryKeyValue=Conv.str2Str(destExtendField.get(dstBizPrimaryKey));
if(isEnableInsert() || isEnableUpdate())
{
//long destId=this.getDestIdByIncrPrimaryKey(srcIncrPrimaryKeyValue, this.getDstProductIdValue());
long destId=this.getDestId(srcIncrPrimaryKeyValue, dstBizPrimaryKeyValue, this.getDstProductIdValue());
syncTime=Database.getDbDateTime(this.srcConn);
srcRow.put(srcLastSyncTimeFieldName, syncTime);
lastTime=syncTime;
lastTime=srcRow.get(srcTimeFieldName);
if(destId==0)
{
if(isEnableInsert())
{
result.addInsertList(""+srcIncrPrimaryKeyValue);
executeSuc=insert(srcRow, dstFieldNames);
count++;
}
pluginLoopEnd(srcRow);
}
else
{
if(isEnableUpdate())
{
result.addUpdateList(""+srcIncrPrimaryKeyValue);
sql=getUpdateSQL(srcRow, destId);
//System.out.println(sql);
try
{
Database.executeSQLWithThrow(this.dstConn, sql);
executeSuc = true;
}
catch (Exception e)
{
executeSuc=false;
System.out.println(sql);
e.printStackTrace();
}
count++;
}
}
}
else
{
executeSuc=true;
syncTime=Database.getDbDateTime(this.srcConn);
}
if(executeSuc)
{
sql="update "+srcTable+" set "+srcLastSyncTimeFieldName+"='"+syncTime+"' where 1=1 and "+srcIncrPrimaryKey+"="+srcIncrPrimaryKeyValue;
try
{
Database.executeSQLWithThrow(this.srcConn, sql);
executeSuc = true;
}
catch (Exception e)
{
count = -1;
System.out.println(sql);
e.printStackTrace();
}
}
else
{
System.out.println("转移失败，重新执行");
waf.lang.Thread.sleep(1000);
}
}
pluginEnd();
}
result.setSuc(executeSuc);
result.setRows(count);
result.setLastId(srcIncrPrimaryKeyValue);
result.setLastTime(lastTime);
return result;
}
private String getUpdateSQL(Map<String, String> srcRow,long destId)
{
String setValue="";
for (int i = 0; i < srcFields.size(); i++)
{
String srcValue=srcRow.get(srcFields.get(i));
if(dstFields.get(i)!=null && srcValue!=null)
{
setValue+=" "+dstFields.get(i)+"='"+Database.escape_mysql(srcRow.get(srcFields.get(i)))+"',\n";
}
}
for (String name : destExtendFieldName)
{
setValue+=" "+name+"='"+Database.escape_mysql(destExtendField.get(name))+"',\n";
}
setValue=waf.lang.StringUtil.removeLastString(setValue, ",\n");
setValue+="\n";
int m=0;
String sql="update "+dstTable +" set \n";
sql+=setValue;
sql=waf.lang.StringUtil.removeLastChar(sql, ",");
sql+="where 1=1 \n" +
"and "+dstIncrPrimaryKey+"='"+destId+"'";
return sql;
}
private String getInsertSQL(Map<String, String> srcRow,String dstFieldNames)
{
List<String> extendValueList=new ArrayList<String>();
for (String name : destExtendFieldName)
{
extendValueList.add(destExtendField.get(name));
}
String extendValues=Conv.ary2str(extendValueList, "'");
String insertValues = getInsertValue(srcRow, extendValues);
String destDynamicFieldsString=Conv.ary2str(destDynamicFieldList);
String finalDstFieldNames=dstFieldNames;
if(destDynamicFieldsString.length()>0)
{
finalDstFieldNames+=","+destDynamicFieldsString;
}
String sql = "insert into "+this.dstTable+"("+finalDstFieldNames+") values "+insertValues;
return sql;
}
private boolean insert(Map<String, String> srcRow,String dstFieldNames)
{
boolean executeSuc=false;
String sql = getInsertSQL(srcRow, dstFieldNames);
try
{
Database.executeSQLWithThrow(this.dstConn, sql);
executeSuc = true;
}
catch (Exception e)
{
executeSuc=false;
System.out.println(sql);
e.printStackTrace();
}
return executeSuc;
}
private String getInsertValue(Map<String, String> srcRow,String extendValues)
{
String lineValue = "(";
for (int i = 0; i < srcFields.size(); i++)
{
if (srcRow.get(srcFields.get(i)) == null)
{
lineValue += "null,";
}
else
{
lineValue += "'"+ Database.escape_mysql(srcRow.get(srcFields.get(i)))+"',";
}
}
lineValue += extendValues;
lineValue=waf.lang.StringUtil.removeLastChar(lineValue, ",");
lineValue += ")";
return lineValue;
}
public long getDestId(long srcIncrPrimaryKeyValue,String dstBizPrimaryKeyValue,String dstProductIdValue)
{
long destId=0;
if(isCheckDestByIncrPrimaryKeyValue())
{
destId=getDestIdByIncrPrimaryKeyValue(srcIncrPrimaryKeyValue, dstProductIdValue);
}
else
{
destId=getDestIdByBizPrimaryKeyValue(dstBizPrimaryKeyValue, dstProductIdValue);
}
return destId;
}
private long getDestIdByIncrPrimaryKeyValue(long srcIncrPrimaryKeyValue,String dstProductIdValue)
{
String sql="select "+dstIncrPrimaryKey+" \n" +
" from "+this.dstTable+" \n" +
" where 1=1 \n" +
" and "+dstProductIdFieldName+"='"+dstProductIdValue+"' \n"+
" and "+dstSrcIdFieldName+"="+srcIncrPrimaryKeyValue;
long destId=Database.getLong(dstConn, sql);
return destId;
}
private long getDestIdByBizPrimaryKeyValue(String dstBizPrimaryKeyValue,String dstProductIdValue)
{
String sql="select "+dstIncrPrimaryKey+" \n" +
" from "+this.dstTable+" \n" +
" where 1=1 \n" +
" and "+dstProductIdFieldName+"='"+dstProductIdValue+"' \n"+
" and "+dstBizPrimaryKey+"='"+dstBizPrimaryKeyValue+"'";
long destId=Database.getLong(dstConn, sql);
return destId;
}
protected void pluginBegin()
{
}
protected void pluginLoopBegin(Map<String, String> row)
{
}
protected void pluginLoopEnd(Map<String, String> row)
{
}
protected void pluginEnd()
{
}
}
4 删除策略的类
package waf.db.combine;
import java.util.ArrayList;
import java.util.HashMap;
import waf.convert.Conv;
import waf.datatype.DateTime;
import waf.db.Database;
import waf.db.combine.util.CombineResult;
import waf.file.IniFileManager;
/**
*
* @author waf.wang
*
*/
public class CombineDeleteStrategy extends BaseCombineStrategy
{
@Override
public CombineResult execute()
{
int count=0;
String datetime=Database.getDbDateTime(srcConn);
datetime=DateTime.addDay(datetime, -10);
long lastId=0;
long lastTryDeleteId=0;
String sql="";
sql="select last_id from "+this.getSrcTableSyncLastId();
lastId=Database.getLong(this.getSrcConn(), sql);
sql="select last_try_delete_id from "+this.getSrcTableSyncLastId();
lastTryDeleteId=Database.getLong(this.getSrcConn(), sql);
// 从待删源表中查最久的
sql="select * \n" +
" from "+this.getSrcTable()+" \n" +
" where 1=1 \n" +
" and "+srcIncrPrimaryKey+"<="+lastId+" \n"+
" and "+srcIncrPrimaryKey+"> "+lastTryDeleteId+" \n";
//" and "+this.timeFieldName+"<'"+datetime+"' \n"+
if(filter.trim().length()>0)
{
sql+=filter+" \n";
}
sql+=" order by "+srcIncrPrimaryKey+" \n" +
" limit "+pagesize;
ArrayList<HashMap<String,String>> rows=Database.getRows(this.getSrcConn(), sql);
String lastTime="";
long beginId=0;
long endId=0;
Database db=new Database(srcConn);
for (HashMap<String,String> row : rows)
{
long id=Conv.str2Long(row.get(this.srcIncrPrimaryKey), 0);
String create_time=row.get(srcTimeFieldName);
if(destExists(row))
{
if(count==0)
{
beginId=id;
}
endId=id;
// sql="delete from "+this.getSrcTable()+" \n" +
// " where 1=1 \n" +
// " and "+incrPrimaryKey+"="+endId+";" +
// db.addBatch(sql);
count++;
}
lastTryDeleteId=id;
lastTime=create_time;
}
boolean isSkip=false;
if(rows.size()>0)
{
if(count==0)
{
System.out.println(new DateTime().getDateTimeString()+" to delete "+getSrcTable()+",id="+lastTryDeleteId+" not exists,skip,lastTime="+lastTime);
lastTryDeleteId++;
isSkip=true;
}
if(count>0)
{
sql="delete from "+this.getSrcTable()+" \n" +
" where 1=1 \n" +
" and "+srcIncrPrimaryKey+">="+beginId+" \n" +
" and "+srcIncrPrimaryKey+"<="+endId;
Database.executeSQL(this.getSrcConn(), sql);
}
else
{
lastTryDeleteId++;
}
if(lastTryDeleteId>0)
{
sql="update "+this.getSrcTableSyncLastId()+" set last_try_delete_id="+lastTryDeleteId+",last_try_delete_time='"+lastTime+"'";
Database.executeSQL(this.getSrcConn(), sql);
}
}
//System.out.println(new DateTime().getDateTimeString()+" area deleted "+count+",lastTime="+lastTime+",beginId="+beginId+",endId="+endId);
CombineResult result = new CombineResult();
//result.setSuc(executeSuc);
result.setRows(count);
result.setLastId(endId);
result.setLastTime(lastTime);
result.setSkip(isSkip);
return result;
}
protected boolean destExists(HashMap<String,String> srcRow)
{
String month=srcRow.get(this.srcTimeFieldName);
long srcId=Conv.str2Long(srcRow.get(this.getSrcIncrPrimaryKey()), 0);
if(month.length()>7)
{
month=month.substring(0, 7);
}
String currDstTable=dstTable+month.replace("-", "");
String sql="select id from "+currDstTable+" " +
" where 1=1 " +
" and from_server='"+IniFileManager.readString("server_id")+"'" +
" and from_row_id="+srcId+
"";
return Database.exists("mypc2", sql);
}
protected String getFieldJudgeDestExists()
{
return "from_server_id";
}
}
5 插入策略的类
package waf.db.combine;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;
import waf.convert.Conv;
import waf.datatype.DateTime;
import waf.db.Database;
import waf.db.combine.util.CombineResult;
import waf.lang.StringUtil;
/**
*
* @author waf.wang
*
*/
public class CombineInsertStrategy extends BaseCombineStrategy
{
@Override
public CombineResult execute()
{
int count = 0;
String sql = "";
boolean executeSuc = true;
sql = "select last_id from "+srcTableSyncLastId;
long lastRowId = Database.getLong(this.srcConn, sql);
String lastTime = "";
// sql="select "+timeFieldName+
// " from "+srcTable+" \n" +
// " where 1=1 " +
// " and "+incrPrimaryKey+"<="+lastRowId+
// " order by id desc" +
// " limit 1";
// String startTime=Database.getString(this.srcConn, sql);
sql=" select "+
Conv.ary2str(srcFields)+" \n"+
" from "+srcTable+" \n" +
" where 1=1 \n" +
" and "+srcIncrPrimaryKey+">"+ lastRowId+"\n";
if(filter.trim().length()>0)
{
sql+=filter+" \n";
}
sql+=" order by "+srcIncrPrimaryKey+"\n"+
" limit "+pagesize;
String extendValues = Conv.ary2str(destExtendFieldValue, "'");
ArrayList<HashMap<String, String>> rows = null;
rows=Database.getRows(srcConn, sql);
String values = "";
boolean outtime = false;
if (rows.size() > 0)
{
String lineValueList = "";
String lastMonth = "";
pluginBegin();
for (HashMap<String, String> row : rows)
{
String lineValue = "(";
for (int i = 0; i < srcFields.size(); i++)
{
if (srcFields.get(i).equalsIgnoreCase(srcTimeFieldName))
{
lastTime = Conv.str2Str(row.get(srcFields.get(i)));
if(lastTime.length()>=7)
{
if (lastMonth.length() == 0)
{
lastMonth = lastTime.substring(0, 7);
}
// 跨月,下次再处理
if (!lastMonth.equalsIgnoreCase(lastTime.substring(0, 7)))
{
outtime = true;
break;
}
}
if (endDate.length() > 0)
{
// 必须用毫秒差呀，否则有错误
long diff = DateTime.diffms(lastTime, endDate);
if (diff >= 0)
{
outtime = true;
break;
}
}
}
if (row.get(srcFields.get(i)) == null)
{
lineValue += "null,";
}
else
{
lineValue += "'"+ Database.escape_mysql(row.get(srcFields.get(i)))+"',";
}
if (srcFields.get(i).equalsIgnoreCase(srcIncrPrimaryKey))
{
lastRowId = Conv.str2Int(row.get(srcFields.get(i)), 0);
}
}
lineValue += extendValues;
lineValue += "),";
if (outtime)
{
break;
}
else
{
lineValueList += lineValue;
pluginLoop(row);
count++;
}
}
values+=lineValueList;
values=StringUtil.removeLastChar(values, ",");
String dstFieldsString=Conv.ary2str(dstFields)+","+Conv.ary2str(destExtendFieldName);
String currDstTable=dstTable+lastMonth.replace("-", "");
// if (dstTable.endsWith("_"))
// {
// dstTable += lastMonth.replace("-", "");
// }
sql = "insert into "+currDstTable+"("+dstFieldsString+") values "+values;
pluginEnd();
if (count > 0)
{
try
{
Database.executeSQLWithThrow(this.dstConn, sql);
executeSuc = true;
}
catch (Exception e)
{
count = -1;
System.out.println(sql);
e.printStackTrace();
}
if (executeSuc)
{
sql = "select count(*) from "+srcTableSyncLastId;
int cnt = Database.getInt(this.srcConn, sql);
if (cnt == 0)
{
sql = "insert into "+srcTableSyncLastId+"(last_id,last_time) values("+
lastRowId+
",'"+lastTime+
"')";
}
else
{
sql = "update "+srcTableSyncLastId+" " +
" set last_id="+lastRowId+"," +
"last_time='"+lastTime+"'";
}
Database.executeSQL(this.srcConn, sql);
}
}
}
CombineResult result = new CombineResult();
result.setSuc(executeSuc);
result.setRows(count);
result.setLastId(lastRowId);
result.setLastTime(lastTime);
return result;
}
protected void pluginBegin()
{
}
protected void pluginLoop(Map<String, String> row)
{
}
protected void pluginEnd()
{
}
}
6 更新策略的类
package waf.db.combine;
import java.util.ArrayList;
import java.util.HashMap;
import waf.convert.Conv;
import waf.datatype.DateTime;
import waf.db.Database;
import waf.db.combine.util.CombineResult;
import waf.lang.StringUtil;
/**
*
* @author waf.wang
*
*/
public class CombineUpdateStrategy extends BaseCombineStrategy
{
protected String foreignKey="";
protected boolean ifCheckExists=false;
protected int waitSec=5;
@Override
public CombineResult execute()
{
int count=0;
String sql="";
//sql="select order_time from "+dstTable+" where from_server=";
sql="select last_id from "+srcTableSyncLastId;
long lastRowId=Database.getLong(this.srcConn, sql);
String lastTime="";
sql="select "+srcTimeFieldName+
" from "+srcTable+" \n" +
" where 1=1 " +
" and "+srcIncrPrimaryKey+"<="+lastRowId+
" order by id desc" +
" limit 1";
String startTime=Database.getString(this.srcConn, sql);
sql="select "+foreignKey+",";
sql+=Conv.ary2str(srcFields);
sql+=" from "+srcTable+" \n" +
" where 1=1 " +
" and "+srcIncrPrimaryKey+">"+lastRowId+
" and "+srcTimeFieldName+">='"+startTime+"'"+
" "+filter+
" order by "+srcIncrPrimaryKey+
" limit "+pagesize;
ArrayList<HashMap<String,String>> rows=Database.getRows(srcConn, sql);
String values="";
Database db2=new Database(this.dstConn);
for (HashMap<String,String> row : rows)
{
if(ifCheckExists)
{
int sec=0;
while(true)
{
sql="select "+srcIncrPrimaryKey+" from "+dstTable+" where "+foreignKey+"='"+row.get(foreignKey)+"'";
int existsId=Database.getInt(this.dstConn, sql);
if(existsId>0)
{
break;
}
else
{
System.out.println(dstTable+" "+foreignKey+" "+row.get(foreignKey)+" not exists! wait for it");
}
sec++;
if(sec>=waitSec)
{
break;
}
waf.lang.Thread.sleep(1000);
}
}
boolean outtime=false;
String setValue="";
for (int i = 0; i < dstFields.size(); i++)
{
if(srcFields.get(i).equalsIgnoreCase(srcTimeFieldName))
{
lastTime=Conv.str2Str(row.get(srcFields.get(i)));
String currMonth=lastTime.substring(0,7);
if(endDate.length()>0)
{
// 必须用毫秒差呀，否则有错误
long diff=DateTime.diffms(lastTime, endDate);
if(diff>=0)
{
outtime=true;
break;
}
}
}
if(dstFields.get(i)!=null && row.get(srcFields.get(i))!=null)
{
setValue+=dstFields.get(i)+"='"+Database.escape_mysql(row.get(srcFields.get(i)))+"',";
}
if(srcFields.get(i).equalsIgnoreCase(srcIncrPrimaryKey))
{
lastRowId=Conv.str2Int(row.get(srcFields.get(i)),0);
}
}
if(outtime)
{
break;
}
else
{
if(setValue.length()>0)
{
String currDstTable=dstTable;
if(currDstTable.endsWith("_"))
{
currDstTable+=lastTime.substring(0,7).replace("-", "");
}
sql="update "+currDstTable +" set ";
sql+=setValue;
sql=StringUtil.removeLastChar(sql, ",");
sql+=" where 1=1 and "+foreignKey+"='"+row.get(foreignKey)+"'";
db2.addBatch(sql);
count++;
}
}
}
CombineResult result=new CombineResult();
if(count>0)
{
boolean executeSuc=false;
try
{
db2.executeBatchWithThrow();
//Database.executeSQLWithThrow(this.dstConn, sql);
executeSuc=true;
}
catch (Exception e)
{
count=-1;
System.out.println(sql);
e.printStackTrace();
}
if(executeSuc)
{
sql="update "+srcTableSyncLastId+" set last_id="+lastRowId+",last_time='"+lastTime+"'";
Database.executeSQL(this.srcConn, sql);
}
result.setSuc(executeSuc);
}
result.setRows(count);
result.setLastId(lastRowId);
result.setLastTime(lastTime);
return result;
}
public void setForeignKey(String foreignKey)
{
this.foreignKey=foreignKey;
}
public String getForeignKey()
{
return foreignKey;
}
public void setCheckExists(boolean ifCheckExists)
{
this.ifCheckExists = ifCheckExists;
}
public int getWaitSec()
{
return waitSec;
}
public void setWaitSec(int waitSec)
{
this.waitSec = waitSec;
}
}
370
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
