6374_FM_final.qxd 1/27/06 12:38 PM Page i JDBC Metadata, MySQL, and Oracle Recipes A Problem-Solution Approach Mahmoud Parsian 6374_FM_final.qxd 1/27/06 12:38 PM Page ii 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 9 8 7 6 5 4 3 2 1 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.comin the Source Code section. 6374_FM_final.qxd 1/27/06 12:38 PM Page iii 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 iv 6374_FM_final.qxd 1/27/06 12:38 PM Page v 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 vi 6374_FM_final.qxd 1/27/06 12:38 PM Page vii 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 viii viii ■ CONTENTS 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 6374_FM_final.qxd 1/27/06 12:38 PM Page ix ■ CONTENTS ix ■ 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