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

CHAPTER 10 n ADVANCED QUERIES USING CRITERIA 217 n ADVANCED QUERIES USING CRITERIA 217 The other two sqlRestriction()methods permit you to pass JDBC parameters and values into the SQL statement. Use the standard JDBC parameter placeholder (?) in your SQL fragment. Paging Through the Result Set One common application pattern that criteria can address is pagination through the result set of a database query. When we say pagination, we mean an interface in which the user sees part of the result set at a time, with navigation to go forward and backward through the results. A naive pagination implementation might load the entire result set into memory for each navigation action, and would usually lead to atrocious performance. Both of us have worked on improving performance for separate projects suffering from exactly this problem. The problem appeared late in testing because the sample dataset that developers were working with was trivial, and they did not notice any performance problems until the first test data load. If you are programming directly to the database, you will typically use proprietary database SQL or database cursors to support paging. Hibernate abstracts this away for you behind the scenes, Hibernate uses the appropriate method for your database. There are two methods on the Criteria interface for paging: setFirstResult() and setMaxResults(). The setFirstResult() method takes an integer that represents the first row in your result set, starting with row 0. You can tell Hibernate to retrieve a fixed number of objects with the setMaxResults() method. Using both of these together, we can construct a paging component in our web or Swing application. We have a very small dataset in our sample application, so here is an admittedly trivial example: Criteria crit = session.createCriteria(Product.class); crit.setFirstResult(1); crit.setMaxResults(2); List results = crit.list(); As you can see, this makes paging through the result set easy. You can increase the first result you return (for example, from 1, to 21, to 41, etc.) to page through the result set. If you only have one result in your result set, Hibernate has a shortcut method for obtaining just that object. Obtaining a Unique Result Sometimes you know you are only going to return zero or one objects from a given query. This could be because you are calculating an aggregate (like COUNT, which we discuss later), or because your restrictions naturally lead to a unique result when selecting upon a property under a unique constraint, for example. You may also limit the results of any result set to just the first result, using the setMaxResults() method discussed earlier. In any of these circumstances, if you want obtain a single Object reference instead of a List, the uniqueResult() method on the Criteria object returns an object or null. If there is more than one result, the uniqueResult() method throws a HibernateException. 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:
Visit our web design programs services for an affordable and reliable webhost to suit all your needs.

216 CHAPTER 10 n ADVANCED QUERIES USING CRITERIA CHAPTER 10 n ADVANCED QUERIES USING CRITERIA If we want to have two restrictions that return objects that satisfy either or both of the restrictions, we need to use the or() method on the Restrictions class, as follows: 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); List results = crit.list(); The orExp logical expression that we have created here will be treated like any other criterion. We can therefore add another restriction to the criteria: 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(); If we wanted to create an OR expression with more than two different criteria, we would use an org.hibernate.criterion.Disjunction object to represent a disjunction. You can obtain this object from the disjunction() factory method on the Restrictions class. The disjunction is more convenient than building a tree of OR expressions in code. To represent an AND expression with more than two criteria, you can use the conjunction() method although you can easily just add those to the Criteria object. The conjunction is also more convenient than building a tree of AND expressions in code. Here is an example that uses the disjunction: Criteria crit = session.createCriteria(Product.class); Criterion price = Restrictions.gt(”price”,new Double(25.0)); Criterion name = Restrictions.like(”name”,”Mou%”); Criterion desc = Restrictions.ilike(”description”,”blocks%”); Disjunction disjunction = Restrictions.disjunction(); disjunction.add(price); disjunction.add(name); disjunction.add(desc); crit.add(disjunction); List results = crit.list(); The last type of restriction is the SQL restriction sqlRestriction(). This restriction allows you to directly specify SQL in the Criteria API. This is useful if you need to use SQL clauses that Hibernate does not support through the Criteria API. Your application s code does not need to know the name of the table your class uses use {alias} to signify the class s table, as follows: Criteria crit = session.createCriteria(Product.class); crit.add(Restrictions.sqlRestriction(”{alias}.name like ‘Mou%’”)); List results = crit.list()
From our experience, we are can tell you that you can find a reliable and cheap webhost service at Java Web Hosting services.

CHAPTER 10 n ADVANCED QUERIES USING CRITERIA 215 n ADVANCED QUERIES USING CRITERIA 215 public static SimpleExpression like(String propertyName, String value, MatchMode matchMode) The first like() or ilike() method takes a pattern for matching. Use the % character as a wildcard to match parts of the string, like so: Criteria crit = session.createCriteria(Product.class); crit.add(Restrictions.like(”name”,”Mou%”)); List results = crit.list(); The second like()or ilike() method uses an org.hibernate.criterion.MatchMode object to specify how to match the specified value to the stored data. The MatchMode object (a type-safe enumeration) has four different matches: ANYWHERE: Anyplace in the string END: The end of the string EXACT: An exact match START: The beginning of the string Here is an example that uses the ilike() method to search for case-insensitive matches at the end of the string: Criteria crit = session.createCriteria(Product.class); crit.add(Restrictions.ilike(”name”,”browser”, MatchMode.END)); List results = crit.list(); The isNull() and isNotNull()restrictions allow you to do a search for objects that have (or do not have) null property values. This is easy to demonstrate: Criteria crit = session.createCriteria(Product.class); crit.add(Restrictions.isNull(”name”)); List results = crit.list(); Several of the restrictions are useful for doing math comparisons. The greater-than comparison is gt(), the greater-than-or-equal-to comparison is ge(), the less-than comparison is lt(), and the less-than-or-equal-to comparison is le(). We can do a quick retrieval of all products with prices over $25 like this: Criteria crit = session.createCriteria(Product.class); crit.add(Restrictions.gt(”price”,new Double(25.0))); List results = crit.list(); Moving on, we can start to do more complicated queries with the Criteria API. For example, we can combine ANDand OR restrictions in logical expressions. When you add more than one constraint to a criteria query, it is interpreted as an AND, like so: Criteria crit = session.createCriteria(Product.class); crit.add(Restrictions.gt(”price”,new Double(25.0))); crit.add(Restrictions.like(”name”,”K%”)); List results = crit.list();
We recommend high quality webhost to host and run your jsp application: christian web host services.

214 CHAPTER 10 n ADVANCED QUERIES USING CRITERIA CHAPTER 10 n ADVANCED QUERIES USING CRITERIA Using Restrictions with Criteria The Criteria API makes it easy to use restrictions in your queries to selectively retrieve objects; for instance, your application could retrieve only products with a price over $30. You may add these restrictions to a Criteriaobject with the add() method. The add() method takes an org.hibernate.criterion.Criterion object that represents an individual restriction. You can have more than one restriction for a criteria query. Although you could create your own objects implementing the Criterionobject, or extend an existing Criterionobject, we recommend that you use Hibernate s built-in Criterion objects from your application s business logic. For instance, you could create your own factory class that returns instances of Hibernate s Criterionobjects appropriately set up for your application s restrictions. Use the factory methods on the org.hibernate.criterion.Restrictions class to obtain instances of the Criterion objects. To retrieve objects that have a property value that equals your restriction, use the eq() method on Restrictions, as follows: public static SimpleExpression eq(String propertyName, Object value) We would typically nest the eq() method in the add() method on the Criteria object. Here is an example of how this would look if we were searching for products with the name Mouse : Criteria crit = session.createCriteria(Product.class); crit.add(Restrictions.eq(”name”,”Mouse”)); List results = crit.list() Next, we search for products that do not have the name Mouse. For this, we would use the ne() method on the Restrictions class to obtain a not-equal restriction: Criteria crit = session.createCriteria(Product.class); crit.add(Restrictions.ne(”name”,”Mouse”)); List results = crit.list(); nTip You cannot use the not-equal restriction to retrieve records with a NULL value in the database for that property (in SQL, and therefore in Hibernate, NULL represents the absence of data, and so cannot be compared with data). If you need to retrieve objects with NULL properties, you will have to use the isNull() restriction, which we discuss further on in the chapter. You can combine the two with an OR logical expression, which we also discuss later in the chapter. Instead of searching for exact matches, we can also retrieve all objects that have a property matching part of a given pattern. To do this, we need to create an SQL LIKE clause, with either the like() or the ilike() method. The ilike() method is case-insensitive. In either case, we have two different ways to call the method: public static SimpleExpression like(String propertyName, Object value) or
If you are in need for chaep and reliable webhost to host your website, our recommendation is http web server services.

Advanced Queries Using Criteria CHAPTER 1 0 n n n Advanced Queries Using Criteria CHAPTER 1 0 n n n Hibernate provides three different ways to retrieve data. We have already discussed HQL and the use of native SQL queries now we add criteria. The Criteria Query API lets you build nested, structured query expressions in Java, providing a compile-time syntax-checking that is not possible with a query language like HQL or SQL. The Criteria API also includes query by example (QBE) functionality this lets you supply example objects that contain the properties you would like to retrieve instead of having to spell the components of the query out step by step. It also includes projection and aggregation methods, including counts. In this chapter, we explore the use of the Criteria API using the sample database established in the previous chapter. Using the Criteria API The Criteria API allows you to build up a criteria query object programmatically the org.hibernate.Criteria interface defines the available methods for one of these objects. The Hibernate Session interface contains several createCriteria() methods. Pass the persistent object s class or its entity name to the createCriteria()method, and Hibernate will create a Criteria object that returns instances of the persistence object s class when your application executes a criteria query. The simplest example of a criteria query is one with no optional parameters or restrictions the criteria query will simply return every object that corresponds to the class. Criteria crit = session.createCriteria(Product.class); List results = crit.list(); When you run this example with our sample data, you will get all objects that are instances of the Product class note that this includes any instances of the Software class because they are derived from Product. Moving on from this simple example, we will add constraints to our criteria queries so we can winnow down the result set.
If you are searching for cheap webhost for your web application, please visit MySQL5 Web Hosting services.

CHAPTER 9 n SEARCHES AND QUERIES 211 n SEARCHES AND QUERIES 211 A bit more complicated than the previous example is the native SQL that returns a result set of objects. In this case, we will need to map an entity to the SQL query. The entity consists of the alias we used for the object in the SQL query and its class. For this example, we used our Supplier class: String sql = “select {supplier.*} from Supplier supplier”; SQLQuery query = session.createSQLQuery(sql); query.addEntity(”supplier”, Supplier.class); List results = query.list(); Hibernate modifies the SQL and executes the following command against the database: select Supplier.id as id0_, Supplier.name as name2_0_ from Supplier supplier The special aliases allow Hibernate to map the database columns back to the object properties. Summary HQL is a powerful object-oriented query language that provides the power of SQL while taking advantage of Hibernate s object-relational mapping and caching. If you are porting an existing application to Hibernate, you can use Hibernate s native SQL facilities to execute SQL against the database. The SQL functionality is also useful for executing SQL statements that are specific to a given database and have no equivalents in HQL. You may turn on SQL logging for Hibernate, and Hibernate will log the generated SQL that it executes against the database. If you add a comment to your HQL query object, Hibernate will display a comment in the log next to the SQL statement this helps with tracing SQL statements back to HQL in your application.
If you are looking for affordable and reliable webhost to host and run your business application visit our ftp web hosting services.

210 CHAPTER 9 n SEARCHES AND QUERIES CHAPTER 9 n SEARCHES AND QUERIES Using Native SQL Although you should probably use HQL whenever possible, Hibernate does provide a way to use native SQL statements directly through Hibernate. One reason to use native SQL is that your database supports some special features through its dialect of SQL that are not supported in HQL. Another reason is that you may want to call stored procedures from your Hibernate application. We discuss stored procedures and other database-specific integration solutions in Appendix A. Rather than just providing an interface to the underlying JDBC connection, like other Java ORM tools, Hibernate provides a way to define the entity (or join) that the query uses. This makes integration with the rest of your ORM-oriented application easy. You can modify your SQL statements to make them work with Hibernate s ORM layer. You do need to modify your SQL to include Hibernate aliases that correspond to objects or object properties. You can specify all properties on an object with {objectname.*}, or you can specify the aliases directly with {objectname.property}. Hibernate uses the mappings to translate your object property names into their underlying SQL columns. This may not be the exact way you expect Hibernate to work, so be aware that you do need to modify your SQL statements for full ORM support. You will especially run into problems with native SQL on classes with subclasses be sure you understand how you mapped the inheritance across either a single table or multiple tables, in order that you select the right properties off of the table. Underlying Hibernate s native SQL support is the org.hibernate.SQLQuery interface, which extends the org.hibernate.Query interface already discussed. Your application will create a native SQL query from the session with the createSQLQuery() method on the Session interface. public SQLQuery createSQLQuery(String queryString) throws HibernateException After you pass a string containing the SQL query to the createSQLQuery() method, you should associate the SQL result with an existing Hibernate entity, a join, or a scalar result. The SQLQuery interface has addEntity(), addJoin(), and addScalar() methods. For the entities and joins, you can specify a lock mode, which we discuss in Chapter 8. The addEntity() methods take an alias argument and either a class name or an entity name. The addJoin() methods take an alias argument and a path to join. Using native SQL with scalar results is the simplest way to get started with native SQL. Our Java code looks like this: String sql = “select avg(product.price) as avgPrice from Product product”; SQLQuery query = session.createSQLQuery(sql); query.addScalar(”avgPrice”,Hibernate.DOUBLE); List results = query.list(); Because we did not specify any entity aliases, Hibernate executes exactly the same SQL that we passed through: select avg(product.price) as avgPrice from Product product The SQL is regular SQL (we did not have to do any aliasing here). We created an SQLQuery object, and then added a scalar mapping with the built-in double type (from the org.hibernate._Hibernate class). We needed to map the avgPriceSQL alias to the object type. The results are a List with one object a Double.
You want to have a cheap webhost for your apache application, then check apache web hosting services.

CHAPTER 9 n SEARCHES AND QUERIES 209 n SEARCHES AND QUERIES 209 simple HQL named query, and one simple SQL query that does the same thing, we have the Hibernate mapping file shown in Listing 9-4: Product.hbm.xml. Listing 9-4. Product.hbm.xml
25.0]]> 25.0]]> Notice that we embedded the SQL and HQL queries in CDATA regions. This protects our SQL queries from interfering with the XML parser we don t have to worry about special characters breaking the XML. For the native SQL query, we also had to specify a return type, so Hibernate knows what type of result data to expect from the database. When you use HQL, Hibernate handles that mapping behind the scenes, because it knows which objects went in. With SQL, you have to specify the return types yourself. In this case, we used the XML element to define our return type as a column named price, with a type of double. Hibernate converts the JDBC result set into an array of objects, just like the previous HQL query. Functionally, they are identical. We discuss native SQL in more detail in the next section of the chapter. You may also specify the flush mode, whether the query is cacheable, the cache region, the fetch size, and the timeout for the HQL and SQL queries. For the SQL query, you may additionally specify whether the SQL query is callable.
Note: If you are looking for cheap and reliable webhost to host and run your mysql application check mysql web server services.

208 CHAPTER 9 n SEARCHES AND QUERIES CHAPTER 9 n SEARCHES AND QUERIES //See the results of the update query = session.createQuery(”from Supplier”); List results = query.list(); After carrying out this query, any supplier previously named SuperCorp will be named MegaCorp. You may use a whereclause with updates to control which rows get updated, or you may leave it off to update all rows. Notice that we printed out the number of rows affected by the query. We also used named parameters in our HQL for this bulk update. Bulk deletes work in a similar way. Use the delete from clause with the class name you would like to delete from. Then use the where clause to narrow down which entries in the table you would like to delete. Use the executeUpdate() method to execute deletes against the database as well. Our code surrounding the HQL DELETE statement is basically the same we use named parameters, and we print out the number of rows affected by the delete: String hql = “delete from Product where name = :name”; Query query = session.createQuery(hql); query.setString(”name”,”Mouse”); int rowCount = query.executeUpdate(); System.out.println(”Rows affected: ” + rowCount); //See the results of the update query = session.createQuery(”from Product”); List results = query.list(); nCaution Using bulk updates and deletes in HQL works almost the same as in SQL, so keep in mind that these are powerful and can erase the data in your tables if you make a mistake with the where clause. Named Queries for HQL and SQL One of Hibernate s best features is the named query, in which your application can store its HQL queries outside the application in the mapping file. This has many benefits for application maintenance. The first benefit is that many objects can share queries you could set up static final strings on classes with the HQL queries, but Hibernate already provides a nice facility for the same thing. The next benefit is that named queries could also contain native SQL queries the application calling the named query does not need to know if the named query is SQL or HQL. This has enormous benefits for migrating SQL-based applications to Hibernate. The last benefit is that you can provide your HQL and SQL queries in a configuration file to your database administrators, who will probably find it easier to work with an XML mapping file than with HQL statements embedded in Java code. Add named queries in the appropriate Hibernate mapping file. HQL queries use the XML element, and SQL queries use the XML element. Both of these XML elements require a name attribute that uniquely identifies the query in the application. With one
Looking for affordable and reliable webhost to host and run your business application? Then look no more and go to servlet web hosting services.

CHAPTER 9 n SEARCHES AND QUERIES 207 n SEARCHES AND QUERIES 207 The distinct keyword only counts the unique values in the row set for instance, if there are 100 products, but 10 have the same price as another product in the results, then a select count(distinct product.price) from Product product query would return 90. In our database, the following query will return 2, one for each supplier: select count(distinct product.supplier.name) from Product product If we removed the distinct keyword, it would return 5, one for each product. All of these queries return an Integer object in the list. You could use the uniqueResult() method here to obtain the result. The aggregate functions available through HQL include the following: avg(property name): The average of a property s value count(property name or *): The number of times a property occurs in the results max(property name): The maximum value of the property values min(property name): The minimum value of the property values sum(property name): The sum total of the property values If you have more than one aggregate method, the result set List will contain an Object array with each of the aggregates you requested. Adding another aggregate to the select clause is straightforward: select min(product.price), max(product.price) from Product prodsuct You can also combine these with other projection properties in the result set. Bulk Updates and Deletes with HQL Bulk updates are new to HQL with Hibernate 3, and deletes work differently in Hibernate 3 than they did in Hibernate 2. The Queryinterface now contains a method called executeUpdate()for executing HQL UPDATEor DELETEstatements. The executeUpdate()method returns an intthat contains the number of rows affected by the update or delete, as follows: public int executeUpdate() throws HibernateException HQL updates look like you would expect them to, being based on SQL UPDATE statements. Do not include an alias with the update; instead, put the setkeyword right after the class name, as follows: String hql = “update Supplier set name = :newName where name = :name”; Query query = session.createQuery(hql); query.setString(”name”,”SuperCorp”); query.setString(”newName”,”MegaCorp”); int rowCount = query.executeUpdate(); System.out.println(”Rows affected: ” + rowCount);
We recommend high quality webhost to host and run your jsp application: christian web host services.