Automatically caching data is useful when you do not want to rebuild the cache for each query. When you query data for the first time, the driver automatically initializes and builds a cache in the background. When AutoCache = true, the driver uses the cache for subsequent query executions, resulting in faster response times.

If replication is enabled, the data is generated once and then copied to local and cloud data stores. With incremental updates, the driver achieves a performance advantage over dropping the cached tables and retrieving the entire table again on every refresh. With iterative updates, the driver only performs the query from the last time that the date was refreshed. If replication is not enabled, updates to the cache require downloading the entire data set.

Configuring Automatic Caching

To automatically update the cache and return results from the local cache, set the following connection string properties:

• AutoCache: This property automatically updates the cache when the value is set to true.
• CacheTolerance: This property ensures that the data retrieved from the database is the most current version. The default value is 600 seconds (10 minutes). The driver checks with the data source for newer records after the tolerance interval has expired. Otherwise, it returns the data directly from the cache.

Caching the TradingAccounts Table

The following example caches the TradingAccounts table in the file specified by the CacheLocation property of the connection string.

String connectionString = "jdbc:sage50uk:Cache Location=C:\\cache.db;" +
                          "AutoCache=true;" +
                          "URL=http://localhost:5493/sdata/accounts50/GCRM/{C4C863BE-B098-4A7D-A78B-D7A92B8ADB59};User=Manager;Password=xxxxxx;";
Connection connection = DriverManager.getConnection(connectionString);
Statement stat = connection.createStatement();
boolean ret = stat.execute("SELECT TradingAccountUUID, Name FROM TradingAccounts WHERE TradingAccountUUID = 'c2ef66a5-a545-413b-9312-79a53caadbc4'");
ResultSet rs=stat.getResultSet();
while(rs.next()){
  System.out.println("Read and cached the row with TradingAccountUUID "+rs.getString("TradingAccountUUID"));
}
connection.close();

Common Use Case

A common use for automatically caching data is to improve driver performance when making repeated requests to a live data source, such as building a report or creating a visualization. With auto caching enabled, repeated requests to the same data may be executed in a short period of time, but within an allowable tolerance (CacheTolerance) of what is considered "live" data.