package coreservlets.beans;

import java.io.*;
import java.sql.*;
import java.util.*;
import com.sun.rowset.*;
import coreservlets.*;

/** Bean for executing database queries and storing the
 *  result in a CachedRowSet. Once the query is performed,
 *  the connection to the database can be closed. Since the
 *  displayed data is from a disconnected ResultSet, the values
 *  may not remain in sync with the database once another
 *  client posts an update to the database. To obtain a "fresh" 
 *  copy of the result set, reexecute the query. 
 *  <P>
 *  Taken from Core Servlets and JavaServer Pages
 *  from Prentice Hall and Sun Microsystems Press,
 *  http://www.coreservlets.com/.
 *  &copy; 2004 Marty Hall and Larry Brown;
 *  may be freely used or adapted
 */

public class CachedScrollableQueryBean extends
               ScrollableQueryBean {
  private int size; // Number of rows in the CachedRowSet.

  /** Execute the query and store the result in a
   *  CachedRowSet.
   */

  public ResultSet executeQuery(String query)
      throws SQLException {
    if (connection == null) {
      throw new SQLException("Unavailable Connection.");
    }
    // Obtaining a fresh copy of the database query, so
    // close the existing result set.
    if (resultSet != null) {
      closeResultSet();
    }
    CachedRowSetImpl cachedRowSet = new CachedRowSetImpl();
    cachedRowSet.setCommand(query);
    cachedRowSet.execute(connection);
    setResultSet(cachedRowSet);
    // Extract the column names for improved performance
    // (the column names don't change while user scrolls through 
    // the result set).
    ResultSetMetaData resultSetMetaData =
      resultSet.getMetaData();
    int columnCount = resultSetMetaData.getColumnCount();
    columnNames = new String[columnCount];
    for(int i=1; i<=columnCount; i++) {
      columnNames[i-1] =
        resultSetMetaData.getColumnName(i).trim();
    }
    size = cachedRowSet.size();
    return(cachedRowSet);
  }

  /** Position the cursor in the row set so that the next
   *  "block" of rows can be examined. Calculations are based on
   *  the number of rows in the row set and on the scroll size.
   *  The default action is to scroll to the first block of 
   *  rows.
   */

  public void findStartRow(String scrollTo)
      throws SQLException {
    Integer value = (Integer)directions.get(scrollTo);
    int direction = FIRST_DIR;
    if (value != null) {
      direction = value.intValue();
    }
    int row = 1;
    switch(direction) {
      case FIRST_DIR: 
             resultSet.beforeFirst();
             break;
      case PREV_DIR:
             if (resultSet.isAfterLast()) {
               row = (size/scrollSize - 1) * scrollSize;
             } else {
               row = resultSet.getRow() - scrollSize * 2;
             }
             if (row < 1) {
               findStartRow(FIRST);
             } else {
               resultSet.absolute(row);
             }
             break;
      case NEXT_DIR:
             if (resultSet.isAfterLast() ||
               resultSet.isLast()) {
               findStartRow(LAST);
             }
              break;
      case LAST_DIR: 
             row = ((size-1)/scrollSize) * scrollSize;
             if (row < 1) {
               resultSet.beforeFirst();
             } else {
               resultSet.absolute(row);
             }
             break;
      default: findStartRow(NEXT);
    }
  }

  public void close() {
    closeConnection();
  }
}