JDBC Introduction, Summary and Details of JDBC Connectivity

JDBC Introduction

How to Interact with DB?

Generally every DB vendor provides a User Interface through which we can easily execute SQL query’s and get the result (For example Oracle Query Manager for Oracle, and TOAD (www.quest.com) tool common to all the databases). And these tools will help DB developers to create database. But as a programmer we want to interact with the DB dynamically to execute some SQL queries from our application (Any application like C, C++, JAVA etc), and for this requirement DB vendors provide some Native Libraries (Vendor Specific) using this we can interact with the DB i.e. If you want to execute some queries on Oracle DB, oracle vendor provides an OCI (Oracle Call Interface) Libraries to perform the same.

Problem 

Application becomes DB Specific, if you want to change your DB you need to change your application’s parameters which are required to connect to DB, and it is a risk on our application. (Since we know writing a new application is faster & easier than modifying an existing application)

Advantages

·         Fast access to DB

·         All individual features provided by the DB can be used

About ODBC

What is ODBC

ODBC (Open Database Connectivity) is an ISV (Independent software vendor product) composes of native API to connect to different databases through via a single API called ODBC.

Open Database Connectivity (ODBC) is an SQL oriented application programming interface developed by in collaboration with IBM and some other database vendors.

ODBC comes with Microsoft products and with all databases on Windows OS.

 

ODBC Architecture

 

Advantages

·         Single API (Protocol) is used to interact with any DB

·         Switching from one DB to another is easy

·         Doesn’t require any modifications in the Application when you want to shift from one DB to other.

What for JDBC?

As we have studied about ODBC and is advantages and came to know that it provides a common API to interact with any DB which has an ODBC Service Provider’s Implementation written in Native API that can be used in your applications.

If an application wants to interact with the DB then the options which have been explained up to now in this book are:

1. Using Native Libraries given by the DB vendor
2. Using ODBC API

And we have listed there Advantages and Disadvantages.

But if the application is a JAVA application then the above given options are not recommended to be used due to the following reasons

1. Native Libraries given by DB vendor
1. Application becomes vendor dependent and
2. The application has to use JNI to interact with Native Lib which may cause serious problem for Platform Independency in our applications.
2. And the second option given was using ODBC API which can solve the 1.a problem but again this ODBC API is also a Native API, so we have to use JNI in our Java applications which lead to the 1.b described problem.

And the answer for these problems is JDBC (Java Data Base Connectivity) which provides a common Java API to interact with any DB.

What is JDBC

As explained above JDBC standards for Java Data Base Connectivity. It is a specification given by Sun Microsystems and standards followed by X/Open SAG (SQL Access Group) CLI (Call Level Interface) to interact with the DB.

About X/Open Group

The JDBC API lets you invoke SQL commands from Java programing language methods. The JDBC API provides database-independent connectivity between the JAVA Applications and a wide range of tabular data bases. JDBC technology allows an application component provider to:

·         Perform connection and authentication to a database server

·         Manage transactions

·         Moves SQL statements to a database engine for preprocessing and execution

·         Executes stored procedures

·         Inspects and modifies the results from SELECT statements

JDBC API

JDBC API is divided into two parts

1. JDBC Core API
2. JDBC Extension or Optional API

JDBC Core API (java.sql package)

This part of API deals with the following futures

1.     Establish a connection to a DB

2.     Getting DB Details

3.     Getting Driver Details

4.     maintaining Local Transaction

5.     executing query’s

6.     getting result’s (ResultSet)

7.     preparing pre-compiled SQL query’s and executing

8.     executing procedures & functions

JDBC Ext OR Optional API (javax.sql package)

      This part of API deals with the following futures

1.     Resource Objects with Distributed Transaction Management support

2.     Connection Pooling.

These two parts of Specification are the part of J2SE and are inherited into J2EE i.e. this specification API can be used with all the component’s given under J2SE and J2EE.

JDBC Architecture:

In the above show archetecture diagram the JDBC Driver forms an abstraction layer between the JAVA Application and DB, and is implemented by 3^rd party vendors or a DB Vendor. But whoever may be the vendor and what ever may be the DB we need not to worry will just us JDCB API to give instructions to JDBC Driver and then it’s the responsibility of JDBC Driver Provider to convert the JDBC Call to the DB Specific Call.

And this 3^rd party vendor or DB vendor implemented Drivers are classified into 4-Types namely 

1. Type-1 (JDBC ODBC Bridge Driver)
2. Type-2 (Java-Native API Driver)
3. Type-3 (Java Net Protocol Driver)
4. Type-4 (Java Native Protocol driver)

Types Of Drivers

Type-1 (JDBC ODBC Bridge Driver)

  

 

This type of Driver is designed to convert the JDBC request call to ODBC call and ODBC response call to JDBC call.

The JDBC uses this interface in order to communicate with the database, so neither the database nor the middle tier need to be Java compliant. However ODBC binary code must be installed on each client machine that uses this driver. This bridge driver uses a configured data source.

Advantages

• Simple to use because ODBC drivers comes with DB installation/Microsoft front/back office product installation
• JDBC ODBC Drivers comes with JDK software

Disadvantages

·         More number of layers between the application and DB. And more number of API conversions leads to the downfall of the performance.

·         Slower than type-2 driver

Where to use?

This type of drivers are generaly used at the development time to test your application’s.

Because of the disadvantages listed above it is not used at production time. But if we are not available with any other type of driver implementations for a DB then we are forced to use this type of driver (for example Microsoft Access).

Examples of this type of drivers

JdbcOdbcDriver from sun

Sun’s JdbcOdbcDriver is one of type-1 drivers and comes along with sun j2sdk (JDK).

  

Setting environment to use this driver

1. Software
   ODBC libraries has to be installed.
2. classpath 
   No additional classpath settings are required apart from the runtime jar (c:\j2sdk1.4\jre\lib\rt.jar) which is defaultly configured.
3. Path
   No additional path configuration is required.

How to use this driver

1. Driver class name              :            sun.jdbc.odbc.JdbcOdbcDriver
2. Driver URL                          :            jdbc:odbc:<DSN>
   here <DSN> (Data Source Name) is an ODBC datasource name which is used by ODBC driver to locate one of the ODBC Service Provider implementation API which can in-turn connect to DB.

Steps to create <DSN>

run ‘Data Sources (ODBC)’ from Control Panal\Administrative Tools\ (for Windows 2000 server/2000 professional/XP)

  run ‘ODBC Data Sources’ from Control Panel\ (for Windows 98)

1. click on Add button available on the above displayed screen.
   this opens a new window titled ‘Create New Data Source’ which displays all the available DB’s lable DB’s ODBC drivers currently installed on your system.
2. Select the suitable driver and click on Finish
3. Give the required info to the driver (like username, service id etc)   

Type-2 (Java Native-API Driver)

Architecture

  

This driver converts the JDBC call given by the Java application to a DB specific native call (i.e. to C or C++) using JNI (Java Native Interface).

Advantages

·         Faster than the other types of drivers due to native library participation in socket programming

Disadvantage

·         DB spcifiic native client library has to be installed in the client machine.

·         Preferablly work in local network environment because network service name must be configured in client system

Where to use?

This type of drivers are suitable to be used in server side applications.

Not recommended to use with the applications using two tire model (i.e. client and database layer’s) because in this type of model client used to interact with DB using the driver and in such a situation the client system sould have the DB native library.

Examples of this type of drivers

1. OCI 8 (Oracle Call Interface) for Oracle implemented by Oracle Corporation.

Setting environment to use this driver

·         Software: Oracle client software has to be installed in client machine

·         classpath                     :         %ORACLE_HOME%\ora81\jdbc\lib\classes111.zip

·         path                             :         %ORACLE_HOME%\ora81\bin

How to use this driver

• Driver class name        :         oracle.jdbc.driver.OracleDriver

• Driver URL                   :         jdbc:oracle:oci8:@TNSName

Note: TNS Names of Oracle is available in Oracle installed folder %ORACLE_HOME%\Ora81\network\admin\tnsnames.ora

2. Weblogic Jdriver for Oracle implemented by BEA Weblogic:

Setting environment to use this driver

·         Oracle client software has to be installed in client machine

·         weblogicoic dll’s has to be set in the path

·         classpath                     :        d:\bea\weblogic700\server\lib\weblogic.jar

·         path                             :         %ORACLE_HOME%\ora81\bin;
                                                    d:\bea\weblogic700\server\bin\ <sub folder>
<sub folder> is

o    oci817_8 if you are using Oracle 8.1.x

o    oci901_8 for Oracle 9.0.x

o    oci920_8 for Oracle 9.2.x

How to use this driver

• Driver class name        :         weblogic.jdbc.oci.Driver

• Driver URL                   :         jdbc:weblogic:oracle:HostName

Type-3 (Java Net Protocol Driver)

Architecture

This type of drivers responsibility is to convert JDBC call to Net protocol (Middleware listener dependent) format and redirect the client request to Middleware Listener and middleware listener inturn uses type-1, type-2 or type-4 driver to interact with DB.

Advantages:

• In absence of DB vendor supplied driver we can use this driver
• Suitable for Applet clients to connect DB, because it uses Java libraries for communication between client and server.

Disadvantages:

• From client to server communication this driver uses Java libraries, but from server to DB connectivity this driver uses native libraries, hence number of API conversion and layer of interactions increases to perform operations that leads to performance deficit.
• Third party vendor dependent and this driver may not provide  suitable driver for all DBs

Where to use?

• Suitable for Applets when connecting to databases

Examples of this type of drivers:

1. IDS Server (Intersolv) driver available for most of the Databases

Setting environment to use this driver

·         Software: IDS software required to be downloaded from the following URL

[ http://www.idssoftware.com/idsserver.html -> Export Evaluation ]

·         classpath                     :         C:\IDSServer\classes\jdk14drv.jar

·         path                             :        

How to use this driver

• Driver class name        :         ids.sql.IDSDriver

• Driver URL                   :         jdbc:ids://localhost:9012/conn?dsn='IDSExamples'

Note: DSN Name must be created in ServerDSN

Type-4 (Java Native Protocol driver)

Architecture

This type of driver converts the JDBC call to a DB defined native protocol.

Advantage

• No client native libraries required to be installed in client machine
• Comes with most of the Databases

Disadvantages:

• Slower in execution compared with other JDBC Driver due to Java libraries are used in socket communication with the DB

Where to use?

• This type of drivers are sutable to be used with server side applications, client side application and Java Applets also.

Examples of this type of drivers

1) Thin driver for Oracle implemented by Oracle Corporation
Setting environment to use this driver

·         classpath                     :         %ORACLE_HOME%\ora81\jdbc\lib\classes111.zip

How to use this driver

• Driver class name        :         oracle.jdbc.driver.OracleDriver

• Driver URL                   :         jdbc:oracle:thin:@HostName:<port no>:<SID>

<port no>  : 1521(default)

<SID> -> ORCL or XE
<HostName> -> localhost(127.0.0.1) or other system IP address where the oracle is running

2) MySQL Jconnector for MySQL database

Setting environment to use this driver

classpath        :        C:\mysql\mysql-connector-java-3.0.8-stable\mysql-connector-java-3.0.8-stable-bin.jar

How to use this driver

• Driver class name        :         com.mysql.jdbc.Driver

• Driver URL                   :         jdbc:mysql:///test, here test is the default schema name.

JDBC API comes in 2 packages

java.sql.*

javax.sql.*

 

Interfaces index

Driver

Every JDBC Driver vendor must one sub class of this class for initial establishment of Connections. DriverManager class need to be first registered with this class before accepting URL and other information for getting DB connection.

Method index

• Connection connect(String url, Properties info)
  This method takes URL argument and user name & password info as Properties object
• boolean acceptURL(String url)
  This method returns boolean value true if the given URL is correct, false if any wrong in URL
• boolean jdbcComplaint()
  JDBC compliance requires full support for the JDBC API and full support for SQL 92 Entry Level. It is expected that JDBC compliant drivers will be available for all the major commercial databases.

Connection

Connection is class in-turn holds the TCP/IP connection with DB. Functions available in this class are used to manage connection live-ness as long as JDBC application wants to connect with DB. The period for how long the connection exists is called as Session. This class also provides functions to execute various SQL statements on the DB. For instance the operations for DB are mainly divided into 3 types

• DDL (create, alter, and drop)
• DML (insert, select, update and delete)
• DCL (commit, rollback)

and also

• call function_name (or) call procedure_name

Method Index

• Statement createStatement()
• PreparedStatement prepareStatement(String preSqlOperation)
• CallableStatement prepareCall(String callToProc())

Statement

Statement class is the super class in its hierarchy. Provides basic functions to execute query (select) and non-related (create, alter, drop, insert, update, delete) query operations.

Method Index

• int executeUpdate(String sql)

This function accepts non-query based SQL operations; the return value int tells that how many number of rows effected/updated by the given SQL operation.

• ResultSet executeQuery(String sql)

This function accepts SQL statement SELECT and returns java buffer object which contains temporary instance of SQL structure maintaining all the records retrieved from the DB. This object exists as long as DB connection exist.

• boolean execute()

This function accepts all SQL operations including SELECT statement also.

PreparedStatement

PreparedStatement class is sub classing from Statement class. While connection class prepareStatement function is creating one new instance this class, function takes one String argument that contains basic syntax of SQL operation represented with “?” for IN parameter representation. In the further stages of the JDBC program, programmer uses setXXX(int index, datatype identifier) to pass values into IN parameter and requests exdcute()/ exuecteUpdate() call.

Method Index

• setInt(int index, int value) – similar functions are provided for all other primitive parameters
• setString(int index, String value)
• setObject(int index, Object value)
• setBinaryStream(int index, InputStream is, int length)

CallableStatement

ResultSet

ResultSetMetaData

DatabaseMetaData

BLOB

CLOB
REF
SavePoint

Struct

SQLInput

SQLOutput

SQLData

Java Sample Examples for JDBC connectivity using different driver

// TypeIDriverTest,java
package com.gaurav;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;


public class Type1DriverTest
{
    Connection con;
    Statement stmt;
    ResultSet rs;
   public Type1DriverTest ()
   {
      try {
// Load driver class into default ClassLoader
   Class.forName ("sun.jdbc.odbc.JdbcOdbcDriver");
         
// Obtain a connection with the loaded driver
   con = DriverManager.getConnection ("jdbc:odbc:empDsn","scott","tiger");
       
// create a statement   
   stmt = con.createStatement();

//execute SQL query
  rs = stmt.executeQuery ("select ename, sal from employee");

  System.out.println ("Name                       Salary");
  System.out.println ("--------------------------------");
  while(rs.next())
   {
    System.out.println (rs.getString(1)+"                "+rs.getString(2));
   }
    rs.close ();
    stmt.close ();     
    con.close ();
  }
   catch(Exception e)
    {
       e.printStackTrace ();
     }
          }
    public static void main (String args[])
    {
        Type1DriverTest t = new Type1DriverTest ();
        }
     }

Type 2 Driver Example

// TypeIIDriverTest,java
package com.gaurav;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class Type2DriverTest {
    Connection con;
    Statement stmt;
    ResultSet rs;

    public Type2DriverTest() {
       
        try {
            // Load the JDBC driver
            String driverName = "oracle.jdbc.driver.OracleDriver";
            Class.forName(driverName);

            // Create a connection to the database using required values
           
            String serverName = "127.0.0.1"; //localhost
            String portNumber = "1521";
            String sid = "XE";
            String url = "jdbc:oracle:thin:@" + serverName + ":" + portNumber + ":" + sid;
            String username = "username";
            String password = "password";
           
         // Obtain a connection with the loaded driver
            con = DriverManager.getConnection(url, username, password);
       
            /* We can directly pass the values as below */
            //con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","username","password");

            // create a statement
            stmt = con.createStatement();

            // execute SQL query
            rs = stmt.executeQuery("select ename,sal from employee");

            System.out.println("Name                       Salary");
            System.out.println("--------------------------------");
            while (rs.next()) {
                System.out.println(rs.getString(1) + "                "
                        + rs.getString(2));
            }
            rs.close();
            stmt.close();
            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void main(String args[]) {
        Type2DriverTest t = new Type2DriverTest();
    }
}