Java Streaming JDBC resultset to read rows from a table keeping resultset open and without firing SQL again

Question

We have a scenario, where we are continuously reading data from a table while another application is inserting records into that table. As per our current implementation we are polling (executing SQL on that table) for new records after certain period of time using Spring's JdbcTemplate.

I am thinking of some way through which I can implement streaming ResultSet where I can keep on reading table data from ResultSet without executing SQL again and again.

EDIT: Adding code snippet from out current implementation:

String SQL = "SELECT * FROM ACTIONSTATUS WHERE STATUS = 'FAILED' and ALERTID > :alertId";

public static void fetchActionStatus(NamedParameterJdbcTemplate template, HashMap<String, String> paramMap, String fetchSql) throws Exception{
    int i = 0;
    List<Map<String, Object>> results = null;
    while(true){
        if(results == null || results.size() == i){
            results = template.queryForList(SQL, paramMap);
            i = 0;
        }else{
            Map<String, Object> row = results.get(i);

            // SOME DATA PROCESSING WHICH MAY TAKE XX MILISEC.
            System.out.println("ALERTID:     " + row.get("ALERTID").toString() + "     ACTIONID:     " + row.get("ACTIONID").toString() + "     STATUS:     " +row.get("STATUS").toString());
            Thread.sleep(100);
            paramMap.put("alertId", row.get("ALERTID").toString());
            i++;
        }
    }

}

Some other application is inserting new records in ACTIONSTATUS table at random interval, we need to continuously monitor ACTIONSTATUS table to process all records. Our current implementation is working fine without any issues. We are looking for some other solution to optimize this approach to somehow stream ResultSet without executing SQL again and again.

Answer 1

I don't think there is a way to do this with JDBC.

From an SQL perspective, it is potentially problematic because of the issue of transaction isolation.