Java Database Connectivity: An Introduction to JDBC API and Database Drivers, Slides of Java Programming

Download Java Database Connectivity: An Introduction to JDBC API and Database Drivers and more Slides Java Programming in PDF only on Docsity!

Java

ava - programming

An Introduction

Java Short Course Day-

J

Docsity.com

Java

Today’s Lecture

o Java Database Connectivity

 Concepts  JDBC API  Data Base Connectivity using Java  Important Features of JDBC API

Docsity.com

Java

JDBC

o Role of Driver

 JDBC API talks with vendor specific driver  Driver converts JDBC API calls to vendor specific Database API calls

o Merits and Demerits  Applications using JDBC API have no access to vendor specific Database API but to driver only  The performance is reduced  The same application can be used with another implementation of the same database for any other vendor for which a driver is available

Docsity.com

Java

o sdfsd

Docsity.com

Java

Java application Data source

JDBC API (^) JDBC-ODBC Bridge ODBC API (^) ODBC Layer

application process

ODBC process

JDBC-ODBC Bridge

• Microsoft has developed the standard ODBC O pen D ata B ase C onnectivity as

a windows specific standard. It converts from ODBC to the vendor’s native

API

• The JDBC driver converts from JDBC to ODBC.
• This is the only way to access Microsoft Access because access does not

provide direct access to its own API.

• Requires you have ODBC and JDBC drivers installed
• This makes accessing Microsoft Access via Java slow and less reliable

because two sets of conversion and divers are required

CCTM: Course material developed by James King (james.king@londonmet.ac.uk Docsity.com )

Java

Part Java, Part Native Driver

• JDBC driver consists of java code and partly native driver code which uses

vendor-specific API for accessing databases

• Requires the JDBC driver with the correct native code for the database to

be loaded on the client

• More efficient than JDBC-ODBC bridge due to fewer layers of

communication and translation

Java application Data source

JDBC API

JDBC Driver (part Java, part native code)

Vendor-specific API

application process

CCTM: Course material developed by James King (james.king@londonmet.ac.uk Docsity.com )

Java

network

Pure Java Driver

• JDBC calls are directly translated to database calls specific to vendor

and sent across the network

• Requires the correct JDBC driver for the vendor to be loaded on client
• Simple and high performance
• Normally requires vendor to create driver because many databases

have proprietary protocols for accessing them across a network

Java application Data source

JDBC API (^) JDBC Driver

application process

CCTM: Course material developed by James King (james.king@londonmet.ac.uk Docsity.com )

Java

The Driver manager

• It is possible that a Java application may need to talk to multiple

databases, possibly from different vendors (especially if it is a

distributed system)

• The JDBC driver manager provides the ability to communicate with

multiple databases and keep track of which driver is needed for which

database.

• Even if you only talk to one database you need to connect to it via the

driver manager

JDBC Driver Manager

ODBC Oracle Driver XYZ Driver

Access Oracle xyzBase CCTM: Course material developed by James King (james.king@londonmet.ac.uk Docsity.com )

Java

Java JDBC Programming Steps

1. Load Driver. The forName method of Class loads the specified JDBC

driver and initialises the DriverManager.

2. Connect to database. getConnection in DriverManager returns a

Connection Object. The Connection Object is used to communicate with the database specified in getConnection.

3. Execute SQL. createStatement in Connection returns an Statement

Object which can be used to send SQL statements to the connected database.

4. Use executeQuery or executeUpdate methods in Statement to

search or modify the database. (Searches produce a ResultSet object)

5. Process the ResultSet or handle any errors 6. Close Statement object and close the Connection when you have no

more statements to send.

CCTM: Course material developed by James King (james.king@londonmet.ac.uk Docsity.com )

Java

Java JDBC Programming Steps

DriverManager getConnection

Connection createStatement

Statement

Class forName

CCTM: Course material developed by James King (james.king@londonmet.ac.uk)

Class.forName loads the DriverManager

DriverManager. getconnection connects to a database and returns a Connection object

Connection. createStatement provides a Statement object you can insert SQL into

Docsity.com

Java

JDBC Drivers

o A JDBC driver is always needed to connect to a database

o Loading a driver requires the class name of the driver. For

 JDBC-ODBC: sun.jdbc.odbc.JdbcOdbcDriver  Oracle driver: oracle.jdbc.driver.OracleDriver  mySQL: org.gjt.mm.mysql.Driver

o The class for the name of the driver is loaded using the static

method forName in the class Class. (Class is in the package

java.lang and so imported automatically)

 Class.forName("org.gjt.mm.mysql.Driver");  This loads and initialises the driver.  It is possible to load several drivers.

 The class DriverManager manages the loaded driver(s)

Docsity.com

Java

Subname: indicates the location and name of the database to be accessed. Syntax is driver specific

Sub-protocol: identifies a database driver

Protocol: JDBC is the only protocol in JDBC

Step 2: Connecting to a Database part 1

• The getConnection method of DriverManager requires the name of the database to connect to, the name of the user connecting and their password it returns a Connection Object.
• The syntax for the name of the database is a little messy and is unfortunately Driver specific

A JDBC URL represents a driver and has following three-part syntax jdbc::

"jdbc:mysql://www3.unl.ac.uk:3306/kingj1”

CCTM: Course material developed by James King (james.king@londonmet.ac.uk Docsity.com )

Java

Step 3: Executing SQL (Creating Statement objects)

• Connection objects can be used to create Statement objects. Statement

objects allow you to send SQL to the database to be executed and

examine any results

Statement statement = connection.createStatement();

• Different SQL statements require different handling.
  • Updates such as INSERT return the number of rows in the table

effected

• Queries such as SELECT return a ResultsSet Object showing all the

matching entries.

CCTM: Course material developed by James King (james.king@londonmet.ac.uk Docsity.com )

Java

Step 4: Executing SQL (Modifying the Database)

• Once you have a Statement Object you can get the database to

execute your SQL statements. SQL which modifies the database

uses the executeUpdate method of Statement

• Lets add a record to the database
• Lets assume the database has a table called test with a single

column called value

• We will add a record value 99" in SQL this would be INSERT INTO

test VALUES('999')

int r=statement. executeUpdate ("INSERT INTO test VALUES('999')");

Number of records effected. In this example SQL statement surrounded by " " should be 1 since we are adding a single record

CCTM: Course material developed by James King (james.king@londonmet.ac.uk Docsity.com )