Growing model with Scrolling/Live Data

package org.zkforge.converters.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.NoSuchElementException;
import java.util.TreeMap;

import javax.sql.DataSource;

import org.zkoss.util.logging.Log;
import org.zkoss.zk.ui.event.Event;
import org.zkoss.zk.ui.event.EventListener;
import org.zkoss.zkplus.databind.BindingListModel;
import org.zkoss.zkplus.databind.BindingListModelList;
import org.zkoss.zul.event.ListDataEvent;


@SuppressWarnings("serial")
public class BindingAutoGrowResultSetModel extends BindingListModelList
implements Iterator, java.io.Serializable, EventListener {

    /**
     * @author Robert Pichelhofer
     *
     * @see BindingListModel
     * @see org.zkoss.zul.BindListModelList
     * @see org.zkoss.zul.ListModel
     * @see org.zkoss.zul.ListModelList
     */
    ResultSetMetaData metaData;
    int columnCount;
    String[] fieldNames;
    String countSQL;
    String pageSQL;
    private DataSource ds;
    private static final Log log = Log.lookup(BindingAutoGrowResultSetModel.class);
    private int pos=0;
    private int limit;
    private int trigger;
    private int pageSize;
    private int oldIndex=0;
    private int dbType;
    private boolean eof;
    private int preloadPages;  
    private int triggerPage;
    private boolean fireUIEvents;

    TreeMap<String, Integer> fieldMap;
    SQLParser sqlParser;

    public BindingAutoGrowResultSetModel(DataSource ds, int dbType) {
        super(new ArrayList(1), false); // dummy
        this.ds = ds;
        this.dbType = dbType;
        preloadPages = 5;
        triggerPage = 2;
        fireUIEvents = false;
    }
    public void setSQLCommand(String sql, int pageSize) throws SQLException  {
        sqlParser = new SQLParser(ds, dbType);
        sqlParser.setSQLCommand(sql);

        this.pageSize = pageSize;
        limit = pageSize * preloadPages;
        trigger = limit - pageSize * triggerPage + 1;
        eof=false;

        _list.clear();

        // load first page
        loadPage(0, limit);
        oldIndex = this.size();
    }


    private Connection getConnection() {
        try {
            return ds.getConnection();
        } catch (SQLException e) {
            log.error("Unable to get a Connection, " + e);
            return null;
        }
    }


    public Object getElementAt(int j) {

        // load this page
        if (j > trigger & !eof) {
            log.debug("new page(s) triggered");
            loadPage(this.getSize(), limit);
        }

        return _list.get(j);
    }
    public Object get(int j) {
        return  getElementAt(j);
    }
    public Iterator iterator() {
        // TODO build an own arrayiterator
        // this can only manage one iterator
        //       
        log.info("Iterator requested");
        pos = 0;
        return this;
    }
    private void loadPage(int offset, int limit) {
        oldIndex = offset;

        Connection conn = getConnection();
        PreparedStatement stmt;
        try {
            log.info(sqlParser.getPageSQL());
            stmt = conn.prepareStatement(sqlParser.getPageSQL());
            stmt.setInt(sqlParser.offsetIndex, offset);
            if (sqlParser.isRelativeLimit()) {
                stmt.setInt(sqlParser.limitIndex, limit);
            } else {    
                stmt.setInt(sqlParser.limitIndex, offset + limit);
            }
            ResultSet rset = stmt.executeQuery();
            rs2Map(rset, offset);
            rset.close();
            stmt.close();
            conn.close();
        } catch (SQLException e) {
            log.error("Could not execute paging SQL, ", e);
        }
        if (oldIndex == getSize()) {
            eof = true;   // no new records
        } else {
            if ((oldIndex + limit) > getSize()) {
                eof = true;
            } else {
                trigger = getSize() - pageSize * (preloadPages - triggerPage) + 1;
            }
            // force GUI-Update

            log.info("loaded: " + oldIndex + " to " + getSize());
            // Events.postEvent(new PagingEvent("onPagingImpl", this, _actpg));
            if (fireUIEvents) {
                fireEvent(ListDataEvent.INTERVAL_ADDED, oldIndex, getSize()-1);
            }
        }
    }
    @SuppressWarnings("unchecked")
    public void rs2Map(ResultSet rs, int index) throws SQLException {
        if (fieldMap ==  null) {
            fieldMap = new TreeMap<String, Integer>(String.CASE_INSENSITIVE_ORDER);
            ResultSetMetaData metaData;
            metaData = rs.getMetaData();
            columnCount = metaData.getColumnCount();
            for (int i = 0; i < columnCount; i++) {
                fieldMap.put(metaData.getColumnName(i+1), i);
            }
        }

        while(rs.next()) {
            RowMap rowMap = new RowMap(fieldMap);
            for (int i = 0; i < columnCount; i++) {
                rowMap.put(i, rs.getObject(i+1));
            }     
            _list.add(rowMap);
            index++;
        }
    }
    public int indexOf(Object ref) {
        log.info("Indexrequst " + ref);
        return _list.indexOf(ref);
    }

    public int getSize() {
        return _list.size();
    }

    public boolean hasNext() {
        return pos < _list.size();
    }

    public Object next() {
        if ( hasNext() )
            return getElementAt(pos++);
        else
            throw new NoSuchElementException();
    }

    public void remove() {
        // TODO Auto-generated method stub

    }


    public void onEvent(Event event) throws Exception {
        System.out.println("Event " + event.getName() + ": " + event.getData());
        if (oldIndex != getSize()) {
            log.info("Update: " + oldIndex + " bis " + getSize());
            fireEvent(ListDataEvent.INTERVAL_ADDED, oldIndex, getSize()-1);
            oldIndex = getSize();
            log.info("Update gesendet oldIndex --> " + oldIndex);
        } 
    }
    /**
     * 
     * @return true when fire Events to modelholder (UI)
     */
    public boolean isFireUIEvents() {
        return fireUIEvents;
    }
    /**
     * Should this model fire Events to the holding 
     * UI Component. This should be false for UI with
     * paging (because paging fires the events) and
     * true for Grids/Listboxes with scrolling.
     * The updates inform about new records form the
     * growing model.
     * 
     * @param fireUIEvents
     */
    public void setFireUIEvents(boolean fireUIEvents) {
        this.fireUIEvents = fireUIEvents;
    }    

}