Jdbc technology based metadata recipes
Download
1 / 34

JDBC ™ Technology-based Metadata Recipes - PowerPoint PPT Presentation


  • 92 Views
  • Uploaded on

JDBC ™ Technology-based Metadata Recipes. Mahmoud Parsian Server Architect LimeLife, Inc. http://www.limelife.com. Session ID#: BOF-0367. Goal of This Talk. Learn how to use JDBC ™ metadata in homogenous and heterogeneous database environments. Agenda: JDBC ™ Metadata Recipes.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' JDBC ™ Technology-based Metadata Recipes' - lamar


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Jdbc technology based metadata recipes

JDBC™Technology-based Metadata Recipes

Mahmoud Parsian

Server ArchitectLimeLife, Inc.

http://www.limelife.com

Session ID#: BOF-0367


Goal of this talk
Goal of This Talk

Learn how to use JDBC™metadata in homogenous and heterogeneous database environments.


Agenda jdbc metadata recipes
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


Agenda jdbc metadata recipes1
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


What is metadata
What is Metadata?

  • Metadata Definition

  • Metadata Examples

  • Database Metadata Examples

  • Purpose of Metadata


Metadata definition
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.


Metadata examples
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.


Metadata examples a library catalog card
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


Database metadata examples
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?


Purpose of metadata
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


Agenda jdbc metadata recipes2
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


What is database metadata
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.


Agenda jdbc metadata recipes3
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


What is jdbc metadata
What is JDBC™ Metadata?

Metadata as Low-Level Objects

Metadata API

  • DatabaseMetaData

  • ResultSetMetaData

  • ParameterMetaData

  • DriverPropertyInfo

  • RowSetMetaData

    Metadata Examples



Metadata api
Metadata API

  • java.sql.DatabaseMetaData

  • java.sql.ResultSetMetaData

  • java.sql.ParameterMetaData

  • java.sql.DriverPropertyInfo

  • javax.sql.RowSetMetaData


Metadata example 1 are transactions supported
Metadata Example-1Are 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

}

}


Metadata example 2 what are the table names for a database
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”);

}


Agenda jdbc metadata recipes4
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


What is a reverse engineering of a db
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


Agenda jdbc metadata recipes5
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


Metadata in a homogenous environment
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


Agenda jdbc metadata recipes6
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


Metadata in a heterogeneous environment
Metadata in a Heterogeneous Environment

  • Metadata in a Heterogeneous Environment

  • Solving the Metadata Problem

  • Example-1: getDBNames()

  • Example-2: getTableNames()

  • DB Vendor Matters


Metadata in a h eterogeneous environment
Metadata in a HeterogeneousEnvironment

Schema does NOT mean the same for all DBs

Catalog does NOT mean the same for all DBs

Connection

DatabaseMetaData

DriverManager

Oracle Driver

PostgreSQL Driver

DB2 Driver

MySQL Driver

MySQL

Oracle

PostgreSQL

DB2


Solving the metadata problem in a heterogeneous environment
Solving the Metadata Problem in a HeterogeneousEnvironment

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

}


Example 1 getdbnames
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()


Example 1 getdbnames1
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;

}


Example 2 gettablenames
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';


Example 2 gettablenames1
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;

}


Db vendor matters
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>


Summary
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)


For more information
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/JDBC_Metadata.htm


Q&A

  • Mahmoud Parsian

  • [email protected]

  • [email protected]


ad