Java导出oracle表结构实例详解

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 

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();   }  } 

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