My Profile
Active Members
TodayLast 7 Days
more...
Awards & Gifts
Online Exams
Fresher Jobs
Our fresher job section is exclusively for fresh graduates! Find jobs for freshers in major Indian
cities including Bangalore, Chennai, Hyderabad, Pune or Kochi
Resources
Find educational articles, blogs, discussion threads and other resources.
Colleges
Find details about any college in India or search for courses.
|
Resources » Articles/Knowledge Sharing » General »
JDBC Drivers
|
JDBC
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.
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.
Advantages
o Single API (Protocol) is used to interact with any DB
o Switching from one DB to another is easy
o 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.
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 3rd 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 3rd party vendor or DB vendor implemented Drivers are classified into 4-Types namely
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.
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
o More number of layers between the application and DB. And more number of API conversions leads to the downfall of the performance.
o 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 à dbc:odbc:
here (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
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
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.
o 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
o Software: Oracle client software has to be installed in client machine
o classpath à %ORACLE_HOME%\ora81\jdbc\lib\classes111.zip
o 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
o Oracle client software has to be installed in client machine
o weblogicoic dll’s has to be set in the path
o classpath à d:\bea\weblogic700\server\lib\weblogic.jar
o path à %ORACLE_HOME%\ora81\bin;
d:\bea\weblogic700\server\bin\ 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 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 :
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
o Software: IDS software required to be downloaded from the following URL
[ http://www.idssoftware.com/idsserver.html -> Export Evaluation ]
o classpath à C:\IDSServer\classes\jdk14drv.jar
o path à
How to use this driver
* Driver class name à ids.sql.IDSDriver
* 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:
This type of driver converts the JDBC call to a DB defined native protocol.
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
o 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::
à 1521
-> ORCL
2) MySQL Jconnector for MySQL database
Setting environment to use this driver
o 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
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
java.sql.*
javax.sql.*
First of all I want to discuss briefly about all the list of interfaces and classes available in java.sql. 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 com.digitalbook.j2ee.jdbc;
import java.sql.*;
public class TypeIDriverTest
{
Connection con;
Statement stmt;
ResultSet rs;
public TypeIDriverTest ()
{
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:digitalbook","scott","tiger");
URL String - ("::", " ", " " ); }
// create a statement
st=con.createStatement();
//execute SQL query
rs =st.executeQuery ("select ename,sal from emp");
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[])
{
TypeIDriverTest demo=new TypeIDriverTest ();
}
}
// TypeIIDriverTest,java
package com.digitalbook.j2ee.jdbc;
import java.sql.*;
public class TypeIIDriverTest
{
Connection con;
Statement stmt;
ResultSet rs;
public TypeIIDriverTest ()
{
try {
// Load driver class into default ClassLoader
Class.forName ("oracle.jdbc.driver.OracleDriver");
// Obtain a connection with the loaded driver
con =DriverManager.getConnection ("jdbc:oracle:oci8:@digital","scott","tiger");
// create a statement
st=con.createStatement();
//execute SQL query
rs =st.executeQuery ("select ename,sal from emp");
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[])
{
TypeIIDriverTest demo=new TypeIIDriverTest ();
}
}
|
|
Responses to the resource: "JDBC Drivers "
|
No responses found. Be the first to respond and make money from revenue sharing program.
|
|
|