Cache Your Data: JDBC vs. ADO.NET

Most applications use some means of storing data whether it be a flat or XML file, an object-based database, or a relational database any one of which can be designed to fit your application needs.

Since the data is usually physically separated from your application you need to somehow fetch and bring it to your application before it can be displayed to users, manipulated and saved back to your data source. In this article, we will compare and contrast the use of Java and .NET to communicate with a remote relational database.

Disconnected vs. Connected

In the connected data model your application opens a connection to the remote database and retrieves the data then leaves the connection open.

Changes to each row are written back to the database for all users to see as the changes are made. The data is streaming back and forth as it is manipulated. In the disconnected model, a connection is opened to the remote database; the data is retrieved and cached in application objects while the connection is closed. You can then edit the data as you see fit, open a connection back to the database, commit the changes resolving any differences between the application version of the data and the version that in the database.

Advantages of Caching Your Data

Since you are not streaming between your application and your database, you are saving a considerable amount of network traffic thereby speeding up the displaying of data to the user. However, since you are caching a substantial amount of data in RAM you may run out of memory especially if you are running a website that gets lots of traffic. If your application retrieves enough records into memory, say by a high number of users simultaneously requesting a page containing 1,000 records from the server, this can cause your website to run out of memory.

When to Use Data Caching

Whenever you are working with a desktop application feeding off data in a remote server on the Internet, network traffic is a major consideration. Let’s say you have returned 1,000 records and you are busy editing a grid when your network connection goes off line. (This happens more frequently than you may think.) A connected model will give you an error message every time the server is not available. In the disconnected model, you cache the data, edit it as you see fit, then wait until your network goes back online to save it to the database. Caching the data is also useful in procedures that need the same data multiple times when processing data in complex calculations. For example: on a payroll system, when you need the exact year-to-date income of a person to calculate five different tax types, it is more efficient for your program to have the data cached inside your application instead of making five round trips to the database server.

You can also cache complete web pages for a desired amount of time so data that is just being displayed without editing will be cached on your web server.

When not to Use Data Caching

As a rule of thumb, the cached model is not to be used on a single tiered web application. It is not necessary for a web page to cache data since at the end of an HTTP Request, the page unloads and the objects are being destroyed anyway. Be wary of the cached model when running a desktop application on a machine with limited memory, or do not retrieve too many records at once. To limit the amount of data retrieved, use paging in your stored procedures or your objects whenever you can.

A Side-By-Side Look at Our Classes and Passages in JDBC and ADO.NET

We are going to be using Microsoft’s SQL Server for our examples and working with the following packages:

VB.NET
   using System.Data;
   using System.Data.SqlClient;

   JAVA
   import java.sql.*;
   import javax.sql.rowset.*;

The idea behind both Sun and Microsoft’s data caching is similar even though the implementation differs.
In both technologies we have objects that hold data and helper objects designed to communicate between the data base and the cached in memory representation of the data.

Microsoft’s uses the DataSet class to represent the data in memory. A dataset can hold multiple tables of data represented by DataTable classes. The DataAdapter object is used to communicate between dataset and the database. The adapter is in charge of selecting, updating, inserting, and deleting data according to the differences between the cached data and the data base. The DataRelation object represents the relations between the different tables in the Dataset. For example: if we are selecting all the categories in a certain data base and all the products belonging to the categories, we will probably want to match the category ID in our products table to our category ID in our category table.

Sun’s implementation is a little different. Instead of a master Dataset class holding everything, it uses CachedRowSet classes that can be populated from a ResultSet class to hold tables and the JoinRowSet class designed specifically for connecting related tables. Each table uses a SyncProvider to communicate to the data base by using a RowSetReader and a RowSetWriter. If we were to put our main classes side by side it would probably look something like this:

ADO.NET JAVA DataSet JoinRowSet DataTable (DataRow, DataColumn) CachedRowSet DataAdapter( SelectCommand, InsertCommand, DeleteCommand, UpdateCommand) SyncProvider(RowSetReader on the ResultSet and RowSetWriter)

Selecting Data

To retrieve data with ADO.NET, open a new SqlConnection, define a new SqlDataAdapter, add a SqlCommand to the adapter, define a new DataSet, and fill it out using our adapter.

VB.NET
   Dim ConnectionStr As string = "database=InvertedSoftware;server= InvertedSoftware "
   Dim objConnection As SqlConnection = new SqlConnection(ConnectionStr)
   Dim categoryDataAdapter As SqlDataAdapter = new SqlDataAdapter()
   categoryDataAdapter.TableMappings.Add("Table", "Category")
   objConnection.Open()
   Dim objCommand As SqlCommand = new SqlCommand("SELECT * FROM tbl_category", objConnection)
   objCommand.CommandType = CommandType.Text
   categoryDataAdapter.SelectCommand = objCommand
   Dim objDataSet As DataSet = New DataSet("Inventory")
   categoryDataAdapter.Fill(objDataSet)

As we mentioned before, a data set can hold multiple tables, so let’s go ahead and add a second table into our DataSet set using the same procedure.

VB.NET
   Dim productDataAdapter As SqlDataAdapter = new SqlDataAdapter()
   productDataAdapter.TableMappings.Add("Table", "Product")
   Dim objCommand2 As SqlCommand = new SqlCommand("SELECT * FROM tbl_product", objConnection)
   productDataAdapter.SelectCommand = objCommand2
   productDataAdapter.Fill(objDataSet)
   objConnection.Close()

Creating a relationship between two tables looks like this:

VB.NET
   Dim dr As DataRelation
   Dim dc1 As DataColumn
   Dim dc2 As DataColumn
   dc1 = ds.Tables("Category").Columns("category_id")
   dc2 = ds.Tables("product").Columns("category_id")
   dr = new System.Data.DataRelation("productsForCategory", dc1, dc2)
   objDataSet.Relations.Add(dr)

Now using Sun’s CachedRowSet, let’s populate CachedRowSet with a standard ResultSet using a SQL Statement. We can cache another statement and now add them both to our JoinRowSet using our Match Column.

JAVA
   String strUrl = "jdbc:microsoft:sqlserver://InvertedSoftware.com:1433;DatabaseName=InvertedSoftware";
   String strUserId = "USER";
   tring strPassword = "PASSWORD";
   String className = " com.microsoft.jdbc.sqlserver.SQLServerDriver ";
   try {
   Class.forName(className);
   } catch(java.lang.ClassNotFoundException e) {
   System.err.println(e.getMessage());
   }
   try {

     Connection con = DriverManager.getConnection(strUrll, strUserId, strPassWord);
     Statement stmt = con.createStatement();
     ResultSet rs1 = stmt.executeQuery("SELECT * FROM tbl_category");
     ResultSet rs2 = stmt.executeQuery("SELECT * FROM tbl_product");
     CachedRowSet categoryCachedRowSet = new CachedRowSetImpl();
     categoryCachedRowSet.populate(rs1);
     CachedRowSet productCachedRowSet = new CachedRowSetImpl();
     productCachedRowSet.populate(rs2);
     con.close();
     JoinRowSet jrs = new JoinRowSetImpl();
     jrs.addRowSet(categoryCachedRowSet, "category_id");
     jrs.addRowSet(productCachedRowSet, "category_id");
     jrs.close();
   } catch(SQLException sqle) {
   }

Updating Data

Using ADO.NET we can create a data adapter and add an update command to it. (A different way would be to add a select command and use a SqlCommandBuilder to generate our corresponding update and delete commands, but this is an entirely different subject and a topic for another article.) Now let’s take our already existing data set and use the adapter to update the data.

VB.NET
   categoryDataAdapter.UpdateCommand = New SqlCommand("UPDATE tbl_category SET category_name = @CategoryName WHERE category_id = @CategoryID", objConnection)
   categoryDataAdapter.UpdateCommand.Parameters.Add("@CategoryName", SqlDbType.NVarChar, 15, "CategoryName")
   Dim IDParm As SqlParameter = categoryDataAdapter.UpdateCommand.Parameters.Add("@CategoryID", SqlDbType.Int)
   IDParm.SourceColumn = "category_id"
   IDParm.SourceVersion = DataRowVersion.Original
   Dim objDataRow As DataRow = objDataSet.Tables("category").Rows(0)
   objDataRow("category_name") = "New Category"
   categoryDataAdapter.Update(objDataSet)

Any differences between our current database state and our current data set must be resolved. Imagine a situation where two users connect and cache the data. One user changes a certain column and updates the database while the other user is still looking at the original data representation generated before the database was updated. Our second user now changes the data and tries to commit the changes. Which version should we use? Well, resolving differences is fairly easy if we can decide on certain rules. Each row in the current data base receives a row state status to determine if it is unchanged. We can then decide to update only original values to prevent overwriting any changes by other users or we can update all of the data rows so that the last saved version of the data is the final version.

Using JDBC: Since there is no one major object and no one major data adapter we have to update one table at a time. Using one of our CachedRowSet we can move the curser into the row we need to update and simply set the value in the column.

JAVA
   categoryCachedRowSet.absolute(2);
   categoryCachedRowSet.updateString(2, "Apples");
   categoryCachedRowSet.updateRow();

Deleting Data

In ADO.NET to delete a row you have to start with a dataset class and refer to the rows collection inside the data table you are working with. Use the delete method on the row you wish to remove.

In JDBC simply call delete row on your CachedRowSet

VB.NET
   objDataSet.Tables("Product").Rows(3).Delete()

   JAVA
   productCachedRowSet.absolute(2);
   productCachedRowSet.deleteRow();

Inserting data

With ADO.NET you again need to get the specific table you are working with and get a reference for a new row. You can then add values to the columns and call the add method on the rows collection.

In JDBC call insert row on your cached row set, update the values on the columns and call the insert row method.

VB.NET
   DataRow objDataRow = objDataSet.Tables("Product").NewRow();
   objDataRow("product_name") = "new product"
   objDataSet.Tables("product").Rows.Add(objDataRow);

   JAVA
   productCachedRowSet.moveToInsertRow();
   crs.updateString("product_name", "new product");
   productCachedRowSet.insertRow();
   productCachedRowSet.moveToCurrentRow();

Committing changes back to the Database

In ADO.NET call the accept changes method.

JDBC uses the exact same method.

VB.NET
   objDataSet.AcceptChanges()

   JAVA
   productCachedRowSet.acceptChanges();

In JDBC calling the accept changes method will commit the data to the database. Any differences between the cached version of the data and the database would raise a SyncProviderException. Evaluating the SyncResolver object will help us determine all of our conflicts and solve them according to our business rules.

JAVA
   try {
     productCachedRowSet.acceptChanges();
   } catch (SyncProviderException spe) {
     SyncResolver resolver = spe.getSyncResolver();
     Object crsValue; // value in productCachedRowSet
     Object resolverValue; // value in the SyncResolver object
     Object resolvedValue; // value to be persisted
     while (resolver.nextConflict()) {
       if (resolver.getStatus() == SyncResolver.UPDATE_ROW_CONFLICT) {
         int row = resolver.getRow();
         productCachedRowSet.absolute(row);
         int colCount = productCachedRowSet.getMetaData().getColumnCount();
         for (int j = 1; j

Mr. Gal Ratner graduated from the Technion Institute in Israel and has been writing software for over 10 years. He is the founder and CEO of Inverted Software located in southern California, which consults to large organizations.