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