Java 2 database programming for dummies

Table of Contents

Introduction

About This Book

How to Use This Book

Foolish Assumptions

How This Book Is Organized

Part I: Getting Down to Database Basics

Part II: Java Database Connection

Part III: Database Interactions 101

Part IV: Picking and Choosing Data

Part V: The Part of Tens

Icons Used in This Book

Where to Go from Here

Part I: Getting Down to Database Basics

Chapter 1: Everything Is Relative in a Relational Database

Keeping Track of Things the Database Way

It's more than just a storage place

A DBMS has a language of its own

Down and Dirty Inside a Database

Tables: Placing information in one spot

Rows and columns: Setting up a simple table

Keeping the Data Orderly

Come 'ma delimited . . . Come on

The fixed is in . . . it's fixed length

Building a Good Relationship with a Relational Database

Shop 101: Making your first table

Let's join tables and sing

A Database Model That You Won't Find on the Showroom Floor

Chapter 2: Designing a Database with an Artist's Eye

The System of Understanding Systems

The labor-intensive manual system

The magnificent database system

The True Story of Entities

Hunting for entities

Thinking logically

Attributes: The Best Part of Any Entity

Looking for attributes in an instance

Letting instances help you

From Attributes to Data, The Decomposing Tale

Getting a beat on decomposing

Decomposing in action

Hitting the decomposing barrier

Describing Data to Your Friends and to the DBMS

Choosing a name

Assigning a data type

Choosing the correct data size

Putting the Pieces of Data Together

Dealing with Duplicate Data

Chapter 3: There's Something Normal About All Data

Simplifying Things with Normalization

Removing Repeated Data with the First Normal Form

Providing the missing link

Sharing the key

Updating, the easy way

Playing hide and seek: Normalization style

Identifying Dependent Data with Second Normal Forms

No independence

Functional dependency shorthand

Removing Transitive Data with the Third Normal Form

An example spells it out

Problems caused by transitive dependency

Letting the Key Open the Door to Your Data

A key by any other name is still a key

Turn the key and unlock data

Integrity: The Honest Truth about Data

Chapter 4: Finding Data Quickly with an Index

Storing Data: First One in Is First in Line

Retrieving Data with the Index Table

Indexes work like a book index

And the answer is . . .

Not in the database? -- Not necessarily true

Creating Primary Keys

A primary key can be tricky to identify

When in doubt, create a new column

The primary key is a foreigner

Making Finds Faster with Secondary Keys

Concatenating Is Like Matchmaking; It Brings Data Together

Searching with clustered indexes

Creating substrings

Selecting Indexes: I Choose You and You

Too Many Indexes Can Cause a Slowdown

Chapter 5: Client/Server Architecture for Snappy Service All the Time

The Lonely Standalone Database Application

Advantages of standalone databases

Disadvantages of standalone databases

Your Serve to Serve Up the Database

Everyone can share in the data

Who changed the data?

Whose data is it anyway?

I'm Your Server, You're My Client

Client/server architecture in action

These clients don't need a lawyer

Server up the data

More than a servant to clients

I was here first. No, I was!

Replication servers love to copy other people's work

Scalability is the key to success

Speaking the Server's Language

Requesting data from any database

Making the middleware connection

Chapter 6: The Setup: Planning a Database Application

Planning Your Database Application

Components of a database application plan

Database schema, data dictionary, and indexes

The method of developing methods

Defining the Parts of Every Database Application

Inserting data

Retrieving data

Modifying data

Deleting data

Describing Your Plan for All to See

Writing down business rules

Giving everyone a paper and an electronic version

Calling in the Professionals

Part II: Java Database Connection

Chapter 7: JDBC in English

JDBC: A Word You Can't Pronounce

The driver

The specification

JDBC is independent

Inside JDBC: An Application Programmer's Guide

Introducing the JDBC driver types

Understanding the differences between JDBC and ODBC

Using JDBC

The Three-Tier Database Application Model

Getting Started with JDBC

Chapter 8: Having the Right Connections Gets You to the Database

Using JDBC to Program

Looking at the database programming big picture

Receiving errors

Stepping through the results

Creating the Database

Making the sample table

Taking a few notes

Connecting the Database to ODBC

Define a database to the ODBC driver

Chapter 9: Writing Your First Java Database Program

The Program: Piece by Piece

Connecting to the database and sending a query

Displaying the results

Moving down the ResultSet

Disconnecting from the database

The Complete Program

Part III: Database Interactions 101

Chapter 10: Table Manners: Creating and Dropping Tables

Database Management from a Distance

Need to create new tables

Need to drop existing tables

Before Creating a Table

The rules of the table

Know your data types

Know your data size

Creating a Table from Afar

Creating your first table

Verifying that you have a new table

Making code easier to read

A mix of data types

The NOT NULL keywords

I'll fill in the blanks

Drop By and Drop a Table

Tim-ber! Down comes another table

Chapter 11: Basic Index Etiquette

Maintaining Your Index

Need to create a new index

Need to drop existing index

Organizing Before Creating an Index

Creating a Primary Index

Writing the code

Verifying that you have a primary index

Creating a Secondary Index

Writing the code

Verifying that you have a secondary index

Creating a Clustered Index

Writing the code

Converting data types

You Don't Need Slippery Hands to Drop an Index

One, two, three, drop!

Drop the index? Let's check!

Chapter 12: Stuffing Data into a Table

Before Inserting Data

Finding data to insert

Validating before inserting data

Creating a table

Don't Be Shy. Be Insertive!

Inserting your first row of data

Verifying that data was inserted

Inserting data into specific columns

Preventing errors by making your code easy to read

Let the DBMS Insert Values for You

The DBMS has a date for you

The DBMS has time for you

Hold out your wrist for a TIMESTAMP

Chapter 13: I'll Just Pick: Selecting Data from a Table

Getting Ready: Before You Select Data

Finding data

Getting to know the data type

Starting with a Simple Request

Selecting the Columns You Want to See

Selecting a single column

Selecting multiple columns

Picking the Rows You Want to See with WHERE

Selecting Rows and Columns

Getting to Know AND, OR, and NOT

Combining conditions with AND

Using OR whenever you're not sure

Using NOT when you know what you don't want

Making Sure Data Passes Your Test

Equal

Not equal

Less than

Greater than

Less than equal

Greater than equal

Between

Finding Almost the Perfect Match with LIKE

Looking for Nothing with NULL

Weeding Out Duplicates with DISTINCT

Searching for One of Many with IN

You're IN the group

You're NOT IN one of the group

Chapter 14: Show Your Hand: Displaying Data from Tables

Non-GUI Versus GUI: Which Way Should I Go?

Following the non-GUI approach

Getting all sticky and GUI

Making your choice

Non-GUI Is for Me

Getting return data

Reading other types of data

Reading Metadata

Reading the number of columns in the table

Reading the data type of a column

Reading the column name

Reading the column width

It's That GUI Kind of Thing

Data in a table

Data in a form

Chapter 15: Did You Hear the Latest?: Updating Data in Tables

Finding Data to Update

Making changes one row at a time

Making changes in multiple rows

Updating an Entire Table

Updating your first table

The Updating walk-about

Updating Multiple Rows

Using the IN test

Using IS NULL

Using comparisons

Updating all rows

Updating Multiple Columns

Specifying multiple columns

Updating with a Calculation

Calculating on the fly

Looking closer at the calculation

Chapter 16: Get Rid of the Bum: Deleting Data in Tables

Finding Data to Delete

Finding what you need to delete

Preventing problems

Giving and getting deletion rights

Deleting a Single Row of Data

Deleting your first row

The Deleting walk-about

Deleting Multiple Rows

Using the IN test

Using IS NULL

Using comparisons

Deleting all rows

Part IV: Picking and Choosing Data

Chapter 17: Picking Data from This Table and That Table

Setting Up the Database for Joins

Creating a parent-child relationship

Using the same column name

Using different column names

Creating sample tables

Knowing the Rules of the Join

Diminishing performance with too many tables

Creating a new table, not combining data

Joining Two Tables

The equal join: The simplest of them all

Parent-child join: Mammas and papas

Multiple comparison joins

Joining Three or More Tables

Name-calling: The Short and Long of It

Creating a column name qualifier

Creating a table alias

Enjoying the Ins and Outs of Joins

Creating an inner join

Creating an outer join

Chapter 18: Getting Together: Grouping and Ordering Data

Only Group-ies Need Apply

Grouping single columns

Grouping multiple columns

Knowing the group restrictions

Conditional Searches

Using the HAVING clause

Following the search restrictions

Sorting Results

Your first sort

Sort keys

Descending sort

Ascending sort

Combined ascending and descending sort

Sorting a column in the ResultSet

Chapter 19: The Art of Subqueries

Subqueries: The Silent Service

Facts about a subquery

Set up the table before company comes

Components of a subquery

Your first subquery

Subqueries and comparison operators

Subqueries and the HAVING clause

Conditional Tests

Existence test

Set membership test

The ANY qualified comparison test

The ALL qualified comparison test

Part V: The Part of Tens

Chapter 20: Ten Most Common Mistakes

Misspelling Table and Column Names

Missing Spaces in the SQL Expression

The Source File Name Is Different than the Class Name

Using Double Quotations Instead of Single Quotations

Extracting the Wrong Data Type from the ResultSet

Incorrectly Referencing Columns in the ResultSet

Designing the Database Carelessly

Forgetting to Extract Values from the ResultSet

Excluding the Semicolon in the SQL Expression

Forgetting to Concatenate the SQL Expression

Chapter 21: Ten Places to Find More Information

Go Back to School at Your Local College

Attend a Conference

Visit Sun Microsystems on the Web

Find a Newsgroup on the Internet

Review Online Documentation

Read Other Books about Java

Subscribe to Computing Magazines

Join or Create a Java Database User Group

Ask for Technical Help at Your Local College Computer Lab

Send E-mail to Authors of Articles about Java Database Programming

Chapter 22: Ten Things That Didn't Fit into This Book

Nested Subqueries

The GRANT Statement

The REVOKE Statement

Column Privileges

Passing Privileges

System Catalog

Table Alterations

Primary Key Creation

Foreign Key Creation

Trigger

Appendix