Caching Automatically

When the Auto Cache option is enabled, the driver will automatically load data into the cache database each time you execute a SELECT statement. Each row returned by the query will be inserted or updated into the corresponding table in the cache database.

This is handled in a streaming fashion: each row is processed into the cache database from the original result set as you read the row from the returned ResultSet object, so the data is not loaded twice. Any rows you do not read from the returned ResultSet will not be updated in the cache. When using Auto Cache, ensure that you always read all rows from the returned ResultSet.

To access data in the cache with Auto Cache enabled, you will need to use the pseudo table name <table>#Cache. For example, to query the Account table in the cache, execute a "SELECT * FROM [Account#Cache]" statement. If you run non-SELECT statements on a table_name#Cache table, the changes will not be replicated on the real data source.

You will need to keep track of any changes that you make to the cache if you want to keep them in sync with the real data source. When using Auto Cache, statements other than SELECT will be executed against the real data table, and not against the cache. This can cause the data in the cache to become out of date.

Cache the Account Table

The following example caches data in the .db file specified by the Cache Location property of the connection string. The database contains the Account table, which is populated with the data from the NetSuite data source.

String connectionString = "jdbc:netsuite:Cache Location=C:\\cache.db;" +
                          "Auto Cache=true;" +
                          "Offline=false;" +
                          "Account Id=XABC123456;Password=password;User=user;Role Id=3;Version=2013_1;Location=C:\\myfolder\\;";
Connection connection = DriverManager.getConnection(connectionString);
Statement stat = connection.createStatement();
boolean ret = stat.execute("SELECT * FROM Account");
ResultSet rs=stat.getResultSet();
while(rs.next()){
  System.out.println("Read and cached the row with Id "+rs.getString("Id"));
}
connection.close();