java orcl自动_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();

}

}