Meta Data

• What if you want to know:

– How many columns are in the result set?
– What is the name of a given column?
– Are the column names 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?

1 JDBC
 Using ResultSetMetaData
• Idea

– From a ResultSet (the return type of executeQuery), derive a

ResultSetMetaData object

– Use that object to look up the number, names, and types of columns

2 JDBC
 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
3 JDBC
 Example Code: MetaDataEx
using ResultSetMetaData
import java.sql.*;

public class MetaDataEx {

public static void main ( String args[ ]) {

try {

Class.forName(“Driver name”);

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

String sql = “SELECT * FROM Person”;

PreparedStatement pStmt = con.prepareStatement(sql);

ResultSet rs = pStmt.executeQuery( );
4 JDBC
 Example Code: MetaDataEx (cont.)
using ResultSetMetaData

ResultSetMetaData rsmd = rs.getMetaData();

int numColumns = rsmd.getColumnCount();

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

String cName;
for (int i=1; i <= numColumns; i++)
{
cName = rsmd.getColumnName(i);
System.out.print(cName);
System.out.print("\t");
}

// changing line
System.out.println("");
5 JDBC
 Example Code: MetaDataEx (cont.)
using ResultSetMetaData
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.print(id);
System.out.print("\t");
System.out.print(name);
System.out.print("\t");
System.out.print(add);
System.out.print("\t");
System.out.print(ph);
System.out.println("");
}
6 JDBC
 Example Code: MetaDataEx (cont.)
using ResultSetMetaData

con.close();

} catch (Exception ex) {

System.out.println(ex);
}

}// end main

}//end class

7 JDBC
 Example Code: MetaDataEx
Compile & Execute

8 JDBC
 DatabaseMetaData

• What if we want to know

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

9 JDBC
 Using DatabaseMetaData

• Idea

– From a Connection, derive a DatabaseMetaData object

– Contains the comprehensive information about the database as a

whole

10 JDBC
 Using DatabaseMetaData

• Idea

– From a Connection, derive a DatabaseMetaData object

– Contains the comprehensive information about the database as a

whole

11 JDBC
 Useful DataBaseMetaData Methods
• getDatabaseProductName ( )
– Returns the name of the database 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.

12 JDBC
 Example Code: Modify MetaDataEx
using DataBaseMetaData
import java.sql.*;

public class MetaDataEx {

public static void main ( String args[ ]) {

try {

Class.forName(“Driver name”);

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

DatabaseMetaData dbMetadata = con.getMetaData();

……

13 JDBC
 Example Code: Modify MetaDataEx
using DataBaseMetaData

String pName = dbMetaData.getDatabaseProductName();

System.out.println(“Database: ” + pName);

String pVer = dbMetaData.getDatabaseProductVersion();

System.out.println(“Version: ” + pVer);

String dName = dbMetaData.getDriverName();

System.out.println(“Driver: ” + dName);

boolean rOnly = dbMetaData.isReadOnly();

System.out.println(“Read-Only: ” + rOnly);

……

14 JDBC
 Example Code: Modify MetaDataEx
using DataBaseMetaData

// create Statement & execute query

// process results

con.close();

}catch ( Exception ex) {

System.out.printl(ex);
}
} // end main
} // end class
15 JDBC
 Example Code: Modify MetaDataEx
Compile & Execute

16 JDBC
 RowSet
• A JDBC RowSet object holds tabular data in a way
that makes it more flexible and easier to use than a
result set.

• Interface RowSet configures the database

connection and prepares query statements
automatically.

• It is part of package javax.sql.

• It is part of J2SE, but it is normally used in the

context of J2EE.

17 JDBC
 RowSet (cont.)
There are two kinds of RowSet objects:

• Connected
– Makes the connection to the database and stays connected
until the application ends

• Disconnected
– Connects, queries the database, then closes.
– Connection can be reestablished for updates.

18 JDBC
 RowSet (cont.)
JDBC provides the five versions of the
RowSets.Two of them are:
3. JdbcRowSet
– Connected RowSet that wraps a ResultSet object,
allowing scrolling and updating.
– It is most similar to a ResultSet object.

19 JDBC
 RowSet (cont.)

2. CachedRowSet
– Disconnected RowSet that is scrollable and updateable.
– It caches the data of a ResultSet in memory.
– Manipulate data and make changes to data while it is
disconnected.
– Reconnect to the data source to write changes back to it.
– It is also serializable, so it can be sent across a network.

20 JDBC
 Web
Design & Development

JDBC Drivers
Types

21
 JDBC Driver Types
• JDBC drivers are divided into four types or levels.
• Each type defines a JDBC driver implementation
with increasingly higher level of platform
independence, performance, deployment and
administration.
• The four types are:

1. Type 1: JDBC – ODBC Bridge

3. Type 2: Native – API/partly Java driver
5. Type 3: Net – protocol/all–Java driver
7. Type 4: Native – protocol/all–Java driver

22 JDBC
 JDBC Driver Types (cont.)

2. Type 1: JDBC – ODBC Bridge

23 JDBC
 JDBC Driver Types (cont.)

2. Type 2: Native – API/partly Java driver

24 JDBC
 JDBC Driver Types (cont.)

2. Type 3: Net – protocol/all–Java driver

25 JDBC
 JDBC Driver Types (cont.)

2. Type 4:Native – protocol/all–Java driver

26 JDBC
 Summary of Driver Types
Type 1 Type 2

Type 3 Type 4

27 JDBC
 General Design Guideline

class Employee {

String name;
String sal;

// constructor
// getter / setters
Database connectivity & business logic
void update( ){ all in one class
// connect database
// execute query
// process results
}

void insertEmp ( ){ ….. }

void calculateTax ( ) { …. }

}
28 JDBC
 General Design Guideline
Business Logic Database Connectivity
//step 1
class Employee {
public class DAO {
String name; public DAO ( ) {
String sal; //step 2 - 5
……….
// constructor ……….
// getter / setters }

void update( ){ public void update (String n, String s)

{
DAO dao = new DAO(); //step 6 –7
dao.update(name, sal); …………
………….
}
}
void insertEmp ( ){ ….. } public void insert(….){…..}
protected void finalize() {
void calculateTax ( ) { …. }
//step 8 – close connection
}
}
}
29 JDBC
 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/

• API for java.sql

– http://java.sun.com/j2se/1.3/docs/api/java/sql/
package-summary.html
30 JDBC