Java for Web Development: Create Full-Stack Java Applications with Servlets, JSP Pages, MVC Pattern and Database Connectivity

CHAPTER 1

Database Connectivity

Introduction

Most web-based application programs need to interact with Database Management Systems (DBMS). This DBMS is used to retrieve information from the database repositories by applications. For example, the online shopping mail needs to keep track of its customers and the items sold. A search site like www.yahoo.com needs to keep track of the URLs of the web pages visited by the user.

Structure

In this chapter, we will cover the following topics:

Techniques of database programming using Java

Types of drivers to connect the Java program with database

How databases are accessed using the JDBC-ODBC bridge and ODBC drivers

Objectives

In this chapter, you will be able to establish the connection between the database and Java program. You will be able to read and write the data from the database with the full support of the stored procedure and cursor.

Database Management

A database is a collection of related information, and a DBMS is software that provides you with a mechanism to retrieve, modify, and add the data to the database. There are many DBMS/RDBMS products available, for example, MS-Access, MS-SQL Server, Oracle, Sybase, Informix Progress, and Ingress. Each of these Relational Database Management Systems (RDBMS) stores the data in its form.

For example, MS-Access stores the data in the MDB file format, whereas MS-SQL stores the data in the. DAT file format.

Imagine you have been assigned a task to develop an application for a general store that allows a shop owner to maintain a record of the daily transactions. You design the database, install an MS-SQL server/Oracle on the shop owner's machine, and tell him to use it.

It will be a good idea for you to develop a customized front-end application in which the client is given options to retrieve, add, and modify the data at the touch of the key.

To accomplish this, you should have a mechanism of making the application work with the file format of the database, that is. MDB or. DAT files.

For your application to communicate with the database, it needs to have the following information:

The location of the database

The name of the database

Figure 1.1 illustrates a database application architecture:

Figure 1.1: A database application architecture

This means that the application you create would be able to work with only one kind of database and will be very difficult to code and port.

The preceding problems are solved by Microsoft's mechanism for efficient communication with the databases called Open Database Connectivity (ODBC).

ODBC Application Programming Interface (API)

ODBC API is a set of library routines that enable your programs to access a variety of databases. All you need to do is install a DBMS-specific ODBC driver and write your program to interact with a specified ODBC driver. Figure 1.2 shows that the application communicates with the ODBC Driver Manager, which transfers the query to a database driver for whom the user wants to communicate.

Later, if the database is upgraded to a newer version of RDBMS or ported to a different RDBMS product, you will need to change only the ODBC driver and not the program, as shown in Figure 1.2:

Figure 1.2: ODBC application architecture

JDBC-API

Java Database Connectivity (JDBC) provides a database programming API for Java programs. Since the ODBC API is written in the C language and makes use of the pointers and other constructs that Java does not support, a Java program cannot directly communicate with an ODBC driver.

Sun Microsystems created the JDBC-ODBC bridge driver that translates the JDBC-API to the ODBC.API. It is used with the ODBC drivers available.

Categories of JDBC Drivers

There are several categories of the JDBC drivers available, which are as follows:

JDBC-ODBC bridge + ODBC Driver

Native API – Partly a Java driver

JDBC – Net pure Java Drivers / N/W Protocol

Nature Protocol pure Java drivers / thin Driver

JDBC-ODBC bridge + ODBC driver

The JDBC-ODBC bridge driver uses the ODBC driver to connect to the database. The JDBC-ODBC Bridge Driver converts the JDBC method calls into the ODBC function calls. This is now discouraged because of the thin Driver. Refer to Figure 1.3, which illustrates the JDBC application architecture:

Figure 1.3: JDBC Application Architecture

JDBC Driver Manager

The JDBC Driver Manager is the backbone of the JDBC architecture. The function of the JDBC driver manager is to connect a Java application to the appropriate Driver specified in your Java Program.

JDBC-ODBC Bridge

As a part of JDBC, Sun Microsystems provides a driver to access the ODBC data sources. This Driver is called the JDBC-ODBC bridge. The JDBC-ODBC bridge is implemented as the JDBC ODBC class, and the nature library is used to access the ODBC driver. In the Windows platform, the native library is JdbcOdbc.dll

The following are the advantages:

Easy to use

It can be easily connected to any database

The following are the disadvantages:

Performance degraded because the JDBC method call is converted into the ODBC function calls

The ODBC driver needs to be installed on the client machine

Native-API driver

The Native-API Driver uses the client-side libraries of the database. The Driver converts the JDBC method calls into native calls of the database API. It is not written entirely in Java.

The following is the advantage:

Performance upgraded, better than the JDBC-ODBC bridge driver

The following are the disadvantages:

The Native Driver needs to be installed on each client machine

The vendor-client library needs to be installed on the client machine

Network protocol driver

It uses middleware (application server) that converts the JDBC calls directly or indirectly into the vendor-specific database protocol. It is fully written in Java.

The following is the advantage:

No client-side library is required

The following are the disadvantages:

Network support is required

Requires database-specific coding to be done in the middle tier

Maintenance of Network Protocol becomes costly because it requires database-specific coding

Thin Driver

The thin Driver converts the JDBC calls directly into the vendor-specific database protocol. That is why it is known as the thin Driver. It is fully written in the Java language.

The following are the advantages:

Better performance than all the other drivers

No software is required at the client-side or server-side

The following is the disadvantage:

Drivers depend on the database

Querying a database

Now that you have understood the JDBC architecture, you can write a Java application that can work with a database. In this section, you will learn about the packages and classes available in Java that allow you to send the queries to a database and process the query results.

Connecting to a database

The java.sql package contains the classes that help in connecting to a database, sending embedded SQL statements to the database, and processing the query results.

The connection object

The connection object represents a connection with a database. You may have several connection objects in an application that connects to one or more databases.

Loading the Driver and establishing the connection

To establish a connection with a database, complete the following steps:

     1.  Register the ODBC JDBC/thin Driver by calling the forName() method from the Class class.

          For example, Class.forName(oracle.JDBC.driver.OracleDriver);

     2.  Call the getConnection(JDBC URL) method from the DriverManager class.

The getConnection() method of the Driver Manager class attempts to locate the Driver that can connect to the database represented by the JDBC URL passed to the getConnection() method.

The JDBC URL

The JDBC-URL is a string that provides a way of identifying a database. A JDBC URL is divided into the following three parts:

: _:

In this, in a JDBC URL is always Jdbc, which is the name of the database connectivity mechanism. If the mechanism of retrieving the data is ODBC-JDBC bridge, the subprotocol must be ODBC. is used to identify the