Java Database Connectivity (JDBC)

JDBC Introduction
JDBC helps you to write java applications that manage these four programming activities:

1. Register a JDBC Driver into DriverManager
   
2. Connect to a data source, like a database by creating a Connection
3. Send queries and update statements to the database
4. Retrieve and process the results received from the database in answer to your query

The following simple code fragment gives a simple example of these three steps:

========================================================================
Class.forName("oracle.jdbc.driver.OracleDriver);
Connection con = DriverManager.getConnection
          ( "jdbc:OracleDriver:DSN", "myLogin","myPassword");
    
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT a, b, c FROM Table1");
while (rs.next()) {
 int x = rs.getInt("a");
 String s = rs.getString("b");
 float f = rs.getFloat("c");
 }

=========================================================

when the Class.forName("oracle.jdbc.driver.OracleDriver) is
executed, the driver calss will be loaded if it was not
loaded earlier. As soon as the class is loaded the static
block will be executed. In the static block the JDBC driver
vendors are responsible for providing the static block in
the driver class. In static block they would have been
written the similar kind of code.

public class oracle.jdbc.driver.OracleDriver implements
Driver
{
static
{
Driver drv= new oracle.jdbc.driver.OracleDriver();
DriverManager.registerDriver(drv)
-----
-----
some other code

}
}

JDBC Driver Manager —

The JDBC DriverManager class defines objects which can connect Java applications to a JDBC driver. DriverManager has traditionally been the backbone of the JDBC architecture. It is quite small and simple.

The Standard Extension packages javax.naming and javax.sql let you use a DataSource object registered with a Java Naming and Directory Interface™ (JNDI) naming service to establish a connection with a data source. You can use either connecting mechanism, but using a DataSource object is recommended whenever possible.

JDBC Architecture

Two-tier and Three-tier Processing Models

The JDBC API supports both two-tier and three-tier processing models for database access.

Figure 1: Two-tier Architecture for Data Access.
In the two-tier model, a Java applet or application talks directly to the data source. This requires a JDBC driver that can communicate with the particular data source being accessed. A user's commands are delivered to the database or other data source, and the results of those statements are sent back to the user. The data source may be located on another machine to which the user is connected via a network. This is referred to as a client/server configuration, with the user's machine as the client, and the machine housing the data source as the server. The network can be an intranet, which, for example, connects employees within a corporation, or it can be the Internet.
In the three-tier model, commands are sent to a "middle tier" of services, which then sends the commands to the data source. The data source processes the commands and sends the results back to the middle tier, which then sends them to the user. MIS directors find the three-tier model very attractive because the middle tier makes it possible to maintain control over access and the kinds of updates that can be made to corporate data. Another advantage is that it simplifies the deployment of applications. Finally, in many cases, the three-tier architecture can provide performance advantages.
Figure 2: Three-tier Architecture for Data Access.
Until recently, the middle tier has often been written in languages such as C or C++, which offer fast performance. However, with the introduction of optimizing compilers that translate Java bytecode into efficient machine-specific code and technologies such as Enterprise JavaBeans™, the Java platform is fast becoming the standard platform for middle-tier development. This is a big plus, making it possible to take advantage of Java's robustness, multithreading, and security features.
With enterprises increasingly using the Java programming language for writing server code, the JDBC API is being used more and more in the middle tier of a three-tier architecture. Some of the features that make JDBC a server technology are its support for connection pooling, distributed transactions, and disconnected rowsets. The JDBC API is also what allows access to a data source from a Java middle tier.

Types of Drivers
There are many possible implementations of JDBC drivers. These implementations are categorized as follows:

• 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.


• 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.


• 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.


• 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.

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:

• 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.


• 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:[propertyList]
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
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:

 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.