prashanthi
DESCRIPTION
Topic: JDBC DriversTRANSCRIPT
JDBC DRIVERS
1. ODBC
2. JDBC
3. JDBCAPI
4. JDBC DRIVERS
5. JDBC STEPS
ODBC
• Open DataBase Connectivity, Standard Database Access a method developed by the SQL Access group in 1992.
• The goal of ODBC is to make it possible to access any data from any application.
• ODBC manages this by inserting a middle layer, called a database driver , between an application and the DBMS.
Home
JDBC• JDBC technology allows you to use the Java to
exploit "Write Once, Run Anywhere“ capabilities.• The JDBC API makes it possible to do 3 things: 1.Establish a connection2.Send SQL statements 3.Process the results • The JDBC API is the database-independent
connectivity between the Java and a wide range of databases.
Home
JDBC API
• The JDBC API provides universal data access from the Java programming language.
• JDBC technology also provides a common base on which tools & alternate interfaces can be built.
• The JDBC 3.0 API is comprised of two packages:
java.sql package javax.sql package
Home
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• Ideal: can change database engines
without changing any application code
JDBC Drivers
• Type I: “Bridge”• Type II: “Native”• Type III: “Middleware”• Type IV: “Pure”
Home
JDBC-ODBC Bridge driver
• The Type 1 driver translates all JDBC calls into ODBC calls and sends them to the ODBC driver. ODBC is a generic API.
• The JDBC-ODBC Bridge driver is recommended only for experimental use or when no other alternative is available.
• The Type 1 driver translates all JDBC calls into ODBC calls and sends them to the ODBC driver. ODBC is a generic API.
• The JDBC-ODBC Bridge driver is recommended only for experimental use or when no other alternative is available.
Type 1: JDBC-ODBC Bridge
Advantage & Disadvantages of
type1 driver1. The JDBC-
ODBC Bridge allows access to almost any database,
2. since the database’s ODBC drivers are already available.
1. Type 1 drivers are not portable.
2. A performance is not gud.
3. The client system requires the ODBC Installation to use the driver.
4. Not good for the Web.
Native-API/partly Java driver
• The distinctive characteristic of type 2 jdbc drivers are that Type 2 drivers convert JDBC calls into database-specific calls i.e. this driver is specific to a particular database.
• Some distinctive characteristic of type 2 jdbc drivers are shown below.
• Ex: Oracle will have oracle native api.
Type 2: Native api/ Partly Java Driver
Advantage & Disadvantages of
type2 driver1.They typically
offer better performance than the JDBC-ODBC Bridge
2.The layers of communication (tiers) are less than that of Type1
3.Also it uses Native api which is Databas specific.
1.Native API must be installed in the Client System .
2.Does not solve portability issue.
3.If we change the Database we have to change the native api as it is specific to a database.
4.Usually not thread safe.
All Java/Net-protocol driver
• Calls middleware server, usually on database host.
• Type 3 database requests are passed through the network to the middle-tier server. The middle-tier then translates the request to the database.
Type 3: All Java/ Net-Protocol Driver
Advantage & Disadvantages of
type3 driver1. This driver is server-
based.2. It is Portable. 3. It is suitable for the
web.4. The net protocol can be
designed to make the client JDBC driver very small and fast to load.
5. It is very flexible .6. They are the most
efficient .
1. It requires another server application to install and maintain.
2. Traversing the recordset may take longer, since the data comes through the backend server.
Native-protocol/all-Java driver
• 100% Pure Java -- the Holy Grail.• The Type 4 uses java networking libraries to
communicate directly with the database server.
• This referred to as the Oracle “thin” driver. • This driver includes its own implementation
of a TCP/IP written entirely in Java, can be downloaded to a browser at runtime.
• This driver requires a TCP/IP listener on the server side.
Type 4: Native-protocol/all-Java driver
Advantage & Disadvantages of
type4 driver1. They are completely
written in Java.2. It is most suitable for
the web.3. Number of translation
layers is very less.4. Performance is typically
quite good. 5. These drivers can be
downloaded dynamically.
1. If you need to attach to various types of SQL databases, several JDBC drivers must be loaded onto the client machines.
JDBC Drivers
JDBC
Type I“Bridge”
Type II“Native”
Type III“Middleware”
Type IV“Pure”
ODBCODBCDriver
CLI (.lib)
MiddlewareServer
JDBC steps
1. Connect to database2. Query database (or
insert/update/delete)3. Process results4. Close connection to database
Home
JDBC Object Classes
• Driver Manager– 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
1. Connect to database
• Load JDBC driver Class.forName("com.mysql.jdbc.Driver").newInstance();
• Make connectionConnection conn = DriverManager.getConnection(url);
• URLFormat:
“jdbc:<subprotocol>:<subname>”jdbc:mysql://128.100.53.33/GROUPNUMBER?user=USER&password=PASSWORD
• DriverManager tries all the drivers• When a driver class is first loaded, it
registers itself with the DriverManage 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
Driver Manager:
2. Query databasea. Create statement
– Statement stmt = conn.createStatement();
– stmt object sends SQL commands to database
– Methods• executeQuery() for SELECT statements• executeUpdate() for INSERT, UPDATE,
DELETE, statementsb. Send SQL statements
– stmt.executeQuery(“SELECT …”);– stmt.executeUpdate(“INSERT …”);
3. Process results• Result of a SELECT statement (rows/columns)
returned as a ResultSet object– ResultSet rs =
stmt.executeQuery("SELECT * FROM users");
• Step through each row in the result– rs.next()
• Get column values in a row– String userid = rs.getString(“userid”);– int type = rs.getInt(“type”);
users tableuserid firstna
melastname
password
type
Bob Bob King cat 0
John John Smith pass 1
Statement Methods
ResultSet executeQuery(String) – Execute a SQL statement that returns a single
ResultSet.
int executeUpdate(String) – Execute a SQL INSERT, UPDATE or DELETE
statement. Returns the number of rows changed.
boolean execute(String) – Execute a SQL statement that may return
multiple results.
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.
• The table rows are retrieved in sequence.
• The 'next' method moves the cursor to the next row. - you can’t rewind
ResultSet Methods
• boolean next() – activates the next row– the first call to next() activates the first row– returns false if there are no more rows
• void close() – disposes of the ResultSet– allows you to re-use the Statement that
created it– automatically called by most Statement
methods.
Print the users tableResultSet rs = stmt.executeQuery("SELECT * FROM users");
while (rs.next()) {String userid = rs.getString(1);String firstname = rs.getString(“firstname”);String lastname = rs.getString(“lastname”);String password = rs.getString(4);int type = rs.getInt(“type”);System.out.println(userid + ” ” + firstname + ” ” + lastname + ” ” + password + ” ” + type);
}
users tableuserid firstna
melastname
password
type
Bob Bob King cat 0
John John Smith pass 1
4. Close connection to database
• Close the ResultSet object– rs.close();
• Close the Statement object– stmt.close();
• Close the connection– conn.close();
import java.sql.*;
public class Tester {
public static void main(String[] args) {
try {
// Load JDBC driver
Class.forName("com.mysql.jdbc.Driver").newInstance();
// Make connection
String url =“jdbc:mysql://128.100.53.33/GRP?user=USER&password=PASS”
Connection conn = DriverManager.getConnection(url);
// Create statementStatement stmt = conn.createStatement();
// Print the users tableResultSet rs =
stmt.executeQuery("SELECT * FROM users");while (rs.next()) {
...}
// Cleanuprs.close(); stmt.close(); conn.close();
} catch (Exception e) { System.out.println("exception " + e);
} }
ANY QUERIES ?
Thank you