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

Source: Internet
Author: User
Tags abs mathematical functions square root

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.