自己给自己写的工作工具类哈哈
sql脚本分割
超过2M的脚本分割为多个接近2M的脚本，并且自动生成回退脚本。
package script;
import java.io.*;
import java.text.SimpleDateFormat;
import java.util.Date;
public class CutSql {
public static void main(String[] args) throws IOException {
SimpleDateFormat sdfa = new SimpleDateFormat( "yyyyMMdd" );
String date=sdfa.format(new Date());
File origin=new File("F:\\mc数据\\t_password.sql");
try {
FileInputStream fis = new FileInputStream(origin);
BufferedReader bh = new BufferedReader(new InputStreamReader(fis));
String sql = bh.readLine();
int sqlLength = sql.length();//代码长度，用来判断字节数
int i=1;
int num=1;
FileWriter out=new FileWriter("F:\\mc数据\\ngucauth\\生产-auth-"+date+"-38-"+ num +"-insert-sql.txt");
FileWriter ROLLBACK=new FileWriter("F:\\mc数据\\auth\\生产-auth-"+date+"-38-"+ num +"-insert-ROLLBACK-sql.txt");
CutSql.getTop(out,"25","auth","insert");
CutSql.getTop(ROLLBACK,"25","auth","delete");
while (sql!= null) {
sql=sql+'\n';
out.write(sql);
String sqlRollback= getROLLBACK(sql);
ROLLBACK.write(sqlRollback);
sql = bh.readLine();
System.out.println(i+"条数据--");
i++;
if (sqlLength*i>1900*1024*num){//2m字节数
num++;
out.close();
ROLLBACK.close();
out=new FileWriter("F:\\mc数据\\auth\\生产-auth-"+date+"-38-"+ num +"-insert-sql.txt");
ROLLBACK=new FileWriter("F:\\mc数据\\auth\\生产-auth-"+date+"-38-"+ num +"-insert-ROLLBACK-sql.txt");
CutSql.getTop(out,"25","auth","insert");
CutSql.getTop(ROLLBACK,"25","auth","delete");
}
}
out.close();
ROLLBACK.close();
bh.close();
} catch (Exception e) {
System.out.println("错误");
}
}
public static void getTop(FileWriter fw,String ip,String table,String type){
try {
fw.write("/*\n");
fw.write(" *地 址： 192.168.168."+ip+":20000\n");
fw.write(" *库 名： "+table+"\n");
fw.write(" *操作类型： "+type+"\n");
fw.write(" *执行时间： 00:00\n");
fw.write("*/\n");
} catch (IOException e) {
e.printStackTrace();
}
}
public static String getROLLBACK(String sql) {
Integer begin = sql.indexOf("VALUES ('");
String sqlROLLBACK="";
if(begin>0) {
String sqlR = sql.substring(begin+9);
Integer end = sqlR.indexOf("',");
sqlROLLBACK = sql.substring(begin+9,begin+9+end);
}
String sqlString = "DELETE FROM `t_password` WHERE DBL_CEN_ID='"+sqlROLLBACK+"';\n";
return sqlString;
}
}
excel生成sql脚本
package update;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import java.io.FileInputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
/**
* sheet转脚本并分割
*/
public class UpdateSet {
public static FileWriter backupSet,rollbackSet,updateSet;
static FileWriter del;
static String yyyyMMdd,table,ngg;
public static void main(String[] args) {
InputStream inp;
try {
inp = new FileInputStream("F:\\脚本分割\\UpdateSet模板.xlsx");
Workbook wb =WorkbookFactory.create(inp);
Sheet sheet = wb.getSheetAt(0);
SimpleDateFormat sdfa = new SimpleDateFormat( "yyyyMMdd" );
yyyyMMdd=sdfa.format(new Date());
table = "t_ucp_orgainfo";
ngg = "ngguser";
backupSet = new FileWriter("F:\\脚本分割\\数据处理\\生产-"+ngg+"-"+yyyyMMdd+"-1-1-update-BACKUP-sql.txt");
rollbackSet =new FileWriter("F:\\脚本分割\\数据处理\\生产-"+ngg+"-"+yyyyMMdd+"-1-1-update-ROLLBACK-sql.txt");
updateSet =new FileWriter("F:\\脚本分割\\数据处理\\生产-"+ngg+"-"+yyyyMMdd+"-1-1-update-sql.txt");
CreateUpdate.topWrite(backupSet);
CreateUpdate.topWriteRemark(backupSet,"27",ngg,"insert");
CreateUpdate.topWrite(rollbackSet);
CreateUpdate.topWriteRemark(rollbackSet,"27",ngg,"update");
CreateUpdate.topWrite(updateSet);
CreateUpdate.topWriteRemark(updateSet,"27",ngg,"update");
int num = 1;
for(int i=1;i<sheet.getLastRowNum()+1;i++){
Row row = sheet.getRow(i);
if (24*i>190*1024*num){
num++;
backupSet.close();
rollbackSet.close();
updateSet.close();
backupSet = new FileWriter("F:\\脚本分割\\数据处理\\生产-"+ngg+"-"+yyyyMMdd+"-"+ num +"-1-update-BACKUP-sql.txt");
rollbackSet =new FileWriter("F:\\脚本分割\\数据处理\\生产-"+ngg+"-"+yyyyMMdd+"-"+ num +"-1-update-ROLLBACK-sql.txt");
updateSet =new FileWriter("F:\\脚本分割\\数据处理\\生产-"+ngg+"-"+yyyyMMdd+"-"+ num +"-1-update-sql.txt");
CreateUpdate.topWrite(backupSet);
CreateUpdate.topWriteRemark(backupSet,"27",ngg,"insert");
CreateUpdate.topWrite(rollbackSet);
CreateUpdate.topWriteRemark(rollbackSet,"27",ngg,"update");
CreateUpdate.topWrite(updateSet);
CreateUpdate.topWriteRemark(updateSet,"27",ngg,"update");
}
backupSet.write(CreateUpdate.backup(row, yyyyMMdd,table));
rollbackSet.write(CreateUpdate.rollback(row, yyyyMMdd,table));
updateSet.write(CreateUpdate.updateSet(row, yyyyMMdd,table));
System.out.println("row"+i+" succeed");
}
//operateSheet(sheet);
backupSet.close();
rollbackSet.close();
updateSet.close();
} catch (Exception e) {
e.printStackTrace();
}finally{
}
}
}
package update;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import java.io.FileWriter;
import java.io.IOException;
import java.text.DecimalFormat;
import java.util.HashMap;
import java.util.Map;
public class CreateUpdate {
public static Map<String,Integer> address;
public void setLocation(String name,int id){
address.put(name, id);
}
public static void topWrite(FileWriter fw){
if(address==null){
address=new HashMap<String, Integer>();
address.put("DBL_CEN_ID", 0);
address.put("ORGACODE", 1);
address.put("SUPERORGACODE", 2);
address.put("ORGACODE2", 3);
address.put("SUPERORGACODE2", 4);
}
}
public static void topWriteRemark(FileWriter fw,String ip,String ngg,String type){
try {
fw.write("/*\n");
fw.write(" *地 址： 192.168.168."+ip+":20000\n");
fw.write(" *库 名： "+ngg+"\n");
fw.write(" *操作类型： "+type+"\n");
fw.write(" *执行时间： 00:00\n");
fw.write("*/\n");
} catch (IOException e) {
e.printStackTrace();
}
}
/*去掉字符串为空格的值*/
public static String removeNull(Row row,int id){
Cell cell=row.getCell(id);
if(cell==null){
return "";
}
if(CellType.NUMERIC.equals(cell.getCellTypeEnum())){
Double d=cell.getNumericCellValue();
return new DecimalFormat("#").format(d);
}
return cell.toString().replaceAll("\\s", "");
}
public static void sortCol(Sheet sheet){
int rowNum=sheet.getLastRowNum()+1;
}
public static void topWriteEnd(FileWriter fw) {
try {
fw.write("commit;\n");
} catch (IOException e) {
e.printStackTrace();
}
}
public static String backup(Row row ,String yyyyMMdd,String table) {
String sqlString=null;
String DBL_CEN_ID=removeNull(row,address.get("DBL_CEN_ID"));
sqlString = "INSERT INTO zxdba_bak.zxdba_"+yyyyMMdd+"_"+table+"_part SELECT * FROM "+table+" where DBL_CEN_ID='" + DBL_CEN_ID + "';\n";
return sqlString;
}
public static String rollback(Row row ,String yyyyMMdd,String table) {
String sqlString=null;
String DBL_CEN_ID=removeNull(row,address.get("DBL_CEN_ID"));
String ORGACODE=removeNull(row,address.get("ORGACODE"));
String SUPERORGACODE=removeNull(row,address.get("SUPERORGACODE"));
String set;
if(SUPERORGACODE == null || SUPERORGACODE == "") {
set= " set ORGACODE='"+ORGACODE+"'";
} else {
set = " set ORGACODE='"+ORGACODE+"' , SUPERORGACODE='"+SUPERORGACODE;
}
sqlString = "UPDATE "+table+set+"' WHERE DBL_CEN_ID='" + DBL_CEN_ID + "';\n";
return sqlString;
}
public static String updateSet(Row row ,String yyyyMMdd,String table) {
String sqlString=null;
String DBL_CEN_ID=removeNull(row,address.get("DBL_CEN_ID"));
String ORGACODE2=removeNull(row,address.get("ORGACODE2"));
String SUPERORGACODE2=removeNull(row,address.get("SUPERORGACODE2"));
String set;
if(SUPERORGACODE2 == null || SUPERORGACODE2 == "") {
set = " set ORGACODE='"+ORGACODE2+"'";
} else {
set = " set ORGACODE='"+ORGACODE2+"' , SUPERORGACODE='"+SUPERORGACODE2;
}
sqlString = "UPDATE "+table+set+"' WHERE DBL_CEN_ID='" + DBL_CEN_ID + "';\n";
return sqlString;
}
}
excel分割
package script;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.*;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;
/**
* sheet分割
*/
public class CutExcel {
public static Sheet sheet,sheeto;
public static FileWriter orgaInfo,delInfo;
public static Workbook wb,wbo;
public static void main(String[] args) {
InputStream inp;
FileOutputStream oup;
try {
SimpleDateFormat df = new SimpleDateFormat("yyyyMMdd");
String nowTime = df.format(new Date());
inp = new FileInputStream("F:\\脚本分割\\prsnBusiRelationTemplet0.xlsx");
oup = new FileOutputStream(new File("F:\\脚本分割\\prsnBusiRelationTemplet1.xlsx"));
wbo = new XSSFWorkbook();
sheeto = wbo.createSheet();
wb = WorkbookFactory.create(inp);
sheet = wb.getSheetAt(0);
Row row0 = sheeto.createRow(0);
row0.createCell(0);
row0.getCell(0).setCellValue("人员(必填)");
row0.createCell(1);
row0.getCell(1).setCellValue("业务(必填)");
row0.createCell(2);
row0.getCell(2).setCellValue("绑定类别(0:绑定，1:解绑）（必填）");
int num = 1;
for (int i = 1; i < sheet.getLastRowNum() + 1; i++) {
Map<String, String> rootMap = new HashMap<String, String>();
Row row = sheet.getRow(i);
String n0 = removeNull(row, 0);
String n1 = removeNull(row, 1);
String n2 = removeNull(row, 2);
Row rowo = sheeto.createRow(i-140*(num-1));
rowo.createCell(0);
rowo.getCell(0).setCellValue(n0);
rowo.createCell(1);
rowo.getCell(1).setCellValue(n1);
rowo.createCell(2);
rowo.getCell(2).setCellValue(n2);
System.out.println(i+" "+(i-140*(num-1)));
if(i>=140*num) {
wbo.write(oup);
oup.flush();
wbo.close();
wbo = new XSSFWorkbook();
sheeto = wbo.createSheet();
num++;
oup = new FileOutputStream(new File("F:\\脚本分割\\prsnBusiRelationTemplet"+num+".xlsx"));
Row rowc = sheeto.createRow(0);
rowc.createCell(0);
rowc.getCell(0).setCellValue("人员(必填)");
rowc.createCell(1);
rowc.getCell(1).setCellValue("业务(必填)");
rowc.createCell(2);
rowc.getCell(2).setCellValue("绑定(0:绑定，1:解绑）（必填）");
}
}
wbo.write(oup);
} catch (Exception e) {
e.printStackTrace();
}
}
/*去掉字符串为空格的值*/
public static String removeNull(Row row,int id){
Cell cell=row.getCell(id);
if(cell==null){
return "";
}
if(CellType.NUMERIC.equals(cell.getCellTypeEnum())){
Double d=cell.getNumericCellValue();
return new DecimalFormat("#").format(d);
}
return cell.toString().replaceAll("\\s", "");
}
}
具体代码在自己的github上，私有。哈哈哈
写给自己的。要能有收获，您就nb |
转播
