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

    • getName

      public String getName()

    • render

      protected void render(JdbcFunctionWriter writer, List<? extends org.hibernate.sql.ast.tree.SqlAstNode> arguments)
      Specified by:
      render in class AbstractJdbcFunction

    • validateArguments

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

    • argumentCount

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