(Hql) Hibernate query language

Hql instance:

1.Query all records in the Table: from category

2.Conditional query with WHERE clause: from Category C where c. Name> 'c5'

3.The result is sorted by a certain field: from Category C order by C. Name DESC (DESC indicates descending order, ASC indicates Ascending Order)

4.Remove duplicate records to obtain a single record: Select distinct C from Category C order by C. Name DESC

5.Query with parameters: from Category C where c. ID>: min and C. ID <: Max. In an hql statement, ': min' indicates a parameter, which can be assigned a value as in JDBC. This can be done in hql, and chain programming is also used here:

Session. createquery ("from Category C where c. ID>: min and C. ID <: Max ")

. Setinteger ("min", 2)

. Setinteger ("Max", 8 );

6.Another query with the hql parameter: from Category C where c. ID>? And C. ID <?

7.Hibernate paging Query

Query q = session. createquery ("from Category C order by C. Name DESC ");

Q. setmaxresults (3 );

Q. setfirstresult (0 );

Setmaxresult () is the maximum display volume per page, and setfirstresult () is the setting where the elements actually start. Here 0 represents the last element.

8.Multi-table join query: Select T. Title, C. name from topic t join t. Category C

9.Hql functions:

A)Count (): Select count (*) from MSG m

B)Max ()-min ()-AVG (): Select max (M. ID), min (M. ID), AVG (M. ID), sum (M. ID) from MSG m

C)Between: From MSG m where M. ID between 3 and 5

D)In: From MSG m where M. ID in (3, 4, 5)

10.Is null; is not null: From MSG m where M. cont is not null

11.Is empty: From topic t where T. msgs is empty

12.Like: From topic t where T. Title like '% 5'.'%'Match All characters,'_'Match a single character.

13.

Some Function functions, but it is not important to know:Select lower (T. Title ),"+

 "Upper (T. Title ),"+

 "Trim (T. Title ),"+

 "Concat (T. Title ,'***'),"+

 "Length (T. Title )"+

 "From topic t")

Trim () removes spaces at the beginning and end and returns a copy of the string. Concat () connects the string to be queried.

14. Abs ()-SQRT ()-Mod (): Select ABS (T. ID ),"+ "SQRT (T. ID ),"+"Mod (T. ID, 2 )"+"From topic t

15.Obtain the current time:Select current_date, current_time, current_timestamp, T. ID from topic t

16. HavingClause:Select T. Title, count (*) from topic T group by T. Title having count (*) <= 1

17. exist:From topic t where not exists (select M. ID from MSG m where M. Topic. ID = T. ID)

Note: In can also implement the exist function, but the execution efficiency of exist is high.

18. Update usage: Update topic t set T. Title = upper (T. Title)

19. hqlThree methods of deletion:

HibernateDeletion method:

/*Method 1*/
String hql = "select P from province as P where P. ID =? ";
Query query = session. createquery (hql );
Query. setstring (0, ID );
Province P = (province) query. List (). Get (0 );
Session. Delete (P );
/*Method 2*/
String hql = "delete province where id =? ";
Query query = session. createquery (hql );
Query. setstring (0, ID );
Int x = query.exe cuteupdate ();
If (x> 0 ){
Flag = true;
}
/*Method 3*/
Province P = (province) Session. Get (province. Class, ID );
Session. Delete (P );

Method 1 is relatively cumbersome.

Method 2HqlDo not addAs +Alias!

Method 3:HibernateBuilt-in methods.

20.Query some fields in the table:

Method 1: Create a constructor for this class, input the desired parameter, and thenHqlThe statement can be written as follows:

Select New Class (C. Name, C. Date, C. Sex) from Class

Method 2: UseJDBCOfSQLStatement:

SEssion. createsqlquery (SQL );