从oracle中导出数据到access中，以mdb格式导出文件

àì夳堔傛蜴生んèń 2022-05-18 06:07 449阅读 0赞

# 从oracle中导出数据到access中，以mdb格式导出文件 #

2017年08月07日 14:35:36

阅读数：568

1.  `@Override`
2.  `public void exportMdbData(Invocation inv, Long taId, Paging page) throws Exception {`
3.   
4.  `//设置表名`
5.  `String tablename1="tb_09as0y4kyet2";`
6.   
7.  `// 空白mdb文件路径. 直接保存在src/cn/iwoo/dataexport/common/下.`
8.  `String blankMdbFilePath = "com/lzzj/prodma/controllers/archives/";`
9.  `// 空白mdb文件名`
10. `String blankMdbFileName = "123.mdb";`
11.  
12. `// 新mdb文件路径`
13. `String defaultSavedMdbFilePath = "com/lzzj/prodma/controllers/archives/";`
14. `// 新mdb文件名`
15. `String defaultSavedMdbFileName = "data.mdb";`
16. `// mdb文件后缀`
17. `String defaultSavedMdbFileExtension = ".mdb";`
18.  
19. `// 需要保存到的新的mdb文件路径和名`
20. `String savedMdbFilePathAndName = defaultSavedMdbFilePath + defaultSavedMdbFileName;`
21.  
22. `//将空白mdb文件拷贝到特定目录`
23. `InputStream is = this.getClass().getClassLoader().getResourceAsStream(blankMdbFilePath + blankMdbFileName);`
24. `savedMdbFilePathAndName=getClass().getClassLoader().getResource("/").getPath()+savedMdbFilePathAndName;`
25. `OutputStream out = new FileOutputStream(savedMdbFilePathAndName);`
26. `byte[] buffer = new byte[1024];`
27. `int numRead;`
28. `while ((numRead = is.read(buffer)) != -1) {`
29. `out.write(buffer, 0, numRead);`
30. `}`
31. `is.close();`
32. `out.close();`
33.  
34. `//开始从oracle取数据`
35. `Connection conn =null;`
36. `PreparedStatement ps=null;`
37. `ResultSet rs =null;`
38. `String createTableSql1="";//一个建表语句`
39. `Map map1=new HashMap();//一个map存一个表的字段`
40. `List<Map> list1 = new ArrayList<Map>();//一个list存一个表的记录`
41. `int mapSize1=0;//一个表的字段个数`
42. `String value="";//用于拼接sql`
43. `try{`
44.  
45. `Class.forName("oracle.jdbc.driver.OracleDriver");`
46. `conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.3.157:1521:ORCL", "pde", "pde");`
47. `map1=getCols(conn,ps,rs,tablename1);//map中存有该表中的所有字段`
48. `mapSize1=map1.size();`
49. `//拼接access中的建表sql语句`
50. `createTableSql1=createTableSql(tablename1,map1);//建表sql`
51.  
52. `String sql2="select * from "+tablename1;//提取ORACLE表数据的sql`
53. `ps = conn.prepareStatement(sql2);`
54. `rs = ps.executeQuery();`
55. `String aa="";`
56. `String bb="";`
57. `while (rs.next()) {`
58. `Map dateMap=new HashMap();//一条记录一个dateMap`
59. `for(int z=1;z<=mapSize1;z++){`
60. `aa=String.valueOf(map1.get(z+""));`
61. `bb=rs.getString(z)==null?"":rs.getString(z);`
62. `dateMap.put(aa, bb);`
63. `}`
64. `list1.add(dateMap);`
65. `}`
66. `//结束从oracle取数据`
67. `}catch(Exception e){`
68. `e.printStackTrace();`
69. `}finally{`
70. `// 关闭记录集`
71. `if (rs != null) {`
72. `try {`
73. `rs.close();`
74. `} catch (SQLException e) {`
75. `e.printStackTrace();`
76. `}`
77. `}`
78.  
79. `// 关闭声明`
80. `if (ps != null) {`
81. `try {`
82. `ps.close();`
83. `} catch (SQLException e) {`
84. `e.printStackTrace();`
85. `}`
86. `}`
87.  
88. `// 关闭链接对象`
89. `if (conn != null) {`
90. `try {`
91. `conn.close();`
92. `} catch (SQLException e) {`
93. `e.printStackTrace();`
94. `}`
95. `}`
96. `}`
97.  
98. `//打开对mdb文件的jdbc-odbc连接`
99. `Connection connection=null;`
100. `Statement statement=null;`
101. `try{`
102.  
103. `Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");`
104. `String database = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ="+ savedMdbFilePathAndName.substring(1).trim(); //, *.accdb`
105. `connection = DriverManager.getConnection(database);`
106. `statement = connection.createStatement();`
107.  
108. `statement.execute(createTableSql1); //创建ACCESS表sql`
109. `String insertSql="";`
110. `int listSize=list1.size();`
111. `//拼接access中的insert语句`
112. `for(int x=0;x<listSize;x++){`
113. `insertSql="insert into "+tablename1+" values(";`
114. `for(int k=1;k<=mapSize1;k++){`
115. `value=String.valueOf(list1.get(x).get(map1.get(k+"")));`
116. `if(k==mapSize1){`
117. `insertSql+="'"+value+"');";`
118. `}else{`
119. `insertSql+="'"+value+"',";`
120. `}`
121.  
122. `}`
123. `statement.execute(insertSql);`
124. `}`
125. `//1.设置文件ContentType类型，这样设置，会自动判断下载文件类型`
126. `inv.getResponse().setContentType("multipart/form-data");`
127. `//2.设置文件头：最后一个参数是设置下载文件名(假如我们叫data.mdb)`
128. `inv.getResponse().setHeader("Content-Disposition", "attachment;fileName="+"data.mdb");`
129. `File file = new File(savedMdbFilePathAndName);`
130. `OutputStream out1;`
131. `FileInputStream inputStream = new FileInputStream(file);`
132. `//3.通过response获取ServletOutputStream对象(out)`
133. `out1 = inv.getResponse().getOutputStream();`
134. `try {`
135.  
136. `int b = 0;`
137. `byte[] buffer1 = new byte[8192];`
138. `while (b != -1){`
139. `b = inputStream.read(buffer1);`
140. `//4.写到输出流(out)中`
141. `out1.write(buffer1,0,b);`
142. `}`
143.  
144. `} catch (IOException e) {`
145. `e.printStackTrace();`
146. `}finally{`
147. `out1.flush();`
148. `inputStream.close();`
149. `out1.close();`
150. `file.delete();`
151. `}`
152. `}catch(Exception e){`
153. `e.printStackTrace();`
154. `}finally{`
155. `// 关闭声明`
156. `if (statement != null) {`
157. `try {`
158. `statement.close();`
159. `} catch (SQLException e) {`
160. `e.printStackTrace();`
161. `}`
162. `}`
163.  
164. `//关闭连接`
165. `if (connection != null) {`
166. `try {`
167. `connection.close();`
168. `} catch (SQLException e) {`
169. `e.printStackTrace();`
170. `}`
171. `}`
172. `}`
173.  
174. `}`
175.  
176. `public Map getCols(Connection conn,PreparedStatement ps,ResultSet rs,String tableNmae) throws SQLException{//获取某张表中的所有字段`
177. `Map map=new HashMap();`
178.  
179. `String sql1="select COLUMN_NAME from user_tab_cols where table_name=upper('"+tableNmae+"') order by column_id";//获取该表中的所有字段`
180. `ps = conn.prepareStatement(sql1);`
181. `rs = ps.executeQuery();`
182.  
183. `int j=0;`
184.  
185. `while (rs.next()) {`
186. `j++;`
187. `map.put(j+"", rs.getString(1));//存表中的所有字段`
188. `}`
189.  
190. `return map;`
191. `}`
192.  
193. `public String createTableSql(String tableNmae,Map map){`
194. `String createTableSql="CREATE TABLE "+tableNmae+" ( ";`
195. `int mapSize=map.size();//字段个数`
196. `String value="";`
197. `for(int k=1;k<=mapSize;k++){`
198. `value=String.valueOf(map.get(k+""));`
199. `if(k==mapSize){`
200. `createTableSql+=value+" Memo );";`
201. `}else{`
202. `createTableSql+=value+" Memo ,";`
203. `}`
204.  
205. `}`
206. `return createTableSql;`
207. `}`

封装的方法比较笨，待优化

文章标签： [access][][oracle][]

[access]: http://so.csdn.net/so/search/s.do?q=access&t=blog
[oracle]: http://so.csdn.net/so/search/s.do?q=oracle&t=blog