Types Of Drivers :
1. Type-1 (JDBC ODBC-Bridge Driver) JDBC-ODBC Bridge Driver
2. Type-2 (Java-Native API Driver) Native API Partly JAVA Driver (Thick
Driver)
3. Type-3 (Java Net Protocol Driver) Intermediate DataBase Access Server
4. Type-4 (Java Native Protocol driver) Pure JAVA Driver (Thin driver)
Type-1 : JDBC-ODBC Bridge Driver :
Since ODBC is written in C-language using pointers, so JAVA does’t
support pointers, a java program can’t communate directly with the DataBase. The
JDBC-ODBC bridge drivertransulates JDBC API calls to ODBC API calls.
Architecture
JAVA JDBC Native DBMS
Application ODBC ODBC Interface
Driver Client driver client
Libraries libraries
DBMS
Interface
DBMS Server
Libraries
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\[Link]) which is defaultly configured.
3. Path
No additional path configuration is required.
How to use this driver
1. Driver class name [Link]
2. Driver URL dbc: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>
1. run ‘Data Sources (ODBC)’ from Control Panal\Administrative Tools\
(for Windows 2000 server/2000 professional/XP)
run ‘ODBC Data Sources’ from Control Panel\
2. 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.
3. Select the suitable driver and click on Finish
4. Give the required info to the driver (like username, service id etc)
Type-2 : Native API Partly JAVA Driver (Thick Driver) :
JDBC Database calls are
translated into Vendor-specific API calls. The
database will process the request and send the
results back through API to JDBC Driver – this will
translate the results to the JDBC standard and
return them to the Java application.
The Vendor specific language API must be
installed on every client that runs the JAVA
application.
Architecture
JDBC JDBC Type II DBMS Client
Application JDBC Driver SP libraries (native)
SP
N/W
DBMS Server
DBMS
OCI libraries (native)
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 programing.
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\[Link]
path %ORACLE_HOME%\ora81\bin
How to use this driver
Driver class name [Link]
Driver URL jdbc:oracle:oci8:@TNSName
Note: TNS Names of Oracle is available in Oracle installed folder %ORACLE_HOME%\
Ora81\network\admin\[Link]
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\[Link]
path %ORACLE_HOME%\ora81\bin;
d:\bea\weblogic700\server\bin\<subfolder><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 [Link]
Driver URL jdbc:weblogic:oracle:HostName
Type-3 Intermediate DataBase Access Server :
Type-3 Driver uses an Intermediate(middleware) database driver
that has the ability to connect multiple JAVA clients to multiple database servers.
Client connect to the Databse server via an Intermediate server component (such as
listener) that acts as a gateway for multple database servers.
Bea weblogic includes Type-3 Driver.
Architecture :
JDBC JDBC Type III Middleware
Application JDBC Driver Net Listener
DBMS Interface DBMS Interface
DBMS DBMS API
OCI Libraries Client
Server
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:
It allows the flexibility on the architecture of the application.
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
[ [Link] -> Export Evaluation ]
classpath C:\IDSServer\classes\[Link]
path
How to use this driver
Driver class name [Link]
Driver URL jdbc:ids://localhost:12/conn?dsn='IDSExamples'
Note: DSN Name must be created in ServerDSN
Type-4 Pure JAVA Driver (Thin driver) :
Type-4 Driver translates JDBC-API calls to direct network calls using
vendor specific networking protocols by making direct server connections with the
database.
Architecture
JDBC
JDBC Type IV DBMS Interface
Application JDBC Server Listener
Driver Native
DBMS
API
This type of driver converts the JDBC call to a DB defined native protocol. DBMS
Advantage
Type-4 driver are simple to deploy since there is 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\[Link]
How to use this driver
Driver class name [Link]
Driver URL jdbc:oracle:thin:@HostName:<port no>:<SID>
<port no> 1521
<SID> -> ORCL
2) MySQL Jconnector for MySQL database
Setting environment to use this driver
classpath C:\mysql\mysql-connector-java-3.0.8-stable\mysql-
[Link]
How to use this driver
Driver class name [Link]
Driver URL jdbc:mysql:///test
Chapter 3 [JDBC Core API]
In this chapter we are going to discuss about 3 versions of JDBC: JDBC 1.0, 2.0
and 3.0
Q) How JDBC API is common to all the Databases and also to all drivers?
A) Fine! The answer is JDBC API uses Factory Method and Abstract Factory Design pattern
implementations to make API common to all the Databases and Drivers. In fact most of
the classes available in JDBC API are interfaces, where Driver vendors must provide
implementation for the above said interfaces.
Q) Then how JDBC developer can remember or find out the syntaxes of vendor
specific classes?
A) No! developer need not have to find out the syntaxes of vendor specific
implementations why because DriverManager is one named class available in JDBC API
into which if you register Driver class name, URL, user and password, DriverManager
class in-turn brings us one Connection object.
Q) Why most of the classes given in JDBC API are interfaces?
A) Why abstract class and abstract methods are?
Abstract class forces all sub classes to implement common methods whichever are
required implementations. Only abstract method and class can do this job. That’s’ why
most part of the JDBC API is a formation of interfaces.
JDBC API comes in 2 packages
[Link].*
[Link].*
First of all I want to discuss briefly about all the list of interfaces and classes available in
[Link]. package
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
Class diagram required here
// TypeI DriverTest,java
package [Link];
import [Link].*;
public class TypeIDriverTest
{
Connection con;
Statement stmt;
ResultSet rs;
public TypeIDriverTest ()
{
try {
// Load driver class into default ClassLoader
[Link] ("[Link]");
// Obtain a connection with the loaded driver
con =[Link] ("jdbc:odbc:digitalbook","scott","tiger");
URL String - ("<protocol>:<subprotocol>:<subname>", " ", " " ); }
// create a statement
st=[Link]();
//execute SQL query
rs =[Link] ("select ename,sal from emp");
[Link] ("Name Salary");
[Link] ("--------------------------------");
while([Link]())
{
[Link] ([Link](1)+" "+[Link](2));
}
[Link] ();
[Link] ();
[Link] ();
}
catch(Exception e)
{
[Link] ();
}
}
public static void main (String args[])
{
TypeIDriverTest demo=new TypeIDriverTest ();
}
}
// TypeIIDriverTest,java
package [Link];
import [Link].*;
public class TypeIIDriverTest
{
Connection con;
Statement stmt;
ResultSet rs;
public TypeIIDriverTest ()
{
try {
// Load driver class into default ClassLoader
[Link] ("[Link]");
// Obtain a connection with the loaded driver
con =[Link]
("jdbc:oracle:oci8:@digital","scott","tiger");
// create a statement
st=[Link]();
//execute SQL query
rs =[Link] ("select ename,sal from emp");
[Link] ("Name Salary");
[Link] ("--------------------------------");
while([Link]())
{
[Link] ([Link](1)+" "+[Link](2));
}
[Link] ();
[Link] ();
[Link] ();
}
catch(Exception e)
{
[Link] ();
}
}
public static void main (String args[])
{
TypeIIDriverTest demo=new TypeIIDriverTest ();
}
}
Chapter 9 : [[Link] package]
This package supplements the [Link] package and is included as a part of JDK 1.4
version. This package mainly provides following features:
1. DataSource interface was introduced in substitution to DriverManager class for
getting connection objects.
2. Connection Pooling
3. Distributed TX management
4. RowSets
Applications can directly use DataSource and RowSet API but connection pooling and
Distributed TX management APIs are used internally by the middle-tier infrastructure.
DataSource
DataSource is an interface. Driver vendor will provide implementation for this
interface (That means in case JDBC Driver Type II driver Oracle vendor for Oracle DB,
Intersolv in case of IDSServer). This object is used to obtain connections into any type of
JDBC program. Though DriverManager class is ideal for getting DB connection object, this
class provides some extra features over DriverManager class:
Applications will obtain DB connection objects through via this factory class
DataSource object will be registered into JNDI, hence any application connected in the
network can obtain this object by requesting JNDI API, DataSource class is having one
method called getConnection() geives one Connection object
Application do not need to hard code a driver class
Changes can be made to a data source properties, which means that it is not necessary to
make changes in application code when something about the data source or driver
changes
Connection pooling and Distributed transactions are available through only the connection
obtained from this object. Connection obtained through DriverManager class do not have
this capability
DataSource interface is implemented by driver vendor. There are 3 types of
implementations available:
1. Basic Implementation- Produces a standard connection object.
2. Connection Pooling Implementation- Produces a connection object that
automatically participates in connection pooling. This implementation works with
a middle-tier connection pooling manager.
3. Distributed transaction implementation- Produces a connection object that
may be used for distributed transactions and almost always participates in
connection pooling. This implementation works with a middle-tier transaction
manager and almost always with a connection pool manager.
A driver that is accessed via a DataSource object does not register itself with the
DriverManager. Rather, a DataSource object is retrieved though a lookup operation and
then used to create a Connection object. With a basic implementation, the connection
obtained through a DataSource object is identical to a connection obtained through the
DriverManager facility.
Method Index
Connection getConnection() – This function returns Connection object on demand
of this method.
Connection getConnection(String user, String pass) – This function returns
Connection object on demand of this method by passing username and password.
Sub classes of this interface are
Type III Driver – IDSServer – Intersolv – [Link]
Type III Driver – WebLogic – BEA – [Link] – XA Support
Connection Pooling
Connections made via a DataSource object that is implemented to work with a
middle tier connection pool manager will participate in connection pooling. This can
improve the performance dramatically because creating a new connection is very
expensive.
Connection Pool provides following features:
Substantial improvement in the performance of DB application can be
accomplished by pre-caching the DB connection objects
CPM supplied DB connections are remote enable
CPM supplied DB connections are cluster aware
CPM supplied DB connections supports DTM (distributed TXs)
CPM supplied DB connections are not actual DB Connection objects, in turn they
are remote object, hence even though client closes DB connection using
[Link]() the actual connection may not be closed instead RMI connection
between client to CPM are closed
CPM supplied DB connection objects are serializable, hence client from any where
in the network can access DB connections
The classes and interfaces used for connection pooling are:
1. ConnectionPoolDataSource
2. PooledConnection
3. ConnectionEvent
4. ConnectionEventListener
Connection Pool Manager resided on middle tier system uses these classes and
interfaces behind the scenes. When the ConnectionPooledDataSource object is called on
to create PooledConnection object, the connection pool manager will register as a
ConnectionEventListener object with the new PooledConnection object. When the
connection is closed or there is an error, the connection pool manager (being listener)
gets a notification that includes a ConnectionEvent object.