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();
	}
	
}