Hibernate query method

Hibernate query method

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<Student>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 usually do not need to use the fully qualified name of the class to import automatically by default.
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 from student 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 '% fly'% matches characters of any length
From student where sname like '[IK%' "[u] matches a single character. Note that underscores cannot match Chinese characters.

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 Fei'Where sid = 11070130";
Query query = session.createQuery(hql);
Int n = query. Executeupdate(); / / N represents 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); / / what type of content is filled with setXXX
List<Student>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<Student> 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<Student>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;

How does hibernate use query?

1: hibernate Data Query Method: HQL, QBC, and Native SQL. HQL is suitable for static queries, while QBC is suitable for more dynamic queries.
A: HQL supports conditional query, connection query, paging query, grouped query, built-in function and custom function query (SUN (), MIN (), MAX ()), subquery: dynamically bound parameter query.
The HQL statement is defined as follows:
String hql = "from book ";
Query query = session. createQuery (hql );
B: QBC, that is, QBC retrieval. QBC creates a Criteria instance through the Session class and performs retrieval using different methods. In fact, Criteria is a container used to load query conditions. QBC has many conditional functions, such as Resstictions. eq (), Resstictions. gt (), Resstictions. ge (),
Resstictions. le (), Resstictions. and (), Resstictions. or.
The Criteria container is used as follows:
Criteria criteria = session. createCriteria (book. class );
Criteria. add (Restrications. It ("id", new Integer (4 )));
List list = criteria. list ();
C: Native SQL method. Both HQL and QBC must be parsed through Hibernate to convert them into SQL statements for database operations. Because we know that SQL can be used across multiple platforms.
The native SQL method is as follows:
String SQL = "select {B. *} from book B"
SQLQuery squery = session. createSQLQuery (SQL );
Squery. addEntity ("B", book. class );
List list = squery. list ();
2: hibernate association query
A: One-to-one association:
B: One-to-multiple, multiple-to-one Association
C: Multi-to-Multi-Association
Finally, to learn hibernate, we usually need to do more work and gradually accumulate experience, so that we will have a great sense of accomplishment. I wish you a better learning experience.

Hibernate query methods

HQL is queried using the query language provided by Hibernate. Hibernate Query lanagueEJBQL (JPQL 1.0) is the query language QBC (query by cretira) provided by EJB. QBE (Query by Example) is queried through the Cretira interface) use the Example programming interface to sort queries by function strength: NativeSQL> HQL> EJBQL (JPQL 1.0)> QBC (query by cretira)> QBE (query by Example) 1: QBE (Query By Example) QBC Query method QBC (Query By Criteria) is a "more object-oriented" search method provided By Hibernate. QBC is more flexible than HQL query in condition query, and supports dynamic and Natural Query statements during running. In Hibernate applications, QBC queries are usually followed by three steps (1) using the createCriteria () method of the Session instance to create the Criteria object (2) use related methods of Restrictions to set the query object for the Criteria object (3) use the list () method of the Criteria object to execute the query, the QBE query that returns the query result is used to retrieve the objects with the same property value as the specified sample object. Therefore, the key to QBE query is to create a sample object. All non-null attributes in the sample object are used as the query conditions. Although QBE does not have a large QBC function, it is more convenient to use QBE in some cases. The tool class Example specifies the sample object as the query condition for the Criteria object. Java code 1 Session session = HibernateSessionFactory. getSessionFactory (). openSe ssion (); 2 Transaction ts = session. beginTransaction (); 3 Customer c = new Customer (); 4 c. setCname ("Hibernate"); 5 Criteria criteria = session. createCriteria (Customer. class); 6 Criteria. add (Example. create (c); 7 Iterator it = criteria. list (). iterator (); 8 ts. commit (); 9 HibernateSessionFactory. closeSession ();