JDBC

Introduction to JDB


Java started as an elegant and promising Web programming language. Thereafter, its transition to hard-core computing environment
 is a phenomenal feat. Apart from its significance on client-side programming, Java has been outstanding in developing mission-critical
 enterprise-scale applications and hence its immense contribution to server-side computing gets all round attention. Thus Java as
 a programming language for enterprise computing has been doing well for the past couple of years. As every enterprise includes a
 database, Sun Microsystems has to come with necessary features for making Java to shine in database programming as well.
 In this overview, we discuss about the role of Java Database Connectivity in accomplishing database programming with ease.

Database Programming with Java

Database Programming with Java

Java provides database programmers some distinct advantages, such as easy object to relational mapping, database independence
 and distributed computing. For languages, such as C++ and Smalltalk, there is a need for tools for mapping their objects to
 relational entities. Java provides an alternative to these tools that frees us from the proprietary interfaces associated with
 database programming. With the "write once, compile once, run anywhere" power that JDBC offers us, Java's database connectivity
 allows us to concentrate on the translation of relational data into objects instead of how we can get that data from the database.

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.

Java affects the way we distribute and maintain application. Currently a Web application allows user to download a bunch of flight
 information as an HTML page. While viewing the page for a particular flight, suppose some one makes a reservation for a seat on that
 flight and this process will not be available to the viewers as the page is just a copy of data from the database. To view the change
 that just occurred, the viewers again have to contact the database to get the latest data. If we reconstruct the same Web application
 using Java RMI to retrieve the data from a single flight object on the server, any number of users can view the data simultaneously and
 if there is any reservation or any change taking place, immediately the changes made to the data will be sent back to all the users and
 hence the users can avail the latest data at any time. Thus JDBC can combine with Java RMI to develop distributed enterprise-scale
 mission-critical three-tier database applications.
JDBC for Relational Databases

There are three main database technologies. They are relational, object and object-relational. A Java application can access any one
 of these database architectures. 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 and Java, as a object solution makes common cause with relational database technology due to the
 fact that object-oriented philosophy dictates that an object's behavior is inseparable from its data. In choosing the
 object-oriented reality of Java, we need to create a translation layer that maps the relational world into our object
 world. Thus with the goal of accessing relational databases, JDBC API specification has been defined by Sun Microsystems
 with the help of popular database vendors.

 What is JDBC?

JDBC is essentially an Application Programming Interface (API) for executing SQL statements, and extracting the results.
Using this API, we can write database clients, such as Java applets, servlets and Enterprise JavaBeans, that connect to a
relational database, such as Oracle, MySQL, Sybase, Informix, Ingres, PostgreSQL, or any other database that implements this API,
 execute SQL statements, and process the results extracted from the database.
JDBC Versions

JDBC 2.0 API is the latest version of JDBC API available in the java.sql package. The previous version focused primarily on basic
 database programming services such as creating connections, executing statements and prepared statements, running batch queries, etc.
 However, the current API supports batch updates, scrollable resultsets, transaction isolation, and the new SQL:1999 data types such as
 BLOB and CLOB in addition to the SQL2 data types.

JDBC 2.0 Optional Package API is available in the javax.sql package and is distributed with the enterprise edition of Java 2, that is J2EE.
 The optional package addresses Java Naming and Directory Interface (JNDI)-based data sources for managing connections, connection pooling,
 distributed transactions, and rowsets.
The Benefits of JDBC

The JDBC API provides a set of implementation-independent generic database access methods for the above mentioned SQL-compliant databases.
 JDBC abstracts much of the vendor-specific details and generalizes the most common database access functions. Thus resulted a set of classes
 and interfaces of the java.sql package that can be used with any database providing JDBC connectivity through a vendor-specific JDBC driver
 in a consistent way. Thus if our application conforms to the most commonly available database features, we should be able to reuse
 an application with another database simply by switching to a new JDBC driver. In other words, JDBC enables us to write applications
 that access relational databases without any thought as to which particular database we are using.
Also database connectivity is not just connecting to databases and executing statements. In an enterprise-level application environment
, there are some important requirements to be met, such as optimizing network resources by employing connection pooling, and implementing
distributed transactions. JDBC has all these features in accomplishing advanced database programming.
The JDBC Drivers

A database vendor typically provides a set of APIs for accessing the data managed by the database server. Popular database vendors have
 supplied some proprietary APIs for client access. Client applications written in native languages such as C and C++ can make these API
 calls for database access directly. The JDBC API provides a Java-language alternative to these vendor-specific APIs. Though this takes
 away the need to access vendor-specific native APIs for database access, the implementation of the JDBC layer still need to make these
 native calls for data access.

JDBC accomplishes its goals through a set of Java interfaces, each gets implemented differently by different vendors. The set of classes
 that implement the JDBC interfaces for a particular database engine is called a JDBC driver. Hence the necessity of a JDBC driver for
 each database server. In building a database application, we do not have to think about the implementation of these underlying classes
 at all as the whole point of using JDBC is to hide the specifics of each database and let us concentrate on our application.
 A JDBC driver is a middleware layer that translates the JDBC calls to the vendor-specific APIs. The Java VM uses the JDBC
 driver to translate the generalized JDBC calls into vendor-specific database calls that the database understands.

There are a number of approaches for connecting from our application to a database server via a database driver.

JDBC-ODBC Bridge - Open Database Connectivity (ODBC) was developed to create a single standard for database access in the Windows
environment. ODBC is a Windows API standard for SQL and it is based on X/Open Call-Level Interface (CLI) specification, which is
a standard API for database access. CLI is intended to be vendor, platform, and database neutral. But ODBC API defines a set of
 functions for directly accessing the data without the need for embedding SQL statements in client applications coded in higher
 level languages.

The JDBC API is originally based on the ODBC API. Thus, it becomes feasible for the first category of JDBC drivers providing
a bridge between the JDBC API and the ODBC API. This bridge translates the standard JDBC calls to corresponding ODBC calls. The
 driver then delegates these calls to the data source. Here, the Java classes for the JDBC API and the JDBC-ODBC bridge are invoked
 within the client application process. Similarly, the ODBC layer executes in another process. This configuration requires the client
 application to have the JDBC-ODBC bridge API, the ODBC driver, and the native language level API, such as the OCI library for Oracle
 installed on each client machine.
Each data access call has to go through many layers, this approach for data access is inefficient for high-performance database
access requirements. Though this is not a preferred one, this has to be used in some situations for example, a Microsoft Access
2000 database can be only be accessed using the JDBC-ODBC bridge.

Part Java, Part Native Driver - This approach use a mixture of Java implementation and vendor-specific native APIs for data access.
 This one is a little bit faster than the earlier one. When a database call is made using JDBC, the driver translates the request
 into vendor-specific API calls. The database will process the request and send the results back through the API, which will forward
 them back to the JDBC driver. The JDBC driver will format the results to confirm to the JDBC standard and return them to the program.
 In this approach, the native JDBC driver, which is part Java and part native code, should be installed on each client along with the
 vendor-specific native language API. The native code uses vendor-specific protocols for communicating with the database. The improved
 efficiency makes this a preferred method over the use of the earlier one.

Intermediate Database Access Server This approach is based on intermediate (middleware) database servers with the ability to connect
multiple Java clients to multiple database servers. In this configuration, clients connect to various database servers via an intermediate
 server that acts as a gateway for multiple database servers. While the specific protocol used between clients and the intermediate server
 depends on the middleware server vendor, the intermediate server can use different native protocols to connect to different databases.
 The Java client application sends a JDBC call through a JDBC driver to the intermediate data access server. The middle-tier then handles
 the request using another driver, for example the above one, to complete the request. This is good because the intermediate server can
 abstract details of connections to database servers.

Pure Java Drivers - This a pure Java alternative to part Java, part native driver. These drivers convert the JDBC API calls to direct
network calls using vendor-specific networking by making direct socket connections with the database like Oracle Thin JDBC Driver.
 This is the most efficient method of accessing databases both in performance and development time. It also the simplest to deploy
 since there are no additional libraries or middleware to install. All major database vendors, such as Oracle, Sybase, and Microsoft,
 provide this type of drivers for their databases.
Alternatives to JDBC

There are two major alternatives to JDBC at present. They are ODBC and SQLJ, a non-approved Java standard for database access.

Without JDBC, only disparate, proprietary database access solutions exist. These solutions force the developer to build a layer
 of abstraction on top of them in order to create database-independent code. The ODBC solution provide this universal abstraction
 layer for languages, such as C and C++, and popular developmental tools, such as Delphi, PowerBuilder, and VisualBasic. But ODBC
 can not enjoy the platform independence of Java as ODBC is restricted to Windows platform. On using JDBC, the server application
 can pick the database at run time based on which client is connecting. Thus JDBC facilitates to change the database just by
 changing the JDBC URL and driver name without adding any new code.