A JDBC driver is a software component enabling a Java application to interact with a database. JDBC drivers are analogous to ODBC drivers, ADO.NET data providers, and OLE DB providers.

To connect with individual databases, JDBC (the Java Database Connectivity API) requires drivers for each database. The JDBC driver gives out the connection to the database and implements the protocol for transferring the query and result between client and database.

Think of a database as just another device connected to your computer

like other devices it has a driver program to relieves you of having to do low level programming to use the database

the driver provides you with a high level api to the database

Open Data Base Connectivity Developed by Microsoft for the Windows platform

as the way for Windows applications to access Microsoft databases (SQL Server, FoxPro, Access)

Has become an industry standard Most data base vendors supply native, odbc, and

jdbc drivers for their data base products

Java Application

JDBC API

Data Base Drivers

Access SQL

Server

DB2 Informix MySQL Sybase

Type 1◦ JDBC-ODBC Bridge

Type 2◦ Native API, partially java

Type 3◦ JDBC Network Driver, partially java

Type 4◦ 100% Java

Translate JDBC into ODBC and use Windows ODBC built in drivers

ODBC must be set up on every client ◦ driver must be physically on each machine for both java

applications and applets◦ for server side servlets ODBC must be set up on web

server driver sun.jdbc.odbc.JdbcOdbc provided by

JavaSoft with JDK

Functions◦ Translates a query by JDBC into a corresponding

ODBC query, which is then handled by the ODBC driver.

Advantages◦ Almost any database for which ODBC driver is

installed, can be accessed. Disadvantages◦ Performance overhead since the calls have to go

through the jdbc Overhead bridge to the ODBC driver, then to the native db connectivity interface (thus may be slower than other types of drivers).◦ The ODBC driver needs to be installed on the

client machine.◦ Not suitable for applets, because the ODBC

driver needs to be installed on the client

Client JDBC-ODBC

ODBC DB

Vendor Library

Converts JDBC to data base vendors native SQL calls

like Type 1 drivers; requires installation of binaries on each client

The JDBC type 2 driver, also known as the Native-API driver, is a database driver implementation that uses the client-side libraries of the database. The driver converts JDBC method calls into native calls of the database API.

Advantages◦ As there is no implementation of jdbc-odbc

bridge, its considerably faster than a type 1 driver.

Disadvantages◦ The vendor client library needs to be installed on

the client machine.◦ Not all databases have a client side library◦ This driver is platform dependent◦ This driver supports all java applications except

Applets

Client JDBC

Vendor Library

DB

Translates JDBC to a DBMS independent network protocol

Typically talks directly with a middleware product which in turn talks to the RDBMS◦ Jaguar, DBAnywhere, SequeLink

Most flexible driver type all java

Functions◦ Follows a three tier communication approach.◦ Can interface to multiple databases - Not vendor specific.◦ The JDBC Client driver written in java, communicates with a

middleware-net-server using a database independent protocol, and then this net server translates this request into database commands for that database.

◦ Thus the client driver to middleware communication is database independent.

Advantages◦ Since the communication between client and the middleware server is

database independent, there is no need for the database vendor library on the client. The client need not be changed for a new database.

◦ The middleware server (which can be a full fledged J2EE Application server) can provide typical middleware services like caching (of connections, query results, etc.), load balancing, logging, and auditing.

◦ A single driver can handle any database, provided the middleware supports it.

◦ Eg:-IDA Server Disadvantages◦ Requires database-specific coding to be done in the middle tier.◦ The middleware layer added may result in additional latency, but is

typically overcome by using better middleware services.

Client JDBC Vendor Middleware

DB

Tier 1 Tier 2 Tier 3

Converts JDBC directly to native API used by the RDBMS

compiles into the application , applet or servlet; doesn’t require anything to be installed on client machine, except JVM

handiest driver type

Written completely in Java, type 4 drivers are thus platform independent. They install inside the Java Virtual Machine of the client. This provides better performance than the type 1 and type 2 drivers as it does not have the overhead of conversion of calls into ODBC or database API calls. Unlike the type 3 drivers, it does not need associated software to work.

As the database protocol is vendor specific, the JDBC client requires separate drivers, usually vendor supplied, to connect to different types of databases. This type includes, for example, the widely-used Oracle thin driver.

Advantages Completely implemented in Java to achieve platform independence. These drivers don't translate the requests into an intermediary

format (such as ODBC). The client application connects directly to the database server. No

translation or middleware layers are used, improving performance. The JVM can manage all aspects of the application-to-database

connection; this can facilitate debugging. Provides a way to manage copies of the database for each user. Disadvantages Drivers are database dependent, as different database vendors use

wildly different (and usually proprietary) network protocols.

Client JDBC DB

Developed by C. Date for Relational Data Base Management Systems (RDBMS)

Simple Declarative Language◦ has no program control statements

Two categories of commands◦ Data Manipulation Commands

deal with: RETRIEVING DATA MAINTAINING DATA (ADDING, UPDATING,DELETING

◦ Data Definition Commands DEAL WITH:

CREATING DATABASE OBJECTS (TABLES,VIEWS) Object organization and attributes

Rules to insure that table data stays accurate and accessible◦ rows in a table should be unique

one column should contain no duplicate data primary key◦ column values cannot contain repeating groups or arrays◦ null is different than space and zero, 2 null values are not

considered equal

Select - query and display data from a database Insert - a new row into a table Update - modify a column in a table Delete a row from a table

Select (column list) from (sources) where (conditions) order by (ocolumn list) column list - comma separated list of names of columns to be in output◦ Ex. ssn , lastname , firstname,gpa◦ can contain literals to be included in output

Ex. Ssn,”~”,lastname,”~”,firstname sources - name(s) of table(s) to retrieve data from conditions (optional) - conditions for selections ◦ lastname like “S%”◦ (lastname = “Steflik) and (firstname like “R%”)

ocolumn list (optional)- list of columns that output should be ordered by Ex◦ select * from student◦ select lastname,fristname from student where gpa > 3.0◦ select lastname,firstname from student order by lastname,firstname

Two tables may be joined and viewed as a single data source is the both have a common column

suppose we have 2 tables: Inventory and category and each has a column called catg_code

In category catg_code is unique and is the primary key

P_no Catg_code qty Catg_code descr

Inventory Category

join

To retrieve all of the part numbers and the name of the category to which the part belongs:◦ select inventory.p_no category.descr from inventroy , category◦ select a.p_no,” “,b.descr from a inventory , b category

Add a row to a table◦ insert into category values(“IGN”,”Ignition System”)

use jdbc execute Update method◦ ex. Stmt.executeUpdate(“insert into Category values(‘IGN’,’Ignition System’)”);

Modify an existing row in a table◦ update category set descr = “Ignition Subsystem” where catg_code = “IGN”

Use jdbc executeUpdate method ◦ String s = “update category set descr = ‘Ignition Subsystem’ where catg_code =

‘IGN’ ”;◦ stmt.executeUpdate(s);

Remove a row from a table◦ delete from category where catg_code = “IGN”

remove the IGN category◦ delete from category where catg_code like “I%”

remove all rows where catg_code starts with an “I”

Create table◦ add a table to a database

Drop Table◦ remove a table from a database

Alter Table◦ add or delete column(s)