Tomcat Web Hosting, Affordable, PHP5, Plesk - Linux Experts Blog

206 CHAPTER 9 n SEARCHES AND QUERIES CHAPTER 9 n SEARCHES AND QUERIES join, right outer join, and full outer join. If you use cross join, just specify both classes in the from clause (from Product p, Supplier s). For the other joins, use a join clause after the from clause. Specify the type of join, the object property to join on, and an alias for the other class. You can use inner join to obtain the supplier for each product, and then retrieve the supplier name, product name, and product price, as so: select s.name, p.name, p.price from Product p inner join p.supplier as s You can retrieve the objects using similar syntax: from Product p inner join p.supplier as s We used aliases in these HQL statements to refer to the entities in our query expressions. These are particularly important in queries with associations that refer to two different entities with the same class for instance, if we are doing a join from a table back to itself. Commonly, these types of joins are used to organize tree data structures. Notice that Hibernate does not return Objectobjects in the result set; instead, Hibernate returns Objectarrays in the results. You will have to access the contents of the Objectarrays to get the Supplier and the Productobjects. If you would like to start optimizing performance, you can ask Hibernate to fetch the associated objects and collections for an object in one query. If you were using lazy loading with Hibernate, the objects in the collection would not be initialized until you accessed them. If you use fetch on a join in your query, you can ask Hibernate to retrieve the objects in the collection at the time the query executes. Add the fetchkeyword after the join in the query, like so: from Supplier s inner join fetch s.products as p When you use fetch for a query like this, Hibernate will return only the Supplier objects, not the Product objects. This is because you are specifying the join, so Hibernate knows which objects to fetch (instead of using lazy loading). If you need to get the Product objects, you can access them through the associated Supplierobject. You cannot use the properties of the Product objects in expressions in the where clause. Use of the fetch keyword overrides any settings you have in the mapping file for object initialization. Aggregate Methods HQL supports a range of aggregate methods, similar to SQL. They work the same way in HQL as in SQL, so you do not have to learn any specific Hibernate terminology. The difference is that in HQL, aggregate methods apply to the properties of persistent objects. The count(…) method returns the number of times the given column name appears in the result set. You may use the count(*) syntax to count all the objects in the result set, or count(product.name) to count the number of objects in the result set with a name property. Here is an example using the count(*) method to count all products: select count(*) from Product product
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 9 n SEARCHES AND QUERIES 205 n SEARCHES AND QUERIES 205 If you only have one result in your HQL result set, Hibernate has a shortcut method for obtaining just that object. Obtaining a Unique Result HQL s Query interface provides a uniqueResult() method for obtaining just one object from an HQL query. Although your query may only yield one object, you may also use the uniqueResult() method with other result sets if you limit the results to just the first result. You could use the setMaxResults() method discussed in the previous section. The uniqueResult() method on the Query object returns a single object, or null if there are zero results. If there is more than one result, the uniqueResult() method throws a NonUniqueResultException. The following short example demonstrates having a result set that would have included more than one result, except that it was limited with the setMaxResults() method: String hql = “from Product where price>25.0″; Query query = session.createQuery(hql); query.setMaxResults(1); Product product = (Product) query.uniqueResult(); //test for null here if needed Unless your query returns one or zero results, the uniqueResult() method will throw a NonUniqueResultException exception. Do not expect Hibernate just to pick off the first result and return it either set the maximum results of the HQL query to 1, or obtain the first object from the result list. Sorting Results with the order by Clause To sort your HQL query s results, you will need to use the order by clause. You can order the results by any property on the objects in the result set: either ascending (asc) or descending (desc). You can use ordering on more than one property in the query if you need to. A typical HQL query for sorting results looks like this: from Product p where p.price>25.0 order by p.price desc If you wanted to sort by more than one property, you would just add the additional properties to the end of the order by clause, separated by commas. For instance, you could sort by product price and the supplier s name, as follows: from Product p order by p.supplier.name asc, p.price asc HQL is more straightforward for ordering than the equivalent approach using the Criteria Query API. Associations Associations allow you to use more than one class in an HQL query, just as SQL allows you to use joins between tables in a relational database. Add an association to an HQL query with the join clause. Hibernate supports five different types of joins: inner join, cross join, left outer
From our experience, we are can tell you that you can find a reliable and cheap webhost service at Java Web Hosting services.

204 CHAPTER 9 n SEARCHES AND QUERIES CHAPTER 9 n SEARCHES AND QUERIES The simplest example of named parameters uses regular SQL types for the parameters: String hql = “from Product where price > :price”; Query query = session.createQuery(hql); query.setDouble(”price”,25.0); List results = query.list(); Normally, you do not know the values that are to be substituted for the named parameters and if you did, you would probably encode them directly into the query string. When the value to be provided will be known only at run time, you can use some of HQL s object-oriented features to provide objects as values for named parameters. The Queryinterface has a setEntity()method that takes the name of a parameter and an object. Using this functionality, we could retrieve all the products that have a supplier whose object we already have: String supplierHQL = “from Supplier where name=’MegaInc’”; Query supplierQuery = session.createQuery(supplierHQL); Supplier supplier = (Supplier) supplierQuery.list().get(0); String hql = “from Product as product where product.supplier=:supplier”; Query query = session.createQuery(hql); query.setEntity(”supplier”,supplier); List results = query.list(); You can also use regular JDBC query parameters in your HQL queries. We do not particularly see any reason why you would want to, but they do work. Paging Through the Result Set Pagination through the result set of a database query is a very common application pattern. Typically, you would use pagination for a web application that returned a large set of data for a query. The web application would page through the database query result set to build the appropriate page for the user. The application would be very slow if the web application loaded all of the data into memory for each user. Instead, you can page through the result set and retrieve the results you are going to display one chunk at a time. There are two methods on the Query interface for paging: setFirstResult() and setMaxResults(), just as with the Criteria interface. The setFirstResult() method takes an integer that represents the first row in your result set, starting with row 0. You can tell Hibernate to only retrieve a fixed number of objects with the setMaxResults() method. Your HQL is unchanged you only need to modify the Java code that executes the query. Excuse our tiny dataset for this trivial example of pagination: Query query = session.createQuery(”from Product”); query.setFirstResult(1); query.setMaxResults(2); List results = query.list(); displayProductsList(results); You can change the numbers around and play with the pagination. If you turn on SQL logging, you can see which SQL commands Hibernate uses for pagination. For the open source HSQLDB database, Hibernate uses topand limit.
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 9 n SEARCHES AND QUERIES 203 n SEARCHES AND QUERIES 203 Criteria crit = session.createCriteria(Product.class); Criterion price = Restrictions.gt(”price”,new Double(25.0)); Criterion name = Restrictions.like(”name”,”Mou%”); LogicalExpression orExp = Restrictions.or(price,name); crit.add(orExp); crit.add(Restrictions.ilike(”description”,”blocks%”)); List results = crit.list(); The equivalent HQL would be the following: from Product where price > 25.0 and name like ‘Mou%’ We would have to wrap that HQL in a couple of lines of Java code, but even so, we find this particular example to be clearer in HQL. In the previous HQL example, you can see that we used the where clause with a > (greater than) comparison operator, an andlogical operator, and a like comparison operator. You do have to enclose literal strings in quotes in HQL. To find names that have the literal Mou at the beginning of the string, we used % in the query. Using Named Parameters Hibernate supports named parameters in its HQL queries. This makes writing queries that accept input from the user easy and you do not have to defend against SQL injection attacks. nNote SQL injection is an attack against applications that create SQL directly from user input with string concatenation. For instance, if we accept a name from the user through a web application form, then it would be very bad form to construct an SQL (or HQL) query like this: String sql = “select p from products where name = ‘” + name + “‘”; A malicious user could pass a name to the application that contained a terminating quote and semicolon, followed by another SQL command (such as delete from products) that would let them do whatever they wanted. They would just need to end with another command that matched the SQL statement s ending quote. This is a very common attack, especially if the malicious user can guess details of your database structure. You could escape the user s input yourself for every query, but it is much less of a security risk if you let Hibernate manage all of your input with named parameters. Hibernate s named parameters are similar to the JDBC query parameters (?) you may already be familiar with, but Hibernate s parameters are less confusing. It is also more straightforward to use Hibernate s named parameters if you have a query that uses the same parameter in multiple places. When you use JDBC query parameters, any time you add, change, or delete parts of the SQL statement, you need to update your Java code that sets its parameters, because the parameters are indexed based on the order they appear in the statement. Hibernate lets you provide names for the parameters in the HQL query, so you do not have to worry about accidentally moving parameters further up or back in the query.
Please visit Domain Name Hosting services for high quality webhost to host and run your jsp applications.

202 CHAPTER 9 n SEARCHES AND QUERIES CHAPTER 9 n SEARCHES AND QUERIES run a projection query on the products in the database that only returned the names, instead of loading the full object into memory, as follows: select product.name from Product product The result set for this query will contain a List of Java String objects. Additionally, we can retrieve the prices and the names for each product in the database, like so: select product.name, product.price from Product product This result set contains a List of Object arrays each array represents one set of properties (in this case, a name and price pair). If you re only interested in a few properties, this approach can allow you to reduce network traffic to the database server and save memory on the application s machine. Using Restrictions with HQL As with SQL, you use the where clause to select results that match your query s expressions. HQL provides many different expressions that you can use to construct a query. In the HQL language grammar, there are the following possible expressions: Logic operators: OR, AND, NOT Equality operators: =, <>, !=, ^= Comparison operators: <, >, <=, >=, like, not like, between, not between Math operators: +, -, *, / Concatenation operator: || Cases: Case when then else _ end Collection expressions: some, exists, all, any In addition, you may also use the following expressions in the where clause: HQL named parameters: :date, :quantity JDBC query parameter: ? Date and time SQL-92 functional operators: current_time(), current_date(), current_timestamp() SQL functions (supported by the database): length(), upper(), lower(), ltrim(), rtrim(), etc. Using these restrictions, you can build a where clause in HQL that is as powerful as an SQL query. For many queries, HQL syntax is more compact and elegant than the Criteria Query API syntax (discussed in Chapter 10). For instance, here is an example of a criteria query that uses logical expressions:
We recommend cheap and reliable webhost to host and run your web applications: Coldfusion Web Hosting services.

CHAPTER 9 n SEARCHES AND QUERIES 201 n SEARCHES AND QUERIES 201 Use this to identify the SQL output in your application s logs if SQL logging is enabled. For instance, if we add a comment to this example, the Java code would look like this: String hql = “from Supplier”; Query query = session.createQuery(hql); query.setComment(”My HQL: ” + hql); List results = query.list(); The output in your application s log will have the comment in a Java-style comment before the SQL: Hibernate: /*My HQL: from Supplier*/ select supplier0_.id as id, supplier0_.name as name2_ from Supplier supplier0_ We have found this useful for identifying SQL in our logs, especially because the generated SQL is a little difficult to follow when you are scanning large quantities of it in logs. The from Clause and Aliases We have already discussed the basics of the fromclause in HQL in the earlier section, The First Example with HQL. The most important feature to note is the alias. Hibernate allows you to assign aliases to the classes in your query with the asclause. Use the aliases to refer back to the class inside the query. For instance, our previous simple example would be the following: from Product as p or the following: from Product as product You ll see either alias-naming convention in applications. The as keyword is optional you can also specify the alias directly after the class name, as follows: from Product product If you need to fully qualify a class name in HQL, just specify the package and class name. Hibernate will take care of most of this behind the scenes, so you only really need this if you have classes with duplicate names in your application. If you need to do this in Hibernate, use syntax such as the following: from com.hibernatebook.criteria.Product The from clause is very basic and useful for working directly with objects. However, if you want to work with the object s properties without loading the full objects into memory, you must use the selectclause. The select Clause and Projection The select clause provides more control over the result set than the fromclause. If you want to obtain the properties of objects in the result set, use the select clause. For instance, we could
Searching for affordable and proven webhost to host and run your servlet applications? Go to Linux Web Hosting services and you will find it.

200 CHAPTER 9 n SEARCHES AND QUERIES CHAPTER 9 n SEARCHES AND QUERIES Logging the Underlying SQL Hibernate can output the underlying SQL behind your HQL queries into your application s log file. This is especially useful if the HQL query does not give the results you expect, or the query takes longer than you wanted. You can run the SQL that Hibernate generates directly against your database in the database s query analyzer at a later date to determine the causes of the problem. This is not a feature you will have to use frequently, but it is useful should you need to turn to your database administrators for help in tuning your Hibernate application. The easiest way to see the SQL for a Hibernate HQL query is to enable SQL output in the logs with the hibernate.show_sql property. Set this property to true in your hibernate. properties or hibernate.cfg.xml configuration files, and Hibernate will output the SQL into the logs. You do not need to enable any other logging settings although setting logging for Hibernate to debug also outputs the generated SQL statements, along with a lot of other verbiage. After enabling SQL output in Hibernate, you should rerun the previous example. Here is the generated SQL statement for the HQL statement from Product: select product0_.id as id, product0_.name as name0_, product0_.description as descript3_0_, product0_.price as price0_, product0_.supplierId as supplierId0_, product0_1_.version as version1_, case when product0_1_.productId is not null then 1 when product0_.id is not null then 0 end as clazz_ from Product product0_ left outer join Software product0_1_ on product0_.id=product0_1_.productId As an aside, remember that the Software class inherits from Product, which complicates Hibernate s generated SQL for this simple query. When we select all objects from our simple Supplier class, the generated SQL for the HQL query from Supplier is much simpler: select supplier0_.id as id, supplier0_.name as name2_ from Supplier supplier0_ When you look in your application s output for the Hibernate SQL statements, they will be prefixed with Hibernate:. The previous SQL statement would look like this: Hibernate: select supplier0_.id as id, supplier0_.name as name2_ from Supplier supplier0_ If you turn your log4j logging up to debug for the Hibernate classes, you will see SQL statements in your log files, along with lots of information about how Hibernate parsed your HQL query and translated it into SQL. Commenting the Generated SQL Tracing your HQL statements through to the generated SQL can be difficult, so Hibernate provides a commenting facility on the Query object that lets you apply a comment to a specific query. The Query interface has a setComment() method that takes a String object as an argument, as follows: public Query setComment(String comment)
You need excellent and relaible webhost company to host your web applications? Then pay a visit to Inexpensive Web Hosting services.

CHAPTER 9 n SEARCHES AND QUERIES 199 n SEARCHES AND QUERIES 199 The Software class, shown in Listing 9-3, extends the Product class and adds a version property we added this subclass so that we could demonstrate polymorphism with Hibernate s queries. Listing 9-3. The Software Class package com.hibernatebook.queries; public class Software extends Product { private String version; public Software() { } public Software(String name, String description, double price, String version) { super(name, description, price); this.setVersion(version); } public String getVersion() { return version; } public void setVersion(String version) { this.version = version; } } The Hibernate mapping files for these three classes are in the source directory for the book, along with a test harness for populating the database and running the examples in this chapter and the next. The first example executes our HQL statement, from Product, and then retrieves a List of Product objects. Query query = session.createQuery(”from Product”); List results = query.list(); Many of the other examples in this chapter use the same supporting Java code as this example. We are going to provide just the HQL for these examples you can execute them the same way we did here, substituting that HQL for the from Product HQL statement. This should make each example clearer as to what you should be looking at. You could also execute these HQL statements in the Hibernate Tools scratch pad.
We would like to recommend you tested and proved virtual web hosting services, which you will surely find to be of great quality.

198 CHAPTER 9 n SEARCHES AND QUERIES CHAPTER 9 n SEARCHES AND QUERIES public Product(String name, String description, double price) { this.name = name; this.description = description; this.price = price; } public String getDescription() { return description; } public void setDescription(String description) { this.description = description; } public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public Supplier getSupplier() { return supplier; } public void setSupplier(Supplier supplier) { this.supplier = supplier; } public double getPrice() { return price; } public void setPrice(double price) { this.price = price; } }
Go visit our java server pages services for a reliable, lowcost webhost to satisfy all your needs.

CHAPTER 9 n SEARCHES AND QUERIES 197 n SEARCHES AND QUERIES 197 public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public List getProducts() { return products; } public void setProducts(List products) { this.products = products; } } The Product class, shown in Listing 9-2, has name, price, and description properties, along with a reference to its parent supplier. Listing 9-2. The Product Class package com.hibernatebook.queries; public class Product { private int id; private Supplier supplier; private String name; private String description; private double price; public Product() { }
We would like to recommend you tested and proved virtual web hosting services, which you will surely find to be of great quality.