Knowledge about Hibernate query

First, we will introduce the differences between get () and load () methods:
The difference between the get () method and the load () method lies in the use of the second-level cache.
The load () method uses the second-level cache, while the get () method directly queries the database if it is not found in the first-level cache and does not search in the second-level cache.
Get (): If there is no record in the database, null is returned, and get () will return data anyway.
Load (): If there are no records in the database, an exception is thrown. If there is data, a proxy object is returned.

Differences between the list and iterator () Methods: (n + 1 ?)
When the list () method is executed, it directly runs the query statement required for the query results.
The iterator () method first executes the query to obtain the Object ID, and then retrieves the object to be queried based on each ID value.
Therefore, for list () queries, only one SQL statement is executed, and for iterator () for method query, you may need to execute n + 1 SQL statement (n is the number of records in the result set ).

The Processing Method of the result set is different:
The List () method will hold all result set objects at a time, and it will Initialize all result set Objects Based on the query results. If the result set is very large, it will occupy a lot of memory, and even cause memory overflow.
The iterator () method does not initialize all objects at a time during execution, but initializes Objects Based on access to the result set. One access can control the number of objects in the cache to avoid occupying too much cache, resulting in memory overflow.

Hql: hql is an Object-Oriented Query Language. Its operation objects include classes, instances, and attributes.
SQL: The operation object of SQL is data objects such as data tables and columns.
Hql is a fully Object-Oriented Query Language. Therefore, it supports inheritance and multiple features.
Hql queries depend on the Query Class. Each query instance corresponds to a query object.

The query interface is the real hql query interface.
// Create a query object
Query query = session. createquery ("from customer as c Where C. Name =: customername and C. Age =: customerage ");
// Dynamically bind Parameters
Query. setstring ("customername", "Tom ");
Query. setinteger ("customerage", 21 );
// Execute the query statement and return the result
List result = query. List ();

Hql query steps:
1: Obtain the hibernate session object.
2: Write hql statements.
3: using the hql statement as a parameter, call the createquery method of the session to create a query object.
4: If the hql statement contains parameters, call the setxxx () method of query to assign values to the parameters.
5: Call the list and other methods of the query object to traverse the query results.

Query also contains two methods:
Setfirstresult (INT firstresult): set the number of records returned from the returned result set.
Setmaxresults (INT maxresults): set the number of results returned by this query.

Object deletion and update.

Projection query: queries only a part of a property.
Returns a string for querying an attribute.
An array is returned when two fields are queried.

Dynamic query: it is mainly used for queries of dozens of tables;
To construct a new object, you must add a constructor;
Add the package name to the new object.

Do not use count (*) to count (Persistent Object)

Grouping and sorting:
The order by clause can be sorted by the ASC or DESC keyword.
For example, form user U order by U. Name ASC, U. Age DESC;

Group by clause and statistical query:
For example: String hql = "select count (u), u. Age from user U group by U. Age having count (u)> 10 ";
List list = session. createquery (hql). List ();

Standard SQL Aggregate functions can be used in hql statements, such as Count (), sum (), max (), min (), age (), etc.

Connection query:
Inner join
Left Outer Join: left Outer Join
Outer right join: Right Outer Join
Full join: Full join (not commonly used)
Urgent outer join: left Outer Join fetch, left join

Fetch: used to obtain connection data at a time, especially set data. Reduce the number of interactions with the database.

Left out join: all records and corresponding order records in the left table of left join will be displayed.
Right out join: In contrast to left out join, right out join returns all records in the table on the right of hql and the corresponding customer object record information.

You can use the following methods to obtain the set data:
1: hibernate. initialize (user. getorder ());
2: User. getorder (). Size ();
3: Urgent connection between left and right
4: Advanced Filter

Criteria queries: queries objects.
The main interfaces include criteria, criterion, expression_r, and restrictions. Supports dynamic generation of SQL statements at runtime.

Procedure:
1: Create a criteria object using the createcriteria () method of seesion
2: Set the query object. name indicates the property of the object.
3: add the query conditions to the criteria object.
4: Execute the list () query to return the result.
Conditional query is completed in three categories:
Criteria: indicates a query.
Criterion: represents a query condition.
Restrictions: A Tool class that generates query conditions.

Local SQL query: Use handwritten SQL to complete all create, update, and delete operations, including stored procedures.
Local SQL steps:
1: Create a sqlquery object through session.
2: Write SQL statements
3: using SQL statements as parameters, call the createsqlquery method of the session to create a query object.
4: If the SQL statement contains parameters, the setxx method of the query is called to assign values to the parameters.
5: Call the addentity or addscalar method of the sqlquery object to associate the selected result with the object or scalar value.
For example, hql SQL
String SQL = "select C. c_id as {C. ID}, C. c_name as {C. name} from customer C where c. ID = 1 ";
Note: If you use *, the table alias and class alias must be consistent.
String sql1 = "select {C. *} from customer C where c. c_id = 2 ";
For example, the SQL statement for conditional Query
Sqlquery query = session. createsqlquery (SQL );
Query. addentiy ("C", customer. Class );

Multi-state query:
Multi-state query is used to query instances of the current class and all sub-classes. Both hql and conditional queries support multi-state query.
For example, the hql statement queries all persistent objects.
Query query = session. createquery ("from Java. Lang. Object ");
System. Out. println (query. List ());

Hql supports multi-state query. multi-state query is used to query instances of the current class and all subclasses,
Session. createquery ("from employee"); or session. createcriteria (employee. Class );
If the employee class has two subclasses: hourlyemployee and salariedemployee, the query statement will find all the employee instances, as well as the instances of the hourlyemployee class and salariedemployee class.
Session. createquery ("from hourlyemployee ");
Session. createcriteria (hourlyemployee. Class); // retrieve child classes only

Sort query result sets:
Both hql and conditional query support sorting of query result sets, but hql uses the order by keyword, while conditional query uses the order class to sort query results.

Paging query:
Paging query is a common processing method in database applications. Both query and criteia interfaces provide paging query methods.
Setfistresult (INT): Specifies the object from which the query starts. The parameter indicates that the index position starts from 0.
Setmaxresults (INT): specifies the maximum number of objects to be queried at a time.

Subquery:
1: Query customers with more than one order.
For example:
From customer C where 1 <(select count (o) from C. Orders O)

Hql subqueries depend on the ability of the underlying database to support subqueries.
All subqueries can be replaced by connection query and group query statements.
For example:
Select C from customer C join C. Orders O group by C. ID having count (o)> 1

If the subquery returns multiple records, you can use the keyword to quantify them:
1: All 2: Any 3: Some 4: In 5: exists
For example:
// Return all customers whose order prices are less than 100
From customer C where 100> All (select O. Price Form C. Orders O)

// Return the customer with an order price less than 100
From customer C where 100> Any (select O. Price from C. Orders O)

// Return the customer with an order price equal to 100
From customer C where 100 = some (select O. Price from C. Order O)

// Return the customer with an order price equal to 100
From customer C where 100 = any (select O. prive from C. Orders O)

// Return the customer with an order price equal to 100
From customer C where 100 in (select O. Price from C. Orders O)

 

Parameter binding:
1: Traditional JDBC parameter binding
For example:
Preparestatement pre = connection. Prepare ("select * from user where user. Name = n? ");
Pre. setstring (1, "Zhao ");
Resultset rs = pre.exe cutequery ();

Hibernate parameter binding: four parameter binding methods are available in hibernate.
1: bind by parameter name (named parameter). Define the name parameter in the statement to start ":".
2: bind by parameter location
3: You can bind any type of parameters to hql queries using the setparameter () method.
4: setproperties () method, bind the named parameter with the property value of an object

For example, start ":".
Query query = session. createquery ("from user where user. Name = customername and user. Age =: customerage ");
Query. setstring ("customername", name );
Query. setinteger ("customerage", age );

For example, parameter location binding
Query query = session. createquery ("from user where user. Name =? And user. Age =? ");
Query. setstring (0, name );
Query. setinteger (1, age );

For example, setparameter has three parameters. Generally, two parameters are written.
String hql = "from user where user. Name =: customername ";
Query query = session. createquery (hql );
Query. setparameter ("customername", name, hibernate. String );

Example: setproperties
Customer customer = new customer ();
Customer. setname ("sdsd ");
Customer. setage (99 );
Query query = session. createquery ("from customer C where c. Name =: Name and C. Age =: Age ");
Query. setproperties (customer );

 

Define name query:
The named SQL statements are not placed in the program, but in the configuration file. configuring SQL statements in loose coupling mode can improve program decoupling.

In the customer. HBM. xml configuration file
<Query name = "findcustomer">
<! Cdaata [from customer C where c. Name =: Name]/>
</Query>
Code in the program:
Query query = session. getnamedquery ("findcustomer ");
Query. setstring ("name", "Tiger ");
List list = query. List ();

Named SQL statement:
<SQL-query name = "mysqlquery">
<! -- Associate the returned result with the object class -->
<Return alias = "S" class = "com. Lovo. Po. Customer">
<! -- Define the SQL statement named SQL query -->
Select {C .*}
From customer C where c. name like "Liu Dehua"
</SQL-query>
SQL _query is a child element of the hibernate-mapping element, so it can be accessed directly through the session