JAVA: Meta Data

<< JAVA: Result Set

Java Graphics >>

Web Design & Development CS506

Lesson 17

Meta Data

In simple terms, Meta Data is data (information) about data. The actual data has no meaning without

existence of Meta data. To clarify this, let's look at an example. Given below are listed some numeric

values

What this information about? We cannot state accurately. These values might be representing some one's

salaries, price, tax payable & utility bill etc. But if we specify Meta data about this data like shown below:

Now, just casting a glance on these values, you can conclude that it's all about some ones salaries.

ResultSet Meta data

ResultSet Meta Data will help you in answering such questions

-How many columns are in the ResultSet?

-What is the name of given column?

-Are the column name case sensitive?

-What is the data type of a specific column?

-What is the maximum character size of a column?

-Can you search on a given column?

Creating ResultSetMetaData object

From a ResultSet (the return type of executeQuery() ), derive a ResultSetMetaData object by calling

getMetaData() method as shown in the given code snippet (here rsis a valid ResultSetobject):

ResultSetMetaData rsmd = rs.getMetaData();

Now, rsmd can be used to look up number, names & types of columns

Useful ResultSetMetaData methods

getColumnCount ( )

Returns the number of columns in the result set

getColumnDisplaySize (int)

Returns the maximum width of the specified column in characters

getColumnName(int) / getColumnLabel (int)

The getColumnName() method returns the database name of the column

The getColumnLabel() method returns the suggested column label for printouts

getColumnType (int)

Returns the SQL type for the column to compare against types in java.sql.Types

Example Code 17.1: Using ResultSetMetaData

The MetaDataEx.java will print the column names by using ResultSetMetaData object and column values

on console. This is an excellent example of the scenario where we have no idea about the column names in

advance

Note: For this example code and for the coming ones, we are using the same database (PersonInfo) the one

we created earlier and repeatedly used. Changes are shown in bold face

// File MetaDataEx.java

import java.sql.*;

public class MetaDataEx {

public static void main (String args[ ]) {

133

Web Design & Development CS506

try {

//Step 2: load driver

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

//Step 3: define the connection URL

String url = "jdbc:odbc:personDSN";

//Step 4: establish the connection

Connection con = null;

con = DriverManager.getConnection(url, "", "");

//Step 5: create PrepareStatement by passing sql and

// ResultSet appropriate fields

String sql = "SELECT * FROM Person";

PreparedStatement pStmt = con.prepateStatement(sql,

ResultSet.TYPE_SCROLL_INSENSITIVE,

ResultSet.CONCUR_UPDATABLE);

//Step 6: execute the query

ResultSet rs = pStmt.executeQuery();

// get ResultSetMetaData object from rs

ResultSetMetaData rsmd = rs.getMetaData( );

// printing no. of column contained by rs

int numColumns = rsmd.getColumnCount();

System.out.println("Number of Columns:" + numColumns);

// printing all column names by using for loop

String cName;

for(int i=1; i<= numColumns; i++) {

cName = rsmd.getColumnName(i);

System.out.println(cName);

System.out.println("\t");

}

// changing line or printing an empty string

System.out.println(" ");

// printing all values of ResultSet by iterating over it

String id, name, add, ph;

while( rs.next() )

{

id = rs.getString(1);

name = rs.getString(2);

add = rs.getString(3);

ph = rs.getString(4);

System.out.println(id);

System.out.println("\t");

System.out.println(name);

System.out.println("\t");

System.out.println(add);

System.out.println("\t");

System.out.println(ph);

System.out.println(" ");

}

//Step 8: close the connection

con.close();

}catch(Exception sqlEx){

System.out.println(sqlEx);

}

} // end main101.} // end class

134

Web Design & Development CS506

Compile & Execute:

The database contains the following values at the time of execution of this program. The database and the

output are shown below:

DataBaseMetaData

DataBase Meta Data will help you in answering such questions

What SQL types are supported by DBMS to create table?

What is the name of a database product?

What is the version number of this database product?

What is the name of the JDBC driver that is used?

Is the database in a read-only mode?

Creating DataBaseMetaData object

From a Connection object, a DataBaseMetaData object can be derived. The following code snippet

demonstrates how to get DataBaseMetaDataobject.

Connection con= DriverManager.getConnection(url, usr, pwd);

DataBaseMetaData dbMetaData = con.getMeataData();

Now, you can use the dbMetaData to gain information about the database.

Useful ResultSetMetaData methods

. getDatabaseProductName( )

Returns the name of the database's product name

. getDatabaseProductVersion( )

Returns the version number of this database product

. getDriverName( )

Returns the name of the JDBC driver used to established the connection

. isReadOnly( )

Retrieves whether this database is in read-only mode

Returns true if so, false otherwise

Example Code 17.2: using DataBaseMetaData

This code is modification of the example code 17.1. Changes made are shown in bold face.

135

Web Design & Development CS506

102.// File MetaDataEx.java 103.import java.sql.*; 104.public class MetaDataEx {

105. public static void main (String args[ ]) {

106. try {

107. //Step 2: load driver

108. Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

109. //Step 3: define the connection URL

110. String url = "jdbc:odbc:personDSN";

111. //Step 4: establish the connection

112. Connection con = null;

113. con = DriverManager.getConnection(url, "", "");

114. // getting DataBaseMetaDat object

115. DataBaseMetaData dbMetaData = con.getMetaData();

116. // printing database product name

117. Sring pName = dbMetaData.getDatabaseProductName();

118. System.out.println("DataBase: " + pName);

119. // printing database product version

120. Sring pVer = dbMetaData.getDatabaseProductVersion();

121. System.out.println("Version: " + pVer);

122. // printing driver name used to establish connection &

123. // to retrieve data

124. Sring dName = dbMetaData.getDriverName();

125. System.out.println("Driver: " + dName);

126. // printing whether database is read-only or not

127. boolean rOnly = dbMetaData.isReadOnly();

128. System.out.println("Read-Only: " + rOnly);

129. // you can create & execute statements and can

130. // process results over here if needed

131. //Step 8: close the connection

132. con.close();

133. }catch(Exception sqlEx){

134. System.out.println(sqlEx);

135. }

136. } // end main

} // end class

137.

Compile & Execute

On executing the above program, the following output will produce:

JDBC Driver Types

JDBC Driver Types are divided into four types or levels.

136

Web Design & Development CS506

Each type defines a JDBC driver implementation with increasingly higher level of platform

independence, performance, deployment and administration.

The four types are:

Type 1: JDBC ODBC Bridge

Type 2: Native API/partly Java driver

Type 3: Net protocol/allJava driver

Type 4: Native protocol/allJava driver

Now, let's look at each type in more detail

Type 1: JDBC ODBC Bridge

-Translates all JDBC calls into ODBC (Open Database

Connectivity) calls and send them to the ODBC Driver -

Generally used for Microsoft database. -Performance is

degraded

4. Type 2: Native API/partly Java driver

-Converts JDBC calls into database-specific calls such as SQL Server, Informix, Oracle or Sybase.

-Partly-Java drivers communicate with database-specific API (which may be in C/C++) using the Java

Native Interface.

-Significantly better Performance than the JDBC-ODBC bridge

4. Type 3: Net protocol/allJava driver

-Follows a three-tiered approach whereby the JDBC database requests ()are passed through the network to

the middle-tier server

-Pure Java client to server drivers which send requests that are not database-

specific to a server that translates them into a database-specific protocol. . -If the middle-tier server is

written in java, it can use a type 1or type 2JDBC driver

to do this

137

Web Design & Development CS506

4. Type 4: Native protocol / all java driver

-Converts JDBC calls into the vendor-specific DBMS protocol so that client application can communicate

directly with the database server

-Completely implemented in Java to achieve platform independence and eliminate deployment issues.

-Performance is typically very good

On Line Resources

· Sun's JDBC Site

http://java.sun.com/products/jdbc/

· JDBC Tutorial

http://java.sun.com/docs/books/tutorial/jdbc/

· List of available JDBC Drivers

http://industry.java.sun.com/products/jdbc/drivers/

· RowSet Tutorial

http://java.sun.com/developer/Books/JDBCTutorial/chapter5.html

· JDBC RowSets Implementation Tutorial

http://java.sun.com/developer/onlineTraining/ Database/jdbcrowsets.pdf

References:

· Java API documentation 5.0

· Java A Lab Course by Umair Javed

· JDBC drivers in the wild

http://www.javaworld.com/javaworld/jw-07-2000/jw-0707-jdbc_p.html

138

Table of Contents: