Java querying the use of all table DatabaseMetaData for Oracle Database

usage of DatabaseMetaData (rpm )

a  .  get an instance of this object

== Con.getmetadata ();

two .  Method Gettables usage of
Prototype:

ResultSet databasemetadata.gettables (String catalog,string schema,string tablename,string []type]

This method can return a result collection ResultSet , the results are concentrated 5 columns, exceeding the reported cross -boundary exception

Function Description: Gets the table information for the specified parameter

Parameter description:
Parameters : Catalog: Directory name, usually empty .
Parameters:Schema: Database name, for Oracle, user name
Parameter:tablename: Table name
Parameter:type: Table's types (table | VIEW)

Note: Parameter names must be capitalized in the process of use. Otherwise get something.

three .  Method GetColumns usage of

Function Description: Gets the column information for the specified table.

Prototype:

ResultSet DatabaseMetaData getcolumns (String catalog,string schema,string tablename,string columnName)

Parameter description:

Parameter Catalog: category name
Parameter Schema: User scheme name
Parameter tableName: Database table name
Parameter columnName: column name

Iv. Methods Getprimarykeys usage of

Function Description: Gets the primary key information for the specified table.

Prototype:

ResultSet DatabaseMetaData Getprimarykeys (String catalog,string schema,string tableName)

Parameter description:

Parameter Catalog: category name
Parameter Schema: User scheme name
Parameter tableName: database table name

Note: Be sure to specify the table name, otherwise the return value will be nothing.

v. Methods . GetTypeInfo () usage of

Function Description: Gets the data type information of the current database.

Vi. Methods Getexportedkeys usage of

Function Description: Gets the foreign key information for the specified table.

Parameter description:
Parameter Catalog: category name
Parameter Schema: User scheme name
Parameter tableName: database table name

The following is an example of MySQL and Oracle to illustrate the two parameters.

The organizational structure of Oracle and MySQL data is completely different , the visual representation of the table and view of the hook path is not the same , in Oracle, the use of a sub-user management mechanism , tables and views are hooked up to a user , At this point the user becomes a "schema" of Oracle, while in MySQL the tables and views are directly attached to the database. Thus, getting the catalog in oralce gets null, and getting the schema capitalized is the list of user names. The catalog obtained in MySQL is a list of database names, and the schema is null. The reader can test with the following two methods provided by DatabaseMetaData, all of which return the resultset data type.

Get category Definition

Rs=dbmd.getcatalogs ();

Get schema Definition

Rs=dbmd.getschemas ();

Based on the above analysis :

If the database is MySQL: Then the first parameter, catalog, can be the name of the database, when the item is null , the name of the database specified in the URL string , the second parameter schema, Fill in null;

If the database is Oralce: Then the first parameter , catalog, is null, the second argument schema, fill in uppercase user names such as "SCOTT"if the item is NULL , then the query scope is for all schema users.

return value analysis

Method Gettables The return value is a result set (ResultSet) type , and for the information in that result set, up to JDK1.5 has reserved more than 20 items to describe the information about the table, but Not every data will return more than 20 items . We can often use the following four items :

Table_schem: For Oracle , this is the uppercase user name , which is null for MySQL .

TABLE_NAME: Name of the table.

Table_cat= is null for Oracle and is the database name for MySQL.

The type of the table_type= table , based on the fourth parameter types an item in the array for the table and view.

Importjava.sql.Connection;ImportJava.sql.DatabaseMetaData;ImportJava.sql.DriverManager;ImportJava.sql.ResultSet;Importjava.sql.SQLException;Importjava.sql.Statement;Importjava.util.ArrayList;Importjava.util.List;
 Public classDababase {PrivateString url= "Jdbc:oracle:thin: @localhost: 1521:zhyl";//server address:, port number: 1521, db instance name: Zhyl. PrivateString username= "Andatabase";PrivateString pw= "Oracl";PrivateConnection conn=NULL;//the user name and password are created by themselves.  PublicConnection Openconn () {Try{class.forname ("Oracle.jdbc.driver.OracleDriver");Try{conn=drivermanager.getconnection (URL,USERNAME,PW);} Catch(SQLException e) {//TODO auto-generated Catch blocke.printstacktrace ();}} Catch(ClassNotFoundException e) {//TODO auto-generated Catch blocke.printstacktrace ();}returnConn;} PublicResultSet executeQuery (String sql) {Dababase db=Newdababase (); ResultSet RS=NULL; Connection Con=db.    Openconn (); Try{Statement sm=con.createstatement (); RS=sm.executequery (SQL); } Catch(SQLException e) {//TODO auto-generated Catch blockE.printstacktrace (); }    returnrs;} Public voidClose () {Try{conn.close (); } Catch(SQLException e) {//TODO auto-generated Catch blockE.printstacktrace (); }}//Gets the table names of all tables in the database and adds them to the list structure.  PublicList gettablenamelist (Connection conn)throwsSQLException {DatabaseMetaData Dbmd=Conn.getmetadata ();//access all tables under the current user AndatabaseResultSet rs= Dbmd.gettables ("null", "andatabase", "%",NewString[] {"TABLE" });//System.out.println ("Kkkkkk" +dbmd.gettables ("null", "%", "%", new string[] {"TABLE"});List tablenamelist=NewArrayList (); while(Rs.next ()) {Tablenamelist.add (rs.getstring ("TABLE_NAME"));}returntablenamelist;}//Gets the column names of all columns in the data table and adds them to the list structure.  PublicList getcolumnnamelist (Connection conn, String TableName)throwsSQLException {DatabaseMetaData Dbmd=Conn.getmetadata (); ResultSet RS= Dbmd.getcolumns (NULL, "%", tableName, "%"); List columnnamelist=NewArrayList (); while(Rs.next ()) {Columnnamelist.add (rs.getstring ("Column_name"));}returncolumnnamelist;} Public Static voidMain (String s[])throwssqlexception{dababase dbconn=Newdababase (); Connection Conn=dbconn.openconn ();if(conn==NULL) System.out.println ("Connection Failed");ElseSystem.out.println ("Connection succeeded");Try{List tablelist= Dbconn.gettablenamelist (conn);//Remove all tables from the current user//List tablelist = dbconn.getcolumnnamelist (conn, "LOGIN");//The table name must be uppercase and all columns of the current table should be removedSystem.out.println (Tablelist.size ()); for(Object object:tablelist) {String ss=(String) object; SYSTEM.OUT.PRINTLN (ss);}} Catch(SQLException e) {e.printstacktrace ();}finally {if(Conn! =NULL) {Try{conn.close ();}Catch(SQLException e) {e.printstacktrace (); }}}}}

Java querying the use of all table DatabaseMetaData for Oracle Database