Joel Brown's Weblog

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

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.

Sample

        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" ) ;
            rs.next() ;
            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);
                }
            }
        }

Posted at 02:20PM Nov 16, 2005 by jfbrown in Database Stuff  |  Comments[3]