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.sql package 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.
• Well 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 arent 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 thats 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 thats scroll-insensitive is a static
  view of the data.
• If the data in the ResultSet is changed by other
  clients, the ResultSet isnt 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)throw
  s SQLExceptionPreparedStatement
  prepareStatement( String SQL, int
  resultSetType, int resultSetConcurrency)throw
  s 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)

1. Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver")
2. conn DriverManager.getConnection(
   "jdbcodbcse452", "", "" )
3. Statement stmt conn.createStatement(
   ResultSet.TYPE_SCROLL_INSENSITIVE,
   ResultSet.CONCUR_READ_ONLY)
4. ResultSet rs stmt.executeQuery( SQL )

19
Sample Code Prepared Query (1 of 1)

1. Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver")
2. conn DriverManager.getConnection(
   "jdbcodbcse452", "", "" )
3. PreparedStatement stmt conn.preparedStatement
   ( SQL, ResultSet.TYPE_SCROLL_INSENSITIVE,
   ResultSet.CONCUR_READ_ONLY)
4. 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 includevoid 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 havent 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 isnt 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 havent 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)

1. import java.sql.
2. public class Batch
3. public static void main(String args)
4. Class.forName("JData2_0.sql.Driver")
5. Connection conn DriverManager.getConne
   ction( "jdbcJDataConnect//localhost/se452"
   )
6. Statement stmt conn.createStatement()

38
Sample Code Batch (2 of 2)

1. stmt.addBatch( "insert into STATE values
   ('SK','Ski')")
2. stmt.addBatch( "insert into STATE values
   ('FD','Fre')")
3. int rows stmt.executeBatch()
4. System.out.println("1st " rows0)
5. System.out.println("2nd " rows1)
6. stmt.close()
7. 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)

1. import java.sql.
2. import javax.sql.
3. import com.NetDirect.Jdbc.
4. public class StateDataSource
5. public static void main(String args) throws
   Exception
6. JDataSource jds new JDataSource()
7. jds.setURL( "jdbcJDataConnect//localhos
   t/se452")
8. DataSource ds jds
9. Connection conn ds.getConnection()
10. // 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.

51
Participants(2 of 3)

• A resource manager is some participant that
  controls and manages some resource.
• Examples include
• DriverManager references
• DataSource references
• The resource manager will ultimately be
  responsible for instructing a given resource to
  perform its transactional processing.

52
Participants(3 of 3)

• A transaction manager is some participant that
  controls and manages a transaction across
  multiple resource managers.
• By interacting with the transaction manager we
  can control the scope and duration of a
  distributed transaction.

53
JTA and JTS

• When building a transaction service, a vendor may
  choose to provide implementations of
• JTA Java Transaction API
• JTS Java Transaction Service

54
RowSets
55
RowSet API

• The javax.sql package defines several interfaces
  to support RowSets
• RowSet
• RowSetInternal
• RowSetListener
• RowSetMetaData
• RowSetReader
• RowSetWriter

56
Features(1 of 2)

• A RowSet is an extension of a ResultSet that
  provides data in accordance with the JavaBeans
  API.
• This class won't provide an in-depth examination
  of JavaBeans.
• We'll see them again briefly in our discussions
  of JavaServer Pages.

57
Features(2 of 2)

• A RowSet can be registered with a RowSetListener
  object.
• Such a listener can report on changes to the
  RowSet such as
• Database cursor movement
• Single-row changes
• RowSet changes

58
Sample Code RowSet (1 of 2)

1. import java.sql.
2. import javax.sql.
3. import com.NetDirect.Jdbc.
4. public class StateRowSet
5. public static void main(String args) throws
   Exception
6. JDBCRowset jrs new JDBCRowset()
7. jrs.setCommand("SELECT FROM STATE")
8. jrs.setUrl( "jdbcJDataConnect//localhos
   t/se452")
9. jrs.execute()

59
Sample Code RowSet (2 of 2)

1. while ( jrs.next() )
2. System.out.println("STATE_I "
   jrs.getString("STATE_I"))
3. System.out.println("STATE_M "
   jrs.getString("STATE_M"))
5. jrs.close()

60
Review

• During this class we have discussed
• Statement batching
• DataSources
• Connection pooling
• Distributed transactions
• RowSets

61
Resources

• JDBC Database Access with JavaGraham Hamilton,
  Rick Cattell, Maydene Fisher, Addison-Wesley,
  1997.ISBN 0-201-30995-5
• Database Programming with JDBC and Java, 2nd
  EditionGeorge Reese, OReilly, Inc., 2000.ISBN
  1-56592-616-1

62
Coming Attractions

• Next week we'll begin looking at Servlets.
• Please read Chapters 1-2 in your text.