SweepawayExpiredDataInDBJob:
public class SweepawayExpiredDataInDBJob extends AbstractJob implements Job { // The key used to fetch period value from /rtms-batch/conf/dev/business.properties(local:D:/usr/local/rtms/batch/conf/business.properties) private static final String DELETE_TABLE_RESERVED_PERIOD_IN_MONTHS = "delete.table.reservedPeriodInMonths"; // The key used to fetch tables value from /rtms-batch/conf/dev/business.properties(local:D:/usr/local/rtms/batch/conf/business.properties) private static final String DELETE_TABLE_NAMES = "delete.table.names"; /** SQL実行クラス */ @Autowired private SqlSession session; @Autowired private BasicDataSource dataSource; // Used to get parammeter from business.xml private static PropertiesConfiguration config = BusinessConfigUtil.getConfiguration(); @Override public void doExecute(String[] args) { logger.info("DeleteExpiredDataInDBJob started."); long startTime = System.currentTimeMillis(); // Step 01:Get period start String periodStr=config.getString(DELETE_TABLE_RESERVED_PERIOD_IN_MONTHS); // If cannot find period,throw exception and exit if(periodStr==null){ String errMsg="Cannot find key:'delete.table.reservedPeriodInMonths' in file 'business.properties'."; logger.error(errMsg+"This is a fatal error and this program has to be terminated."); throw new BatchApplicationException(errMsg); } int period=0;// First important parameter try { period=Integer.parseInt(periodStr); }catch(NumberFormatException ex) { // If period is not a legal integer,throw exception and exit String errMsg="The value of key:'delete.table.reservedPeriodInMonths' should be a integer,but now it is '"+periodStr+"' !"; logger.error(errMsg+".This is a fatal error and this program has to be terminated."); throw new BatchApplicationException(errMsg ); } if(period<=0) { // If period is not bigger than Zero,throw exception and exit String errMsg="The value of key:'delete.table.reservedPeriodInMonths' should be bigger than Zero,but now it is '"+periodStr+"' !"; logger.error(errMsg+".This is a fatal error and this program has to be terminated."); throw new BatchApplicationException(errMsg ); } // Step 01 completed logger.info("Step 01.Got valid value of key:'delete.table.reservedPeriodInMonths', the value is '"+period+"'"); // Step 02:Get tableList start String tableNamesStr=config.getString(DELETE_TABLE_NAMES); // If cannot find table names,throw exception and exit if(tableNamesStr==null){ String errMsg="Cannot find key:'delete.table.names' in file 'business.properties'."; logger.error(errMsg+"This is a fatal error and this program has to be terminated."); throw new BatchApplicationException(errMsg); } int lengthOftableNamesStr=tableNamesStr.trim().length(); if(lengthOftableNamesStr==0){ // If no table in tableNamesStr,throw exception and exit String errMsg="The value of key:'delete.table.names' should contain at least a table name,but now it is '"+tableNamesStr+"' !"; logger.error(errMsg+".This is a fatal error and this program has to be terminated."); throw new BatchApplicationException(errMsg ); } // Split and find table names String[] arr=tableNamesStr.split(";"); List<String> tableList=new ArrayList<String>();// Second important parameter for(String tmp:arr) { String tableName=tmp.trim(); if(tableName.length()>0){ tableList.add(tableName); } } // Step 02 completed logger.info("Step 02.Got valid value of key:'delete.table.names', the count of table names is '"+tableList.size()+"' and the series of tables names is '"+String.join(",", tableList)+"'."); // Step 03:Get dateExpired(the date before N months) Start Date dateNow = new Date(); Date dateExpired = new Date(); Calendar calendar = Calendar.getInstance(); calendar.setTime(dateNow); calendar.add(Calendar.MONTH, -period); dateExpired = calendar.getTime(); String dateExpiredStr=getDateStrFrom(dateExpired); // Step 03 completed logger.info("Step 03.The expired date='"+dateExpiredStr+"',any record in specifed tables will be deleted if it's column:'CREATED_DATETIME'<='"+dateExpiredStr+"'."); // Step 04:Delete data // Initialize conn&stmt Connection conn=null; Statement stmt=null; try { conn=dataSource.getConnection(); stmt = conn.createStatement(); }catch(Exception ex) { // If DataSource is invalid,throw exception and exit String errMsg="DataSource is not ready!"; logger.error(errMsg+".This is a fatal error and this program has to be terminated."); throw new BatchApplicationException(errMsg ); } // Check the existence of every table List<String> notExistTableList=new ArrayList<String>(); for(String tableName:tableList) { // 0401:Check the existence of table String existSql="SELECT COUNT (*) as cnt FROM ALL_TABLES WHERE table_name = UPPER('"+tableName+"')"; try { ResultSet rs=stmt.executeQuery(existSql); while(rs.next()){ int countExist=rs.getInt("cnt"); if(countExist!=1) { notExistTableList.add(tableName); } } }catch(Exception ex) { String errMsg="When execute the sql:'"+existSql+"',the error:'"+ex.getMessage()+"' happened."; logger.error(errMsg+".This is a fatal error and this program has to be terminated."); throw new BatchApplicationException(errMsg ); } } if(notExistTableList.size()>0) { // Found some table not exist String errMsg="The tables:"+String.join(",", notExistTableList)+" do not exist!Please check them in value of key:'delete.table.names'"; logger.error(errMsg+".This is a fatal error and this program has to be terminated."); throw new BatchApplicationException(errMsg ); } logger.info("All tables do exist."); // Search & Delete for(String tableName:tableList) { String countSql="select count(*) as cnt from "+tableName+" where CREATED_DATETIME <='"+dateExpiredStr+"' "; String deleteSql="delete from "+tableName+" where CREATED_DATETIME <='"+dateExpiredStr+"' "; try { ResultSet rs=stmt.executeQuery(countSql); int countShouldBeDeleted=0; while(rs.next()){ countShouldBeDeleted=rs.getInt("cnt"); if(countShouldBeDeleted==0) { logger.info("There is no record in table '"+tableName+"' should be deleted!"); }else if(countShouldBeDeleted==1) { logger.info("There is "+countShouldBeDeleted+" record in table '"+tableName+"' should be deleted!"); }else { logger.info("There are "+countShouldBeDeleted+" records in table '"+tableName+"' should be deleted!"); } } if(countShouldBeDeleted>0) { int countHasBeenDeleted=stmt.executeUpdate(deleteSql); logger.info(countHasBeenDeleted+" records(expected:"+countShouldBeDeleted+") have been deleted from table:'"+tableName+"'."); }else { logger.info("No expired data in table:'"+tableName+"'."); } }catch(Exception ex) { String errMsg="When execute the sql:'"+countSql+" or "+deleteSql+"',the error:'"+ex.getMessage()+"' happened."; logger.error(errMsg+".This is a fatal error and this program has to be terminated."); throw new BatchApplicationException(errMsg ); } } try { stmt.close(); conn.close(); }catch(Exception ex) { String errMsg="DataSource statement/connection can't be closed!"; logger.error(errMsg+".This is a minor error and this program has to be continued."); } long endTime = System.currentTimeMillis(); double seconds=((double)endTime - (double)startTime)/1000; logger.info("Time elapsed：" + seconds + "s"); logger.info("DeleteExpiredDataInDBJob completed."); } public static String getDateStrFrom(Date date) throws BatchApplicationException{ try{ Format formatter = new SimpleDateFormat("yyyy-MM-dd"); return formatter.format(date); } catch(Exception ex){ throw new BatchApplicationException("Can't change date '"+date+"' to string using 'yyyy-MM-dd' fromat."); } } }
SweepawayExpiredDataInDBJob2
public class SweepawayExpiredDataInDBJob2 extends AbstractJob implements Job { // The key used to fetch period value from /rtms-batch/conf/dev/business.properties(local:D:/usr/local/rtms/batch/conf/business.properties) private static final String DELETE_TABLE_RESERVED_PERIOD_IN_MONTHS = "delete.table.reservedPeriodInMonths"; // The key used to fetch tables value from /rtms-batch/conf/dev/business.properties(local:D:/usr/local/rtms/batch/conf/business.properties) private static final String DELETE_TABLE_NAMES = "delete.table.names"; /** SQL実行クラス */ @Autowired private SqlSession session; @Autowired private BasicDataSource dataSource; // Used to get parammeter from business.xml private static PropertiesConfiguration config = BusinessConfigUtil.getConfiguration(); @Override public void doExecute(String[] args) { //session.commit(false); logger.info("DeleteExpiredDataInDBJob started."); long startTime = System.currentTimeMillis(); // Step 01:Get period start String periodStr=config.getString(DELETE_TABLE_RESERVED_PERIOD_IN_MONTHS); // If cannot find period,throw exception and exit if(periodStr==null){ String errMsg="Cannot find key:'delete.table.reservedPeriodInMonths' in file 'business.properties'."; logger.error(errMsg+"This is a fatal error and this program has to be terminated."); throw new BatchApplicationException(errMsg); } int period=0;// First important parameter try { period=Integer.parseInt(periodStr); }catch(NumberFormatException ex) { // If period is not a legal integer,throw exception and exit String errMsg="The value of key:'delete.table.reservedPeriodInMonths' should be a integer,but now it is '"+periodStr+"' !"; logger.error(errMsg+".This is a fatal error and this program has to be terminated."); throw new BatchApplicationException(errMsg ); } if(period<=0) { // If period is not bigger than Zero,throw exception and exit String errMsg="The value of key:'delete.table.reservedPeriodInMonths' should be bigger than Zero,but now it is '"+periodStr+"' !"; logger.error(errMsg+".This is a fatal error and this program has to be terminated."); throw new BatchApplicationException(errMsg ); } // Step 01 completed logger.info("Step 01.Got valid value of key:'delete.table.reservedPeriodInMonths', the value is '"+period+"'"); // Step 02:Get tableList start String tableNamesStr=config.getString(DELETE_TABLE_NAMES); // If cannot find table names,throw exception and exit if(tableNamesStr==null){ String errMsg="Cannot find key:'delete.table.names' in file 'business.properties'."; logger.error(errMsg+"This is a fatal error and this program has to be terminated."); throw new BatchApplicationException(errMsg); } int lengthOftableNamesStr=tableNamesStr.trim().length(); if(lengthOftableNamesStr==0){ // If no table in tableNamesStr,throw exception and exit String errMsg="The value of key:'delete.table.names' should contain at least a table name,but now it is '"+tableNamesStr+"' !"; logger.error(errMsg+".This is a fatal error and this program has to be terminated."); throw new BatchApplicationException(errMsg ); } // Split and find table names String[] arr=tableNamesStr.split(";"); List<String> tableList=new ArrayList<String>();// Second important parameter for(String tmp:arr) { String tableName=tmp.trim(); if(tableName.length()>0){ tableList.add(tableName); } } // Step 02 completed logger.info("Step 02.Got valid value of key:'delete.table.names', the count of table names is '"+tableList.size()+"' and the series of tables names is '"+String.join(",", tableList)+"'."); // Step 03:Get dateExpired(the date before N months) Start Date dateNow = new Date(); Date dateExpired = new Date(); Calendar calendar = Calendar.getInstance(); calendar.setTime(dateNow); calendar.add(Calendar.MONTH, -period); dateExpired = calendar.getTime(); String dateExpiredStr=getDateStrFrom(dateExpired); // Step 03 completed logger.info("Step 03.The expired date='"+dateExpiredStr+"',any record in specifed tables will be deleted if it's column:'CREATED_DATETIME'<='"+dateExpiredStr+"'."); // Step 04:Delete data // Initialize conn&stmt Connection conn=null; Statement stmt=null; try { conn=dataSource.getConnection(); stmt = conn.createStatement(); }catch(Exception ex) { // If DataSource is invalid,throw exception and exit String errMsg="DataSource is not ready!"; logger.error(errMsg+".This is a fatal error and this program has to be terminated."); throw new BatchApplicationException(errMsg ); } // Check the existence of every table List<String> notExistTableList=new ArrayList<String>(); for(String tableName:tableList) { // 0401:Check the existence of table String existSql="SELECT COUNT (*) as cnt FROM ALL_TABLES WHERE table_name = UPPER('"+tableName+"')"; try { ResultSet rs=stmt.executeQuery(existSql); while(rs.next()){ int countExist=rs.getInt("cnt"); if(countExist!=1) { notExistTableList.add(tableName); } } }catch(Exception ex) { String errMsg="When execute the sql:'"+existSql+"',the error:'"+ex.getMessage()+"' happened."; logger.error(errMsg+".This is a fatal error and this program has to be terminated."); throw new BatchApplicationException(errMsg ); } } if(notExistTableList.size()>0) { // Found some table not exist String errMsg="The tables:"+String.join(",", notExistTableList)+" do not exist!Please check them in value of key:'delete.table.names'"; logger.error(errMsg+".This is a fatal error and this program has to be terminated."); throw new BatchApplicationException(errMsg ); } logger.info("All tables do exist."); try { conn.setAutoCommit(false); // Delete int index=0; for(String tableName:tableList) { index++; //String countSql="select count(*) as cnt from "+tableName+" where CREATED_DATETIME <='"+dateExpiredStr+"' "; String deleteSql="delete from "+tableName+" where CREATED_DATETIME <='"+dateExpiredStr+"' "; int countHasBeenDeleted=stmt.executeUpdate(deleteSql); logger.info("No."+index+" "+countHasBeenDeleted+" records have been deleted from table:'"+tableName+"'."); } conn.commit(); }catch(Exception ex) { try { conn.rollback(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } //String errMsg="When execute the sql:'"+deleteSql+"',the error:'"+ex.getMessage()+"' happened."; //logger.error(errMsg+".This is a fatal error and this program has to be terminated."); //throw new BatchApplicationException(errMsg ); } try { stmt.close(); conn.close(); }catch(Exception ex) { String errMsg="DataSource statement/connection can't be closed!"; logger.error(errMsg+".This is a minor error and this program has to be continued."); } long endTime = System.currentTimeMillis(); double seconds=((double)endTime - (double)startTime)/1000; logger.info("Time elapsed：" + seconds + "s"); logger.info("DeleteExpiredDataInDBJob completed."); } public static String getDateStrFrom(Date date) throws BatchApplicationException{ try{ Format formatter = new SimpleDateFormat("yyyy-MM-dd"); return formatter.format(date); } catch(Exception ex){ throw new BatchApplicationException("Can't change date '"+date+"' to string using 'yyyy-MM-dd' fromat."); } } }
SweepawayExpiredDataInDB_InsertTestDataJob
class TypeField{ String type; String field; } /** * To insert test datum to 21 tables * * @date 19.10.02 */ @Component public class SweepawayExpiredDataInDB_InsertTestDataJob extends AbstractJob implements Job { @Autowired private BasicDataSource dataSource; static final int RecordCount=5000;// The count of records will be inserted to every table @Override public void doExecute(String[] args) { logger.info("DeleteExpiredInsertTestDataJob started."); long startTime = System.currentTimeMillis(); // Initialize conn&stmt Connection conn=null; Statement stmt=null; try { conn=dataSource.getConnection(); stmt = conn.createStatement(); }catch(Exception ex) { // If DataSource is invalid,throw exception and exit String errMsg="DataSource is not ready!"; logger.error(errMsg+".This is a fatal error and this program has to be terminated."); throw new BatchApplicationException(errMsg ); } // Pattern {"TableName","PK:INTERFACE_CODE","CH:","CH:","CH:","CH:","CH:","CH:CREATED_USER","DT:CREATED_DATETIME","CH:UPDATED_USER","DT:UPDATED_DATETIME"}, String[][] arr= {{"ACTUAL_GOODS_INFO","PK:TRACKING_NO","DT:FIRST_SCAN_DATETIME","CH:CREATED_USER","DT:CREATED_DATETIME","CH:UPDATED_USER","DT:UPDATED_DATETIME"}, {"ALLOCATION_CALC_DETAILS","PK:REQUEST_ID","CH:PLAN_AREA_CODE","CH:STEP_NO","DT:DELIVERY_DAY","CH:PROGRESS","CH:CREATED_USER","DT:CREATED_DATETIME","CH:UPDATED_USER","DT:UPDATED_DATETIME"}, {"ALLOCATION_CALC_STATUS","PK:REQUEST_ID","DT:DELIVERY_DAY","CH:LAP","CH:STATUS","CH:CREATED_USER","DT:CREATED_DATETIME","CH:UPDATED_USER","DT:UPDATED_DATETIME"}, {"ALLOCATION_REQ_DET_DEL","PK:SHIPPER_CODE","CH:ORDER_NO","DT:DELIVERY_DATE","CH:TEMPERATURE_CODE","CH:MATERIAL_CODE","CH:TRACKING_NO","CH:QUANTITY","CH:CREATED_USER","DT:CREATED_DATETIME","CH:UPDATED_USER","DT:UPDATED_DATETIME"}, {"ALLOCATION_REQUEST","PK:SHIPPER_CODE","CH:ORDER_NO","DT:DELIVERY_DATE","CH:DELIVERY_TIMESLOT_FROM","CH:DELIVERY_TIMESLOT_TO","CH:COLLECTION_DELIVERY_TYPE","CH:CARRIER_CODE","CH:DEPOT_CODE","CH:AREA_CODE","CH:RECEIVER_CODE","CH:RECEIVER_NAME","CH:RECEIVER_POSTAL_CODE","CH:RECEIVER_ADDRESS1","CH:LAP","CH:SENT_PASCO","CH:DOORSTEP_DELIVERY_FLG","CH:CUSTOMER_NOTIFICATION_TYPE","CH:CUSTOMER_NOTIFICATION_STATUS","CH:NAME_PLATE_FLG","CH:CREATED_USER","DT:CREATED_DATETIME","CH:UPDATED_USER","DT:UPDATED_DATETIME"}, {"ALLOCATION_REQUEST_DEL","PK:SHIPPER_CODE","CH:ORDER_NO","DT:DELIVERY_DATE","CH:DELIVERY_TIMESLOT_FROM","CH:DELIVERY_TIMESLOT_TO","CH:COLLECTION_DELIVERY_TYPE","CH:CARRIER_CODE","CH:DEPOT_CODE","CH:AREA_CODE","CH:RECEIVER_CODE","CH:RECEIVER_NAME","CH:RECEIVER_POSTAL_CODE","CH:RECEIVER_ADDRESS1","CH:LAP","CH:SENT_PASCO","CH:DOORSTEP_DELIVERY_FLG","CH:CUSTOMER_NOTIFICATION_TYPE","CH:CUSTOMER_NOTIFICATION_STATUS","CH:NAME_PLATE_FLG","CH:CREATED_USER","DT:CREATED_DATETIME","CH:UPDATED_USER","DT:UPDATED_DATETIME"}, {"ALLOCATION_REQUEST_DETAILS","PK:SHIPPER_CODE","CH:ORDER_NO","DT:DELIVERY_DATE","CH:TEMPERATURE_CODE","CH:MATERIAL_CODE","CH:TRACKING_NO","CH:QUANTITY","CH:CREATED_USER","DT:CREATED_DATETIME","CH:UPDATED_USER","DT:UPDATED_DATETIME"}, {"ALLOCATION_RESULT","PK:DELIVERY_LIST_ID","CH:CARRIER_CODE","CH:DEPOT_CODE","DT:DELIVERY_DATE","CH:PRINTED_TYPE","CH:DELIVERY_TIMERANGE_START","CH:DELIVERY_TIMERANGE_END","CH:CREATED_USER","DT:CREATED_DATETIME","CH:UPDATED_USER","DT:UPDATED_DATETIME"}, {"ALLOCATION_RESULT_DETAILS","PK:DELIVERY_LIST_ID","CH:SHIPPER_CODE","CH:ORDER_NO","DT:DELIVERY_DATE","CH:CREATED_USER","DT:CREATED_DATETIME","CH:UPDATED_USER","DT:UPDATED_DATETIME"}, {"COMPACT_ALLOCATION_REQUEST","PK:SHIPPER_CODE","CH:REQUEST_TYPE","CH:ORDER_NO","CH:TRACKING_NO","DT:TARGET_DATE","CH:TARGET_TIME_FROM","CH:TARGET_TIME_TO","CH:QUANTITY","CH:CUSTOMER_NAME","CH:CUSTOMER_POSTAL_CODE","CH:CUSTOMER_DELIVERY_ADDRESS","CH:CUSTOMER_TELEPHONE_NO","CH:REQUEST_STATUS","CH:TEMPERATURE_CODE","CH:MATERIAL_CODE","CH:OKIHAI_FLG","CH:CREATED_USER","DT:CREATED_DATETIME","CH:UPDATED_USER","DT:UPDATED_DATETIME"}, {"DATA_IO_HISTORY","PK:INTERFACE_CODE","CH:CONNECT_POINT_CODE","CH:CONNECT_POINT_DETAIL_CODE","DT:DELIVERY_DATE","CH:IO_STATUS","CH:IO_COUNT1","CH:CREATED_USER","DT:CREATED_DATETIME","CH:UPDATED_USER","DT:UPDATED_DATETIME"}, {"DELIVERY_HISTORY","PK:DELIVERY_LIST_ID","CH:VEHICLE_ID","CH:SHIPPER_CODE","CH:ORDER_NO","DT:DELIVERY_DATE","CH:LAP_COUNT","DT:INPUT_DATE","CH:STATUS_CODE","CH:DELIVERY_ORDER","CH:INPUT_FUNCTION_TYPE","CH:MAIL_SEND_FLG","CH:DISABLED_FLG","CH:TRACKING_NO","CH:REASON_CODE","CH:SHIPPER_NAME","CH:SHORT_SHIPPER_NAME","CH:DEPOT_CODE","CH:STATUS_NAME_MOBILE","CH:CARRIER_CODE","CH:CARRIER_NAME","CH:SHORT_CARRIER_NAME","CH:MATERIAL_CODE","CH:POSTABLE_FLG","CH:DELIVERED_FLG","CH:RECEIVER_ADDRESS1","CH:RECEIVER_POSTAL_CODE","CH:RECEIVER_NAME","CH:TOTAL_QUANTITY","CH:TEMPERATURE_CODE","CH:DOORSTEP_DELIVERY_FLG","CH:CREATED_USER","DT:CREATED_DATETIME","CH:UPDATED_USER","DT:UPDATED_DATETIME"}, {"DELIVERY_INFO","PK:TRACKING_NO","CH:SITE_TYPE","CH:SITE_ORDER_NO","CH:STATUS_CODE","CH:REASON_CODE","CH:COLLECTION_DELIVERY_TYPE","CH:SHIPPER_CODE","CH:SHIPPER_NAME","CH:CARRIER_CODE","CH:CARRIER_NAME","CH:DEPOT_CODE","CH:DEPOT_NAME","CH:LAP_COUNT","CH:CREATED_USER","DT:CREATED_DATETIME","CH:UPDATED_USER","DT:UPDATED_DATETIME"}, {"DELIVERY_INFO_HISTORY","PK:PROCESS_TYPE","DT:PROCESS_DATETIME","DT:TRACKING_CREATE_DATETIME","CH:TRACKING_NO","CH:SITE_TYPE","CH:SITE_ORDER_NO","CH:STATUS_CODE","CH:REASON_CODE","CH:COLLECTION_DELIVERY_TYPE","CH:SHIPPER_CODE","CH:SHIPPER_NAME","CH:CARRIER_CODE","CH:CARRIER_NAME","CH:DEPOT_CODE","CH:DEPOT_NAME","CH:LAP_COUNT","CH:CREATED_USER","DT:CREATED_DATETIME","CH:UPDATED_USER","DT:UPDATED_DATETIME"}, {"DELIVERY_START_END","PK:DELIVERY_LIST_ID","CH:VEHICLE_ID","DT:DELIVERY_DATE","CH:LAP_COUNT","CH:DRIVER_CODE","CH:START_STOP_TYPE","CH:CREATED_USER","DT:CREATED_DATETIME","CH:UPDATED_USER","DT:UPDATED_DATETIME"}, {"DELIVERY_TRACKING_INFO_FILE","PK:SITE_TYPE","DT:LINKAGE_DATETIME","CH:FILE_OUTPUT_FLG","CH:FILE_SEND_FLG","CH:CREATED_USER","DT:CREATED_DATETIME","CH:UPDATED_USER","DT:UPDATED_DATETIME"}, {"DRIVER_AMOUNT","PK:CARRIER_CODE","CH:DRIVER_CODE","DT:DELIVERY_DATE","CH:LAP","CH:FUNCTION_TYPE","CH:AMOUNT","CH:DELIVERY_LIST_ID","CH:CREATED_USER","DT:CREATED_DATETIME","CH:UPDATED_USER","DT:UPDATED_DATETIME"}, {"DRIVER_AMOUNT_HISTORY","PK:CARRIER_CODE","CH:DRIVER_CODE","DT:DELIVERY_DATE","CH:LAP","CH:FUNCTION_TYPE","CH:AMOUNT","CH:CREATED_USER","DT:CREATED_DATETIME"}, {"GOODS_SCAN_HISTORY","PK:TRACKING_NO","DT:SCAN_DATETIME","CH:SCAN_LOCATION_CODE","CH:CREATED_USER","DT:CREATED_DATETIME","CH:UPDATED_USER","DT:UPDATED_DATETIME"}, {"ORDER_PAYMENT_DETAILS","PK:ORDER_PAYMENT_ID","DT:DELIVERY_DATE","CH:LAP","CH:ORDER_NO","CH:CARRIER_CODE","CH:DRIVER_CODE","CH:BILL_AMOUNT","CH:RECEIVER_PAYMENT_METHOD_TYPE","CH:VALID_FLG","CH:CREATED_USER","DT:CREATED_DATETIME","CH:UPDATED_USER","DT:UPDATED_DATETIME"}, {"REDELIVERY_REQUEST","PK:REDELIVERY_REQUEST_ID","CH:TRACKING_NO","CH:ORDER_NO","CH:SHIPPER_CODE","DT:REDELIVERY_DATE","CH:BOX_FLG","CH:REDELIVERY_COUNT","CH:SELF_PICK_UP_FLG","CH:MAIL_SEND_FLG","CH:DOORSTEP_DELIVERY_FLG","CH:NAME_PLATE_FLG","CH:REGISTER_FLG","CH:CUSTOMER_NOTIFICATION_TYPE","CH:CREATED_USER","DT:CREATED_DATETIME","CH:UPDATED_USER","DT:UPDATED_DATETIME"}, }; logger.info("There are "+arr.length+" tables will be changed."); // Clear tables before insert for(String[] innerArr:arr) { String tableName=innerArr[0]; String deleteSql="delete from "+tableName+" where 1=1 "; try { int countHasBeenDeleted=stmt.executeUpdate(deleteSql); logger.info(countHasBeenDeleted+" records deleted from table:'"+tableName+"' after executed the sql:'"+deleteSql+"'."); }catch(Exception ex) { String errMsg="When execute the sql:'"+deleteSql+" or "+"',the error:'"+ex.getMessage()+"' happened."; logger.error(errMsg+".This is a fatal error and this program has to be terminated."); throw new BatchApplicationException(errMsg ); } } // Insert Data to tables for(String[] innerArr:arr) { String tableName=innerArr[0]; List<TypeField> typefields=new ArrayList<TypeField>(); for(int j=1;j<innerArr.length;j++) { String temp=innerArr[j]; String[] arrTmp=temp.split(":"); TypeField tf=new TypeField(); tf.type=arrTmp[0]; tf.field=arrTmp[1]; typefields.add(tf); } int insertedActual=0; for(int i=0;i<RecordCount;i++) { StringBuilder sb=new StringBuilder(); sb.append("insert into "+tableName+"("); List<String> fields=new ArrayList<String>(); for(TypeField tf:typefields) { fields.add(tf.field); } sb.append(String.join(",",fields)); sb.append(") values("); List<String> values=new ArrayList<String>(); for(TypeField tf:typefields) { if(tf.type.equals("PK")) { values.add("'"+String.valueOf(i)+"'"); }else if(tf.type.equals("CH")) { values.add("'0'"); }else if(tf.type.equals("DT")) { values.add("'2018-10-03'"); } } sb.append(String.join(",",values)); sb.append(")"); String insertSql=sb.toString(); try { insertedActual+=stmt.executeUpdate(insertSql); }catch(Exception ex) { String errMsg="When execute the sql:'"+insertSql+" or "+"',the error:'"+ex.getMessage()+"' happened."; logger.info(errMsg+"This is a minor error and this program will be continued."); //throw new BatchApplicationException(errMsg ); } } logger.info(insertedActual+" records(expected:"+RecordCount+") have been inserted to table:'"+tableName+"'."); } // Close DB connection before exit try { stmt.close(); conn.close(); }catch(Exception ex) { String errMsg="DataSource statement/connection can't be closed!"; logger.error(errMsg+".This is a minor error and this program has to be continued."); } long endTime = System.currentTimeMillis(); double seconds=((double)endTime - (double)startTime)/1000; logger.info("Time elapsed：" + seconds + "s"); logger.info("DeleteExpiredInsertTestDataJob completed."); } }
SweepawayExpiredDataInDb_InsertTestDataJob2
@Component public class SweepawayExpiredDataInDb_InsertTestDataJob2 extends AbstractJob implements Job { @Autowired private BasicDataSource dataSource; private static final int RECORD_COUNT=10000; @Override public void doExecute(String[] args) { int recordCount=RECORD_COUNT;// The count of records will be inserted to every table logger.info("DeleteExpiredInsertTestDataJob started."); long startTime = System.currentTimeMillis(); // Initialize conn&stmt Connection conn=null; Statement stmt=null; try { conn=dataSource.getConnection(); stmt = conn.createStatement(); }catch(Exception ex) { // If DataSource is invalid,throw exception and exit String errMsg="DataSource is not ready!"; logger.error(errMsg+".This is a fatal error and this program has to be terminated."); throw new BatchApplicationException(errMsg ); } // Pattern {"TableName","PK:INTERFACE_CODE","CH:","CH:","CH:","CH:","CH:","CH:CREATED_USER","DT:CREATED_DATETIME","CH:UPDATED_USER","DT:UPDATED_DATETIME"}, String[][] arr= {{"ACTUAL_GOODS_INFO","PK:TRACKING_NO","DT:FIRST_SCAN_DATETIME","CH:CREATED_USER","DT:CREATED_DATETIME","CH:UPDATED_USER","DT:UPDATED_DATETIME"}, {"ALLOCATION_CALC_DETAILS","PK:REQUEST_ID","CH:PLAN_AREA_CODE","CH:STEP_NO","DT:DELIVERY_DAY","CH:PROGRESS","CH:CREATED_USER","DT:CREATED_DATETIME","CH:UPDATED_USER","DT:UPDATED_DATETIME"}, {"ALLOCATION_CALC_STATUS","PK:REQUEST_ID","DT:DELIVERY_DAY","CH:LAP","CH:STATUS","CH:CREATED_USER","DT:CREATED_DATETIME","CH:UPDATED_USER","DT:UPDATED_DATETIME"}, {"ALLOCATION_REQ_DET_DEL","PK:SHIPPER_CODE","CH:ORDER_NO","DT:DELIVERY_DATE","CH:TEMPERATURE_CODE","CH:MATERIAL_CODE","CH:TRACKING_NO","CH:QUANTITY","CH:CREATED_USER","DT:CREATED_DATETIME","CH:UPDATED_USER","DT:UPDATED_DATETIME"}, {"ALLOCATION_REQUEST","PK:SHIPPER_CODE","CH:ORDER_NO","DT:DELIVERY_DATE","CH:DELIVERY_TIMESLOT_FROM","CH:DELIVERY_TIMESLOT_TO","CH:COLLECTION_DELIVERY_TYPE","CH:CARRIER_CODE","CH:DEPOT_CODE","CH:AREA_CODE","CH:RECEIVER_CODE","CH:RECEIVER_NAME","CH:RECEIVER_POSTAL_CODE","CH:RECEIVER_ADDRESS1","CH:LAP","CH:SENT_PASCO","CH:DOORSTEP_DELIVERY_FLG","CH:CUSTOMER_NOTIFICATION_TYPE","CH:CUSTOMER_NOTIFICATION_STATUS","CH:NAME_PLATE_FLG","CH:CREATED_USER","DT:CREATED_DATETIME","CH:UPDATED_USER","DT:UPDATED_DATETIME"}, {"ALLOCATION_REQUEST_DEL","PK:SHIPPER_CODE","CH:ORDER_NO","DT:DELIVERY_DATE","CH:DELIVERY_TIMESLOT_FROM","CH:DELIVERY_TIMESLOT_TO","CH:COLLECTION_DELIVERY_TYPE","CH:CARRIER_CODE","CH:DEPOT_CODE","CH:AREA_CODE","CH:RECEIVER_CODE","CH:RECEIVER_NAME","CH:RECEIVER_POSTAL_CODE","CH:RECEIVER_ADDRESS1","CH:LAP","CH:SENT_PASCO","CH:DOORSTEP_DELIVERY_FLG","CH:CUSTOMER_NOTIFICATION_TYPE","CH:CUSTOMER_NOTIFICATION_STATUS","CH:NAME_PLATE_FLG","CH:CREATED_USER","DT:CREATED_DATETIME","CH:UPDATED_USER","DT:UPDATED_DATETIME"}, {"ALLOCATION_REQUEST_DETAILS","PK:SHIPPER_CODE","CH:ORDER_NO","DT:DELIVERY_DATE","CH:TEMPERATURE_CODE","CH:MATERIAL_CODE","CH:TRACKING_NO","CH:QUANTITY","CH:CREATED_USER","DT:CREATED_DATETIME","CH:UPDATED_USER","DT:UPDATED_DATETIME"}, {"ALLOCATION_RESULT","PK:DELIVERY_LIST_ID","CH:CARRIER_CODE","CH:DEPOT_CODE","DT:DELIVERY_DATE","CH:PRINTED_TYPE","CH:DELIVERY_TIMERANGE_START","CH:DELIVERY_TIMERANGE_END","CH:CREATED_USER","DT:CREATED_DATETIME","CH:UPDATED_USER","DT:UPDATED_DATETIME"}, {"ALLOCATION_RESULT_DETAILS","PK:DELIVERY_LIST_ID","CH:SHIPPER_CODE","CH:ORDER_NO","DT:DELIVERY_DATE","CH:CREATED_USER","DT:CREATED_DATETIME","CH:UPDATED_USER","DT:UPDATED_DATETIME"}, {"COMPACT_ALLOCATION_REQUEST","PK:SHIPPER_CODE","CH:REQUEST_TYPE","CH:ORDER_NO","CH:TRACKING_NO","DT:TARGET_DATE","CH:TARGET_TIME_FROM","CH:TARGET_TIME_TO","CH:QUANTITY","CH:CUSTOMER_NAME","CH:CUSTOMER_POSTAL_CODE","CH:CUSTOMER_DELIVERY_ADDRESS","CH:CUSTOMER_TELEPHONE_NO","CH:REQUEST_STATUS","CH:TEMPERATURE_CODE","CH:MATERIAL_CODE","CH:OKIHAI_FLG","CH:CREATED_USER","DT:CREATED_DATETIME","CH:UPDATED_USER","DT:UPDATED_DATETIME"}, {"DATA_IO_HISTORY","PK:INTERFACE_CODE","CH:CONNECT_POINT_CODE","CH:CONNECT_POINT_DETAIL_CODE","DT:DELIVERY_DATE","CH:IO_STATUS","CH:IO_COUNT1","CH:CREATED_USER","DT:CREATED_DATETIME","CH:UPDATED_USER","DT:UPDATED_DATETIME"}, {"DELIVERY_HISTORY","PK:DELIVERY_LIST_ID","CH:VEHICLE_ID","CH:SHIPPER_CODE","CH:ORDER_NO","DT:DELIVERY_DATE","CH:LAP_COUNT","DT:INPUT_DATE","CH:STATUS_CODE","CH:DELIVERY_ORDER","CH:INPUT_FUNCTION_TYPE","CH:MAIL_SEND_FLG","CH:DISABLED_FLG","CH:TRACKING_NO","CH:REASON_CODE","CH:SHIPPER_NAME","CH:SHORT_SHIPPER_NAME","CH:DEPOT_CODE","CH:STATUS_NAME_MOBILE","CH:CARRIER_CODE","CH:CARRIER_NAME","CH:SHORT_CARRIER_NAME","CH:MATERIAL_CODE","CH:POSTABLE_FLG","CH:DELIVERED_FLG","CH:RECEIVER_ADDRESS1","CH:RECEIVER_POSTAL_CODE","CH:RECEIVER_NAME","CH:TOTAL_QUANTITY","CH:TEMPERATURE_CODE","CH:DOORSTEP_DELIVERY_FLG","CH:CREATED_USER","DT:CREATED_DATETIME","CH:UPDATED_USER","DT:UPDATED_DATETIME"}, {"DELIVERY_INFO","PK:TRACKING_NO","CH:SITE_TYPE","CH:SITE_ORDER_NO","CH:STATUS_CODE","CH:REASON_CODE","CH:COLLECTION_DELIVERY_TYPE","CH:SHIPPER_CODE","CH:SHIPPER_NAME","CH:CARRIER_CODE","CH:CARRIER_NAME","CH:DEPOT_CODE","CH:DEPOT_NAME","CH:LAP_COUNT","CH:CREATED_USER","DT:CREATED_DATETIME","CH:UPDATED_USER","DT:UPDATED_DATETIME"}, {"DELIVERY_INFO_HISTORY","PK:PROCESS_TYPE","DT:PROCESS_DATETIME","DT:TRACKING_CREATE_DATETIME","CH:TRACKING_NO","CH:SITE_TYPE","CH:SITE_ORDER_NO","CH:STATUS_CODE","CH:REASON_CODE","CH:COLLECTION_DELIVERY_TYPE","CH:SHIPPER_CODE","CH:SHIPPER_NAME","CH:CARRIER_CODE","CH:CARRIER_NAME","CH:DEPOT_CODE","CH:DEPOT_NAME","CH:LAP_COUNT","CH:CREATED_USER","DT:CREATED_DATETIME","CH:UPDATED_USER","DT:UPDATED_DATETIME"}, {"DELIVERY_START_END","PK:DELIVERY_LIST_ID","CH:VEHICLE_ID","DT:DELIVERY_DATE","CH:LAP_COUNT","CH:DRIVER_CODE","CH:START_STOP_TYPE","CH:CREATED_USER","DT:CREATED_DATETIME","CH:UPDATED_USER","DT:UPDATED_DATETIME"}, {"DELIVERY_TRACKING_INFO_FILE","PK:SITE_TYPE","DT:LINKAGE_DATETIME","CH:FILE_OUTPUT_FLG","CH:FILE_SEND_FLG","CH:CREATED_USER","DT:CREATED_DATETIME","CH:UPDATED_USER","DT:UPDATED_DATETIME"}, {"DRIVER_AMOUNT","PK:CARRIER_CODE","CH:DRIVER_CODE","DT:DELIVERY_DATE","CH:LAP","CH:FUNCTION_TYPE","CH:AMOUNT","CH:DELIVERY_LIST_ID","CH:CREATED_USER","DT:CREATED_DATETIME","CH:UPDATED_USER","DT:UPDATED_DATETIME"}, {"DRIVER_AMOUNT_HISTORY","PK:CARRIER_CODE","CH:DRIVER_CODE","DT:DELIVERY_DATE","CH:LAP","CH:FUNCTION_TYPE","CH:AMOUNT","CH:CREATED_USER","DT:CREATED_DATETIME"}, {"GOODS_SCAN_HISTORY","PK:TRACKING_NO","DT:SCAN_DATETIME","CH:SCAN_LOCATION_CODE","CH:CREATED_USER","DT:CREATED_DATETIME","CH:UPDATED_USER","DT:UPDATED_DATETIME"}, {"ORDER_PAYMENT_DETAILS","PK:ORDER_PAYMENT_ID","DT:DELIVERY_DATE","CH:LAP","CH:ORDER_NO","CH:CARRIER_CODE","CH:DRIVER_CODE","CH:BILL_AMOUNT","CH:RECEIVER_PAYMENT_METHOD_TYPE","CH:VALID_FLG","CH:CREATED_USER","DT:CREATED_DATETIME","CH:UPDATED_USER","DT:UPDATED_DATETIME"}, {"REDELIVERY_REQUEST","PK:REDELIVERY_REQUEST_ID","CH:TRACKING_NO","CH:ORDER_NO","CH:SHIPPER_CODE","DT:REDELIVERY_DATE","CH:BOX_FLG","CH:REDELIVERY_COUNT","CH:SELF_PICK_UP_FLG","CH:MAIL_SEND_FLG","CH:DOORSTEP_DELIVERY_FLG","CH:NAME_PLATE_FLG","CH:REGISTER_FLG","CH:CUSTOMER_NOTIFICATION_TYPE","CH:CREATED_USER","DT:CREATED_DATETIME","CH:UPDATED_USER","DT:UPDATED_DATETIME"}, }; logger.info("There are "+arr.length+" tables may be changed."); // Clear tables before insert for(String[] innerArr:arr) { String tableName=innerArr[0]; String deleteSql="delete from "+tableName+" where 1=1 "; try { int countHasBeenDeleted=stmt.executeUpdate(deleteSql); logger.info(countHasBeenDeleted+" records deleted from table:'"+tableName+"' after executed the sql:'"+deleteSql+"'."); }catch(Exception ex) { String errMsg="When execute the sql:'"+deleteSql+" or "+"',the error:'"+ex.getMessage()+"' happened."; logger.error(errMsg+".This is a fatal error and this program has to be terminated."); throw new BatchApplicationException(errMsg ); } } // Insert Data to tables int index=0; // No of current tables for(String[] innerArr:arr) { index++; String tableName=innerArr[0]; List<TypeField> typefields=new ArrayList<TypeField>(); for(int j=1;j<innerArr.length;j++) { String temp=innerArr[j]; String[] arrTmp=temp.split(":"); TypeField tf=new TypeField(); tf.type=arrTmp[0]; tf.field=arrTmp[1]; typefields.add(tf); } // for save time if(tableName.equals("DELIVERY_INFO_HISTORY")) { recordCount=10;// DELIVERY_INFO_HISTORY's primary key limitation }else { recordCount=RECORD_COUNT; } int insertedActual=0; try { conn.setAutoCommit(false); for(int i=0;i<recordCount;i++) { StringBuilder sb=new StringBuilder(); sb.append("insert into "+tableName+"("); List<String> fields=new ArrayList<String>(); for(TypeField tf:typefields) { fields.add(tf.field); } sb.append(String.join(",",fields)); sb.append(") values("); List<String> values=new ArrayList<String>(); for(TypeField tf:typefields) { if(tf.type.equals("PK")) { values.add("'"+String.valueOf(i)+"'"); }else if(tf.type.equals("CH")) { values.add("'0'"); }else if(tf.type.equals("DT")) { values.add("'2018-10-03'"); } } sb.append(String.join(",",values)); sb.append(")"); String insertSql=sb.toString(); insertedActual+=stmt.executeUpdate(insertSql); } conn.commit(); }catch(Exception ex) { try { conn.rollback(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } //String errMsg="When execute the sql:'"+insertSql+" or "+"',the error:'"+ex.getMessage()+"' happened."; //logger.info(errMsg+"This is a minor error and this program will be continued."); //throw new BatchApplicationException(errMsg ); } logger.info("No."+index+" "+insertedActual+" records(expected:"+recordCount+") have been inserted to table:'"+tableName+"'."); } // Close DB connection before exit try { stmt.close(); conn.close(); }catch(Exception ex) { String errMsg="DataSource statement/connection can't be closed!"; logger.error(errMsg+".This is a minor error and this program has to be continued."); } long endTime = System.currentTimeMillis(); double seconds=((double)endTime - (double)startTime)/1000; logger.info("Time elapsed：" + seconds + "s"); logger.info("DeleteExpiredInsertTestDataJob completed."); } }
--END-- 19.10.4
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
