JDBC Session 3

1. JDBC Session 3 • Tonight’s topics: • Connection Pooling • Transaction Processing Redux • Distributed Transactions • RowSets • Yet more lab time! (Design Patterns next week.)

2. 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.

3. JDBC Session 3 Connection Pooling (p. 2) • To use connection pooling: • Set up server for connection pooling • 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

4. 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(); }}

5. 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

6. 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.

7. 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.

8. 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.

9. 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.

10. 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.

11. 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.

12. 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(); }}

13. 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.

14. 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

15. 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

16. 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.

17. 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.

18. 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