Transcript Basics of JDBC - WordPress.com

Basics of JDBC
Session 14
Objectives

Explain the concept of database connectivity

Describe ODBC
Discuss what is JDBC
Discuss why we need JDBC
Describe the java.sql package in brief
Discuss types of drivers
Explain the anatomy of a JDBC program





Basics of JDBC / 2 of 23
Database




A database contains data that is in an
organized form
Client/Server applications make extensive
use of database programming
Activities may involve opening a connection,
communicating with a database, executing
SQL statements and retrieving query results
Standardized APIs are available that simplify
database programming. Examples of these
are ODBC and JDBC
Basics of JDBC / 3 of 23
ODBC

Open DataBase Connectivity (ODBC) is an
Application Programming Interface (API)
provided by Microsoft for accessing
databases

Provides functions to insert, modify and
delete data and obtain information from the
database
Basics of JDBC / 4 of 23
How ODBC
connection takes place?
Applicatio
n
ODBC
Interface
Driver
Manager
Access
Driver
Access
Database
Oracle
Driver
Oracle
Database
…
…
 Application could be a GUI program
 Makes use of ODBC to interact with databases
 Driver Manager is part of Microsoft ODBC and is used
to manage various drivers in the system
Basics of JDBC / 5 of 23
Need for JDBC






ODBC uses a C interface that has lot of drawbacks
A literal translation of the ODBC C interface into a
Java API would not be desirable
ODBC mixes simple and advanced features together
and has complex options even for simple queries
A Java API like JDBC is needed in order to enable a
“pure Java” solution
JDBC is portable
JDBC is a standard interface for Java programmers
to access relational databases
Basics of JDBC / 6 of 23
JDBC

JDBC is a Java Database Connectivity API
that is a part of the Java Enterprise API

Defines a set of API objects and methods to
interact with databases
JDBC is a must for all Java applications that
access data stored in external data providers
like SQL Server, Oracle or Access

Basics of JDBC / 7 of 23
JDBC Driver Types (1)

JDBC-ODBC Bridge plus ODBC Driver



ODBC is not readily convertible to Java
Sun provides a bridge driver to access ODBC data
sources from JDBC
This is called the JDBC-ODBC Bridge plus ODBC
driver
ODBC Driver
JDBC ODBC
bridge
DB-client
ODBC Driver
Database Server
Oracle
Database Server
SQL Server
Basics of JDBC / 8 of 23
JDBC Driver Types (2)

Native API partly-Java Driver




JDBC calls are converted into calls on the client
API for DBMS
This driver uses JavaNativeInterface(JNI) that
calls the local database APIs
The Native APIs partly-Java driver calls the Native
Database Library that accesses the database
This driver like the ODBC driver needs binary
code on the client machine
JDBC Driver (Java
and Binary Code)
DB-client
Vendor
Specific
Protocol
Database Server
Basics of JDBC / 9 of 23
JDBC Driver Types (3)

JDBC-Net pure Java driver





Uses a networking protocol and middleware to
communicate with the server
Server then translates the messages
communicated to DBMS specific function calls
Specific protocol used depends on the vendor
No need for client installation
Allows access to multiple back-end databases
Database Server
JDBC Driver
(Pure Java
DB-client
Driver)
Networking protocol
& middleware
Database Server
SQL Server
Basics of JDBC / 10 of 23
JDBC Driver Types (4)

Native-protocol pure Java driver




100% Java enabled and does not use CLI libraries
Capable of communicating directly with the database
Converts JDBC calls into network protocols such as
TCP/IP and other proprietary protocols used by
DBMS directly
Since many of these protocols are proprietary, the
database vendors themselves will be the primary
source of usage
JDBC Driver
(Pure Java Driver)
DB-client
Vendor
Specific
Protocol
Database Server
Basics of JDBC / 11 of 23
JDBC architecture (1)
Java Program
JDBC Driver
SQL command
Results
Application Server
Database
Basics of JDBC / 12 of 23
JDBC architecture (2)

The JDBC API interface comprises of two
layers:



Application Layer – developer makes calls to
database through SQL and retrieves results
Driver layer – handles all communication with a
specific driver implementation
Four main Java interfaces that every Driver
layer must implement are :

Driver, Connection, Statement and ResultSet
Basics of JDBC / 13 of 23
JDBC architecture (3)
Application layer
Driver layer
Implements Interfaces
Driver
Statement
Connection
ResultSet
Basics of JDBC / 14 of 23
JDBC components




Application – here the JDBC methods are
used to execute SQL and get results
Driver Manager – to load specific drivers for
an application
Driver
Data Source – User application interacts with
this to get results
Basics of JDBC / 15 of 23
The java.sql package



JDBC API defines a set of interfaces and
classes used for communicating with the
database
These are contained in the java.sql package
Classes included in this package are :


Date, DriverManager, DriverPropertyInfo, Time,
TimeStamp, Types
Interfaces included are :

Callable Statement, Connection,
DatabaseMetaData, Driver, PreparedStatement,
ResultSet, ResultSetMetaData, Statement
Basics of JDBC / 16 of 23
Creating a JDBC application
Begin
1
Import the
java.sql package
2
Load and
Register the driver
3
Create a
Connection object
4
Create a
Statement object
5
Execute the
statement
6
7
Close Connection
8
End
Basics of JDBC / 17 of 23
Example
Output
Basics of JDBC / 18 of 23
Using SQL (1)

To retrieve the name, phone, email and phone
number from the table colleagues:
SELECT name, email, phone FROM colleagues

To find out the number of employees who joined
together on the same day and whose job_id is
equal to 5:
SELECT count(*) FROM Employee WHERE
job_id=5 GROUP BY hire_date
Basics of JDBC / 19 of 23
Using SQL (2)

SQL DML statements do not return the results
as ResultSets

Instead they return an integer representing
the number of rows affected
Assume we need to add a new record –


INSERT into COFFEE
VALUES(‘French_Roast’,00049,8.99,0,0)
Basics of JDBC / 20 of 23
Using SQL (3)

The LIKE operator is used with SQL
statements to compare two strings


SELECT * FROM employee WHERE emp_id LIKE
‘A%’
Data Definition Language statements are
used to create tables, add columns to the
existing tables; delete tables and so on

CREATE TABLE Emp(emp_name
VARCHAR(25),emp_no VARCHAR(4),emp_age
number);
Basics of JDBC / 21 of 23
Example (1)
Basics of JDBC / 22 of 23
Example (2)
Output
Basics of JDBC / 23 of 23