Hibernate (VII.) How to query in Hibernate

Preface

Before the query in hibernate is always confusing, do not understand what is in the specific things. is because of the lack of summary. Before reading this article, you should know that the database of some query operations, multi-table query, etc., if you do not understand, you can first look at the MySQL data table query operation in detail, so that the article is not so difficult to read.

--wh

I. 5 ways to search in hibernate

1.1. Navigation object Map Retrieval method

Navigating to other objects based on objects that have already been loaded

For example: In the preceding various mapping relationships, the entity class contains references to other class objects.

Dept d = (Dept) session.get (dept.class,2);

D.getstaffset (). Size (); The D object associates a staff collection, and hibernate automatically retrieves the staff data. How to retrieve, look at the SQL statement sent in the following figure.

　　　　　　　　　　　　　　　　　　　　　　　

1.2. OID Retrieval method

Retrieving objects according to the OID of the object

Example: Session.get ()/session.load ()

This is very familiar to everyone, you do not have to elaborate here too much.

1.3. HQL Search method

Hql:hibernate Query Language, is an object-oriented query language, it is similar to the SQL query language, in the various retrieval methods provided by Hibernate, HQL is the most widely used as a retrieval method,

Note: The HQL operation is all the properties in the Pojo class, not the fields in the database table.

1.3.1, setting various query conditions in query statements

1.3.2, supports projection queries, that is, only partial properties of objects are retrieved

1.3.3, support paging query

1.3.4, Support connection query

1.3.5, support for grouping queries, allowing the having and group by keywords to be used

1.3.6, providing built-in aggregation functions such as SUM (), MIN (), MAX (), etc.

1.3.7, ability to invoke user-defined SQL functions or standard SQL functions

1.3.8, support sub-query

1.3.9, support dynamic binding parameters

To retrieve steps using HQL:

1> Get session

2> Writing HQL

3> creating a Query object from Session.createquery (HQL)

4> set the condition parameters for the query object (if a parameter is required in HQL)

5> executing a query

List (): Returns a list of collections that may be loaded with arrays, possibly Pojo objects.

Uniqueresult (): Returns a query result, when there is only one or 0 of the known query results, the use is not a problem, if there are multiple return results, then the exception is reported

Experiment

Create an environment that uses two-way, one-to-many relationships with dept and staff, where the specific code is not listed. The point is not here. There are 9 records in the original record staff, all pointing to the department with ID 2 in Dept.

1.3.1, setting various query conditions in query statements

1.3.1.1, query all records, no query criteria

View Code

Results

　　　　　　　　　　　　　　

　　　　　　　　　　　　　　　　

1.3.1.2, conditional query, find out the staff information of id=3

View Code

1.3.2, supports projection queries, that is, only partial properties of objects are retrieved

That is, you do not need to query all the fields in the table, only some of the object's properties are queried, this is the projection query

1.3.2.1, what degrees do not use, direct query, the result is to put the found property into the list collection. This is used only to retrieve one property of an object, and if more than one property needs to be encapsulated in a different way

View Code

1.3.2.2, use the new List () or new Map () or new staff () to encapsulate the returned value.

Using the new staff ()

View Code

Use the new List ()

View Code

Use new Map ()

View Code

1.3.3, support paging query

Use Setfirst () and Setmaxresult () to set the starting index and the total number of data taken separately. It's the same as limit m,n.

View Code

　　

1.3.4, Support connection query

Supports 7 types of connection notation.

1.3.4.1, INNER join inner join can omit inner, direct join

View Code

　　　　　　　　　　　　　　　　　　　　　　　　

1.3.4.2, urgent internal connection inner JOIN fetch

The list in which the inner join is returned is object[], and the list in which the urgent inner join is returned is the Pojo class object

View Code

For a record that is not duplicated, change the hql to "select DISTINCT D from Dept D INNER join fetch d.staffset";

1.3.4.3, implicit internal connection do not write any keywords, complete the table connection

It's actually connecting two tables through where. Very simple.

View Code

1.3.4.4, left outer join, leave outer join, can omit outer, direct leave join

View Code

1.3.4.5, urgent left outer connection, outer join fetch

The same difference is that the list contains the Pojo object.

View Code

1.3.4.6, right outer connection: outer JOIN

Know the left outer connection, the right outer connection will also be.

View Code

1.3.4.7, cross-linking, produces Cartesian product.

What is Cartesian product? Connect two tables, such as a table has 3 records, another table also has 3 records, then after the connection, there will be 9 data, in which there are some duplicate data, take the instance to speak, class and students, there are three students a,b,c, there are two classes e,f, link up, will appear Descartes product, 6 data, which will appear such data, A,e, A,f, B,e, B,f, C,e, c,f, A,b,c repeated appearance, that is, in E class, and in class F, so it is unreasonable. This is what is called the Cartesian product.

Due to a record in dept, the Cartesian product cannot be displayed, so a dept record is added manually. Then cross-connect. There will be a Cartesian product,

View Code

Note: Many of the above connections use a reference to represent the corresponding table, such as "from Dept D inner join D.staffset" D.staffset is like a representative of the staff of this table, in fact, represents the staff of this table, so understanding, from the Dept from the Dept table to find all records, for example, found the Dept table in the 2-door, in the 2-door how many staff it, Can all find out, in the context of what we say, just the staff is all in the department, then found the staff table in all staff, that is, the equivalent of the staff of the table, even if 2 of the door does not include all the staff, then there are other departments, Must include the rest of the staff, that is to say, regardless of the degree can be found all staff, so d.staffset is equivalent to the staff table. And so on, so it is understood in other HQL statements.

1.3.5, support for grouping queries, allowing the having and group by keywords to be used

View Code

1.3.6, providing built-in aggregation functions such as SUM (), MIN (), MAX (), etc.

This is not a good example of using these functions ... So here's not a demo, it's simple. HQL and SQL are not much worse.

1.3.7, ability to invoke user-defined SQL functions or standard SQL functions

This is the same as above, there are many functions in SQL. Which means that HQL can use functions in SQL

1.3.8, support sub-query

View Code

1.3.9, conditional query, two ways to set parameters dynamically in HQL

Method One: From the staff where id =? Instead of the value you want to fill in, set the value below and start at 0, the first one? is in the 0 position, if there are two? Number, use the 0,1 index number to insert the value.

View Code

Mode two: From the staff where id =: ID uses the name ": id" to indicate the name of the inserted value, and below does not use the index number to determine the location of the inserted value, directly using this nickname

View Code

Note that not only can you insert a value in the position, but you can also insert an object. For example

View Code

These two ways can be, but in the case of more parameters, it is recommended to use aliases, which is more clear, not error-prone, in a small number of parameters can use the index.

　　

1.4. QBC Search method

Qbc:query by Criteria is a more object-oriented query language that provides a series of QBC APIs to retrieve objects.

HQL can do things, use QBC also mostly can use, this through the example to see how QBC is used.

Steps:

1> Get session

2>session.createcriteria (Obejct.class); Create a Criteria Object

3> uses the criteria API method to increase the condition. Add (Restrictions.eq (property name, value))

4> executing a query

List (): Returns a list of collections that may be loaded with arrays, possibly Pojo objects.

Uniqueresult (): Returns a query result, when there is only one or 0 of the known query results, the use is not a problem, if there are multiple return results, then the exception is reported

Example one: Use QBC to query staff

View Code

Example two: Using QBC to make conditional queries on staff

View Code

Example three: QBC can also make connection query

View Code

Give a table to see the QBC added conditional query statement.

　　　　　　　　　　　　　　　　

The emphasis is on the use of an offline criteria object.

1. In the Web layer encapsulates the query condition into the offline criteria object, bind its Detachedcriteria object to the thread.

2, to the DAO layer, you can get the offline criteria by thread, and then create a session. Give the session to Detachedcriteria, and you'll be able to execute the query

Code:

Web tier

Detachedcriteria Detachedcriteria =detachedcriteria.forclass (customer.class);

Detachedcriteria.add (Restrictions.eq ("name", "Kitty"));

DAO layer

Session session = Hibernateutils.opensession ();

Transaction Transaction = Session.begintransaction ();

Associating an offline query object to a session

Criteria = Detachedcriteria.getexecutablecriteria (session);

Customer customer = (customer) criteria.uniqueresult ();

　　　　　　　　　　　　　　　　

1.5. Local SQL Retrieval method

Written using standard SQL statements.

Steps:

1> Get session

2> Writing SQL statements

3>session.createsqlquery (SQL); Get Sqlquey Object

4> sets the parameters for the SQL statement.

5> executing a query

List (): Returns a list of collections that are loaded with object[].

Returns a collection of entity class objects that, if bound to an entity class, uses Addentity (Xxx.class).

Example one: Querying all records of staff

View Code

Example two: Querying all records of the staff and binding entities. Addentity.

View Code

Second, summary

The above is what we say 5 kinds of search, which said the focus is HQL usage, the above examples are all written almost to hql have a certain understanding. Remember that HQL is an operation on the Pojo class, not a table in the database. When using a connection query, you can use QBC because it is simpler to use any connection with CreateAlias (). Typically, SQL statements are extracted from the development and placed in HBM, for example

　　　　　　

mapping files in HBM (can also be configured with annotations)

<!--Here you can define a named query--

<!--define HQL statement <query name= "" ></query>-

<!--defining SQL statements <sql-query name= "" ></sql-query>-

<query name= "Findcustomerbyname" >

<! [Cdata[from Customer where name =?]] >

</query>

* A name for the HQL statement

Program code:

is equivalent to separating the SQL statements. Easy Maintenance

Query query = session.getnamedquery ("Findcustomerbyname");

Query.setparameter (0, "Tom");

Customer customer = (customer) query.uniqueresult ();

Hibernate (VII.) How to query in Hibernate