Practical Database Programming with Java ffffiirrss0011..iinndddd ii 77//2200//22001111 1111::1122::5566 AAMM IEEE Press 445 Hoes Lane Piscataway, NJ 08854 IEEE Press Editorial Board Lajos Hanzo, E ditor in Chief R. Abhari M. El - Hawary O. P. Malik J. Anderson B - M. Haemmerli S. Nahavandi G. W. Arnold M. Lanzerotti T. Samad F. Canavero D. Jacobson G. Zobrist Kenneth Moore, Director of IEEE Book and Information Services (BIS) ffffiirrss0022..iinndddd iiii 77//2200//22001111 1111::1122::5577 AAMM Practical Database Programming with Java Ying Bai Department of Computer Science and Engineering Johnson C. Smith University Charlotte, North Carolina IEEE PRESS A John Wiley & Sons, Inc., Publication ffffiirrss0033..iinndddd iiiiii 77//2200//22001111 1111::1122::5577 AAMM Copyright © 2011 by the Institute of Electrical and Electronics Engineers, Inc. Published by John Wiley & Sons, Inc., Hoboken, New Jersey Published simultaneously in Canada No part of this publication may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, electronic, mechanical, photocopying, recording, scanning, or otherwise, except as permitted under Section 107 or 108 of the 1976 United States Copyright Act, without either the prior written permission of the Publisher, or authorization through payment of the appropriate per-copy fee to the Copyright Clearance Center, Inc., 222 Rosewood Drive, Danvers, MA 01923, (978) 750-8400, fax (978) 750-4470, or on the web at www.copyright.com. Requests to the Publisher for permission should be addressed to the Permissions Department, John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030, (201) 748-6011, fax (201) 748-6008, or online at http://www.wiley.com/go/permissions. Limit of Liability/Disclaimer of Warranty: While the publisher and author have used their best efforts in preparing this book, they make no representations or warranties with respect to the accuracy or completeness of the contents of this book and specifi cally disclaim any implied warranties of merchantability or fi tness for a particular purpose. No warranty may be created or extended by sales representatives or written sales materials. The advice and strategies contained herein may not be suitable for your situation. You should consult with a professional where appropriate. Neither the publisher nor author shall be liable for any loss of profi t or any other commercial damages, including but not limited to special, incidental, consequential, or other damages. For general information on our other products and services or for technical support, please contact our Customer Care Department within the United States at (800) 762-2974, outside the United States at (317) 572-3993 or fax (317) 572-4002. Wiley also publishes its books in a variety of electronic formats. Some content that appears in print may not be available in electronic formats. For more information about Wiley products, visit our web site at www. wiley.com. Library of Congress Cataloging-in-Publication Data: Bai, Ying, 1956- Practical database programming with Java / Ying Bai. p. cm. ISBN 978-0-470-88940-4 (pbk.) 1. Database management–Computer programs. 2. Database design. 3. Java (Computer program language) 4. Computer software–Development. I. Title. QA76.9.D3B314 2011 005.13'3–dc22 2011009323 obook ISBN 9781118104651 ePDF ISBN 9781118104668 ePub ISBN 9781118104699 Printed in the United States of America 10 9 8 7 6 5 4 3 2 1 ffffiirrss0044..iinndddd iivv 77//2255//22001111 77::1188::0055 PPMM This book is dedicated to my wife, Yan Wang, and to my daughter, Xue Bai. ffffiirrss0055..iinndddd vv 77//2200//22001111 1111::1122::5599 AAMM Contents Preface xxiii Acknowledgments xxv Chapter 1 Introduction 1 What This Book Covers 2 How This Book Is Organized and How to Use This Book 3 How to Use the Source Code and Sample Databases 5 Instructor and Customer Support 6 Homework Solutions 7 Chapter 2 Introduction to Databases 9 2.1 What Are Databases and Database Programs? 10 2.1.1 File Processing System 10 2.1.2 Integrated Databases 11 2.2 Develop a Database 12 2.3 Sample Database 13 2.3.1 Relational Data Model 15 2.3.2 Entity–Relationship Model 16 2.4 Identifying Keys 17 2.4.1 Primary Key and Entity Integrity 17 2.4.2 Candidate Key 17 2.4.3 Foreign Keys and Referential Integrity 17 2.5 Defi ne Relationships 18 2.5.1 Connectivity 18 2.6 ER Notation 21 2.7 Data Normalization 21 2.7.1 First Normal Form (1NF) 22 2.7.2 Second Normal Form (2NF) 23 2.7.3 Third Normal Form (3NF) 24 vii ffttoocc..iinndddd vviiii 77//2200//22001111 1111::1133::0000 AAMM viii Contents 2.8 Database Components in Some Popular Databases 26 2.8.1 Microsoft Access Databases 26 2.8.1.1 Database File 27 2.8.1.2 Tables 27 2.8.1.3 Queries 27 2.8.2 SQL Server Databases 27 2.8.2.1 Data Files 28 2.8.2.2 Tables 28 2.8.2.3 Views 29 2.8.2.4 Stored Procedures 29 2.8.2.5 Keys and Relationships 29 2.8.2.6 Indexes 30 2.8.2.7 Transaction Log Files 30 2.8.3 Oracle Databases 30 2.8.3.1 Data Files 31 2.8.3.2 Tables 31 2.8.3.3 Views 31 2.8.3.4 Stored Procedures 31 2.8.3.5 Indexes 32 2.8.3.6 Initialization Parameter Files 33 2.8.3.7 Control Files 33 2.8.3.8 Redo log Files 33 2.8.3.9 Password Files 34 2.9 Create Microsoft Access Sample Database 34 2.9.1 Create the LogIn Table 34 2.9.2 Create the Faculty Table 36 2.9.3 Create the Other Tables 37 2.9.4 Create Relationships among Tables 39 2.10 Create Microsoft SQL Server 2008 Sample Database 44 2.10.1 Create the LogIn Table 46 2.10.2 Create the Faculty Table 48 2.10.3 Create Other Tables 49 2.10.4 Create Relationships among Tables 54 2.10.4.1 Create Relationship between the LogIn and the Faculty Tables 54 2.10.4.2 Create Relationship between the LogIn and the Student Tables 57 2.10.4.3 Create Relationship between the Faculty and the Course Tables 58 2.10.4.4 Create Relationship between the Student and the StudentCourse Tables 59 2.10.4.5 Create Relationship between the Course and the StudentCourse Tables 60 2.11 Create Oracle 10g XE Sample Database 61 2.11.1 Create an Oracle User Database 63 2.11.2 Add New Data Tables into the Oracle User Database 64 2.11.2.1 Create the LogIn Table 65 2.11.2.2 Create the Faculty Table 69 2.11.2.3 Create Other Tables 74 ffttoocc..iinndddd vviiiiii 77//2200//22001111 1111::1133::0000 AAMM Contents ix 2.11.3 Create the Constraints Between Tables 77 2.11.3.1 Create the Constraints between the LogIn and Faculty Tables 77 2.11.3.2 Create the Constraints between the LogIn and Student Tables 79 2.11.3.3 Create the Constraints between the Course and Faculty Tables 80 2.11.3.4 Create the Constraints between the StudentCourse and Student Tables 82 2.11.3.5 Create the Constraints between the StudentCourse and Course Tables 82 2.12 Chapter Summary 85 Homework 85 Chapter 3 JDBC API and JDBC Drivers 89 3.1 What Are JDBC and JDBC API? 89 3.2 JDBC Components and Architecture 90 3.3 How Does JDBC Work? 92 3.3.1 Establish a Connection 92 3.3.1.1 Using DriverManager to Establish a Connection 92 3.3.1.2 Using DataSource Object to Establish a Connection 93 3.3.2 Build and Execute SQL Statements 94 3.3.3 Process Results 94 3.3.3.1 Using ResultSet Object 95 3.3.3.2 Using RowSet Object 95 3.4 JDBC Driver and Driver Types 95 3.4.1 Type I: JDBC-ODBC Bridge Driver 96 3.4.2 Type II: Native-API-Partly-Java Driver 97 3.4.3 Type III: JDBC-Net-All-Java Driver 97 3.4.4 Type IV: Native-Protocol-All-Java Driver 98 3.5 JDBC Standard Extension API 99 3.5.1 JDBC DataSource 99 3.5.1.1 Java Naming and Directory Interface 100 3.5.1.2 Deploy and Use a Basic Implementation of DataSource 100 3.5.2 JDBC Driver-Based Connection Pooling 102 3.5.3 Distributed Transactions 104 3.5.3.1 Distributed Transaction Components and Scenarios 104 3.5.3.2 The Distributed Transaction Process 105 3.5.4 JDBC RowSet 106 3.5.4.1 Introduction to Java RowSet Object 106 3.5.4.2 Implementation Process of a RowSet Object 107 3.6 Chapter Summary 108 Homework 109 Chapter 4 JDBC Application Design Considerations 113 4.1 JDBC Application Models 113 4.1.1 Two-Tier Client-Server Model 113 4.1.2 Three-Tier Client–Server Model 114 ffttoocc..iinndddd iixx 77//2200//22001111 1111::1133::0000 AAMM x Contents 4.2 JDBC Applications Fundamentals 115 4.2.1 Loading and Registering Drivers 116 4.2.2 Getting Connected 117 4.2.2.1 The DriverManager and Driver Classes 117 4.2.2.2 Using the DriverManager.getConnection() Method 119 4.2.2.3 Using the Driver.connect() Method 119 4.2.2.4 The JDBC Connection URL 120 4.2.2.5 Establish a Database Connection 120 4.2.3 Executing Statements 122 4.2.3.1 Overview of Statement Objects and Their Execution Methods 123 4.2.3.2 Using the Statement Object 126 4.2.3.3 Using the PreparedStatement Object 127 4.2.3.4 Using the CallableStatement Object 131 4.2.3.5 More about the Execution Methods 135 4.2.3.6 Creating and Executing SQL Statements 137 4.2.4 Retrieving Results 140 4.2.4.1 The ResultSet Interface 141 4.2.4.2 Getting and Processing the ResultSet Object 142 4.2.5 Using JDBC MetaData Interfaces 145 4.2.5.1 Using the ResultSetMetaData Interface 145 4.2.5.2 Using the DatabaseMetaData Interface 147 4.2.5.3 Using the ParameterMetaData Interface 149 4.2.6 Closing the Connection and Statements 149 4.3 Chapter Summary 151 Homework 152 Chapter 5 Introduction to NetBeans IDE 155 5.1 Overview of the NetBeans IDE 6.8 156 5.1.1 The NetBeans Platform 158 5.1.2 The NetBeans Open Source IDE 159 5.2 Installing and Confi guring the NetBeans IDE 6.8 161 5.3 Exploring NetBeans IDE 6.8 164 5.3.1 An Overview of NetBeans IDE 6.8 GUI 165 5.3.2 Build a New Java Project 167 5.3.2.1 Build a Java Application Project 168 5.3.2.2 Build a Java Desktop Application 178 5.3.2.3 Build a Java Class Library 183 5.3.2.4 Build a Java Project with Existing Sources 191 5.3.2.5 Build a Java Free-Form Project 192 5.3.3 Build a JavaFX Application Project 193 5.3.3.1 Overview of JavaFX 193 5.3.3.2 JavaFX SDK 194 5.3.3.3 JavaFX Script Language 195 5.3.3.4 Build a JavaFX Script Application 195 5.3.3.5 Build a JavaFX Desktop Business Application 201 5.3.3.6 Build JavaFX Mobile Business Application 214 ffttoocc..iinndddd xx 77//2200//22001111 1111::1133::0000 AAMM