Application: gvSIG desktop

package org.gvsig.fmap.dal.store.jdbc2.spi.operations;

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.List;

import org.apache.commons.lang3.StringUtils;

import org.gvsig.expressionevaluator.ExpressionBuilder;

import org.gvsig.fmap.dal.SQLBuilder.SelectBuilder;

import org.gvsig.fmap.dal.exception.DataException;

import org.gvsig.fmap.dal.feature.FeatureQuery;

import org.gvsig.fmap.dal.feature.FeatureQueryOrder;

import org.gvsig.fmap.dal.feature.FeatureType;

import org.gvsig.fmap.dal.store.jdbc2.JDBCHelper;

import org.gvsig.fmap.dal.store.jdbc2.spi.JDBCSQLBuilderBase;

import org.gvsig.fmap.dal.store.jdbc.exception.JDBCSQLException;

import org.gvsig.fmap.dal.store.jdbc2.JDBCUtils;

import org.gvsig.fmap.dal.store.jdbc2.OperationsFactory.TableReference;

import static org.gvsig.fmap.dal.store.jdbc2.spi.JDBCSQLBuilderBase.PROP_TABLE;

import org.gvsig.tools.evaluator.Evaluator;

public class CountOperation extends AbstractConnectionOperation {

    private final TableReference table;

    private final String baseFilter;

    private final FeatureQuery query;

    private final FeatureType featureType;

    public CountOperation(

            JDBCHelper helper

        ) {

        this(helper, null, null, null, null);

    }

    public CountOperation(

            JDBCHelper helper,

            FeatureType featureType,

            TableReference table,

            String baseFilter,

            FeatureQuery query

        ) {

        super(helper);

        this.featureType = featureType;

        this.table = table;

        this.baseFilter = baseFilter;

        this.query = query;

    }

    @Override

    public final Object perform(Connection conn) throws DataException {

        return this.count(conn);

    }

    public String getSQL() {

        JDBCSQLBuilderBase sqlbuilder = this.createSQLBuilder();

        ExpressionBuilder expbuilder = sqlbuilder.expression();

        SelectBuilder select = sqlbuilder.select();

        if( this.query!=null && this.query.hasGroupByColumns() ) {

          JDBCSQLBuilderBase subsqlbuilder = this.createSQLBuilder();

          SelectBuilder subselect = subsqlbuilder.select();

          subselect.column().value(subsqlbuilder.count().all());

          subselect.from().table()

                  .database(this.table.getDatabase())

                  .schema(this.table.getSchema())

                  .name(this.table.getTable());

          subselect.from().subquery(this.table.getSubquery());

          Evaluator filter = query==null? null:query.getFilter();

          if( filter != null ) {

              String sqlfilter = filter.getSQL();

              if( ! StringUtils.isEmpty(sqlfilter) ) {

                  if( this.helper.supportFilter(this.featureType, filter) ) {

                      subselect.where().set(expbuilder.toValue(sqlfilter));

                  }

              }

          }

          if( ! StringUtils.isEmpty(baseFilter) ) {

              subselect.where().and(expbuilder.toValue(baseFilter));

          }

          FeatureQueryOrder order = query==null? null:query.getOrder();

          if( order != null ) {

              for( FeatureQueryOrder.FeatureQueryOrderMember member : order.members() ) {

                  if( member.hasEvaluator() ) {

                      String sqlorder = member.getEvaluator().getSQL();

                      if( ! StringUtils.isEmpty(sqlorder) ) {

                          subselect.order_by().custom(sqlorder);

                      }

                  } else {

                      subselect.order_by()

                              .column(member.getAttributeName())

                              .ascending(member.getAscending());

                  }

              }

          }

          List<String> groupbyColumns = query==null? null:query.getGroupByColumns();

          if( groupbyColumns!=null && !groupbyColumns.isEmpty() ) {

            for(String columnName : groupbyColumns ) {

                subselect.group_by(expbuilder.column(columnName));

            }

          }

          subsqlbuilder.setProperties(

                  ExpressionBuilder.Variable.class, 

                  PROP_TABLE, table

          );

          String subsql = subselect.toString();

          select.from().table()

                  .database(this.table.getDatabase())

                  .schema(this.table.getSchema())

                  .name(this.table.getTable());

          select.from().subquery(subsql);

        } else {

          select.column().value(sqlbuilder.count().all());

          select.from().table()

                  .database(this.table.getDatabase())

                  .schema(this.table.getSchema())

                  .name(this.table.getTable());

          select.from().subquery(this.table.getSubquery());

          if (!StringUtils.isEmpty(baseFilter)) {

              sqlbuilder.select().where().set( expbuilder.custom(baseFilter) );

          }

          if( this.query!=null ) {

            if( this.query.getFilter()!=null && !StringUtils.isBlank(this.query.getFilter().getSQL()) ) {

              // El and() hace un set() si no hay un filtro previo

              select.where().and(expbuilder.toValue(this.query.getFilter().getSQL()));

            }

          }

        }

        this.helper.processSpecialFunctions(sqlbuilder, featureType, null);

        select.remove_all_columns();

        select.column().value(sqlbuilder.count().all());

        sqlbuilder.setProperties(

                ExpressionBuilder.Variable.class, 

                PROP_TABLE, table

        );

        String sql = select.toString();

        return sql;

    }

    public long count(Connection conn) throws DataException {

        String sql = this.getSQL();

        Statement st = null;

        ResultSet rs = null;

        try {

            st = conn.createStatement();

            rs = JDBCUtils.executeQuery(st, sql);

            if (!rs.next()) {

                return 0;

            }

            return rs.getLong(1);

        } catch (SQLException ex) {

            throw new JDBCSQLException(ex);

        } finally {

            JDBCUtils.closeQuietly(st);

            JDBCUtils.closeQuietly(rs);

        }

    }

}