JDBC - DatabaseMetaData in java - retrieve database information




  • java.sql.DatabaseMetaData is an interface.
  • java.sql.DatabaseMetaData can be used to obtain information about the database as a whole.
  • java.sql.DatabaseMetaData extends java.sql.Wrapper.
  • DatabaseMetaData important methods -
getDriverName() - Returns driver name.
getDriverVersion() - returns driver version.
getDatabaseProductName() - returns database name
getDatabaseProductVersion() - returns database version
getUserName() - returns username used to connect to database.
getURL() - returns URL used to connect to database.
getDatabaseMinorVersion() - returns database’s minor/initial version.
getDatabaseMajorVersion() - returns current database version.



In case you are using Oracle 11g, your database details will look like this -

--Database driver name and version--
getDriverName() - Database driver name = Oracle JDBC driver
getDriverVersion() - Database driver version = 11.1.0.7.0-Production
--Database product name and version--
getDatabaseProductName() - Database product name = Oracle
getDatabaseProductVersion() - Database product version = Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
--Database user name--
getUserName() - Database user name = ANKIT
--Database URL--
getURL() - Database URL = jdbc:oracle:thin:@localhost:1521:orcl
--Database Minor and Major version--
getDatabaseMinorVersion() - Database Minor version = 2
getDatabaseMajorVersion() - Database Major version = 11
 

JDBC Program1 - How to use DatabaseMetaData
package DatabaseMetaData;
//JDBC-
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
/** Copyright (c), AnkitMittal JavaMadeSoEasy.com */
public class DatabaseMetaDataTest {
   public static void main(String... arg) {
          Connection con = null;
          PreparedStatement prepStmt = 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("--Database driver name and version--");
                 System.out.println("Database driver name = "
                              + dbMetaData.getDriverName());
                 System.out.println("Database driver version = "
                              + dbMetaData.getDriverVersion());
                 System.out.println("\n--Database product name and version--");
                 System.out.println("Database product name = "
                              + dbMetaData.getDatabaseProductName());
                 System.out.println("Database product version = "
                              + dbMetaData.getDatabaseProductVersion());
                 System.out.println("\n--Database user name--");
                 System.out.println("Database user name = "
                              + dbMetaData.getUserName());
                 System.out.println("\n--Database URL--");
                 System.out.println("Database URL = " + dbMetaData.getURL());
                 System.out.println("\n--Database Minor and Major version--");
                 System.out.println("Database Minor version = "
                              + dbMetaData.getDatabaseMinorVersion());
                 System.out.println("Database Major version = "
                              + dbMetaData.getDatabaseMajorVersion());
          } catch (ClassNotFoundException e) {
                 e.printStackTrace();
          } catch (SQLException e) {
                 e.printStackTrace();
          }
          finally{
                 try {
                       if(prepStmt!=null) prepStmt.close(); //close PreparedStatement
                       if(con!=null) con.close(); // close connection
                 } catch (SQLException e) {
                       e.printStackTrace();
                 }
          }
   }
}
/*OUTPUT
Connection established successfully!
--Database driver name and version--
Database driver name = Oracle JDBC driver
Database driver version = 11.1.0.7.0-Production
--Database product name and version--
Database product name = Oracle
Database product version = Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
--Database user name--
Database user name = ANKIT
--Database URL--
Database URL = jdbc:oracle:thin:@localhost:1521:orcl
--Database Minor and Major version--
Database Minor version = 2
Database Major version = 11
*/



JDBC Program1 - Obtain all tables in database using DatabaseMetaData
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 DatabaseMetaDataTest_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 Program2 - Obtain all stored procedures in database using DatabaseMetaData
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 DatabaseMetaDataTest_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 Program3 - Obtain all functions in database using DatabaseMetaData

                 String functions[] = { "FUNCTION" };
                 rs = dbMetaData.getTables(null, null, null, functions);
                 while (rs.next()) {
                       System.out.println(rs.getString(3));
                 }


JDBC Program4 - Obtain all indexes in database using DatabaseMetaData

                 String indexes[] = { "INDEX" };
                 rs = dbMetaData.getTables(null, null, null, indexes);
                 while (rs.next()) {
                       System.out.println(rs.getString(3));
                 }


JDBC Program5 - Obtain all views in database using DatabaseMetaData

                 String views[] = { "VIEW" };
                 rs = dbMetaData.getTables(null, null, null, views);
                 while (rs.next()) {
                       System.out.println(rs.getString(3));
                 }
                



JDBC Program6 - Obtain all sequences in database using DatabaseMetaData

                 String sequences[] = { "SEQUENCE" };
                 rs = dbMetaData.getTables(null, null, null, sequences);
                 while (rs.next()) {
                       System.out.println(rs.getString(3));
                 }



JDBC Program7 - Obtain all triggers in database using DatabaseMetaData
                   String triggers[] = { "TRIGGER" };
                 rs = dbMetaData.getTables(null, null, null, triggers);
                 while (rs.next()) {
                       System.out.println(rs.getString(3));
                 }



RELATED LINKS>

JDBC- Batch PreparedStatement example- Execute INSERT query(DML command) using PreparedStatement's addBatch() and executeBatch() methods in java


12 Differences between Statement, PreparedStatement and CallableStatement in java


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