Wednesday Jan 25, 2006

Using RowSets for CRUD, or Not

I've noticed a few confusing forum posts on just what the heck our CachedRowSet is capable of.

CachedRowSet implements ResultSet.  The ResultSet javadoc describes itself as " A table of data representing a database result set, which is usually generated by executing a statement that queries the database. "  (the emphasis is mine.)

It works by first getting the "rows" of data from the database.  This is done by executing a database query, i.e. an sql SELECT statement.  Note:  stored procedures can also return a ResultSet, but we do not currently support stored procs.

You cannot set the command of a CachedRowSet to an sql UPDATE or DELETE statement.  These statements do not return a ResultSet when executed.

After you have a populated CachedRowSet (again, because you've executed some SELECT statement), you can then update or delete rows that exists within the CachedRowSet, or you can insert entirely new rows.  To propagate your changes to the database, call acceptChanges().   You can also do the update or delete of rows through the DataProvider interface.  For more info on doing this, see the tutorials for Accessing Databases on the Java Studio Creator Tutorials page.

But what if you want to batch update many rows, let's say reduce every manager's salary by 10%:

update EMP_TBL set salary=salary\*.90 where title='manager'

This single UPDATE statement is efficient - it's all done within the database server process. 

However, to do this with a CachedRowSet is not efficient.   First, you'd have to compose a select statement that included the primary key and the column(s) to be updated.  You execute it.  All rows to be updated get transferred to the web server  where your code resides.  Now your code has to loop through each row, setting the new salary value.  Lastly, you acceptChanges().  If you look at the logging for what happens during the acceptChanges(), you'll see we do one SELECT and one UPDATE for each row in the CachedRowSet that has changed.

For a twenty row update, the single UPDATE statement sends one single command to the db server.  The CachedRowSet solution does the initial select, creates a bunch of java objects, then does 20 SELECT and 20 UPDATE commands to the db server. 

So how do you that single UPDATE within Creator?  Use plain old JDBC.

For the next release, we hope to provide a framework or at least helper classes to assist you.  But for now, you're on your own.

One final note: our CachedRowSet is implemented with class CachedRowSetXImpl (which implements our own CachedRowSetX).  The eXtension of jdbc's CachedRowSet adds the advanced properties you see in the CachedRowSet's property sheet; most of these properties are used to work around problems in database driver metadata, or allow fine tuning of the generated INSERT and UPDATE statements.

Wednesday Nov 16, 2005

Using JDBC in Creator

There's nothing special about using JDBC from within a Creator Web Application. - just get your connection and away you go.  You can google the net for all sorts of advice on using jdbc.  And there's always those book thingies.

Some notes:

Getting the connection from the app server's connection pool. 

For the bundled Application Server 8.1 PE, or whatever app server you plan to use, it's likely that you will be using database connection pools.  Getting a connection from the pool is usually done through a JNDI lookup.  For example, if you wish to use the creator data source "Travel", just do this:

import javax.sql.DataSource;
import java.sql.Connection;
        Connection conn = null ;
        // the following should be in a try-catch...

javax.naming.Context ctx = new javax.naming.InitialContext() ;
     DataSource ds = (DataSource)ctx.lookup("java:comp/env/jdbc/Travel") ;
     conn = ds.getConnection() ;

Setup your connection

Because you are likely using your app/web server's connection pool, set your connection properties at the start. For example, to assure the connection behaves the way you desire, call setAutoCommit().

Cleanup your connection and JDBC objects when finished.

Commit or rollback your transaction.

Close all your JDBC objects. Close each Statement and ResultSet, then close the connection.  Put this code in a finally block to make sure it's executed.

Don't assume a close() on the connection will do all of the above.  If the connection is from a pool, the jdbc driver never sees the close().

Can I use the same Data Source for my CachedRowSet(s) and for my own JDBC stuff?


Having Creator setup your Data Source in the bundled App Server

For test deploys to Creator's bundled App Server, Creator will configure any data sources used by your CachedRowSet properties.  If you need a Data Source that's not used by a CachedRowSet, you can tell crreator to handle it by going to the Projects Pane, select the "Data Source References" node, then right-click "Add a Data Source Reference".

Synchronizing a CachedRowSet with my manual JDBC changes

Suppose you have a Table component displaying data from a CachedRowSet.  You need to do some complex operation that updates/deletes/inserts data.  How do you need to synchronize the data in the CachedRowSet?  Answer:  that's up to you.  The easiest way is to just re-execute the CachedRowSet.  Otherwise you'll need to iterate through the CachedRowSet, or use a wrapping CachedRowSetDataProvider, to manually update the CachedRowSet.

When should I use CachedRowSets and when should I use JDBC?

That's up to you.  A CachedRowSet is good for retrieving data, and it's easy to use for simple updates/inserts/deletes of that data.  However, it doesn't do everything, so don't hesitate to use plain old JDBC if that's what is needed.


        Connection conn = null ;
        Statement sqlStatement = null ;
        ResultSet rs = null ;
        try {
            javax.naming.Context ctx = new javax.naming.InitialContext() ;
            DataSource ds = (DataSource)ctx.lookup("java:comp/env/jdbc/Travel") ;
            conn = ds.getConnection() ;
            // setup the connection
            conn.setAutoCommit(false) ;
            // execute the query
            sqlStatement = conn.createStatement() ;
            rs = sqlStatement.executeQuery("select count(\*) from TRIP" ) ;
            int rows = rs.getInt(1) ;
            conn.commit() ;
            info("Rows in table TRIP: " + Integer.toString(rows)) ;
        } catch (Exception ex) {
            error("Error counting rows: " + ex.getMessage() );
            try {
                if ( conn != null ) {
                    conn.rollback() ;
            } catch (SQLException sqle) {
                log("Error on rollback " + sqle.getMessage() );
        finally {
            // close the ResultSet
            if ( rs != null ) {
                try {
                    rs.close() ;
                } catch (Exception ex) {
                    log("Error Description", ex);
            // close the statement
            if ( sqlStatement != null ) {
                try {
                    sqlStatement.close() ;
                } catch (Exception ex) {
                    log("Error Description", ex);
            if ( conn != null ) {
                // cleanup and close the conneciton.
                try {
                    conn.close() ;    
                } catch (Exception ex) {
                    log("Error Closing connection ", ex);

Tuesday Oct 11, 2005

Logging Database Access

Last Friday I received a bug that was very vague in it's description - "Creator pages seem slow".  My first guess was that it was an application issue, but there wasn't a way to prove it.  We need to see what sql is being executed and when.

So I went through our CachedRowSet implementation to beef up the logging.  Now I know my esteemed co-workers opinion is to don't log, debug.  I'll just say I disagree.  There are reasons to log at run time and "cross-boundary" tracking (such as shipping sql off to a database) is a common one.  With the FCS version, you'll be able to see

  • when a query is executed, including it's actual SELECT statement and any input parameters
  • what actually happens within acceptChanges().  You might  be surprised - a select will precede every update or delete of a row.

What it doesn't show is the actual time it takes to execute the statement.  To see this information, check the rowset property "printStatements".  Oh, and the output is rather ugly as I'm more substance over style.

Back to the bug.   Yuck.  I've just exposed a performance problem.  It turns out we were executing rowsets when we didn't need to, sometimes multiple times.  So if you think your web pages that access your database is slow in EA or EA2, it'll be better with the production release.







« April 2014

No bookmarks in folder