Hibernate query method

How to query data using HQL: 1. How to query OID by using the get () and load () methods provided by session to load the specified OID object, you can only query by Object id. 2. The HQL Query method uses the HQL language to Query through the Query interface. 3. QBC query by using interfaces and classes such as Criteria 4.

How to query data using HQL: 1. How to query OID by using the get () and load () methods provided by session to load the specified OID object, you can only query by Object id. 2. The HQL Query method uses the HQL language to Query through the Query interface. 3. QBC query by using interfaces and classes such as Criteria 4.

Use HQL to query data

Hibernate provides the following query methods:

1. OID Query Method

You can use the get () and load () methods provided by the session to load the specified OID object. You can only query by Object id.

2. HQL Query Method

Use the HQL language to Query through the Query interface.

3. QBC Query Method

Query through interfaces and classes such as Criteria

4. Local SQL query

The native SQL language is used for queries. The query results are not result sets, but persistence objects.

5. Object navigation Query Method

Navigate to the associated object through the loaded object.

HQL is an Object-Oriented Query Language provided by Hibernate. HQL and SQL syntax formats are similar. HQL operation persistence classes, rather than database tables.

To query using HQL:

1. Get the session object.

Session session =HibernateUtil.getSessionFactory().openSession();

2. Compile HQL statements.

String hql ="from Student where sname='Tom'";

3. Create a Query object.

Query query = session.createQuery(hql);

4. Execute the query and obtain the result.

List
 
  list = query.list();
 

Note: The HQL operation is a persistence class. Therefore, Student refers to the persistence class Student in step 2, and sname refers to the persistence class property sname, rather than the database table field sname.

The Query interface is an HQL Query interface that provides various Query functions, equivalent to JDBC Statement and PreparedStatement. You can create an object through createQuery () of the session.

Object Query

Is to query the complete information of the persistence class, to query all its attributes

For example:

From Student or from com. po. Student generally does not need to use the full-qualified names of classes to be introduced automatically. From Student s or from Student as s, which is an alias for the class name, where as is optional. Where clause. From Student where sid = 11070130 or fromStudent s where s. sid = 11070130

Various operators can be used in the where clause.

For example:

From Student where sex = 'male' and sid <11070200 from Student where sid between 11070100 and 11070200 from Student where sname like '% fei' % match any length character from Student where sname like' _ ik % '_' matches a single character, note that Chinese characters cannot be matched with underlines.

You can use the following three methods to return Query results: list (), iterate (), and uniqueResult ();

The list () method is used to return a set of query persistence classes, while the iterate () method directly returns a falling device of the persistence class.

The difference between the two is:

1. Different query mechanisms

Use list () to query all the qualified records from the database, including all fields.

Using iterate (), first retrieve all qualified records from the database, including only the id field. Then, check whether the student information in the cache exists.

A) if the cache contains all the data, you do not need to query the database and directly reference it. Query once. At this time, the iterate () efficiency is significantly higher than list ().

B) if the cache does not contain any data, you need to query the database n times (n indicates the number of records meeting the query conditions), query each record in sequence, and query (n + 1) at this time, the efficiency of iterate () is much lower than that of list ().

In most cases, list () is used for query. When an object contains a large number of attributes or most of the data to be loaded already exists in the cache, you can use iterate ().

Property query (projection query)

Only query partial attributes of the persistence class, not all attributes

Attribute query method:

1. directly specify attributes for property query. For example, you can query the student ID and name of a student.

String hql = “select sid,sname from Student”;Query query = session.createQuery(hql);List list = query.list();Iterator it = list.iterator();while(it.hasNext()){    Object obj[]= (Object[])it.next;    System.out.println(obj[0]+”    ”+obj[1]);}

Note: In this query method, each element in the List set is an object array instead of a student type. The length of the array depends on the number of queried attributes. In the preceding example, the length of the object array is 2, the first element stores the student id, and the second element stores the Student name.

2. query attributes through the constructor. For example, you can query the student ID and name of a student.

First, a response constructor is provided for the persistence class.

String hql = “select new Student(sid,sname) from Student”;Query query = session.createQuery(hql);List list = query.list();Iterator it = list.iterator();while(it.hasNext()){    Student stu =(Student)it.next();    System.out.println(stu.getSid()+””+stu.getSname());}

Entity update and Deletion

For example, change the name of a student numbered 11070130 to Zhao Fei.

String hql = "update Student set sname = 'zhao fee' where sid = 11070130"; Query query = session. createQuery (hql); int n = query.exe cuteUpdate (); // n indicates the number of update records.

For example, deleting student information with student ID 11070130

String hql = “delete from Student where sid=11070130”;Query query = session.createQuery(hql);int n = query.executeUpdate();

Parameter binding

Disadvantages of String concatenation: low performance, poor security, and easy to splice errors.

Parameter binding 1: "?" Placeholder

String name = "Zhao Fei"; String hql = "fromStudent where sname = ?"; Query query = session. createQuery (hql); query. setString (0, name); // fill in the List with setXXX
 
  
List = query. list ();
 

Parameter binding 2: named Parameters

String name = "Zhao Fei"; String hql = "fromStudent where sname =: sname"; Query query = session. createQuery (hql); query. setString ("sname", name); List
 
  
List = query. list ();
 

HQL sorting Function:

HQL uses the order by clause to sort the query results. Sort in ascending order by default.

For example:

String hql = “from Student order bysname”;

You can also specify the sorting Policy (asc ascending and desc descending)

For example:

String hql = “from Student order bysname desc”;

The order by clause can specify multiple sorting conditions,

For example:

String hql = “from student order bysname,sid desc”;

Sort by Student name in ascending order. objects with the same student name are sorted by student ID in descending order.

Use of statistical functions

There are five statistical functions: avg (), sum (), min (), max (), count (), which are used to calculate the average, sum, minimum, and maximum respectively, counting function.

Different from statistical functions in SQL, statistical functions in SQL are used to operate fields in database tables, while statistical functions in HQL perform operations on attributes of persistence classes.

Example 1: count the number of students

String hql=”select count(*) from Student”;Long n =(Long)session.createQuery(hql).uniqueResult();

Example 2: query the average score, lowest score, and highest score of all students.

String hql = “select avg(score),min(score),max(score)from Student”;Object obj[] =(Object[])session.createQuery(hql).uniqueResult();

Group Query

Example: count the number of students in each class

select gid,count(*) from Student group by gid;

The having clause is used to add query conditions for grouping results.

Example: count the number of students in a class greater than 20

select gid,count(*) from Student  group by gid having count(*)>20

Paging Query

Use the methods provided by The Hibernate Query interface.

1. setFirstResult () sets the position of the first record.

2. setMaxResult () sets the maximum number of returned records.

Paging implementation:

1. Obtain the total number of records based on the results

    Query query =session.createQuery(“from Student”);    List
 
  list = query.list();    int count =list.size();
 

2. calculate the total number of pages

    inttotalpages = (count%pageSize==0)?(count/pageSize):(count/pageSize+1);

3. Implement Paging

    query.setFirstResult((pageIndex-1)*pageSize);    query.setMaxResults(pageSize);    List result =query.list();

Subquery

Example: query information about students whose scores are higher than those of Zhang Hua and Zhao Fei

From Student where score> all (select score fromStudent where sname = 'zhang hua' or sname = 'zhao fei ');

The meaning of all is that the score is higher than all the scores in the brackets. If you want to query a question that is higher than Zhang Hua or Zhao Fei's score, you should use the any keyword.

Connection Query

Use the join clause to perform joint queries between multiple persistent objects. Query for persistent objects.

Query category

For example, the database table used is the class table and student table. The table is as follows:

Internal Connection Syntax:

From Entity inner join Entity. property

Entity indicates a persistence class, and Entity. propery indicates the attributes associated with the second persistence class in the first persistence class.

For example, query all matching data in the class and student.

From Grade g inner join g. students is equivalent to the following two statements: from Grade g, Student s whereg = s. grade

From Grade g, Student s where g. gid = s. grade. gid;

Left Outer Join syntax

From Entity left outer join Entity. property

For example, displaying the matching information between the class and the student, and displaying the class without the student

From Grade g left join g. students;

Right Outer Join Syntax:

From Entity right outer join Entity. property

For example, displaying the matching information between the class and the student, and displaying the student information without the class

From Grade g right join g. students; or

From Student s left join s. grade;