导出Oracle数据库中的表结构、存储过程、函数等
package com.demo.email.util;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileWriter;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
public class Demo3 {
public static void main(String[] args) throws Exception {
Demo3.init();
Demo3.test();
}
private static String SQL = "SELECT DBMS_METADATA.GET_DDL(U.OBJECT_TYPE, U.object_name), U.OBJECT_TYPE "
+ "FROM USER_OBJECTS U " + "where U.OBJECT_TYPE = 'TABLE' " + "or U.OBJECT_TYPE = 'VIEW' "
+ "or U.OBJECT_TYPE = 'INDEX' " + "or U.OBJECT_TYPE = 'PROCEDURE' " + "or U.OBJECT_TYPE = 'SEQUENCE' "
+ "or U.OBJECT_TYPE = 'TRIGGER' " + "or U.OBJECT_TYPE = 'FUNCTION' " + "order by U.OBJECT_TYPE desc";
private static final String TYPE_MARK = "-1";
private static String username = "user1"; // 用户名
// 获得连接对象
public static void init() {
try {
Class.forName(driver);
conn1 = DriverManager.getConnection(url, username, password);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void test() throws Exception {
Demo3.writeMsg(conn1);
}
public static void writeMsg(Connection conn) throws Exception {
Statement statement = conn.createStatement();
String userName = conn.getMetaData().getUserName();
File file = new File("/usr/local/tables/"+ userName +".txt") ;
if(!file.getParentFile().exists()) {
file.getParentFile().mkdirs() ;
}
BufferedWriter writer = new BufferedWriter(new FileWriter(file)) ;
ResultSet rs = statement.executeQuery(SQL);
Clob ddl;
String type = TYPE_MARK;
int count = 0;
List<String> list = new ArrayList<String>();
while (rs.next()) {
ddl = rs.getClob(1);
String str = ddl.getSubString(1L, (int) ddl.length()) ;
System.out.println(str);
if(str.trim().startsWith("CREATE")) {
writer.write(";");
writer.flush();
writer.newLine();
}
writer.write(str);
writer.flush();
writer.newLine();
if (!rs.getString(2).equals(type)) {
if (!type.equals(TYPE_MARK)) {
list.add(type + "," + count);
type = rs.getString(2);
count = 1;
} else {
type = rs.getString(2);
count++;
}
} else
count++;
}
list.add(type + "," + count);
rs.close();
statement.close();
writer.flush();
writer.newLine();
for (String type1 : list) {
System.out.print(type1.split(",")[0] + ":" + type1.split(",")[1] + ";");
writer.write(type1.split(",")[0] + ":" + type1.split(",")[1] + ";");
}
writer.flush();
writer.newLine();
writer.close();
}
private static Connection conn1 = null;
private static String driver = "oracle.jdbc.driver.OracleDriver"; // 驱动
private static String url = "jdbc:oracle:thin:@192.168.5.12:1521/dba1";
private static String password = "123456"; // 密码123456
// 关闭连接
public static void close() {
try {
conn1.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}