JDBC recipes

Mahmoud Parsian

JDBC Recipes

A Problem-Solution Approach

5203ch00_FM.qxd 8/12/05 12:17 PM Page i

JDBC Recipes: A Problem-Solution Approach

Copyright © 2005 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: 1-59059-520-3

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

Development Editor: Jim Sumser

Technical Reviewer: Sumit Pal

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

Jonathan Hassell, Chris Mills, Dominic Shakeshaft, Jim Sumser

Associate Publisher: Grace Wong

Project Manager: Beckie Stones

Copy Edit Manager: Nicole LeClerc

Copy Editor: Kim Wimpsett

Assistant Production Director: Kari Brooks-Copony

Production Editor: Katie Stence

Compositor and Artist: Kinetic Publishing Services, LLC

Proofreaders: Liz Welch and Lori Bring

Indexer: Tim Tate

Interior Designer: Van Winkle Design Group

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.

5203ch00_FM.qxd 8/12/05 12:17 PM Page ii

This book is dedicated to

my love, Behnaz;

my gozal daughter, Maral;

my gibldiz son, Yaseen;

my mother, Monireh, and the memory of my father, Bagher.

5203ch00_FM.qxd 8/12/05 12:17 PM Page iii

5203ch00_FM.qxd 8/12/05 12:17 PM Page iv

Contents at a Glance

About the Author . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xix

About the Technical Reviewer. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxi

Acknowledgments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxiii

Introduction. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxv

■CHAPTER 1 Introducing JDBC . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1

■CHAPTER 2 Exploring JDBC’s Novel Features. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45

■CHAPTER 3 Making Database Connections. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 87

■CHAPTER 4 Making Database Connections Using DataSource . . . . . . . . . . . . . . . . . . 153

■CHAPTER 5 Exploring the ResultSet Interface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 169

■CHAPTER 6 Working with Scrollable and Updatable ResultSet Objects . . . . . . . . . . 209

■CHAPTER 7 Reading and Writing BLOBs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 231

■CHAPTER 8 Reading and Writing CLOBs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 257

■CHAPTER 9 Working with Date, Time, and Timestamp in JDBC. . . . . . . . . . . . . . . . . . 307

■CHAPTER 10 Handling Exceptions in JDBC . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 341

■CHAPTER 11 Exploring the Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 381

■CHAPTER 12 Working with the PreparedStatement. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 431

■CHAPTER 13 Passing Input Parameters to PreparedStatement . . . . . . . . . . . . . . . . . . 493

■CHAPTER 14 Exploring JDBC Utilities . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 569

■INDEX . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 615

v

5203ch00_FM.qxd 8/12/05 12:17 PM Page v

5203ch00_FM.qxd 8/12/05 12:17 PM Page vi

Contents

About the Author . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xix

About the Technical Reviewer. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxi

Acknowledgments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxiii

Introduction. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxv

■CHAPTER 1 Introducing JDBC . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1

1-1. What Is JDBC? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1

1-2. What Is JDBC’s High-Level Architecture?. . . . . . . . . . . . . . . . . . . . . . . . . . . . 3

1-3. What Is JDBC’s Detailed Architecture?. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3

1-4. What Is a Relational Database? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6

1-5. What Is ODBC? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6

1-6. What Is a JDBC-ODBC Bridge?. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8

1-7. What Is SQL? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13

1-8. What Is JDBC Programming? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15

1-9. What Is the JDBC API (in a Nutshell)?. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26

1-10. What Are the Core JDBC Classes/Interfaces? . . . . . . . . . . . . . . . . . . . . . . 26

1-11. What Is a JDBC Driver? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27

1-12. How Do You Load a JDBC Driver? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29

1-13. How Do You Test a JDBC Driver Installation? . . . . . . . . . . . . . . . . . . . . . . . 32

1-14. Where Can You Obtain a JDBC Driver for Your Database

System? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34

1-15. What Is a JDBC Driver Type? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34

1-16. What Types of JDBC Drivers Exist? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35

1-17. What Are the Selection Criteria for JDBC Drivers?. . . . . . . . . . . . . . . . . . . 37

1-18. What Is the URL Syntax for Connecting to a Database? . . . . . . . . . . . . . . 38

1-19. What Is the Mapping Between Java to JDBC SQL Types?. . . . . . . . . . . . . 39

1-20. How Do You Handle JDBC Errors/Exceptions? . . . . . . . . . . . . . . . . . . . . . . 42

■CHAPTER 2 Exploring JDBC’s Novel Features. . . . . . . . . . . . . . . . . . . . . . . . . . . . 45

2-1. What Is Database Metadata? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45

2-2. What Is a Transaction? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46

2-3. How Do You Turn On Autocommit Mode?. . . . . . . . . . . . . . . . . . . . . . . . . . . 48

2-4. How Do You Turn Off Autocommit Mode?. . . . . . . . . . . . . . . . . . . . . . . . . . . 48

2-5. How Do You Roll Back a Transaction? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48

vii

5203ch00_FM.qxd 8/12/05 12:17 PM Page vii

2-6. How Do You Start and End a Transaction in JDBC? . . . . . . . . . . . . . . . . . . . 48

2-7. What Is Connection Pool Management?. . . . . . . . . . . . . . . . . . . . . . . . . . . . 49

2-8. How Do You Improve the Performance of JDBC Applications? . . . . . . . . . . 51

2-9. What Are Oracle’s JDBC Drivers? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63

2-10. How Do You Connect with Oracle’s JDBC Thin Driver? . . . . . . . . . . . . . . . 64

2-11. How Do You Connect with Oracle’s JDBC OCI Driver? . . . . . . . . . . . . . . . . 65

2-12. How Do You Connect with Oracle’s KPRB Driver? . . . . . . . . . . . . . . . . . . . 65

2-13. What Are MySQL’s JDBC Drivers? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65

2-14. How Do You Register the MySQL Driver with the Driver

Manager? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 66

2-15. How Do You Get a MySQL Connection from the Driver

Manager? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 66

2-16. What Are the Key JDBC Concepts for an Oracle Database? . . . . . . . . . . . 69

2-17. What Are the Key JDBC Concepts for a MySQL Database? . . . . . . . . . . . . 72

2-18. How Do You Set Your Environment for JDBC? . . . . . . . . . . . . . . . . . . . . . . 73

2-19. What Are Some JDBC Resources? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74

2-20. How Do You Debug Problems Related to the JDBC API? . . . . . . . . . . . . . . 75

2-21. Does MySQL Support Transactions? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 76

2-22. Does Oracle Support Transactions? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 77

2-23. What Do the Different Versions of JDBC Offer? . . . . . . . . . . . . . . . . . . . . . 78

2-24. What Is the Core Functionality of a JDBC Driver? . . . . . . . . . . . . . . . . . . . 79

2-25. Where Can You Find Information and Pointers for Writing

a JDBC Driver? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 82

2-26. What Is the JDBC Driver Certification Program? . . . . . . . . . . . . . . . . . . . . 82

2-27. What Is a Two-Tier Model for JDBC? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 83

2-28. What Is a Three-Tier Model for JDBC?. . . . . . . . . . . . . . . . . . . . . . . . . . . . 84

■CHAPTER 3 Making Database Connections . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 87

3-1. What Is a Connection Object?. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 88

3-2. What Is the Relationship of Connection to Other Objects?. . . . . . . . . . . . . . 88

3-3. What Are the Connection Creation Options? . . . . . . . . . . . . . . . . . . . . . . . . 89

3-4. What Is the Function of the DriverManager Class? . . . . . . . . . . . . . . . . . . . 90

3-5. How Do You Create Connection(s) Using the DriverManager

Class? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 91

3-6. How Do You Get a List of Loaded Drivers?. . . . . . . . . . . . . . . . . . . . . . . . . . 95

3-7. How Do You Connect to an Oracle Database? . . . . . . . . . . . . . . . . . . . . . . . 97

3-8. How Do You Connect to a MySQL Database?. . . . . . . . . . . . . . . . . . . . . . . 100

3-9. How Do You Get a List of All Available Parameters for Creating

a JDBC Connection? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 103

3-10. How Do You Create Connection(s) Using the Driver Interface? . . . . . . . . 106

3-11. What Is Basic Connection Management?. . . . . . . . . . . . . . . . . . . . . . . . . 108

3-12. How Do You Determine If a Database Supports Transactions? . . . . . . . . 113

viii ■CONTENTS

5203ch00_FM.qxd 8/12/05 12:17 PM Page viii

3-13. How Do You Limit the Number of Rows Returned from a

SQL Query? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 115

3-14. How Do You Get the Driver of a Connection? . . . . . . . . . . . . . . . . . . . . . . 117

3-15. How Do You Commit and Roll Back Updates to a Database? . . . . . . . . . 118

3-16. How Do You Determine If a SQL Warning Occurs?. . . . . . . . . . . . . . . . . . 119

3-17. What Are the MySQL Connection Properties? . . . . . . . . . . . . . . . . . . . . . 121

3-18. What Are the Oracle Connection Properties? . . . . . . . . . . . . . . . . . . . . . . 123

3-19. Can a JDBC Application Connect to More Than One

Database? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 124

3-20. How Do You Test to See If Your Connection Is Alive? . . . . . . . . . . . . . . . . 125

3-21. How Do You Keep the Connection Alive in a Production

Environment? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 130

3-22. How Do You Disconnect from a Database? . . . . . . . . . . . . . . . . . . . . . . . 131

3-23. What Are the Rules for Connection’s Autocommit? . . . . . . . . . . . . . . . . . 132

3-24. How Do You Create a New Type Map? . . . . . . . . . . . . . . . . . . . . . . . . . . . 134

3-25. How Do You Create a SQL to Java Type Map Entry? . . . . . . . . . . . . . . . . 135

3-26. Is There Any Limit on the Number of Connections for JDBC? . . . . . . . . . 145

3-27. How Do You Connect As SYSDBA or SYSOPER to an Oracle

Database? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 147

3-28. How Do You Check MySQL’s/Oracle’s JDBC Installation? . . . . . . . . . . . . 148

■CHAPTER 4 Making Database Connections Using DataSource . . . . . . . . . 153

4-1. How Do You Create Connection Using a DataSource Object? . . . . . . . . . . 153

4-2. How Do You Create a DataSource Object?. . . . . . . . . . . . . . . . . . . . . . . . . 156

4-3. How Do You Create a DataSource Object Using Oracle? . . . . . . . . . . . . . . 156

4-4. How Do You Create a DataSource Object Using MySQL? . . . . . . . . . . . . . 156

4-5. How Do You Create a DataSource Object Using a Relational

Database (Oracle/MySQL)? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 157

4-6. How Do You Create a DataSource Object Using a DataSource

Factory?. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 160

4-7. What Are the DataSource Properties? . . . . . . . . . . . . . . . . . . . . . . . . . . . . 160

4-8. How Do You Deploy/Register a DataSource? . . . . . . . . . . . . . . . . . . . . . . . 161

4-9. How Do You Use a File-Based System for Registering a

DataSource Object? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 162

4-10. What Is the Problem with File-Based DataSource Objects? . . . . . . . . . . 163

4-11. How Do You Retrieve a Deployed/Registered DataSource?. . . . . . . . . . . 164

4-12. How Do You Obtain a Connection with the DataSource

Without Using JNDI? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 165

4-13. How Do You Obtain a Connection with the DataSource

Using JNDI?. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 166

■CONTENTS ix

5203ch00_FM.qxd 8/12/05 12:17 PM Page ix

■CHAPTER 5 Exploring the ResultSet Interface . . . . . . . . . . . . . . . . . . . . . . . . . . 169

5-1. What Is a ResultSet?. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 169

5-2. What Is the Relationship of ResultSet to Other

Classes/Interfaces?. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 174

5-3. How Does the JDK Define a ResultSet? . . . . . . . . . . . . . . . . . . . . . . . . . . . 175

5-4. What Kinds of ResultSet Objects Exist? . . . . . . . . . . . . . . . . . . . . . . . . . . . 175

5-5. How Do You Set a ResultSet Type?. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 176

5-6. How Do You Get a ResultSet Type? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 178

5-7. Which ResultSet Types Are Supported by Databases?. . . . . . . . . . . . . . . . 178

5-8. What Is a ResultSet Concurrency? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 180

5-9. How Do You Set ResultSet Concurrency?. . . . . . . . . . . . . . . . . . . . . . . . . . 181

5-10. How Do You Get ResultSet Concurrency? . . . . . . . . . . . . . . . . . . . . . . . . 181

5-11. What Is ResultSet Holdability? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 181

5-12. How Do You Set ResultSet Holdability? . . . . . . . . . . . . . . . . . . . . . . . . . . 182

5-13. How Do You Set ResultSet Holdability Using the

Connection Object?. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 182

5-14. How Do You Check ResultSet Holdability? . . . . . . . . . . . . . . . . . . . . . . . . 182

5-15. How Do You Get ResultSet Holdability? . . . . . . . . . . . . . . . . . . . . . . . . . . 183

5-16. How Do You Create and Manipulate ResultSet Objects? . . . . . . . . . . . . . 183

5-17. How Do You Get Rows from a Database Table?. . . . . . . . . . . . . . . . . . . . 183

5-18. How Do You Get Data from a ResultSet? . . . . . . . . . . . . . . . . . . . . . . . . . 185

5-19. How Do You Get Rows from a Database Table?. . . . . . . . . . . . . . . . . . . . 186

5-20. How Do You Get Data from a ResultSet? . . . . . . . . . . . . . . . . . . . . . . . . . 187

5-21. How Do You Determine If a Fetched Value Is NULL? . . . . . . . . . . . . . . . . 191

5-22. How Do You Get the Column Names in a Result Set? . . . . . . . . . . . . . . . 191

5-23. How Do You Get the Number of Rows in a Database Table? . . . . . . . . . . 193

5-24. How Do You Get BLOB Data from a Database Table? . . . . . . . . . . . . . . . 194

5-25. How Do You Get CLOB Data from a Database Table? . . . . . . . . . . . . . . . 199

5-26. How Do You Match Using Wildcards in a SQL Statement? . . . . . . . . . . . 200

5-27. How Do You Read/Extract Data from a Microsoft Excel

Spreadsheet File? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 200

5-28. How Do You Write Data to a Microsoft Excel Spreadsheet

File?. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 203

5-29. Which Is the Preferred Collection Class to Use for Storing

Database Result Sets? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 205

5-30. How Do You Retrieve a Whole Row/Record of Data at Once

Instead of Calling an Individual ResultSet.getXXX() Method for

Each Column? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 208

x ■CONTENTS

5203ch00_FM.qxd 8/12/05 12:17 PM Page x

■CHAPTER 6 Working with Scrollable and Updatable

ResultSet Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 209

6-1. What Is a Scrollable ResultSet? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 209

6-2. How Do You Determine If a Database Supports Scrollable

ResultSets? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 212

6-3. How Do You Create a Scrollable ResultSet?. . . . . . . . . . . . . . . . . . . . . . . . 212

6-4. How Do You Determine If a ResultSet Is Scrollable? . . . . . . . . . . . . . . . . . 214

6-5. How Do You Move the Cursor in a Scrollable ResultSet?. . . . . . . . . . . . . . 214

6-6. How Do You Get the Cursor Position in a Scrollable Result Set? . . . . . . . . 216

6-7. How Do You Get the Number of Rows in a Table Using a

Scrollable ResultSet? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 217

6-8. How Do You Determine If a Database Supports Updatable

ResultSets? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 218

6-9. How Do You Create an Updatable ResultSet? . . . . . . . . . . . . . . . . . . . . . . 219

6-10. How Do You Determine If a ResultSet Is Updatable? . . . . . . . . . . . . . . . . 219

6-11. How Do You Update a Row in a Database Table Using an

Updatable Result Set?. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 220

6-12. How Do You Cancel Updates to an Updatable ResultSet? . . . . . . . . . . . . 221

6-13. How Do You Insert a Row into a Database Table Using an

Updatable ResultSet? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 222

6-14. How Do You Delete a Row from a Database Table Using an

Updatable ResultSet? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 226

6-15. How Do You Refresh a Row in an Updatable ResultSet? . . . . . . . . . . . . . 229

■CHAPTER 7 Reading and Writing BLOBs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 231

7-1. What Is a BLOB? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 231

7-2. How Do You Define a BLOB Data Type in a Table?. . . . . . . . . . . . . . . . . . . 233

7-3. What Are the Restrictions for Using BLOBs? . . . . . . . . . . . . . . . . . . . . . . . 234

7-4. How Do You Create a java.sql.Blob Object? . . . . . . . . . . . . . . . . . . . . . . . . 234

7-5. How Do You Materialize BLOB Data? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 235

7-6. How Do You Insert a New Record with a BLOB? . . . . . . . . . . . . . . . . . . . . 236

7-7. How Do You Select and Display a BLOB in a JFrame?. . . . . . . . . . . . . . . . 240

7-8. How Do You Delete an Existing BLOB from the Oracle

Database? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 243

7-9. How Do You Delete an Existing BLOB from the MySQL

Database? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 245

7-10. How Do You Serialize a Java Object to the Oracle Database? . . . . . . . . . 246

7-11. How Do You Serialize a Java Object to the MySQL Database? . . . . . . . . 252

7-12. Should You Use byte[] or java.sql.Blob? Which Has the Best

Performance? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 256

■CONTENTS xi

5203ch00_FM.qxd 8/12/05 12:17 PM Page xi

■CHAPTER 8 Reading and Writing CLOBs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 257

8-1. What Is a CLOB? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 257

8-2. How Do You Define a CLOB Data Type in a Table?. . . . . . . . . . . . . . . . . . . 259

8-3. What Are the Restrictions for Using CLOBs? . . . . . . . . . . . . . . . . . . . . . . . 261

8-4. How Do You Create a java.sql.Clob Object? . . . . . . . . . . . . . . . . . . . . . . . . 261

8-5. How Do You Materialize CLOB Data? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 262

8-6. How Do You Insert a New Record with a CLOB? . . . . . . . . . . . . . . . . . . . . 265

8-7. How Do You Select and Display a CLOB in a JFrame?. . . . . . . . . . . . . . . . 270

8-8. How Do You Select and Display an Oracle CLOB Using

a Servlet? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 274

8-9. How Do You Select and Display a MySQL CLOB Using

a Servlet? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 277

8-10. How Do You Select and Display an Oracle CLOB (As a URL)

Using a Servlet? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 280

8-11. How Do You Select and Display a MySQL CLOB (As a URL)

Using a Servlet? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 284

8-12. How Do You Insert a CLOB into an Oracle Database Using

a Servlet? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 285

8-13. How Do You Insert a CLOB into a MySQL Database Using

a Servlet? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 290

8-14. How Do You Update an Existing CLOB of an Oracle Database

Using a Servlet? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 293

8-15. How Do You Update an Existing CLOB of a MySQL Database

Using a Servlet? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 297

8-16. How Do You Delete an Existing CLOB of an OracleDatabase

Using a Servlet? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 300

8-17. How Do You Delete an Existing CLOB of an MySQL Database

Using a Servlet? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 303

8-18. Should You Use java.lang.String or java.sql.Clob?

Which Has the Best Performance? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 305

■CHAPTER 9 Working with Date, Time, and Timestamp in JDBC . . . . . . . . 307

9-1. What Is the Mapping of Date-Related SQL and Java Types?. . . . . . . . . . . 308

9-2. How Do You Retrieve Date, Time, and Timestamp from

a Database? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 309

9-3. How Does MySQL Handle Date, Time, and Timestamp? . . . . . . . . . . . . . . 310

9-4. How Does Oracle Handle Date, Time, and Timestamp?. . . . . . . . . . . . . . . 310

9-5. How Do You Get the Current Date As a java.util.Date Object? . . . . . . . . . . 314

9-6. How Do You Create a java.sql.Date Object?. . . . . . . . . . . . . . . . . . . . . . . . 314

9-7. How Do You Get the Current Timestamp As a java.sql.Timestamp

Object? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 316

xii ■CONTENTS

5203ch00_FM.qxd 8/12/05 12:17 PM Page xii

9-8. How Do You Get the Current Timestamp As a java.sql.Time

Object? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 316

9-9. How Do You Convert from a java.util.Date Object to

a java.sql.Date Object? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 316

9-10. How Do You Convert a String Date Such As 2003/01/10 into

a java.util.Date Object?. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 317

9-11. How Do You Create Yesterday’s Date from a Date in the

String Format of MM/DD/YYYY? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 317

9-12. How Do You Create a java.util.Date Object from a Year, Month,

Day Format? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 318

9-13. How Do You Convert a String Date Such As 2003/01/10 to

a java.sql.Date Object? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 319

9-14. How Do You Get a Timestamp Object?. . . . . . . . . . . . . . . . . . . . . . . . . . . 320

9-15. How Do You Create a java.sql.Time Object? . . . . . . . . . . . . . . . . . . . . . . 320

9-16. How Do You Convert the Current Time to a java.sql.Date

Object? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 322

9-17. How Do You Determine the Day of the Week from Today’s

Date?. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 322

9-18. How Do You Determine the Day of the Week from a Given

java.util.Date Object? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 322

9-19. How Do You Convert java.sql.Date to java.util.Date?. . . . . . . . . . . . . . . . 322

9-20. What Is java.text.SimpleDateFormat? . . . . . . . . . . . . . . . . . . . . . . . . . . . 323

9-21. How Do You Convert java.util.Date to a Date String in the

Format MM/DD/YYYY? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 323

9-22. How Do You Create a Time String in the Format HH:MM:SS

from an Hour, Minute, Second Format? . . . . . . . . . . . . . . . . . . . . . . . . . . . . 324

9-23. How Do You Convert a java.util.Date Object to a Time String

in the Format HH:MM:SS? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 325

9-24. How Do You Check for a Leap Year? . . . . . . . . . . . . . . . . . . . . . . . . . . . . 325

9-25. How Do You Convert Between Different Java Date Classes? . . . . . . . . . 326

9-26. How Do You Add/Subtract Days for a Given Date

(Given As a String)?. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 327

9-27. How Do You Find the Difference Between Two Given Dates? . . . . . . . . . 328

9-28. How Do You Convert a Timestamp to Month-Day-Year? . . . . . . . . . . . . . 331

9-29. How Do You Determine the Validity of a Format Pattern for

SimpleDateFormat? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 332

9-30. How Do You Get a Date Label from a java.sql.Timestamp

Object? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 333

9-31. How Do You Convert a java.sql.Timestamp Object to

a java.util.Date Object?. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 335

9-32. What Does Normalization Mean for java.sql.Date and

java.sql.Time? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 335

■CONTENTS xiii

5203ch00_FM.qxd 8/12/05 12:17 PM Page xiii

9-33. Does MySQL/Oracle JDBC Driver Normalize java.sql.Date and java.sql.Time

Objects? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 336

9-34. How Do You Make a java.sql.Timestamp Object for a Given

Year, Month, Day, Hour, and So On? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 339

9-35. How Do You Get a Date for a Specific Time Zone? . . . . . . . . . . . . . . . . . 340

■CHAPTER 10 Handling Exceptions in JDBC. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 341

10-1. What Is an Exception? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 342

10-2. What Is java.lang.Exception? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 342

10-3. What Are the Components of an Exception? . . . . . . . . . . . . . . . . . . . . . . 344

10-4. What Is the Definition of SQLException? . . . . . . . . . . . . . . . . . . . . . . . . . 344

10-5. Is SQLException a “Checked” Exception? . . . . . . . . . . . . . . . . . . . . . . . . 346

10-6. What Is the Relationship of SQLException to Other Classes? . . . . . . . . . 346

10-7. What Is an Example of Using SQLException?. . . . . . . . . . . . . . . . . . . . . . 348

10-8. How Do You Get the Details of a SQLException? . . . . . . . . . . . . . . . . . . . 349

10-9. What Is SQLException Chaining? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 350

10-10. How Do You Get All SQLException Instances? . . . . . . . . . . . . . . . . . . . . 351

10-11. What Is a SQLWarning? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 352

10-12. How Do You Get All SQLWarning Instances? . . . . . . . . . . . . . . . . . . . . . 353

10-13. How Do You Determine Whether a SQL Warning Has

Occurred? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 353

10-14. How Do You Create and Traverse a Custom SQLWarning

Structure? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 355

10-15. How Do You Wrap Database Exceptions? . . . . . . . . . . . . . . . . . . . . . . . 357

10-16. What Are the SQLState Codes Returned by

SQLException.getSQLState()?. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 358

10-17. What Is a BatchUpdateException? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 364

10-18. What Is a DataTruncation Class? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 373

10-19. How Do You Use DataTruncation? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 375

10-20. How Do You Use DataTruncation for ResultSet? . . . . . . . . . . . . . . . . . . 379

■CHAPTER 11 Exploring the Statement. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 381

11-1. How Do You Represent a SQL Statement Object? . . . . . . . . . . . . . . . . . . 381

11-2. How Do You Create Statement Objects? . . . . . . . . . . . . . . . . . . . . . . . . . 382

11-3. How Do You Create a Scrollable ResultSet?. . . . . . . . . . . . . . . . . . . . . . . 383

11-4. How Do You Create an Updatable ResultSet? . . . . . . . . . . . . . . . . . . . . . 385

11-5. How Do You Execute SQL Statements Using Statement

Objects? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 386

11-6. How Do You Create a Database Table Using a Statement? . . . . . . . . . . . 387

11-7. How Do You Drop a Database Table Using a Statement? . . . . . . . . . . . . 388

11-8. How Do You Retrieve Automatically Generated Keys Using

a Statement (MySQL)? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 388

xiv ■CONTENTS

5203ch00_FM.qxd 8/12/05 12:17 PM Page xiv