Posted on 2008-11-01 10:08
Neil's NoteBook 阅读(96)
评论(0) 编辑 收藏
If you don't know the identifiers of the objects you are looking for, you
need a query. Hibernate supports an easy-to-use but powerful object oriented
query language (HQL). For programmatic query creation, Hibernate supports a
sophisticated Criteria and Example query feature (QBC and QBE). You may also
express your query in the native SQL of your database, with optional support
from Hibernate for result set conversion into objects.
10.4.1. Executing
queries
HQL and native SQL queries are represented with an instance of org.hibernate.Query
. This interface offers methods for
parameter binding, result set handling, and for the execution of the actual
query. You always obtain a Query
using the current
Session
:
List cats = session.createQuery(
"from Cat as cat where cat.birthdate < ?")
.setDate(0, date)
.list();
List mothers = session.createQuery(
"select mother from Cat as cat join cat.mother as mother where cat.name = ?")
.setString(0, name)
.list();
List kittens = session.createQuery(
"from Cat as cat where cat.mother = ?")
.setEntity(0, pk)
.list();
Cat mother = (Cat) session.createQuery(
"select cat.mother from Cat as cat where cat = ?")
.setEntity(0, izi)
.uniqueResult();]]
Query mothersWithKittens = (Cat) session.createQuery(
"select mother from Cat as mother left join fetch mother.kittens");
Set uniqueMothers = new HashSet(mothersWithKittens.list());
A query is usually executed by invoking list()
,
the result of the query will be loaded completely into a collection in memory.
Entity instances retrieved by a query are in persistent state. The uniqueResult()
method offers a shortcut if you know your
query will only return a single object. Note that queries that make use of eager
fetching of collections usually return duplicates of the root objects (but with
their collections initialized). You can filter these duplicates simply through a
Set
.
10.4.1.1. Iterating results
Occasionally, you might be able to achieve better performance by executing
the query using the iterate()
method. This will only
usually be the case if you expect that the actual entity instances returned by
the query will already be in the session or second-level cache. If they are not
already cached, iterate()
will be slower than list()
and might require many database hits for a simple
query, usually 1 for the initial select
which only returns identifiers, and n
additional selects to initialize the actual instances.
// fetch ids
Iterator iter = sess.createQuery("from eg.Qux q order by q.likeliness").iterate();
while ( iter.hasNext() ) {
Qux qux = (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;
}
}
10.4.1.2. Queries that return
tuples
Hibernate queries sometimes return tuples of objects, in which case each
tuple is returned as an array:
Iterator kittensAndMothers = sess.createQuery(
"select kitten, mother from Cat kitten join kitten.mother mother")
.list()
.iterator();
while ( kittensAndMothers.hasNext() ) {
Object[] tuple = (Object[]) kittensAndMothers.next();
Cat kitten = (Cat) tuple[0];
Cat mother = (Cat) tuple[1];
....
}
Queries may specify a property of a class in the select
clause. They may even call SQL aggregate functions.
Properties or aggregates are considered "scalar" results (and not entities in
persistent state).
Iterator results = sess.createQuery(
"select cat.color, min(cat.birthdate), count(cat) from Cat cat " +
"group by cat.color")
.list()
.iterator();
while ( results.hasNext() ) {
Object[] row = (Object[]) results.next();
Color type = (Color) row[0];
Date oldest = (Date) row[1];
Integer count = (Integer) row[2];
.....
}
10.4.1.4. Bind parameters
Methods on Query
are provided for binding values
to named parameters or JDBC-style ?
parameters. Contrary to JDBC, Hibernate numbers parameters from
zero. Named parameters are identifiers of the form :name
in the query string. The advantages of named
parameters are:
-
named parameters are insensitive to the order they occur in the query string
-
they may occur multiple times in the same query
-
they are self-documenting
//named parameter (preferred)
Query q = sess.createQuery("from DomesticCat cat where cat.name = :name");
q.setString("name", "Fritz");
Iterator cats = q.iterate();
//positional parameter
Query q = sess.createQuery("from DomesticCat cat where cat.name = ?");
q.setString(0, "Izi");
Iterator cats = q.iterate();
//named parameter list
List names = new ArrayList();
names.add("Izi");
names.add("Fritz");
Query q = sess.createQuery("from DomesticCat cat where cat.name in (:namesList)");
q.setParameterList("namesList", names);
List cats = q.list();
If you need to specify bounds upon your result set (the maximum number of
rows you want to retrieve and / or the first row you want to retrieve) you
should use methods of the Query
interface:
Query q = sess.createQuery("from DomesticCat cat");
q.setFirstResult(20);
q.setMaxResults(10);
List cats = q.list();
Hibernate knows how to translate this limit query into the native SQL of your
DBMS.
10.4.1.6. Scrollable iteration
If your JDBC driver supports scrollable ResultSet
s, the Query
interface
may be used to obtain a ScrollableResults
object,
which allows flexible navigation of the query results.
Query q = sess.createQuery("select cat.name, cat from DomesticCat cat " +
"order by cat.name");
ScrollableResults cats = q.scroll();
if ( cats.first() ) {
// find the first name on each page of an alphabetical list of cats by name
firstNamesOfPages = new ArrayList();
do {
String name = cats.getString(0);
firstNamesOfPages.add(name);
}
while ( cats.scroll(PAGE_SIZE) );
// Now get the first page of cats
pageOfCats = new ArrayList();
cats.beforeFirst();
int i=0;
while( ( PAGE_SIZE > i++ ) && cats.next() ) pageOfCats.add( cats.get(1) );
}
cats.close()
Note that an open database connection (and cursor) is required for this
functionality, use setMaxResult()
/setFirstResult()
if you need offline pagination
functionality.
10.4.1.7. Externalizing named
queries
You may also define named queries in the mapping document. (Remember to use a
CDATA
section if your query contains characters that
could be interpreted as markup.)
<query name="ByNameAndMaximumWeight"><![CDATA[
from eg.DomesticCat as cat
where cat.name = ?
and cat.weight > ?
] ]></query>
Parameter binding and executing is done programatically:
Query q = sess.getNamedQuery("ByNameAndMaximumWeight");
q.setString(0, name);
q.setInt(1, minWeight);
List cats = q.list();
Note that the actual program code is independent of the query language that
is used, you may also define native SQL queries in metadata, or migrate existing
queries to Hibernate by placing them in mapping files.
Also note that a query declaration inside a <hibernate-mapping>
element requires a global unique
name for the query, while a query declaration inside a <class>
element is made unique automatically by
prepending the fully qualified name of the class, for example eg.Cat.ByNameAndMaximumWeight
.
10.4.2. Filtering collections
A collection filter is a special type of
query that may be applied to a persistent collection or array. The query string
may refer to this
, meaning the current collection
element.
Collection blackKittens = session.createFilter(
pk.getKittens(),
"where this.color = ?")
.setParameter( Color.BLACK, Hibernate.custom(ColorUserType.class) )
.list()
);
The returned collection is considered a bag, and it's a copy of the given
collection. The original collection is not modified (this is contrary to the
implication of the name "filter", but consistent with expected behavior).
Observe that filters do not require a from
clause
(though they may have one if required). Filters are not limited to returning the
collection elements themselves.
Collection blackKittenMates = session.createFilter(
pk.getKittens(),
"select this.mate where this.color = eg.Color.BLACK.intValue")
.list();
Even an empty filter query is useful, e.g. to load a subset of elements in a
huge collection:
Collection tenKittens = session.createFilter(
mother.getKittens(), "")
.setFirstResult(0).setMaxResults(10)
.list();
HQL is extremely powerful but some developers prefer to build queries
dynamically, using an object-oriented API, rather than building query strings.
Hibernate provides an intuitive Criteria
query API
for these cases:
Criteria crit = session.createCriteria(Cat.class);
crit.add( Restrictions.eq( "color", eg.Color.BLACK ) );
crit.setMaxResults(10);
List cats = crit.list();
The Criteria
and the associated Example
API are discussed in more detail in Chapter 15, Criteria Queries.
10.4.4. Queries in
native SQL
You may express a query in SQL, using createSQLQuery()
and let Hibernate take care of the mapping
from result sets to objects. Note that you may at any time call session.connection()
and use the JDBC Connection
directly. If you chose to use the Hibernate API,
you must enclose SQL aliases in braces:
List cats = session.createSQLQuery("SELECT {cat.*} FROM CAT {cat} WHERE ROWNUM<10")
.addEntity("cat", Cat.class)
.list();
List cats = session.createSQLQuery(
"SELECT {cat}.ID AS {cat.id}, {cat}.SEX AS {cat.sex}, " +
"{cat}.MATE AS {cat.mate}, {cat}.SUBCLASS AS {cat.class}, ... " +
"FROM CAT {cat} WHERE ROWNUM<10")
.addEntity("cat", Cat.class)
.list()
SQL queries may contain named and positional parameters, just like Hibernate
queries. More information about native SQL queries in Hibernate can be found in
Chapter 16, Native SQL.