扫二维码与项目经理沟通
我们在微信上24小时期待你的声音
解答本文疑问/技术咨询/运营咨询/技术建议/互联网交流
利用Java如何实现导出oracle数据库中的表结构？很多新手对此不是很清楚，为了帮助大家解决这个难题，下面小编将为大家详细讲解，有这方面需求的人可以来学习下，希望你能有所收获。
创新互联专注于海州企业网站建设,成都响应式网站建设公司,成都商城网站开发。海州网站建设公司,为海州等地区提供建站服务。全流程定制制作，专业设计，全程项目跟踪，创新互联专业和态度为您提供的服务
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 list = new ArrayList();
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
看完上述内容是否对您有帮助呢？如果还想对相关知识有进一步的了解或阅读更多相关文章，请关注创新互联行业资讯频道，感谢您对创新互联的支持。
我们在微信上24小时期待你的声音
解答本文疑问/技术咨询/运营咨询/技术建议/互联网交流
微信二维码
移动版官网