Java/Java开发框架 数据库相关教程 大数据相关教程 HTML/CSS相关教程 JavaScript/前端框架 开发工具相关教程 后端开发相关教程 Java开发技术 Java面试题库 Java架构设计 大数据资讯 大数据技术 大数据应用 Python开发技术 HTML HTML5 CSS JavaScript jQuery Vue AngularJS MySQL Oracle SQLServer MongoDB Redis

Java导出oracle表结构实例详解

发布时间:2020-03-03 14:00:10  作者:本站编辑  来源:本站原创  浏览次数:

摘要:这篇Java开发技术栏目下的“Java导出oracle表结构实例详解”,介绍的技术点是“Java导出oracle表结构、Oracle、实例详解、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 

另外需要jdbc的Oracle驱动。

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

文章关键词: Java导出oracle表结构 Oracle Java 实例详解 表结构 实例

  • 0

    开心

  • 0

    板砖

  • 0

    感动

  • 0

    有用

  • 0

    疑问

  • 0

    难过

  • 0

    无聊

  • 0

    震惊

评论已有 0

相关文章

加载更多