Cheap Java Services With Tomcat, Jsp, Php, Mysql, J2Ee, Servlet, Struts & Hibernate Hosting Support and Coldfusion Web Hosting

CHAPTER 15 Advanced query options 15.1.3 Projection and report queries In HQL, JPA QL, and SQL, you write a SELECT clause to define the projection for a particular query. The Criteria API also supports projection, of course programmatically and not string-based. You can select exactly which objects or properties of objects you need in the query result and how you possibly want to aggregate and group results for a report. Simple projection lists The following criteria query returns only the identifier values of Item instances which are still on auction: session.createCriteria(Item.class) .add( Restrictions.gt(”endDate”, new Date()) ) .setProjection( Projections.id() ); The setProjection() method on a Criteria accepts either a single projected attribute, as in the previous example, or a list of several properties that are to be included in the result: session.createCriteria(Item.class) .setProjection( Projections.projectionList() .add( Projections.id() ) .add( Projections.property(”description”) ) .add( Projections.property(”initialPrice”) ) ); This query returns a List of Object[], just like HQL or JPA QL would with an equivalent SELECT clause. An alternative way to specify a property for projection is the Property class: session.createCriteria(Item.class) .setProjection( Projections.projectionList() .add( Property.forName(”id”) ) .add( Property.forName(”description”) ) .add( Property.forName(”initialPrice”) ) ); In HQL and JPA QL, you can use dynamic instantiation with the SELECT NEW operation and return a collection of custom objects instead of Object[]. Hibernate bundles a ResultTransformer for criteria queries that can do almost the same (in fact, it s more flexible). The following query returns the same result as the previous one, but wrapped in data transfer objects: session.createCriteria(Item.class) .setProjection( Projections.projectionList() .add( Projections.id()
If you are in need for chaep and reliable webhost to host your website, our recommendation is http web server services.

Querying with criteria and example Result transformers are also useful if you want to retrieve aliased entities in a join query: Criteria crit = session.createCriteria(Item.class) .createAlias(”bids”, “b”) .createAlias(”seller”, “s”) .setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP); List result = crit.list(); for (Object aResult : result) { Map map = (Map) aResult; Item item = (Item) map.get(Criteria.ROOT_ALIAS); Bid bid = (Bid) map.get(”b”); User seller = (User) map.get(”s”); … } First, a criteria query is created that joins Item with its bids and seller associations. This is an SQL inner join across three tables. The result of this query, in SQL, is a table where each result row contains item, bid, and user data almost the same as shown in figure 14.2. With the default transformer, Hibernate returns only Item instances. And, with the DISTINCT_ROOT_ENTITY transformer, it filters out the duplicate Item references. Neither option seems sensible what you really want is to return all information in a map. The ALIAS_TO_ENTITY_MAP transformer can marshal the SQL result into a collection of Map instances. Each Map has three entries: an Item, a Bid, and a User. All result data is preserved and can be accessed in the application. (The Criteria.ROOT_ALIAS is a shortcut for “this”.) Good use cases for this last transformer are rare. Note that you can also implement your own org.hibernate.transform.ResultTransformer. Furthermore, HQL and native SQL queries also support a ResultTransformer: Query q = session.createQuery( “select i.id as itemId,” + ” i.description as desc,” + ” i.initialPrice as price from Item i”); q.setResultTransformer( Transformers.aliasToBean(ItemDTO.class) ); This query now returns a collection of ItemDTO instances, and the attributes of this bean are populated through the setter methods setItemId(), setDesc(), and setPrice(). A much more common way to define what data is to be returned from a query is projection. The Hibernate criteria supports the equivalent of a SELECT clause for simple projection, aggregation, and grouping.
Searching for affordable and proven webhost to host and run your servlet applications? Go to Linux Web Hosting services and you will find it.

CHAPTER 15 Advanced query options As a consequence, criteria queries may return duplicate references to distinct instances of the root entity, even if you don t apply FetchMode.JOIN for a collection in your query. The last query example may return hundreds of Item references, even if you have only a dozen in the database. Remember our discussion in Dynamic fetching strategies with joins, in chapter 14, section 14.3.1 and look again at the SQL statement and resultset in figure 14.3. You can remove the duplicate references in the result List by wrapping it in a LinkedHashSet (a regular HashSet wouldn t keep the order or the query result). In HQL and JPA QL, you can also use the DISTINCT keyword; however, there is no direct equivalent of this in Criteria. This is where the ResultTransformer becomes useful. Applying a result transformer A result transformer can be applied to a query result so that you can filter or marshal the result with your own procedure instead of the Hibernate default behavior. Hibernate s default behavior is a set of default transformers that you can replace and/or customize. All criteria queries return only instances of the root entity, by default: List result = session.createCriteria(Item.class) .setFetchMode(”bids”, FetchMode.JOIN) .setResultTransformer(Criteria.ROOT_ENTITY) .list(); Set distinctResult = new LinkedHashSet(result); The Criteria.ROOT_ENTITY is the default implementation of the org.hibernate.transform.ResultTransformer interface. The previous query produces the same result, with or without this transformer set. It returns all Item instances and initializes their bids collections. The List probably (depending on the number of Bids for each Item) contains duplicate Item references. Alternatively, you can apply a different transformer: List distinctResult = session.createCriteria(Item.class) .setFetchMode(”bids”, FetchMode.JOIN) .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY) .list(); Hibernate now filters out duplicate root entity references before returning the result this is effectively the same filtering that occurs in HQL or JPA QL if you use the DISTINCT keyword.
Please visit our professional web hosting services to find out about cheap and reliable webhost service that will surely answer all your demands.

Querying with criteria and example Dynamic fetching with criteria queries In HQL and JPA QL, you use the join fetch operation to eagerly fill a collection or to initialize an object that is mapped as lazy and would otherwise be proxied. You can do the same using the Criteria API: session.createCriteria(Item.class) .setFetchMode(”bids”, FetchMode.JOIN) .add( Restrictions.like(”description”, “%Foo%”) ); This query returns all Item instance with a particular collection and eagerly loads the bids collection for each Item. A FetchMode.JOIN enables eager fetching through an SQL outer join. If you want to use an inner join instead (rare, because it wouldn t return items that don t have bids), you can force it: session.createCriteria(Item.class) .createAlias(”bids”, “b”, CriteriaSpecification.INNER_JOIN) .setFetchMode(”b”, FetchMode.JOIN) .add( Restrictions.like(”description”, “%Foo%”) ); You can also prefetch many-to-one and one-to-one associations: session.createCriteria(Item.class) .setFetchMode(”bids”, FetchMode.JOIN) .setFetchMode(”seller”, FetchMode.JOIN) .add( Restrictions.like(”description”, “%Foo%”) ); Be careful, though. The same caveats as in HQL and JPA QL apply here: Eager fetching more than one collection in parallel (such as bids and images) results in an SQL Cartesian product that is probably slower than two separate queries. Limiting the resultset for pagination, if you use eager fetching for collections, is also done in-memory. However, dynamic fetching with Criteria and FetchMode is slightly different than in HQL and JPA QL: A Criteria query doesn t ignore the global fetching strategies as defined in the mapping metadata. For example, if the bids collection is mapped with fetch=”join” or FetchType.EAGER, the following query results in an outer join of the ITEM and BID table: session.createCriteria(Item.class) .add( Restrictions.like(”description”, “%Foo%”) ); The returned Item instances have their bids collections initialized and fully loaded. This doesn t happen with HQL or JPA QL unless you manually query with LEFTJOINFETCH (or, of course, map the collection as lazy=”false”, which results in a second SQL query).
From our experience, we are can tell you that you can find a reliable and cheap webhost service at Java Web Hosting services.

CHAPTER 15 Advanced query options The second way to express inner joins with the Criteria API is to assign an alias to the joined entity: session.createCriteria(Item.class) .createAlias(”bids”, “b”) .add( Restrictions.like(”description”, “%Foo%”) ) .add( Restrictions.gt(”b.amount”, new BigDecimal(99) ) ); And the same for a restriction on a single-valued association, the seller: session.createCriteria(Item.class) .createAlias(”seller”, “s”) .add( Restrictions.like(”s.email”, “%hibernate.org” ) ); This approach doesn t use a second instance of Criteria; it s basically the same alias assignment mechanism you d write in the FROM clause of an HQL/JPA QL statement. Properties of the joined entity must then be qualified by the alias assigned in createAlias() method, such as s.email. Properties of the root entity of the criteria query (Item) may be referred to without the qualifying alias, or with the alias “this”: session.createCriteria(Item.class) .createAlias(”bids”, “b”) .add( Restrictions.like(”this.description”, “%Foo%”) ) .add( Restrictions.gt(”b.amount”, new BigDecimal(99) ) ); Finally, note that at the time of writing only joining of associated entities or collections that contain references to entities (one-to-many and many-to-many) is supported in Hibernate with the Criteria API. The following example tries to join a collection of components: session.createCriteria(Item.class) .createAlias(”images”, “img”) .add( Restrictions.gt(”img.sizeX”, 320 ) ); Hibernate fails with an exception and tells you that the property you want to alias doesn t represent an entity association. We think this feature will likely be implemented by the time you read this book. Another syntax that is also invalid, but that you may be tempted to try, is an implicit join of a single-valued association with the dot notation: session.createCriteria(Item.class) .add( Restrictions.like(”seller.email”, “%hibernate.org”) ); The “seller.email” string isn t a property or a component s property path. Create an alias or a nested Criteria object to join this entity association. Let s discuss dynamic fetching of associated objects and collections.
We would like to recommend you tested and proved virtual web hosting services, which you will surely find to be of great quality.

Querying with criteria and example We first look at regular joins and how you can express restrictions that involve associated classes. Joining associations for restriction There are two ways to express a join in the Criteria API; hence there are two ways in which you can use aliases for restriction. The first is the createCriteria() method of the Criteria interface. This basically means you can nest calls to createCriteria(): Criteria itemCriteria = session.createCriteria(Item.class); itemCriteria.add( Restrictions.like(”description”, “Foo”, MatchMode.ANYWHERE) ); Criteria bidCriteria = itemCriteria.createCriteria(”bids”); bidCriteria.add( Restrictions.gt( “amount”, new BigDecimal(99) ) ); List result = itemCriteria.list(); You usually write the query as follows (method chaining): List result = session.createCriteria(Item.class) .add( Restrictions.like(”description”, “Foo”, MatchMode.ANYWHERE) ) .createCriteria(”bids”) .add( Restrictions.gt(”amount”, new BigDecimal(99) ) ) .list(); The creation of a Criteria for the bids of the Item results in an inner join between the tables of the two classes. Note that you may call list() on either Criteria instance without changing the query result. Nesting criteria works not only for collections (such as bids), but also for single-valued associations (such as seller): List result = session.createCriteria(Item.class) .createCriteria(”seller”) .add( Restrictions.like(”email”, “%@hibernate.org”) ) .list(); This query returns all items that are sold by users with a particular email address pattern.
If you are searching for cheap webhost for your web application, please visit MySQL5 Web Hosting services.

CHAPTER 15 Advanced query options ” where b.ITEM_ID = {alias}.ITEM_ID )” ) ); This query returns all Item objects which have no bids greater than 100. (The Hibernate criteria query system is extensible: You could also wrap the LENGTH() SQL function in your own implementation of the Criterion interface.) Finally, you can write criteria queries that include subqueries. Writing subqueries A subquery in a criteria query is a WHERE clause subselect. Just like in HQL, JPA QL, and SQL, the result of a subquery may contain either a single row or multiple rows. Typically, subqueries that return single rows perform aggregation. The following subquery returns the total number of items sold by a user; the outer query returns all users who have sold more than 10 items: DetachedCriteria subquery = DetachedCriteria.forClass(Item.class, “i”); subquery.add( Restrictions.eqProperty(”i.seller.id”, “u.id”)) .add( Restrictions.isNotNull(”i.successfulBid”) ) .setProjection( Property.forName(”i.id”).count() ); Criteria criteria = session.createCriteria(User.class, “u”) .add( Subqueries.lt(10, subquery) ); This is a correlated subquery. The DetachedCriteria refers to the u alias; this alias is declared in the outer query. Note that the outer query uses a less than operator because the subquery is the right operand. Also note that i.seller.id does not result in a join, because SELLER_ID is a column in the ITEM table, which is the root entity for that detached criteria. Let s move on to the next topic about criteria queries: joins and dynamic fetching. 15.1.2 Joins and dynamic fetching Just like in HQL and JPA QL, you may have different reasons why you want to express a join. First, you may want to use a join to restrict the result by some property of a joined class. For example, you may want to retrieve all Item instances that are sold by a particular User. Of course, you also want to use joins to dynamically fetch associated objects or collections, as you d do with the fetch keyword in HQL and JPA QL. In criteria queries you have the same options available, with a FetchMode.
From our experience, we can recommend PHP Web Hosting services, if you need affordable webhost to host and run your web application.

Querying with criteria and example We think both these options are ugly, even after spending five minutes trying to format them for maximum readability. JDK 5.0 static imports can help improve readability considerably, but even so, unless you re constructing a query on the fly, the HQL or JPA QL string is much easier to understand. You may have noticed that many standard comparison operators (less than, greater than, equals, and so on) are built into the Criteria API, but certain operators are missing. For example, any arithmetic operators such as addition and division aren t supported directly. Another issue is function calls. Criteria has built-in functions only for the most common cases such as string case-insensitive matching. HQL, on the other hand, allows you to call arbitrary SQL functions in the WHERE clause. The Criteria API has a similar facility: You can add an arbitrary SQL expression as a Criterion. Adding arbitrary SQL expressions Let s assume you want to test a string for its length and restrict your query result accordingly. The Criteria API has no equivalent to the LENGTH() function in SQL, HQL, or JPA QL. You can, however, add a plain SQL function expression to your Criteria: session.createCriteria(User.class) .add( Restrictions.sqlRestriction( “length({alias}.PASSWORD) < ?", 5, Hibernate.INTEGER ) ); This query returns all User objects that have a password with less than 5 characters. The {alias} placeholder is needed to prefix any table alias in the final SQL; it always refers to the table the root entity is mapped to (USERS in this case). You also use a position parameter (named parameters aren t supported by this API) and specify its type as Hibernate.INTEGER. Instead of a single bind argument and type, you can also use an overloaded version of the sqlRestriction() method that supports arrays of arguments and types. This facility is powerful for example, you can add an SQL WHERE clause subselect with quantification: session.createCriteria(Item.class) .add( Restrictions.sqlRestriction( "'100' > all” + ” ( select b.AMOUNT from BID b” +
If you are looking for affordable and reliable webhost to host and run your business application visit our ftp web hosting services.

CHAPTER 15 Advanced query options session.createCriteria(User.class) .add( Restrictions.like(”username”, “G%”) ); session.createCriteria(User.class) .add( Restrictions.like(”username”, “G”, MatchMode.START) ); The allowed MatchModes are START, END, ANYWHERE, and EXACT. You often also want to perform case-insensitive string matching. Where you d resort to a function such as LOWER() in HQL or JPA QL, you can rely on a method of the Criteria API: session.createCriteria(User.class) .add( Restrictions.eq(”username”, “foo”).ignoreCase() ); You can combine expressions with logical operators. Combining expressions with logical operators If you add multiple Criterion instances to the one Criteria instance, they re applied conjunctively (using and): session.createCriteria(User.class) .add( Restrictions.like(”firstname”, “G%”) ) .add( Restrictions.like(”lastname”, “K%”) ); If you need disjunction (or), there are two options. The first is to use Restrictions.or() together with Restrictions.and(): session.createCriteria(User.class) .add( Restrictions.or( Restrictions.and( Restrictions.like(”firstname”, “G%”), Restrictions.like(”lastname”, “K%”) ), Restrictions.in(”email”, emails) ) ); The second option is to use Restrictions.disjunction() together with Restrictions.conjunction(): session.createCriteria(User.class) .add( Restrictions.disjunction() .add( Restrictions.conjunction() .add( Restrictions.like(”firstname”, “G%”) ) .add( Restrictions.like(”lastname”, “K%”) ) ) .add( Restrictions.in(”email”, emails) ) );
We highly recommend you visit web and email hosting services if you need stable and cheap web hosting platform for your web applications.