java导出oracle表结构

来源：互联网发布：三类医疗器械软件编辑：程序博客网时间：2024/06/06 08:33

最近用到的，因为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驱动。

0 0