JDBC Metadata, MySQL, and Oracle recipes

Mahmoud Parsian

JDBC Metadata, MySQL,

and Oracle Recipes

A Problem-Solution Approach

6374_FM_final.qxd 1/27/06 12:38 PM Page i

JDBC Metadata, MySQL, and Oracle Recipes: A Problem-Solution Approach

Copyright © 2006 by Mahmoud Parsian

All rights reserved. No part of this work may be reproduced or transmitted in any form or by any means,

electronic or mechanical, including photocopying, recording, or by any information storage or retrieval

system, without the prior written permission of the copyright owner and the publisher.

ISBN-13: 978-1-59090-637-1

ISBN-10: 1-59059-637-4

Printed and bound in the United States of America 987654321

Trademarked names may appear in this book. Rather than use a trademark symbol with every occurrence

of a trademarked name, we use the names only in an editorial fashion and to the benefit of the trademark

owner, with no intention of infringement of the trademark.

Lead Editor: Steve Anglin

Technical Reviewer: Sumit Pal

Editorial Board: Steve Anglin, Dan Appleman, Ewan Buckingham, Gary Cornell, Jason Gilmore,

Jonathan Hassell, James Huddleston, Chris Mills, Matthew Moodie, Dominic Shakeshaft, Jim Sumser,

Matt Wade

Project Managers: Beckie Brand, Elizabeth Seymour

Copy Edit Manager: Nicole LeClerc

Copy Editor: Liz Welch

Assistant Production Director: Kari Brooks-Copony

Production Editor: Lori Bring

Compositor: Linda Weideman, Wolf Creek Press

Proofreader: Dan Shaw

Indexer: Lucie Haskins

Artist: Kinetic Publishing Services, LLC

Cover Designer: Kurt Krames

Manufacturing Director: Tom Debolski

Distributed to the book trade worldwide by Springer-Verlag New York, Inc., 233 Spring Street, 6th Floor,

New York, NY 10013. Phone 1-800-SPRINGER, fax 201-348-4505, e-mail [email protected], or

visit http://www.springeronline.com.

For information on translations, please contact Apress directly at 2560 Ninth Street, Suite 219, Berkeley,

CA 94710. Phone 510-549-5930, fax 510-549-5939, e-mail [email protected], or visit http://www.apress.com.

The information in this book is distributed on an “as is” basis, without warranty. Although every precaution

has been taken in the preparation of this work, neither the author(s) nor Apress shall have any liability to

any person or entity with respect to any loss or damage caused or alleged to be caused directly or indirectly

by the information contained in this work.

The source code for this book is available to readers at http://www.apress.com in the Source Code section.

6374_FM_final.qxd 1/27/06 12:38 PM Page ii

This book is dedicated to my dears

my beautiful wife, Behnaz;

my gozal daughter, Maral;

my gibldiz son, Yaseen, who taught me how to play Yu-Gi-Oh!

my mother, Monireh, who taught me my mother

language and introduced me to the world of computer science;

memory of my father, Bagher,

who taught me honesty and hard work;

my angel sister, Nayer Azam;

and my brother, Ahmad

6374_FM_final.qxd 1/27/06 12:38 PM Page iii

6374_FM_final.qxd 1/27/06 12:38 PM Page iv

Contents at a Glance

About the Author . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xiii

About the Technical Reviewer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xv

Acknowledgments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xvii

Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xix

■CHAPTER 1 What Is JDBC Programming?. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1

■CHAPTER 2 Database Metadata, Part 1 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19

■CHAPTER 3 Database Metadata, Part 2 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 89

■CHAPTER 4 ResultSet Metadata. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 155

■CHAPTER 5 Parameter Metadata . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 185

■CHAPTER 6 Exploring Driver Property Information . . . . . . . . . . . . . . . . . . . . . . . . . 217

■CHAPTER 7 RowSet Metadata. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 239

■CHAPTER 8 Web Access to Metadata, Part 1. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 307

■CHAPTER 9 Web Access to Metadata, Part 2. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 381

■CHAPTER 10 RDF and JDBC Metadata . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 461

■INDEX . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 467

v

6374_FM_final.qxd 1/27/06 12:38 PM Page v

6374_FM_final.qxd 1/27/06 12:38 PM Page vi

Contents

About the Author . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xiii

About the Technical Reviewer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xv

Acknowledgments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xvii

Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xix

■CHAPTER 1 What Is JDBC Programming? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1

1.1. What Is JDBC? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2

1.2. What Is JDBC Programming?. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4

1.3. How Do You Handle JDBC Errors/Exceptions? . . . . . . . . . . . . . . 10

1.4. What Is JDBC Metadata Programming?. . . . . . . . . . . . . . . . . . . . 14

1.5. What Is an Example of JDBC Metadata Programming? . . . . . . 15

■CHAPTER 2 Database Metadata, Part 1 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19

2.1. What Is Metadata?. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21

2.2. What Is Database Metadata? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22

2.3. How Do You Discover Database Metadata?. . . . . . . . . . . . . . . . . 24

2.4. What Is JDBC’s Answer to Database Metadata? . . . . . . . . . . . . 25

2.5. What Is the Vendor Name Factor in Database Metadata?. . . . . 27

2.6. How Do You Find JDBC’s Driver Information? . . . . . . . . . . . . . . . 29

2.7. What Are a Database’s SQL Keywords?. . . . . . . . . . . . . . . . . . . . 33

2.8. What Are the Available SQL Data Types? . . . . . . . . . . . . . . . . . . . 37

2.9. What Are Catalogs and Schemas? . . . . . . . . . . . . . . . . . . . . . . . . 41

2.10. What Are the Table Names for a Database? . . . . . . . . . . . . . . . 44

2.11. What Are the View Names for a Database?. . . . . . . . . . . . . . . . 50

2.12. Does a Table Exist in a Database? . . . . . . . . . . . . . . . . . . . . . . . 53

2.13. What Are a Table’s Column Names?. . . . . . . . . . . . . . . . . . . . . . 58

2.14. What Are the Table Types Used in a Database? . . . . . . . . . . . . 64

2.15. What Are the Primary Keys for a Table?. . . . . . . . . . . . . . . . . . . 66

2.16. What Are a Table’s Privileges?. . . . . . . . . . . . . . . . . . . . . . . . . . . 69

2.17. What Are a Table Column’s Privileges? . . . . . . . . . . . . . . . . . . . 74

vii

6374_FM_final.qxd 1/27/06 12:38 PM Page vii

2.18. How Do You Find the Number of Rows Affected by

a SQL Query? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79

2.19. What Is a Table’s Optimal Set of Columns

That Uniquely Identify a Row? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81

■CHAPTER 3 Database Metadata, Part 2 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 89

3.1. What Are a Table’s Indexes?. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 89

3.2. Does an Index Exist for a Specific Table? . . . . . . . . . . . . . . . . . 102

3.3. What Are the Names of a Database’s Stored Procedures?. . . 104

3.4. What Is the Signature of a Stored Procedure? . . . . . . . . . . . . . 112

3.5. What Is the Username of the Database Connection?. . . . . . . . 122

3.6. Is the Database Connection Read-Only? . . . . . . . . . . . . . . . . . . 123

3.7. What Is the JDBC’s Driver Information? . . . . . . . . . . . . . . . . . . . 123

3.8. How Can You Determine Where a Given Table Is

Referenced via Foreign Keys? . . . . . . . . . . . . . . . . . . . . . . . . . . . . 128

3.9. What Foreign Keys Are Used in a Table? . . . . . . . . . . . . . . . . . . 137

3.10. What Is the JDBC View of a Database’s

Internal Structure? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 148

3.11. Does a Database Support Batching? . . . . . . . . . . . . . . . . . . . . 150

■CHAPTER 4 ResultSet Metadata . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 155

4.1. What Is ResultSet Metadata?. . . . . . . . . . . . . . . . . . . . . . . . . . . . 156

4.2. What Is a ResultSetMetaData Object? . . . . . . . . . . . . . . . . . . . . 156

4.3. How Do You Create a ResultSetMetaData Object?. . . . . . . . . . 157

4.4. How Does JDBC Define ResultSetMetaData? . . . . . . . . . . . . . . 160

4.5. What Is the Weakness of the ResultSetMetaData

Interface? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 163

4.6. What Is the Relationship of ResultSetMetaData to

Other Objects? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 163

4.7. How Do You Express ResultSetMetaData in XML? . . . . . . . . . . 164

4.8. How Do You Get a Table’s Metadata Without

Selecting Any Rows? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 174

4.9. How Do You Retrieve the Column Types from

a ResultSet? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 179

4.10. How Do You Retrieve the Column Name/Data/Type

from a ResultSet?. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 180

4.11. What Is ResultSet Holdability?. . . . . . . . . . . . . . . . . . . . . . . . . . 182

viii ■CONTENTS

6374_FM_final.qxd 1/27/06 12:38 PM Page viii

■CHAPTER 5 Parameter Metadata . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 185

5.1. What Are Dynamic Parameters? . . . . . . . . . . . . . . . . . . . . . . . . . 186

5.2. What Is ParameterMetaData? . . . . . . . . . . . . . . . . . . . . . . . . . . . 189

5.3. What Is the Definition of ParameterMetaData?. . . . . . . . . . . . . 190

5.4. How Do You Create a ParameterMetaData Object? . . . . . . . . . 192

5.5. How Do You Get Information from a

ParameterMetaData Object?. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 199

5.6. How Do You Get XML Information from a

ParameterMetaData Object?. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 203

5.7. Why Should You Use ParameterMetadata Wisely? . . . . . . . . . . 207

5.8. How Do You Get ParameterMetadata from

Stored Procedures (Oracle)?. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 208

5.9. How Do You Get ParameterMetadata from

Stored Procedures (MySQL)? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 212

■CHAPTER 6 Exploring Driver Property Information. . . . . . . . . . . . . . . . . . . . 217

6.1. What Is DriverPropertyInfo? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 217

6.2. How Do You Create a DriverPropertyInfo Object?. . . . . . . . . . . 218

6.3. What Is a Sample Application of DriverPropertyInfo? . . . . . . . 224

6.4. What Connection Properties Should You Supply to a

Database Driver in Order to Connect to a Database?. . . . . . . . . 225

6.5. How Does MySQL’s Driver Implement

Driver.getPropertyInfo()? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 231

■CHAPTER 7 RowSet Metadata . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 239

7.1. What Is a RowSet Object? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 240

7.2. What Are the Relationships Between Key Classes and

Interfaces for Rowsets?. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 243

7.3. What Is a JdbcRowSet? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 244

7.4. What Is a CachedRowSet? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 247

7.5. What Is a WebRowSet? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 252

7.6. What Is a FilteredRowSet? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 263

7.7. How Do You Get Metadata from a FilteredRowSet Object?. . . 268

7.8. What Is a SyncResolver? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 273

7.9. How Do You Create a RowSet Object? . . . . . . . . . . . . . . . . . . . . 276

7.10. What Is a RowSetMetaData Object?. . . . . . . . . . . . . . . . . . . . . 279

7.11. How Do You Create a RowSetMetaData Object? . . . . . . . . . . 280

7.12. What Is a WebRowSet Object? . . . . . . . . . . . . . . . . . . . . . . . . . 283

■CONTENTS ix

6374_FM_final.qxd 1/27/06 12:38 PM Page ix

7.13. How Do You Create a WebRowSet Object? . . . . . . . . . . . . . . . 283

7.14. How Do You Get WebRowSet’s Metadata as XML? . . . . . . . . 289

7.15. How Do You Retrieve Metadata from

RowSetMetaData? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 293

7.16. How Can You Create a Custom

RowSetMetaData Object? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 294

7.17. How Can You Instantiate and Load a New

CachedRowSet Object from a Non-JDBC Source? . . . . . . . . . . . 298

■CHAPTER 8 Web Access to Metadata, Part 1 . . . . . . . . . . . . . . . . . . . . . . . . . . 307

8.1. How Do Web Clients and Servers Communicate? . . . . . . . . . . 308

8.2. What Is a Java Servlet in a Nutshell? . . . . . . . . . . . . . . . . . . . . . 309

8.3. What Is CGI? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 311

8.4. How Does a Java Servlet Work? . . . . . . . . . . . . . . . . . . . . . . . . . 311

8.5. How Does a Servlet Talk to a Servlet Container? . . . . . . . . . . . 314

8.6. What Are the Advantages of Java Servlets? . . . . . . . . . . . . . . . 315

8.7. What Is the Life Cycle of a Servlet? . . . . . . . . . . . . . . . . . . . . . . 315

8.8. What Is a Very Simple Servlet Example Using JDBC? . . . . . . . 317

8.9. How Do You Get a List of Table Types for a Database? . . . . . . 320

8.10. How Do You Get a List of Table Names for a Database?. . . . 324

8.11. How Do You Get a List of View Names for a Database? . . . . 331

8.12. How Do You Get a List of Tables and Views for

a Database?. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 338

8.13. How Do You Get a List of SQL’s Keywords?. . . . . . . . . . . . . . . 347

8.14. How Do You Get a Table’s Primary Key Columns? . . . . . . . . . 351

8.15. How Do You Get a Table’s Columns? . . . . . . . . . . . . . . . . . . . . 358

8.16. How Do You Get a View’s Columns? . . . . . . . . . . . . . . . . . . . . . 367

8.17. How Do You Get Stored Procedure Names? . . . . . . . . . . . . . . 370

■CHAPTER 9 Web Access to Metadata, Part 2 . . . . . . . . . . . . . . . . . . . . . . . . . . 381

9.1. How Do You Get the Signature of a Stored Procedure?. . . . . . 381

9.2. How Do You Get Database Catalogs? . . . . . . . . . . . . . . . . . . . . . 391

9.3. How Do You Get Database Schemas? . . . . . . . . . . . . . . . . . . . . 396

9.4. What Are the Exported Keys for a Table? . . . . . . . . . . . . . . . . . . 402

9.5. What Foreign Keys Are Used in a Table? . . . . . . . . . . . . . . . . . . 413

9.6. What Are the Available SQL Types Used by a Database? . . . . 426

9.7. What Are the Table’s Privileges? . . . . . . . . . . . . . . . . . . . . . . . . . 432

x ■CONTENTS

6374_FM_final.qxd 1/27/06 12:38 PM Page x

9.8. What Are the Column Privileges? . . . . . . . . . . . . . . . . . . . . . . . . 439

9.9. What Are a Table’s Optimal Set of Columns That

Uniquely Identify a Row or Record? . . . . . . . . . . . . . . . . . . . . . . . 446

9.10. How Do You Get a RowSet’s Metadata?. . . . . . . . . . . . . . . . . . 455

■CHAPTER 10 RDF and JDBC Metadata . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 461

10.1. What Is RDF? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 461

10.2. Where Can You Get More Information on RDF? . . . . . . . . . . . 462

10.3. What Is an Example of RDF? . . . . . . . . . . . . . . . . . . . . . . . . . . . 462

10.4. How Do You Access RDF? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 463

10.5. What Are the Applications of RDF? . . . . . . . . . . . . . . . . . . . . . . 463

10.6. Can JDBC Metadata Be Expressed As RDF?. . . . . . . . . . . . . . 464

10.7. How Do You Exploit RDF Data Models in Your

Java Applications? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 465

■INDEX . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 467

■CONTENTS xi

6374_FM_final.qxd 1/27/06 12:38 PM Page xi

6374_FM_final.qxd 1/27/06 12:38 PM Page xii

About the Author

MAHMOUD PARSIAN is a Sun-certified Java programmer and a senior lead soft￾ware engineer at Ask Jeeves (http://www.ask.com). Mahmoud earned his

PhD in computer science from Iowa State University (Ames, Iowa) and has

been working in the software industry for more than 22 years. His expertise

is in Java technology, JDBC, database design/development, and server-side

Java programming. Mahmoud’s current project is MyJeeves (http://

myjeeves.ask.com).

Mahmoud’s honors include the following (partial list):

• Ask Jeeves Bright Star Award, Ask Jeeves; November 2004

• Octopus Award, Octopus.com; July 2001

• Cisco Systems Leadership Award, Cisco Systems; June 2000

• Individual Achievement Award, Cisco Systems; July 2000

• Winner of the Circle of Excellence Award; Digital Equipment Corporation, 1991

• Winner of the Best Quality (Alex Trotman, CEO) Award; Ford Motor Company, 1990

• Five-time winner of the Specialist of the Quarter Award; Digital Equipment Corpora￾tion, 1990–94

You can contact Mahmoud at [email protected].

xiii

6374_FM_final.qxd 1/27/06 12:38 PM Page xiii

6374_FM_final.qxd 1/27/06 12:38 PM Page xiv