Transcript Java Database Connectivity (JDBC)

Java Database
Connectivity (JDBC)
Topics
1. The Vendor Variation Problem
2. SQL and Versions of JDBC
3. Creating an ODBC Data Source
4. Simple Database Access
5. Modifying the Database Contents
6. Transactions
7. Meta Data
8. Using a GUI to Access a Database
9. Scrollable ResultSets in JDBC 2.0
10. Modifying Databases via Java Methods
11. Using the DataSource Interface.
Introduction to JDBC
• JDBC is used for accessing databases from Java
applications
• Information is transferred from relations to
objects and vice-versa
- databases optimized for searching/indexing
- objects optimized for engineering/flexibility
3
JDBC Architecture
Oracle
Driver
Oracle
Java
Application
JDBC
DB2
Driver
DB2
Network
MySQL
Driver
MySQL
4
JDBC Architecture (cont.)
Application
JDBC
Driver
• Java code calls JDBC library
• JDBC loads a driver
• Driver talks to a particular database
• An application can work with several databases by using
all corresponding drivers
• Ideal: can change database engines without changing
any application code (not always in practice)
What is JDBC?

“An API that lets you access virtually any tabular data
source from the Java programming language”

JDBC Data Access API – JDBC Technology Homepage
What’s an API?
 See J2SE documentation
 What’s a tabular data source?
“… access virtually any data source, from relational
databases to spreadsheets and flat files.”




JDBC Documentation
We’ll focus on accessing Oracle databases
6
General Architecture



What design pattern is
implied in this
architecture?
What does it buy for us?
Why is this architecture
also multi-tiered?
7
8
JDBC

JDBC is a Sun trademark



It is often taken to stand for Java Database Connectivity
Java is very standardized, but there are many versions
of SQL
JDBC is a means of accessing SQL databases from Java


JDBC is a standardized API for use by Java programs
JDBC is also a specification for how third-party vendors
should write database drivers to access specific SQL versions
Driver types

There are four types of drivers:

JDBC Type 1 Driver -- JDBC/ODBC Bridge drivers





ODBC (Open DataBase Connectivity) is a standard software API
designed to be independent of specific programming languages
Sun provides a JDBC/ODBC implementation
JDBC Type 2 Driver -- use platform-specific APIs for data
access
JDBC Type 3 Driver -- 100% Java, use a net protocol to
access a remote listener and map calls into vendor-specific
calls
JDBC Type 4 Driver -- 100% Java

Most efficient of all driver types
Why JDBC drivers?
•
To cope with the variation in internal format of databases from
vendor to vendor
•
JDBC drivers are some mediating software that allow JDBC to
communicate with the vendor-specific API for that database.
•
For information about JDBC drivers form specific databases, visit
http://servlet.java.sun.com/products/jdbc/drivers.
Sun’s JDBC-ODBC driver for MS-databases
•
Microsoft's ODBC drivers were originally available only for
Microsoft (MS) databases
•
Sun’s JDBC-ODBC bridge driver is for MS-databases
•
It is in package sun.jdbc.odbc, which is included in the J2SE
•
JDBC-ODBC driver converts the JDBC protocol into the
corresponding ODBC one
SQL and Versions of JDBC
Versions of JDBC
•
JDBC 1.0 with JDK 1.1
•
JDBC 2.0 with JDK 1.2 (J2SE 1.2) (commonly used)
•
•
scrolling forwards and backwards in a result set
•
making updates to database tables using Java methods
JDBC 2.1 with J2SE 1.2.2  J2SE 1.3.
•
•
•
server-side database manipulation
JDBC 3.0 with J2SE 1.4.
•
Access from spreadsheets and flat files or any data source
•
Included java.sql and javax.sql
JDBC 4.0 with JDK6.
java.sql
•
The package comprising the core JDBC API is called java.sql
•
We use a simple MS Access database, which means that the
inbuilt JDBC-ODBC bridge driver can be employed
•
If you wish to experiment with other databases, place the
appropriate JDBC driver within folder java\jre\lib\ext.
Creating an ODBC Data Source
Creating an ODBC Data Source
1. First , it is necessary to register the database as an ODBC Data
Source.
2. Once this has been done, the database can be referred to by
its Data Source Name (DSN).
steps to set up your own ODBC Data Source(only for ODBC databases!)
Simple Database Access
Using JDBC to access a database requires several steps
Using JDBC to access a database requires several steps
1.Load the Database Driver
•
class is used to hold methods that operate upon other
classes in order to furnish details of their characteristics
•
forName  method of class
Using JDBC to access a database requires several steps
2. Establish a Connection to the Database
•
declare a Connection reference
•
call static method getConnection of class DriverManager to
return a Connection object
•
Method getConnection takes three String arguments:
1. a URL-style address for the database;
2. a user name;
3. a password.
Using JDBC to access a database requires several steps
2. Establish a Connection to the Database
Address format: jdbc:<sub-protocol>:<data-source>
• <sub-protocol>  specifies a database connection service (i.e., a
driver)
• <data-source>  provides all the information needed by the
service to locate the database
Using JDBC to access a database requires several steps
2. Establish a Connection to the Database
Assuming that our Finances database is indeed local and that we
did not set a user name or password for this database
If this same database were remote
Using JDBC to access a database requires several steps
3. Create a Statement Object and Store its Reference
• Call createStatement () method of our Connection object
• Save the address of the object returned in a Statement
reference.
Using JDBC to access a database requires several steps
4. Run a Query/Update and Accept the Result(s)
• Class Statement has methods to execute queries:
1. executeQuery ()  to retrieve data from a database
2. executeUpdate() to change the contents of the database
•
The former method returns a ResultSet object,
•
latter returns an integer that indicates the number of database
rows that have been affected by the updating operation.
Using JDBC to access a database requires several steps
4. Run a Query/Update and Accept the Result(s)
Using JDBC to access a database requires several steps
4. Run a Query/Update and Accept the Result(s)
Using JDBC to access a database requires several steps
5. Manipulate/Display/Check Result(s)
•
The only method of ResultSet that we need to make use of at
present is next, which moves the ResultSet cursor/pointer to
the next row in the set of rows referred to by that object.
•
We can retrieve data via either the field name or the field
position using the appropriate method
5. Manipulate/Display/Check Result(s)
5. Manipulate/Display/Check Result(s)
6. Repeat Steps 4 and 5 as Required
7.Close the Connection
Modifying the Database Contents
Modifying the Database Contents
•
Database Update Statements :
•
•
•
•
INSERT
DELETE
UPDATE
Update statements are submitted via the executeUpdate()
method of statement interface
Modifying the Database Contents
Example:
Example:
Example:
Example:
Example:
Example:
Example:
Transactions
Transactions
• one or more SQL statements that may be grouped together as a
single processing entity
• We want either all the statements or none of them
to be
executed.
• Java provides the Connection interface methods
1. commit
 used at the end of a transaction to
commit/finalise the database changes
2. rollback  used to restore the database to the state it
was in prior to the current transaction
Transactions
• By default, JDBC automatically commits each individual SQL
statement that is applied to a database.
• In order to change this default behaviour so that transaction
processing may be carried out, we must first execute Connection
method setAutoCommit with an argument of false
• We can then use methods commit and rollback to effect
transaction processing.
Transactions
Meta Data
Meta Data
• 'data about data‘
•
Two categories of meta data available through the
JDBC API:
•
data about the rows and columns returned by a query (i.e.,
data about ResultSet objects);
•
data about the database as a whole.
The first of these is provided by interface ResultSetMetaData, an
object of which is returned by the ResultSet method getMetaData
Meta Data
•
data about the rows and columns is provided by interface
ResultSetMetaData, an object of which is returned by the
ResultSet method getMetaData
•
Data about the database as a whole is provided by interface
DatabaseMetaData, an object of which is returned by the
Connection method getMetaData
Meta Data
•
Information available from a
ResultSetMetaData
object includes the following:
• the number of fields/columns in a ResultSet object;
• the name of a specified field;
• the data type of a field;
• the maximum width of a field;
• the table to which a field belongs.
Meta Data
•
SQL types is represented in class java.sql.Types
INTEGER and VARCHAR are particularly commonplace, the latter
of these corresponding to string values.
Meta Data
•
The example coming up makes use of the following
ResultSetMetaData methods, which return properties
of the database fields held in a ResultSetMetaData
object.
Meta Data
Meta Data
Meta Data
Meta Data
Meta Data
Meta Data
Meta Data
Meta Data
Scrollable
ResultSets in JDBC 2
Scrollable ResultSets in JDBC 2
With the emergence of JDBC 2 in Java 2, however, a great deal more
flexibility was made available to Java programmers by the
introduction of the following ResultSet methods:
Scrollable ResultSets in JDBC 2
• results.relative(-3); //Move back 3 rows.
•
results.absolute(3); // Move to row 3 (from start of ResultSet).
•
An overloaded form of the Connection method createStatement
is used to create scrollable ResultSets. This method that takes
two integer arguments.
Scrollable ResultSets in JDBC 2
• There are three possible values that <resultSetType>
can take:
• TYPE_SCROLL_SENSITIVE causes any changes made to the data
rows to be reflected dynamically in the ResultSet object, while
TYPE_SCROLL_INSENSITIVE does not.
Scrollable ResultSets in JDBC 2
• <resultSetConcurrency> can take two possible values
the first means that we cannot make changes to the
ResultSet rows, while the second will allow changes to be
made
Scrollable ResultSets in JDBC 2
Scrollable ResultSets in JDBC 2
Scrollable ResultSets in JDBC 2
Scrollable ResultSets in JDBC 2
Scrollable ResultSets in JDBC 2
Scrollable ResultSets in JDBC 2
Scrollable ResultSets in JDBC 2
Scrollable ResultSets in JDBC 2
Modifying
Databases via Java
Methods
Modifying Databases via Java Methods
•
With of JDBC 2 we can modify ResultSet rows directly via Java
methods
•
Without having to send SQL statements
•
the changes reflected in the database itself!
To do this:
1. use the second version of createStatement
2. supply ResultSet.CONCUR_UPDATABLE as the second
argument.
Modifying Databases via Java Methods
•
The updateable ResultSet object does not have to be
scrollable
Modifying Databases via Java Methods
•
A set of updateXXX methods are used to update database.
•
Each of these methods takes two arguments:
• a string specifying the name of the field to be
updated;
• a value of the appropriate type that is to be
assigned to the field
1. Updating
There are three steps involved in the process of updating:
2. Insertion
For an insertion, the new row is initially stored within a special
buffer called the 'insertion row' and there are three steps involved
in the process:
2. Insertion
3. Delete
Example :
Sample Output