Java导出oracle表结构实例详解
Java导出oracle表结构实例详解
最近用到的，因为plsql是收费的，不让用，找了很多方法终于发现了这个。
核心语句
SELECTDBMS_METADATA.GET_DDL(U.OBJECT_TYPE,U.object_name),U.OBJECT_TYPE FROMUSER_OBJECTSU whereU.OBJECT_TYPE='TABLE' orU.OBJECT_TYPE='VIEW' orU.OBJECT_TYPE='INDEX' orU.OBJECT_TYPE='PROCEDURE' orU.OBJECT_TYPE='SEQUENCE' orU.OBJECT_TYPE='TRIGGER' orderbyU.OBJECT_TYPEdesc
自己写的Java方法，未做封装。
packagesql;
importjava.io.FileInputStream;
importjava.io.FileWriter;
importjava.sql.Clob;
importjava.sql.Connection;
importjava.sql.DriverManager;
importjava.sql.ResultSet;
importjava.sql.Statement;
importjava.util.ArrayList;
importjava.util.List;
importjava.util.Properties;
publicclassMain{
privatestaticfinalStringTYPE_MARK="-1";
privatestaticStringSQL=
"SELECTDBMS_METADATA.GET_DDL(U.OBJECT_TYPE,U.object_name),U.OBJECT_TYPE"+
"FROMUSER_OBJECTSU"+
"whereU.OBJECT_TYPE='TABLE'"+
"orU.OBJECT_TYPE='VIEW'"+
"orU.OBJECT_TYPE='INDEX'"+
"orU.OBJECT_TYPE='PROCEDURE'"+
"orU.OBJECT_TYPE='SEQUENCE'"+
"orU.OBJECT_TYPE='TRIGGER'"+
"orderbyU.OBJECT_TYPEdesc";
privatestaticStringURL="jdbc:oracle:thin:@192.168.1.2:1521:orcl";
privatestaticStringUSERNAME="abc";
privatestaticStringPASSWORD="abc";
privatestaticStringOUTFILE="tables.sql";
/**
*@paramargs
*@throwsException
*@throws
*/
publicstaticvoidmain(String[]args)throwsException{
//TODOAuto-generatedmethodstub
Propertiesproperties=newProperties();
properties.load(newFileInputStream("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);
FileWriterfw=newFileWriter(OUTFILE);
Class.forName("oracle.jdbc.driver.OracleDriver");
Connectioncon=DriverManager.getConnection(URL,USERNAME,PASSWORD);
Statementstatement=con.createStatement();
ResultSetrs=statement.executeQuery(SQL);
Clobddl;
Stringtype=TYPE_MARK;
intcount=0;
Listlist=newArrayList();
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(Stringtype1: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=SELECTDBMS_METADATA.GET_DDL(U.OBJECT_TYPE,U.object_name),U.OBJECT_TYPE\ FROMUSER_OBJECTSU\ whereU.OBJECT_TYPE='TABLE'\ orU.OBJECT_TYPE='VIEW'\ orU.OBJECT_TYPE='INDEX'\ orU.OBJECT_TYPE='PROCEDURE'\ orU.OBJECT_TYPE='SEQUENCE'\ orU.OBJECT_TYPE='TRIGGER'\ orderbyU.OBJECT_TYPEdesc
另外需要jdbc的Oracle驱动。
感谢阅读，希望能帮助到大家，谢谢大家对本站的支持！