Hibernate common query Statement (HQL)

Hibernate Query Language HQL

Keywords are case-insensitive in hql, but property and class names are case-sensitive

1, simple attribute query "important"
* A single property query that returns a list of result set properties consistent with the corresponding attribute types in the entity class and the element type
* Multiple attribute queries, the returned collection element is an object array, the type of the array element and the corresponding property in the entity class are the same type
The length of the array depends on the number of attributes in the Select
* If you think that the return array is not enough to object, you can use HQL dynamic instantiation of Student objects
See also: Simplepropertyquerytest.java

Returns a list of result set attributes, with the corresponding property type in the element type and entity class consistent list students = Session.createquery ("Select name from Student"). List (); Querying multiple properties whose collection element is the type of the object array//array element and the corresponding property in the entity class the type of the consistent//array length depends on the number of attributes in the select list students = Session.createquery ("SELECT ID," Name from Student "). List (); If you think that the return array is not enough to object, you can use HQL to dynamically instantiate the Student object//In this case, the Student object Collection List students = Session.createquery ("Select New Student" ( ID, name) from Student "). List (); You can use the alias list students = Session.createquery ("Select S.id, s.name from Student S"). List (); You can use the as named alias list students = Session.createquery ("Select S.id, S.name from Student as S"). List ();

2, Entity object Query "important"
* N + 1 problem, by default, using Query.iterate query, there can be n+1 problems
The so-called n+1 is to send out a n+1 SQL statement at the time of query
1: First issue a Query object ID list of SQL
N: Query from the ID list to the cache, if there is no matching data in the cache, the corresponding SQL statement will be issued according to the ID
* The difference between list and iterate.
* The list emits SQL statements each time, and the list puts data into the cache without taking advantage of the data in the cache
* Iterate: Iterate cached data is used by default, but n+1 problems can occur if there is no data in the cache
See also: Simpleobjectquerytest1.java/simpleobjectquerytest2.java

 //returns a collection of Student objects//Can ignore select list students = Session.createquery ("from Student"). List (); Returns a collection of Student objects//You can omit the Select, and the table can use the alias list students = Session.createquery ("from Student S"). List (); Returns a collection of Student objects//You can ignore a select, which can use the as named alias list students = Session.createquery ("From Student as S"). List (); Returns a collection of Student objects//using Select to query entity objects, you must use the alias list students = Session.createquery ("Select S from Student as S"). List (); SELECT * FROM ... is not supported. Such a query statement list students = Session.createquery ("SELECT * from Student"). List (); /** * Use list query to issue a query statement, obtain student object data, * * Hibernate:select student0_.id as id1_, student0_.name as name1_, * student0_.cr Eatetime as Createtime1_, Student0_.classesid as classesid1_ * FROM t_student student0_ * * */List students = Session.creat Equery ("from Student"). List (); /** * N+1 Issue * * 1: SQL * hibernate:select with Query ID list student0_.id as col_0_0_ from t_student student0_ * * N: In order to issue according to ID query St Udent object's SQL * Hibernate:select student0_.id as id1_0_, student0_.name as name1_0_,* Student0_.createtime as createtime1_0_, Student0_.classesid as classesid1_0_ * from T_student student0_ where student0_. Id=? * */iterator ITER = Session.createquery ("from Student"). Iterate (); /** * No n+1 problem * Because the list operation has placed the student object in a level-one cache. So when you use the iterate operation again * It first emits a query ID list of SQL, the data from the ID to the cache, only in the cache can not find the corresponding * data To send SQL to the database query * * * * * * * * * * */** * Query SQL * * By default the list sends the SQL of the query object to the database each time, unless the query cache is configured, the following list operation * Although there is already object data in the first level cache, However, the list does not use the cache by default, and it emits SQL * * By default, the list puts data into the cache but does not use the data */students = Session.createquery ("from Student"). List ();

3, the condition inquiry "important"
* Can be used to pass parameters in the form of a spelling string
* Can be used. To pass the argument (the index starts at 0)
* Can be used: parameter name to pass parameters
* If you pass multiple parameters, you can use the Setparamterlist method
* Functions of the database can be used in hql, such as: Date_format
See also: Simpleconditionquerytest.java

You can spell the string list students = Session.createquery ("Select S.id, s.name from Student s where s.name like '%1% '"). List (); Query query = Session.createquery ("Select S.id, s.name from Student s where s.name like?"); Query.setparameter (0, "%1%"); List students = Query.list (); can be used. The parameter value passed by the index of the parameter//parameter in the way passed from 0//is not enclosed by single quotes. Note Method Chain Programming List students = Session.createquery ("Select S.id, s.name from Student s whe Re s.name like? "). Setparameter (0, "%1%"). List (); Use: Parameter name to pass parameter value List students = Session.createquery ("Select S.id, s.name from Student s where S.name like:myname"). S Etparameter ("MyName", "%1%"). List (); Passing parameter values by using: Parameter name List students = Session.createquery ("Select S.id, s.name from Student s where S.name like:myname and S.id=:myid "). Setparameter (" MyName ","%1% "). Setparameter (" myID ", a). List (); In, you need to use Setparameterlist for parameter passing List students = session.createquery ("Select S.id, s.name from Student s where S.id in (: M Yids) "). Setparameterlist (" Myids ", New Object[]{1, 2, 3, 4, 5}). List (); Query for February 2008 student List students = session.createquery ("Select S.id, s.name from Student s where Date_format (S.createtime, ' %y-%m ') =? "). Setparameter (0, "2008-02"). List (); Query from 2008-01-10 to 2008-02-15 student List students = session.createquery ("Select S.id, s.name from Student s where s.createtime Between? and? "). Setparameter (0, Sdf.parse ("2008-01-10 00:00:00")). Setparameter (1, Sdf.parse ("2008-02-15 23:59:59")). List (); 

4, Hibernate also support the direct use of SQL query
See also: Sqlquerytest.java

List students = Session.createsqlquery ("SELECT * from T_student"). List (); For (iterator Iter=students.iterator (); Iter.hasnext ();) {object[] obj = (object[]) iter.next (); System.out.println (Obj[0] + "," + obj[1]); }

5. External named query
* Use <query> tags in the mapping file to define HQL
* Using Session.getnamedquery () method in the program to get the HQL query string
See also: Student.hbm.xml, Namequerytest.java

<query name= "Searchstudents" > <!--[cdata[SELECT s from Student s where s.id<?]] --> </query> List students = Session.getnamedquery ("searchstudents"). Setparameter (0). List ();

6. Query Filter
* Define filter parameters in the mapping file
* Use these parameters in the mapping of the class
* Enable the filter in the program
See also: Student.hbm.xml, Filterquerytest.java

<class name= "com.bjsxt.hibernate.Student" table= "t_student" > <id name= "id" > <generator class= "Native" /> </id> <property name= "name"/> <property name= "createtime"/> <many-to-one name= "Classes" column= "Classesid"/> <filter name= "filtertest" condition= "ID <: myID"/> </class> <filter-def Name = "Filtertest" > <filter-param name= "myID" type= "integer"/> </filter-def> session.enablefilter (" Filtertest "). Setparameter (" myID ", 10); List students = Session.createquery ("from Student"). List ();

7, paging query "important"
* Setfirstresult (), starting from 0
* Setmaxresults, how many data per page display
See also: Pagequerytest.java

List students = Session.createquery ("from Student"). Setfirstresult (1). Setmaxresults (2). List ();

8, the object navigation query, HQL in the use. Navigating "Important"
See also: Objectnavquerytest.java

List students = Session.createquery ("Select S.name from Student s where s.classes.name like '%1% '"). List ();

9, connection query "important"
* Internal Company
* OUTER JOIN (left/right)
See also: Joinquerytest.java

List students = Session.createquery ("Select C.name, s.name from Student s join s.classes C"). List (); List students = Session.createquery ("Select C.name, s.name from Student s inner join s.classes C"). List ();

10, statistical inquiry "important"
See also: Statquerytest.java

List Students =session.createquery ("SELECT count (*) from Student"). List (); Long Count = (long) students.get (0); System.out.println (count); Long Count = (long) session.createquery (select COUNT (*) from Student). Uniqueresult ();

11, DML style of operation (as little as possible, because the slow to save the different steps)
See also: Dmlquerytest.java

Session.createquery ("Update Student s set s.name=?") Where S.id <? "). Setparameter (0, "Dick"). Setparameter (1, 5). Executeupdate ();