Java数据库编程深入JDBC

需积分: 10 42 浏览量更新于2024-07-27 收藏 1.08MB PDF 举报

"Java数据库编程通过JDBC技术" 在Java世界中，JDBC（Java Database Connectivity）是连接数据库的关键技术，它为Java程序员提供了一种标准的接口，用于与各种类型的数据库进行交互。《Java Database Programming with JDBC》这本书由Pratik Patel撰写，详细介绍了如何利用JDBC进行数据库操作。 **JDBC是什么？** JDBC是Java API的一个部分，它允许Java程序与关系型数据库进行通信。JDBC提供了一套规范，定义了Java应用程序如何调用SQL语句、执行查询以及处理结果集。通过JDBC，开发者无需关心底层数据库的具体实现，只需要关注业务逻辑和SQL语句的编写。 **JDBC结构** JDBC的核心组件包括驱动管理器、数据库驱动程序和连接。驱动管理器负责加载合适的数据库驱动，并建立到数据库的连接。数据库驱动程序则实现了JDBC接口，使得Java代码能与特定数据库进行通信。连接对象是实际与数据库进行交互的桥梁。 **ODBC在JDBC中的角色** ODBC（Open Database Connectivity）是Windows平台上的一个数据库访问标准。在Java中，JDBC可以通过JDBC-ODBC桥接器来与ODBC兼容的数据库进行通信。这使得Java应用可以利用已有的ODBC驱动来连接非Java原生支持的数据库。 **SQL基础** SQL是Structured Query Language的缩写，是用于管理和操作关系数据库的标准语言。SQL包括数据定义语言（DDL）、数据操纵语言（DML）和数据查询语言（DQL）。DDL用于创建和修改数据库结构，如创建表、声明域；DML用于插入、更新和删除数据；DQL用于查询数据。 **使用JDBC驱动** 在Java中，要使用JDBC驱动，首先需要将其安装到系统类路径中，并通过`Class.forName()`方法注册。JDBC URL用于指定数据库的地址、类型和其他连接参数。对于ODBC驱动，还需配置ODBC数据源，通过JDBC-ODBC桥接器进行连接。 **互动式SQL Applet** 书中提到的互动式SQL Applet是一个示例，演示了如何在Web环境中使用JDBC。这个Applet展示了如何创建基本的用户界面，监听用户事件，打开数据库连接，执行SQL语句，并展示结果。 JDBC为Java开发者提供了一种强大的工具，使得他们能够轻松地进行数据库操作。理解JDBC的基本概念、结构和驱动程序的使用，对于任何想要在Java中进行数据库开发的程序员来说都是至关重要的。通过学习和实践，开发者可以构建出高效、可靠的数据库应用程序。

'Author',

);

Here we have inserted the appropriate information in the correct order into the

EMPLOYEE table. To be safe, you can specify which field each of the listed tokens goes

into:

INSERT INTO EMPLOYEE (empno, lastname, firstname, function, department)

VALUES (

'00201', 'Pratik', 'Patel', 'Author', ''

);

If you don’t want to add all the fields in the row, you can specify only the fields you wish

to add:

INSERT INTO EMPLOYEE (empno, lastname, firstname, function)

VALUES (

'00201', 'Pratik', 'Patel', 'Author'

);

As you can see, I chose not to add anything in the department field. Note that if a field’s

check constraint is not met, or a table check is not met, an error will be produced. For

example, if we did not add something under the firstname field, an error would have been

returned because we defined the table’s firstname column check as NOT NULL. We did

not set up a check for the department field, so the previous command would not produce

an error.

To delete a table’s contents without removing the table completely, you can run a

command like this:

DELETE FROM EMPLOYEE;

This statement will wipe the table clean, leaving no data in any of the columns, and,

essentially, deleting all of the rows in the table. Deleting a single entry requires that you

specify some criteria for deletion:

DELETE FROM EMPLOYEE

WHERE empno='00201';

You can delete multiple rows with this type of operation, as well. If the WHERE clause

matches more than one row, all of the rows will be deleted. You can also delete multiple

entries by using the SELECT command in the WHERE clause; we will get to the

SELECT command in the next section.

If you really want to get fancy, you can use one statement to delete the same row from

more than one table:

DELETE FROM EMPLOYEE, CONFIDENTIAL

WHERE empno='00201';

The final command I want to cover in this section is UPDATE. This command allows

you to change one or more existing fields in a row. Here is a simple example of how to

change the firstname field in the EMPLOYEE table:

UPDATE EMPLOYEE

SET firstname = 'PR'

WHERE empno='00201';

We can set more than one field, if we wish, by adding more expressions, separated by

commas, like this:

UPDATE EMPLOYEE

SET firstname='PR', function='Writer'

WHERE empno='00201';

As you’ll see in the next section, the WHERE clause can take the form of a SELECT

query so that you can change multiple rows according to certain criteria.

Data Query Language

You have seen how to create your tables and add data to them, now let’s see how to

retrieve data from them. The SQL commands that you use to retrieve data from a table

are part of the Data Query Language (DQL). DQL’s primary command is SELECT, but

there are a host of predicates you can use to enhance SELECT’s flexibility and specificity.

Oftentimes, the key to understanding the process of querying is to think in terms of

mathematical sets. SQL, like all fourth-generation languages, is designed to pose the

question, “What do I want?” as opposed to other computer languages, like Java and C++,

which pose the question, “How do I do it?”

Let’s look at a set representation of our example database as shown in Figure 2.3. When

making queries, you’ll want to ask these questions:

• Where is the data located in terms of the table?

• What are the references?

• How can I use them to specify what I want?

Mastering SQL querying is not an easy task, but with the proper mind set, it is intuitive

and efficient, thanks to the relational model upon which SQL is based.

The syntax of the SELECT statement is shown here:

SELECT column_names

FROM table_names

WHERE predicates

Let’s take a look at the various functions of the SELECT command. To retrieve a

complete table, run this query:

SELECT * FROM EMPLOYEE;

To get a list of employees in the Editorial department, run this query:

SELECT * FROM EMPLOYEE

WHERE department = 'Editorial';

To sort the list based on the employees’ last names, use the ORDER BY directive:

SELECT * FROM EMPLOYEE

WHERE department= 'Editorial'

ORDER BY lastname;

To get this ordered list but only see the employee number, enter the following statements:

SELECT empno FROM EMPLOYEE

WHERE department = 'Editorial'

ORDER BY lastname;

To get a list of users with the name Pratik Patel, you would enter:

SELECT * FROM EMPLOYEE

WHERE (firstname='Pratik') AND (lastname='Patel');

What if we want to show two tables at once? No problem, as shown here:

SELECT EMPLOYEE.*, CONFIDENTIAL.*

FROM EMPLOYEE, CONFIDENTIAL;

Here’s a more challenging query: Show the salary for employees in the Editorial

department. According to our tables, the salary information is in the CONFIDENTIAL

table, and the department in which an employee belongs is in the EMPLOYEE table.

How do we associate a comparison in one table to another? Since we used the reference

of the employee number in the CONFIDENTIAL table from the EMPLOYEE table, we

can specify the employees that match a specified department, and then use the resulting

employee number to retrieve the salary information from the CONFIDENTIAL table:

SELECT c.salary

FROM EMPLOYEE as e, CONFIDENTIAL as c

WHERE e.department = 'Editorial'

AND c.empno = e.empno;

We have declared something like a variable using the as keyword. We can now reference

the specific fields in the table using a “.”, just like an object. Let’s begin by determining

which people in the entire company are making more than $25,000:

SELECT salary

FROM CONFIDENTIAL

WHERE salary > 25000;

Now let’s see who in the Editorial department is making more than $25,000:

SELECT c.salary

FROM EMPLOYEE as e, CONFIDENTIAL as c

WHERE e.department = 'Editorial'

AND c.empno = e.empno

AND c.salary > 25000;

You can perform a number of other functions in SQL, including averages. Here’s how to

get the average salary of the people in the Editorial department:

SELECT AVG (c.salary)

FROM EMPLOYEE as e, CONFIDENTIAL as c

WHERE e.department = 'Editorial'

AND c.empno = e.empno;

Of course, the possibilities with SQL exceed the relatively few examples shown in this

chapter. Because this book’s goal is to introduce the JDBC specifically, I didn’t use

complex queries in the examples. And now our discussion on SQL is complete. If you are

interested in learning more about SQL, I recommend that you check out our book’s

Website, where I have posted a list of recommended books on the topic of SQL and

distributed databases.

Coming Up Next

The next chapter begins our journey into JDBC. I’ll show you how to use JDBC drivers

for connecting to data sources. Then we’ll cover installing drivers, as well as the proper

way to use drivers that are dynamically fetched with an applet. Finally, we’ll discuss the

security restrictions of using directly downloaded drivers as opposed to locally installed

drivers.

Chapter 3

Using JDBC Drivers

As a developer who’s using the JDBC, one of the first things you need to understand is

how to use JDBC drivers and the JDBC API to connect to a data source. This chapter

outlines the steps necessary for you to begin that process. We’ll be covering the details of

getting JDBC drivers to work, as well as the driver registration process we touched on in

Chapter 1. We’ll also take some time to explore JavaSoft’s JDBC-ODBC Bridge, which

allows your Java programs to use ODBC drivers to call ODBC data sources.

Before our discussion gets underway though, I need to point out a few things about JDBC

drivers. First, there are no drivers packaged with the JDBC API; you must get them

yourself from software vendors. Check out this book’s Web site for links to demo

versions of drivers for your favorite database server, as well as free JDBC drivers

available on the Internet. Second, if you want to use ODBC, don’t forget that you’ll need

ODBC drivers, as well. If you don’t have a database server, but you want to use JDBC,

don’t despair: You can use the ODBC drivers packaged with Microsoft Access. Using the

JDBC-ODBC Bridge, you can write Java applications that can interact with an Access

database.

Unfortunately, applets enforce a security restriction that does not allow access to the local

disk, so ODBC drivers might not work in the applet context (inside a Web browser). A

future release of the Java Development Kit (JDK) may change or relax this security

restriction. A workaround for Java-enabled Web browsers is being prepared, and by the

time you read this, it may very well be possible to use the JDBC-ODBC bridge. Using

ODBC drivers in Java programs also requires pre-installation of the ODBC drivers and

JDBC-ODBC Bridge on the client machine. In contrast, JDBC drivers that are 100

percent Java class files can be downloaded dynamically over the network, along with the

calling applet’s class file. I’ll provide a more thorough discussion of this point in Chapter

Quick Start Guide

So you’re a regular Java hacker, and you’ve already figured out how to install the JDBC

API package. Now you want to jump right into it. This section will outline the four basic

steps for running your first query and getting the results. The steps are explained in

greater detail in Chapter 4. Figure 3.1 is a diagram relating the four classes that you’ll call

on in your JDBC Java program, and it is the skeleton around which you can build

database-aware Java programs. The diagram does not list all of the methods available in

the respective classes. See Chapter 12, the JDBC API reference, for the complete class

and method list.

剩余207页未读，继续阅读

snipeer123

粉丝: 0
资源: 5

Java数据库编程深入JDBC

JDBC与Java数据库编程.pdf

JDBC与Java数据库编程

Java JDBC由浅入深.pdf

JAVA数据库编程JDBC 与 Java数据库接口JDBC入门基础讲座 与 JDBC API数据库编程实作教材-程序设计-JAVA JDBC全压缩包.rar

JDBC-Java数据库编程

jdbc与java数据库编程

JDBC与Java数据库编程基础

Java JDBC：数据库编程指南

Java数据库编程技术大全：JDBC连接数据库详细教程

Java JDBCAPI数据库编程实战指南

最新资源

JAVA数据库编程JDBC 与 Java数据库接口JDBC入门基础讲座与 JDBC API数据库编程实作教材-程序设计-JAVA JDBC全压缩包.rar