JDBC tutorial from SUN(3)

Lesson: JDBC Basics

In this lesson you will learn the basics of the JDBC™ API.

* We start by giving you set up instructions in Getting Started. This lesson sets up a basic development environment, including Java™ SE 6 and NetBeans™.

* Setting Up a Database installs a testing database accessable through the NetBeans IDE, and installs its database driver.

* Establishing a Connection connects you to your database.

* Setting up Tables, Retrieving Values from Result Sets and Updating Tables all develop the process of configuring your database, sending queries and retrieving data from your database.
[color=darkred]第一步：准备好运行环境。执行CRU等操作。熟悉statement, resultset等[/color]

The next lesson discusses how to use joins, row sets, transactions and stored procedures.
[color=darkred]第二步：joins?rowsets?事物和存储过程[/color]

The final lesson describes how to create and run a complete JDBC application and provides some code examples for study.
[color=darkred]第三步：完整的JDBC应用[/color]

Getting Started

This lesson helps you to set up a JDBC™ development environment. This includes the following steps:

1. Install the latest version of the Java™ platform on your machine. If you don't already have a database, it is recommended that you download the latest version of the NetBeans™ IDE and the Sun Application Server, which comes with Java DB.
[color=darkred]不忘强烈推荐自家的NetBeans和 包含Java DB的Sun Application Server[/color]

To install the Java platform, follow the instructions after downloading Java platform software and NetBeans. These downloads provide you with the JDBC package. JDBC includes the java.sql and javax.sql packages, which has the necessary interfaces and classes you'll need for developing JDBC applications.

You can find the latest release at the following URL:

http://java.sun.com/products/JDK/CurrentRelease

2. Install a driver on your machine.

You also need to install a driver on your machine. If you installed NetBeans with the Sun Application Server, then you have the driver that you need.

Note: A JDBC driver can come from many sources: database software, such as Java DB, a JDBC driver vendor such as DataDirect, Oracle, MySQL, or an ISV/OEM such as Sun. Your driver should include instructions for installing it. For a JDBC driver written for specific Database Management Systems (DBMS), installation consists of copying the driver onto your machine. No special configuration is needed.

3. Install your Database Management System (DBMS) if needed.

If you do not already have a Database Management System (DBMS) installed, follow the vendor's instructions for installation. You can download the Java DB database, which comes bundled with the Sun Application Server:
Free Trial Download, and the NetBeans IDE. as well.

Types of Drivers

[color=darkred]四种驱动程序——每个介绍jdbc的文章都要说到这个话题。但似乎没有什么真正的应用意义。[/color]
There are many possible implementations of JDBC drivers. These implementations are categorized as follows:
o Type 1 - drivers that implement the JDBC API as a mapping to another data access API, such as ODBC. Drivers of this type are generally dependent on a native library, which limits their portability. The JDBC-ODBC Bridge driver is an example of a Type 1 driver.

o Type 2 - drivers that are written partly in the Java programming language and partly in native code. These drivers use a native client library specific to the data source to which they connect. Again, because of the native code, their portability is limited.

o Type 3 - drivers that use a pure Java client and communicate with a middleware server using a database-independent protocol. The middleware server then communicates the client?s requests to the data source.

o Type 4 - drivers that are pure Java and implement the network protocol for a specific data source. The client connects directly to the data source.

Check which driver type comes with your DBMS on the JDBC Data Access API Drivers page. Java DB ships with two Type 4 drivers, an Embedded driver and a Network Client Driver.

[color=darkred]jdbc目前的driverlist 以及driver所支持的jdbc类型。
[url]http://developers.sun.com/product/jdbc/drivers[/url][/color]

Setting Up a Database

Before you write a JDBC™ application, you need to set up a database called COFFEEBREAK. You learn how to create a database through your vendor documentation. Once you have set up the COFFEEBREAK database, continue with this lesson.

We strongly suggest that you use the Java DB database bundled with Netbeans, to work through this tutorial. Directions for setting up and connecting to the Java DB™ and using the NetBeans™ IDE can be found here.
Overview
Assume that our sample database is being used by the proprietor of a small coffee house called The Coffee Break, where coffee beans are sold by the pound and brewed coffee is sold by the cup. To keep things simple, also suppose that the proprietor needs only two tables, one for types of coffee and one for coffee suppliers.

To store data in the database, you create tables. Later, when you learn about creating the tables used as examples in this tutorial, the tables will be in the default database. We purposely kept the size and number of tables small to keep things manageable.

Once you have created the database and tables to store the data, you'll a open a connection with your DBMS. You also need to know some SQL code. After that, you'll discover how easy it is to use JDBC to pass SQL statements to your DBMS and then process the results that are returned.

Establishing a Connection

First, you need to establish a connection with the DBMS you want to use. Typically, a JDBC™ application connects to a target data source using one of two mechanisms:

[color=darkred]通过两种方式连接数据库:DriverManager和DataSource。
[/color]
* DriverManager: This fully implemented class requires an application to load a specific driver, using a hardcoded URL. As part of its initialization, the DriverManager class attempts to load the driver classes referenced in the jdbc.drivers system property. This allows you to customize the JDBC Drivers used by your applications.
[color=darkred]更推荐DataSource方式，它使底层数据源更透明[/color]
* DataSource: This interface is preferred over DriverManager because it allows details about the underlying data source to be transparent to your application. A DataSource object's properties are set so that it represents a particular data source.

Establishing a connection involves two steps: Loading the driver, and making the connection.
Loading the Driver

Loading the driver you want to use is very simple. It involves just one line of code in your program. To use the Java DB driver, add the following line of code:

       Class.forName("org.apache.derby.jdbc.EmbeddedDriver");

Your driver documentation provides the class name to use. In the example above, EmbeddedDriver is one of the drivers for Java DB.

Calling the Class.forName automatically creates an instance of a driver and registers it with the DriverManager, so you don't need to create an instance of the class. If you were to create your own instance, you would be creating an unnecessary duplicate, but it would do no harm.

After you have loaded a driver, it can make a connection with a DBMS.
Making the Connection

The second step in establishing a connection is to have the appropriate driver connect to the DBMS.
Using the DriverManager Class

The DriverManager class works with the Driver interface to manage the set of drivers available to a JDBC client. When the client requests a connection and provides a URL, the DriverManager is responsible for finding a driver that recognizes the URL and for using it to connect to the corresponding data source. Connection URLs have the following form:
jdbc:derby:<dbName>[propertyList]

[color=darkred]一个数据库可以在以下这些位置————very 奇怪[/color]
The dbName portion of the URL identifies a specific database. A database can be in one of many locations: in the current working directory, on the classpath, in a JAR file, in a specific Java DB database home directory, or in an absolute location on your file system.

If you are using a vendor-specific driver, such as Oracle, the documentation will tell you what subprotocol to use, that is, what to put after jdbc: in the JDBC URL. For example, if the driver developer has registered the name OracleDriver as the subprotocol, the first and second parts of the JDBC URL will be jdbc.driver.OracleDriver . The driver documentation will also give you guidelines for the rest of the JDBC URL. This last part of the JDBC URL supplies information for identifying the data source.

The getConnection method establishes a connection:

        Connection conn = DriverManager.getConnection("jdbc:derby:COFFEES");

In place of " myLogin " you insert the name you use to log in to the DBMS; in place of " myPassword " you insert your password for the DBMS. So, if you log in to your DBMS with a login name of " Fernanda " and a password of " J8, " just these two lines of code will establish a connection:

        String url = "jdbc:derby:Fred";
        Connection con = DriverManager.getConnection(url, "Fernanda", "J8");

If one of the drivers you loaded recognizes the JDBC URL supplied to the method DriverManager.getConnection, that driver establishes a connection to the DBMS specified in the JDBC URL. The DriverManager class, true to its name, manages all of the details of establishing the connection for you behind the scenes. Unless you are writing a driver, you probably won't use any of the methods in the interface Driver, and the only DriverManager method you really need to know is DriverManager.getConnection
[color=darkred]除非你要写个Driver，否则你只要知道DriverManager.getConnection这一个方法就可以了[/color]
The connection returned by the method DriverManager.getConnection is an open connection you can use to create JDBC statements that pass your SQL statements to the DBMS. In the previous example, con is an open connection, and you use it in the examples that follow.
Using a DataSource Object for a connection
Using a DataSource object increases application portability by making it possible for an application to use a logical name for a data source instead of having to supply information specific to a particular driver. The following example shows how to use a DataSource to establish a connection:

You can configure a DataSource using a tool or manually. For example, Here is an example of a DataSource lookup:
[color=darkred]为什么ds还要强转？[/color]

         InitialContext ic = new InitialContext()

         DataSource ds = ic.lookup("java:comp/env/jdbc/myDB");
         Connection con = ds.getConnection();
         DataSource ds = (DataSource) org.apache.derby.jdbc.ClientDataSource()
         ds.setPort(1527);
         ds.setHost("localhost");
         ds.setUser("APP")
         ds.setPassword("APP");

        Connection con = ds.getConnection(); 

DataSource implementations must provide getter and setter methods for each property they support. These properties typically are initialized when the DataSource object is deployed.

        VendorDataSource vds = new VendorDataSource();
        vds.setServerName("my_database_server");
        String name = vds.getServerName();

JDBC-ODBC Bridge Driver

For normal use, you should obtain a commercial JDBC driver from a vendor such as your database vendor or your database middleware vendor. The JDBC-ODBC Bridge driver provided with JDBC is recommended only for development and testing, or when no other alternative is available.