Quick Start to Hibernate query

From: http://www.router.net.cn/Article/26665.html

 

If you do not know the persistent identifier of the object to be searched, you need to use hibernate to query. Here, I will share my experience with you and hope it will be useful to you.

Hibernate supports powerful and easy-to-use object-oriented Query Language (hql ). If you want to create a query by programming, Hibernate provides a comprehensive query by criteria (QBC) and Hibernate query by example (QBE) function. You can also use native SQL to describe hibernate queries. hibernate provides additional support for converting result sets into objects.

Execute Query

Hql and native SQL queries are expressed by org. hibernate. query instances. This interface provides methods for parameter binding, result set processing, and running actual queries. You can always obtain a query object through the current session:

  
 

   
  
 
  
ListCats=Session. Createquery (
 
   
  

  
"From cat as cat where Cat. Birthdate<? ")
 
   
  
 
  
. Setdate (0, date)
 
   
  
 
  
. List ();
 
   
  
 
  
 
 
   
  
 
  
ListMothers=Session. Createquery (
 
   
  
 
  
"Select mother from cat as CAT join cat. Mother as mother whereCat. Name=? ")
 
   
  

  
. Setstring (0, name)
 
   
  
 
  
. List ();
 
   
  
 
  
 
 
   
  
 
  
ListKittens=Session. Createquery (
 
   
  
 
  
"From cat as cat whereCat. Mother=? ")
 
   
  
 
  
. Setentity (0, PK)
 
   
  
 
  
. List ();
 
   
  
 
  
 
 
   
  

  
CatMother= (CAT) Session. createquery (
 
   
  
 
  
"Select cat. Mother from cat as cat whereCat=? ")
 
   
  
 
  
. Setentity (0, Izi)
 
   
  
 
  
. Uniqueresult ();]
 
   
  
 
  
 
 
   
  
 
  
QueryMotherswithkittens= (CAT) Session. createquery (
 
   
  
 
  
"Select mother from cat as mother left join fetch Mother. kittens ");
 
   
  

  
SetUniquemothers=NewHashset (motherswithkittens. List ());
 
  
 

A query is usually executed when list () is called, and the execution result is fully loaded into a collection in the memory ). The returned object is in the persistent state. If you know that the query returns only one object, you can use the list () shortcut uniqueresult (). Note: The root objects (their collection classes are initialized) are often returned when a set is pre-crawled ). You can use a set to filter repeated objects.

1. iterating results)

In some cases, you can useIterate ()Method To get better performance. This is usually because the expected results already exist in the session or second-level cache. Otherwise,Iterate ()ComparableList ()Slow, and simple queries may also require multiple database accesses:Iterate ()Will first use1Statement to obtain the identifiers of all objects, and then executeNThe attached SELECT statement instantiate the actual object.

  
 

   
  
 
  
// Fetch IDS
 
   
  

  
IteratorITER=Sess. Createquery ("from eg. qux Q order by Q. likeliness"). iterate ();
 
   
  
 
  
While (ITER. hasnext ()){
 
   
  
 
  
QuxQux= (Qux) ITER. Next (); // fetch the object
 
   
  
 
  
// Something we couldnt express in the query
 
   
  
 
  
If (qux. calculatecomplicatedalgorithm ()){
 
   
  

  
// Delete the current instance
 
   
  
 
  
ITER. Remove ();
 
   
  
 
  
// Dont need to process the rest
 
   
  
 
  
Break;
 
   
  
 
  
}
 
   
  
 
  
}
 
  
 

2. Query of tuples

Tuples indicates that a result row contains multiple objects. hibernate queries sometimes return tuples, and each tuples returns an array:

  
 

   
  

  
IteratorKittensandmothers=Sess. Createquery (
 
   
  
 
  
"Select kitten, mother from cat kitten join kitten. Mother ")
 
   
  
 
  
. List ()
 
   
  
 
  
. Iterator ();
 
   
  
 
  
 
 
   
  
 
  
While (kittensandmothers. hasnext ()){
 
   
  

  
Object []Tuple= (Object []) kittensandmothers. Next ();
 
   
  
 
  
CatKitten=Tuple[0];
 
   
  
 
  
CatMother=Tuple[1];
 
   
  
 
  
....
 
   
  
 
  
}
 
  
 

3. scalar results

You can specify the attributes of a class in the select clause, or even call the SQL statistics function. Properties or statistical results are identified as "scalar" results (rather than persistence (persistent state) entities ).

 

  
 

   
  

  
IteratorResults=Sess. Createquery (
 
   
  
 
  
"Select cat. Color, min (Cat. Birthdate), count (CAT) from Cat" +
 
   
  
 
  
"Group by CAT. Color ")
 
   
  
 
  
. List ()
 
   
  
 
  
. Iterator ();
 
   
  
 
  
 
   
  
 
  
While (results. hasnext ()){
 
   
  

  
Object []Row= (Object []) results. Next ();
 
   
  
 
  
ColorType= (Color) Row [0];
 
   
  
 
  
DateOldest= (Date) Row [1];
 
   
  
 
  
IntegerCount= (Integer) Row [2];
 
   
  
 
  
.....
 
   
  
 
  
}
 
  
 

4. Bind Parameters

The interface query provides question marks (?) for naming parameters and JDBC styles (?) Parameter binding method. Unlike JDBC, Hibernate starts counting parameters from 0. Named parameters indicates the name identifier in the query string. Named parameters has the following advantages:

Named parameters is irrelevant to the order in which the query string appears.

They can appear multiple times in the same query string.

They are self-explanatory.

  
 

   
  
 
  
// Named parameter (preferred)
 
   
  
 
  
QueryQ=Sess. Createquery ("from domesticcat cat whereCat. Name=: Name ");
 
   
  
 
  
Q. setstring ("name", "fritz ");
 
   
  
 
  
IteratorCats=Q. Iterate ();
 
   
  

  
// Positional Parameter
 
   
  
 
  
QueryQ=Sess. Createquery ("from domesticcat cat whereCat. Name=? ");
 
   
  
 
  
Q. setstring (0, "Izi ");
 
   
  
 
  
IteratorCats=Q. Iterate ();
 
   
  
 
  
// Named parameter list
 
   
  
 
  
ListNames=NewArraylist ();
 
   
  

  
Names. Add ("Izi ");
 
   
  
 
  
Names. Add ("fritz ");
 
   
  
 
  
QueryQ=Sess. Createquery ("from domesticcat cat where Cat. Name in (: nameslist )");
 
   
  
 
  
Q. setparameterlist ("nameslist", names );
 
   
  
 
  
ListCats=Q. List ();
 
  
 

5. Paging

If you need to specify the range of result sets (the maximum number of rows to be returned/or the Starting number of rows), you should use the method provided by the query interface:

   
  


    
   
QueryQ=Sess. Createquery ("from domesticcat ");

    
   
Q. setfirstresult (20 );

    
   
Q. setmaxresults (10 );

    
   
ListCats=Q. List ();

   
  
 

Hibernate knows how to convert a qualified Query into Native SQL of your database ).

6. scrollable Iteration)

If your JDBC driver supports rolling resuleset, the query interface can use scrollableresults, allowing you to flexibly walk through the query results.

  
 

   
  

  
QueryQ=Sess. Createquery ("select cat. Name, cat from domesticcat cat" +
 
   
  
 
  
"Order by CAT. Name ");
 
   
  
 
  
ScrollableresultsCats=Q. Scroll ();
 
   
  
 
  
If (cats. First ()){
 
   
  
 
  
 
 
   
  

  
// Find the first name on each page of an alphabetical list of cats by name
 
   
  
 
  
Firstnamesofpages=NewArraylist ();
 
   
  
 
  
Do {
 
   
  
 
  
StringName=Cats. Getstring (0 );
 
   
  
 
  
Firstnamesofpages. Add (name );
 
   
  
 
  
}
 
   
  

  
While (cats. Scroll (page_size ));
 
   
  
 
  
 
 
   
  
 
  
// Now get the first page of cats
 
   
  
 
  
Pageofcats=NewArraylist ();
 
   
  
 
  
Cats. beforefirst ();
 
   
  
 
  
IntI=0;
 
   
  

  
While (page_size>I ++) & cats. Next () pageofcats. Add (cats. Get (1 ));
 
   
  
 
  
 
 
   
  
 
  
}
 
   
  
 
  
Cats. Close ()
 
  
 

Note: To use this function, you must keep the database connection (and cursor) in the open state. If you need to disconnect and use the paging function, use setmaxresult ()/setfirstresult ()

7. External name query (externalizing named queries)

You can define a name query (named queries) in the ing file ). (If your query string contains characters that may be interpreted as XML markup, don't forget to wrap it in CDATA .)

Parameter binding and execution are completed programmatically:

   
  


    
   
QueryQ=Sess. Getnamedquery ("bynameandmaximumweight ");

    
   
Q. setstring (0, name );

    
   
Q. setint (1, minweight );

    
   
ListCats=Q. List ();

   
  
 

Pay attention to the actualProgramCodeIt has nothing to do with the query language used. You can also define native SQL queries in the metadata, or store other original query statements in the configuration file, in this way, Hibernate can be centrally managed for migration purposes.

Note that The query declared in the element must have a globally unique name. The query declared in the element automatically has a global name, which is limited by the full name of the class. For example, eg. Cat. bynameandmaximumweight.