How to get all tables, storedProcedures, functions, indexes, views, sequences and triggers from database in java


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
  1. all tables,
  2. stored Procedures,
  3. functions,
  4. indexes,
  5. views,
  6. sequences and
  7. 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 */
public class DatabaseMetaDataExample_tables {
   public static void 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 */
public class DatabaseMetaDataExample_Procedures {
   public static void 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.

RELATED LINKS>

JDBC- Calling Oracle database STORED PROCEDURE and pass its IN parameter from java - CallableStatement example in java

JDBC- Calling Oracle database STORED PROCEDURE- OUT parameter - CallableStatement example in java

JDBC- Calling Oracle database STORED PROCEDURE- IN OUT parameter - CallableStatement example in java


JDBC- Calling Oracle database FUNCTION - CallableStatement example in java


No comments:

Post a Comment