Application: gvSIG desktop

    public Exists(Formatter<Value> formatter) {

  public Select(Formatter<Value> formatter) {

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

import org.gvsig.expressionevaluator.ExpressionBuilder;

import org.gvsig.expressionevaluator.ExpressionBuilder.Value;

import org.gvsig.expressionevaluator.Formatter;

/**

 *

 * @author jjdelcerro

 */

public class JDBCFormatter implements Formatter<Value> {

    private final Formatter<ExpressionBuilder.Value>[] formatters;

    public JDBCFormatter(Formatter<Value> formatter) {

        this.formatters = new Formatter[]{

            new Exists(this),

            new Select(this)

        };

    }

    @Override

    public boolean canApply(Value value) {

        for (Formatter<Value> formatter : formatters) {

            if (formatter.canApply(value)) {

                return true;

            }

        }

        return false;

    }

    @Override

    public String format(Value value) {

        for (Formatter<Value> formatter : formatters) {

            if (formatter.canApply(value)) {

                return formatter.format(value);

            }

        }

        return value.toString(this);

    }

}

import org.gvsig.expressionevaluator.spi.formatter.value.BaseFormatter;

import org.gvsig.fmap.dal.store.jdbc2.spi.expressionbuilderformatter.JDBCFormatter;

            new BaseFormatter(this),

            new JDBCFormatter(this),

            new ST_AsBinary(this.builder, this)

import java.time.LocalDate;

import java.time.LocalTime;

import org.gvsig.expressionevaluator.ExpressionBuilder;

import org.gvsig.expressionevaluator.ExpressionUtils;

import org.gvsig.expressionevaluator.GeometryExpressionBuilder;

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

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

    public void testFilterByDate() throws Exception {

        FeatureStore sourceStore = TestUtils.openSourceStore1();

        JDBCServerExplorer explorer = TestUtils.openServerExplorer(DBNAME);

        createFrom(explorer, sourceStore);        

        copyFrom(explorer, sourceStore, FeatureStore.MODE_APPEND);

        FeatureStore h2Store = openTargetStore1(explorer);

        ExpressionBuilder expbuilder = ExpressionUtils.createExpressionBuilder();

        String filter = expbuilder.or(

          expbuilder.and(

            expbuilder.gt(

                    expbuilder.column("Time"), 

                    expbuilder.time("01:02:03")

            ),

            expbuilder.lt(

                    expbuilder.column("Time"), 

                    expbuilder.time(new Date())

            )

          ),

          expbuilder.and(

            expbuilder.gt(

                    expbuilder.column("Date"), 

                    expbuilder.date("2019-02-17")

            ),

            expbuilder.lt(

                    expbuilder.column("Date"), 

                    expbuilder.date(new Date())

            )

          )

        ).toString();

        FeatureQuery query = sourceStore.createFeatureQuery();

        query.addFilter(filter);

        List<Feature> features = h2Store.getFeatures(query);

        int sz = features.size();

    }

package org.gvsig.fmap.dal.store.h2.operations.sql;

import java.text.ParseException;

import java.text.SimpleDateFormat;

import java.util.Date;

import java.util.List;

import junit.framework.TestCase;

import org.gvsig.expressionevaluator.GeometryExpressionBuilder;

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

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

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

import org.gvsig.fmap.dal.store.h2.TestUtils;

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

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

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

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

import org.gvsig.fmap.dal.store.jdbc2.spi.operations.ResultSetForSetProviderOperation;

import org.gvsig.tools.library.impl.DefaultLibrariesInitializer;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

public class TestDate extends TestCase {

  private static final Logger LOGGER = LoggerFactory.getLogger(TestDate.class);

  public TestDate(String testName) {

    super(testName);

  }

  @Override

  protected void setUp() throws Exception {

    super.setUp();

    new DefaultLibrariesInitializer().fullInitialize();

  }

  @Override

  protected void tearDown() throws Exception {

    super.tearDown();

  }

  private java.sql.Date date(String x) throws ParseException {

    Date d;

    if (x == null) {

      d = new Date();

    } else {

      d = new SimpleDateFormat("yyyy-M-d").parse(x);

    }

    return new java.sql.Date(d.getTime());

  }

  private java.sql.Time time(String x) throws ParseException {

    Date d = new SimpleDateFormat("H:mm:ss").parse(x);

    return new java.sql.Time(d.getTime());

  }

  private java.sql.Timestamp timestamp(String x) throws ParseException {

    Date d = new SimpleDateFormat("yyyy-M-d H:mm:ss").parse(x);

    return new java.sql.Timestamp(d.getTime());

  }

  public void testDate1() throws Exception {

    JDBCHelper helper = TestUtils.getJDBCHelper();

    JDBCSQLBuilderBase sqlbuilder = helper.createSQLBuilder();

    OperationsFactory operations = helper.getOperations();

    List<String> expectedSQLs = TestUtils.getSQLs("date.sql");

    FeatureStore sourceStore = TestUtils.openSourceStore1();

    TableReference table = operations.createTableReference(

            "dbtest",

            sqlbuilder.default_schema(),

            "test",

            null

    );

    GeometryExpressionBuilder expbuilder = sqlbuilder.expression();

    SimpleDateFormat df = new SimpleDateFormat();

    String filter = expbuilder.or(

            expbuilder.and(

                    expbuilder.gt(

                            expbuilder.column("Time"),

                            expbuilder.time("01:02:03")

                    ),

                    expbuilder.lt(

                            expbuilder.column("Time"),

                            expbuilder.time("20:52:55")

                    )

            ),

            expbuilder.and(

                    expbuilder.gt(

                            expbuilder.column("Date"),

                            expbuilder.date("2019-02-17")

                    ),

                    expbuilder.lt(

                            expbuilder.column("Date"),

                            expbuilder.date("2020-02-23")

                    )

            )

    ).toString();

    FeatureType featureType = sourceStore.getDefaultFeatureType();

    FeatureQuery query = sourceStore.createFeatureQuery();

    query.addFilter(filter);

    ResultSetForSetProviderOperation resultSetForSetProvider = operations.createResultSetForSetProvider(

            table,

            null,

            null,

            query,

            featureType,

            featureType,

            0,

            0,

            0

    );

    String sql = resultSetForSetProvider.getSQL();

    System.out.println("###### SQL:" + sql);

    System.out.println("###### EXP:" + expectedSQLs.get(0));

    assertEquals("Date SQL", expectedSQLs.get(0), sql);

  }

  // TODO: a?adir un test con where, group y order.

}

-- Date SQL

-- Simple

SELECT 

    "ID", "Byte", "Bool1", "Long", "Timestamp", "Date", "Time", "Bool2", 

    "String", "Bool3", "Double", "Bool4", "Float", "Bool5", "Decimal", 

    NVL2("Geometry",ST_AsBinary("Geometry"),NULL) 

  FROM "PUBLIC"."test" 

  WHERE ((

    ("Time" > TIME '01:02:03') AND 

    ("Time" < TIME '20:52:55')

    ) OR (

    ("Date" > DATE '2019-02-17') AND 

    ("Date" < DATE '2020-02-23')

  ));

  public boolean isValid(StringBuilder message);

import javax.swing.JOptionPane;

import org.gvsig.tools.swing.api.threadsafedialogs.ThreadSafeDialogsManager;

    public boolean isValid(StringBuilder message) {

        int searchMode = this.tabSearchMode.getSelectedIndex();

        SearchConditionPanel panel = this.conditionPanels.get(searchMode);

        boolean valid = panel.isValid(message);

        return valid;

    }

        StringBuilder message = new StringBuilder();

        if( !this.isValid(message) ) {

          ThreadSafeDialogsManager dialogManager = ToolsSwingLocator.getThreadSafeDialogsManager();

          dialogManager.messageDialog(

                  "_The_specified_search_condition_is_not_valid", 

                  "_Search",

                  JOptionPane.WARNING_MESSAGE

          );

          return;

        }

  public boolean isValid(StringBuilder message) {

    if( this.advancedExpression.isValid() ) {

      return true;

    }

    message.append("The expression entered is not valid.");

    return false;

  }

  @Override

  @Override

  public boolean isValid(StringBuilder message) {

    return true;

  }

  public boolean isEmpty();

  public List<EditableFeatureAttributeDescriptor> getColumns();

import static org.gvsig.expressionevaluator.ExpressionBuilder.BASE_FORMATTER;

            return this.toString(BASE_FORMATTER);

        return ExpressionBuilder.BASE_FORMATTER;

    private final List<EditableFeatureAttributeDescriptor> extraColumns;

    public boolean isEmpty() {

      return this.extraColumns.isEmpty();

    }

    @Override

        EditableFeatureAttributeDescriptor newAttr = new DefaultEditableFeatureAttributeDescriptor(null, false);