Java导出oracle表结构实例详解

 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驱动。

感谢阅读，希望能帮助到大家，谢谢大家对本站的支持！