需要更新一些不规范的时间格式，如将某个时间格式化为yy-MM-dd，实际上为 yy-MM-dd hh:mm:ss,并且需要提供回滚脚本。例如：规范化时间的脚本如下：
update test set test_date=substring(account_date,1,10) WHERE test_date>‘2017-06-01 00:00:00‘ AND test_date<‘2017-09-07 00:00:00‘这个脚本是ok，但执行时受到影响的行数如果有几百条甚至上千条记录的话，回滚脚本怎么写呢？
模板如下：
update test set test_date= ‘‘ where id=‘‘;1.首先，从test表中查出上述的参数：
select id,test_date from test where test_date>‘2017-06-01 00:00:00‘ AND test_date<‘2017-09-07 00:00:00‘将结果导入到文本中，名为为param.txt
2.然后运行以下java程序，打印出可执行的回滚脚本
private void insertData() throws IOException{
FileReader reader = new FileReader("D:\\document\\load\\data1.txt");
BufferedReader br = new BufferedReader(reader);
String updateSql="update test set test_date= ‘‘ where id=‘‘;";
String line = null;
Integer count=0;
String[] param=new String[2];
String regexp = "\‘\‘";
while((line = br.readLine()) != null) {
param=line.split("\\\t");
String first=updateSql.replaceFirst(regexp,"\‘"+param[1]+"\‘");
String second=first.replaceFirst(regexp,"\‘"+param[0]+"\‘");
System.out.println(second);
count++;
}
br.close();
reader.close();
}
3.如果行数较多，需要打印到文件中(Console打印的记录数受限的)
private void insertData() throws IOException{
FileReader reader = new FileReader("D:\\document\\load\\data1.txt");
BufferedReader br = new BufferedReader(reader);
StringBuffer sbf=new StringBuffer();
String updateSql="update test set test_date= ‘‘ where id=‘‘;";
String line = null;
Integer count=0;
String[] param=new String[2];
String regexp = "\‘\‘";
while((line = br.readLine()) != null) {
param=line.split("\\\t");
String first=updateSql.replaceFirst(regexp,"\‘"+param[1]+"\‘");
String second=first.replaceFirst(regexp,"\‘"+param[0]+"\‘");
//System.out.println(second);
sbf.append(second).append("\n");
count++;
}
writeFile("D:\\document\\load\\rollback.sql", sbf);
br.close();
reader.close();
}
public void writeFile(String fileName,StringBuffer stf)
throws IOException {
BufferedWriter writer = new BufferedWriter(new FileWriter(fileName));
writer.write(stf.toString());
writer.close();
}