HQL Functions

Hibernate Query Language (HQL) makes some additional functions available regardless of the underlying database in use.

BIT_LENGTH

Returns the length of binary data.
List<Number> bits = entityManager.createQuery(
 "select bit_length( c.duration ) " +
 "from Call c ", Number.class )
.getResultList();

CAST

Performs a SQL cast. The cast target should name the Hibernate mapping type to use. See the data types chapter on for more information.
List<String> durations = entityManager.createQuery(
 "select cast( c.duration as string ) " +
 "from Call c ", String.class )
.getResultList();

EXTRACT

Performs a SQL extraction on DateTime values. An extraction extracts parts of the DateTime (the year, for example).
List<Integer> years = entityManager.createQuery(
 "select extract( YEAR from c.timestamp ) " +
 "from Call c ", Integer.class )
.getResultList();
See the abbreviated forms below.

YEAR

Abbreviated extract form for extracting the year.
List<Integer> years = entityManager.createQuery(
 "select year( c.timestamp ) " +
 "from Call c ", Integer.class )
.getResultList();

MONTH

Abbreviated extract form for extracting the month.
List<Integer> months = entityManager.createQuery(
 "select month( c.timestamp ) " +
 "from Call c ", Integer.class )
.getResultList();

DAY


Abbreviated extract form for extracting the day.

List<Integer> days= entityManager.createQuery(
 "select day( c.timestamp ) " +
 "from Call c ", Integer.class )
.getResultList();

HOUR


Abbreviated extract form for extracting the hour.

MINUTE

Abbreviated extract form for extracting the minute.

SECOND

Abbreviated extract form for extracting the second.

STR

Abbreviated form for casting a value as character data.
List<String> timestamps = entityManager.createQuery(
 "select str( c.timestamp ) " +
 "from Call c ", String.class )
.getResultList();
List<String> timestamps = entityManager.createQuery(
 "select str( cast(duration as float) / 60, 4, 2 ) " +
 "from Call c ", String.class )
.getResultList();

Reference


Comments