Welcome! [www.web

download report

Transcript Welcome! [www.web

JDBC Session 3
Tonight’s topics:
1. Connection Pooling
2. Transaction Processing Redux
3. Distributed Transactions
4. RowSets
5. Yet more lab time! (Design Patterns next week.)
JDBC Session 3
Connection Pooling
•
•
•
•
•
(p. 1)
Connection Pooling is a Server-side technology
– Used with application servers (EJB containers) such as WebSphere,
WebLogic, JBoss
– May also be used with servlet containers such as Tomcat
– Requires server-side use of a JNDI naming service
– The Pool may then be used by servlets, JSPs & EJBs that reside on the
server and hence have access to the JNDI service.
It is extremely expensive to open and close database connections every time a
user requests a connection to carry out a SQL statement.
With connection pooling, the application server maintains a pre-defined pool
of “open” database connections that are shared between application clients
(servlets, JSPs & EJBs).
When the client creates a connection via a DataSource object, the connection
will come out of the pool.
When a client closes a connection, the connection is returned back to the pool.
JDBC Session 3
Connection Pooling
(p. 2)
To use connection pooling:
1. Set up server for connection pooling
2. Client (servlet, JSP or EJB) gets connection from pool
Setting up the server:
• Uses these packages, classes & interfaces:
- javax.sql.DataSource
- javax.naming.* ( JNDI )
- javax.sql.ConnectionPoolDataSource
- javax.sql.PooledConnection
•
See lessons page of web site for examples
JDBC Session 3
Connection Pooling
(p. 3)
Client (servlet, JSP or EJB) code for getting a pooled connection:
import java.sql.Connection;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;
Connection conn = null;
try {
Context
ctx = new InitialContext();
DataSource ds = (DataSource) ctx.lookup("jdbc/MurachBooks");
Connection conn = ds.getConnection( "myLogin", "myPassword");
}
catch (Exception e) { e.printStackTrace(); }
try {
PreparedStatement ps = conn.prepareStatement( “select * from file” );
ResultSet rs = ps.executeQuery();
while (rs.next()) { ... }
}
catch (SQLException e) { e.printStackTrace(); }
finally {
try { if( conn != null ) conn.close(); }
catch (SQLException e2) { e2.printStackTrace(); }
}
JDBC Session 3
Transaction Processing Redux (p. 1)
A transaction is a sequence of SQL statements that performs one Logical Unit of
Work (LUW).
For example, let's say that a customer orders an item from your inventory. You can
write a single SQL transaction that updates both the customer table and the
orders table at the same time.
Each transaction has the following four (ACID) properties:
• atomicity
• consistency
• isolation
• durability
JDBC Session 3
Transaction Processing Redux (p. 2)
The atomicity of a transaction means that if any part of it fails, the entire
transaction is aborted. So the transaction is committed only if each part of it
executes successfully.
Consider a transaction atomic if it executes either completely or
not at all.
Let's say you want execute a transaction that transfers $300 from a customer's
savings account to their checking account.
Suppose that the debit statement commits but the credit statement aborts.
In this case atomicity prevents the customer from losing money, because it causes
the entire transaction to be rolled back.
JDBC Session 3
Transaction Processing Redux (p. 3)
The isolation property ensures that transactions don't interfere with each other's
processing.
Consider a transaction isolated if the transaction executes
serially. In other words, it should appear as if the transaction
runs alone with no other transaction occurring
simultaneously. This guarantees data integrity.
While you are executing a transaction, isolation guarantees that no other
transaction can modify the data that your transaction has changed.
It does this by putting an exclusive lock on your data. This lock is not released
until you commit or roll back the transaction.
JDBC Session 3
Transaction Processing Redux (p. 4)
The durability of transactions ensures that all changes made to the databases by a
transaction are permanent once that transaction is committed.
Consider a transaction durable if a permanent record of the
transaction persists. This may sound obvious, but for
optimization purposes transactional records are often kept in
memory. However, the transaction cannot be considered ACID
until the data is written to permanent storage.
Durability ensures that data integrity is maintained even if a system failure occurs
during a transaction. A system failure includes such events as a hardware fault
or power loss.
JDBC Session 3
Transaction Processing Redux (p. 5)
The consistency of a transaction ensures that when it is committed, all relevant
databases are in a consistent state.
Although second on the list, the last term of an ACID transaction
to consider is consistent. A transaction ensures consistency if
it is atomic, isolated, and durable. If an airplane possesses 10
seats and each seat sells for $100, then at the end of 10
successful transactions the airline's account should have
$1,000 more than it did when it started. If this is the case,
the database is in a consistent state.
And consistency ensures that if a transaction is aborted, all databases roll back to
the state they were in before the transaction began.
JDBC Session 3
Transaction Processing Redux (p. 6)
Let's say that you want to write Java transaction processing code to execute and
commit a SQL transaction using Java Database Connectivity (JDBC).
You must open a connection to the database before you can do this. You call the
getConnection method of the DriverManager class to open a database
connection.
You must carry out the following three phases for each SQL statement:
• execute
• complete
• commit
A SQL statement is complete when you have retrieved all its result sets and update
counts. And it is committed when it executes its instructions on the database.
The database changes that result from the commit method are permanent.
JDBC Session 3
Transaction Processing Redux (p. 7)
In most cases each SQL statement is completed immediately after it is executed.
When you create a connection, its default auto-commit mode is enabled. This
causes each SQL statement to be automatically committed immediately after
it is completed.
In other words, each SQL statement is treated as a transaction.
You can group two or more SQL statements into a transaction, provided you have
disabled auto-commit.
When you disable auto-commit, you are in what is called transaction mode.
You need to re-enable auto-commit when you have completed committing your
transactions. By doing this, you release any exclusive locks that your
transactions may have generated. Calling the rollback method also releases
any database locks that the connection holds.
JDBC Session 3
Transaction Processing Redux (p. 8)
Here’s some DriverManager transaction mode code:
Connection connect2 = null;
connect2 = DriverManager.getConnection(url, usrID, usrPass);
connect2.setAutoCommit(false);
try {
// Calls to prepared statements here to update database tables.
connect2.commit();
connect2.setAutoCommit(true);
}
catch( SQLException ex ) {
System.err.println( "SQLException: " + ex.getMessage() );
if (connect2 != null) {
try {
System.err.println( "Rolling back transaction…“ );
connect2.rollback();
}
catch( SQLException exp ) { System.err.println("SQLException: " exp.getMessage()); }
}
}
finally {
try { if(connect2 != null ) connect2.close(); }
catch (SQLException e2) { e2.printStackTrace(); }
}
JDBC Session 3
Transaction Processing Redux (p. 9)
The Connection object also lets you set the level of “transaction isolation”:
Connection connect2 = null;
connect2 = DriverManager.getConnection(url, usrID, usrPass);
connect2. setTransactionIsolation(int level);
Where (int level) is one of the Connection field constants:
static int TRANSACTION_NONE
Indicates that transactions are not supported.
static int TRANSACTION_READ_COMMITTED
Dirty reads are prevented; non-repeatable reads and phantom reads can occur.
static int TRANSACTION_READ_UNCOMMITTED
Dirty reads, non-repeatable reads and phantom reads can occur.
static int TRANSACTION_REPEATABLE_READ
Dirty reads and non-repeatable reads are prevented; phantom reads can occur.
static int TRANSACTION_SERIALIZABLE
Dirty reads, non-repeatable reads and phantom reads are prevented.
Refer to the Connection API for details.
JDBC Session 3
Distributed Transactions (p. 1)
•
Like Connection Pooling, Distributed Transactions are a Server-side
technology
– Used with application servers
– Requires server-side use of Java Transaction API (JTA)
– Distributed Transactions may then be used by servlets, JSPs & EJBs that
reside on the server and hence have access to the JTA service.
•
If a client needs to perform operations across multiple data bases in the same
transaction – or – if two or more servlets or EJBs need to participate in the
same transaction, then we are beyond the realm of DriverManager
transactions and enter – the twilight zone – I mean, the world of Distributed
Transactions.
•
JTA will then let you programmatically control transactions across multiple
databases
JDBC Session 3
Distributed Transactions (p. 2)
Setting up the server to use distributed transactions:
• Uses these packages, classes & interfaces:
- javax.sql.XAConnection
- javax.sql.XADataSource
- javax.naming.* ( JNDI )
- javax.transaction.* ( JTA )
- javax.transaction.xa.* ( JTA )
• Similar process on server as setting up Connection Pooling
• Distributed transactions covered in detail in WROX Chapter 17
• See lessons page of web site for links
JDBC Session 3
Distributed Transactions (p. 3)
Client (servlet, JSP or EJB) code for getting Connection with
distributed transaction capability:
•
See WROX pp. 221-222.
JDBC Session 3
Distributed Transactions (p. 4)
Locking Strategies for Distributed Transactions
•
•
•
In designing distributed multiuser applications, sharing objects in real time is essential, but it can
lead to resource sharing conflicts. In such conflicts, a user or process can change an object's state
while another user or process is using the object. Database managers solve this problem using
various locking strategies.
You can employ one of two types of locking strategies: pessimistic or optimistic locking.
–
Pessimistic Locking guarantees the highest level protection. Developer asks the database to
lock the record until the application is done modifying it. No one else can read or write to
the record until it is done. This is NOT a scalable solution.
–
Optimistic Locking lets the user retrieve any data they want. However, when the user
wants to perform an update on the data, they must compare an ID or timestamp value
between the modified copy of the record and the existing record on file. If the modified
record does not have a newer ID or timestamp an exception is thrown. Note, however, that
optimistic locking is not part of the J2EE specification and is not supported by all
application servers. For example, JBoss does not currently support optimistic locking.
See lessons page of web site for links.
JDBC Session 3
RowSets
javax.sql.RowSet
•
•
Extends java.sql.ResultSet
– Used primarily in EJBs
– Manipulates tabular data sources
– Serializable
See lessons page of web site for link to tutorial