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