Thursday, September 04, 2014

On demand refresh of Materialized of views

Use case is simple:  Some OLTP system is committing & updating the database.
In reporting system, they want to see the updates based on end user refresh.
Core idea is whenever end user clicks 'Refresh" data, at that time, a REST API call invokes following JDBC code to refresh targeted Materialized views.
( From my code  Vault... Good & Old RDBMS days..)

    public RefreshDataResponse refreshView(String[] names) {
   
    RefreshDataResponse response = new RefreshDataResponse();
    HashMap map = new HashMap();
   
long lStartTime = System.currentTimeMillis();
   try{
    Connection connection = jdbcHelper.dataSource.getConnection();
 
    String prefix = "call DBMS_SNAPSHOT.REFRESH('";
    String suffix = "','?')";
   
    for ( String name : names){
    String finalCall = prefix+name+suffix;
   
    System.out.println(" final call "+finalCall);
   
    CallableStatement stmt = connection.prepareCall(finalCall);

    String update =null;
    if ( flag == true ) update = "TRUE";
    else update = "FALSE";
    map.put(name,update);
    }
    response.setStatus("OK");
    response.setIdAndStatus(map);
    connection.close();
   
} catch (Exception e) {
// TODO Auto-generated catch block
response.setStatus("ERROR");
response.setErrorMessage(e.getLocalizedMessage());
e.printStackTrace();
}finally {
}
 
    long lEndTime = System.currentTimeMillis();
        long difference = lEndTime - lStartTime;
        System.out.println("MV refresh Elapsed milliseconds: " + difference + "in sec:"+difference/1000);
 
 
    return response;
   
    }