1.jdbc工具类
复制代码
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
27import java.io.BufferedInputStream; import java.io.FileInputStream; import java.io.InputStream; import java.util.Properties; public class PropertyUtils { public static Properties getProp(String propName){ Properties prop = new Properties(); try{ //读取属性文件a.properties InputStream in = new BufferedInputStream (new FileInputStream(propName)); prop.load(in); ///加载属性列表 /* Iterator<String> it=prop.stringPropertyNames().iterator(); while(it.hasNext()){ String key=it.next(); System.out.println(key+":"+prop.getProperty(key)); }*/ //迭代获取key及value in.close(); } catch(Exception e){ e.printStackTrace(); } return prop; } }
2.单表迁移方法
复制代码
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
114package com.huateng.flowsharp; import java.math.BigDecimal; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.sql.Types; import java.util.LinkedHashMap; import java.util.Map; import java.util.Properties; import java.util.Set; import org.slf4j.Logger; import org.slf4j.LoggerFactory; public class 单表数据迁移 { private static final Logger logger = LoggerFactory.getLogger(单表数据迁移.class); private static Map<String, Object> table_Map = new LinkedHashMap<String, Object>(); private static String table; private static String id; public static void main(String[] args) throws SQLException { table = args[0]; id = args[1]; logger.info("表名为:" + table + " id为" + id + "数据迁移开始"); Connection conn = getConnection("zx_jdbc.properties"); loadData(conn); Connection conn1 = getConnection("jc_jdbc.properties"); writeData(conn1); logger.info("表名为:" + table + " id为" + id + "数据迁移结束"); } public static void loadData(Connection conn) throws SQLException { logger.info("reading is start..."); String sql = "SELECT * from " + table + " where id=?"; PreparedStatement prestat = conn.prepareStatement(sql); prestat.setString(1, id); ResultSet result = prestat.executeQuery(); ResultSetMetaData metaDatat = result.getMetaData(); int columnCount = metaDatat.getColumnCount(); while (result.next()) { for (int i = 1; i <= columnCount; i++) { String key = metaDatat.getColumnLabel(i); if (Types.VARCHAR == metaDatat.getColumnType(i)) { String value = result.getString(i); table_Map.put(key, value); } if (Types.NUMERIC == metaDatat.getColumnType(i)) { BigDecimal value = result.getBigDecimal(i); table_Map.put(key, value); } } } logger.info("reading is end..."); conn.close(); } public static void writeData(Connection conn) throws SQLException { logger.info("writing is start..."); String sql = "insert into " + table + " ("; Set<String> set = table_Map.keySet(); for (String key : set) { sql += key + ","; } sql = sql.substring(0, sql.length() - 1); sql += ") values ("; for (String key : set) { if (table_Map.get(key) instanceof String) { sql += "'" + table_Map.get(key) + "'" + ","; } else if (table_Map.get(key) instanceof BigDecimal) { sql += table_Map.get(key) + ","; } else { sql += null + ","; } sql = sql.trim(); } sql = sql.substring(0, sql.length() - 1) + ")"; PreparedStatement prestat = conn.prepareStatement(sql); prestat.execute(); logger.info("writing is end..."); conn.close(); } public static Connection getConnection(String jdbc) throws SQLException { Properties prop = PropertyUtils.getProp("src/test/java/config/" + jdbc); try { Class.forName(prop.getProperty("classDriver")); } catch (ClassNotFoundException e) { e.printStackTrace(); } String url = prop.getProperty("url"); String username = prop.getProperty("username"); String passwd = prop.getProperty("passwd"); return DriverManager.getConnection(url, username, passwd); } }
3.多表数据迁移
复制代码
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
179package com.huateng.flowsharp; import java.math.BigDecimal; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.sql.Types; import java.util.HashMap; import java.util.LinkedHashMap; import java.util.Map; import java.util.Properties; import java.util.Set; import org.slf4j.Logger; import org.slf4j.LoggerFactory; /** * 执行完后将trunct环境卖出申请记账和买入申请记账迁移到本地jc环境 * @author lichuang * */ public class 多表数据迁移 { private static final Logger logger = LoggerFactory.getLogger(多表数据迁移.class); private static String[] strArr = {"CPES_CLICK_DEAL_CONTRACT","CES_QUOTE_DEAL","CES_QUOTE_INFO" ,"CPES_CLICK_DEAL_DETAILS","DPC_DRAFT_INFO","HTES_DRAFT_INFO"}; private static Map<String,Object> contract_Map = new LinkedHashMap<String, Object>(); private static Map<String,Object> deal_Map = new LinkedHashMap<String, Object>(); private static Map<String,Object> quote_MAP = new LinkedHashMap<String, Object>(); private static Map<String,Object> details_Map = new LinkedHashMap<String, Object>(); private static Map<String,Object> draft_Map = new LinkedHashMap<String, Object>(); private static Map<String,Object> htestDraft_Map = new LinkedHashMap<String, Object>(); private static String contractId = null; public static void main(String[] args) throws SQLException { logger.info("批次id为"+args[0]+"开始克隆"); contractId = args[0]; Connection conn = getConnection("zx_jdbc.properties"); loadData(conn); Connection conn1 = getConnection("jc_jdbc.properties"); writeData(conn1); } //读取数据 public static void loadData(Connection conn) throws SQLException{ logger.info("reading is start..."); PreparedStatement prestat= null; Map map = null; for(String str:strArr){ String sql = null; if(str.equals(strArr[0])){ map = contract_Map; prestat= conn.prepareStatement("SELECT * from CPES_CLICK_DEAL_CONTRACT c where c.ID =?"); }else if(str.equals(strArr[1])){ map = deal_Map; prestat= conn.prepareStatement("SELECT * from CES_QUOTE_DEAL d where d.BUSS_CONTRACT_ID =?"); }else if(str.equals(strArr[2])){ map = quote_MAP; prestat= conn.prepareStatement("SELECT * from CES_QUOTE_INFO q where q.DEAL_ID in (SELECT d.ID from CES_QUOTE_DEAL d where d.BUSS_CONTRACT_ID =?)"); }else if(str.equals(strArr[3])){ map = details_Map; prestat= conn.prepareStatement("SELECT * from NBMS_ZX.CPES_CLICK_DEAL_DETAILS e where e.CONTRACT_ID =?"); }else if(str.equals(strArr[4])){ map = draft_Map; prestat= conn.prepareStatement("SELECT * from DPC_DRAFT_INFO d where d.ID in (SELECT e.DPC_DRAFT_ID from NBMS_ZX.CPES_CLICK_DEAL_DETAILS e where e.CONTRACT_ID =?)"); }else if(str.equals(strArr[5])){ map = htestDraft_Map; prestat= conn.prepareStatement("SELECT * from HTES_DRAFT_INFO h where h.DRAFT_NUMBER = (SELECT d.DRAFT_NUMBER from DPC_DRAFT_INFO d where d.ID in (SELECT e.DPC_DRAFT_ID from NBMS_ZX.CPES_CLICK_DEAL_DETAILS e where e.CONTRACT_ID =?))"); } prestat.setString(1, contractId); ResultSet result = prestat.executeQuery();; ResultSetMetaData metaDatat = result.getMetaData(); int columnCount = metaDatat.getColumnCount(); while(result.next()){ for (int i = 1; i <=columnCount; i++) { String key =metaDatat.getColumnLabel(i); if(Types.VARCHAR==metaDatat.getColumnType(i)){ String value = result.getString(i); map.put(key, value); }if(Types.NUMERIC==metaDatat.getColumnType(i)){ BigDecimal value = result.getBigDecimal(i); map.put(key, value); } } } } conn.close(); logger.info("reading is end..."); } //写数据 public static void writeData(Connection conn) throws SQLException{ logger.info("writing is start..."); PreparedStatement prestat= null; Map map = null; for(String str:strArr){ String sql = null; if(str.equals(strArr[0])){ sql = "insert into CPES_CLICK_DEAL_CONTRACT ("; map = contract_Map; }else if(str.equals(strArr[1])){ sql = "insert into CES_QUOTE_DEAL ("; map = deal_Map; }else if(str.equals(strArr[2])){ sql = "insert into CES_QUOTE_INFO ("; map = quote_MAP; }else if(str.equals(strArr[3])){ sql = "insert into CPES_CLICK_DEAL_DETAILS ("; map = details_Map; }else if(str.equals(strArr[4])){ sql = "insert into DPC_DRAFT_INFO ("; map = draft_Map; }else if(str.equals(strArr[5])){ sql = "insert into HTES_DRAFT_INFO ("; map = htestDraft_Map; } Set<String> set = map.keySet(); for(String key:set){ sql+=key+","; } sql=sql.substring(0,sql.length()-1); sql+=") values ("; for(String key:set){ if (map.get(key) instanceof String) { sql+="'"+map.get(key)+"'"+","; }else if(map.get(key) instanceof BigDecimal){ sql+=map.get(key)+","; }else{ sql+=null+","; } sql = sql.trim(); } sql = sql.substring(0, sql.length()-1)+")"; prestat= conn.prepareStatement(sql); prestat.execute(); } logger.info("writing is end..."); conn.close(); } //flowsharp/src/test/java/config/jc_jdbc.properties //获取jdbc连接 public static Connection getConnection(String jdbc) throws SQLException { Properties prop = PropertyUtils.getProp("src/test/java/config/"+jdbc); try { Class.forName(prop.getProperty("classDriver")); } catch (ClassNotFoundException e) { e.printStackTrace(); } String url = prop.getProperty("url"); String username = prop.getProperty("username"); String passwd = prop.getProperty("passwd"); return DriverManager.getConnection(url,username,passwd); } }
4.包含blob大字段数据迁移
复制代码
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
171package com.huateng.flowsharp; import java.io.BufferedOutputStream; import java.io.ByteArrayInputStream; import java.io.ByteArrayOutputStream; import java.io.File; import java.io.FileOutputStream; import java.io.ObjectInputStream; import java.io.ObjectOutputStream; import java.sql.Blob; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.sql.Statement; import com.huateng.flowsharp.inner.entity.Process; public class Blob处理类 { static String []str; static Blob blob ; public static void main(String[] args) throws Exception { String []str = {"f109f9d0bec44ef1a1ae21159862cfd4"}; for (String processId:str) { writeDate(processId); } } public static void writeDate(String processId) throws Exception { com.huateng.flowsharp.inner.entity.Process obj =null; try { Connection conn = getConnection(); Statement stat = conn.createStatement(); String sql = "SELECT * from FLOWSHARP_ZX.TBL_SERPROCESS s where s.PROCESS_ID = "+"'"+processId+"'"; stat.execute(sql); ResultSet result = stat.getResultSet(); getData(result); //ByteArrayOutputStream outStream = new ByteArrayOutputStream(); //FileOutputStream output = new FileOutputStream(new File("C:/Users/lichuang/Desktop/test.txt")); /*InputStream in = blob.getBinaryStream(); byte []data =new byte[4096]; int count = -1; while((count = in.read(data,0,4096))!=-1){ //output.write(data, 0, count); outStream.write(data, 0, count); } output.flush(); output.close();*/ //String content= new String(outStream.toByteArray(),"UTF8"); //String content = new String(blob.getBytes((long)1, (int)blob.length())); byte[] btArr =blob.getBytes((long)1, (int)blob.length()); ByteArrayInputStream bi = new ByteArrayInputStream(btArr); ObjectInputStream oi = new ObjectInputStream(bi); Process obj1 = (Process) oi.readObject(); bi.close(); oi.close(); result.close(); stat.close(); conn.close(); Connection conn1 = getConnection1(); Statement stat1 = conn1.createStatement(); String sql1 = "insert INTO FLOWSHARP_JC.TBL_SERPROCESS(TBL_SERPROCESS.PROCESS_ID, TBL_SERPROCESS.PROCESS_NAME, TBL_SERPROCESS.BIZ_ID, TBL_SERPROCESS.NODE_ID, TBL_SERPROCESS.NODE_NAME, TBL_SERPROCESS.PROCESS_BYTE, TBL_SERPROCESS.START_DATE, TBL_SERPROCESS.END_DATE, TBL_SERPROCESS.STARTED_USER, TBL_SERPROCESS.MODEL_ID, TBL_SERPROCESS.SUPER_PROCESS_ID, TBL_SERPROCESS.RUN_FLAG) VALUES"+ "("+"'"+str[0]+"'"+"," +"'"+str[1]+"'"+"," +"'"+str[2]+"'"+"," +"'"+str[3]+"'"+"," +"'"+str[4]+"'"+"," +"empty_blob()"+"," +"'"+str[5]+"'"+"," +"'"+str[7]+"'"+"," +"'"+str[8]+"'"+"," +"'"+str[9]+"'"+"," +"'"+str[10]+"'"+"," +"'"+str[11]+"'"+")"; stat1.execute(sql1); ResultSet rs =stat1.executeQuery("select * from FLOWSHARP_JC.TBL_SERPROCESS s where s.PROCESS_ID = "+"'"+processId+"'"+"for update"); if(rs.next()){ Blob b =rs.getBlob(6); ByteArrayOutputStream bo = new ByteArrayOutputStream(); ObjectOutputStream oo = new ObjectOutputStream(bo); oo.writeObject(obj1); byte[]bytes = bo.toByteArray(); bo.close(); oo.close(); BufferedOutputStream os = new BufferedOutputStream(b.setBinaryStream(0)); os.write(bytes); os.flush(); os.close(); stat1.close(); conn1.close(); } } catch (SQLException e) { e.printStackTrace(); } } public static void getData(ResultSet result) throws Exception{ ResultSetMetaData metaDatat = result.getMetaData(); int columnCount = metaDatat.getColumnCount(); for (int i = 1; i <=columnCount; i++) { if(i>1) System.out.print(","); System.out.print(metaDatat.getColumnLabel(i)); } System.out.println(); while(result.next()){ str = new String[columnCount]; for (int i = 1; i <=columnCount; i++) { if(i>1) System.out.print(","); if("PROCESS_BYTE".equals(metaDatat.getColumnLabel(i))){ blob =result.getBlob(i); }else{ str[i-1]=result.getString(i); } } System.out.println(); } } public static Connection getConnection() throws SQLException{ try { Class.forName("oracle.jdbc.driver.OracleDriver"); } catch (ClassNotFoundException e) { e.printStackTrace(); } String url = "jdbc:oracle:thin:@ip:port/service"; String username = "用户名1"; String passwd = "密码1"; return DriverManager.getConnection(url,username,passwd); } public static Connection getConnection1() throws SQLException{ try { Class.forName("oracle.jdbc.driver.OracleDriver"); } catch (ClassNotFoundException e) { e.printStackTrace(); } String url = "jdbc:oracle:thin:@ip:port/service"; String username = "用户名2"; String passwd = "密码2"; return DriverManager.getConnection(url,username,passwd); } }
最后
以上就是激情超短裙最近收集整理的关于数据迁移-jdbc的全部内容,更多相关数据迁移-jdbc内容请搜索靠谱客的其他文章。
本图文内容来源于网友提供,作为学习参考使用,或来自网络收集整理,版权属于原作者所有。
发表评论 取消回复