JDBC Basic
➤ Intro to JDBC
➤ JDBC vs ODBC
➤ JDBC API
➤ JDBC Driver
Connect to Database
➤ Oracle Connection
➤ MySQL Connection
➤ Using Eclipse IDE
➤ Coding Standards
➤ Close JDBC Object
➤ Java.sql Package
➤ DriverManager Class
➤ Get Connection
JDBC Statement
➤ Statement Interface
➤ Statement Example
➤ Create a database
PreparedStatement
➤ Types of statements
➤ PreparedStatement
➤ Statement vs PreParedStatement
PreparedStatement Example
➤ Insert record
➤ Update record
➤ Select record
➤ Create a table
➤ More Examples
➤ Surrogate Key
Others
➤ DatabaseMetaData
➤ ResultSetMetaData & ParameterMetaData
In this post we will learn about metadata in JDBC. What it metadata in jdbc? The JDBC supports how many types of metadata? What is DatabaseMetaData in jdbc, their methods and sample program?
Gathering more info about given info or providing more info about the given info is called MetaData. In Java it is code about code.. info about info.. either gathering info or providing info. In JDBC metadata concepts are given to gather more info about DB software, tables, and query params.
JDBC supports 3 modes of metadata
1) DatabaseMetaData
2) ResultSetMetaData
3) ParameterMetaData
In this post, we will learn about JDBC DatabaseMetaData. The ResultSetMetaData and ParameterMetaData will be discussed in another post.
- The DatabaseMetaData gives limitations and capabilities of underlying database software and also gives some more details about database software.
- DatabaseMetaData object means, it is an object of underlying JDBC driver software supplied Java class that implements java.sql.DatabaseMetaData (I).
- To create the object,
DatabaseMetaData dbmd = con.getMetaData();
- We can invoke multiple methods on “
dbmd
” to get more info about underlying database software capabilities and limitations. - DatabaseMetaData is very useful to create GUI database tools like MySQL front, TOAD for Oracle, SQL Developer and etc
Note:- JDBC MetaData operations are not given to perform CURD operations. They are given to know more about underlying database software and database tables.
The methods invoked on MetaData objects return “null” or 0 i.e the underlying JDBC driver software is not able to fetch that info. What’s why some methods of below program give 0
In DatabaseMetaData docs, there are many methods are given to get the information of the database. Using some of that method we will demonstrate it using the Java program.
Sample Java program to get details of database using DatabaseMetaData
The below program displays the details of Oracle database. You can get details of any database by passing required values.
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.SQLException;
public class DatabaseMetaDataTest {
public static void main(String[] args) {
// declare variables
Connection con = null;
DatabaseMetaData dbmd = null;
try {
// load jdbc driver class
Class.forName("oracle.jdbc.driver.OracleDriver");
// establish the connection
con = DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:knowprogram",
"scott", "tiger");
// get Database MetaData object
dbmd = con.getMetaData();
// display database details
if(dbmd != null) {
System.out.println("Database MetaData class name:: "
+ con.getClass());
System.out.println("Database software name:: "
+ dbmd.getDatabaseProductName());
System.out.println("Database software version:: "
+ dbmd.getDatabaseProductVersion());
System.out.println();
System.out.println("Database software Major version:: "
+ dbmd.getDatabaseMajorVersion());
System.out.println("Database software minor version:: "
+ dbmd.getDatabaseMinorVersion());
System.out.println();
System.out.println("JDBC major version:: "
+ dbmd.getJDBCMajorVersion());
System.out.println("JDBC minor version:: "
+ dbmd.getJDBCMinorVersion());
System.out.println();
System.out.println("JDBC driver version:: "
+ dbmd.getDriverMajorVersion()
+"."+dbmd.getDriverMinorVersion());
System.out.println();
System.out.println("All SQL Keywords:: "
+ dbmd.getSQLKeywords());
System.out.println();
System.out.println("All numeric functions:: "
+ dbmd.getNumericFunctions());
System.out.println("All System functions:: "
+ dbmd.getSystemFunctions());
System.out.println("All String functions:: "
+ dbmd.getStringFunctions());
System.out.println();
System.out.println("Max chars in database table name:: "
+ dbmd.getMaxTableNameLength());
System.out.println("Max chars in column name in db:: "
+ dbmd.getMaxColumnNameLength());
System.out.println("Max row size:: "
+ dbmd.getMaxRowSize());
System.out.println("Support Procedure:: "
+ dbmd.supportsStoredProcedures());
System.out.println();
System.out.println("Max tables in select query:: "
+ dbmd.getMaxTablesInSelect());
System.out.println("Max columns in select query:: "
+ dbmd.getMaxColumnsInSelect());
System.out.println("Max Connections to database:: "
+ dbmd.getMaxConnections());
}
} catch(SQLException se) {
se.printStackTrace();
} catch(ClassNotFoundException cnfe) {
cnfe.printStackTrace();
} catch(Exception e) {
e.printStackTrace();
}
finally {
// close connection
try {
if(con!=null) con.close();
} catch(SQLException se) {
se.printStackTrace();
} // try-catch
} // finally
} // main
} // class
Java program to get details of MySQL database using DatabaseMetaData
Similarly we can also get the information of MySQL database. Use the above program for MySQL database, update driver name and URL pattern in the above program.
// load jdbc driver class
Class.forName("com.mysql.cj.jdbc.Driver");
// establish the connection
con = DriverManager.getConnection(
"jdbc:mysql:///logicaldbname","username","password");
DatabaseMetaData gettables Example
In java.sql.DatabaseMetaData interface the gettables() method is given to retrieves the information of tables.
The signature of getTables method is:- ResultSet getTables(String catalog, String schemaPattern, String tableNamePattern, String[] types) throws SQLException
It retrieves a description of the tables available in the given catalog. Only table descriptions matching the catalog, schema, table name, and type criteria are returned. They are ordered by TABLE_TYPE, TABLE_CAT, TABLE_SCHEM, and TABLE_NAME. The table type can be TABLE, VIEW, ALIAS, SYSTEM TABLE, SYNONYM, etc.
Each table description has the following columns:
- TABLE_CAT String => table catalog (may be null)
- TABLE_SCHEM String => table schema (may be null)
- TABLE_NAME String => table name
- TABLE_TYPE String => table type. Typical types are “TABLE”, “VIEW”, “SYSTEM TABLE”, “GLOBAL TEMPORARY”, “LOCAL TEMPORARY”, “ALIAS”, “SYNONYM”.
- REMARKS String => explanatory comment on the table
- TYPE_CAT String => the types catalog (may be null)
- TYPE_SCHEM String => the types schema (may be null)
- TYPE_NAME String => type name (may be null)
- SELF_REFERENCING_COL_NAME String => name of the designated “identifier” column of a typed table (may be null)
- REF_GENERATION String => specifies how values in SELF_REFERENCING_COL_NAME are created. Values are “SYSTEM”, “USER”, “DERIVED”. (may be null)
Note: Some databases may not return information for all tables.
Parameters:
catalog
– a catalog name; must match the catalog name as it is stored in the database; “” retrieves those without a catalog; null means that the catalog name should not be used to narrow the searchschemaPattern
– a schema name pattern; must match the schema name as it is stored in the database; “” retrieves those without a schema; null means that the schema name should not be used to narrow the searchtableNamePattern
– a table name pattern; must match the table name as it is stored in the databasetypes
– a list of table types, which must be from the list of table types returned from getTableTypes(),to include; null returns all types
JDBC Program to Display all tables in a Database
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
public class GetTableInfo {
public static void main(String[] args) {
Connection con = null;
DatabaseMetaData dbmd = null;
ResultSet rs = null;
String table[] = {"TABLE"};
try {
// load jdbc driver class
Class.forName("oracle.jdbc.driver.OracleDriver");
// establish the connection
con = DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:knowprogram",
"scott", "tiger");
// get Database MetaData object
dbmd = con.getMetaData();
// display database details
if(dbmd != null) {
// get table details
rs = dbmd.getTables(null, null, null, table);
}
// process results
while(rs.next()) {
System.out.println(rs.getString(3));
}
} catch(SQLException se) {
se.printStackTrace();
} catch(ClassNotFoundException cnfe) {
cnfe.printStackTrace();
} catch(Exception e) {
e.printStackTrace();
}
finally {
try {
if(con!=null) con.close();
} catch(SQLException se) {
se.printStackTrace();
}
try {
if(rs!=null) rs.close();
} catch(SQLException se) {
se.printStackTrace();
}
}
}
}
DatabaseMetaData getcolumns example
ResultSet getColumns(String catalog, String schemaPattern,
String tableNamePattern, String columnNamePattern)
throws SQLException
Retrieves a description of table columns available in the specified catalog.
Only column descriptions matching the catalog, schema, table and column name criteria are returned. They are ordered by TABLE_CAT,TABLE_SCHEM, TABLE_NAME, and ORDINAL_POSITION.
Each column description has the following columns
- TABLE_CAT String => table catalog (may be null)
- TABLE_SCHEM String => table schema (may be null)
- TABLE_NAME String => table name
- COLUMN_NAME String => column name
- DATA_TYPE int => SQL type from java.sql.Types
- TYPE_NAME String => Data source dependent type name, for a UDT the type name is fully qualified
- COLUMN_SIZE int => column size.
- BUFFER_LENGTH is not used.
- DECIMAL_DIGITS int => the number of fractional digits. Null is returned for data types where DECIMAL_DIGITS is not applicable.
- NUM_PREC_RADIX int => Radix (typically either 10 or 2)
- NULLABLE int => is NULL allowed.
- columnNoNulls – might not allow NULL values
- columnNullable – definitely allows NULL values
- columnNullableUnknown – nullability unknown
- REMARKS String => comment describing column (may be null)
- COLUMN_DEF String => default value for the column, which should be interpreted as a string when the value is enclosed in single quotes (may be null)
- SQL_DATA_TYPE int => unused
- SQL_DATETIME_SUB int => unused
- CHAR_OCTET_LENGTH int => for char types the maximum number of bytes in the column
- ORDINAL_POSITION int => index of column in table (starting at 1)
- IS_NULLABLE String => ISO rules are used to determine the nullability for a column.
- YES — if the column can include NULLs
- NO — if the column cannot include NULLs
- empty string — if the nullability for the column is unknown
- SCOPE_CATALOG String => catalog of the table that is the scope of a reference attribute (null if DATA_TYPE isn’t REF)
- SCOPE_SCHEMA String => schema of the table that is the scope of a reference attribute (null if the DATA_TYPE isn’t REF)
- SCOPE_TABLE String => table name that this the scope of a reference attribute (null if the DATA_TYPE isn’t REF)
- SOURCE_DATA_TYPE short => source type of a distinct type or user-generated Ref type, SQL type from java.sql.Types (null if DATA_TYPE isn’t DISTINCT or user-generated REF)
- IS_AUTOINCREMENT String => Indicates whether this column is auto-incremented
- YES — if the column is auto-incremented
- NO — if the column is not auto incremented
- empty string — if it cannot be determined whether the column is auto-incremented
- IS_GENERATEDCOLUMN String => Indicates whether this is a generated column
- YES — if this a generated column
- NO — if this not a generated column
- empty string — if it cannot be determined whether this is a generated column
- The COLUMN_SIZE column specifies the column size for the given column. For numeric data, this is the maximum precision. For character data, this is the length in characters.
For Datetime datatypes
, this is the length in characters of the String representation (assuming the maximum allowed precision of the fractional seconds component). For binary data, this is the length in bytes. For the ROWID datatype, this is the length in bytes. Null is returned for data types where the column size is not applicable.
Parameters:
catalog
– a catalog name; must match the catalog name as it is stored in the database; “” retrieves those without a catalog; null means that the catalog name should not be used to narrow the searchschemaPattern
– a schema name pattern; must match the schema name as it is stored in the database; “” retrieves those without a schema; null means that the schema name should not be used to narrow the searchtableNamePattern
– a table name pattern; must match the table name as it is stored in the databasecolumnNamePattern
– a column name pattern; must match the column name as it is stored in the database
The getColumns() method returns ResultSet object.
Java program to display columnname based on the tablename
The below program is developed for the Oracle database, but you can work with any database with required values.
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;
public class GetColumnInfo {
public static void main(String[] args) {
Scanner scan = null;
Connection con = null;
DatabaseMetaData dbmd = null;
ResultSet rs = null;
String table = null;
try {
// read table name
scan = new Scanner(System.in);
if(scan!= null) {
System.out.print("Enter table name:: ");
table = scan.next();
// table name is case-sensitive
// and in Oracle db all table names
// are in capital letter
// so convert table into uppercase
table = table.toUpperCase();
}
// load JDBC driver class
Class.forName("oracle.jdbc.driver.OracleDriver");
// establish the connection
con = DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:knowprogram",
"scott", "tiger");
// get Database MetaData object
dbmd = con.getMetaData();
// display database details
if(dbmd != null) {
// get table details
rs = dbmd.getColumns(null, null, table, null);
}
// process results
System.out.println("Columns of "+table+" table are:: ");
while(rs.next()) {
System.out.println(rs.getString(4));
}
} catch(SQLException se) {
se.printStackTrace();
} catch(ClassNotFoundException cnfe) {
cnfe.printStackTrace();
} catch(Exception e) {
e.printStackTrace();
}
finally {
try {
if(con!=null) con.close();
} catch(SQLException se) {
se.printStackTrace();
}
try {
if(rs!=null) rs.close();
} catch(SQLException se) {
se.printStackTrace();
}
}
}
}
The output:-
Enter table name:: emp
Columns of EMP table are::
EMPNO
ENAME
JOB
MGR
HIREDATE
SAL
COMM
DEPTNO
DNAME
References:- Oracle Docs for DatabaseMetaData
If you enjoyed this post, share it with your friends. Do you want to share more information about the topic discussed above or do you find anything incorrect? Let us know in the comments. Thank you!