Package ch.tocco.nice2.persist.core.impl.hibernate.query.function

Class BirthdayQueryFunction

java.lang.Object
ch.tocco.nice2.persist.core.api.hibernate.query.AbstractJdbcFunction
ch.tocco.nice2.persist.core.impl.hibernate.query.function.BirthdayQueryFunction
All Implemented Interfaces:
JdbcFunction
@Component public class BirthdayQueryFunction extends AbstractJdbcFunction
This function adds a boolean expression that tests for a given date to be inside a given date range while not taking the year into account. That is especially useful for birthdays (hence the name), for example to find all users whose birthday is in the next 7 days.

This function would add postgreSQL specific SQL to the query, that looks like this: 

 select pk from nice_user
 where
   extract(year from age('current_date'::date, birthday))
    -
   extract(year from age('current_date'::date + '7 days'::interval, birthday))
   != 0
where birthday is the column containing a date/datetime value and current_date is the reference date (the current time). Type casts in the SQL statement allow to specify date or datetime values.

The statement counts the days from the birthday until (now) and subtracts the days from birthday until (now + 7 days). If the result is < 0, the age incremented and thus the person has its birthday in this range. If we test for '!= 0' instead of '< 0' we can also use negative intervals, to get all persons whose birthday was last week, for example.

There are three parameters mandatory for this function:

  • date the date to test, this is either a path to a field of type date or datetime, or a DateTime value.
  • reference the date/datetime used as the reference. This can again, be a path or a value. While being a value, it is possible to specify strings which are known to postgreSQL, such as "now".
  • interval the interval string used for calculating the range. This must be a PostgreSQL specific interval definition, like "7 days". It's possible to use negative intervals, like "-7 days".
A valid example for a query using this function: 
     find User where BIRTHDAYIN(birthdate, "now", "9 days")

  • Constructor Details

    • BirthdayQueryFunction

      public BirthdayQueryFunction(TypeManager typeManager)

  • Method Details

    • createFunction

      public org.hibernate.dialect.function.SQLFunction createFunction(org.hibernate.type.TypeResolver typeResolver, SqlWriter sqlWriter)
      Returns:
      an SQLFunction that contains the raw sql statement that is executed on the db.
      See Also:
      • SQLFunctionTemplate

    • validateArguments

      public void validateArguments(javax.persistence.criteria.Expression<?>[] arguments)

    • argumentCount

      public int argumentCount()
      Returns:
      the number of expected arguments.

    • argumentOrder

      public int[] argumentOrder()
      Description copied from interface: JdbcFunction
      Order of parameters as required in SQL statement. This method must be implemented when the order or count of query parameters is different to the SQL parameters. The returned array lists indices of params in order as they are used for the SQL query.