SyntaxHighlighter.all();
Need for JDBC
Using vendor specific API in an application for performing database operations has following cons :-
1) Application developer need to learn different API for each database.
2) Each time database is changed application need to be modified.
Solution of this problem was provided by ODBC. ODBC is a set of ‘C’ function prototypes which are implemented by database vendors. Application programmers use ODBC functions to write connectivity code.
For Java programmers JDBC introduced so vendor gave classes for implementation in Java. Different vendors provide implementation of JDBC interfaces in different ways.
Depending upon vendor implementation we have 4 types of JDBC drivers :-
1) Type 1 or JDBC-ODBC Bridge Driver.
2) Type 2 or Native Driver.
3) Type 3 or Network Driver.
4) Type 4 or Pure Java Native Driver.
1) Type 1 or JDBC-ODBC Bridge Driver is a Java wrapper of ODBC driver i.e in this driver implementation classes of JDBC interfaces provided by vendor invokes ODBC functions using JNI.
For ex:-
public void update(String query)
{
calls C-function -> void insert() { c code}
}
Advantages
a) This is simplest driver from implementation perspective.
b) Single type-1 JDBC driver can be used to connect an application to any database i.e. different implementation are not required for different databases.
Disadvantages
a) ODBC driver need to be installed on each machine on which application is executing.
b) Degraded performance is obtained because each database operation results in multiple calls and conversions.
2) Type 2 or Native Driver
In Type 2 JDBC driver implementation classes provided by vendor invokes functions of native driver using JNI.
For ex –
public void update(String q)
{
submitRecord(); -> submitRecord() {.. } //Oracle
}
public void newupdate(String q)
{
insertRecord(); -> insertRecord() {.. } //Mysql
}
Advantages
a) ODBC driver is not required.
b) Better performance is obtained as compared to Type-1 JDBC driver.
Disadvantage
a) Native driver need to be installed on each machine on which application is executing.
b) For each database different driver implementation is required.
3) Type 3 or Network Driver
This is modified version of Type2 driver in which implementation classes provided by vendor invokes functions of native driver using Standard Networking Protocol such as TCP/IP.
Advantages
a) Type 2 driver need to be installed only on a single machine on the network.
Disadvantages
a) Additional networking support is required.
b) For each database different implementation of interfaces is required.
4) Type 4 or Pure Java Native Driver
In this driver implementation driver classes directly communicate to the database using database specific protocol.
Advantage
a) ODBC and native driver is not required.
b) Better performance is obtained as compared to other driver.
Disadvantage
a) Different implementation classes are required for each database.
java.sql package
Contains classes and interfaces which represent JDBC API’s.
Commonly used classes and interfaces are
1) Driver Manager 5) Callable Statement 9) SQL Exception
2) Connection 6) Result Set
3) Statement 7) ResultSet MetaData
4) Prepared Statement 8) Database MetaData
1) Driver Manager – is a utility class that act as a factory of connection. This interface represents the functionality of database connection and act as a factory of statements.
3) Statement :- Is responsible for executing query over a database connection and act as a factory of ResultSet.
4) Prepared Statement :- provides the facility of executing parameterized queries.
5) Callable Statement :- provides the facility of executing stored procedures and functions.
6) ResultSet – is used to hold the records returned by SELECT query and act as a factory of ResultSetMetadata.
7) ResultSetMetaData – provides methods for obtaining information about the records contained in ResultSet.
8) DatabaseMetaData – provides methods for obtaining information about the database.
9) SQL Exception – is the super class of all database related exceptions.
Connection (Java Interface)
OracleConnection SQLConnection (implementation of vendor)
Statement(Java Interface)
OracleStatement SQLStatement (implementation of vendor)
class A implement X { }
class B implement X { }
class XFactory
{
public static X getX()
{
return new A();
}
}
X a = XFactory.getX(); // no need to know the class given by the vendor
interface Connection
{
public Statement createStatement();
// factory method to create Statement object
}
Implementation by X vendor
public class XConnection implements Connection
{
public Statement createStatement()
{
return new XStatement();
}
}
public class YConnection implements Connection
{
public Statement createStatement()
{
return new YStatement();
}
}
Let ‘con’ be an object of type XConnection
Statement stmt = con.createStatement();
Vendor is asked to create object of Statement in Connection class similarly in Statement class he is asked to return object of ResultSet.
So,
Connection made factory of Statement
Statement made factory of ResultSet
ResultSet made factory of ResultSetMetaData
DriverManager makes object of Connection.
Steps to connect a Java Application to a Database
1) Register the driver class with the driver manager. Each driver implementation contains a class that exposes factory methods which are used by the driver manager for creating connection object. This class need to be registered with the driver manager. All driver classes contains registration code in their static block hence in order to register the class its simply need to loaded.
In Type-1 JDBC driver- sun.jdbc.odbc.JdbcOdbcDriver is to be loaded.
Class.forName(sun.jdbc.odbc.JdbcOdbcDriver)
2) Create a connection object.
getConnection() factory method of DriverManager class is used for creating connections.
Connection con = DriverManager.getConnection(“jdbc:odbc:mydb”)
3) Create a statement object
createStatement() :- factory method of connection interface is used to create Statement object.
Statement stmt = con.createStatement()
4) Execute the query
Statement interface provides following methods for executing queries :-
public ResultSet executeQuery(String selectQuery) throws SQLException;
public void execute(String NonDMLQuery) throws SQLException;
If Select query is executed fetch the records from ResultSet. Fetching records from ResultSet is a 2 step process-
a) Record pointer is placed on the desired record.
b) Value of the individual fields of current record is read.
public boolean next() :- method of ResultSet is used to advance the record pointer by one Record.
5) Close the connection
public void close() throws SQLException
Sample program for JDBC connectivity
import java.sql.*;
class SelectTest
{
public static void main(String[] args)
{
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con = DriverManager.getConnection(jdbc:odbc:mydb, "system", "oracle");
Statement stmt = con.createStatement();
ResultSet rset = stmt.executeQuery("Select * from emp");
System.out.println("Following records are fetched -");
while(rset.next())
{
System.out.println(rset.getInt(1) + "\t" + rset.getString(2) + "\t" +
rset.getString(3));
}
con.close();
}
catch(Exception e)
{ System.out.println(e) }
}
}
learnersquest 