java导出oracle表结构

最近用到的，因为plsql是收费的，不让用，找了很多方法终于发现了这个。

核心语句

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' 
order by U.OBJECT_TYPE desc

自己写的java方法，未做封装。

 

package sql;

import java.io.FileInputStream;
import java.io.FileWriter;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

public class Main {

	private static final String TYPE_MARK = "-1";
	
	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' " +
		"order by U.OBJECT_TYPE desc";
	
	private static String URL = "jdbc:oracle:thin:@192.168.1.2:1521:orcl";
	private static String USERNAME = "abc";
	private static String PASSWORD = "abc";
	private static String OUTFILE = "tables.sql";
	
	/**
	 * @param args
	 * @throws Exception 
	 * @throws  
	 */
	public static void main(String[] args) throws Exception {
		// TODO Auto-generated method stub
		Properties properties = new Properties();
		properties.load(new FileInputStream("config.properties"));
		URL = properties.getProperty("url", URL);
		USERNAME = properties.getProperty("username", USERNAME);
		PASSWORD = properties.getProperty("password", PASSWORD);
		OUTFILE = properties.getProperty("outfile", OUTFILE);
		SQL = properties.getProperty("sql", SQL);
		
		FileWriter fw = new FileWriter(OUTFILE);
		Class.forName("oracle.jdbc.driver.OracleDriver");
		Connection con = DriverManager.getConnection(URL, USERNAME, PASSWORD);
		Statement statement = con.createStatement();
		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);
			fw.write(ddl.getSubString(1L, (int)ddl.length()));
			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);
		fw.flush();
		fw.close();
		rs.close();
		statement.close();
		con.close();
		for(String type1 : list)
			System.out.print(type1.split(",")[0] + ":" + type1.split(",")[1] + ";");
		System.out.println();
	}

}

config.properties

 

url=jdbc:oracle:thin:@192.168.1.2:1521:orcl
username=abc
password=abc
outfile=tables.sql
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' \
order by U.OBJECT_TYPE desc

另外需要jdbc的oracle驱动。