服务器之家

服务器之家 > 正文

Java导出oracle表结构实例详解

时间:2020-08-23 15:10     来源/作者:Java之家

 Java导出oracle表结构实例详解

最近用到的,因为plsql是收费的,不让用,找了很多方法终于发现了这个。

核心语句

?
1
2
3
4
5
6
7
8
9
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方法,未做封装。

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
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驱动。

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

标签:

相关文章

热门资讯

2020微信伤感网名听哭了 让对方看到心疼的伤感网名大全
2020微信伤感网名听哭了 让对方看到心疼的伤感网名大全 2019-12-26
歪歪漫画vip账号共享2020_yy漫画免费账号密码共享
歪歪漫画vip账号共享2020_yy漫画免费账号密码共享 2020-04-07
Intellij idea2020永久破解,亲测可用!!!
Intellij idea2020永久破解,亲测可用!!! 2020-07-29
最新idea2020注册码永久激活(激活到2100年)
最新idea2020注册码永久激活(激活到2100年) 2020-07-29
男生常说24816是什么意思?女生说13579是什么意思?
男生常说24816是什么意思?女生说13579是什么意思? 2019-09-17
返回顶部