The HQL statement of 11-hibernate.

1. Hibernate Query Introduction

Hibernate hql Query, criteria query, call stored procedure

Hibernate transaction management and concurrency control

Hibernate delay loading strategy, crawl strategy, level two cache management

Hibernate consolidation Struts

2. HQL Introduction

hql:hibernate Query Language , which is an object-oriented query Language provided by Hibernate.

(1) Set various query conditions in the query statement

(2) Supports dynamic binding parameters (similar to preprocessing in SQL statements). Assignment)

(3) Support projection query (longitudinal query, followed by examples of specific instructions), paging query, connection query, grouped query, subquery

(4) Some aggregate functions are built in

The query interface in Hibernate is an interface specifically designed to execute HQL statements.

main steps for HQL queries:

"1". Create a Query object

Query query=session.createquery ("from Dept");

"2". Execute Query List results

1) List all the results

List<dept> depts=query.list ();

for (Dept dept:depts) {...}

2 List individual Results

Query query=session.createquery ("from Dept where id=1");

Query.setmaxresults (1);

Dept dept= (Dept) Query.uniqueresult ();

3 iterative access results; less use, trouble

Iterator<dept> it=query.iterate ();

while (It.hasnext ()) {

}

3. HQL basic Syntax: Persistent class names are case-sensitive

1) Select the persistence class to query.

String hql= "from Dept"

Query query=session.createquery (HQL);

String hql= "from Dept as D";

As is the meaning of its alias, can be omitted, the following examples will specify

2. Create a class for the selected property, providing a constructor that takes these two properties as parameters, and uses this class to encapsulate the query results.

3 WHERE Condition clause

string hqlstring= "from Dept D where d.createdtime>= ' 2010-01-01 '";

Query Query=session.createquery (hqlstring);

string hqlstring= "from Dept D where d.name like ' java% ' and d.employees are not empty and d.createdtime<current _date () ";

4). Binding Query Parameters

• Binding by parameter name, where the method chain is used

String hql= "from Dept where Id>:id and name Like:likename"

List<dept> dept=session.createquery (HQL). Setlong ("id", new Long (Ingputid)). SetString ("Likename", "%" + inputname+ "%"). List ();

5) Distinct filter duplicate value

Select distinct createtime from Dept

6) Aggregate function (count)

Select COUNT (ID) from Dept d

7 Order by sort (ASC ascending, desc descending)

From Dept d d.createdtime asc,d.name desc

8 GROUP by pair Records grouping

Select count (e) from the Employee e Group by e.dept;

9 Having the conditional filtering of the data after grouping

Select E.dept.name from Eployee e GROUP by E.dept has count (e) >1

4. Other knowledge points

Page-Search

The query interface provides two ways to display query results in batches

Setfirstresult (int firstresult): Data is fetched from the first few records in the recordset. The default is 0.

Session.begintransaction ();

String hql = "from Employee";

list<employee> Empls = Session.createquery (HQL). Setmaxresults (PageSize). Setfirstresult ((pageNo-1) * pageSize) . List ();

Session.gettransaction (). commit ();

Setmaxresults (int maxresults): Sets the maximum number of objects to return per query.

Batch Modification and deletion: The database is manipulated directly through SQL statements, and the data in the current session cache is not synchronized with the data in the database .

String hql= "Delete Dept as D where D.name like:likename";

Query query=session.createquery (HQL). SetString ("Likename", "% III");

int count= query.executeupdate ();

Connection Query

INNER JOIN (inner join) left OUTER join (outer join)

right outer join (right outer join) full join (fully connected)

String hql = "from Dept d JOIN d.employees E"

+ "WHERE d.name like:likename";

fetching connection query fetch and lazy load relative

Crawl: Refers to the loading of an object data from the database, while it is associated with the object and the collection of data together to reduce the data of SQL statements, so as to improve query efficiency.

From Dept D-left join Fetch d.employees e where d.name like "%web%"

5. The above knowledge point case analysis

Package com.hbsi.one2many;

Import Java.util.HashSet;

Import java.util.List;

 

Import Java.util.Set;

Import Org.hibernate.Query;

Import org.hibernate.Session;

 

Import Org.junit.Test;

 

Import Com.hbsi.utils.HibernateSessionFactory; public class Apphql {@Test public void Find () {Session session = Hibernatesessionfactory.gets

      

      Ession ();

      /*//a simple query that returns the result to list query query = Session.createquery ("from Department");

      list<department> list = Query.list ();

      for (Department dep:list) {System.out.println (Dep.getdepid () + "---" +dep.getdepname ());

      *//*//Simple query, return the result to the class object String hql = "from Department where id>1";

      Setmaxresults (1) is to set the maximum query result to 1, otherwise you use the Uniqueresult () method will be wrong, the change method returned the result is a unique value.

      Query query = session.createquery (HQL). Setmaxresults (1);

        Department dep = (Department) query.uniqueresult (); System.out.println (Dep.getdepid () + "---" +dep.gEtdepname ()); * */*//projection query (that is, vertical query, is to find a record of a field), select some properties, list elements are: object[]; Because of the more cumbersome use, it is not recommended in actual development to use the mapping query String hql = "Sele

      CT depid,depname from Department ";

      Query query = session.createquery (HQL);

      List List = Query.list ();

        for (int i=0;i<list.size (); i++) {object[] obj = (object[)) list.get (i);

        System.out.println (obj[0]+ "---" +obj[1]);

      System.out.println ("-----------------"); }*//*//encapsulates the results of a subset of queries through a class, simplifying operations, but performing less efficiently String hql = "Select New Com.hbsi.one2many.DepartRow" (d.depid,d

      . depname) from Department D ";

      Query query = session.createquery (HQL);

      list<departrow> list = Query.list ();

      for (Departrow dr:list) {System.out.println (Dr.getid () + "-----" +dr.getname ());

      }*//*//Query for a property does not need to encapsulate the extra attribute class, directly returns a list String hql = "Select Depname from Department" of the property type;

      Query query = session.createquery (HQL); List<string> List = query.list ();

      for (String s:list) {System.out.println (s);

      }*//*//Query contains an employee's department is empty application String hql = "from Department D where d.emp are not empty";

      Query query = session.createquery (HQL);

      list<department> list = Query.list ();

        for (Department d:list) {System.out.println (D.getdepname ());

        For (Employee e:d.getemp ()) {System.out.println ("|----" +e.getempname ()); }*///Binding query parameters:. The subscript begins with 0/*string hql = "from Department D where d.depid>"

      and depname=? ";

      Query query = session.createquery (HQL);

      Query.setinteger (0,1);

      Query.setstring (1, "software Department");

      list<department> dep = Query.list ();

      for (Department d:dep) {System.out.println (D.getdepid () + "----" +d.getdepname ()); }*///By name (: First name) This method is to give the conditional name method to avoid not knowing which question mark is assigned what value/*string hql = "from Department D where d.depid> : IDs and DePname=:name ";

      Query query = session.createquery (HQL). Setinteger ("IDs", 1). SetString ("name", "software Department");

      list<department> dep = Query.list ();

      for (Department d:dep) {System.out.println (D.getdepid () + "----" +d.getdepname ()); * *//Statistics Department, generally through the number of statistical IDs to achieve the function, do not recommend the use of Count (*); The following two methods have the same background SQL statements/*string hql = "SELECT Count" (DEP

      Id) from Department ";

      Query query = session.createquery (HQL);

      Method one: By obtaining the total record number statistics List List = Query.list ();

      System.out.println (list.get (0));

      Method Two: The Uniqueresult method gets the lang type, not the integer type, and the strong to integer type will error long DEP = (long) query.uniqueresult (); System.out.println (DEP);///statistics The number of staff in each department, number of people; Here is the number, not the number of departments/*string hql = "Select E.dep.depna

      Me,count (E.empid) from the Employee e Group by E.DEP having Count (e.empid) >=1 ";

      Query query = session.createquery (HQL);

      List List = Query.list ();

        for (int i=0;i<list.size (); i++) {object[] obj = (object[]) list.get (i);

        System.out.print (obj[0]+ "--");

      System.out.println (obj[1]);

      }*///Delete the user with the HQL statement, where the HQL deletes the data in the database table, but the session cache retains the data; this is called the executeupdate UPDATE statement, so the transaction will be rolled back and the data in the database is not deleted.

      

      /*session.begintransaction ();

      Employee E = (employee) session.get (employee.class,6);

      System.out.println (E.getempname ());

      Query query = session.createquery ("Delete Employee where empid=6");

      Query.executeupdate ();

      System.out.println (E.getempname ());

      Session.gettransaction (). commit (); * *//inner join inner joins; here's a like fuzzy query/*string hql = "from Department D join D.emp where D.D

      Epname Like:likename ";

      Query query = session.createquery (HQL). SetString ("Likename", "% software%");

      List List = Query.list ();

        for (int i=0;i<list.size (); i++) {object[] obj = (object[)) list.get (i);

        Department dep = (Department) obj[0]; EmployeE emp = (Employee) obj[1];

        System.out.println (Dep.getdepname ());

      System.out.println ("|---" +emp.getempname ());

      } * * *//left join left/*string hql = "from Employee e join E.DEP";

      Query query = session.createquery (HQL);

      List List = Query.list ();

        for (int i=0;i<list.size (); i++) {object[] obj = (object[)) list.get (i);

        Department dep = (Department) obj[1];

        Employee EMP = (employee) obj[0];

        System.out.println (Dep.getdepname ());

      System.out.println ("|---" +emp.getempname ());

        }*///fetch Crawl;

      String hql = "from Department D join Fetch d.emp";

      Query query = session.createquery (HQL);

      Here the list is repeated to crawl, and set can be used, but the set does not guarantee the order of the table list<department> list = Query.list ();

      set<department> set = new hashset<department> (list);

       for (Department d:set) {System.out.println (D.getdepname ()); For (Employee e:d.getemp ()) {System.out.println ("|---" +e.getempname ()); }//Simple connection, multiple table connection, the internal connection, the left and right connection, grab the empty department in the table (no corresponding employees) are not displayed, in order to test this reason/*query Query = Session

      . CreateQuery ("from Department");

      list<department> list = Query.list ();

      for (Department d:list) {System.out.println (D.getdepname ());

      }*///Test paging/*list<employee> List = Findemployee (2,2);

      for (Employee e:list) {System.out.println (E.getempname ()); Hibernatesessionfactory.closesession () ()////pages public list<employee> Findemploye

      E (int nowpage,int pageSize) {Session session = Hibernatesessionfactory.getsession ();

   

      Session.begintransaction ();

      Query query = Session.createquery ("from Employee");

      Query.setfirstresult ((nowPage-1) *pagesize);

      Query.setmaxresults (pageSize); list<employee> list = QuEry.list ();

      Session.gettransaction (). commit ();

      Hibernatesessionfactory.closesession ();

   return list;
 }

}