Application: gvSIG desktop

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

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.ArrayList;

import java.util.List;

import org.gvsig.expressionevaluator.ExpressionBuilder;

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

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

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

import org.gvsig.fmap.dal.feature.spi.FeatureProvider;

import org.gvsig.fmap.dal.feature.spi.FeatureReferenceProviderServices;

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

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

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

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

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

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

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

import org.gvsig.fmap.geom.DataTypes;

public class FetchFeatureProviderByReferenceOperation extends AbstractConnectionOperation {

    private final FeatureType featureType;

    private final FeatureReferenceProviderServices reference;

    private final TableReference table;

    public FetchFeatureProviderByReferenceOperation(

            JDBCHelper helper,

            FeatureReferenceProviderServices reference,

            FeatureType featureType,

            TableReference table

        ) {

        super(helper);

        this.reference = reference;

        this.table = table;

        this.featureType = featureType;

    }

    @Override

    public final Object perform(Connection conn) throws DataException {

        FeatureProvider feature = fetchFeatureProviderByReference(conn);

        return feature;

    }

    public String getSQL() {

        List<FeatureAttributeDescriptor> columns = new ArrayList<>();        

        JDBCSQLBuilderBase sqlbuilder = this.createSQLBuilder();

        String sql = this.getSQL(sqlbuilder,columns);

        return sql;

    }

    public String getSQL(JDBCSQLBuilderBase sqlbuilder, List<FeatureAttributeDescriptor> columns) {

        // FeatureAttributeDescriptor[] primaryKey = storeType.getPrimaryKey();

        String[] primaryKeys = reference.getKeyNames();

        ExpressionBuilder expbuilder = sqlbuilder.expression();

        for (FeatureAttributeDescriptor attr : featureType) {

            if( attr.isComputed() ) {

                continue;

            }

            if( attr.getType()==DataTypes.GEOMETRY ) {

                sqlbuilder.select().column().name(attr.getName()).as_geometry();

                columns.add(attr);

            } else {

                sqlbuilder.select().column().name(attr.getName());

                columns.add(attr);

            }

        }

        sqlbuilder.select().from().table()

                .database(this.table.getDatabase())

                .schema(this.table.getSchema())

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

        for (String name : primaryKeys )  {

            if( !sqlbuilder.select().has_column(name) ) {

                sqlbuilder.select().column().name(name);

                columns.add(featureType.getAttributeDescriptor(name));

            }

            Object value = reference.getKeyValue(name);

            if( value == null ) {

                sqlbuilder.select().where().and(

                    expbuilder.is_null(expbuilder.column(name))

                );                

            } else {

                sqlbuilder.select().where().and(

                    expbuilder.eq(

                        expbuilder.column(name), 

                        expbuilder.parameter(name).value(value)

                    )

                );              

            }

        }

        sqlbuilder.select().limit(1);

        sqlbuilder.setProperties(

                ExpressionBuilder.Variable.class, 

                PROP_FEATURE_TYPE, featureType,

                PROP_TABLE, table

        );

        String sql = sqlbuilder.select().toString();

        return sql;

    }

    public FeatureProvider fetchFeatureProviderByReference(

            Connection conn

        ) throws DataException {

        List<FeatureAttributeDescriptor> columns = new ArrayList<>();        

        JDBCSQLBuilderBase sqlbuilder = this.createSQLBuilder();

        String sql = this.getSQL(sqlbuilder,columns);

        PreparedStatement st = null;

        ResultSet rs = null;

        try {

            st = conn.prepareStatement(sql);

            sqlbuilder.setParameters(st);

            rs = JDBCUtils.executeQuery(st, sql);

            if (!rs.next()) {

                return null;

            }

            FeatureProvider data = this.helper.createFeature(featureType);

            this.helper.fetchFeature(data, rs, columns.toArray(new FeatureAttributeDescriptor[columns.size()]), null); 

            return data;

        } catch (SQLException ex) {

            throw new JDBCSQLException(ex);

        } finally {

            JDBCUtils.closeQuietly(st);

            JDBCUtils.closeQuietly(rs);

        }        

    }

}