Java导出oracle表结构实例详解_Java教程

Java导出oracle表结构实例详解

2020-08-23 15:10Java之家 Java教程

这篇文章主要介绍了 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