Hibernate's powerful HQL query

Hibernate is equipped with a very powerful query language that looks like SQL. But instead of being fooled by the similarity in grammatical structures, HQL is very conscious of being designed as a fully object-oriented query that can understand concepts such as inheritance, polymorphism, and correlation.

Basic rules

1. The HQL syntax is similar to SQL and is a statement from the select from structure. But what he followed was not a table name and a field name, but a class name and a property name.
2. HQL Basic query syntax is similar to SQL
3. HQL is not case sensitive. However, the Java class name, package name, and property name are designed to be case sensitive.
4. The use of the package name. For example: If the registered entity class EMP has only one class, then the query can not add the package name, hibernate will automatically retrieve the EMP class. But if you register multiple entity classes, the names are called EMP. It is time to add the package name to differentiate multiple entity classes.

First HQL Query

1  Packagecom.qcf.test;2 3 Importjava.util.List;4 5 6 ImportOrg.hibernate.Query;7 Importorg.hibernate.Session;8 Importorg.hibernate.SessionFactory;9 Importorg.hibernate.Transaction;Ten Importorg.hibernate.cfg.Configuration; One  A ImportCom.qcf.po.User; -  -  Public classTesthiber { the      Public Static voidMain (string[] args) { -         //reading information from a configuration file -Configuration con=NewConfiguration (). Configure (); -         //Get Sessionfactory Object +Sessionfactory factory=con.buildsessionfactory (); -         //Get Session Object +Session session=factory.opensession (); AString hql= "from User"; at         //Create a HQL query -Query query=session.createquery (HQL); -List list=query.list (); -         //iterate over the results of a query -          for(inti = 0; I < list.size (); i++) { -User user=(User) list.get (i); in System.out.println (User.getname ()); -         } to          + session.close (); -     } the}

View Code

Query Result:

Dividing HQL queries by return type

1. Single Object

Here is a reminder of the number of records in this HQL consciousness statistics database, which we all know count (1) is much faster than count (*), but you must use COUNT (1) to get an error when using COUNT (*)!

1  Packagecom.qcf.test;2 3 Importjava.util.List;4 5 6 ImportOrg.hibernate.Query;7 Importorg.hibernate.Session;8 Importorg.hibernate.SessionFactory;9 Importorg.hibernate.Transaction;Ten Importorg.hibernate.cfg.Configuration; One  A ImportCom.qcf.po.User; -  -  Public classTesthiber { the      Public Static voidMain (string[] args) { -         //reading information from a configuration file -Configuration con=NewConfiguration (). Configure (); -         //Get Sessionfactory Object +Sessionfactory factory=con.buildsessionfactory (); -         //Get Session Object +Session session=factory.opensession (); AString hql= "SELECT COUNT (*) from User"; at         //Create a HQL query -Query query=session.createquery (HQL); -Number n=(number) Query.uniqueresult (); - System.out.println (N.intvalue ()); -          - session.close (); in     } -}

View Code

2. List Collection

Refer to the contents of the first HQL query!

3, object[] array
Sometimes, we don't need to find out all the properties of an entity class, we just need to find some properties. In this case, you can put the returned content in object[] instead of in the entity object.

 1  String hql= "Select U.name,u.age from User u"  2   Create HQL query  3  query Q= Session.createquery (h QL);  4  list<object[]> list=q.list ();  5   int  i = 0; I < list.size (); I++ 6  object[] Os=list.get (i);  7  System.out.println ("User name is:" +os[0]+ "Age is:" +os[1]); 8 } 

View Code

Show Results:

Hibernate:select User0_.username as col_0_0_, user0_.userage as col_1_0_ from user user0_
User name is: haha age is: 18
The username is: three Ages is: 18
User name is: Zhang San age is: 18
User name is: John Doe age is: 18
User name is: Harry Age is: 18
User name is: Zhang Age is: 18

4. Map Collection

We can also put the results of the query into the map

1String hql= "Select New Map (U.name as name,u.age as age) from User u";2         //Create a HQL query3Query q=session.createquery (HQL);4List<map> list=q.list ();5          for(inti = 0; I < list.size (); i++) {6Map map=List.get (i);7System.out.println ("username" +map.get ("name") + "Age" +map.get ("Ages"));8         }9Session.close ();

View Code

Show Results:

Hibernate:select User0_.username as col_0_0_, user0_.userage as col_1_0_ from user user0_
User name haha age 18
User name three age 18
User name Zhang San age 18
User name John Doe age 18
User name Harry age 18
User name Zhang age 18

5. Entity objects

Object arrays and maps are handy for querying only part of a property. In fact, we can also construct methods to encapsulate the detected data directly into the entity object.

1 New Construction Method:2  PublicEMP (short empno, String ename) {3         Super();4          This. empno =empno;5          This. ename =ename;6 }7String hql = "Select New EMP (e.empno,e.ename) from emp E";8Query q =session.createquery (HQL);9             Tenlist<emp> list =q.list (); One  for(intI=0;i<list.size (); i++){ AEmp e =List.get (i); -SYSTEM.OUT.PRINTLN ("Employee Number:" +e.getempno () + "-Employee name" +e.getename ());  -}

View Code

6. Where clause and parameter passing

1 //String hql = "from Emp where ename=?";2String hql = "from Emp where Ename=:ename";//bind dynamically with parameter names! (Recommended use!) )3Query q =session.createquery (HQL);4 //q.setstring (0, "SMITH"); //The parameter index counts from 0, not the same as JDBC, starting with 1. 5Q.setstring ("ename", "SMITH");6List List =q.list ();7              for(intI=0;i<list.size (); i++){8EMP C =(EMP) list.get (i);9 System.out.println (C.getename ());Ten}

View Code

7, HQL paging query

Paging is an essential feature of the project, and different databases have different paging methods, and hibernate masks the differences between the databases. We can do the paging function with the following simple code (if the principles and practices of paging are forgotten, refer to the project content previously taught).

1String hql = "from Emp";2Query q =session.createquery (HQL);3Q.setfirstresult (0);//fetch data from the first few4Q.setmaxresults (10);//set the maximum number of records per page5             6List List =q.list ();7              for(intI=0;i<list.size (); i++){8EMP C =(EMP) list.get (i);9 System.out.println (C.getename ());Ten}

View Code

8. Cross-table queries and object navigation

　　

Complex table join queries in SQL, cross-table operations. In the HQL, we just have to simply use the properties, similar to the El expression we learned earlier. In this way, we can write more complex queries with simple code.

1             String hql = "from Emp e where e.dept.deptno=?" ; 2             Query q = session.createquery (HQL); 3             Q.setinteger (0, 10);

View Code

9. Join (Inner Connection, outer connection)

In SQL we have inner connection, right outer connection, left outer connection, full outer connection, we also have these concepts in HQL. However, there are several inconsistencies:

1. If there is no relationship between the two entity classes, you cannot use the join
2. Because only two entity classes have an association relationship to use join, you do not need to specify the join condition as SQL.

The code examples are as follows:

1 String hql = "Select E.ename,d.dname from Emp e left join e.dept d"; 2 3 Query q = session.createquery (HQL);

View Code

10. SQL Native query (Native SQL)

　　

Sometimes hql may not be able to meet our requirements. We need to use the original SQL to do our function. We can use SQL queries in hibernate in the following ways:

1String sql = "Select Ename,sal from emp where Empno=:id";2SQLQuery q =session.createsqlquery (SQL);3Q.setinteger ("id", 7369);4List List = Q.list ();//The returned result is list<object[]>5              for(intI=0;i<list.size (); i++){6Object[] C =(object[]) list.get (i);7System.out.println (c[0]+ "-" +c[1]); 8             }9String sql = "SELECT * from emp where empno=:id";TenSQLQuery q =session.createsqlquery (SQL); OneQ.setinteger ("id", 7369); AQ.addentity (EMP.class); -list<emp> list =q.list (); -              for(intI=0;i<list.size (); i++){ theEMP C =List.get (i); -System.out.println (C.getename () + "-" +c.getsal ());  -}

View Code

Hibernate's powerful HQL query