24 JDBC – INTRODUCTION

Dr M. Vijayalakshmi

    JDBC

 

JDBC stands for Java Database Connectivity. It is used for accessing databases from Java applications.Java is a very standardized programming language, but there are many versions of SQL databases.

 

ODBC vs. JDBC

 

Open DataBase Connectivity API can be used to access relational databases. ODBC can be used with Java applied as the form of JDBC-ODBC Bridge. ODBC is not appropriate for direct use from the Java programming language. It uses C interface. There occur a number of drawbacks in the security, implementation, robustness, automatic portability during the calls from Java to native C code.

 

Programs that are developed using Java-JDBC API are platform and vendor independent. JDBC involves the logical quote of “write once, compile once, run anywhere”. It provides a standard API for tool or database developers and makes it possible to write database applications using pure Java API. JDBC driver manager and  JDBC drivers provide the bridge between the database and Java world.

 

JDBC Architecture

 

Figure 26.1 Architecture of JDBC

 

Figure 26.1 shows the architecture of the working of JDBC. The application program written in Java code calls the JDBC library. JDBC loads a driver and through the driver, the application code talks to a particular database. A machine can have more than one driver and more than one database. The ideal thing about JDBC is that it can change database engines without changing any application code.

 

JDBC Drivers

 

There are four types of JDBC Drivers listed below,

 

• Type I: Bridge

• Type II: Native

• Type III: Middleware

• Type IV: Pure

    JDBC Driver Types

 

Figure 26.2 shows the types of Drivers.

 

Figure 26.2 Types of JDBC Drivers

 

The Type I driver is an JDBC-ODBC bridge driver that uses ODBC driver to connect to the database. This JDBC-ODBC bridge driver converts JDBC method calls into the ODBC function calls.

The Type II drivers are the Native API driver that uses the client-side libraries of the database. This driver converts JDBC method calls into native calls of the database API. It is not written entirely in Java.

The Type III drivers are called the Network Protocol driver that uses middleware which is an application server that converts JDBC calls directly or indirectly into the vendor-specific database protocol. It is fully written in Java.

The Type IV drivers are the thin driver that converts JDBC calls directly into the vendor-specific database protocol. That is why it is known as thin driver. It is fully written in Java language.

 

Type I Drivers

 

These drivers uses bridging technology. They require installation/configuration on client machines.These drivers are not recommended for Web. Example of Type I Driver is ODBC Bridge.

   Type II Drivers

 

These drivers are native API drivers. They require installation/configuration on client machines. Type II drivers are used to leverage existing CLI libraries and are usually not thread-safe, mostly obsolete now. Examples include Intersolv Oracle Driver, WebLogic drivers.

 

Type III Drivers

 

These drivers call middleware server, usually on database host. They are very flexible and allow access to multiple databases using one driver. There is only the need to download one driver, but it is another server application to install and maintain. An example of it is Symantec DBAnywhere.

 

Type IV Drivers

 

There is full 100% percentile Pure Java referred as the Holy Grail. These drivers use Java networking libraries to talk directly to database engines. A disadvantage here is the need to download a new driver for each database engine. Examples include Oracle, mySQL etc.

 

Let us now look in detail about JDBC Drivers.

 

JDBC Driver

 

The Driver class names for each of the database is given below,

In Oracle, the driver class name can be given as,

oracle.jdbc.driver.OracleDriver

In MySQL, the driver class name can be given as,

com.mysql.jdbc.Driver

In MS SQL, the driver class name can be given as,

com.microsoft.jdbc.sqlserver.SQLServerDriver

 

JDBC Limitations 

 

The limitations of working with JDBC is that there are no scrolling cursors available and no bookmarks can be made while processing the records thereby it allows only sequential processing.

 

Working with JDBC

 

Package to be Imported

 

JDBC is implemented via classes in the java.sql package.

import java.sql.*

 

Loading a Driver Directly

 

Driver d = new foo.bar.MyDriver();

Connection c = d.connect(…);

 

We can create a Driver and load the driver. Using the Driver object we need to create a Connection object . This is not recommended but instead we can use a Driver Manager but this is useful if we know that we need this particular driver.

 

DriverManager

 

A Driver Manager tries all the drivers. It uses the first one that works. When a driver class is first loaded, it registers itself with the Driver Manager. Therefore, to register a driver, we need to just load the driver.

 

Registering a Driver

 

Either of the below two methods can be used to register a driver.

 

1. Statically load driver

Class.forName(“foo.bar.MyDriver”);

Connection c = DriverManager.getConnection(…);

2. Use the jdbc.drivers system property

   JDBC Object Classes

 

The classes that are used from the imported java.sql.* package is listed below.

 

• DriverManager

This class is used for loading and choosing the drivers.

• Driver

This class performs the connection to actual database.

• Connection

   The Connection class creates the connectivity through a series of SQL statements to and from the database.

• Statement

The Statement object created using this class represents a single SQL statement.

• ResultSet

The ResultSet object represents a set of the records returned from a statement.

 

JDBC URL’s

 

The format of JDBC URL is given below that has components like subprotocol and source.

 

For example,

 

jdbc:subprotocol:source

Here each driver has its own subprotocol . Each subprotocol has its own syntax for the source.

The URL for Type I Driver which is an ODBC Bridge is given below.

jdbc:odbc:DataSource

Example: jdbc:odbc:jnf

The URL to connect to MySQL database is given below,

jdbc:mysql://host[:port]/database

Example: jdbc:mysql://foo.nowhere.com:4333/jnf

     DriverManager

 

Connection getConnection (String url, String user, String password)

This connects to given JDBC URL with given user name and password and returns a Connection object. It can throw java.sql.SQLException when connection cannot be established.

 

Connection

 

A Connection represents a session with a specific database. Within the context of a Connection, SQL statements are executed and results are returned. A program can have multiple connections to a database. A connection provides “metadata” — information about the database, tables, and fields, along with the methods to deal with transactions.

 

Obtaining a Connection

 

The snippet code for obtaining the connection with JDBC-ODBC bridge is given below. First we need to dynamically load and register the driver. Then establish a connection by creating a Connection object using the DriverManager class.

 

String url = “jdbc:odbc:jnf”;

try {

Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);

Connection con = DriverManager.getConnection(url);

}

catch (ClassNotFoundException e)

{ e.printStackTrace(); } catch (SQLException e)

{ e.printStackTrace(); }

   

Connection Methods

 

In the context of a Connection, SQL statements are executed and results are returned. Statement objects can be created using createStatement() method on the Connection object. Similarly PreparedStatement object can be created when we use prepareStatement() method on the Connection object and CallableStatement object can be created when we use prepareCall() method on the Connection object. The syntax of these methods are given below.

 

1. Statement createStatement()

This returns a new Statement object.

2. PreparedStatement prepareStatement(String sql)

This returns a new PreparedStatement object.

3. CallableStatement prepareCall(String sql)

This returns a new CallableStatement object.

 

Statement

 

A Statement object is used for executing a static SQL statement and obtaining the results produced by it.

 

Statement Methods

 

There are three methods which works with the Statement object.

ResultSet executeQuery(String)

This method executes an SQL SELECT statement that returns a single ResultSet.

int executeUpdate(String)

This method is used to execute an SQL INSERT, UPDATE or DELETE statement and returns the number of rows changed.

boolean execute(String)

This method is used to execute an SQL statement that may return multiple results.

   

ResultSet

 

A ResultSet provides access to a table of data generated by executing a Statement. Only one ResultSet per Statement can be open at once. The table rows are retrieved in sequence. A ResultSet maintains a cursor pointing to its current row of data. The ‘next()’ method is used to move the cursor to the next row.

 

ResultSet Methods

 

Below is the list of methods to work with the ResultSet object.

  • boolean next()

     This method activates the next row, the first call to next() activates the first row and returns false if there are no more rows.

  • void close()

   This method disposes the ResultSet. It allows you to re-use the statement that created it automatically called by most statement methods.

  • Type getType(int columnIndex)

This method returns the given field as the given type fields indexed starting at 1 (not 0).

  • Type getType(String columnName)

This method performs the same function as before, but uses name of field and less efficient.

  • int findColumn(String columnName)

This method looks up column index given column name.

  • String getString(int columnIndex)

    This method retrieves the value of the designated column in the current row of this ResultSet object as a String in the Java programming language.

  • boolean getBoolean(int columnIndex)

    This method retrieves the value of the designated column in the current row of this ResultSet object as a boolean in the Java programming language.

  • byte getByte(int columnIndex)

     This method retrieves the value of the designated column in the current row of this ResultSet object as a byte in the Java programming language.

  • short getShort(int columnIndex)

      This method retrieves the value of the designated column in the current row of this ResultSet object as a short in the Java programming language.

  •  int getInt(int columnIndex)

     This method retrieves the value of the designated column in the current row of this ResultSet object as an int in the Java programming language.

  •  long getLong(int columnIndex)

    This method retrieves the value of the designated column in the current row of this ResultSet object as a long in the Java programming language.

  •  float getFloat(int columnIndex)

    This method retrieves the value of the designated column in the current row of this ResultSet object as a float in the Java programming language.

  • double getDouble(int columnIndex)

    This method retrieves the value of the designated column in the current row of this ResultSet object as a double in the Java programming language.

  •   Date getDate(int columnIndex)

     This method retrieves the value of the designated column in the current row of this ResultSet object as a java.sql.Date object in the Java programming language.

  • Time getTime(int columnIndex)

     This method retrieves the value of the designated column in the current row of this ResultSet object as a java.sql.Time object in the Java programming language.

  • Timestamp getTimestamp(int columnIndex)

      This method retrieves the value of the designated column in the current row of this ResultSet object as a java.sql.Timestamp object in the Java programming language.

 

isNull

 

In SQL, NULL means the field is empty. It is not the same as 0 or “”. In JDBC, you must explicitly ask if a field is null by calling,

ResultSet.isNull(column)

 

Summary

 

This module gave an introduction about JDBC API that defines how a client may access a database. It also discussed about the JDBC Architecture that uses a driver manager and database-specific drivers to provide a transparent connectivity to heterogeneous databases. The module also provides us with a basic understanding about the design of JDBC API, the classes, methods and relations in them.

 

References