1. Get an instance of this object
Connection con ;con = DriverManager.getConnection(url,userName,password);DatabaseMetaData dbmd = con.getMetaData();
2. Usage of method getTables
prototype:
ResultSet DatabaseMetaData.getTables(String catalog,String schema,String tableName,String []type)
This method can return the result set ResultSet, with 5 columns in the result set. If it exceeds the bounds, an out-of-bounds exception will be reported.
Function description: Get the table information of the specified parameters
Parameter description:
Parameters: catalog: directory name, usually empty.
Parameters: schema: database name, for oracle username parameter: tablename: table name parameter: type: table type (TABLE | VIEW)
Note: During use, the parameter name must be in capital. Otherwise you get something.
3. Usage of method getColumns
Function description: Get column information of 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
4. Methods of use of getPrimaryKeys
Function description: Get the primary key information of 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 have nothing.
5. Method.Usage of getTypeInfo()
Function description: Get the data type information of the current database.
6. Methods of use of getExportedKeys
Function description: Get foreign key information for the specified table.
Parameter description:
Parameter catalog: Category name Parameter schema: User scheme name Parameter tableName: Database table name
Let’s explain these two parameters using MySQL and Oracle as examples.
The organizational structure of Oracle and MySQL data is completely different. In terms of intuitive appearance, the ways of attaching tables and views are different. In Oracle, a user management mechanism is adopted. When tables and views are attached to a certain user, the user will become an "schema" of Oracle; in MySQL, tables and views are directly attached to the database. In this way, getting catalog in Oralce gets null, and getting schema gets capitalized is a list of user names. The catalog obtained in MySQL is the database name list, and the schema is null. Readers can test through the following two methods provided by DatabaseMetaData, and they return both the ResultSet data type.
//Get the category definition
rs=dbmd.getCatalogs();
//Get the pattern 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 this item is null, it is the database name specified in the Url string, and the second parameter schema, fill in null;
If the database is Oralce: Then the first parameter catalog is null, the second parameter schema is filled in the uppercase user name such as "SCOTT". If the item is null, the query scope is all schema users.
Return value analysis
The return value of the method getTables is a result set (ResultSet). For the information in the result set, more than 20 items are reserved to JDK1.5 for the relevant information of the table. However, not every data will return these more than 20 items. We can often use four items:
TABLE_SCHEM: For Oracle, it is a capitalized user name and null for MySQL.
TABLE_NAME: The name of the table.
TABLE_CAT=null for Oracle and database name for MySQL.
TABLE_TYPE=Table type, used for table and view based on an item in the fourth parameter type array.
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 database {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 name and password are created by yourself. public Connection 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();}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(); }}// Get the table names of all tables in the database and add 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("kkkkkkkkk"+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 columns in the data table and add them to the list structure. public List getColumnNameList(Connection conn, String tableName)throws SQLException {DatabaseMetaData dbmd = conn.getMetaData();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("Conn failed");elseSystem.out.println("Conn successful");try {List tableList = dbConn.getTableNameList(conn);//Fetch all tables of the current user//List tableList = dbConn.getColumnNameList(conn, "LOGIN");//The table name must be in uppercase, take out 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 the full content of the Java query all tables of the Oracle database, DatabaseMetaData (detailed explanation) brought to you by the editor. I hope everyone will support Wulin.com~