Java JDBC：数据库连接与操作指南

需积分: 22 179 浏览量更新于2024-07-19 收藏 1.6MB PDF 举报

"Java Database Connectivity (JDBC)是Java编程语言中用于与关系数据库交互的一组接口和类。本资源详细介绍了如何在应用程序中利用JDBC API来操作数据库和检索数据，适合对Java数据库编程感兴趣的读者。" 在Java开发中，数据库连接是一个核心部分，特别是在企业级应用中。Java Database Connectivity (JDBC) API使得Java开发者能够方便地连接到各种类型的数据库，执行SQL语句，以及处理查询结果。JDBC提供了标准化的方法，使得开发者无需关注底层数据库的实现细节，提高了代码的可移植性。第1章《Java在企业中》讨论了Java作为企业开发工具的角色。它解释了数据库在企业软件中的重要性，并概述了如何使用Java进行数据库编程。这一章还探讨了企业环境对数据库的需求，以及Java如何适应这些需求。第2章《关系型数据库和SQL》首先定义了什么是关系型数据库，然后介绍了数据库引擎的概念，如MySQL、Oracle和SQL Server等。此外，本章还提供了一个SQL的基础教程，包括不同SQL版本之间的差异。第3章《JDBC入门》详细介绍了JDBC的基本概念。它说明了JDBC是什么，以及如何建立数据库连接。在连接数据库时可能会遇到的问题、基本的数据库访问操作、SQL数据类型与Java数据类型的对应关系、滚动结果集的使用，以及JDBC支持的辅助类都在这一章中得到讲解。同时，还展示了如何通过一个简单的数据库Servlet实现数据库操作。第4章《高级JDBC》深入探讨了JDBC的更多功能，如预编译SQL语句（PreparedStatement）的选择，批处理操作，以及可更新的结果集。本章还涵盖了处理复杂数据类型的方法，以及元数据的使用，这对于获取数据库结构信息非常有用。第5章《JDBC可选包》主要介绍了JDBC Optional Package，这个包提供了更高级的功能，如数据源和连接池。数据源允许更有效地管理数据库连接，而连接池则优化了数据库资源的使用，提高了性能和效率。通过以上内容，读者可以系统地学习到使用JDBC进行数据库操作的知识，包括建立连接、执行SQL语句、处理结果集以及优化数据库访问性能等方面。这本书不仅适合初学者，也对有经验的Java开发者具有参考价值，有助于提升他们在数据库编程方面的技能。

JDBC and Java 2

edition

age 13

transaction management. Building on this foundation, we will discuss JDBC and how it can be used

to execute SQL against any potential database engine.

1.3.1.1 SQL

The Java database API, JDBC, requires that the database being used support ANSI SQL2 as the

query language. The SQL language itself is worthy of a tiny mini-industry within the publishing

field, so covering it is well beyond the scope of this book.

[1]

The SQL in this book, however, stays

away from the more complex areas of the language and instead sticks with basic DELETE, INSERT,

SELECT, and UPDATE statements. For a short overview of SQL, check out Chapter 2.

[1]

O'Reilly is publishing a SQL reference guide, SQL in a Nutshell, by Kevin Kline with Daniel Kline.

The only additional level of complexity I use consists of stored procedures in the later chapters.

Stored procedures are precompiled SQL stored on the database server and executed by naming the

procedure and passing parameters to it. In other words, a stored procedure is much like a database

server function. Stored procedures provide an easy mechanism for separating Java programmers

from database issues and improving database performance.

1.3.1.2 JDBC

JDBC is in a SQL-level API that allows you to embed SQL statements as arguments to methods in

JDBC interfaces. To enable you to do this in a database-independent fashion, JDBC requires

database vendors (such as those mentioned earlier in this chapter) to furnish a runtime

implementation of its interfaces. These implementations route your SQL calls to the database in the

proprietary fashion it recognizes. As the programmer, though, you do not ever have to worry about

how it is routing SQL statements. The façade provided by JDBC gives you complete freedom from

any issues related to particular database issues; you can run the same code no matter what database

is present.

1.3.1.3 Transaction management

Transaction management involves packaging related database transactions into a single unit and

handling any error conditions that result. To get through this book, you need only to understand

basic transaction management in the form of beginning a transaction and either committing it on

success or aborting it on failure. JDBC provides you with the ability to auto-commit any transaction

on the atomic level (that is, statement by statement) or wait until a series of statements have

succeeded (or failed) and call the appropriate commit (or rollback) method.

1.3.2 Database Technologies

A Java application can use one of three major database architectures:

• Relational database

• Object database

• Object-relational database

The overwhelming majority of today's database applications use relational databases. The JDBC

API is thus heavily biased toward relational databases and their standard query language, SQL.

Relational databases find and provide relationships between data, so they collide head-on with

object solutions such as Java, since object-oriented philosophy dictates that an object's behavior is

inseparable from its data. In choosing the object-oriented reality of Java, you need to create a

JDBC and Java 2

edition

age 14

translation layer that maps the relational world into your object world. While JDBC provides you

with access to relational databases, it leaves the issue of object-to-relational mapping up to you.

Object databases, on the other hand, do not attempt to separate object data from behavior. The best

way to think of an object database is as a permanent store of objects with which your applications

can interface. This object-oriented encapsulation of data, however, makes it difficult to relate data

as well as relational databases do. Additionally, with JDBC so tightly bound to SQL, it is difficult

to create JDBC drivers to run against an object database. As of the writing of this book, Sun, in

cooperation with the Object Database Management Group (ODMG), is working on a specification

for a Java object database API.

Object-relational databases enjoy a "best of both worlds" advantage by providing both object and

relational means of accessing data. Until recently, object relational databases have relied almost

entirely on C++ objects to act as their object store. With all of the excitement around Java, however,

object-relational vendors are starting to enable their systems to support database objects written and

extended in Java. In this realm, your Java objects do not need to map relational data into business

objects. For the sake of easy, ad hoc querying, however, an object-relational database also provides

complex relational queries; sometimes these queries can even be done in an ANSI SQL superset

language.

1.4 Database Programming with Java

While the marriage of Java and database programming is beneficial to Java programmers, Java also

helps database programmers. Specifically, Java provides database programmers with the following

features they have traditionally lacked:

• Easy object to relational mapping

• Database independence

• Distributed computing

If you are interested in taking a pure object approach to systems development, you may have run

into the cold reality that most of the world runs on relational databases into which companies have

often placed hefty investments. This leaves you trying to map C++ and Smalltalk objects to

relational entities. Java provides an alternative to these two tools that frees you from the proprietary

interfaces associated with database programming. With the "write once, compile once, run

anywhere" power that JDBC offers you, Java's database connectivity allows you to worry about the

translation of relational data into objects instead of worrying about how you are getting that data.

A Java database application does not care what its database engine is. No matter how many times

the database engine changes, the application itself need never change. In addition, a company can

build a class library that maps its business objects to database entities in such a way that

applications do not even know whether or not their objects are being stored in a database. Later in

the book I discuss building a class library that allows you to map the data you retrieve through the

JDBC API into Java objects.

Java affects the way you distribute and maintain an application. A traditional client/server

application requires an administrator responsible for the deployment of the client program on users'

desktops. That administrator takes great pains to assure that each desktop provides a similar

operating environment so that the application may run as it was intended to run. When a change is

made to the application, the administrator makes the rounds and installs the upgrade.

JDBC and Java 2

edition

age 15

The Java language employs the idea of the zero-install client. The object code for the entire

application, client and server, resides on the network. Since the JVM provides an application with a

guaranteed runtime environment, no administration is needed for the configuration of that

environment for individual applications. The users simply use a virtual machine interface such as

HotJava to locate the desired application. By clicking on the application icon, a user can run it

without even realizing the application was never stored on their local machine.

The traditional application makes a clear distinction between the locations where processing occurs.

In traditional applications, database access occurs on the server, and GUI processing occurs on the

client; the objects on the client machine talk to the database through a specialized database API. In

other situations, the client might talk to the server through a set of TCP/IP or UDP/IP socket APIs.

Either way, a wall of complex protocols divides the objects found on the client from those on the

server. Java helps tear down this wall between client and server through another piece of its

Enterprise platform, RMI.

RMI allows applications to call methods in objects on remote machines as if those objects were

located on the same machine. Calling a method in another object in Java is of course as simple as

the syntax object.method(arg). If you want to call that method from a remote machine without

RMI, however, you would have to write code that allows you to send an object handle, a method

name, and arguments through a TCP/IP socket, translate it into an object.method(arg) call on the

remote end, perform the method call, pass the return value back across the socket, and then write a

bunch of code to handle network failures. That is a lot of work for a simple method call, and I did

not even get into the issues you would have to deal with, such as passing object references as

arguments and handling garbage collection on each end. Finally, since you have written this

complex protocol to handle method calls across the network, you have serious rewriting to do if you

decide that a given object needs to exist on the client instead of the server (or vice versa).

With RMI, any method call, whether on the same machine or across the network, uses the same

Java method call syntax. This freedom to distribute objects across the network is called a distributed

object architecture. Other languages use much more complex protocols like Common Object

Request Broker Architecture (CORBA) and the Distributed Computing Environment (DCE). RMI,

however, is a Java-specific API for enabling a distributed architecture. As such, it removes many of

the complexities of those two solutions.

For a client/server database application, a distributed architecture allows the various parts of the

application to refer to the same physical objects when referring to particular objects in the data

model. For example, take an airline ticketing system that allows customers on the Internet to book

flights. Current web applications would have a user download a bunch of flight information as an

HTML page. If I book the last seat on a flight that you are viewing at the same time, you will not

see my booking of that last seat. This is because on each client screen you simply see copies of data

from the database.

If you reconstruct this web application so that it uses RMI to retrieve data from a single flight object

on the server, you can allow any number of different customers to view the exact same plane

objects at the same time. In this way, you can be certain that all viewers see any change made to the

plane object simultaneously. When I book the last seat on that flight, the flight object makes an

RMI call to all clients viewing it to let them know another seat was booked.

1.4.1 Putting It All Together

The pieces of the story are now in place. You will be using JDBC for your database access and RMI

to distribute the objects that make up your application. This book covers the JDBC API in complete

JDBC and Java 2

edition

age 1

detail and discusses RMI as it pertains to the creation of distributed three-tier database applications.

To better use these APIs once you have gone beyond this book, I strongly recommend further

reading on these topics: object-oriented design methodologies, patterns in software development,

and general database programming.

Chapter 2. Relational Databases and SQL

Good sense is the most evenly shared thing in the world, for each of us thinks he is so well endowed

with it that even those who are the hardest to please in all other respects are not in the habit of

wanting more than they have. It is unlikely that everyone is mistaken in this. It indicates rather that

the capacity to judge correctly and to distinguish true from false, which is properly what one calls

common sense or reason, is naturally equal in all men, and consequently the diversity in our

opinions does not spring from some of us being more able to reason than others, but only from our

conducting our thoughts along different lines and not examining the same things.

— René Descartes, Discourse on the Method

Before you dive into the details of database programming in Java, I would like to take a chapter to

provide a basic discussion of relational databases for those of you who might have little or no

experience in this area. The subject of relational databases, however, is a huge topic that cannot

possibly be covered fully in this chapter. It is only designed to provide you with the most basic

introduction. Experienced database developers will find nothing new in this chapter; you will

probably want to skip ahead to Chapter 3.

2.1 What Is a Relational Database?

Programming is all about data processing; data is central to everything you do with a computer.

Databases—like filesystems—are nothing more than specialized tools for data storage. Filesystems

are good for storing and retrieving a single volume of information associated with a single virtual

location. In other words, when you want to save a WordPerfect document, a filesystem allows you

to associate it with a location in a directory tree for easy retrieval later.

Databases provide applications with a more powerful data storage and retrieval system based on

mathematical theories about data devised by Dr. E. F. Codd. Conceptually, a relational database can

be pictured as a set of spreadsheets in which rows from one spreadsheet can be related to rows from

another; in reality, however, the theory behind databases is much more complex. Each spreadsheet

in a database is called a table. As with a spreadsheet, a table is made up of rows and columns.

A simple way to illustrate the structure of a relational database is through a CD catalog. Let's say

that you have decided to create a database to keep track of your music collection. Not only do you

want to be able to store a list of your albums, but you also want to use this data later to help you

select music for parties. Your collection might look something like Table 2.1.

Table 2.1, A List of CDs from a Sample Music Collection

Artist Title Category Year

The Cure Pornography Alternative 1983

Garbage Garbage Alternative 1996

Hole Live Through This Alternative 1994

Nine Inch Nails The Downward Spiral Industrial 1994

Public Image Limited Compact Disc Alternative 1985

The Sex Pistols Never Mind the Bollocks, Here Come the Sex Pistols Punk 1977

JDBC and Java 2

edition

age 1

Skinny Puppy Last Rights Industrial 1992

Wire A Bell Is a Cup Until It Is Struck Alternative 1989

Of course, you could simply keep this list in a spreadsheet. But what if you wanted to have Johnny

Rotten night? Nothing in this list tells you which music in your catalog features him. You might

have another spreadsheet that lists musicians and the bands to which they belong, but there is

nothing about such a spreadsheet that can provide an easy programmatic answer to your question.

Databases and Database Engines

Developers new to database programming often run into problems understanding just

what a database is. In some contexts, it represents a collection of common data like the

music database you are looking at in this chapter. In other contexts, however, it may mean

the software used to support that collection of data, a process instance of that software, or

even the server machine on which the process is running.

Technically speaking, a database really is the collection of related data and the

relationships supporting the data. The database software is the software—such as Oracle,

Sybase, MySQL, and UDB—that is used to access that data. A database engine, in turn, is

a process instance of the software accessing your database. Finally, the database server is

the computer on which the database engine is running.

I will continue to use the term database interchangeably to refer to any of these

definitions. It is important, however, to database programming to understand this

breakdown.

With a database, you could easily ask the question "Can you give me all compact discs in my

collection with which Johnny Rotten was involved?" We will formally ask that question in a

minute. To make asking that question easier, however, you have to design your database to store the

information you need so that you can relate compact discs to individual musicians. You might

create another table called musicians that stores a list of musicians. For your purposes, you will

store only last names, first names, and nicknames in this list. However, you could store more

information, such as birthdays. Table 2.2

shows a part of your list.

Table 2.2, The Data in the Musicians Table

Last Name First Name Nickname

Jourgenson Al

Lydon John Johnny Rotten

Reznor Trent

Smith Robert

Nothing in these two lists relates musicians to bands, much less musicians to compact discs.

Another problem you can see in this list is that Robert Smith is a very common name, and there are

likely multiple artists who have that name. How do you know which Robert Smith should be related

to which compact disc? Database tables generally have one or more columns called keys that

uniquely identify each row. The key of the albums table could be the CD title; it is not uncommon,

however, for the same title to be used for different albums by different bands. The simplest thing to

do is to add another column to serve as the key column—let's call it an album ID. This column will

just be a sequential list of numbers. As you add new discs to the collection, increment the album ID

by one and insert that information. Thus album 1 is The Cure's Pornography, album 2 is Garbage's

剩余252页未读，继续阅读

gombaniro

粉丝: 0
资源: 10

Java JDBC：数据库连接与操作指南

java jdbc 项目实战

关于数据库连接技术ODBC_OpenDatabaseConnectivity_的

Database Connectivity Toolkit.part3

JDBC（Java Database Connectivity，Java数据库连接），它是一套用于执行SQL语句的Java API。

JAVA如何链接MySql数据库

java和Oracle的关系

java数据库对网页进行的操作

java的Databasemanager类怎么应用在主函数里

我想做个java分享，可以帮我列一下分享目录么

java.sql.SQLSyntaxErrorException: Unknown database 'test'

最新资源