- 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>