private void populateSearchBuilderItem(ResultSet rst, SearchBuilderItemImpl sbi)
     throws SQLException {
   sbi.setName(rst.getString(1));
   sbi.setContext(rst.getString(2));
   sbi.setSearchaction(Integer.valueOf(rst.getInt(3)));
   sbi.setSearchstate(Integer.valueOf(rst.getInt(4)));
   sbi.setVersion(rst.getDate(5));
   sbi.setItemscope(Integer.valueOf(rst.getInt(6)));
   sbi.setId(rst.getString(7));
 }
  /**
   * get the Instance Master
   *
   * @return
   * @throws HibernateException
   */
  private SearchBuilderItem getMasterItem(Connection connection) throws SQLException {
    log.debug("get Master Items with " + connection); // $NON-NLS-1$

    PreparedStatement pst = null;
    ResultSet rst = null;
    try {
      pst =
          connection.prepareStatement(
              "select " //$NON-NLS-1$
                  + SEARCH_BUILDER_ITEM_FIELDS
                  + " from " //$NON-NLS-1$
                  + SEARCH_BUILDER_ITEM_T
                  + " where itemscope = ? "); //$NON-NLS-1$
      pst.clearParameters();
      pst.setInt(1, SearchBuilderItem.ITEM_GLOBAL_MASTER.intValue());
      rst = pst.executeQuery();
      SearchBuilderItemImpl sbi = new SearchBuilderItemImpl();
      if (rst.next()) {
        populateSearchBuilderItem(rst, sbi);
      } else {
        sbi.setName(SearchBuilderItem.INDEX_MASTER);
        sbi.setContext(SearchBuilderItem.GLOBAL_CONTEXT);
        sbi.setSearchaction(SearchBuilderItem.ACTION_UNKNOWN);
        sbi.setSearchstate(SearchBuilderItem.STATE_UNKNOWN);
        sbi.setItemscope(SearchBuilderItem.ITEM_GLOBAL_MASTER);
      }
      return sbi;
    } finally {
      try {
        rst.close();
      } catch (Exception ex) {
        log.debug(ex);
      }
      try {
        pst.close();
      } catch (Exception ex) {
        log.debug(ex);
      }
    }
  }
  private List findPending(int batchSize, Connection connection, SearchIndexBuilderWorker worker)
      throws SQLException {
    // Pending is the first 100 items
    // State == PENDING
    // Action != Unknown
    long start = System.currentTimeMillis();
    try {
      log.debug("TXFind pending with " + connection); // $NON-NLS-1$

      SearchBuilderItem masterItem = getMasterItem(connection);
      Integer masterAction = getMasterAction(masterItem);
      log.debug(
          " Master Item is "
              + masterItem.getName()
              + ":" //$NON-NLS-1$ //$NON-NLS-2$
              + masterItem.getSearchaction()
              + ":" //$NON-NLS-1$
              + masterItem.getSearchstate()
              + "::" //$NON-NLS-1$
              + masterItem.getVersion());
      if (SearchBuilderItem.ACTION_REFRESH.equals(masterAction)) {
        log.debug(" Master Action is " + masterAction); // $NON-NLS-1$
        log.debug("  REFRESH = " + SearchBuilderItem.ACTION_REFRESH); // $NON-NLS-1$
        log.debug("  RELOAD = " + SearchBuilderItem.ACTION_REBUILD); // $NON-NLS-1$
        // get a complete list of all items, before the master
        // action version
        // if there are none, update the master action action to
        // completed
        // and return a blank list

        refreshIndex(connection, masterItem);

      } else if (SearchBuilderItem.ACTION_REBUILD.equals(masterAction)) {
        rebuildIndex(connection, masterItem, worker);
      } else {
        // get all site masters and perform the required action.
        List siteMasters = getSiteMasterItems(connection);
        for (Iterator i = siteMasters.iterator(); i.hasNext(); ) {
          SearchBuilderItem siteMaster = (SearchBuilderItem) i.next();
          Integer action = getSiteMasterAction(siteMaster);
          if (SearchBuilderItem.ACTION_REBUILD.equals(action)) {
            rebuildIndex(connection, siteMaster, worker);
          } else if (SearchBuilderItem.ACTION_REFRESH.equals(action)) {
            refreshIndex(connection, siteMaster);
          }
        }
      }
      PreparedStatement pst = null;
      PreparedStatement lockedPst = null;
      ResultSet rst = null;
      try {
        pst =
            connection.prepareStatement(
                "select " //$NON-NLS-1$
                    + SEARCH_BUILDER_ITEM_FIELDS
                    + " from " //$NON-NLS-1$
                    + SEARCH_BUILDER_ITEM_T
                    + " where searchstate = ? and     " //$NON-NLS-1$
                    + "        itemscope = ?  order by version "); //$NON-NLS-1$
        lockedPst =
            connection.prepareStatement(
                "update " //$NON-NLS-1$
                    + SEARCH_BUILDER_ITEM_T
                    + " set searchstate = ? " //$NON-NLS-1$
                    + " where id = ?  and  searchstate = ? "); //$NON-NLS-1$
        pst.clearParameters();
        pst.setInt(1, SearchBuilderItem.STATE_PENDING.intValue());
        pst.setInt(2, SearchBuilderItem.ITEM.intValue());
        rst = pst.executeQuery();
        ArrayList<SearchBuilderItemImpl> a = new ArrayList<SearchBuilderItemImpl>();
        while (rst.next() && a.size() < batchSize) {

          SearchBuilderItemImpl sbi = new SearchBuilderItemImpl();
          populateSearchBuilderItem(rst, sbi);
          if (!SearchBuilderItem.ACTION_UNKNOWN.equals(sbi.getSearchaction())) {
            lockedPst.clearParameters();
            lockedPst.setInt(1, SearchBuilderItem.STATE_LOCKED.intValue());
            lockedPst.setString(2, sbi.getId());
            lockedPst.setInt(3, SearchBuilderItem.STATE_PENDING.intValue());
            if (lockedPst.executeUpdate() == 1) {
              sbi.setSearchstate(SearchBuilderItem.STATE_LOCKED);
              a.add(sbi);
            }
            connection.commit();
          }
        }
        return a;
      } finally {
        try {
          rst.close();
        } catch (Exception ex) {
          log.debug(ex);
        }
        try {
          pst.close();
        } catch (Exception ex) {
          log.debug(ex);
        }
      }

    } finally {
      long finish = System.currentTimeMillis();
      log.debug(" findPending took " + (finish - start) + " ms"); // $NON-NLS-1$ //$NON-NLS-2$
    }
  }