JAVA自动化生成Oracle建表，索引，SEQ

　　做项目的时候，很多时候需要将数据模型中建表脚本，主键，索引，SEQ，配置化数据保存成脚本（导出DMP也可以），这个时候就可以通过DBMS_METADATA.GET_DDL这个函数实现。

JAVA实现：
（1）建立数据库连接
（2）执行查询
（3）建立多个写的文件流（TABLE,SEQUENCE,INDEX）
（4）写入文件
（5）关闭连接
　　

url=jdbc:oracle:thin:@192.168.1.2:1521:orcl
username=zz
password=zz
tables_file=tables.sql
sequences_file=sequences.sql
indexs_file=indexs.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 = 'INDEX' \
or U.OBJECT_TYPE = 'SEQUENCE' \
order by U.OBJECT_TYPE desc
　　
　　

package com.message.export;
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.Properties;
public class Main {
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 TABLES_FILE = "tables.sql";
private static String SEQUENCES_FILE = "sequences.sql";
private static String INDEXS_FILE = "indexs.sql";
public static void main(String[] args) throws Exception {
Properties properties = new Properties();
properties.load(new FileInputStream("src/config.properties"));
URL = properties.getProperty("url", URL);
USERNAME = properties.getProperty("username", USERNAME);
PASSWORD = properties.getProperty("password", PASSWORD);
TABLES_FILE = properties.getProperty("tables_file", TABLES_FILE);
SEQUENCES_FILE = properties.getProperty("sequences_file", SEQUENCES_FILE);
INDEXS_FILE = properties.getProperty("indexs_file", INDEXS_FILE);
SQL = properties.getProperty("sql", SQL);
FileWriter fwT = new FileWriter(TABLES_FILE);
FileWriter fwS = new FileWriter(SEQUENCES_FILE);
FileWriter fwI = new FileWriter(INDEXS_FILE);
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con = DriverManager.getConnection(URL, USERNAME, PASSWORD);
Statement statement = con.createStatement();
ResultSet rs = statement.executeQuery(SQL);
while (rs.next()) {
Clob ddl = rs.getClob(1);
String objectName = rs.getString(2);
String ddlStr = ddl.getSubString(1L, (int) ddl.length());
if ("TABLE".equals(objectName)) {
fwT.write(ddlStr);
}
if ("SEQUENCE".equals(objectName)) {
fwS.write(ddlStr);
}
if ("INDEX".equals(objectName)) {
fwI.write(ddlStr);
}
}
fwT.flush();
fwS.flush();
fwI.flush();
fwT.close();
fwS.close();
fwI.close();
rs.close();
statement.close();
con.close();
}
}