java 。sql脚本分割，excel生成sql脚本，excel分割

以你之姓@ 2023-02-27 14:59 5阅读 0赞

### 自己给自己写的工作工具类哈哈 ###

# 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