Java Programming Language

Connection Pooling in a Three-tier Environment
The following sequence of steps outlines what happens when a JDBC client
requests
a connection from a DataSource object that implements connection pooling:
n The client calls DataSource.getConnection.
n The application server providing the DataSource implementation looks in
its
connection pool to see if there is a suitable PooledConnection object- a
physical database connection-available. Determining the suitability of a
given
PooledConnection object may include matching the client's user
authentication
information or application type as well as using other
implementation-specific
criteria. The lookup method and other methods associated with managing the
connection pool are specific to the application server.
n If there are no suitable PooledConnection objects available, the
application
server calls the ConnectionPoolDataSource.getPooledConnection
method to get a new physical connection. The JDBC driver implementing
ConnectionPoolDataSource creates a new PooledConnection object and
returns it to the application server..n Regardless of whether the
PooledConnection was retrieved from the pool or
was newly created, the application server does some internal bookkeeping to
indicate that the physical connection is now in use.
n The application server calls the method PooledConnection.getConnection
to get a logical Connection object. This logical Connection object is
actually a
"handle" to a physical PooledConnection object, and it is this handle that
is
returned by the DataSource.getConnection method when connection pooling
is in effect.
n The application server registers itself as a ConnectionEventListener by
calling the method PooledConnection.addConnectionEventListener.
This is done so that the application server will be notified when the
physical
connection is available for reuse.
n The logical Connection object is returned to the JDBC client, which uses
the
same Connection API as in the basic DataSource case. Note that the
underlying physical connection cannot be reused until the client calls the
method
Connection.close.
Connection pooling can also be implemented in a two-tier environment where
there
is no application server. In this case, the JDBC driver provides both the
implementation of DataSource which is visible to the client and the
underlying
ConnectionPoolDataSource implementation.

Possible interaction between concurrent transactions is categorized as
follows:
n dirty reads occur when transactions are allowed to see uncommitted changes
to
the data. In other words, changes made inside a transaction are visible
outside the
transaction before they are commited. If the changes are rolled back instead
of
being committed, it is possible for other transactions to have done work
based on
incorrect, transient data.
n nonrepeatable reads occur when:
a. Transaction A reads a row
b. Transaction B changes the row
c. Transaction A reads the same row a second time and gets different results
n phantom reads occur when:
a. Transaction A reads all rows that satisfy a WHERE condition
b. Transaction B inserts an additional row that satisfies the same condition
c. Transaction A reevaluates the WHERE condition and picks up the additional
"phantom" row

Types of Drivers
There are many possible implementations of JDBC drivers. These
implementations
are categorized as follows:
n Type 1 - drivers that implement the JDBC API as a mapping to another data
access API, such as ODBC. Drivers of this type are generally dependent on a
native library, which limits their portability. The JDBC-ODBC Bridge driver
is an
example of a Type 1 driver.
n Type 2 - drivers that are written partly in the Java programming language
and
partly in native code. These drivers use a native client library specific to
the data
source to which they connect. Again, because of the native code, their
portability
is limited.
n Type 3 - drivers that use a pure Java client and communicate with a
middleware
server using a database-independent protocol. The middleware server then
communicates the client's requests to the data source.
n Type 4 - drivers that are pure Java and implement the network protocol for
a
specific data source. The client connects directly to the data source.

#3
A new Connection object created using the JDBC 2.1 core API has an
initially empty type map associated with it. A user may enter a custom
mapping for a UDT in this type map. When a UDT is retrieved from a data
source with the method ResultSet.getObject, the getObject method will check
the connection's type map to see if there is an entry for that UDT. If so,
the getObject method will map the UDT to the class indicated. If there is no
entry, the UDT will be mapped using the standard mapping.

A user may create a new type map, which is a java.util.Map object,
make an entry in it, and pass it to the java.sql methods that can perform
custom mapping. In this case, the method will use the given type map instead
of the one associated with the connection.

For example, the following code fragment specifies that the SQL type
ATHLETES will be mapped to the class Athletes in the Java programming
language. The code fragment retrieves the type map for the Connection object
con, inserts the entry into it, and then sets the type map with the new
entry as the connection's type map.

java.util.Map map = con.getTypeMap();
map.put("mySchemaName.ATHLETES", Class.forName("Athletes"));
con.setTypeMap(map);

#1
Callable Statement is used as follows :

CallableStatement cs = con.prepareCall("{call SHOW_SUPPLIERS}");
ResultSet rs = cs.executeQuery();

#2
By default a Connection object is in auto-commit mode, which means that it
automatically commits changes after executing each statement. If auto-commit
mode has been disabled, the method commit must be called explicitly in order
to commit changes; otherwise, database changes will not be saved.