Java数据库编程全方位指南

需积分: 10 131 浏览量更新于2024-07-29 收藏 6.18MB PDF 举报

"Java Database Programming Bible 2002 是一本由John O'Donahue编写的书籍，全面介绍了Java数据库编程技术。本书包含了702页的内容，旨在帮助读者掌握JDBC（Java Database Connectivity）和SQL（Structured Query Language）的使用。书中通过清晰的解释和丰富的实际例子来教授这些技术。第一部分是数据库、SQL和JDBC的入门，包含四章内容：第1章介绍了关系型数据库的基本概念；第2章讲述了如何设计数据库；第3章讲解了SQL的基础知识；第4章引入了JDBC，它是Java与数据库交互的主要接口。第二部分专注于在两层客户端/服务器应用程序中使用JDBC和SQL，共六章。第5章介绍了如何使用JDBC和SQL创建表；第6章讲解了插入、更新和删除数据的操作；第7章涉及使用SQL查询获取数据；第8章讨论了组织搜索结果和使用索引的方法；第9章涵盖了联接和复合查询的使用；第10章则展示了如何构建一个客户端/服务器应用。第三部分是关于使用JDBC构建三层Web站点，包括五章：第11章讲述了如何建立会员制Web站点；第12章探讨了在Servlets和Java Server Pages（JSP）中使用JDBC数据源；第13章介绍了预编译语句（PreparedStatement）和可调用语句（CallableStatement）的应用；第14章讲解了管理图像和文档的BLOBs和CLOBs；第15章使用JSP、XSL和滚动结果集显示数据；第16章涉及与JDBC结合使用JavaMail API。第四部分涉及数据库、JDBC和XML的结合，共三章：第17章介绍了XML文档对象模型（DOM）与JDBC的配合；第18章利用行集（Rowsets）显示数据；第19章讲解了通过SQL访问XML文档。第五部分讲解了EJB（Enterprise JavaBeans）、数据库和持久化，包括四章：第20章介绍了EJB的基础；第21章阐述了Bean-Managed Persistence；第22章涵盖了Container-Managed Persistence；第23章讨论了Java数据对象（Java Data Objects, JDO）和透明持久化。第六部分是关于数据库管理，包括两章：第24章涉及用户管理和数据库安全；第25章介绍了性能调优的方法。书后还附有两个附录：A部分简要指导SQL语法；B部分介绍了如何安装Apache和Tomcat。这本书是学习Java数据库编程的宝贵资源，适合希望深入理解JDBC和SQL的开发者阅读。"

Chapter 1:Relational Databases

-14-

Table 1-4: Inventory Table

Item_Number Name Description Qty Cost

1002 Rice Krispies Cereal 97 1.87

1003 Shredded Wheat Cereal 103 2.05

1004 Oatmeal Cereal 15 0.98

1005 Chocolate Chip Cookies 217 1.26

1006 Fig Bar Cookies 162 1.57

1007 Sugar Cookies Cookies 276 1.03

1008 Cola Soda 144 0.61

1009 Lemon Soda 96 0.57

1010 Orange Soda 84 0.71

Notice that the Inventory Table includes an Item_Number column, which is the

primary key for this table.

When a customer places an order, two additional tables come into use. The first of

these is the Ordered Items Table, which lists the inventory items and quantities in

each order. This is shown in Table 1-5.

Table 1-5: Ordered Items Table

ID Order_Number Item_Number Qty

5000 2 1001 2

5001 2 1004 1

5002 2 1005 4

5003 2 1010 6

5004 3 1006 4

5005 3 1009 2

5006 4 1002 5

5007 4 1003 2

5008 5 1006 3

5009 5 1007 1

5010 5 1008 2

Chapter 1:Relational Databases

-15-

In addition to its primary key, the Ordered Items Table contains two foreign keys. In

this case, they are the Item_Number, from the Inventory Table, and the

Order_Number, from the Orders Table. The Orders Table is shown in Table 1-6.

Table 1-6: Orders Table

Order_Number Customer_ID Order_Date Ship_Date

2 101 12/8/01 12/10/01

3 103 12/9/01 12/11/01

4 104 12/9/01 12/11/01

6 120 12/12/01 12/14/10

5 106 12/10/01 12/12/01

7 121 12/14/01 12/16/01

The Orders Table contains all the information defining the customer's order. Its

primary key is the Order_Number column, and it contains the foreign key column

Customer_ID, pointing to the Customers table, to identify the customer placing the

order.

Notice that the way these tables have been designed eliminates redundancy. No item

of information is saved in more than one place, and each piece of information is

saved as a single row in the appropriate table.

Cross-Reference

Eliminating redundancy is an important aspect of database

design. By ensuring that information is stored in only one

place, the problems resulting from discrepancies between

different copies of the same data item are eliminated.

It is easy to understand how the keys are used if you analyze one of the orders. For

example, you can find out all about the customer who placed order 4 by looking up

customer 104 in the Customers Table. Similarly, by referring to the Ordered_Items

Table, you can see that the items ordered on order 4 were 5 of inventory item 1002

and 2 of inventory item 1003. Looking these numbers up in the Inventory Table tells

you that inventory item number 1002 refers to Rice Krispies, while inventory item

number 1003 refers to Shredded Wheat.

By combining the information in these tables, you can see that order 4 was placed by

customer 104, Vito Corleone, on 12/9/01, and that he ordered 5 boxes of Rice

Krispies and 2 boxes of Shredded Wheat, inventory numbers 1002 and 1003,

respectively, for shipment on 12/11/01. This information is obtained by matching up

the various keys, using a SQL statement such as the following:

Chapter 1:Relational Databases

-16-

SELECT c.First_Name, c.Last_Name, i.Name, oi.Qty

FROM CUSTOMERS c, ORDERS o, ORDERED_ITEMS oi,

INVENTORY i

WHERE o.Order_Number = 4 AND

c.Customer_Id = o.Customer_Id AND

i.Item_Number = oi.Item_Number AND

o.Order_Number = oi.Order_Number;

SQL commands such as the SELECT command shown above are reviewed briefly

later in this chapter and are discussed in considerable detail in subsequent chapters.

Relationships

As illustrated in the preceding discussions of primary and foreign keys, they are

defined to model the relationships among the different tables in a database. These

tables can be related in one of three ways:

§ One-to-one

§ One-to-many

§ Many-to-many

One-to-one relationships

In a one-to-one relationship, every row in the first table has a corresponding row in

the second table. This type of relationship is often created to separate different types

of data for security reasons. For example, you might want to keep confidential

information such as credit-card data separate from less restricted information.

Another common reason for creating tables with a one-to-one relationship is to

simplify implementation. For example, if you are creating a Web application involving

several forms, you might want to use a separate table for each form.

Other reasons for breaking a table into smaller parts with one-to-one relationships

between them are to improve performance or to overcome inherent restrictions such

as the maximum column count that a database system supports.

Tables related in a one-to-one relationship should always have the same primary key.

This is used to perform joins when the related tables are queried together.

One-to-many relationships

In a one-to-many relationship, every row in the first table can have zero, one, or many

corresponding rows in the second table. But for every row in the second table, there

is exactly one row in the first table. For example, there is a one-to-many relationship

between the Orders Table and the Ordered_Items Table reviewed previously.

Chapter 1:Relational Databases

-17-

One-to-many relationships are also sometimes called parent-child or master-detail

relationships because they are commonly used for lookup tables. The relationship

between the Orders Table and the Ordered_Items Table is an example of a

one-to-many relationship, where a single order corresponds to multiple ordered

items.

Many-to-many relationships

In a many-to-many relationship, every row in the first table can have many

corresponding rows in the second table, and every row in the second table can have

many corresponding rows in the first table. Many-to-many relationships can't be

directly modeled in a relational database. They must be broken into multiple

one-to-many relationships.

The Ordered_Items Table illustrates how a many-to-many relationship can be broken

into multiple one-to-many relationships. In the customer orders example illustrated by

Tables 1-4 through 1-6, orders and inventory are related in a many-to-many

relationship; multiple inventory items can correspond to a single order, and a single

inventory item can appear on multiple orders. The Ordered_Items Table is used to

implement a one-to-many mapping of inventory items to orders.

Views

Codd's View Update Rule (Rule 6) states that data can be presented to the user in

different logical combinations, called views. All views must support the same range of

data-manipulation capabilities as are available for a table.

Views are implemented in a relational database system by allowing the user to select

data from the database to create temporary tables, known as views. These views are

usually saved by name along with the selection command used to create them. They

can be accessed in exactly the same way as normal tables.

Frequently, views are used to create a table that is a subset of an existing table.

Table 1-7 is a typical example, showing rows from Table 1-2 (where Last_Name =

'Corleone', and City = 'New York').

Table 1-7: View of New York Corleones

ID FIRST_NAME MI LAST_NAME STREET CITY ST ZIP

100 Michael A Corleone 123 Pine New York NY 10006

101 Fredo X Corleone 19 Main New York NY 10007

103 Francis X Corleone 17 Main New York NY 10005

Chapter 1:Relational Databases

-18-

Normalization

Normalization is the process of organizing the data in a database by making it

conform to a set of rules known as the normal forms. The normal forms are a set of

design guidelines that are designed to eliminate redundancies and to ensure

consistent dependencies. Apart from wasting space, redundant data creates

maintenance problems. For example, if you save a customer's address in two

locations, you need to be absolutely certain to make any required changes in both

locations.

It is important to ensure that data dependencies are consistent so that you can

access and manipulate data in a logical and consistent manner. A glance at the

examples shown in Tables 1-2 and 1-4 through 1-6 reveals how related data items

are stored in the same table, separate from unrelated items.

Although normalization enhances the integrity of the data by minimizing redundancy

and inconsistency, it does so at the cost of some impact on performance.

Data-retrieval efficiency can be reduced, since applying the normalization rules can

result in data being redistributed across multiple records. This can be seen from the

examples shown in Tables 1-2 and 1-4 through 1-6, where information pertaining to a

single order is distributed across four separate tables.

A database that conforms to the normalization rules is said to be in normal form. If the

database conforms to the first rule, the database is said to be in first normal form,

abbreviated as 1NF. If it conforms to the first four rules, the database is considered to

be in fourth normal form (4NF).

First normal form

The requirements of the first normal form are as follows:

§ All records have the same number of fields.

§ All fields contain only a single data item.

§ There must be no repeated fields.

The first of these requirements, that all occurrences of a record type must contain the

same number of fields, is a built-in feature of all database systems.

The second requirement, that all fields contain only one data item, ensures that you

can retrieve data items individually. This requirement is also known as the atomicity

requirement. Requiring that each data item be stored in only one field in a record is

important to ensure data integrity.

Finally, each row in the table must be identified using a unique column or set of

columns. This unique identifier is the primary key.

剩余685页未读，继续阅读

jicheninsjtu

粉丝: 0
资源: 2

Java数据库编程全方位指南

Java Database Programming Bible

database programming

Java数据库编程宝典2

本地磁盘E的文件使用查找到的

Java毕设项目：基于spring+mybatis+maven+mysql实现的社区服务管理系统分前后台【含源码+数据库+毕业论文】

基于小程序的图书馆自习室座位预约管理微信小程序源代码（java+小程序+mysql+LW）.zip

基于知识图谱的出版物检索和推荐系统源码+文档+全部资料.zip

基于python深度学习对花卉进行目标检测-含摄像头识别-含代码和数据集.zip

分布式电源选址定容与优化配置MATLAB程序基于多目标粒子群算法 （1）该程序为基于多目标粒子群算法的分布式电源优化配置与选址定容程序，期刊lunwen源程序，配有该lunwen （2）本程序可有效

分布式电源优化配置与选址定容MATLAB程序基于遗传算法 （1）该程序为基于遗传算法的分布式电源优化配置与选址定容程序，硕士学位lunwen源程序，配有该lunwen （2）本程序可有效配置分布式电

最新资源

分布式电源选址定容与优化配置MATLAB程序基于多目标粒子群算法（1）该程序为基于多目标粒子群算法的分布式电源优化配置与选址定容程序，期刊lunwen源程序，配有该lunwen （2）本程序可有效

分布式电源优化配置与选址定容MATLAB程序基于遗传算法（1）该程序为基于遗传算法的分布式电源优化配置与选址定容程序，硕士学位lunwen源程序，配有该lunwen （2）本程序可有效配置分布式电