In this java JDBC tutorial we will use java.sql.DatabaseMetaData to get/obtain/retrieve information about the database as a whole in java.

How to get information of

all tables,

stored Procedures,

functions,

indexes,

views,

sequences and

triggers

from database in java.

JDBC Example/Program1 - Obtain all tables in database using DatabaseMetaData in java

package DatabaseMetaData;

//JDBC-

import java.sql.Connection;

import java.sql.DatabaseMetaData;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

/** Copyright (c), AnkitMittal JavaMadeSoEasy.com */

publicclass DatabaseMetaDataExample_tables {

publicstaticvoid main(String... arg) {

Connection con = null;

PreparedStatement prepStmt = null;

ResultSet rs = null;

try {

// registering Oracle driver class

Class.forName("oracle.jdbc.driver.OracleDriver");

// getting connection

con = DriverManager.getConnection(

"jdbc:oracle:thin:@localhost:1521:orcl", "ankit",

"Oracle123");

System.out.println("Connection established successfully!");

DatabaseMetaData dbMetaData = con.getMetaData();

System.out.println("\n--Print all database tables--");

String tables[] = { "TABLE" };

rs = dbMetaData.getTables(null, null, null, tables);

while (rs.next()) {

System.out.println(rs.getString(3));

}

} catch (ClassNotFoundException e) {

e.printStackTrace();

} catch (SQLException e) {

e.printStackTrace();

} finally {

try {

if (rs != null)

rs.close(); // close resultSet

if (prepStmt != null)

prepStmt.close(); // close PreparedStatement

if (con != null)

con.close(); // close connection

} catch (SQLException e) {

e.printStackTrace();

}

}

}

}

/*OUTPUT

Connection established successfully!

--Print all database tables--

EMPLOYEE

TEST_FILES

TEST_IMG

USER1

SYS_IOT_OVER_70794

WWV_COLUMN_EXCEPTIONS

.

.

.

.

(And many more)

*/

JDBC Example/Program2 - get all stored procedures in database using DatabaseMetaData in java

import java.sql.Connection;

import java.sql.DatabaseMetaData;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

/** Copyright (c), AnkitMittal JavaMadeSoEasy.com */

publicclass DatabaseMetaDataExample_Procedures {

publicstaticvoid main(String... arg) {

Connection con = null;

PreparedStatement prepStmt = null;

ResultSet rs = null;

try {

// registering Oracle driver class

Class.forName("oracle.jdbc.driver.OracleDriver");

// getting connection

con = DriverManager.getConnection(

"jdbc:oracle:thin:@localhost:1521:orcl", "ankit",

"Oracle123");

System.out.println("Connection established successfully!");

DatabaseMetaData dbMetaData = con.getMetaData();

System.out.println("\n--Print all database PROCEDUREs--");

String storedProcedures[] = { "PROCEDURE" };

rs = dbMetaData.getTables(null, null, null, storedProcedures);

while (rs.next()) {

System.out.println(rs.getString(3));

}

} catch (ClassNotFoundException e) {

e.printStackTrace();

} catch (SQLException e) {

e.printStackTrace();

} finally {

try {

if (rs != null)

rs.close(); // close resultSet

if (prepStmt != null)

prepStmt.close(); // close PreparedStatement

if (con != null)

con.close(); // close connection

} catch (SQLException e) {

e.printStackTrace();

}

}

}

}

/*OUTPUT

Connection established successfully!

--Print all database PROCEDUREs--

MYPROC_EMPLOYEE_INSERT

MYPROC_EMPLOYEE_INSERT_IN

MYPROC_EMPLOYEE_SELECT

MYPROC_EMPLOYEE_SELECT_OUT

MYPROC_IN_OUT

APEX

APEX_ADMIN

.

.

.

.

(And many more)

*/

JDBC Example/Program3 - retrieve all functions in database using DatabaseMetaData in java

String functions[] = { "FUNCTION" };

rs = dbMetaData.getTables(null, null, null, functions);

while (rs.next()) {

System.out.println(rs.getString(3));

}

JDBC Example/Program4 - Obtain all indexes in database using DatabaseMetaData in java

String indexes[] = { "INDEX" };

rs = dbMetaData.getTables(null, null, null, indexes);

while (rs.next()) {

System.out.println(rs.getString(3));

}

JDBC Example/Program5 - Obtain all views in database using DatabaseMetaData in java

String views[] = { "VIEW" };

rs = dbMetaData.getTables(null, null, null, views);

while (rs.next()) {

System.out.println(rs.getString(3));

}

JDBC Example/Program6 - Obtain all sequences in database using DatabaseMetaData in java

String sequences[] = { "SEQUENCE" };

rs = dbMetaData.getTables(null, null, null, sequences);

while (rs.next()) {

System.out.println(rs.getString(3));

}

JDBC Example/Program7 - Obtain all triggers in database using DatabaseMetaData in java

String triggers[] = { "TRIGGER" };

rs = dbMetaData.getTables(null, null, null, triggers);

while (rs.next()) {

System.out.println(rs.getString(3));

}

So in this java JDBC tutorial we learned how to use DatabaseMetaData to get/obtain/retrieve information of all tables, stored Procedures, functions, indexes, views, sequences and triggers from database in java.