503111
JAVA TECHNOLOGY
CHAPTER 2: JDBC
1
JDBC –
Java DataBase Connectivity
08/08/2022 503111 – Chapter 2 - JDBC 2
JDBC API Overview
JDBC is Java API that allows the Java programmers to
access database management system from Java code.
The JDBC API makes it possible to do three things:
■ Establish a connection with a database or access any tabular data source
■ Send SQL statements
■ Process the results
To connect with individual databases, JDBC 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.
08/08/2022 503111 – Chapter 2 - JDBC 3
Types of Drivers
■ Type-1 JDBC-ODBC bridge
■ Type-2 Native API, part java driver
■ Type-3 Pure-java driver for database middleware
■ Type-4 Pure-java driver for direct-to-database
08/08/2022 503111 – Chapter 2 - JDBC 4
Type – 1 Driver
Functions
■ Translates query obtained by JDBC into corresponding
ODBC query, which is then handled by the ODBC
driver.
■ Sun provides a JDBC-ODBC Bridge driver.
[Link]. This driver is native
code and not Java.
■ Client -> JDBC Driver -> ODBC Driver -> Database
08/08/2022 503111 – Chapter 2 - JDBC 5
Type – 1 Driver
08/08/2022 503111 – Chapter 2 - JDBC 6
Type – 1 Driver
Advantages
■ Easy to connect.
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.
■ For prototyping only and not for production
■ The ODBC driver needs to be installed on the client machine.
■ Compared to other driver types it's slow.
■ This driver depends on the ODBC Drivers , and therefore , java
applications also become indirectly dependent on ODBC drivers.
08/08/2022 503111 – Chapter 2 - JDBC 7
Type – 2( Native API) Drivers
Function
■ The JDBC type 2 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
■ The type 2 driver is not written entirely in Java as it interfaces
with non-Java code that makes the final database callSs.
■ Client JDBC API Database specific Native APIs
08/08/2022 503111 – Chapter 2 - JDBC 8
Type – 2( Native API) Drivers
08/08/2022 503111 – Chapter 2 - JDBC 9
Type – 2( Native API) Drivers
Advantages:
■ This Driver helps in accessing the data faster as compared to Type-1 drivers
Disadvantages:
■ The vendor client library needs to be installed on the client
machine since the conversion from JDBC call to database specific
native call is done on the client machine.
■ Database specific native functions are executed on the client
machine and any bug in this driver can crash the JVM.
■ This driver is platform dependent .
08/08/2022 503111 – Chapter 2 - JDBC 10
Type – 3(Pure Java) Drivers
Also Named as Network Protocol Drivers
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.
■ Client -> JDBC Driver -> Network-protocol driver -> Middleware-
Net Server -> Any Database,...
08/08/2022 503111 – Chapter 2 - JDBC 11
Type – 3(Pure Java) Drivers
08/08/2022 503111 – Chapter 2 - JDBC 12
Type – 3(Pure Java) Drivers
Advantages:
■ Type-3 drivers are Pure Java Drivers so Platform independent.
■ We can switch over from one database to another without
changing the client-side driver classes, by just changing the
configuration of the middleware.
■ Easy deployment
Disadvantage:
■ Requires database-specific coding to be done in the middle tier
08/08/2022 503111 – Chapter 2 - JDBC 13
Type – 4(Pure Java) Drivers
Also Named as Native Protocol Drivers
Function:
■ Type 4 drivers, coded entirely in Java, communicate directly with
a vendor's database, usually through socket connections. No
translation or middleware layers are required, improving
performance.
■ The driver converts JDBC calls into the vendor-specific database
protocol so that client applications can communicate directly with
the database server.
■ Completely implemented in Java to achieve platform
independence.
■ Client -> Native-protocol JDBC Driver -> database server
08/08/2022 503111 – Chapter 2 - JDBC 14
Type – 4(Pure Java) Drivers
08/08/2022 503111 – Chapter 2 - JDBC 15
Type – 4(Pure Java) Drivers
Advantage:
■ These drivers don't translate the requests into an intermediary
format (such as ODBC), nor do they need a middleware layer to
service requests. This can enhance performance considerably.
■ The JVM can manage all aspects of the application-to-database
connection; this can facilitate debugging.
Disadvantage:
■ Drivers are database dependent.
08/08/2022 503111 – Chapter 2 - JDBC 16
JDBC Architecture
08/08/2022 503111 – Chapter 2 - JDBC 17
■ [Link] a connection
■ [Link] JDBC Statements
■ [Link] SQL Statements
■ [Link] ResultSet
■ [Link] connections
08/08/2022 503111 – Chapter 2 - JDBC 18
1. Establish a connection
■ Import JDBC Packages import [Link].*;
■ Register the Drivers:
■ You must register the your driver in your program before you use it.
■ IT is the process by which the Oracle driver's class file is loaded
into memory so it can be utilized as an implementation of the JDBC
interfaces.
■ Two approaches are used to register the database
■ [Link]("[Link]");
■ [Link]()
08/08/2022 503111 – Chapter 2 - JDBC 19
[Link]("[Link]");
■ The most common approach to register a driver is to use
Java's [Link]() method
■ This method is used to dynamically load the driver's
class file into memory, which automatically registers it.
try { [Link]("[Link]"); }
catch(ClassNotFoundException ex)
{ [Link]("Error: unable to load driver class!");
}
08/08/2022 503111 – Chapter 2 - JDBC 20
Approach (II) -
[Link]():
■ The second approach you can use to register a driver is
to use the static [Link]()
method.
try {
Driver myDriver = new [Link]();
[Link]( myDriver );
} catch(ClassNotFoundException ex)
{ [Link]("Error: unable to load driver class!"); }
08/08/2022 503111 – Chapter 2 - JDBC 21
2. Create JDBC statement(s)
■ Make the connection
■ Connection con =
[Link]( "jdbc:oracle:thin:@oracle-
prod:1521:OPROD", username, passwd);
■ What do you think this statement does?
■ Establishes connection to database by obtaining
a Connection object
■ Statement stmt = [Link]() ;
■ Creates a Statement object for sending SQL statements to the
database
08/08/2022 503111 – Chapter 2 - JDBC 22
Prepared Statements
■ Prepared statements are pre-compiled SQL statements. Precompiled
SQL is useful if the same SQL is to be executed repeatedly, for example,
in a loop.
■ The syntax is straightforward: just insert question marks for any
parameters that you'll be substituting later on before you send the SQL
to the database.
Syantax:
■ PreparedStatement pstmt = [Link]("update Orders set pname = ? where
Prod_Id = ?");
[Link](1, "Bob"); [Link](2, 100);
[Link]();
■ Prepared Statements are also useful in preventing SQL injection
Hacking.
08/08/2022 503111 – Chapter 2 - JDBC 23
Callable Statements
■ Callable statements are used to execute database stored
Procedures.
Syantax.
CallableStatement cstmt = [Link]("{call getEmpName (?,?)}");
[Link](1, 111111111);
[Link](2, [Link]);
[Link]();
String empName = [Link](2);
[Link](empName);
08/08/2022 503111 – Chapter 2 - JDBC 24
Callable Statements- Example
import [Link].*
Import [Link].*;
Import [Link].*;
public class Sample extends HttpServlet
{
public void doGet(HttpServletRequest request, HttpServletResponse
response) throws ServletException, IOException
{
try
{
[Link]("[Link]");
}
08/08/2022 503111 – Chapter 2 - JDBC 25
Callable Statements- Example
catch(ClassNotFoundException ex)
{ [Link](); }
Connection con=null;
CallableStatement cstmt=null;
try
{ con=[Link]("jdbc:oracle:thin:@localhos
t:1521:XE"
"system" "password");
cstmt=[Link]("{call p1}"); //called the procedure
08/08/2022 503111 – Chapter 2 - JDBC 26
Callable Statements- Example
//how to create procedure had writen in bellow
[Link]();
[Link]("done");
}
catch(SQLException ex) {[Link](); }
if(cstmt!=null) //close the callablestatement
{
[Link]();
cstmt=null;
}
08/08/2022 503111 – Chapter 2 - JDBC 27
Callable Statements- Example
■ //create the procedure and execute in sql command
prompt
CREATE PROCEDURE P1
AS
BEGIN
INSERT INTO PERSON VALUES(1 'SUBAS');
INSERT INTO PERSON VALUES(2 'VIJAY');
INSERT INTO PERSON VALUES(3 'CNU');
END;
}
08/08/2022 503111 – Chapter 2 - JDBC 28
Executing SQL Statements
■ String createLehigh = "Create table Lehigh " +
"(SSN Integer not null, Name VARCHAR(32), " +
"Marks Integer)";
[Link](createLehigh);
//What does this statement do?
■ String insertLehigh = "Insert into Lehigh values“ +
"(123456789,abc,100)";
[Link](insertLehigh);
08/08/2022 503111 – Chapter 2 - JDBC 29
Get ResultSet
String queryLehigh = "select * from Lehigh";
ResultSet rs = [Link](queryLehigh);
//What does this statement do?
while ([Link]()) {
int ssn = [Link]("SSN");
String name = [Link]("NAME");
int marks = [Link]("MARKS");
}
08/08/2022 503111 – Chapter 2 - JDBC 30
Metadata in JDBC
■ JDBC getMetaData() is the collective data structure, data type and
properties of a table.
ResultSet rs = null;
ResultSetMetaData metaData = [Link]();
int rowCount = [Link]();
[Link]("Table Name : " + [Link](2));
for (int i = 0; i < rowCount; i++) {
[Link]([Link](i + 1) + " \t");
[Link]([Link](i + 1) + "\t");
[Link]([Link](i + 1));
08/08/2022 503111 – Chapter 2 - JDBC 31
Close connection
■ At the end of your JDBC program, it is required explicitly close
all the connections to the database to end each database session.
However, if you forget, Java's garbage collector will close the
connection when it cleans up stale objects.
■ [Link]();
■ [Link]();
08/08/2022 503111 – Chapter 2 - JDBC 32