DataMigrationOperation.jsp
11.7 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
<%@ page language="java" contentType="text/html; charset=UTF-8" %>
<%@ page import="java.io.File" %>
<%@ page import="weaver.general.*" %>
<%@ page import="weaver.conn.RecordSet" %>
<%@ page import="weaver.conn.RecordSetTrans" %>
<%@ page import="com.alibaba.fastjson.JSONObject" %>
<%@ page import="weaver.upgradetool.dbupgrade.logger.DBUpgradeLogger" %>
<%@ page import="java.util.Map" %>
<%@ page import="java.io.FileWriter" %>
<%@ page import="weaver.upgradetool.dbupgrade.upgrade.*" %>
<%@ page import="java.sql.Connection" %>
<%@ page import="java.sql.PreparedStatement" %>
<%@ page import="java.util.List" %>
<%@ page import="java.util.Date" %>
<%@ page import="java.text.SimpleDateFormat" %>
<%@ include file="/system/upgradetoe9/MigrationOperation.jsp"%>
<%
String operation=request.getParameter("operation");
String mainsequence = Util.null2String(request.getParameter("mainsequence"));
DBUpgradeOperation dbupgrade = new DBUpgradeOperation();
if(operation==null){
operation="";
}
if("closetrigger".equalsIgnoreCase(operation)){//执行关闭触发器的操作
DBUpgradeOperation operationUtil=new DBUpgradeOperation();
operationUtil.closeDBTrigger();//执行关闭触发器操作
JSONObject json=new JSONObject();
json.put("status","success");
response.getWriter().write(json.toString());
return;
}else if("opentrigger".equalsIgnoreCase(operation)){
DBUpgradeOperation operationUtil=new DBUpgradeOperation();
operationUtil.openDBTrigger();//执行开启触发器操作
JSONObject json=new JSONObject();
json.put("status","success");
response.getWriter().write(json.toString());
return;
}else if("autoexecute".equalsIgnoreCase(operation)){
String logfilepath=GCONST.getRootPath()+"system" + File.separatorChar + "upgradetoe9" + File.separatorChar + "resource" + File.separatorChar +"logfile";
File file=new File(logfilepath);
if(!file.exists() && !file.isDirectory()){
file.mkdir();
}
JSONObject jsonObj = dbupgrade.executeTask(mainsequence);
out.print(jsonObj.toJSONString());
}else if("getActionExecuteInfo".equalsIgnoreCase(operation)){
DBUpgradeProcess process = new DBUpgradeProcess();
JSONObject jsonObj = process.getProcessDetailByMain(mainsequence);
out.print(jsonObj.toJSONString());
}else if("checkdbdate".equalsIgnoreCase(operation)){//迁移数据比对
String reportpath = GCONST.getRootPath() + "system" + File.separatorChar + "upgradetoe9" + File.separatorChar + "report" + File.separatorChar + "dbcheck.txt";//文件报告存储路径
DBUpgradeOperation dboperation=new DBUpgradeOperation();
StringBuffer stringbuffer=new StringBuffer();
String migrationtablespath=GCONST.getRootPath() + "system" + File.separatorChar + "upgradetoe9" + File.separatorChar +"resource" + File.separatorChar + "migration.properties";
Map<String,String> oldDBinfo=dboperation.getOldDBinfo();
Map<String,String> newDBinfo=dboperation.getNewDbInfo();
PropUtil tableprop=PropUtil.getInstance(migrationtablespath);
String tablestr=Util.null2String(tableprop.getValues("synctables"));
String exceptiontablestr=","+Util.null2String(tableprop.getValues("e9_exceptiontables")).toLowerCase()+",";//逃避检测的表
String[] checktables=tablestr.split(",");
FileWriter fw=null;
try {
File file = new File(reportpath);
if (file.exists()) {
file.delete();
}else{
File fileParent = file.getParentFile();
if(!fileParent.exists()){
fileParent.mkdir();
}
}
file.createNewFile();
fw = new FileWriter(file);
for(int i=0;i<checktables.length;i++){
String tablename=checktables[i];
if(exceptiontablestr.indexOf(","+tablename.toLowerCase()+",")>=0){
//如果表在例外中，则跳过这个表的检测
continue;
}
int oldrownum=0;
int newrownum=0;
if(oldDBinfo.containsKey(tablename)){
oldrownum=Util.getIntValue(oldDBinfo.get(tablename),0);
}
if(newDBinfo.containsKey(tablename)){
newrownum=Util.getIntValue(newDBinfo.get(tablename),0);
}
if(oldrownum>newrownum){
stringbuffer.append("表名："+tablename+":源数据库记录:"+oldrownum+" 目标数据库记录:"+newrownum+"<br/>");
fw.write("表名："+tablename+":源数据库记录:"+oldrownum+" 目标数据库记录:"+newrownum+"\r\n");
}
}
} catch (Exception e) {
DBUpgradeLogger.write2File(e+"");
}finally {
if(fw!=null){
try{
fw.flush();
fw.close();
}catch(Exception e){
DBUpgradeLogger.write2File("数据库对比文件失败" + e);
}
}
}
JSONObject json=new JSONObject();
if(stringbuffer.length()>0){
json.put("status","1");
stringbuffer.append("迁移数据检测结果存在差异，请确认数据库存在差异的表数据是否正确，如果不正确联系技术人员<br/>");
json.put("result",stringbuffer.toString());
}else{
json.put("status","0");
}
out.println(json.toJSONString());
}else if("analyzetable".equalsIgnoreCase(operation)){
RecordSet rs=new RecordSet();
String dbtype=rs.getDBType();
if(dbtype.equalsIgnoreCase("oracle")){
DBUpgradeLogger.write2File("开始分析数据库");
rs.execute("declare\n" +
"v_tName varchar(50);\n" +
"v_sqlanalyze varchar(500);\n" +
"v_num number;\n" +
"v_sql varchar(500);\n" +
"cursor c1 \n" +
"is\n" +
"select table_name from user_tables;\n" +
"begin\n" +
"open c1;\n" +
"loop\n" +
"fetch c1 into v_tName;\n" +
"if c1%found then\n" +
"\n" +
"v_sqlanalyze :='analyze table '||v_tName||' estimate statistics';\n" +
"execute immediate v_sqlanalyze;\n" +
"v_sql := 'select NUM_ROWS from user_tables where table_name =upper('''||v_tName||''')';\n" +
"\n" +
"execute immediate v_sql into v_num;\n" +
"dbms_output.put_line('表名： '||v_tName||' 行数： '||v_num);\n" +
"else\n" +
"exit;\n" +
"end if;\n" +
"end loop;\n" +
"end;");
}
Connection conn = null;
PreparedStatement pst = null;
PreparedStatement pst1 = null;
DBUtil dbUtil = new DBUtil();
conn = dbUtil.getSourceConnection();
String dbtypeold=dbUtil.getDBtype();
if("oracle".equalsIgnoreCase(dbtypeold)) {
try{
pst1 = conn.prepareStatement("declare\n" +
"v_tName varchar(50);\n" +
"v_sqlanalyze varchar(500);\n" +
"v_num number;\n" +
"v_sql varchar(500);\n" +
"cursor c1 \n" +
"is\n" +
"select table_name from user_tables;\n" +
"begin\n" +
"open c1;\n" +
"loop\n" +
"fetch c1 into v_tName;\n" +
"if c1%found then\n" +
"\n" +
"v_sqlanalyze :='analyze table '||v_tName||' estimate statistics';\n" +
"execute immediate v_sqlanalyze;\n" +
"v_sql := 'select NUM_ROWS from user_tables where table_name =upper('''||v_tName||''')';\n" +
"\n" +
"execute immediate v_sql into v_num;\n" +
"dbms_output.put_line('表名： '||v_tName||' 行数： '||v_num);\n" +
"else\n" +
"exit;\n" +
"end if;\n" +
"end loop;\n" +
"end;");
pst1.execute();
}catch(Exception e){
DBUpgradeLogger.write2File("分析数据出现错误"+e+"");
}finally{
try {
if(pst1!=null&& !pst1.isClosed()){
pst1.close();
}
if (conn != null && !conn.isClosed()) {
conn.close();
}
} catch (Exception e1) {
DBUpgradeLogger.write2File(e1.getMessage());
}
}
}
}else if("datamigration2".equalsIgnoreCase(operation)){//执行数据库二次迁移的操作
Date date=new Date();
RecordSet rs1=new RecordSet();
String dbtype=rs1.getDBType();
if(dbtype.equalsIgnoreCase("oracle")){
SimpleDateFormat format=new SimpleDateFormat("yyyyMMddHHmmss");
String dbname=format.format(date);
FileOperation fileOperation =new FileOperation();
List<String> tables=fileOperation.AnalyzeImpLogFile("ORA-06502");//获取数据错误的表
if(tables==null){//如果为空的话说明未上传
JSONObject jsonObject=new JSONObject();
jsonObject.put("status","3");//文件未上传
response.getWriter().print(jsonObject.toJSONString());
return;
}
UpgradeRecordSet rs=new UpgradeRecordSet();
StringBuffer tablestr=new StringBuffer();
for (int i=0;i<tables.size();i++){
try{
DBUpgradeLogger.write2File("导入出错的表名："+tables.get(i));
rs.executeUpdate(" alter table "+tables.get(i)+" rename to "+tables.get(i)+"bak");
}catch(Exception e){
continue;
}
if(tablestr.length()>0){
tablestr.append(","+tables.get(i));
}else{
tablestr.append(tables.get(i));
}
}
if(tablestr.length()>0){
fileOperation.doGenerateSecondExportFile("ecology_"+dbname,tablestr.toString());//生成二次导出文件
fileOperation.doGenerateSecondImportFile("ecology_"+dbname);//生成二次导入文件
JSONObject jsonObject=new JSONObject();
jsonObject.put("status","1");//日志检测中，有表数据出现问题，需要进行导入导出操作，不需要进行二次导入的做
response.getWriter().print(jsonObject.toJSONString());
return;
}else{
JSONObject jsonObject=new JSONObject();
jsonObject.put("status","0");//日志检测无差异，不需要进行二次导入的做
response.getWriter().print(jsonObject.toJSONString());
return;
}
}else{
JSONObject jsonObject=new JSONObject();
jsonObject.put("status","0");//sqlserver无需检测
response.getWriter().print(jsonObject.toJSONString());
return;
}
}
%>