Hibernate advanced query skills-set filtering and subquery

1 , Set filtering: For a loaded Customer Object, assuming that Orders When the set uses the delayed loading mechanism Customer. getorders (). iterator () , Hibernate Will initialize Orders Set, and then load it to the database Customer Object Order Object, and fill Orders But in many cases, we only need to associate some objects that meet certain conditions, instead of loading all associated objects, which brings unnecessary performance overhead. At this time, we can use Hibernate To process the loading of associated objects. Let's look at the following code: List list = session. createfilter (customer. getorders (), "where this. Price> 100 order by this. Price"). List (); For (INT I = 0; I <list. Size (); I ++ ){ Order order = (Order) list. Get (I ); } In the above Code Session. createfilter () Method, a set of query objects is created. This method requires two parameters. The first parameter specifies the set to be filtered, the second parameter specifies the filter set conditions, and the method returns Query Object. This method does not require that the Set object to be operated has been initialized, but requires that the object containing this set object must be in the persistent state. When executed List () Methods, no matter whether the set of persistent objects has been initialized or not, the data will be retrieved in the database. Hibernate Cache does not appear Oid The same object. If the collection object has been initialized, List () Method does not create a new object, but only returns an existing object. If the collection object has not been initialized List () Method creates an object to be joined, but does not initialize a set that contains the object to be joined. In addition to adding filter conditions to the Collection object for auditing and filtering, you can also perform many other operations. refer to the following code: ① Pagination of collection objects: List list = session. createfilter (customer. getoders (), "order by this. Price ASC ") . Setfirstresult (10) . Setmaxresults (50) . List (); ②. An attribute of the associated object in the search set: List list = session. createfilter (customer. getorders (), "select this. ordernumber"). List (); 2 , Subquery: The subquery is SQL Statement, which can be SQL Statement using another SQL Statement query result, in Hibernate Medium Hql Query also supports subquery functions. As shown in the following code: List list = session. createquery ("from customer C where 1> (select count (o) from C. Orders O)"). List (); The number of orders queried by the above program exceeds 1 And face-to-face queries Hql Statement SQL Statement: Select * from customer C where 1> (select count (O. ID) from order o where c. ID = O. customer_id ); If the subquery returns multiple records, you can use the following keywords: ALL: All records returned by the subquery statement Any: Any result returned by the subquery statement. Some: And "Any" Equivalent In: And "= Any" Equivalent Exists: The subquery statement returns at least one record. For example, an order with a price greater 100 Customer From customer C where 100> Any (select O. Price from C. Orders O ); If you operate a set in a subquery, Hql Provides functions and attributes for manipulating a set: Size () Functions and Size Attribute: obtains the number of elements in a collection. Minindex () Functions and Minindex Attribute: obtain the minimum index value for the set with the index created (for details about the SET index, refer to the first part of the ing value type set) Minelement () Functions and Minelement Attribute: for a set of elements that contain basic types, obtain the element with the smallest median value in the set. Maxelement () Functions and Maxelement Attribute: For a set that contains elements of the basic type, obtain the element with the largest median value in the set. Element () Function: obtains all elements in a collection. For example, the query order number is greater 0 Customer From customer C where size (C. Orders)> 0; Or From customer C where c. Orders. size> 0; Above Hql The statement will generate SQL Statement: Select * from customer C where 0> (select count (O. ID) from order where O. customer_id = C. ID ); Note: In Hql The subquery must appear in Where Clause, and must be enclosed by a pair of parentheses. Why must I appear in Where After the words? In fact, you will find out after thinking about it. Hibernate Any object to be queried must have evidence to follow. This "data" is Hibernate In Hql from Object must be in Hibernate The main configuration file has clear configurations. Therefore Hibernate Not Supported SQL Statement that appears in From The dynamic view subquery after the sentence.