Java Database Connectivity

ninetimesdissemblingSoftware and s/w Development

Nov 10, 2012 (5 years and 7 days ago)

207 views

3/16/2013

B.Ramamurthy

1

Java Database Connectivity

B.Ramamurthy


3/16/2013

B.Ramamurthy

2

Introduction

Most popular form of database system is the
relational database system.

Examples: MS Access, Sybase, Oracle, MS
Sequel Server.

Structured Query Language (SQL) is used
among relational databases to construct
queries.

These queries can be stand
-
alone or
embedded within applications. This form of
SQL is known as embedded SQL.

3/16/2013

B.Ramamurthy

3

Simple Database Application

A

P

P

L

I

C

A

T

I

O

N

DBMS

DB

Ex: Access

Oracle

Sybase

3/16/2013

B.Ramamurthy

4

Multi
-
Databases

A

P

P

L

I

C

A

T

I

O

N

DBMS 1

DBMS 2

DBMS 3

DB

DB

DB

3/16/2013

B.Ramamurthy

5

Standard Access to DB

A

P

P

L

I

C

A

T

I

O

N

D

R

I

V

E

R


M

G

R

DBMS

Driver 1

DBMS

Driver 2

DBMS

Driver 3

DBMS 1

DBMS 2

DBMS 3

DB

DB

DB

3/16/2013

B.Ramamurthy

6

ODBC Architecture

Application

Class1

Class2

Driver Manager

DriverType1

DriverType2

DriverType3

DataSource2

DataSource1

DataSource3

ODBC

3/16/2013

B.Ramamurthy

7

Open Database Connectivity
(ODBC) Standard


ODBC standard is an interface by which application
programs can access and process SQL databases in a
DBMS
-
independent manner. It contains:

A
Data Source

that is the database, its associated
DBMS, operating system and network platform

A

DBMS Driver
that is supplied by the DBMS vendor
or independent software companies

A
Driver Manager

that is supplied by the vendor of
the O/S platform where the application is running

3/16/2013

B.Ramamurthy

8

ODBC Interface

It is a system independent interface to database
environment that requires an ODBC driver to be
provided for each database system from which
you want to manipulate data.

The database driver bridges the differences
between your underlying system calls and the
ODBC interface functionality.

3/16/2013

B.Ramamurthy

9

An Example

Application

DriverManager

Sybase driver

mSQL driver

Informix driver

3/16/2013

B.Ramamurthy

10

Application in Java

Application in

Java

DriverManager

Sybase driver

mSQL driver

Informix driver

3/16/2013

B.Ramamurthy

11

Java Support for ODBC : JDBC

When applications written in Java want to
access data sources, they use classes and
associated methods provided by Java DBC
(JDBC) API.

JDBC is specified an an “interface”.

An interface in Java can have many
“implementations”.

So it provides a convenient way to realize
many “drivers”

3/16/2013

B.Ramamurthy

12

Java Support for SQL

Java supports embedded SQL.

Also it provides an JDBC API as a standard
way to connect to common relational
databases.

You need a JDBC:ODBC bridge for using the
embedded SQL in Java.

Java.sql package and an extensive exception
hierarchy.

We will examine incorporating this bridge
using sample code.


3/16/2013

B.Ramamurthy

13

Data Source

Local relational database; Ex: Oracle

Remote relational database on a server;
Ex: SQLserver

On
-
line information service; Ex: Dow
Jones, Customer database

3/16/2013

B.Ramamurthy

14

Data Source and Driver

Data source is the data base created using
any of the common database applications
available.

Your system should have the driver for the
database you will be using.

For example your Windows system should
have the MS Access Driver.

There are a number of JDBC drivers available.

Information on installing them is available at :

http://industry.java.sun.com/products/jdbc/drivers



3/16/2013

B.Ramamurthy

15

JDBC Components

Driver Manager
: Loads database drivers, and
manages the connection between application & driver.

Driver
: Translates API calls to operations for a specific
data source.

Connection
: A session between an application and a
driver.

Statement
: A SQL statement to perform a query or an
update operation.

Metadata
: Information about the returned data, driver
and the database.

Result Set

: Logical set of columns and rows returned
by executing a statement.

3/16/2013

B.Ramamurthy

16

JDBC Classes

Java supports DB facilities by providing
classes and interfaces for its components

DriverManager

class

Connection

interface (abstract class)

Statement
interface

(to be instantiated
with values from the actual SQL
statement)

ResultSet

interface

3/16/2013

B.Ramamurthy

17

Driver Manager Class

Provides static, “factory” methods for
creating objects implementing the
connection

interface.


Factory methods create objects on demand

when a connection is needed to a DB
driver, DriverManager does it using it
factory methods.

3/16/2013

B.Ramamurthy

18

Connection interface

Connection class represents a session
with a specific data source.

Connection object establishes
connection to a data source, allocates
statement objects, which define and
execute SQL statements.

Connection can also get info (metadata)
about the data source.

3/16/2013

B.Ramamurthy

19

Statement interface

Statement interface is implemented by the
connection object.

Statement object provides the workspace for
SQL query, executing it, and retrieving
returned data.

SELECT {what} FROM {table name} WHERE
{criteria} ORDER BY {field}

Queries are embedded as strings in a
Statement object.

Types: Statement, PreparedStatement,
CallableStatement

3/16/2013

B.Ramamurthy

20

ResultSet interface

Results are returned in the form of an
object implementing the ResultSet
interface.

You may extract individual columns,
rows or cell from the ResultSet using
the metadata.

3/16/2013

B.Ramamurthy

21

JDBC Application Architecture

Application

Connection

Driver Manager

Driver

Driver

Driver

DataSource

DataSource

DataSource

Statement

Result Set

3/16/2013

B.Ramamurthy

22

JDBC Programming Steps

Import necessary packages; Ex: import java.sql.*;

Load JDBC driver(driver should have been installed)

Data source and its location should have been
registered.

Allocate Connection object, Statement object and
ResultSet object

Execute query using Statement object

Retrieve data from ResultSet object

Close Connection object.

3/16/2013

B.Ramamurthy

23

Identifying Data Sources

It is specified using URL format.

<scheme>: <sub_scheme>:<scheme
-
specific
-
part>

Example(for local source):
jdbc:odbc:tech_books

Alternatively, for remote connection,

jdbc:odbc://bina.cse.buffalo.edu:4333/tech_boo
ks

Database servers & IDE

IDE such as Netbeans and App Engines
such as Google automatically support a
relational database server and help
deploy your database.

Small footprints database such as
derby, pointbase, cloudscape are
convenient for prototyping.

Oracle, DB2, MySQL and MS SQL server
are industrial strength DBMS.

3/16/2013

B.Ramamurthy

24

3/16/2013

B.Ramamurthy

25

Summary

JDBC API of Java language conforms to the ODBC
standard.

It supports ODBC by providing classes/interfaces
for driver manager, driver, connection, statement
and resultset and others.

You can access various components in JDBC by
instantiating or implementing objects and accessing
their methods.