Transcript Slide 1

JDBC™Technology-based
Metadata Recipes
Mahmoud Parsian
Server Architect
LimeLife, Inc.
http://www.limelife.com
Session ID#: BOF-0367
2006 JavaOneSM Conference | Session BOF-0367
Goal of This Talk
Learn how to use JDBC™ metadata in
homogenous and heterogeneous
database environments.
2006 JavaOneSM Conference | Session BOF-0367 |
2
Agenda: JDBC™ Metadata Recipes
What is metadata?
What is Database metadata?
What is JDBC metadata?
What is a reverse engineering of a DB?
Metadata in a homogenous environment
Metadata in a heterogeneous environment
2006 JavaOneSM Conference | Session BOF-0367 |
3
Agenda: JDBC™ Metadata Recipes
What is “metadata”?
What is Database metadata?
What is JDBC metadata?
What is a reverse engineering of a DB?
Metadata in a homogenous environment
Metadata in a heterogeneous environment
2006 JavaOneSM Conference | Session BOF-0367 |
4
What is Metadata?
●
●
●
●
Metadata Definition
Metadata Examples
Database Metadata Examples
Purpose of Metadata
2006 JavaOneSM Conference | Session BOF-0367 |
5
Metadata Definition
Metadata is data about data, which provide
structured, descriptive information about
other data.
Metadata (Greek: meta-+ Latin: data
“information”), literally “data about data”, is
information that describes another set of
data.
2006 JavaOneSM Conference | Session BOF-0367 |
6
Metadata Examples
Example-1: a library catalog card, which
contains data about the contents and
location of a book: It is data about the data
in the book referred to by the card.
2006 JavaOneSM Conference | Session BOF-0367 |
7
Metadata Examples:
A Library Catalog Card
A library catalog is an organized, searchable list of records
that identify, describe, and locate materials in one or
more library collections. Each record includes
information about the material, such as its:
●
●
●
●
●
●
●
Author(s)
Title
Publisher
Publication Date
Subject Heading(s)
Physical Appearance (size, number of pages, maps,
illustrations, etc.)
Location within a collection
2006 JavaOneSM Conference | Session BOF-0367 |
8
Database Metadata Examples
Example-1: what is the list of tables and
views owned by database user Alex?
Example-2: what is the signature of a
stored procedure called printPayroll?
Example-3: what is the list of SQL
keywords supported by a Connection
object?
2006 JavaOneSM Conference | Session BOF-0367 |
9
Purpose of Metadata
The purpose of the metadata is to make database
objects in the database more accessible to
users and to provide basic information about the
objects in the database's collection.
Each object within the database is described in a
record by fields, such as:
●
●
●
●
Table/View Names
Stored Procedure names & their signatures
Connection Properties
Result Set Properties
2006 JavaOneSM Conference | Session BOF-0367 |
10
Agenda: JDBC™ Metadata Recipes
What is “metadata”?
What is Database metadata?
What is JDBC metadata?
What is a reverse engineering of a DB?
Metadata in a homogenous environment
Metadata in a heterogeneous environment
2006 JavaOneSM Conference | Session BOF-0367 |
11
What is Database Metadata?
You can use database metadata to
●
●
●
●
●
●
Discover database schema and catalog information.
Discover database users, tables, views, and stored
procedures.
Understand and analyze the result sets returned by SQL
queries.
Find out the table, view, or column privileges.
Determine the signature of a specific stored procedure in the
database.
Identify the primary(PK)/foreign(FK) keys for a given table.
2006 JavaOneSM Conference | Session BOF-0367 |
12
Agenda: JDBC™ Metadata Recipes
What is metadata?
What is Database metadata?
What is JDBC metadata?
What is a reverse engineering of a DB?
Metadata in a homogenous environment
Metadata in a heterogeneous environment
2006 JavaOneSM Conference | Session BOF-0367 |
13
What is JDBC™ Metadata?
Metadata as Low-Level Objects
Metadata API
● DatabaseMetaData
● ResultSetMetaData
● ParameterMetaData
● DriverPropertyInfo
● RowSetMetaData
Metadata Examples
2006 JavaOneSM Conference | Session BOF-0367 |
14
Metadata as Low-Level Objects
2006 JavaOneSM Conference | Session BOF-0367 |
15
Metadata API
●
●
●
●
●
java.sql.DatabaseMetaData
java.sql.ResultSetMetaData
java.sql.ParameterMetaData
java.sql.DriverPropertyInfo
javax.sql.RowSetMetaData
2006 JavaOneSM Conference | Session BOF-0367 |
16
Metadata Example-1
Are Transactions Supported?
java.sql.Connection conn = getConnection(“datasourceName”);
java.sql.DatabaseMetaData meta = conn.getMetaData();
if (meta == null) {
// metadata not supported by the Driver
}
else {
// Check to see if transactions are supported
if (meta.supportsTransactions()) {
// Transactions are supported
}
else {
// Transactions are not supported
}
}
2006 JavaOneSM Conference | Session BOF-0367 |
17
Metadata Example-2:
What Are the Table Names for a Database?
private static final String[] DB_TABLE_TYPES = { "TABLE" };
private static final String[] DB_VIEW_TYPES = { "VIEW" };
java.sql.Connection conn = getConnection(“datasourceName”);
java.sql.DatabaseMetaData meta = conn.getMetaData();
java.sql.ResultSet tables = null;
java.sql.ResultSet views = null;
…
views = meta.getTables(null, null, null, DB_VIEW_TYPES);
tables = meta.getTables(null, null, null, DB_TABLE_TYPES);
while (tables.next()) {
String tableName = rs.getString(“TABLE_NAME”);
}
2006 JavaOneSM Conference | Session BOF-0367 |
18
Agenda: JDBC™ Metadata Recipes
What is metadata?
What is Database metadata?
What is JDBC metadata?
What is a reverse engineering of a DB?
Metadata in a homogenous environment
Metadata in a heterogeneous environment
2006 JavaOneSM Conference | Session BOF-0367 |
19
What is a reverse engineering
of a DB?
Generate the original schema from
database metadata:
Examples:
●
●
●
Torque: an object-relational mapper for Java
SQL2Java:object-relational mapping tool.
Abator: a code generator for the iBATIS data
mapping framework
2006 JavaOneSM Conference | Session BOF-0367 |
20
Agenda: JDBC™ Metadata Recipes
What is metadata?
What is Database metadata?
What is JDBC metadata?
What is a reverse engineering of a DB?
Metadata in a homogenous environment
Metadata in a heterogeneous environment
2006 JavaOneSM Conference | Session BOF-0367 |
21
Metadata in a
Homogenous Environment
Schema means the same for all DBs
Catalog means the same for all DBs
Connection
DatabaseMetaData
DriverManager
MySQL Driver
MySQL
MySQL
MySQL
2006 JavaOneSM Conference | Session BOF-0367 |
22
Agenda: JDBC™ Metadata Recipes
What is metadata?
What is Database metadata?
What is JDBC metadata?
What is a reverse engineering of a DB?
Metadata in a homogenous environment
Metadata in a heterogeneous environment
2006 JavaOneSM Conference | Session BOF-0367 |
23
Metadata in a Heterogeneous
Environment
●
●
●
●
●
Metadata in a Heterogeneous
Environment
Solving the Metadata Problem
Example-1: getDBNames()
Example-2: getTableNames()
DB Vendor Matters
2006 JavaOneSM Conference | Session BOF-0367 |
24
Metadata in a
Heterogeneous Environment
Schema does NOT mean the same for all DBs
Catalog does NOT mean the same for all DBs
Connection
DatabaseMetaData
DriverManager
MySQL Driver Oracle Driver
MySQL
Oracle
PostgreSQL Driver
DB2 Driver
PostgreSQL
2006 JavaOneSM Conference | Session BOF-0367 |
DB2
25
Solving the Metadata Problem in a
Heterogeneous Environment
import java.sql.Connection;
public class ConnectionObject {
private Connection conn = null;
private String databaseVendor = null;
ConnectionObject(Connection conn,
String databaseVendor) {
this.conn = conn;
this.databaseVendor = databaseVendor;
}
… get()/set() methods
}
2006 JavaOneSM Conference | Session BOF-0367 |
26
Example-1: getDBNames()
How do we get database names?
●
Oracle treats “schema” as a database name,
●
MySQL treats “catalog” as a database name.
●
In order to get the name of databases from
Oracle, you must use
DatabaseMetaData.getSchemas()
●
In order to get the name of databases from
MySQL, you must use
DatabaseMetaData.getCatalogs()
2006 JavaOneSM Conference | Session BOF-0367 |
27
Example-1: getDBNames()
public static ResultSet getDBNames(ConnectionObject conn)
throws SQLException {
ResultSet dbNames = null;
DatabaseMetaData meta = conn.getMetaData();
if (conn.isOracle()) {
dbNames = meta.getSchemas();
}
else if (conn.isMySQL()) {
dbNames = meta.getCatalogs();
}
else {
…
}
return dbNames;
}
2006 JavaOneSM Conference | Session BOF-0367 |
28
Example-2: getTableNames()
●
●
●
●
DatabaseMetaData.getTables() method returns
the table names for a given database connection object.
The getTables() method works well for MySQL
The getTables() does NOT work well for Oracle
databases (in addition to user’s tables, it returns system
tables, which are not needed)
To get a list of user-defined tables and views, we use the
Oracle’s metadata table called user_objects, which
keeps track of objects (tables, views, ...) owned by the
user. We may use the following SQL query:
select object_name from user_objects
where object_type = 'TABLE';
2006 JavaOneSM Conference | Session BOF-0367 |
29
Example-2: getTableNames()
public static ResultSet getTableNames(ConnectionObject conn)
throws SQLException {
ResultSet tableNames = null;
DatabaseMetaData meta = conn.getMetaData();
if (conn.isOracle()) {
tableNames = getOracleTableNames(conn); // cannot use metadata
}
else if (conn.isMySQL()) {
tableNames = meta.getTables(…);
}
else {
…
}
return tableNames;
}
2006 JavaOneSM Conference | Session BOF-0367 |
30
DB Vendor Matters
<?xml version='1.0'>
<databases>
<database id="db1“, dbVendor=“mysql”
<url>jdbc:mysql://localhost/octopus</url>
<driver>org.gjt.mm.mysql.Driver</driver>
<username>root</username><password>mysql</password>
...
</database>
<database id="db2“, dbVendor=“oracle”
<url>jdbc:oracle:thin:@localhost:1521:kitty</url>
<driver>oracle.jdbc.driver.OracleDriver</driver>
<username>scott</username><password>tiger</password>
...
</database>
...
</databases>
2006 JavaOneSM Conference | Session BOF-0367 |
31
Summary
●
●
JDBC API enable us to get metadata
JDBC metadata can be used to develop
●
●
●
●
GUI database applications
SQL Adapters and Connectors
Reverse engineer the whole database
In using JDBC metadata, make sure to consider
the “vendor” factor (this will make your DB
applications to work in homogenous and
heterogeneous environments)
2006 JavaOneSM Conference | Session BOF-0367 |
32
For More Information
●
Metadata Definition: http://en.wikipedia.org/wiki/Metadata
●
JDBC™ Basics:
http://java.sun.com/docs/books/tutorial/jdbc/basics/
●
JDBC™ API Tutorial and Reference, Third Edition
By Maydene Fisher,…, Addison Wesley, 2003.
●
JDBC™ Metadata, MySQL and Oracle Recipes,
Mahmoud Parsian, Apress.com, 2006
●
Understanding JDBC™ Metadata by Kyle Brown
http://members.aol.com/kgb1001001/Articles/JDBCMetadata/JD
BC_Metadata.htm
2006 JavaOneSM Conference | Session BOF-0367 |
33
Q&A
Mahmoud Parsian
[email protected]
[email protected]
2006 JavaOneSM Conference | Session XXXX |
34