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 discuss ResultSetMetaData and ParameterMetaData in Java.
Data about data is called MetaData. 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 database software, tables, and query params.
JDBC supports 3 modes of metadata
1) DatabaseMetaData:- To gather more info underlying Database software and its limitations and capabilities
2) ResultSetMetaData:- To gather more info about Database tables represented by ResultSet object like column names, column data types, column size e.t.c.
3) ParameterMetaData:- It gives more info about parameters (?) that are there in SQL Queries represented by PreparedStatement object, CallableStatement object…
- The ResultSetMetaData in Java is used to gather more info about database tables represented by ResultSet objects like column names, column data types, column size and etc
- ResultSetMetaData object means, it is an object of the underlying JDBC driver software supplied Java class that implements java.sql.ResultSetMetaData interface.
- To create the object
ResultSet rs = st.executeQuery(
"select sno, sname, sadd. avg from student");
ResultSetMetaData rsmd = rs.getMetaData();
- we can call more methods on “rsmd” to get more info database table like column names, column data types, column sizes, and e.t.c.
In ResultSetMetaData docs, we can get all methods of the ResultSetMetaData interface. The important methods of ResultSetMetaData interface are,
getColumnCount():-
It returns the number of columns in this ResultSet object. Its return type is int.getColumnLabel(-)
:- It gets the designated column’s suggested title for use in printouts and displays. Its return type is String.getColumnTypeName(-):-
It retrieves the designated column’s database-specific type name, and it returns String.isSigned():-
It
indicates whether values in the designated column are signed numbers, and its return type is boolean.isWritable():-
It
indicates whether it is possible for a write on the designated column to succeed. It returns a boolean value.isReadOnly():-
It indicates whether the designated column is definitely not writable, and it also returns a boolean value.
The ResultSetMetaData is useful for report generation. In the report, we display details along with their column name. GUI database tools are created by using ResultSetMetaData, when we select schema then it gives all tables, e.t.c
Java Program to Display table details using ResultSetMetaData
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
public class ResultSetMetaDataTest {
public static void main(String[] args) {
Connection con = null;
Statement st = null;
ResultSet rs = null;
ResultSetMetaData rsmd = null;
int columnCount = 0;
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");
// create Statement object
if(con != null)
st = con.createStatement();
// send and execute SQL query to database object
if(st != null)
rs = st.executeQuery("SELECT * FROM EMP");
// get ResultSet MetaData object
if(rs!= null)
rsmd = rs.getMetaData();
// get column number
if(rsmd != null)
columnCount = rsmd.getColumnCount();
// print column names
if(rsmd != null) {
for(int i=1; i<=columnCount; i++)
System.out.print(rsmd.getColumnLabel(i)+" ");
}
System.out.println();
// display data type of columns
if(rsmd != null) {
for(int i=1; i<=columnCount; i++)
System.out.print(rsmd.getColumnTypeName(i)+" ");
}
System.out.println();
// process the result
if(rsmd != null) {
while(rs.next()) {
for(int i=1; i<=columnCount; i++)
System.out.print(rs.getString(i)+" ");
System.out.println();// new line
}
}
} 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(st!=null) st.close();
} catch(SQLException se) {
se.printStackTrace();
}
try {
if(rs!=null) rs.close();
} catch(SQLException se) {
se.printStackTrace();
}
}
}
}
Output:-
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DNAME
NUMBER VARCHAR2 VARCHAR2 NUMBER DATE NUMBER NUMBER NUMBER VARCHAR2
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 null 20 RESEARCH
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30 SALES
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30 SALES
..............................................................
..............................................................
ParameterMetaData
It gives more info about parameters (?) that are there in SQL Queries represented by PreparedStatement object, CallableStatement object…
Note:- Most of the JDBC drivers are not supporting ParameterMetaData concepts. Even the latest driver also doesn’t support it. As part of the JDBC technology, the ParameterMetaData interface is there but their implementations are not given for this interface.
To create this object,
PreparedStatement ps = con.prepareStatement(
"insert into student values(?,?,?,?)");
ParameterMetaData pmd = ps.getParameterMetaData();
Query = "Select sno, sname, sadd, avg from"+
" student where sno=?"
// create ParameterMetaData object
ParameterMetaData pmd = null;
if(ps!=null)
pmd = ps.getParameterMetaData();
// get parameters count and details
if(pmd != null) {
paramCnt = pmd.getParameterCount();
for(int i=1; i<=paramCnt; ++i) {
sysout(i+ " param mode::" + pmd.getParameterMode(i));
sysout(i+ " param type::" + pmd.getParameterTypeName(i));
sysout(i+ " param is signed?::" + pmd.isSigned(i));
}
}
Also see:-
JDBC program to display all tables in a database using DatabaseMetaData
JDBC program to display all columns of a given table using 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!