Application: gvSIG desktop

/**

 * gvSIG. Desktop Geographic Information System.

 *

 * Copyright (C) 2007-2013 gvSIG Association.

 *

 * This program is free software; you can redistribute it and/or

 * modify it under the terms of the GNU General Public License

 * as published by the Free Software Foundation; either version 3

 * of the License, or (at your option) any later version.

 *

 * This program is distributed in the hope that it will be useful,

 * but WITHOUT ANY WARRANTY; without even the implied warranty of

 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the

 * GNU General Public License for more details.

 *

 * You should have received a copy of the GNU General Public License

 * along with this program; if not, write to the Free Software

 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston,

 * MA  02110-1301, USA.

 *

 * For any additional information, do not hesitate to contact us

 * at info AT gvsig.com, or visit our website www.gvsig.com.

 */

package org.gvsig.fmap.dal.store.jdbc;

import java.security.InvalidParameterException;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.ArrayList;

import java.util.Collections;

import java.util.Iterator;

import java.util.List;

import org.cresques.cts.IProjection;

import org.gvsig.fmap.dal.DALLocator;

import org.gvsig.fmap.dal.DataManager;

import org.gvsig.fmap.dal.DataServerExplorer;

import org.gvsig.fmap.dal.DataStore;

import org.gvsig.fmap.dal.DataStoreNotification;

import org.gvsig.fmap.dal.DataTypes;

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

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

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

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

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

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

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

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

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

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

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

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

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

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

import org.gvsig.fmap.dal.resource.ResourceAction;

import org.gvsig.fmap.dal.resource.exception.ResourceExecuteException;

import org.gvsig.fmap.dal.resource.spi.ResourceProvider;

import org.gvsig.fmap.dal.spi.DataStoreProviderServices;

import org.gvsig.fmap.dal.store.db.DBHelper;

import org.gvsig.fmap.dal.store.db.FeatureTypeHelper;

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

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

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

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

import org.gvsig.fmap.geom.Geometry;

import org.gvsig.fmap.geom.GeometryLocator;

import org.gvsig.fmap.geom.GeometryManager;

import org.gvsig.fmap.geom.primitive.Envelope;

import org.gvsig.tools.dynobject.DynObject;

import org.gvsig.tools.dynobject.exception.DynFieldNotFoundException;

import org.gvsig.tools.exception.BaseException;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

/**

 * @author jmvivo

 *

 */

public class JDBCStoreProvider extends AbstractFeatureStoreProvider

                implements JDBCHelperUser {

        final static private Logger logger = LoggerFactory

                        .getLogger(JDBCStoreProvider.class);

        private List<ResultSetInfo> resulsetList;

        public static String NAME = "JDBC";

        public static String DESCRIPTION = "JDBC source";

        public static final String METADATA_DEFINITION_NAME = NAME;

        private long mlsecondsToZombie = 1000 * 60 * 10; // 10 Min

        protected JDBCHelper helper;

        protected boolean directSQLMode;

        private Long totalCount = null;

        private GeometryManager geomManager = null;

        public JDBCStoreProvider(JDBCStoreParameters params,

                        DataStoreProviderServices storeServices) throws InitializeException {

                this(

                                params, 

                                storeServices,

                                DBHelper.newMetadataContainer(METADATA_DEFINITION_NAME)

                );

        }

        protected JDBCStoreProvider(JDBCStoreParameters params,

                        DataStoreProviderServices storeServices, DynObject metadata)

                        throws InitializeException {

                super(params, storeServices, metadata);

                geomManager = GeometryLocator.getGeometryManager();

                resulsetList = new ArrayList<ResultSetInfo>(10);

                helper = createHelper();

                if (params.getSQL() != null && (params.getSQL()).trim().length() > 0) {

                        directSQLMode = true;

                }

                this.initFeatureType();

        }

        public Object getDynValue(String name) throws DynFieldNotFoundException {

                try {

                        if( DataStore.METADATA_ENVELOPE.equalsIgnoreCase(name) ) {

                                Envelope env = this.getEnvelope();

                                if( env != null ) {

                                        return env;

                                }

                        } else if( DataStore.METADATA_CRS.equalsIgnoreCase(name) ) {

                                IProjection proj;

                                proj = this.getFeatureStore().getDefaultFeatureType().getDefaultSRS();

                                if( proj != null ) {

                                        return proj;

                                }

                        }

                } catch (DataException e) {

                        throw new RuntimeException(e);

                }

                return super.getDynValue(name);

        }

        protected JDBCStoreParameters getJDBCParameters() {

                return (JDBCStoreParameters) this.getParameters();

        }

        /**

         * Load data form a resulset.<br>

         *

         * <strong>Note:</strong><br>

         * this method have to perform <code>resouceBegin</code> at the begining and

         * <code>resourceEnd</code> at the end of execution.

         *

         *

         * @param data

         * @param resulsetID

         *

         * @return

         * @throws DataException

         */

        public void loadFeatureProvider(final FeatureProvider data, final int resultsetID)

                        throws DataException {

                getResource().execute(new ResourceAction() {

                        public Object run() throws Exception {

                                ResultSet rs = getResultSet(resultsetID);

                                FeatureAttributeDescriptor attr;

                                Iterator<FeatureAttributeDescriptor> iter = FeatureTypeHelper.iterator(data.getType());

                                while (iter.hasNext()) {

                                        attr = iter.next();

                                        loadFeatureProviderValue(data, rs, attr);

                                }

                                return null;

                        }

                });

        }

        protected void loadFeatureProviderValue(FeatureProvider data, ResultSet rs,

                        FeatureAttributeDescriptor attr) throws DataException {

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

                        byte[] buffer;

                        try {

                                buffer = rs.getBytes(attr.getIndex() + 1);

                                if (buffer == null) {

                                        data.set(attr.getIndex(), null);

                                } else {

                                        data.set(attr.getIndex(), this.helper.getGeometry(buffer));

                                }

                        } catch (SQLException e) {

                                throw new JDBCSQLException(e);

                        } catch (BaseException e) {

                                throw new ReadException(getProviderName(), e);

                        }

                } else {

                        try {

                                data.set(attr.getIndex(), rs.getObject(attr.getIndex() + 1));

                        } catch (SQLException e) {

                                throw new JDBCSQLException(e);

                        }

                }

        }

        public long getTimeToResulSetZombie() {

                return mlsecondsToZombie;

        }

        public void setTimeToResulSetZombie(long mlSeconds) {

                mlsecondsToZombie = mlSeconds;

        }

        private class ResultSetInfo{

                private ResultSet resultSet = null;

                private long lastUse = 0;

                public ResultSetInfo(ResultSet resulSet) {

                        this.resultSet = resulSet;

                        used();

                }

                private void used() {

                        lastUse = System.currentTimeMillis();

                }

                public ResultSet get() {

                        used();

                        return resultSet;

                }

                public boolean isZombie() {

                        return System.currentTimeMillis() - lastUse > mlsecondsToZombie;

                }

        }

        public final int createResultSet(String sql, int fetchSize)

                        throws DataException {

        return createResultSet(sql, null, fetchSize);

        }

        public final int createResultSet(final String sql, final Object[] values,

                        final int fetchSize)

                        throws DataException {

                logger.debug("Creating resultSet with sql: {}", sql);

                synchronized (this) {

                        checksResulsets();

                        return ((Integer) getResource().execute(new ResourceAction() {

                                public Object run() throws Exception {

                                        ResultSetInfo newRs =

                                                        new ResultSetInfo(createNewResultSet(sql, values,

                                                                        fetchSize));

                                        int newId = getNewId();

                                        if (newId < 0) {

                                                newId = resulsetList.size();

                                                resulsetList.add(newRs);

                                        } else {

                                                resulsetList.set(newId, newRs);

                                        }

                                        logger.debug("Created resultset id: {} (total open: {})",

                                                        newId, getResultsetOpenCount());

                                        return Integer.valueOf(newId);

                                }

                        })).intValue();

                }

        }

        private int getNewId() {

                int newId;

                if (resulsetList.size() < 1) {

                        return -1;

                }

                for (newId = 0; newId < resulsetList.size(); newId++) {

                        if (resulsetList.get(newId) == null) {

                                return newId;

                        }

                }

                return -1;

        }

        protected final void forceCloseAllResultSet()

                        throws ResourceExecuteException,

                        JDBCException {

                synchronized (this) {

                        // FIXME: Esto no deberia funcionar. 

                        Iterator iter = resulsetList.iterator();

                        Integer rsID = null;

                        while (iter.hasNext()) {

                                rsID = (Integer) iter.next();

                                if (rsID != null) {

                                        try {

                                                forceCloseResultSet(rsID.intValue());

                                        } catch (InvalidResultSetIdException e) {

                                                continue;

                                        }

                                }

                                iter.remove();

                        }

                }

        }

        protected final void forceCloseResultSet(int rsID)

                        throws ResourceExecuteException, JDBCException,

                        InvalidResultSetIdException {

                logger.warn("Close forced of resultSet ({})", rsID);

                closeResulset(rsID);

        }

        protected final ResultSet getResultSet(int resultsetID)

                        throws InvalidResultSetIdException {

                if (resultsetID >= resulsetList.size()) {

                        throw new InvalidResultSetIdException(resultsetID);

                }

                ResultSetInfo rsInfo = resulsetList.get(resultsetID);

                if (rsInfo == null) {

                        throw new InvalidResultSetIdException(resultsetID);

                }

                return rsInfo.get();

        }

        private ResultSet dropResultSet(int resultsetID)

                        throws InvalidResultSetIdException {

                if (resultsetID >= resulsetList.size()) {

                        throw new InvalidResultSetIdException(resultsetID);

                }

                ResultSetInfo rsInfo = (ResultSetInfo) resulsetList.get(resultsetID);

                if (rsInfo == null) {

                        throw new InvalidResultSetIdException(resultsetID);

                }

                if (resultsetID == resulsetList.size() - 1) {

                        resulsetList.remove(resultsetID);

                } else {

                        resulsetList.set(resultsetID, null);

                }

                return rsInfo.get();

        }

        public final boolean resulsetNext(final int resultsetID)

                        throws JDBCException,

                        InvalidResultSetIdException, ResourceExecuteException {

                return ((Boolean) getResource().execute(new ResourceAction() {

                        public Object run() throws Exception {

                            boolean bool = getResultSet(resultsetID).next();

                                return Boolean.valueOf(bool);

                        }

                })).booleanValue();

        }

        public final void closeResulset(final int resultsetID)

                        throws JDBCException,

                        InvalidResultSetIdException, ResourceExecuteException {

                synchronized (this) {

                        getResource().execute(new ResourceAction() {

                                public Object run() throws Exception {

                                        ResultSet rs = dropResultSet(resultsetID);

                                        closeResulset(rs);

                                        return null;

                                }

                        });

                        if (logger.isDebugEnabled()) {

                                logger.debug(" id: " + resultsetID + " (total "

                                                + getResultsetOpenCount() + ")");

                        }

                        checksResulsets();

                }

        }

        public final void checksResulsets() throws JDBCException,

                        InvalidResultSetIdException, ResourceExecuteException {

                synchronized (this) {

                        getResource().execute(new ResourceAction() {

                                public Object run() throws Exception {

                                        ResultSetInfo rsInfo;

                                        for (int i = 0; i < resulsetList.size(); i++) {

                                                rsInfo = (ResultSetInfo) resulsetList.get(i);

                                                if (rsInfo == null) {

                                                        continue;

                                                }

                                                if (rsInfo.isZombie()) {

                                                        forceCloseResultSet(i);

                                                }

                                        }

                                        return null;

                                }

                        });

                }

        }

        protected void closeResulset(final ResultSet rs) throws JDBCException,

                        ResourceExecuteException {

                getResource().execute(new ResourceAction() {

                        public Object run() throws Exception {

                                Statement st = rs.getStatement();

                                Connection con = st.getConnection();

                                try {

                                        rs.close();

                                } finally {

                                        // TODO revisar esto

                                        try{ st.close();  } catch (Exception ex){ };

                                        try{ con.close(); } catch (Exception ex){ };

                                }

                                return null;

                        }

                });

        }

        private int getResultsetOpenCount() {

                int count = 0;

                Iterator<ResultSetInfo> iter = resulsetList.iterator();

                while (iter.hasNext()) {

                        if (iter.next() != null) {

                                count++;

                        }

                }

                return count;

        }

        protected final int openResulsetCount() {

                int count = 0;

                Iterator<ResultSetInfo> iter = resulsetList.iterator();

                while (iter.hasNext()) {

                        if (iter.next() != null) {

                                count++;

                        }

                }

                return count;

        }

        public boolean closeResourceRequested(ResourceProvider resource) {

                try {

                        checksResulsets();

                        return openResulsetCount() == 0 && closeResource(resource);

                } catch (DataException e) {

                        logger.error("Exception throws", e);

                        return false;

                }

        }

        protected String fixFilter(String filter) {

                if (filter == null) {

                        return null;

                }

                return filter;

        }

        protected JDBCHelper createHelper() throws InitializeException {

                return new JDBCHelper(this, getJDBCParameters());

        }

        protected JDBCHelper getHelper() {

                return helper;

        }

        protected void resetCount() {

                totalCount = null;

        }

        /**

         * Get feature count for a <code>filter</code>.<br>

         *

         * <code>filter</code> can be <code>null</code>.<br>

         *

         * <strong>Note:</strong><br>

         * this method have to perform <code>resouceBegin</code> at the begining and

         * <code>resourceEnd</code> at the end of execution.

         *

         *

         * @param filter

         * @return

         * @throws DataException

         */

        protected long getCount(String filter)

                        throws DataException {

                this.open();

                if (filter == null && totalCount != null) {

                        return totalCount.longValue();

                }

                final String sql = compoundCountSelect(filter);

                long count = ((Long) getResource().execute(new ResourceAction() {

                        public Object run() throws Exception {

                                long count = 0;

                                ResultSet rs = createNewResultSet(sql, null, 1);

                                try {

                                        if (rs.next()) {

                                                count = rs.getLong(1);

                                        }

                                } catch (SQLException e) {

                                        throw new JDBCSQLException(e);

                                } finally {

                                        closeResulset(rs);

                                }

                                return Long.valueOf(count);

                        }

                })).longValue();

                if (filter == null) {

                        totalCount = new Long(count);

                }

                return count;

        }

        public void close() throws CloseException {

                helper.close();

        }

        public void open() throws OpenException {

                helper.open();

        }

        @Override

        protected FeatureProvider internalGetFeatureProviderByReference(

                        FeatureReferenceProviderServices reference) throws DataException {

                return internalGetFeatureProviderByReference(reference,

                                getFeatureStore()

                                .getDefaultFeatureType());

        }

        /**

         * Return "is null" expression for current provider<br/>

         */

        protected String getIsNullExpression() {

            return "is null";

        }

        @Override

        protected FeatureProvider internalGetFeatureProviderByReference(

                        FeatureReferenceProviderServices reference,

                        FeatureType featureType)

                        throws DataException {

                StringBuilder filter = new StringBuilder();

                FeatureAttributeDescriptor[] pk =

                                getFeatureStore().getFeatureType(featureType.getId())

                                                .getPrimaryKey();

                List<Object> values = new ArrayList<Object>();

                int i;

                Object value;

                for (i = 0; i < pk.length; i++) {

                    value = reference.getKeyValue(pk[i].getName());

                    filter.append(helper.getSqlFieldName(pk[i]));

                    if (value == null) {

                        filter.append(" ");

                        filter.append(getIsNullExpression());

                        filter.append("");

                    } else {

                        values.add(helper.dalValueToJDBC(pk[i], value));

                        filter.append(" = ? ");

                    }

                    if (i < pk.length -1) {

                        filter.append(" AND ");

                    }

                }

                String sql = compoundSelect(featureType, filter.toString(), null, 1, 0);

                FeatureProvider data;

                int rsId = createResultSet(sql, values.toArray(), 1);

                try {

                        if (!resulsetNext(rsId)) {

                                throw new RuntimeException("Reference Not found");

                        }

                        data = createFeatureProvider(featureType);

                        loadFeatureProvider(data, rsId);

                } finally {

                        closeResulset(rsId);

                }

                return data;

        }

        public int getOIDType() {

                return DataTypes.UNKNOWN;

        }

        protected void initFeatureType() throws InitializeException {

                EditableFeatureType edFType = null;

                try {

                        edFType = this.getStoreServices().createFeatureType(getName());

                        helper.loadFeatureType(edFType, getJDBCParameters());

                } catch (DataException e) {

                        throw new InitializeException(this.getProviderName(), e);

                }

                FeatureType defaultType = edFType.getNotEditableCopy();

                List<FeatureType> types = Collections.singletonList(defaultType);

                this.getStoreServices().setFeatureTypes(types, defaultType);

        }

        protected ResultSet createNewResultSet(final String sql,

                        final Object[] values, final int fetchSize)

                        throws DataException {

                this.open();

                return (ResultSet) getResource().execute(new ResourceAction() {

                        public Object run() throws Exception {

                                Connection conn = null;

                                PreparedStatement st = null;

                                ResultSet rs = null;

                                try {

                                        conn = helper.getConnection();

                                        conn.setAutoCommit(false);

                                        st = conn.prepareStatement(sql);

                                        if (values != null) {

                                                Object value;

                                                for (int i = 0; i < values.length; i++) {

                                                        value = values[i];

                                                        if (value instanceof Geometry) {

                                                                byte[] bytes;

                                                                try {

                                                                        bytes = ((Geometry) value).convertToWKB();

                                                                } catch (BaseException e) {

                                                                        throw new InvalidParameterException();

                                                                }

                                                                st.setBytes(i + 1, bytes);

                                                        }

                                                        st.setObject(i + 1, value);

                                                }

                                        }

                                        if (fetchSize > 0) {

                                                st.setFetchSize(fetchSize);

                                        }

                                        rs = st.executeQuery();

                                        if (fetchSize > 0) {

                                                rs.setFetchSize(fetchSize);

                                        }

                                        return rs;

                                } catch (SQLException e) {

                                        try {

                                                rs.close();

                                        } catch (Exception e1) {

                                        }

                                        try {

                                                st.close();

                                        } catch (Exception e1) {

                                        }

                                        try {

                                                conn.close();

                                        } catch (Exception e1) {

                                        }

                                        throw new JDBCExecutePreparedSQLException(sql,values,e);

                                }

                        }

                });

        }

        protected boolean closeResource(ResourceProvider resource) {

                try {

                        this.helper.close();

                } catch (CloseException e) {

                        logger.error("Exception in close Request", e);

                }

                return !this.helper.isOpen();

        }

        protected String compoundCountSelect(String filter) {

                // Select

                StringBuilder sql = new StringBuilder();

                sql.append("Select count(");

                String[] pkFields = getJDBCParameters().getPkFields();

                if (pkFields != null && pkFields.length == 1) {

                        sql.append(helper.escapeFieldName(pkFields[0]));

                } else {

                        sql.append('*');

                }

                sql.append(") ");

                sql.append("from ");

                if (this.directSQLMode) {

                        sql.append("(");

                        sql.append(getJDBCParameters().getSQL());

                        sql.append(") as _subquery_alias_ ");

                } else {

                        sql.append(getJDBCParameters().tableID());

                }

                sql.append(' ');

                appendWhere(sql, filter);

                return sql.toString();

        }

        protected void appendWhere(StringBuilder sql, String filter) {

                filter = fixFilter(filter);

                String initialFilter = getJDBCParameters().getBaseFilter();

                if ((initialFilter != null && initialFilter.length() != 0)

                                || (filter != null && filter.length() != 0)) {

                        sql.append("where (");

                        if (initialFilter != null && initialFilter.length() != 0

                                        && filter != null && filter.length() != 0) {

                                // initialFilter + filter

                                sql.append('(');

                                sql.append(initialFilter);

                                sql.append(") and (");

                                sql.append(filter);

                                sql.append(')');

                        } else if (initialFilter != null && initialFilter.length() != 0) {

                                // initialFilter only

                                sql.append(initialFilter);

                        } else {

                                // filter only

                                sql.append(filter);

                        }

                        sql.append(") ");

                }

        }

        public void closeDone() throws DataException {

                // Do nothing

        }

        public void opendDone() throws DataException {

                // Nothing to do

        }

        public Envelope getEnvelope() throws DataException {

                this.open();

                String defaultGeometryAttributeName;

                defaultGeometryAttributeName = this.getFeatureStore()

                        .getDefaultFeatureType()

                                .getDefaultGeometryAttributeName();

                if( defaultGeometryAttributeName != null ) {

                        return this.helper.getFullEnvelopeOfField(

                                        this.getJDBCParameters(),

                                        defaultGeometryAttributeName, 

                                        this.getJDBCParameters().getWorkingArea()

                                );

                }

                return null;

        }

        public void resourceChanged(ResourceProvider resource) {

                this.getStoreServices().notifyChange(

                                DataStoreNotification.RESOURCE_CHANGED,

                                resource);

        }

        public boolean allowAutomaticValues() {

                return this.helper.allowAutomaticValues();

        }

        public DataServerExplorer getExplorer() throws ReadException {

                DataManager manager = DALLocator.getDataManager();

                JDBCServerExplorerParameters exParams;

                JDBCStoreParameters params = getJDBCParameters();

                try {

                        exParams = (JDBCServerExplorerParameters) manager

                                        .createServerExplorerParameters(JDBCServerExplorer.NAME);

                        exParams.setHost(params.getHost());

                        exParams.setPort(params.getPort());

                        exParams.setDBName(params.getDBName());

                        exParams.setUser(params.getUser());

                        exParams.setPassword(params.getPassword());

                        exParams.setUrl(params.getUrl());

                        exParams.setCatalog(params.getCatalog());

                        exParams.setSchema(params.getSchema());

                        exParams.setJDBCDriverClassName(params.getJDBCDriverClassName());

                        return manager.openServerExplorer(JDBCServerExplorer.NAME,exParams);

                } catch (DataException e) {

                        throw new ReadException(this.getProviderName(), e);

                } catch (ValidateDataParametersException e) {

                        throw new ReadException(this.getProviderName(), e);

                }

        }

        @Override

        protected void doDispose() throws BaseException {

                this.close();

                resulsetList = null;

                this.helper.dispose();

                super.doDispose();

        }

        public Object createNewOID() {

                return null;

        }

        public String compoundSelect(FeatureType type, String filter, String order,

                        long limit, long offset) throws DataException {

                StringBuilder sql = new StringBuilder();

                JDBCStoreParameters params = getJDBCParameters();

                        FeatureAttributeDescriptor[] fields = type

                                        .getAttributeDescriptors();

                        // Select

                        sql.append("Select ");

                        for (int i = 0; i < fields.length - 1; i++) {

                                sql.append(helper.getSqlFieldName(fields[i]));

                                sql.append(", ");

                        }

                        sql.append(helper.getSqlFieldName(fields[fields.length - 1]));

                        sql.append(' ');

                        FeatureAttributeDescriptor[] pkFields = getStoreServices()

                                        .getProviderFeatureType(type.getId()).getPrimaryKey();

                        if (pkFields != null && pkFields.length > 0) {

                                // checks for pk fields are in select

                                boolean toAdd;

                                for (int i = 0; i < pkFields.length; i++) {

                                        toAdd = true;

                                        for (int j = 0; j < fields.length; j++) {

                                                if (pkFields[i].getName().equals(fields[j].getName())) {

                                                        toAdd = false;

                                                        break;

                                                }

                                        }

                                        if (toAdd) {

                                                sql.append(", ");

                                                sql.append(helper.getSqlFieldName(pkFields[i]));

                                        }

                                }

                                sql.append(' ');

                        }

                        // table

                        sql.append("from ");

                        if (directSQLMode) {

                            sql.append(" (");

                            sql.append(params.getSQL());

                            sql.append(") as _subquery_alias_ ");

                        } else {

                            sql.append(params.tableID());

                        }

                        sql.append(' ');

                        // Where

                        appendWhere(sql, filter);

                        // Order

                        if ((params.getBaseOrder() != null && params.getBaseOrder()

                                        .length() != 0)

                                        || (order != null && order.length() != 0)) {

                                sql.append("order by ");

                                if (order != null && order.length() != 0) {

                                        // order

                                        sql.append(order);

                                } else {

                                        // initial order

                                        sql.append(params.getBaseOrder());

                                }

                                sql.append(' ');

                        }

                // limit offset

                if (limit > 0 || offset > 0) {

                        sql.append(helper.compoundLimitAndOffset(limit,offset));

                }

                return sql.toString();

        }

        public long getFeatureCount() throws DataException {

                return getCount(null);

        }

        public String getProviderName() {

                return NAME;

        }

        public boolean hasGeometrySupport() {

                return false;

        }

        public FeatureSetProvider createSet(FeatureQuery query,

                        FeatureType featureType) throws DataException {

                return new JDBCSetProvider(this, query, featureType);

        }

        public Object getSourceId() {

                return this.getJDBCParameters().getSourceId();

        }

        public String getName() {

                return this.getJDBCParameters().tableID();

        }

        public String getFullName() {

                return this.getJDBCParameters().getHost()+":"+this.getJDBCParameters().getDBName()+":"+this.getJDBCParameters().tableID();

        }

        public ResourceProvider getResource() {

                return getHelper().getResource();

        }

        protected boolean isDirectSQLMode(){

            return directSQLMode;

        }

}