Programming and So

This is an old issue:

java.math.BigDecimal type must be used in order to insert java.lang.Long objects in Ms Access databases via Jdbc Odbc brigde.

Reference: Sun Forum

Following example configures one quartz job to run on normal days and another quartz job to run on holidays dates. That is, one quartz trigger run when the other not. I imagine some easier solution exists, but it works.

applicationContext.xml

<!-- Properties file -->
<bean id="properties" class="org.springframework.beans.factory.config.PropertiesFactoryBean">
<property name="location" value="file:/foo/dates.prop"/>
</bean>

<!-- QUARTZ Configuration -->
<bean class="org.springframework.scheduling.quartz.SchedulerFactoryBean">
<property name="triggers">
	<list>
            <ref bean="normalTrigger" />
            <ref bean="holidayTrigger" />
        </list>
    </property>
<property name="calendars">
        <map>
            <entry key="calendarNormal"><ref bean="normalCalendar" /></entry>
            <entry key="calendarHoliday"><ref bean="holidayCalendar" /></entry>
        </map>
    </property>
</bean>

<!-- Everyday but holidays -->
<bean id="normalCalendar" class="MyNormalCalendar">
    <constructor -arg><ref bean="properties" /></constructor>
</bean>

<!-- Every holiday -->
<bean id="holidayCalendar" class="MyHolidayCalendar">
    <constructor -arg><ref bean="properties" /></constructor>
</bean>

<!-- Normal Job -->
<bean id="normalTrigger" class="org.springframework.scheduling.quartz.CronTriggerBean">
<property name="jobDetail" ref="someJobDetail" />
<property name="calendarName" value="calendarNormal"/>
<property name="cronExpression">
        <!-- Every five minutes from 8h to 14h -->
        <value>0 0/5 8-14 * * ?</value>
    </property>
</bean>

<!-- Holiday Job -->
<bean id="holidayTrigger" class="org.springframework.scheduling.quartz.CronTriggerBean">
<property name="jobDetail" ref="anotherJobDetail" />
<property name="calendarName" value="calendarHoliday"/>
<property name="cronExpression">
        <!-- Every five minutes from 8h to 14h -->
        <value>0 0/5 8-14 * * ?</value>
    </property>
</bean>

dates.prop
workingDays.legalHolidays=2008-01-01,2008-03-21,2008-03-24,2008-05-01,2008-05-08,2008-05-12,2008-07-14,2008-08-15,2008-11-11,2008-12-25

MyHolidayCalendar.java

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.GregorianCalendar;
import java.util.Properties;

import org.quartz.impl.calendar.HolidayCalendar;

/**
 *
 * Activate trigger on holidays.
 *
 * @author gz451w
 *
 */
@SuppressWarnings("serial")
public class MyHolidayCalendar extends HolidayCalendar {

  private static final String WORKING_DAYS_LEGAL_HOLIDAYS = "workingDays.legalHolidays";
  private Properties properties;

  /**
   * Build activate days list
   * @param properties
   */
  public MyHolidayCalendar(Properties properties) {

    this.properties = properties;

    String stringDates = getProperties().getProperty(WORKING_DAYS_LEGAL_HOLIDAYS);

    if (stringDates != null) {
      Calendar[] dates = getDatesInverse(stringDates);
      for (Calendar date : dates) {
        addExcludedDate(date.getTime());
      }
    }
  }

  /**
   * @return the properties
   */
  public Properties getProperties() {
    return properties;
  }

  /**
   * @param properties the properties to set
   */
  public void setProperties(Properties properties) {
    this.properties = properties;
  }

  /**
   * Inverse list from a date list
   * @param stringDates
   * @return
   */
  private Calendar[] getDatesInverse(String stringDates) {

    String[] stringDatesArray = stringDates.split(",");

    // Holiday list
    ArrayList<calendar> dates = new ArrayList</calendar><calendar>();
    for (String stringDate : stringDatesArray) {
      String[] splitDate = stringDate.split("-");
      Calendar date =
        new GregorianCalendar(
            Integer.parseInt(splitDate[0]),
            Integer.parseInt(splitDate[1]) - 1,  // Month field starts with 0!
            Integer.parseInt(splitDate[2]));
      dates.add(date);
    }

    // Exclude non-holiday dates
    ArrayList</calendar><calendar> returnDates = new ArrayList</calendar><calendar>();
    try {

      Calendar c = Calendar.getInstance();
        c.setTime(new SimpleDateFormat("dd/MM/yyyy").parse("01/01/" + dates.get(0).get(Calendar.YEAR)));

      while(c.get(Calendar.YEAR) == dates.get(0).get(Calendar.YEAR)){
        if (!dates.contains(c)) {
          returnDates.add((Calendar)c.clone());
        }
          c.add(Calendar.DAY_OF_YEAR, 1);
        }

    } catch (Exception e) {
      e.printStackTrace();
    }

    return returnDates.toArray(new Calendar[]{});

  }

}

MyNormalCalendar.java

import java.util.Calendar;
import java.util.GregorianCalendar;
import java.util.Properties;

import org.quartz.impl.calendar.HolidayCalendar;

/**
 *
 * Activate trigger on non-holiday dates.
 *
 * @author gz451w
 *
 */
@SuppressWarnings("serial")
public class MyNormalCalendar extends HolidayCalendar {

  private static final String WORKING_DAYS_LEGAL_HOLIDAYS = "workingDays.legalHolidays";

  private Properties properties;

  /**
   * Exclude holidays
   * @param properties
   */
  public MyNormalCalendar(Properties properties) {

    this.properties = properties;

    String stringDates = getProperties().getProperty(WORKING_DAYS_LEGAL_HOLIDAYS);
    if (stringDates != null) {
      Calendar[] dates = getDates(stringDates);
      for (Calendar date : dates) {
          addExcludedDate(date.getTime());
      }
    }
  }

  /**
   * @return the properties
   */
  public Properties getProperties() {
    return properties;
  }

  /**
   * @param properties the properties to set
   */
  public void setProperties(Properties properties) {
    this.properties = properties;
  }

  /**
   * Get holidays
   * @param stringDates
   * @return
   */
  private Calendar[] getDates(String stringDates) {

    String[] stringDatesArray = stringDates.split(",");

    Calendar[] dates = new Calendar[stringDatesArray.length];
    int i = 0;
    for (String stringDate : stringDatesArray) {
      String[] splitDate = stringDate.split("-");
      Calendar date =
        new GregorianCalendar(
            Integer.parseInt(splitDate[0]),
            Integer.parseInt(splitDate[1]) - 1,  // Month field starts with 0!
            Integer.parseInt(splitDate[2]));
      dates[i++] = date;
    }
    return dates;

  }

}

Consider following SQL queries:

select count(1)
from request
where request_date between
to_date(?, 'DD-MM-YYYY HH24:MI:SS') and
to_date(?, 'DD-MM-YYYY HH24:MI:SS')

select count(1)
from request
where request_date between ? and ?

It seems both queries should be similar. Moreover, I thought the second one would be the fastest. However, the sample below shows another reality.

import oracle.jdbc.pool.OracleDataSource;

import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

/**
 * Sample class demonstrating Oracle 10g date manipulation
 */
public class OracleTest extends JdbcDaoSupport {

  // Query using Oracle date conversion
  static final String countRequests1 =
    "select count(1) " +
        "from request " +
    "where request_date between to_date(?, 'DD-MM-YYYY HH24:MI:SS') and " +
                            "to_date(?, 'DD-MM-YYYY HH24:MI:SS')";

  // Query using JDBC date conversion
  static final String countRequests2 =
    "select count(1) " +
        "from request " +
    "where request_date between ? and ?";

  // Compare two queries
  private void doQueries() throws Exception {

    // Oracle Datasource
    OracleDataSource ds = new OracleDataSource();
    ds.setURL("jdbc:oracle:thin:@127.0.0.1:2000:PREMIDBP");
    ds.setUser("sdf_monitor");
    ds.setPassword("sdf_monitor");
    SimpleJdbcTemplate jdbcTemplate = new SimpleJdbcTemplate(ds);

    // Date parameters
    Calendar c = Calendar.getInstance();
    Date dateTo = (Date)c.getTime().clone();
    c.add(Calendar.MINUTE, -1);
    c.set(Calendar.MILLISECOND, 0);
    Date dateFrom = c.getTime();

    // String parameters
    String dateFromString = new SimpleDateFormat("dd-MM-yyyy HH:mm:ss").format(dateFrom);
    String dateToString = new SimpleDateFormat("dd-MM-yyyy HH:mm:ss").format(dateTo);

    // Query countRequests1
    long initTime = System.nanoTime();
    jdbcTemplate.queryForInt(countRequests1, dateFromString, dateToString);
    System.out.println("Query 1: " + (System.nanoTime() - initTime) / 1000000 + " ms.");

    // Query countRequests2
    initTime = System.nanoTime();
    jdbcTemplate.queryForInt(countRequests2, dateFrom, dateTo);
    System.out.println("Query 2: " + (System.nanoTime() - initTime) / 1000000 + " ms.");

  }

  // Launcher
  public static void main(String... args) throws Exception {
        new OracleTest().doQueries();
  }

}

With my data results are:

Query 1: 1208 ms.
Query 2: 20038 ms.


Curious behaviour.

Edited!

An explanation can be always found (Spanish link): JDBC 9.x y las variables de Timestamp de Java