ETL数据库表同步框架
现实中,经常碰到对多个不同的表进行转移处理，并且转移的过程中还要进行合并或者其他操作。标准的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; } }
浙公网安备 33010602011771号