Transcript document

JDBC
( JAVA DATABASE CONNECTIVITY)
Prepared by :
Raithatha Yash
Roll No : 6540
What is JDBC ?
 A java API that provides cross DBMS
connectivity to a wide varity of SQL
Databases
 It contains various java classes & interfaces
which can be implemented by vendors of
different DBMS
 It is included in J2SE & J2EE both
Yash Raithatha ,6540
History
 Microsoft ODBC
 ODBC uses CLI specifications from SQL
Access group
 ODBC in C so no Platform independency
 Sun decided to make such an API through
JAVA code
Yash Raithatha ,6540
JDBC Driver
 The JDBC Driver provides vendor-specific
implementations of the abstract classes &
interfaces provided by the JDBC API.
 Used to connect to the database.
 To connect different DBMS we need to have
the corresponding JDBC driver which is
generally provided by the vendor of DBMS
Yash Raithatha ,6540
Yash Raithatha ,6540
JDBC Architecture
Application
JDBC
Driver




Java code calls JDBC library
JDBC loads a driver
Driver talks to a particular database
Can have more than one driver -> more than one
database
 Best : can change database engines without
changing any application code
Yash Raithatha ,6540
JDBC Drivers
 Type I: “Bridge”
 Type II: “Native”
 Type III: “Middleware”
 Type IV: “Pure”
Yash Raithatha ,6540
Type I Drivers
i.e Bridge
 Converts JDBC calls into ODBC function calls
which in turn communicates with the ODBC
driver
 ODBC driver manipulates the database
JDBC
ODBC
ODBC
Driver
Yash Raithatha ,6540
Data
Source
Advantages & disadvantages
Advantages
if JDBC driver for any DBMS is not available
easy to install
Disadvantages
ODBC driver needs to be installed on client
Application will become platform
dependent as ODBC driver is bound with
machine
Performance overhead
Yash Raithatha ,6540
Type II Drivers i.e Native
 Converts JDBC calls into native Database API
calls using CLI library
 Not written entirely in java
 Usually not thread-safe
 Mostly obsolete now
 e.g. Intersolv Oracle Driver, WebLogic drivers
JDBC
Native DB
API
Data
source
Yash Raithatha ,6540
Advantages & disadvantages
Advantages
Better Performance than type 1 driver
Disadvantages
No Platform independency
 Vendor client library needs to be installed on
the client
Yash Raithatha ,6540
Type III Drivers i.e.
Middleware
 Convert JDBC calls into server protocol which
in turn converts them to DBMS specific
protocol
 Written entirely in java
 Best :Only need to download one driver to
support multiple database
e.g. Symantec DBAnywhere
Yash Raithatha ,6540
MiddleWare
DS1
JDBC
Middleware
Server
DS 2
DS 3
Yash Raithatha ,6540
Advantages & disadvantages
Advantages
No need for vendor db library on client
machine
middle ware services like caching , loading
,auditing , etc
Disadvantage
database specific coding must be done in
middle ware server
Yash Raithatha ,6540
Type IV Drivers
 100% Pure Java -- the Holy Grail
 Use Java networking libraries to talk directly
to database engines
 Only disadvantage: need to download a new
driver for each database engine
 e.g. Oracle, mSQL
JDBC
Data Source
Yash Raithatha ,6540
Yash Raithatha ,6540
JDBC Drivers (Fig.)
Type I
“Bridge”
JDBC
Type II
“Native”
Type III
“Middleware”
Type IV
“Pure”
ODBC
ODBC
Driver
CLI (.lib)
Middleware
Server
JDBC
URLs
jdbc:subprotocol:source
 each driver has its own subprotocol
 each subprotocol has its own syntax for the
source
jdbc:odbc:DataSource
 e.g. jdbc:odbc:Northwind
jdbc:msql://host[:port]/database
 e.g.
jdbc:msql://foo.nowhere.com:4333/accounting
Yash Raithatha ,6540
java.sql
 JDBC is implemented via classes & interfaces
in the java.sql package
Yash Raithatha ,6540
JDBC Object Classes
 DriverManager
 Loads, chooses drivers
 Driver
 connects to actual database
 Connection
 a series of SQL statements to and from the DB
 Statement
 a single SQL statement
 ResultSet
 the records returned from a Statement
Yash Raithatha ,6540
DriverManager
 DriverManager tries all the drivers
 Uses the first one that works
 When a driver class is first loaded, it registers
itself with the DriverManager
 Therefore, to register a driver, just load it!
Yash Raithatha ,6540
Driver
Driver d = new
foo.bar.MyDriver();
Connection c = d.connect(...);
 Not recommended, use DriverManager
instead
 Useful if you know you want a particular
driver
Yash Raithatha ,6540
Connection
 A Connection represents a session with a specific
database.
 Within the context of a Connection, SQL statements
are executed and results are returned.
 Can have multiple connections to a database
 Also provides “metadata” -- information about the
database, tables, and fields
 Also methods to deal with transactions
Yash Raithatha ,6540
Statement
 A Statement object is used for executing a
static SQL statement and obtaining the
results produced by it.
Yash Raithatha ,6540
ResultSet
 A ResultSet provides access to a table of data
generated by executing a Statement.
 Only one ResultSet per Statement can be open
at once.
 A ResultSet maintains a cursor pointing to its
current row of data.
 The 'next' method moves the cursor to the next
row.
Yash Raithatha ,6540
Example
 String DBDriver = "sun.jdbc.odbc.JdbcOdbcDriver";
 String url = "jdbc:odbc:;DRIVER=Microsoft Access
Driver (*.mdb);DBQ=c:/My Project/mydata.mdb";
 Connection conn;
Yash Raithatha ,6540
try{
Class.forName(DBDriver);
conn = DriverManager.getConnection(url);
}
catch(Exception e){
JOptionPane.showMessageDialog(null,
"Error in establishing Connection");
} // catch ends
Yash Raithatha ,6540
try{
String sql = “ Your SQL Query “;
PreparedStatement ps =
conn.prepareStatement(sql);
ResultSet rs = ps.executeQuery( );
Or
int rowsAffeted=ps.executeUpdate( );
}
Catch(Exception e){
}
Yash Raithatha ,6540
Yash Raithatha ,6540