使用java备份和恢复SQLServer表数据

2024-01-01 22:49:44

需求

近来工作中遇到一个问题,内网办公系统中的数据需要导出到外网中进行查询,外网的数据库中还有一些表存储外网的数据,因此无法使用全库备份恢复功能来满足需求。即只从内网数据库中导出若干表的内容至外网数据库的对应表。

其他解决方案:使用SQL Server自身的导出SQL语句的方法其实也可以,但是涉及到几十个表,一一手工导出工作量较大。

因此自己写了个函数,将内网中的表数据导出,根据数据生成insert into 语句,然后使用批处理的方式导入到外网数据库中。

1.表结构的分析

生成insert语句时,不得不面对的是引号的使用,对于int等类型无需引号,但是对于char、text等必须加上引号,一个表动辄几十个字段,人工用数组等方式记录字段的类型、名称再进行处理,也比较耗费精力,尤其是数据表的结构时常还在变化,而且是另一个开发公司在维护,何时变化也不得而知。这里通过分析建表语句完成了自动的字段类型的对应,从而大大减轻了人工维护字段及其类型的工作量。

譬如建表语句如下(在SQL Server企业管理器中选中表,Ctrl+C即可得到),这里存放一个文件中:

CREATE TABLE [Import_BizDescrs] (
	[DescrId] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[DescrType] [char] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[DescrAt] [datetime] NULL ,
	[Descr] [text] COLLATE Chinese_PRC_CI_AS NULL ,
	[DescrBy] [char] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[BizId] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[SampId] [char] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[AssistTaskId] [char] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[CreatePerson] [char] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[CreateDate] [datetime] NULL ,
	[UpdatePerson] [char] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[UpdateDate] [datetime] NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

从以上文字中,做出如下处理,即可获得表名、字段名、字段类型以及附带的是否需要引号。

public static void exportTableDataByCreateSQL(String createSQLFileName){
    	ArrayList <String> fieldNames = new ArrayList<String>();
    	ArrayList <String> fieldtypes = new ArrayList<String>();
    	ArrayList <Boolean> needQuotationMark = new ArrayList<Boolean>();
    	String tableName = "";
    	String filePath = "C:\\importSQLs\\";
    	String tmpString;
    	try {
			BufferedReader reader = new BufferedReader(new FileReader(createSQLFileName));
			
			String line = reader.readLine();
			//从第一行获取表名			
			tableName = line.substring(line.indexOf("[")+1,line.indexOf("]"));;
			System.out.println(tableName) ;
			//后面获取其他信息
			while ((line = reader.readLine()) != null) {
				if(line.startsWith(")")){
					break;
				}
				tmpString = line.substring(line.indexOf("[")+1,line.indexOf("]"));
				fieldNames.add(tmpString);
				line = line.substring(line.indexOf("]")+1);
				tmpString = line.substring(line.indexOf("[")+1,line.indexOf("]"));
				fieldtypes.add(tmpString);	
				if(tmpString.contains("char")
						||tmpString.contains("text")
						||tmpString.contains("datetime")){
					needQuotationMark.add(true);
				}else{
					needQuotationMark.add(false);
				}
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
}

2.备份表

获取到完整的字段、字段属性后,就可以开始生成SQL文件了,对于值为null的数据,略去相应的插入语句,同时由于是全表全量导入,开始时增加了delete from tableName;的语句

全部代码如下:

public static void exportTableDataByCreateSQL(String createSQLFileName){
    	ArrayList <String> fieldNames = new ArrayList<String>();
    	ArrayList <String> fieldtypes = new ArrayList<String>();
    	ArrayList <Boolean> needQuotationMark = new ArrayList<Boolean>();
    	String tableName = "";
    	String filePath = "C:\\importSQLs\\";
    	String tmpString;
    	try {
			BufferedReader reader = new BufferedReader(new FileReader(createSQLFileName));			
			String line = reader.readLine();
			//从第一行获取表名			
			tableName = line.substring(line.indexOf("[")+1,line.indexOf("]"));;
			//后面获取其他信息
			while ((line = reader.readLine()) != null) {
				if(line.startsWith(")")){
					break;
				}
				tmpString = line.substring(line.indexOf("[")+1,line.indexOf("]"));
				fieldNames.add(tmpString);
				line = line.substring(line.indexOf("]")+1);
				tmpString = line.substring(line.indexOf("[")+1,line.indexOf("]"));
				fieldtypes.add(tmpString);	
				if(tmpString.contains("char")
						||tmpString.contains("text")
						||tmpString.contains("datetime")){
					needQuotationMark.add(true);
				}else{
					needQuotationMark.add(false);
				}
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	//开始生成insert语句
    	Connection conn;
		try {
			Class.forName("net.sourceforge.jtds.jdbc.Driver");
			conn = DriverManager.getConnection(GlobalVar.ConnURL,GlobalVar.ConnUser,GlobalVar.ConnPWD);
			Statement stmt = conn.createStatement();
	        ResultSet rs = stmt.executeQuery("SELECT * FROM " + tableName);     
	        FileWriter writer = new FileWriter(filePath + tableName+".lims2", false);
	        BufferedWriter bufferedWriter = new BufferedWriter(writer);	    
	        bufferedWriter.write("delete from "+tableName + ";");
	        bufferedWriter.newLine();
	        while (rs.next()) {
	        	String FieldsString="";
		        String ValuesString="";
	        	for(int i=0; i< needQuotationMark.size();i++){
	        		tmpString = rs.getString(fieldNames.get(i));	        		
	        		if(tmpString != null){
	        			if(FieldsString.equals("")){
	        				FieldsString += fieldNames.get(i);
	        				ValuesString += (needQuotationMark.get(i)?"'":"")+ tmpString.trim() +(needQuotationMark.get(i)?"'":"");
	        			}else{
	        				FieldsString += "," + fieldNames.get(i);
	        				ValuesString += "," + (needQuotationMark.get(i)?"'":"")+ tmpString.trim() + (needQuotationMark.get(i)?"'":"");
	        			}
	        		}
	        	}
	            bufferedWriter.write("insert into "+ tableName + "("+FieldsString+")  values("+ValuesString+");");
	            bufferedWriter.newLine();
	        }
	        bufferedWriter.close();
	        writer.close();
	        rs.close();
	        stmt.close();
	        conn.close();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}         	
    }

调用以上函数时,只需将保存建表语句的sql文件位置作为参数即可,在本项目中,生成的sql文件以.lims2结尾。

3.还原表

由于生成的表数据文件较多,这里写了个批处理文件,将导出的表数据文件和该批处理文件一起拷贝到外网服务器中,运行批处理即完成了导入操作。以后准备写个上传的页面,省去了远程桌面的麻烦。批处理文件如下:

osql -S 127.0.0.1 -U 用户名 -P 密码 -d lims2 -i Import_table1.lims2 -o Rst_Import_table1.log
osql -S 127.0.0.1 -U 用户名 -P 密码 -d lims2 -i import_table2.lims2 -o Rst_import_table2.log
osql -S 127.0.0.1 -U 用户名 -P 密码 -d lims2 -i Import_table3.lims2 -o Rst_Import_table3.log
osql -S 127.0.0.1 -U 用户名 -P 密码 -d lims2 -i import_table4.lims2 -o Rst_import_table4.log
osql -S 127.0.0.1 -U 用户名 -P 密码 -d lims2 -i import_table5.lims2 -o Rst_import_table5.log
........

结语

此方法适用于同步部分表而不是整库的情况,尤其是两个数据库无法直接通信,需要手工同步的场景。

文章来源:https://blog.csdn.net/bill_lee_sh_cn/article/details/135328112
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。