Application: gvSIG desktop

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.

}