A comparison example of HQL function summary and fetch current time in Hibernate

In many cases, the Data Access Layer (DAO layer) that we are responsible for usually uses SQL statements or HQL statements, and sometimes error when we use the HQL statement stitching, usually because we use standard SQL statements to open object-oriented statements that are indeed hibernate Sessionfactory.getcurrentsession (). createquery (SQL);

We should change to Sessionfactory.getcurrentsession (). createsql (SQL);

But sometimes the project requires the use of HQL, for example, the corresponding interface is encapsulated into a jar package, I encountered such a situation, in the use of HQL statement comparison of the current time, the beginning of the use of SQL statement splicing, the results have been error. Here to show you the functions commonly used in HQL:

　　

Function name	Description	Type	Support	How to use	Note
ABS (N)	Take absolute value	Mathematical functions	JPAQL HQL	ABS (column_name[numeric type Object Properties])
SQRT (N)	Take the square root	Mathematical functions	JPAQL HQL	SQRT (column_name[numeric type Object Properties])
MOD (x, y)	Take the remainder	Mathematical functions	JPAQL HQL	MOD ([object attribute (number) or Value],[object property (number) or value])	Number must be integral, return the remainder of parameter 1/Parameter 2
SIZE (c)	Number of objects in the method collection	Aggregate functions	JPAQL HQL
Minelement (c)	Returns the smallest element in the collection	Aggregate functions	HQL
Maxelement (c)	Returns the largest element in a collection	Aggregate functions	HQL
Minindex (c)	Returns the index collection minimum index	Aggregate functions	HQL
Maxindex (c)	Returns the Index collection maximum index	Aggregate functions	HQL
MAX (N)	Returns the maximum value	Aggregate functions	JPQHQL HQL
MIN (N)	Returns the minimum value	Aggregate functions	JPQHQL HQL
COUNT (N)	return count	Aggregate functions	JPQHQL HQL
CONCAT (S1,S2)	Connect a string	String functions	JPQHQL HQL	CONCAT ([Object Properties],[Object Properties])
SUBSTRING (S,offset,length)	return substring	String functions	JPQHQL HQL	SUBSTRING ([string attribute field to intercept], start position, intercept length)
TRIM ([[[Both/leading/trailing]] char from s)	Default to remove spaces on both sides of a string	String functions	JPQHQL HQL	TRIM ([String object property column])	Remove spaces at both ends of the field
LOWER (s)	Lowercase	String functions	JPQHQL HQL	LOWER ([String object property column])	Capitalize all the letters in the column results
UPPER (s)	Capital	String functions	JPQHQL HQL	UPPER ([String object property column])	Capitalize all the letters in the column results
LENGTH (s)	return string length	String functions	JPQHQL HQL	LENGTH (field name)	Returns the length of the field content, including numbers. A null value returns NULL.
Current_date ()	Returns the current date of the database	Time function	JPAQL HQL	Current_date ()	Returns the current date of the database
Current_time ()	Time	Time function	JPAQL HQL	Current_time ()	Returns the current time of the database
SECOND (d)	Extract a specific second from a date	Time function	HQL	SECOND (Time field)	Empty when NULL is returned
MINUTE (d)	Extract specific points from the date	Time function	HQL	MINUTE (Time field)	Empty when NULL is returned
HOUR (d)	Extracting specific hours from a date	Time function	HQL	HOUR (Time field	Empty when NULL is returned
Day (d)	Extract a specific day from a date	Time function	HQL	Day (Time field)	Empty when NULL is returned
MONTH (d)	Extract specific months from a date	Time function	HQL	MONTH (Time field)	Empty when NULL is returned
Year (d)	Extracting a specific year from a date	Time function	HQL	Year (Time field)	Empty when NULL is returned

Eg: the database is compared to the current time at a time

1. String hql = "from" + User.class.getName () + "WHERE (to_days (registerdate) >= to_days (Current_timestamp ()) and to_ Days (Registerdate) < To_days (Current_timestamp ()) + 1) ";

2. String hql = "from" + User.class.getName () + "WHERE Unix_timestamp (Inserttime) > Unix_timestamp (current_timestam P ())-24 * 60 * 60 ";

Note: Both the HQL statement and the SQL statement do not recommend using Current_date ()-1 or currentdate () +1, which is usually no problem, but an exception is encountered in special cases. For example, the current date is November 1, then current_date ()-1 will generate date 1970-01-01, and then the condition query will not be anything.

A comparison example of HQL function summary and fetch current time in Hibernate