Understanding SQL and Java Together
A Guide to SQLJ, JDBC, and Related Technologies
- 1st Edition - May 10, 2000
- Imprint: Morgan Kaufmann
- Authors: Jim Melton, Andrew Eisenberg
- Language: English
- Paperback ISBN:9 7 8 - 1 - 5 5 8 6 0 - 5 6 2 - 6
- eBook ISBN:9 7 8 - 0 - 0 8 - 0 5 2 0 0 2 - 5
With the growth of Java and the rise of database-powered Web applications, the need to use Java with SQL is clear. Until now, authoritative coverage of the techniques available to… Read more
Purchase options
With the growth of Java and the rise of database-powered Web applications, the need to use Java with SQL is clear. Until now, authoritative coverage of the techniques available to meet these challenges and reap their benefits-both programming and career benefits-didn't exist.
Understanding SQL and Java Together examines all the standards for combining SQL and Java. It shows you exactly how to use their features to write efficient and effective code supporting Java access to SQL data in a variety of ways. You'll gain a thorough understanding of the relationship between SQL and Java, which will allow you to write static and dynamic SQL programs in Java, merge Java code with SQL databases and SQL code, and use other data management techniques wherever appropriate.
* Covers all the technologies for using SQL and Java together, including JDBC, Java Blend, and SQLJ Parts 0, 1, and 2* Explains how to embed SQL code in Java and take advantage of Java's ability to compile that code for a specific DBMS* Explains how to store and invoke Java routines in an SQL database-and how to store Java objects in an SQL database for seamless interchange among application layers* Covers dynamic SQL access techniques using JDBC and advantageous ways to combine static and dynamic SQL* Comes with a CD-ROM containing Oracle's JDeveloper , Sybase's Adaptive Server Anywhere, Informix's Cloudscape, the complete database schema, and the complete text of most of the examples
Developers in DBMS and related technologies, people building Web and/or client-server/multi-tier applications and practitioners in the IT field
DedicationPREFACESQL and Java-and Why They MatterWhy We Wrote This BookWho Should Read ItHow This Book Is OrganizedExamplesMoving TargetsAdditional ResourcesRelevant ProductsConventionsAcknowledgementsTrademarks1. Chapter 1 - Introduction1.1 Overview of the Book1.2 Road Map1.3 What is the Problem That Needs Solving?1.3.1 Putting Java and Persistent Data Together1.3.2 Java and "Native" Interfaces1.3.3 Java Serialization1.3.4 Static SQL, Dynamic SQL, and ODBC (SQL/CLI)1.3.5 JDBC, With and Without ODBC1.3.6 SQLJ Specifications and Standards1.3.7 Java Blend1.3.8 Interfaces to (ODMG-Compliant) OODBMSs1.4 Chapter Summary2. Chapter 2 - Java for the SQL Programmer2.1 Introduction 372.2 The Java Environment 382.3 Control Flow in Java 402.4 Java Data Types 442.4.1 Primitive Data Types 442.4.2 Object Wrappers for the Primitive Data Types 442.4.3 Strings 452.4.4 The BigDecimal Class 462.4.5 Arrays 472.5 Java Classes 472.5.1 Fields, Methods, and Constructors 472.5.2 Method Invocation 502.5.3 Static Fields, Static Methods, and Static Initializers 502.5.4 The main Method 522.5.5 Garbage Collection 532.6 Object Oriented Aspects of Java 532.6.1 Inheritance 542.6.2 The Object Class 552.6.3 Polymorphism 562.6.4 Abstract Classes and Methods 572.6.5 Interfaces 572.7 Exceptions 592.8 Packages 622.9 Serialization and Externalization 632.9.1 Serialization 632.9.1.1 Customizing Serialization 652.9.2 Externalization 652.10 JAR Files 662.11 Other Features of the Java Programming Language 672.12 Chapter Summary3. Chapter 3 - SQL for the Java Programmer3.1 Introduction 693.2 SQL-What Is It and What's It Good For? 703.2.1 Applications Languages Versus Data Sublanguages 713.2.2 Procedural Versus Nonprocedural Languages 713.2.3 SQL's Background and Focus 723.2.3.1 The Origins of SQL 723.2.3.2 Dialects of SQL 733.2.3.3 Traditional Data 743.2.3.4 Complex Data 743.3 SQL Language Resources 753.4 SQL Examples 753.4.1 Data Retrieval 773.4.2 Data Creation 793.4.3 Updating Data 823.4.4 Removal of Data 833.4.5 More Complex Operations 843.5 Stored Routines 873.6 Why Use SQL Instead of an OODBMS? 893.7 SQL and Java Together 903.8 Object models: SQL and Java 913.9 Data Type Relationships 913.10 Null Values, 3-Valued Logic, and Related Issues 953.11 SQL and Programming Languages 963.11.1 Embedded SQL (and Module Language) 963.11.2 Dynamic SQL 973.11.3 Call-Level Interfaces 993.12 Result Sets 1023.13 Data and Metadata-Relationships and Differences 1053.14 Chapter Summary4. Chapter 4 - JDBC 1.0 API4.1 Introduction 1074.1.1 Types of JDBC Implementations 1084.1.2 JDBC Implementations 1104.1.3 SQL Statements 1144.1.3.1 Variations among DBMS's 1154.1.3.2 JDBC Escape Syntax 1154.2 Registering JDBC Drivers 1174.3 Connecting to a Database 1184.3.1 JDBC URL's 1184.3.2 getConnection 1184.4 Examining Database Metadata 1224.5 Executing an SQL Statement Once 1244.5.1 SELECT statements 1254.5.1.1 Result Set Metadata 1254.5.1.2 Result Set Data 1264.5.1.3 The Date, Time, and Timestamp Classes 1304.5.2 Positioned Update and Delete Statements 1314.5.3 DML and DDL Statements 1334.5.4 Unknown Statements and Call Statements 1334.5.5 SQL Exception and Warning Conditions 1354.6 Executing an SQL Statement Multiple Times 1364.7 Executing a Call Statement 1384.8 Asynchronous Execution 1404.9 JavaSoft JDBC-ODBC Bridge 1404.10 Chapter Summary5. Chapter 5 - SQLJ Part 0 (Embedding SQL in Java)5.1 Introduction 1435.2 SQLJ - An Informal Group of Companies 1445.3 Writing an SQLJ Part 0 Program 1455.3.1 A simple SQLJ Part 0 Program 1455.3.2 Connection Contexts 1475.3.2.1 Connection Context Declaration 1495.3.2.2 The Generated Connection Context Class 1505.3.2.3 Implicit Connection Contexts 1525.3.3 Execution Contexts 1525.3.4 Host Variables and Expressions 1535.3.4.1 Data Type Issues 1555.3.5 Calling Stored Routines 1565.3.6 Result Set Iterators 1585.3.6.1 Binding to Columns by Name 1585.3.6.2 Binding to Columns by Position 1615.3.6.3 Positioned Update and Delete Statements 1635.3.6.4 Multiple Result Sets 1655.3.7 Other SQLJ Part 0 Statements 1665.4 SQLJ Part 0/JDBC Interoperability 1665.5 Using SQLJ Part 0 Inside a Database 1685.6 SQLJ Part 0 Translator 1695.6.1 Binary Portability 1715.6.2 Customization of an SQLJ Part 0 Application 1715.6.3 Execution of your SQLJ Part 0 Application 1735.7 Levels of Conformance 1755.8 SQLJ Part 0 Reference Implementation 1755.9 Products Supporting SQLJ Part 0 1755.10 Advantages of SQLJ Part 0 1765.11 SQLJ Part 0 Runtime Interfaces and Classes 1765.11.1 sqlj.runtime.AsciiStream 1775.11.2 sqlj.runtime.BinaryStream 1775.11.3 sqlj.runtime.ConnectionContext 1775.11.4 sqlj.runtime.ExecutionContext 1795.11.5 sqlj.runtime.ForUpdate 1825.11.6 sqlj.runtime.NamedIterator 1825.11.7 sqlj.runtime.PositionedIterator 1825.11.8 sqlj.runtime.ResultSetIterator 1835.11.9 sqlj.runtime.StreamWrapper 1855.11.10 sqlj.runtime.UnicodeStream 1865.11.11 sqlj.runtime.SQLNullException 1865.12 SQLJ Part 0 Exceptions 1865.13 Chapter Summary6. Chapter 6 - SQLJ Part 1 (SQL Routines Using the Java(tm) Programming Language)6.1 Introduction 1896.1.1 Review of SQL/PSM 1906.1.1.1 Routine Characteristics 1926.2 Installing JAR Files in SQL 1946.3 Creating Procedures and Functions 1966.3.1 Using a Method in an SQL Function 1966.3.1.1 Mappable Data Types 1996.3.1.2 Executing SQL Statements 2006.3.2 Using a Method in an SQL Procedure 2036.3.2.1 Returning Result Sets 2056.3.3 Special Treatment for the main Method 2066.3.4 Null Values 2076.3.5 Static Variables 2086.3.6 Privilege Checking 2086.3.7 Exceptions 2096.4 SQL-Java Paths 2096.5 Privileges 2116.6 Dropping Java Routines 2116.7 Deployment Descriptors 2126.8 Operations on JAR Files 2136.8.1 Installing JAR Files 2136.8.2 Replacing JAR Files 2146.8.3 Removing JAR Files 2146.9 Optional Features in SQLJ Part 1 2156.10 Status Codes 2156.11 Products in the Marketplace 2166.11.1 Sybase Adaptive Server Anywhere 2166.12 The Value Proposition for SQLJ Part 1 2176.13 Chapter Summary7. Chapter 7 - SQL User-Defined Types7.1 Introduction 2237.2 User-Defined Types 2247.2.1 Evolution of Type Systems 2247.2.2 Introducing User-Defined Types 2267.3 SQL:1999 User-Defined Types 2267.3.1 Distinct Types 2267.3.2 Introducing Structured Types 2287.4 Structured Types 2297.4.1 Major Characteristics 2297.4.2 Attributes 2307.4.2.1 Data Types 2307.4.2.2 Accessing Attributes 2317.4.2.3 A Note About Table Names vs Correlation Names 2327.4.2.4 Attribute Characteristics 2337.4.3 Behaviors and Semantics 2367.4.3.1 Encapsulation 2377.4.3.2 Methods 2377.4.3.3 Functions 2437.4.3.4 SQL-Paths 2447.4.3.5 Procedures 2457.4.3.6 Effects of Null Parameters 2457.4.3.7 Polymorphism and Overloading 2467.4.4 Creating Instances of Structured Types 2487.4.4.1 Instances Are Values 2497.4.4.2 A Little Philosophy of Mathematics 2497.4.4.3 Constructors and the NEW Expression 2507.5 Using Structured Types 2527.5.1 Storing In The Database 2527.5.2 Updating In The Database 2537.5.3 Retrieving From The Database 2557.5.4 Deleting From The Database 2567.5.5 Copying An Instance From One Site To Another 2567.5.6 User-Defined Type Locators 2577.5.7 Transforms and Transform Groups 2587.5.8 The Type Predicate 2607.6 Type Hierarchies 2607.6.1 What is a Type Hierarchy? 2617.6.2 Subtypes and Supertypes 2627.6.3 Single Inheritance 2657.6.4 Most Specific Type 2657.6.5 Substitutability 2667.6.6 Polymorphism and Overloading (Redux) 2677.6.6.1 Method Overriding 2677.6.6.2 Casting and Comparison 2707.7 Typed Tables 2737.7.1 Relationship to Structured Types 2747.7.2 REF Types and REF Values 2757.7.3 Scope 2767.7.4 Dereferencing REF Values 2767.7.5 Invoking Methods on Rows of Typed Tables 2787.7.6 Data Modeling Decisions 2787.8 Table Hierarchies 2787.8.1 Relationship to Type Hierarchies 2787.8.2 Table Hierarchy Model 2797.8.3 Syntax Enhancements 2807.9 Implementation Issues 2817.10 SQL Object Model 2827.10.1 Why Does SQL Have an Object Model? 2827.10.2 SQL Object Model Summary 2837.11 A Java-SQL translation dictionary 2867.12 Limitations - technical and economic 2877.13 Chapter Summary8. Chapter 8 - SQLJ Part 2 (SQL Types Using the Java(tm) Programming Language)8.1 Introduction 2898.2 Associating a Class and a Structured Type 2898.2.1 Creating Instances of External Java Datatypes 2928.2.1.1 "Is it Live or is it Memorex?" 2938.2.2 Referencing the Fields of External Java Datatypes 2948.2.3 Using Java Methods in SQL 2948.2.4 Using Java Methods that Modify an Object 2968.2.5 Static Methods 2988.2.6 Static Fields 2998.2.7 Null Values 3018.2.8 Ordering 3028.2.9 Subtypes 3038.2.9.1 Collapsing Subclasses 3058.2.10 Extending the Mappable Types 3078.3 Deployment Descriptors 3098.4 Products in the Marketplace 3108.4.1 Sybase Adaptive Server Anywhere 3108.4.2 Cloudscape 3128.5 Chapter Summary9. Chapter 9 - JDBC 2.0 API9.1 Introduction 3159.2 Scrollable Result Sets 3169.2.1 Hints for the JDBC Driver 3179.3 Updatable Result Sets 3189.4 Result Set Sensitivity 3209.5 Execution of Batches 3219.6 SQL:1999 Data Types 3229.6.1 Blobs and Clobs 3239.6.2 Arrays 3269.6.3 REF Type 3279.6.4 Distinct User-defined Data Types 3289.6.5 Structured User-defined Data Types 3299.7 Java Objects in the Database 3309.8 Customizing SQL Types 3319.9 JDBC 2.0 Optional Package API 3349.10 Implementation of the JDBC 2.0 API 3349.11 Chapter Summary10. Chapter 10 - Java Blend10.1 Introduction 33710.2 Java Blend Architecture 33810.3 Mapping Between the Models 34010.3.1 Simple Tables 34010.3.2 One-to-Many Relationships 34110.3.3 Many-to-Many Relationships 34210.3.4 Subtypes 34310.4 Building a Java Blend Application 34410.4.1 Creating the Mapping, Database to Java 34510.4.1.1 Mapping the Other Way, Java to Database 35110.4.1.2 Viewed Tables 35210.4.1.3 Changing the Mapping between Tables and Classes 35310.4.2 Writing OQL Queries 35310.4.3 The preprocess Command 35410.4.4 The Database Class 35610.4.5 The Transaction Class 35810.4.6 Writing our Applications 35910.4.6.1 Creating, Modifying, and Destroying Persistent Data 35910.4.6.2 Listing All Instances of a Persistence-Capable Class 36010.4.6.3 Tallying the Votes 36310.5 Additional Java Blend Features 36510.5.1 Optimistic Concurrency Control 36510.5.1.1 Lock Groups 36610.5.2 Prefetch 36610.5.3 Object Caching 36610.6 ODMG 36710.6.1 Language-Independent Specifications 36710.6.2 The Java Binding for ODMG 36910.6.3 How Java Blend Relates to ODMG 2.0 37010.6.4 Other Products that Support ODMG 2.0 37010.7 Chapter Summary11. Chapter 11 - GUI Java Application Builders11.1 Introduction 37311.2 Why use a GUI Application Builder for Java? 37311.2.1 PowerJ, JDeveloper, and Visual J++ 37411.2.2 Other Products 39311.3 Chapter Summary12. Chapter 12 - Future Developments and Standards Processing12.1 Introduction 39512.2 Starting The New Millenium 39612.3 The More Distant Future 39712.4 Standards Processing of Java, JDBC, and SQLJ Technologies 39812.5 Less Formal Standardization 40612.6 Acceptance, Implementation, and Wide Use 40612.7 Chapter Summary 40812.8 A Final Word13. Appendix A - Relevant Standards Bodies13.1 Introduction 41113.2 Contacting ISO 41213.3 Selected National Standards Bodies14. Appendix B - Database Schema Used in Our Example14.1 Introduction 41714.2 The movies Table 41714.3 The awards Table 41814.4 The votes Table 41914.5 The movies_in_stock Table 41914.6 The movie_stars Table15. Appendix C - Movie and Vote Classes15.1 Introduction 42115.2 The Movie Class 42115.3 The Vote Class 42715.4 The VoteOutOfRange Exception Class16. Appendix D - SQL/PSM Syntax16.1 Introduction 42916.2 SQL PSM Syntax17. Appendix E - SQLJ Part 0 Syntax17.1 Introduction 43317.2 Names and Identifiers 433
- Edition: 1
- Published: May 10, 2000
- Imprint: Morgan Kaufmann
- Language: English
JM
Jim Melton
Jim Melton is editor of all parts of ISO/IEC 9075 (SQL) and is a representative for database standards at Oracle Corporation. Since 1986, he has been his company's representative to ANSI INCITS Technical Committee H2 for Database and a US representative to ISO/IEC JTC1/SC32/WG3 (Database Languages). In addition, Jim has participated in the W3C's XML Query Working Group since 1998 and is currently co-Chair of that Working Group. He is also Chair of the WG's Full-Text Task Force, co-Chair of the Update Language Task Force, and co-editor of two XQuery-related specifications. He is the author of several SQL books.
Affiliations and expertise
Oracle Corporation, Sandy, Utah, USAAE
Andrew Eisenberg
Andrew Eisenberg is Manager of Standards and Consortia at Progress Software Corporation. He represents his company on the ANSI NCITS Technical Committee H2 for Database, the W3C XML Query Work Group, and the Transaction Processing Performance Council. He has represented prior companies on the OMG and the SQLJ informal group.