Object-Oriented Enterprise Application Development

1. Object-Oriented Enterprise Application Development Advanced JDBC

2. Topics • During this class we will examine: • Statement batching • DataSources • Connection pooling • Distributed transactions • RowSets

3. JDBC Extensions

4. JDBC 2.0 • JDBC 2.0 provides some standard extensions to the JDBC 1.0 API. • This includes some new interfaces as well as some changes to existing ones.

5. JDBC 2.0 Goal • The goal of JDBC 2.0 are to provide additional capabilities within the JDBC API to support advanced database functionality.

6. JDBC 2.0 Features • Some of the new features of JDBC 2.0 include: • Scrollable result sets • Updateable result sets • Batch updates • New SQL3-compatible data types.

7. JDBC 2.0 & Java

8. Required Packages • JDBC 2.0 includes a new javax.sqlpackage as well as some changes to the original java.sql package. • This new packages supports capabilities such as database connection pooling and distributed transactions.

9. Common Interfaces • We won't use all of the interfaces provided by JDBC 2.0 in this class. We'll focus on the most critical. • We’ll also revisit some of the interfaces from JDBC 1.0.

10. Result SetsRevisited

11. Enhancements • The ResultSet has been enhanced to make them scrollable and updateable. • This allows them to compete more effectively with other technologies such as the Visual Basic and PowerBuilder. • Vendors aren’t required to implement scrollable and updateable ResultSets to be JDBC 2.0 compliant.

12. Scrollability • A ResultSet can now have one (1) of four (3) possible scrolling attributes: • Forward and backward • Scroll insensitive • Scroll sensitive

13. Scroll-Sensitive • A ResultSet that’s scroll-sensitive is “live.” • As other users modify the data reflected in the ResultSet, the ResultSet can be changed to reflect the revised view of the data.

14. Scroll-Insensitive • A ResultSet that’s scroll-insensitive is a static view of the data. • If the data in the ResultSet is changed by other clients, the ResultSet isn’t updated accordingly.

15. Creating Scrollable ResultSets(1 of 3) • To create a scrollable ResultSet we use the overloaded createStatement() and prepareStatement() methods: Statement createStatement( int resultSetType, int resultSetConcurrency)throws SQLExceptionPreparedStatement prepareStatement( String SQL, int resultSetType, int resultSetConcurrency)throws SQLException

16. Creating Scrollable ResultSets(2 of 3) • Legal ResultSet types include: • TYPE_FORWARD_ONLY • TYPE_SCROLL_INSENSITIVE • TYPE_SCROLL_SENSITIVE • These values are defined in the ResultSet interface.

17. Creating Scrollable ResultSets(3 of 3) • Legal ResultSet concurrency include: • CONCUR_READ_ONLY • CONCUR_UPDATEABLE • These values are defined in the ResultSet interface.

18. Sample Code – Query (1 of 1) • Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver"); • conn = DriverManager.getConnection( "jdbc:odbc:se452", "", "" ); • Statement stmt = conn.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); • ResultSet rs = stmt.executeQuery( SQL );

19. Sample Code – Prepared Query (1 of 1) • Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver"); • conn = DriverManager.getConnection( "jdbc:odbc:se452", "", "" ); • PreparedStatement stmt = conn.preparedStatement( SQL, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); • ResultSet rs = stmt.executeQuery( );

20. Absolute Positioning • A ResultSet supports absolute positioning using the following methods: • boolean absolute(int row) • boolean first() • boolean last() • void afterLast() • void beforeFirst()

21. Relative Positioning • A ResultSet supports relative positioning using the following methods: • boolean relative(int row) • boolean next() • boolean previous()

22. Testing Position • A ResultSet allows you to test for various positions: • boolean isAfterLast() • boolean isBeforeFirst() • boolean isFirst() • boolean isLast() • int getRow()

23. Updateability(1 of 2) • A ResultSet can be updated. • Changes made to the ResultSet object will be reflected in the underlying data store. • To be updateable, the ResultSet must have a concurrency type of CONCUR_UPDATEABLE.

24. Updateability(2 of 2) • Only queries that meet the following restrictions can have an updateable ResultSet: • The query references a single table. • The query returns the primary key. • The query returns all non-null columns that do not also have a default value.

25. Updating Rows(1 of 3) • There are methods provided for updating each particular type of column. • Examples include:void updateString(int col,String val)throws SQLExceptionvoid updateString(String col,String val)throws SQLException

26. Updating Rows(2 of 3) • Although the data in the row has been changed, those changes haven’t yet been pushed out to the database. • To save the changes to the underlying data store, use the updateRow() method.void updateRow()throws SQLException

27. Updating Rows(3 of 3) • If any of the positioning methods are invoked on the ResultSet prior to the call to the updateRow() method, then all changes made to that row will be discarded.

28. Deleting Rows • To delete the current row from the underlying data store, use the deleteRow() method.void deleteRow()throws SQLException

29. Inserting Rows(1 of 5) • Inserting a new row consists of four (4) steps: • Call the moveToInsertRow() method • Update the columns within the new row • Call the insertRow() method • Returning to the original row in the ResultSet

30. Inserting Rows(2 of 5) • First call the the moveToInsertRow() method. • This creates a new “buffer” area within the ResultSet for the new row. • It isn’t legal to call the updateRow() or deleteRow() method while the cursor is positioned on the insert row.

31. Inserting Rows(3 of 5) • Next use the same update() methods used to update the ResultSet. • This sets the various data members within the new row. • If you navigate off of this new row, your changes will be discarded.

32. Inserting Rows(4 of 5) • Next call the insertRow() method. • This performs the physical insertion of the row into the underlying data store. • Any values that haven’t been set on columns in the ResultSet are assumed to be null and will be interpreted that way by the data store.

33. Inserting Rows(5 of 5) • Finally call the moveToCurrentRow() method. • This returns you to the row on which you were positioned prior to beginning the insertion process.

34. Batch Updates

35. Features(1 of 2) • A batch update allows multiple statements to be executed against the database at one time. • These statements are not executed as part of the same transaction unless setAutoCommit() has been invoked with a value of false.

36. Features(2 of 2) • Batch updates are not meant for SQL select operations. • To construct a batch, use the addBatch() methods: void addBatch(String SQL)throws SQLException void addBatch()throws SQLException

37. Sample Code – Batch (1 of 2) • import java.sql.*; • public class Batch { • public static void main(String [] args) { • Class.forName("JData2_0.sql.$Driver"); • Connection conn = DriverManager.getConnection( "jdbc:JDataConnect://localhost/se452"); • Statement stmt = conn.createStatement();

38. Sample Code – Batch (2 of 2) • stmt.addBatch( "insert into STATE values ('SK','Ski')"); • stmt.addBatch( "insert into STATE values ('FD','Fre')"); • int [] rows = stmt.executeBatch(); • System.out.println("1st: " + rows[0]); • System.out.println("2nd: " + rows[1]); • stmt.close(); • conn.close(); • } • }

39. Exceptions • Each query within the batch might result in an exception. • Rather than throw a SQLException, the executeBatch() method can throw the BatchUpdateException. • The getUpdateCounts() method on this exception tells you how many rows were updated.

40. Database Connectivity Revisited

41. DataSources • Connection allocation in JDBC 2.0 is usually implemented using the DataSource interface. • The approach is to place the DataSource reference into a naming service. • The client application then retrieves this reference and uses it to create connections.

42. Sample Code – DataSource (1 of 1) • import java.sql.*; • import javax.sql.*; • import com.NetDirect.Jdbc.*; • public class StateDataSource { • public static void main(String [] args) throws Exception { • JDataSource jds = new JDataSource(); • jds.setURL( "jdbc:JDataConnect://localhost/se452"); • DataSource ds = jds; • Connection conn = ds.getConnection(); • // do database stuff…. • } • }

43. Database Connectivity • Opening a database connection is an expensive process in terms of machine resources and wall-clock time. • As a general rule we open as few of these connections as possible. • With JDBC 2.0 we can create a connection pool that allows us to reuse connections.

44. Connection Pool API • The javax.sql package defines several new interfaces to support the use of connection pools: • ConnectionPoolDataSource • PoolableConnection • ConnectionEventListener • The use of these interfaces is vendor specific.

45. Connection Pooling(1 of 2) • Provides a cache, or pool, of prefabricated database connections. • When a client needs a database connection, it goes to the pool. • When the client is done with the connection, that connection is returned to the pool.

46. Connection Pooling(2 of 2) • Pools increase performance by avoiding the cost of creating new connections for each client. • Pools decrease resource usage by using a small number of connections to service a large number of clients.

47. Transactions Revisited

48. Defined • A distributed transaction is one that spans two (2) or more DataSource references. • Such transactions make use of middle-tier software called a transaction manager. • This manager takes over the responsibility for transaction demarcation. This makes it illegal for a developer to provide their own transaction support.

49. Distributed Transaction API • The javax.sql package defines several interfaces to support distributed transactions: • XADataSource • XAConnection • DataSource • The use of these interfaces is vendor specific.

50. Participants(1 of 3) • A resource is some participant that has transactional state. • Examples include: • Database connections • JMS message queue connections • Our application objects typically don't have transactional state unless we implement it programmatically.