Java query the use of all table DatabaseMetaData in Oracle Database (detailed) _java

One. Get an instance of this object

Connection con;
con = drivermanager.getconnection (Url,username,password);
DatabaseMetaData Dbmd = Con.getmetadata ();

Two. Method Gettables usage

Prototype:

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

This method returns the result set resultset, with 5 columns in the result set, exceeding the reported cross boundary exception

Function Description: Get the table information of the specified parameters

Parameter description:
Parameters: Catalog: directory name, generally null.
Parameters: Schema: Database name, for Oracle, user name
Parameters: TableName: Table name
Parameters: Type: Table of types (table | VIEW)

Note: During use, parameter names must be capitalized. Otherwise get something.

Three. Method GetColumns usage

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

Prototype:

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

Parameter description:

Parameter catalog: Category name
Parameter schema: User scenario Name
Parameter tablename: Database table name
Parameter columnname: Column name

Iv. the use of method Getprimarykeys

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 scenario Name
Parameter tablename: Database table name

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

methods. Use of GetTypeInfo ()

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

Vi. the use of method Getexportedkeys

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

Parameter description:
Parameter catalog: Category name
Parameter schema: User scenario Name
Parameter tablename: Database table name

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

The organization of Oracle and MySQL data is completely different, intuitive representation, tables and views are not the same way, in Oracle, the adoption of a user-management mechanism, tables and views are linked under a user, the user will become a "schema" Oracle While in MySQL tables and views are directly attached to the database. Thus, obtaining the catalog in the Oralce is null, and obtaining the schema is capitalized by the list of user names. The catalog in MySQL is the list of database names, and the schema is null. Readers can test with the following two methods provided by DatabaseMetaData, and they return all resultset data types.

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, which, when it is null, fills in null for the name of the database specified in the URL string, and the second parameter schema;

If the database is Oralce: Then the first parameter catalog, NULL, the second argument schema, fill in the uppercase user name such as "SCOTT", and if the item is NULL, then the query scope is 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, more than 20 items are reserved to JDK1.5 to describe the table, but not every data will return more than 20 items. Here are four items we can use:

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

TABLE_NAME: The 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 argument, types an item in the array for tables and views.

Import java.sql.Connection;

Import Java.sql.DatabaseMetaData;

Import Java.sql.DriverManager;

Import Java.sql.ResultSet;

Import java.sql.SQLException;

Import java.sql.Statement;

Import java.util.ArrayList;

Import java.util.List;

public class Dababase {private String url= "Jdbc:oracle:thin: @localhost: 1521:zhyl";

Server address:, port number: 1521, database instance name: Zhyl.

Private String username= "Andatabase";

Private String pw= "Oracl";

Private Connection Conn=null;

The user's name and password are established by themselves.

Public Connection Openconn () {try {class.forname ("oracle.jdbc.driver.OracleDriver");

try {conn=drivermanager.getconnection (URL,USERNAME,PW);

catch (SQLException e) {//TODO auto-generated catch block E.printstacktrace ();

The catch (ClassNotFoundException e) {//TODO auto-generated catch block E.printstacktrace ();

Return conn;

Public ResultSet executequery (String sql) {Dababase db = new Dababase ();

ResultSet rs = null; Connection con =db.

  Openconn (); try {Statement sm = con.createstatemenT ();

  rs = sm.executequery (SQL);

  catch (SQLException e) {//TODO auto-generated catch block E.printstacktrace ();

} return RS;

  public void Close () {try {conn.close ();

  catch (SQLException e) {//TODO auto-generated catch block E.printstacktrace ();

}//Gets the table names of all tables in the database and adds them to the list structure.

Public List gettablenamelist (Connection conn) throws SQLException {DatabaseMetaData Dbmd = Conn.getmetadata ();

Access all tables under the current user andatabase ResultSet rs = dbmd.gettables ("null", "andatabase", "%", new string[] {"Table"});

System.out.println ("Kkkkkk" +dbmd.gettables ("null", "%", "%", new string[] {"TABLE"}));

List tablenamelist = new ArrayList ();

while (Rs.next ()) {Tablenamelist.add (rs.getstring ("table_name"));

return tablenamelist;

//Get the column names of all the columns in the datasheet and add them to the list structure. Public List getcolumnnamelist (Connection conn, String tablename) throws SQLException {DatabaseMetaData Dbmd = Conn.getm

Etadata (); ResultSet rs = dbmd.getcolumns (null, "%", TableName, "%");

List columnnamelist = new ArrayList ();

while (Rs.next ()) {Columnnamelist.add (rs.getstring ("column_name"));

return columnnamelist;

public static void Main (String s[]) throws SQLException {dababase dbconn = new Dababase ();

Connection conn = Dbconn.openconn ();

if (conn==null) System.out.println ("Connection failed");

else System.out.println ("Connection succeeded"); try {List tablelist = dbconn.gettablenamelist (conn);//Remove all tables for the current user//list tablelist = dbconn.getcolumnnamelist (conn, "LOG

In "),//The table name must be uppercase, remove all columns of the current table System.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 (); }
}
}
}
}

The above is a small series for everyone to bring Java query Oracle database all table DatabaseMetaData usage (detailed) All content, I hope that we support cloud-Habitat Community ~