Distributed Transactions

As with pooled connections, connections made via data source object that is
implemented to work with the middle tier infrastructure may participate in distributed
transactions. This gives an application the ability to involve data sources on multiple
servers in a single transaction.
The classes and interfaces used for distributed transactions are:
 XADataSource
 XAConnection
These interfaces are used by transaction manager; an application does not use them
directly.
The XAConnection interface is derived from the PooledConnection interface, so what
applies to a pooled connection also applies to a connection that is part of distributed
transaction. A transaction manager in the middle tier handles everything transparently.
The only change in application code is that an application cannot do anything that would
interfere with the transaction manager’s handling of the transaction. Specifically
application cannot call the methods [Link] or [Link] and it
cannot set the connection to be in auto-commit mode.
An application does not need to do anything special to participate in a distributed
transaction. It simply creates connections to the data sources it wants to use via the
[Link] method, just as it normally does. The transaction manager
manages the transaction behind the scenes. The XADataSource interface creates
XAConnection objects, and each XAConnection object creates an XAResource object that
the transaction manager uses to manage the connection.

Rowsets
The RowSet interface works with various other classes and interfaces behind the
scenes. These can be grouped into three categories.
1. Event Notification
o RowSetListener
A RowSet object is a JavaBeansTM component because it has properties and participates
in the JavaBeans event notification mechanism. The RowSetListener interface is
implemented by a component that wants to be notified about events that occur to a
particular RowSet object. Such a component registers itself as a listener with a rowset
via the [Link] method.
o When the RowSet object changes one of its rows, changes all of it rows, or moves its
cursor, it also notifies each listener that is registered with it. The listener reacts by
carrying out its implementation of the notification method called on it.
o RowSetEvent
As part of its internal notification process, a RowSet object creates an instance of
RowSetEvent and passes it to the listener. The listener can use this RowSetEvent object
to find out which rowset had the event.
2. Metadata
RowSetMetaData
This interface, derived from the ResultSetMetaData interface, provides information about
the columns in a RowSet object. An application can use RowSetMetaData methods to find
out how many columns the rowset contains and what kind of data each column can
contain.
The RowSetMetaData interface provides methods for setting the information about
columns, but an application would not normally use these methods. When an application
calls the RowSet method execute, the RowSet object will contain a new set of rows, and
its RowSetMetaData object will have been internally updated to contain information about
the new columns.

3. The Reader/Writer Facility

A RowSet object that implements the RowSetInternal interface can call on the
RowSetReader object associated with it to populate itself with data. It can also call on
the RowSetWriter object associated with it to write any changes to its rows back to
the data source from which it originally got the rows. A rowset that remains
 connected to its data source does not need to use a reader and writer because it can
simply operate on the data source directly.

RowSetInternal
By implementing the RowSetInternal interface, a RowSet object gets access to its internal
state and is able to call on its reader and writer. A rowset keeps track of the values in its
current rows and of the values that immediately preceded the current ones, referred to
as the original values. A rowset also keeps track of (1) the parameters that have been set
for its command and (2) the connection that was passed to it, if any. A rowset uses the
RowSetInternal methods behind the scenes to get access to this information. An
application does not normally invoke these methods directly.

RowSetReader
A disconnected RowSet object that has implemented the RowSetInternal interface can
call on its reader (the RowSetReader object associated with it) to populate it with data.
When an application calls the [Link] method, that method calls on the rowset's
reader to do much of the work. Implementations can vary widely, but generally a reader
makes a connection to the data source, reads data from the data source and populates
the rowset with it, and closes the connection. A reader may also update the
RowSetMetaData object for its rowset. The rowset's internal state is also updated, either
by the reader or directly by the method [Link].

RowSetWriter
A disconnected RowSet object that has implemented the RowSetInternal interface can
call on its writer (the RowSetWriter object associated with it) to write changes back to the
underlying data source. Implementations may vary widely, but generally, a writer will do
the following:
 Make a connection to the data source
 Check to see whether there is a conflict, that is, whether a value that has been
changed in the rowset has also been changed in the data source
 Write the new values to the data source if there is no conflict
 Close the connection
The RowSet interface may be implemented in any number of ways, and anyone may
write an implementation. Developers are encouraged to use their imaginations in coming
up with new ways to use rowsets.
Type III Driver – WebLogic – BEA – [Link]
Type III Driver – WebLogic – BEA – [Link]
Type II & IV driver – Oracle DB - Oracle –

JDBC:
There are three types of statements in JDBC
Create statement : Is used to execute single SQL statements.
Prepared statement: Is used for executing parameterized quaries. Is used to run pre-
compiled SEQL Statement.
Callable statement: Is used to execute stored procedures.
Stored Procedures: Is a group of SQL statements that perform a logical unit and performs
a particular task.
Are used to encapsulate a set operations or queries t execute on data.
execute() – returns Boolean value
executeupdate( ) – returns resultset Object
executeupdate( ) – returns integer value

Loading the Driver:

[Link](“[Link]”);
Conn=[Link](“jdbc:odbc:dsn”, “username”, “password”);
( ORACLE Driver )
[Link](“[Link]”);
 Conn=[Link](“jdbc:oracle:thin:@[Link]:1521:dbn”,
“username”, “password”);
Data base connection:
Public static void main(String args[]);
Connection con;
Statement st;
Resultset rs;
try { // Getting all rows from Table
[Link](“[Link]”);
Conn=[Link](“[Link]”, “username” , ”password”);
st = [Link]( );
rs = [Link](“SELECT * FROM mytable”);
while([Link]());
{
String s= [Link](1); or [Link](“COL_A”);
int i = rs. getInt(2);
Float f = [Link](3);
Process(s,i,f);
}
catch(SQLException e)
{}
//Getting particular rows from Table
st = [Link]( );
rs = [Link](“SELECT * FROM mytable WHERE COL A = “Prasad”);
while([Link]( ));
{
String s = [Link](1);
Int i = [Link](2);
Float f = [Link](3);
Process(s,i,f);
}
Catch(SQLException e); { }
//updating a row from table.
try {
st = [Link]( );
int numupdated = [Link](“UPDATE mytable SET COL_A = “prasad” WHERE
COL_B=”746”);
rs = [Link]();
[Link](); }
catch(SQLExceptione); { }
// Receiving rows from table
try {
st = [Link]( );
rs = [Link](“SELECT * FROM mytable SET COL_A=?’);
int colunm=1;
[Link](colunm,”hari”);
rs = [Link]( );
//update rwo from table
st = [Link]( );
int numupdated = [Link](“UPDATE mytable SET COL_A =? WHERE COL_B=?”);
int column=1;
[Link](colunm,”Prasad”);
int column=2;
[Link](column,”746”);
int numupdated = [Link]( );
} catch(SqlException e); { }
//callable statement
try {
cst = [Link](“{call add1(??,??)}”);
 [Link](1,a);
[Link](2,b);
[Link](1,[Link]);
[Link]( );
[Link](“[Link]( )”); }

Connection Pool with webLogic server :

You can connect the database in your app using :
[Link](“[Link]”).newInstance();
[Link] conn = [Link](“jdbc:weblogic:Oracle:dbn”, ”username”,
“password”);
( Or )
[Link] prop = new [Link]( );
[Link](“user”, “hari”);
[Link](“password”,”prasad”);
[Link] d =
([Link])[Link](“[Link]”).newInstance( );
[Link] conn = [Link](“jdbc:weblogic:Oracle:dbn”, prop);

public static void main(String args[]) throws Exception

{
[Link] con=null;
[Link] st =null;
try {
context ctx=null;
Hashtable ht = new Hashtable( );
[Link](Context.INTIAL_CONTEXT_FACTORY,”weblogic:jndi:WLInitialContextFACTORY”);
[Link](Context_PROVIDER_URL,”t3://Localhost:7001”);
//get a context from JNDI lookup
ctx = newIntialContext( ):
[Link] ds =([Link])[Link](“OraclegbJNDI”);
con =[Link]( );
[Link](“Making Connection……”);
st = [Link]( );
}
finally {
try {
if(stmt !=null)
[Link]( );
if(stmt !=null)
[Link]( ); }
What is a transaction
transaction is collection of logical operation that perform a task
Transaction should ACID properties.
A for Automicity
C for Consistency
I for Isolation
D for Durability.
A transaction can be termed as any operation such as storing, retrieving, updating or
deleting records in the table that hits the database.
What is the purpose of setAutoCommit( )
It is set as
[Link]();
after any updates through the program cannot be effected to the [Link] have
commit the transctions .For this puprpose we can set AutoCommit flag to Connection
Object.
What are the three statements in JDBC & differences between them
which is used to run simple sql statements like select and update
2. PrepareStatment is used to run Pre compiled sql.
3. CallableStatement is used to execute the stored procedures.
What is stored procedure. How do you create stored procedure ?
Stored procedures is a group of SQL statements that performs a logical unit and
performs a particular task.
Stored procedures are used to encapsulate a set of operations or queries to
execute on data.
Stored Procedure is a stored program in database, PL/SQL program is a Stored
Procedure. Stored Procedures can be called from java by CallableStatement
A precompiled collection of SQL statements stored under a name and processed as
a unit.
Stored procedures can:
[Link] input parameters and return multiple values in the form of output parameters
to the calling procedure or batch.
[Link] programming statements that perform operations in the database, including
calling other procedures.
[Link] a status value to a calling procedure or batch to indicate success or failure (and
the reason for failure).
What are batch updates?
Batch Update facility allows multiple update operations to be submitted to a database for
processing at once. Using batch updates will improve the performance.
What is the difference between Resultset and Rowset
A RowSet is a disconnected, serializable version of a JDBC ResultSet.
The RowSet is different than other JDBC interfaces in that you can write a RowSet to be
vendor neutral. A third party could write a RowSet implementation that could be used
with any JDBC-compliant database. The standard implementation supplied by Sun uses a
ResultSet to read the rows from a database and then stores those rows as Row objects in
a Vector inside the RowSet. In fact, a RowSet implementation could be written to get its
data from any source. The only requirement is that the RowSet acts as if it was a
ResultSet. Of course, there is no reason that a vendor couldn't write a RowSet
implementation that is vendor specific.
The standard implementations have been designed to provide a fairly good range
of functionality. The implementations provided are:
CachedRowSetImpl - This is the implementation of the RowSet that is closest to the
definition of RowSet functionality that we discussed earlier. There are two ways to load
this RowSet. The execute ( ) method will load the RowSet using a Connection object. The
populate( ) method will load the RowSet from a previously loaded ResultSet.

WebRowSetImpl - This is very similar to the CachedRowSetImpl (it is a child class) but
it also includes methods for converting the rows into an XML document and loading the
RowSet with an XML document. The XML document can come from any Stream or
Reader/Writer object. This could be especially useful for Web Services.

JdbcRowSetImpl - This is a different style of implementation that is probably less useful

in normal circumstances. The purpose of this RowSet is to make a ResultSet look like a
JavaBean. It is not serializable and it must maintain a connection to the database.
The remaining two implementations are used with the first three implementations:
FilteredRowSetImpl - This is used to filter data from an existing RowSet. The filter will
skip records that don't match the criteria specified in the filter when a next() is used on
the RowSet.
JoinRowSetImpl - This is used to simulate a SQL join command between two or more
RowSet objects.

What are the steps for connecting to the database using JDBC
Using DriverManager:
1. Load the driver class using [Link](driverclass) and [Link]() loads the
driver class and passes the control to DriverManager class
2. [Link]() creates the connection to the databse
Using DataSource.
DataSource is used instead of DriverManager in Distributed Environment with the help of
JNDI.
1. Use JNDI to lookup the DataSource from Naming service server.
1. [Link] method will return Connection object to the database

What is Connection Pooling ?

Connection pooling is a cache of data base connections that is maintained in
memory , so that the connections may be reuse.
Connection pooling is a place where a set of connections are kept and are used by
the different programers with out creating conncections to the database(it means there
is a ready made connection available for the programmers where he can use). After
using the connection he can send back that connection to the connection pool. Number
of connections in connection pool may vary.

How do you implement Connection Pooling

Connection Pooling can be implemented by the following way.
* A [Link] interface that serves as a resource
manager connection factory for pooled [Link] objects. Each database
vendors provide the implementation for that interface.
For example, the oracle vendors implementation is as follows:
[Link] Class.
 A [Link] interface encapsulates the physical connection for
the database. Again, the vendor provides the implementation.

What [Link]( ) method will do

[Link]() is used to load the Driver class which is used to connect the
application with Database. Here Driver class is a Java class provided by Database vendor.
What is the difference between JDBC 1.0 and JDBC 2.0
The JDBC 2.0 API includes many new features in the [Link] package as well as the
new Standard Extension package, [Link]. This new JDBC API moves Java applications
into the world of heavy-duty database computing. New features in the [Link] package
include support for SQL3 data types, scrollable result sets, programmatic updates, and
batch updates. The new JDBC Standard Extension API, an integral part of Enterprise
JavaBeans (EJB) technology, allows you to write distributed transactions that use
connection pooling, and it also makes it possible to connect to virtually any tabular data
source, including files and spread sheets.

The JDBC 2.0 API includes many new features like

1. Scrollable result sets
2. Batch updates
3. Connection Pooling
4. Distributed transactions
5. set autocomit ( )
What is JDBC?
JDBC is a layer of abstraction that allows users to choose between databases. It allows
you to change to a different database engine and to write to a single API. JDBC allows
you to write database applications in Java without having to concern yourself with the
underlying details of a particular database.

What are the two major components of JDBC?

One implementation interface for database manufacturers, the other implementation
interface for application and applet writers.

What is JDBC Driver interface?

The JDBC Driver interface provides vendor-specific implementations of the abstract
classes provided by the JDBC API. Each vendors driver must provide implementations of
the [Link],Statement,PreparedStatement, CallableStatement, ResultSet and
Driver.
What are the common tasks of JDBC?
Create an instance of a JDBC driver or load JDBC drivers through [Link]
Register a driver
Specify a database
Open a database connection
Submit a query
Receive results
What packages are used by JDBC?
There are 8 packages: [Link], Connection,Statement, PreparedStatement,
CallableStatement, ResultSet, ResultSetMetaData, DatabaseMetaData.

What are the flow statements of JDBC?

A URL string -->getConnection-->DriverManager-->Driver-->Connection-->Statement--
>executeQuery-->ResultSet.
1). Register the Driver
2) load the Driver
3)get the connection
4) create the statement
5) Execute the query
6) fetch the results with ResultSet

What are the steps involved in establishing a connection?

This involves two steps: (1) loading the driver and (2) making the connection.

How can you load the drivers?

Loading the driver or drivers you want to use is very simple and involves just one line of
code. If, for example, you want to use the JDBC-ODBC Bridge driver, the following code
will load it:
Eg. [Link]("[Link]");
Your driver documentation will give you the class name to use. For instance, if the class
name is [Link] , you would load the driver with the following line of code:
E.g. [Link]("[Link]");

What [Link] will do while loading drivers?

It is used to create an instance of a driver and register it with the DriverManager.
When you have loaded a driver, it is available for making a connection with a DBMS.

How can you make the connection?

In establishing a connection is to have the appropriate driver connect to the DBMS. The
following line of code illustrates the general idea:
E.g.
String url = "jdbc:odbc:Fred";
Connection con = [Link](url, "Fernanda", "J8");
How can you create JDBC statements?
A Statement object is what sends your SQL statement to the DBMS. You simply
create a Statement object and then execute it, supplying the appropriate execute
method with the SQL statement you want to send. For a SELECT statement, the method
to use is executeQuery. For statements that create or modify tables, the method to use is
executeUpdate. E.g. It takes an instance of an active connection to create a Statement
object. In the following example, we use our Connection object con to create the
Statement object stmt :
Statement stmt = [Link]();

How can you retrieve data from the ResultSet?

First JDBC returns results in a ResultSet object, so we need to declare an instance of
the class ResultSet to hold our results. The following code demonstrates declaring the
ResultSet object rs.
E.g.
ResultSet rs = [Link]("SELECT COF_NAME, PRICE FROM COFFEES");
Second:
String s = [Link]("COF_NAME");
The method getString is invoked on the ResultSet object rs , so getString will retrieve
(get) the value stored in the column COF_NAME in the current row of rs

What are the different types of Statements?

1. Create Statement : For Simple statement used for static query.

[Link] Statement :For a runtime / dynamic query .Where String is a dynamic

query you want to execute

3. Callable Statement (Use prepareCall) : //For Stored procedure Callable statement,

where sql is stored procedure.
try
{
Connection conn = DriverManage