references:
minor revision May
28 / 2001
1) 'Java 1.2 Developer's Handbook',
Heller&Roberts 2) JDK 1.2.2 API
3) 'Advanced Java', Cliff
Berg 4) The Java Tutorial SQL, Maydene Fischer
RDBMS -a relational database management system
In a relational database management system information is stored in
rows of a table.
Structured Query Language (SQL)
is used to retrieve, store and change the data.
SQL is an ANSI standard that all vendors more or less comply with.
The Single Tier Database
The original database system was a coupling of the user interface, the
application code,
and database libraries all on a single mainframe which could have hundreds
or thousands
of terminals attached. The Single Tier was simple, by comparison inefficient,
limited in the
area that could be covered, and not designed to handle several application
instances at once.
The Two-Tier Model
With the advent of socket technology, the database evolved into a two-tier
design. Sockets
in conjunction with communication protocols, LANS
(Local Area Networks) and WANS
(Wide Area Networks) allowed an application residing on a client machine
to access, via a
socket, a database located on the back-end
of
a server.
In this model, the client sends SQL
requests to the database server. The server accesses the database and
sends back a reply
containing the requested data. The client application formats and presents
this data to the user.
Many vendors provide libraries written in 'C' to manage this type of
client-server communication.
This model frequently locked a user into vendor version updates. Switching
vendors meant
rewriting front-end applications. The data-accessing 'intelligence'
was largely associated
with the client making the client large and costly.
Three or multi-Tier Database Model
A middle tier soon evolved added between the client and the database
server. The middle tier
could be multithreaded to accept multiple client requests and could
also be attached to one or
more database servers.
This added flexibility to the architecture as data requests could come
from different applications
designed to make queries over vendor-neutral
protocols such as HTTP. The back end of
the
middle tier could be designed to handle different
proprietary
database servers.
The middle
tier also offered a good vantage point to position gateways
and
firewalls
for security control.
As well, additional data management libraries could be added at this
juncture. The middle tier
lightened the client's 'machine' load ultimately spawning today's concept
of the
thin client which
is primarily a user interface, reminicent of the mainframe terminal.
(A mainframe terminal had no
intelligence at all and was really just a keyboard and monitor interfaced
to a net card.) Today's
thin client will always be smarter than the old mainframe terminal.
Java Centric Models
Today network architectures are often referred to as n-tiered designs.
The latest conception
for a java-centric network at it's simplest uses a browser as a client
GUI communicating
with a servlet which would interface with a database. A more complex
architecture might
have a browser as a front end communicating again with a servlet. The
servlet in turn would
communicate with an Enterprise JavaBean. The EJB in turn would take
care of accessing
any of a number of database resources.
JDBC
JDBC offers a single API to handle all database activity in a uniform
and database independent
way. The objective
of JDBC is to allow a single java application meet a corporation's database
requirements despite the fact that the company has it's information
deployed across several
different database platforms.
The JDBC API, (Java Database Connectivity) resides in the java.sql package.
JDBC is not a derivative of ODBC (Microsoft's
Open DataBase Connectivity). Rather,
both JDBC and ODBC derive from X/Open SQL
Command Level Interface(CLI).
JDBC is written in Java.
ODBC
is written in C. The small and efficient
JDBC-ODBC
bridge translates JDBC to ODBC.
JDBC has
1) an Application layer, to access
the database(s), and
2) a
Driver layer, to handle connecting to the given vendor-specific
or custom database.
At the design, level a vendor / programmer must implement four interfaces at the driver level.
The Driver Interface // Driver targets database via --> a data_base_url
Every jdbc application must have at least one driver implementation.
Driver's locate and
access databases a string similar to a URL. This link essentially decouples
application and
driver development. The JDBC url follows the
www standard URL syntax as in;
jdbc:< subprotocol >:<subname >
where subprotocol
is
the driver type (vendor) and subname
is the network-encoded database
name. The subname contains the database connection
information such as host, port, database
name, user_name and password. Optionally,
network
indirection,
can be used to hide
connection information, by creating a subprotocol defining a local
service which resolves an
'alias' to a given database
JDBC url Examples
Example 1. jdbc:oracle:services // describes an oracle driver and a local database, services
Example 2.
jdbc:msql://bugserver.bug:9999/bugparade
// describes a server named bugserver
in the bug domain, on port 9999 and database bugparade
Example 3.
i.e. jdbc:nameService:alias
// describes a naming service, nameService and a name alias
JDBC database driver categories
From the inception of JDBC specifications for
different types of driver types were specified.
| Type 1
JDBC-ODBC bridge driver ( aka JDBC/ODBC driver ) // Java to ODBC |
A client bridges to an ODBC
driver via a JDBC-ODBC
bridge driver. In this model the ODBC driver for the database must be installed. A Type 1 driver translates queries into ODBC queries // via ODBC |
| Type 2
Native-library-to-Java-driver ( aka Native-API ) // Java to Native |
Level 2 drivers specialized
for specific databases like
Oracle and Sybase. A Java part interfaces to a native binary driver which in turn connects to the database. (The driver interfaces with the vendor's driver or database API). // 1st from Oracle, Sybase & Informix |
| Type 3
A network- protocol Java driver ( aka Open Protocol -Net ) // Java to Neutral Protocol |
The application communicates
through a JDBC driver
to a server which uses a DBMS-independent protocol which in turn communicates with a middleware protocol handler that translates to calls on the DBMS libraries Type 3 drivers can talk to multiple databases and are not vendor specific. The all-java client driver communicates with the net server via a database independent protocol. |
| Type 4
DBMS-protocol all-Java driver (aka Proprietary Protocol-Net) // All Java net protocol to native |
Level 4 drivers are written
by the vendor entirely in Java
to connect to a Java-based socket and calls are made directly on the DBMS server via it's native protocol. |
Type 1 ) client -- JDBC-ODBC driver
-- ODBC driver -- database
// bridge driver to ODBC
Type 2 ) client -- JDBC driver
-native- Vendor API --
database //
JDBC to vendor driver to DBMS
Type 3 ) client -- JDBC driver -network-
ProxyServer
-native-
any
database // driver to neutral
protocol
Type 4 ) client -- JDBC driver -- directly
to database
//driver to vendor net protocol
Selective look at the JDBC API
Selected methods of the Driver interface // 2 of 6 methods
| public Connection connect
(String url, Properties info) throws SQLException |
Checks sub-protocol name of the url
string for a match. If a
match is found, a TCP/IP connection is attempted based on rest of the url info. If successful, a Connection object is returned, (a vendor's Connection object ) An SQLException is thrown if the url subprotocol is valid but the connection fails. If the url is a mismatch null is returned. Properties is a container class encapsulating a user_name and password. |
| public boolean
acceptsURL
(String url) throws SQLException |
This method asks the driver if the URL is valid. |
The Driver Interface
One of the six methods the Driver interface describes is the important
connect
method that does the actual connecting to the url that represents the
database.
Usually this method is not called directly but only indirectly through
the method
getConnection( ) of the DriverManager class. If a resource is found
matching the
database described in the url a TCP/IP connection is attempted. If
successful,
the method returns a Connection object. ( This is part of the driver
vendor's
responsibilities to create an implementation of the Connection interface
that
is returned by the connect( ) method. The connect( ) method also takes
a
Properties object that store a user name and password. The method throws
an SQLException if the url is valid but the TCP/IP connection fails.
The signature of the connect method
public Connection connect
(String url, Properties info) throws SQLException
Every jdbc application must have at least one driver implementation.
Driver's
locate and access the particular database files using a special jdbc
version or
a url. The JDBC url follows the www standard
URL syntax as in;
jdbc:< subprotocol >:<subname >
where subprotocol
is the driver type, usually
the vendor's name and subname
is the network-encoded database name. The subname
will contain any network
locating information needed such as the host
domain name or IP address, the
port to use, the database file name, a user_name
and a password.
Optionally,
network indirection, can be used to
hide away the connection
information. In this case the subprotocol field
will provide a naming service
which in turns resolves a connection to a database
based on having been
supplied some kind of alias.
JDBC url Examples
The first example shows a jdbc driver for a mysql
database system located on the
local machine for an unspecified database file
in the DB directory inside the data
directory inside the mysql directory. You can
create a url something like this to
do local development using mySQL on your personal
machine.
Example 1 String url = "jdbc:mysql:///mysql/data/DB/";
The next example describes an oracle driver and a local database called services.
Example 2. jdbc:oracle:services
The third example describes a server named bugserver
in the bug domain, on port
9999 and a database called bugparade.
Example 2. jdbc:msql://bugserver.bug:9999/bugparade
The last example describes a naming service, nameService
and a name alias
which will take care of resolving the connection
to the database without
exposing details about the machine hosting the
database.
Example 3.
i.e. jdbc:nameService:alias
The DriverManager Class
The DriverManager class is the traditional utility
class allowing a user to manage
a set of database drivers. It's methods can be
used to obtain driver connections,
to register and deregister and set up database
logging. The DriverManager class
is simpler to use then the newer DataSource interface
and remains widely used
(We look at the advantages the DataSource interface
confer shortly).
DriverManager maintains a list of drivers which
take care of registering themselves
with the class using the static registerDriver(
) method. The driver vendor supplies
a section of static code, ( called a static
initializer ) that takes care of instantiating
the the class and registering it with the DriverManager.
Example
static { // driver
developer instaniate the driver
// passes instance to DriverManager.registerDriver(
)
}
For this reason, you would not normally instantiate the driver and register
it
yourself as this would presumably be redundant.
The DriverManager can load the class template
that is used to instatiate the
driver in one of two ways, either using the Class.forName(
) method or by
adding the class to the java.lang.System. property
called "jdbc.drivers".
Class.forName( )
A program can also explicitly load drivers as
needed via a call to class Class'
forName( ) method. This is the recommended way
of loading the driver class
into the java environment. This will make the
class available for the section
of static code we mentioned above that will create
and register the driver
object with DriverManager's list of drivers.
Example
Class.forName("Sql_Driver");
Using the java.lang.System Property "jdbc.driver".
The other way the driver class can be made available is to specify the
driver
as a java.lang.System property. This takes the form of a comma separated
list as is shown in the following examples. This method depends on
the
System property not changing. If there is any doubt about the underlying
system's information, the Class.forName( ) approach is probably better
to take. The following examples from the jdk documentation show how
drivers are specified via a java property assignment.
Example 1
jdbc.drivers=foo.bah.Driver:wombat.sql.Driver:bad.taste.ourDriver
Example 2
jdbc.drivers=oracle.sql.Driver:weblogic.sql.Driver:CloudScape.sqlDriver
DriverManager Methods // 5 of 14 methods
| public static synchronized Connection
getConnection (String url,Properties info) throws SQLException |
iterates a vector of registered drivers against
it's
url arg , passing parameters to each driver's connect( )method. Uses the first driver to return a Connection object. |
| public static synchronized Connection
getConnection ( String url ) throws SQLException |
used if the Properties object is empty |
| public static synchronized Connection
getConnection(String url,String user, String password) throws SQLException |
creates a Properties
object then calls
getConnection(String url, Properties info) |
| public static synchronized void
registerDriver(java.sql.Driver driver) throws SQLException |
stores the Driver
object and a securityContext
describing where the driver came from in a vector. |
| public static void setLogWriter
(java.io.PrintWriter out) |
sets a private static PrintWriter
reference to
the PrintWriter object passed as an arg into it |
The DataSource interface
The DataSource interface, new in
the JDBC 2.0 API, provides another way to connect
to a data source and the Java database people prefer it to DriverManager.
The Enterprise
JavaBeans architecture uses DataSource implementations to find it's
database resources.
If neccessary, a DataSource class can be extended to wrap a generic
DriverManager.
The DriverManager class has been heavily used and spoken of in texts
so it won't be
disappearing anytime soon.
The Application Layer
Involves the three interface implementations, defined at the Driver
layer, but used at the
application layer. These are the Connection,
Statement
and ResultSet implementations
used to access databases. Normally, the Connection
object is obtained from the driver
implementation via the DriverManager.getConnection(
). Once the Connection object
is returned, a Statement
object
can be created with Connection's createStatement(
)
method. The Statement object provides the methods
used to send SQL commands to a
database. If the SQL statement sent is a SELECT
query,
the result is returned encapsulated
in a ResultSet
object. The ResultSet
instance
in turn provides methods to access the
returned result set data.
The Connection Interface
The Connection interface represents
a session (or connection) with the database provided
by the Driver. The interface includes
definitions for the following methods.
Connection Methods // 7 of 24 methods
| Statement createStatement(
)
throws SQLException |
returns a Statement object, used to issue queries. |
| PreparedStatement
prepareStatement (String sql) throws SQLException |
returns a PreparedStatement
object,
(configured with the sql string passed in) |
| CallableStatement
prepareCall (String sql) throws SQLException |
returns a CallableStatement,
(this object is optimized for stored procedures) |
| void setAutoCommit
(boolean autoCommit) throws SQLException |
when a JDBC connection is made, auto-commit
is
set
to true by default meaning no rollback where changes are automatically committed to the database. Setting auto-commit to false makes rollbacks possible If auto commit has been disabled, an explicit commit must be done or database changes will not be saved. |
| void commit (
)
throws SQLException |
Makes all changes made since the previous commit
or
rollback permanent and releases any database locks currently held by the Connection. |
| void rollback(
)
throws SQLException |
drops changes made since beginning of transaction |
| DatabaseMetaData
getMetaData( ) throws SQLException |
returns an object implementing the DatabaseMetaData
interface. This object provides information about the database as a whole (DatabaseMetaData interface defines over 130 information retrieving methods which vendors are free to implement in whatever manner they wish) . |
The DatabaseMetaData implementation
is important for testing which of the features one
expects of a target database are supported by any given JDBC driver.
The Statement Interface
A statement is the vehicle for sending SQL to the database and retrieving
the results. They
can be SQL updates, inserts, deletes or queries
as well as table creations or deletions.
It common methods are listed below.
Statement Interface Methods // 6 of 18 methods
| ResultSet executeQuery
(String sql) throws SQLException |
executes one SQL query, and returns a ResultSet object |
| int executeUpdate(String
sql)
throws SQLException |
executes an SQL query, returning the number of rows effected |
| boolean execute(String sql) | a general means to execute SQL statements, which
may return
multiple ResultSets and/or number counts.The boolean flag is used to indicate if there are more result sets. |
| ResultSet getResultSet
(
)
throws SQLException |
used with execute( ) to get more result
sets
// i.e. in a do... while loop |
| int getUpdateCount
(
)
throws SQLException |
used with execute( ) to get more update
counts
// i.e in a loop |
| boolean getMoreResults
(
)
throws SQLException |
used to test if there are more results/updates
in a set of
multiple results/update counts . |
The PreparedStatement Interface
Figure 1:
CallableStatement descends from PreparedStatement
which
descends from Statement
Statement Interface
|
PreparedStatement Interface
|
CallableStatement Interface
PreparedStatement extends Statement
and is useful for repeating statements with minor
variations. setType( ) methods
are used to fill parameter values marked by question
marks
in a PreparedStatement.
Parameters
are indexed from 1 to n. PreparedStatement has
a set of setType( ) methods,
one
for each java type,which is used to set the corresponding
SQL type (in locations represented by question marks).
Example // from JDK1.2.2 API documentation
PreparedStatement pstmt = con.prepareStatement
("UPDATE EMPLOYEES SET SALARY = ? WHERE
ID = ?");
pstmt.setBigDecimal(1, 153833.00);
pstmt.setInt(2, 110592);
// the first argument is the question mark index, (starting at 1), & the second is the value to be stored
The CallableStatement Interface
CallableStatement extends PreparedStatement.
The CallableStatement interface is used
to execute SQL stored procedures.
(Also called persistent stored modules,
they are
program routines stored in the database which
are invoked by SQL)
IN parameters use inherited execute(
) and setType( ) methods
and OUT parameters use
getType( ) methods to return java
types for SQL returns from within the stored procedures.
CallableStatement's set of getType( )
methods are a mirror of PreparedStatement's setType(
)
methods. get/setType( ) methods
access reference requested data in each column by row
position (1 to n) or by name.
| Exerpt from the Java Tutorial, authored by Maydene
Fischer http://java.sun.com/docs/books/tutorial/jdbc/index.html
" In some DBMSs, the following SQL statement creates a stored procedure:
create procedure SHOW_SUPPLIERS
The following code puts the SQL
statement into a string and assigns it to the variable
String
createProcedure = "create procedure SHOW_SUPPLIERS " +
The following code fragment uses
the Connection object con to create a Statement
Statement stmt = con.createStatement();
The procedure SHOW_SUPPLIERS
will be compiled and stored in the database as a
|
The ResultSet Interface
The ResultSet interface defines
methods for accessing data tables generated by a Statement
execution. ResultSet tables are
fully indexed and accessible by row number or column name.
The interface has over 40 accessing methods( ). These three methods
may throw SQLExceptions
as vendors have the option of caching the results on the remote side
creating the possibility of a
communication error.
The three (of 40 methods)
that throw SQLExceptions
| boolean next ( ) thows SQLException | positions the result set to the next row. The
initial position is just before the first row. |
| ResultSetMetaData getMetaData(
)
throws SQLException |
returns an object describing the current result set |
| void close( ) throws SQLException | closes a result set |
The ResultSetMetaData Interface
This interface allows the developer to gather information about a
ResultSet object and defines
about 20 methods( ).
Transactions
A database transaction
is a sequence of operations with a well-defined beginnning and end
point, the set of which can be treated as a unit
of change for the database. The end point of a
transaction is when the transaction is committed
and thereby made permanent, or rolled back
in which case the changes are discarded. JDBC
specifies one transaction can exist at a time per
connection. Also, drivers are required to allow
concurent request from different threads on the
same connection. Transactions are by default
in autocommit
mode where commit( )
is called
automatically when the statement completes. Setting
setAutoCommit( false) turns autocommit off.
If autocommit is disabled and a transaction is
performed which obtains a lock on the database,
the lock will remain in effect until the commit(
) or abort( ) are called , or some other form of
disconnection occurs. A local
transaction is one executed against a single data resource.
A
transaction coordinated by a transaction manager across multiple heterogeneous
data resources
is called a global transaction. The
Java Transaction API , JTA,
provides a standard Java
interface to tranaction management.
Functions
Most databases have a set of functions that can
be called to construct expressions.
To call a function, use the
escape sequence with the fn keyword.
Example
ResultSet rs = statement.executeQuery("{ \fn LOG( " + a_float + " ) }");