JDBC, Data Source, Connection Pool

 JDBC

JDBC is an API for accessing databases in an uniform way.

JDBC Provides:

• Platform independent access to data bases
• Location transparency
• Transparency to proprietary database issues
• Support for both two-tier and multi-tier models for database access.

JDBC Architecture :

Driver Types:

Type 1: JDBC-ODBC Bridge

This combination provides JDBC access via ODBC drivers. ODBC binary code and in many cases, database client code must be loaded on each client machine that uses a JDBC-ODBC Bridge. A product called SequeLink from Data Direct Technologies provides a driver that supports some ODBC drivers (for example Microsoft Access).

Type one drivers provide JDBC access via one or more Open Database Connectivity (ODBC) drivers. ODBC, which predates JDBC, is widely used by developers to connect to databases in a non-Java environment.

Pros: A good approach for learning JDBC. May be useful for companies that already have ODBC drivers installed on each client machine typically the case for Windows-based machines running productivity applications. May be the only way to gain access to some low-end desktop databases.

Cons: Not for large-scale applications. Performance suffers because there’s some overhead associated with the translation work to go from JDBC to ODBC. Doesn’t support all the features of Java. User is limited by the functionality of the underlying ODBC driver.

A JDBC/ODBC bridge provides JDBC API access through one or more ODBC drivers. Some ODBC native code and in many cases native database client code must be loaded on each client machine that uses this type of driver.

The advantage for using this type of driver is that it allows access to almost any database since the database ODBC drivers are readily available.

Disadvantages for using this type of driver include the following:

• Performance is degraded since the JDBC call goes through the bridge to the ODBC driver then to the native database connectivity interface. The results are then sent back through the reverse process
• Limited Java feature set
• May not be suitable for a large-scale application

Type 2: Partial Java Driver

This type of driver converts JDBC calls into calls on the client API for Oracle, Sybase, Informix, DB2, or other DBMS. Note that, like the bridge driver, this style of driver requires that some binary code be loaded on each client machine.

This type of driver converts the calls that a developer writes to the JDBC application programming interface into calls that connect to the client machine’s application programming interface for a specific database, such as IBM, Informix, Oracle or Sybase.

Pros: Performance is better than that of Type 1, in part because the Type 2 driver contains compiled code that’s optimized for the back-end database server’s operating system.

Cons: User needs to make sure the JDBC driver of the database vendor is loaded onto each client machine. Must have compiled code for every operating system that the application will run on. Best use is for controlled environments, such as an intranet.

A native-API partly Java technology-enabled driver converts JDBC calls into calls on the client API for DBMSs. Like the bridge driver, this style of driver requires that some binary code be loaded on each client machine. An example of this type of driver is the Oracle Thick Driver, which is also called OCI.

Advantages for using this type of driver include the following:

• Allows access to almost any database since the databases ODBC drivers are readily available
• Offers significantly better performance than the JDBC/ODBC Bridge
• Limited Java feature set

Disadvantages for using this type of driver include the following:

• Applicable Client library must be installed
• Type 2 driver shows lower performance than type 3 or 4

Type 3: Pure Java Driver for Database Middleware

This style of driver translates JDBC calls into the middleware vendor’s protocol, which is then translated to a DBMS protocol by a middleware server. The middleware provides connectivity to many different databases.

This driver translates JDBC calls into the middleware vendor’s protocol, which is then converted to a database-specific protocol by the middleware server software.

Pros: Better performance than Types 1 and 2. Can be used when a company has multiple databases and wants to use a single JDBC driver to connect to all of them. Server-based, so no need for JDBC driver code on client machine. For performance reasons, the back-end server component is optimized for the operating system on which the database is running.

Cons: Needs some database-specific code on the middleware server. If the middleware must run on different platforms, a Type 4 driver might be more effective.

A net-protocol fully Java-enabled driver translates JDBC API calls into a DBMS-independent net protocol which is then translated to a DBMS protocol by a server. This net server middleware is able to connect all of its Java technology-based clients to many different databases. Many mainframe legacy non-relational databases use this kind of driver.

Advantages for using this type of driver include the following:

• Allows access to almost any database since the databases ODBC drivers are readily available
• Offers significantly better performance than the JDBC/ODBC Bridge and Type 2 Drivers
• Advanced Java feature set
• Scalable
• Caching
• Advanced system administration
• Does not require applicable database client libraries

The disadvantage for using this type of driver is that it requires a separate JDBC middleware server to translate specific native-connectivity interface.

Type 4: Direct to Database pure Java Driver

This style of driver converts JDBC calls into a network protocol that sends the converted packets in a proprietary format to be used directly by DBMSs, thus allowing a direct call from the client machine to the DBMS server and providing a practical solution for intranet access. This type of driver has become very popular recently and is supported by most database software vendors. All JDBC drivers from Data Direct Technologies (driver vendor) are Type 4 drivers.

Pros: Better performance than Types 1 and 2. No need to install special software on client or server.

Cons: Not optimized for server operating system, so the driver can’t take advantage of operating system features. (The driver is optimized for the database and can take advantage of the database vendor’s functionality.) User needs a different driver for each different database.

A native-protocol fully Java technology-enabled driver converts JDBC technology calls into the network protocol used by DBMSs directly. This allows a direct call from the client machine to the DBMS server.

Advantages for using this type of driver include the following:

• Advantages for using this type of driver include the following:
• Allows access to almost any database since the databases ODBC drivers are readily available
• Offers significantly better performance than the JDBC/ODBC Bridge and Type 2 Drivers
• Scalable
• Caching
• Advanced system administration
• Superior performance
• Advance Java feature set
• Does not require applicable database client libraries
• The disadvantage for using this type of driver is that each database will require a driver
• The disadvantage for using this type of driver is that each database will require a driver

JDBC Architecture Types:

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

Two-Tier Architecture

In the two-tier model, a Java 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.

Multi-Tier Architecture

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.

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 row sets. The JDBC API is also what allows access to a data source from a Java middle tier.

Data Source 

What is a Data Source?

A Data Source object provides a way for a JDBC client to obtain a database connection from a connection pool.

A Data Source :

• Is stored in the WLS JNDI tree
• Can support transactions
• Is associated with a connection pool.

What is a Connection Pool?

A connection pool is a group of ready-to-use database connections associated with a Data Source.

Connection pools:

• Are created at WebLogic Server startup
• Can be administered using the Admin Console
• Can be dynamically resized to accommodate increasing load

Benefits of Data Sources + Connection Pools:

• Time and overhead are saved by using an existing database connection
• Connection information is managed in one location in the Admin Console
• The number of connections to a database can be controlled
• The DBMS can be changed without the application developer having to modify underlying code.
• A Connection pool allows an application to borrow a DBMS connection. 

Data Source Architecture 

How Data Sources are used

Step 1) A client will Look up for Data Source through a JNDI 

Step 2) WebLogic Returns the Data Source Name by Identifying in JNDI tree.

Step 3) Once Data Source is retuned to the client, then client will borrow a connection from Connection Pool using getConnection() method.

Step 4) Connection Pool will return a connection to the client.

Step 5) Client will access the database using the connection which is retuned from the connection pool 

Multi Data Source 

An abstraction around a group of generic data sources that provides load balancing or failover processing.