DATABASE CONNECTIVITY

This articles explains the need of database connectivity, the way it is achieved through Java using the JDBC(java database connectivity) APIs.

Need of Database Connetivity

Consider a scenario where you have to develop an application for an airlines company to maintain a record of daily transactions. You install your database server ,lets say SQL server, design the airlines database and ask airlines personnel to use it. Will this daabase alone be of any use to the airline personnel? The answer is NO!. The task of updating SQL server by using SQL statements will be a tedious process. An application will need to be developed that is user friendly and provides a client, the options to retrieve , add, and modify data at the touch of a key.

Sun Microsystems has included JDBC API as a part of J2SDK to develope java applications that can communicate with the databases.The following figure shows the airlines reservation system developed in java interacting with the airlines database using the JDBC API:

PROBLEM:

Java applications cannot directly communicate with a database to submit data and retrieve the results of queries. This is because a database can interpret only SQL statements and not java language statements. For this reason, you need a mechanism to translate java statements into SQL statements . Java Drivers provide us with such a mechanism.

JDBC Drivers

JDBC DRIVERS act as an interface between a java application and a database. It enables connectivity to a database. A driver sends the request of a java aplication to the database . After processing the request, the database sends the response back to the driver . The driver translates and sends the response of the jdbc api. The JDBC API forwards it to the Java application.

JDBC supports four types of drivers

1. JDBC-ODBC Bridge driver
2. Native-API Partly-Java driver
3. JDBC-Net Pure-Java driver
4. Native Protocol Pure-Java driver

THE CODING PART...

Java has provided us with the jdbc api classes and interfaces , available in the java.sql and the javax.sql packages.The classes and interfaces perform a number of tasks, such asa establish and close a connection with the database , send a request to a database , retrieve data from a database, and update data in a database. The commomnly used classes and interfaces in the Jdbc API are:

• DriverManager class: loads the driver for a databse .
• Driver interface: Represents a database driver. All JDBC classes must implement the Driver interface.
• Connection interface: Enables you to establish a connection between a java application and a databse.
• Statement interface: Enables you to execute SQL statements.
• ResultSet interface: Represents the information retrieved form a databse.
• SQLException class: Provides information about the exceptions that occur while interacting with databases.

To query database and display the result using Java applications, you need to follow the below mentioned steps:

1. Load a driver.
2. Connect to a database.
3. Create and Execute JDBC statements.
4. Handle SQL exceptions.
5. Close all the connections.

Now we will discuss step wise implementation of these steps.

Loading a driver:

Java has provided us with two ways of loading a driver, namely:

• Using the forName() method
• Using the registerDriver() method

forName() method: The forName() method is available in the java.lang.Class class. The forName() method loads the JDBCdriver and register the driver with the driver manager. The syntax to load a JDBC driver to access a database is:

Class.forName("<driver_name>");

for eg: You can laod the JDBC-ODBC Bridge using the following method call:

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

rigisterDriver() method: You can create an instance of the Driver class to load the jdbc driver. The syntax for that is

Driver d = new sun.jdbc.odbc.JdbcOdbcDriver();

Once you have created the Driver object, call the registerDriver() method to register it with the DriverManager. Or in other words simply call the registerDriver() method using the class name DriverManager and pass the object of the driver class into the registerDriver() method. Syntax of that is:

DriverManager.registerDriver(d);

Connecting to a Database:

interface to establish a connection of the Java application with a database. You can create multiple Connection objects in a Java application to access and retrieve data from multiple databases. The DriverManager class provides the getConnection() method to create a COnnection object. The getConnection() method is an overloaded method that has three forms. Syntax of the same is:

Connection con = DriverManager.getConnectio.("jdbc.odbc.MydataSource");

where jdbc is the protocol, odbc is the subprotocol and MydataSource is the DSN(ie data source name)

Creating and Executing JDBC Statements

You need to create a Statement object to send requests to and retrieve results from a database. The Connection object provides the createStatements() method to create a Statement object. You can use the following code snippet to create a Statement object:

Connection con = DriverManager.getConnection("jdbc:odbc:MyDataSource","NewUser","New password");
Statement stmt = con.createStatement();

The statement interface contains the following methods to send static SQL statements to a database:

• ResultSet executeQuery(String str): Executes an sql statement and returns a single object of the type, ResultSet.
• int ExecuteUpdate(String str): Executes the sql statements and returns the number of data rows that are affected after processing the sql statement
• boolean execute(String str): Executes an SQL statement and returns a boolean value.

Handling SQL Exceptions:

The java.sql package provides the SQLException class, which is derived from the java.lang.Exception class.The SQLException is thrown by various methods in the JDBC API and enables you to determine the reason of the errors that occur while connecting a java application to a database. You can catch the SQLException in a java application using the try and catch exception handling block.

Closing the opened connection

As the resources used in running any process are extremely precious to us so closing all the opened resources is a good coding practice. To close a resource , we use the close() method.

I hereby leave you with a simple program which hal used the above mentioned classes and methods to give you a better understanding of JDBC:

Program code:

import java.sql.*;
import java.io.*;
class DataBaseDemo
{
  public static void main(String... aa)
  {
    try
    {
      //load the driver class
      Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

      System.out.println("driver loaded");

      //create the connection
      Connection c = DriverManager.getConnection("jdbc:odbc:mydatabase";"deven";"password");
      System.out.println("connected");

      // create the statement
      Statement s = c.createStatement();

      //create the result set
      ResultSet rs = s.executeQuery("select emloyeeID, login from human resource.employee");
      while(rs.next())
      {
        System.out.println(rs.getString(1));
        System.out.println(rs.getString(2));
      }
    }
    catch(Exception e)
    {
      System.out.println(e);
    }
  }
}