private List<List<PropertyPair>> findResults(
      Query query, PropertySelectionCollector collector, PlasmaType type, Connection con) {
    Object[] params = new Object[0];
    JDBCDataConverter converter = JDBCDataConverter.INSTANCE;

    if (log.isDebugEnabled()) {
      log(query);
    }

    AliasMap aliasMap = new AliasMap(type);

    Map<Type, List<String>> selectMap = collector.getResult();

    // construct a filter adding to alias map
    JDBCFilterAssembler filterAssembler = null;
    Where where = query.findWhereClause();
    if (where != null) {
      filterAssembler = new JDBCFilterAssembler(where, type, aliasMap);
      params = filterAssembler.getParams();
    }

    JDBCOrderingDeclarationAssembler orderingDeclAssembler = null;
    OrderBy orderby = query.findOrderByClause();
    if (orderby != null)
      orderingDeclAssembler = new JDBCOrderingDeclarationAssembler(orderby, type, aliasMap);
    JDBCGroupingDeclarationAssembler groupingDeclAssembler = null;
    GroupBy groupby = query.findGroupByClause();
    if (groupby != null)
      groupingDeclAssembler = new JDBCGroupingDeclarationAssembler(groupby, type, aliasMap);

    String rootAlias = aliasMap.getAlias(type);
    StringBuilder sqlQuery = new StringBuilder();
    sqlQuery.append("SELECT ");

    int i = 0;
    List<String> names = selectMap.get(type);
    for (String name : names) {
      PlasmaProperty prop = (PlasmaProperty) type.getProperty(name);
      if (prop.isMany() && !prop.getType().isDataType()) continue;
      if (i > 0) sqlQuery.append(", ");
      sqlQuery.append(rootAlias);
      sqlQuery.append(".");
      sqlQuery.append(prop.getPhysicalName());
      i++;
    }

    // construct a FROM clause from alias map
    sqlQuery.append(" FROM ");
    Iterator<PlasmaType> it = aliasMap.getTypes();
    int count = 0;
    while (it.hasNext()) {
      PlasmaType aliasType = it.next();
      String alias = aliasMap.getAlias(aliasType);
      if (count > 0) sqlQuery.append(", ");
      sqlQuery.append(aliasType.getPhysicalName());
      sqlQuery.append(" ");
      sqlQuery.append(alias);
      count++;
    }

    // append WHERE filter
    if (filterAssembler != null) {
      sqlQuery.append(" ");
      sqlQuery.append(filterAssembler.getFilter());
    }

    // set the result range
    // FIXME: Oracle specific
    if (query.getStartRange() != null && query.getEndRange() != null) {
      if (where == null) sqlQuery.append(" WHERE ");
      else sqlQuery.append(" AND ");
      sqlQuery.append("ROWNUM >= ");
      sqlQuery.append(String.valueOf(query.getStartRange()));
      sqlQuery.append(" AND ROWNUM <= ");
      sqlQuery.append(String.valueOf(query.getEndRange()));
    }

    if (orderingDeclAssembler != null) {
      sqlQuery.append(" ");
      sqlQuery.append(orderingDeclAssembler.getOrderingDeclaration());
    }

    if (groupingDeclAssembler != null) {
      sqlQuery.append(" ");
      sqlQuery.append(groupingDeclAssembler.getGroupingDeclaration());
    }

    List<List<PropertyPair>> rows = new ArrayList<List<PropertyPair>>();
    PreparedStatement statement = null;
    ResultSet rs = null;
    try {
      statement =
          con.prepareStatement(
              sqlQuery.toString(),
              ResultSet.TYPE_FORWARD_ONLY, /*ResultSet.TYPE_SCROLL_INSENSITIVE,*/
              ResultSet.CONCUR_READ_ONLY);

      // set params
      // note params are pre-converted
      // to string in filter assembly
      // FIXME: are parameters relevant in SQL in this context?
      if (filterAssembler != null) {
        params = filterAssembler.getParams();
        if (params != null)
          for (i = 0; i < params.length; i++) statement.setString(i + 1, String.valueOf(params[i]));
      }

      if (log.isDebugEnabled()) {
        if (params == null || params.length == 0) {
          log.debug("executing: " + sqlQuery.toString());
        } else {
          StringBuilder paramBuf = new StringBuilder();
          paramBuf.append(" [");
          for (int p = 0; p < params.length; p++) {
            if (p > 0) paramBuf.append(", ");
            paramBuf.append(String.valueOf(params[p]));
          }
          paramBuf.append("]");
          log.debug("executing: " + sqlQuery.toString() + " " + paramBuf.toString());
        }
      }

      statement.execute();
      rs = statement.getResultSet();
      int numcols = rs.getMetaData().getColumnCount();
      ResultSetMetaData rsMeta = rs.getMetaData();
      List<PropertyPair> row = null;
      PropertyPair pair = null;
      while (rs.next()) {
        row = new ArrayList<PropertyPair>();
        rows.add(row);
        for (i = 1; i <= numcols; i++) {
          String columnName = rsMeta.getColumnName(i);
          int columnType = rsMeta.getColumnType(i);
          PlasmaProperty prop = (PlasmaProperty) type.getProperty(columnName);
          Object value = converter.fromJDBCDataType(rs, i, columnType, prop);
          pair = new PropertyPair(prop, value);
          pair.setColumn(i);
          row.add(pair);
        }
      }
    } catch (Throwable t) {
      StringBuffer buf = this.generateErrorDetail(t, sqlQuery.toString(), filterAssembler);
      log.error(buf.toString());
      throw new DataAccessException(t);
    } finally {
      try {
        if (rs != null) rs.close();
        if (statement != null) statement.close();
      } catch (SQLException e) {
        log.error(e.getMessage(), e);
      }
    }
    return rows;
  }
  private int countResults(Connection con, Query query, PlasmaType type) {
    int result = 0;
    Object[] params = new Object[0];
    JDBCFilterAssembler filterAssembler = null;

    StringBuilder sqlQuery = new StringBuilder();
    AliasMap aliasMap = new AliasMap(type);

    sqlQuery.append("SELECT COUNT(*)");
    sqlQuery.append(aliasMap.getAlias(type));
    Statement statement = null;
    ResultSet rs = null;

    try {
      Where where = query.findWhereClause();
      if (where != null) {
        filterAssembler = new JDBCFilterAssembler(where, type, aliasMap);
        sqlQuery.append(" ");
        sqlQuery.append(filterAssembler.getFilter());
        params = filterAssembler.getParams();

        if (log.isDebugEnabled()) {
          log.debug("filter: " + filterAssembler.getFilter());
        }
      } else {
        sqlQuery.append(" FROM ");
        sqlQuery.append(type.getPhysicalName());
        sqlQuery.append(" ");
        sqlQuery.append(aliasMap.getAlias(type));
      }
      if (query.getStartRange() != null && query.getEndRange() != null)
        log.warn(
            "query range (start: "
                + query.getStartRange()
                + ", end: "
                + query.getEndRange()
                + ") ignored for count operation");

      if (log.isDebugEnabled()) {
        log.debug("queryString: " + sqlQuery.toString());
        log.debug("executing...");
      }

      statement =
          con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
      statement.execute(sqlQuery.toString());
      rs = statement.getResultSet();
      rs.first();
      result = rs.getInt(1);
    } catch (Throwable t) {
      StringBuffer buf = this.generateErrorDetail(t, sqlQuery.toString(), filterAssembler);
      log.error(buf.toString());
      throw new DataAccessException(t);
    } finally {
      try {
        if (rs != null) rs.close();
        if (statement != null) statement.close();
      } catch (SQLException e) {
        log.error(e.getMessage(), e);
      }
    }
    return result;
  }