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

CHAPTER 14 Querying with HQL and JPA QL all mapped to the same table together with the User data. You can also write a path expression in the SELECT clause: select distinct u.homeAddress.city from User u This query returns a List of Strings. Because duplicates don t make much sense, you eliminate them with DISTINCT. The second usage of multipart path expressions is implicit association joining: from Bid bid where bid.item.description like ‘%Foo%’ This results in an implicit join on the many-to-one associations from Bidto Item the name of this association is item. Hibernate knows that you mapped this association with the ITEM_ID foreign key in the BID table and generates the SQL join condition accordingly. Implicit joins are always directed along many-to-one or one-to-one associations, never through a collection-valued association (you can t write item.bids.amount). Multiple joins are possible in a single path expression. If the association from Item to Category is many-to-one (instead of the current many-to-many), you can write from Bid bid where bid.item.category.name like ‘Laptop%’ We frown on the use of this syntactic sugar for more complex queries. SQL joins are important, and especially when optimizing queries, you need to be able to see at a glance exactly how many of them there are. Consider the following query (again, using a many-to-one from Item to Category): from Bid bid where bid.item.category.name like ‘Laptop%’ and bid.item.successfulBid.amount > 100 How many joins are required to express this in SQL? Even if you get the answer right, it takes more than a few seconds to figure out. The answer is three; the generated SQL looks something like this: select … from BID B inner join ITEM I on B.ITEM_ID = I.ITEM_ID inner join CATEGORY C on I.CATEGORY_ID = C.CATEGORY_ID inner join BID SB on I.SUCCESSFUL_BID_ID = SB.BID_ID where C.NAME like ‘Laptop%’ and SB.AMOUNT > 100 It s more obvious if you express this query with explicit HQL and JPA QL joins in the FROM clause.
Please visit our professional web hosting services to find out about cheap and reliable webhost service that will surely answer all your demands.

Joins, reporting queries, and subselects HQL and JPA QL join options In Hibernate queries, you don t usually specify a join condition explicitly. Rather, you specify the name of a mapped Java class association. This is basically the same feature we d prefer to have in SQL, a join condition expressed with a foreign key constraint name. Because you ve mapped most, if not all, foreign key relationships of your database schema in Hibernate, you can use the names of these mapped associations in the query language. This is really syntactical sugar, but it s convenient. For example, the Item class has an association named bids with the Bid class. If you name this association in a query, Hibernate has enough information in the mapping document to then deduce the table join expression. This helps make queries less verbose and more readable. In fact, HQL and JPA QL provide four ways of expressing (inner and outer) joins: An implicit association join An ordinary join in the FROM clause A fetch join in the FROM clause A theta-style join in the WHERE clause Later we show you how to write a join between two classes that don t have an association defined (a theta-style join) and how to write ordinary and fetch joins in the FROM clause of a query. Implicit association joins are common abbreviations. (Note that we decided to make the following examples easier to read and understand by often omitting the SELECT clause valid in HQL, invalid in JPA QL.) Implicit association joins So far, you ve used simple qualified property names like bid.amount and item.description in queries. HQL and JPA QL support multipart property path expressions with a dot notation for two different purposes: Querying components Expressing implicit association joins The first use is straightforward: from User u where u.homeAddress.city = ‘Bangkok’ You reference parts of the mapped component Address with a dot notation. No tables are joined in this query; the properties of the homeAddress component are
We recommend high quality webhost to host and run your jsp application: christian web host services.

CHAPTER 14 Querying with HQL and JPA QL Figure 14.2 The result table of an ANSI-style inner join of two tables Second, you filter these joined rows using a join condition. (Any good database engine has much more sophisticated algorithms to evaluate a join; it usually doesn t build a memory-consuming product and then filters all rows.) The join condition is a boolean expression that evaluates to true if the joined row is to be included in the result. In case of the left outer join, each row in the (left) ITEM table that never satisfies the join condition is also included in the result, with NULL values returned for all columns of BID. A right outer join retrieves all bids and null if a bid has no item not a sensible query in this situation. Right outer joins are rarely used; developers always think from left to right and put the driving table first. In SQL, the join condition is usually specified explicitly. (Unfortunately, it isn t possible to use the name of a foreign key constraint to specify how two tables are to be joined.) You specify the join condition in the ON clause for an ANSI-style join or in the WHEREclause for a so-called theta-style join, where I.ITEM_ID=B.ITEM_ID. We now discuss the HQL and JPA QL join options. Remember that both are based on and translated into SQL, so even if the syntax is slightly different you should always refer to the two examples shown earlier and verify that you understood what the resulting SQL and resultset looks like. Figure 14.3 The result of an ANSI-style left outer join of two tables
Visit our web design programs services for an affordable and reliable webhost to suit all your needs.

Joins, reporting queries, and subselects 14.3 Joins, reporting queries, and subselects It s difficult to categorize some queries as advanced and others as basic. Clearly, the queries we ve shown you in the previous sections of this chapter aren t going to get you far. At the least you also need to know how joins work. The ability to arbitrarily join data is one of the fundamental strengths of relational data access. Joining data is also the basic operation that enables you to fetch several associated objects and collections in a single query. We now show you how basic join operations work and how you use them to write a dynamic fetching strategy. Other techniques we d consider advanced include nesting of statements with subselects and report queries that aggregate and group results efficiently. Let s start with joins and how they can be used for dynamic fetching. 14.3.1 Joining relations and associations You use a join to combine data in two (or more) relations. For example, you may join the data in the ITEM and BID tables, as shown in figure 14.1. (Note that not all columns and possible rows are shown; hence the dotted lines.) What most people think of when they hear the word join in the context of SQL databases is an inner join. An inner join is the most important of several types of joins and the easiest to understand. Consider the SQL statement and result in figure 14.2. This SQL statement is an ANSI-style inner join in the FROM clause. If you join tables ITEM and BID with an inner join, using their common attributes (the ITEM_ID column), you get all items and their bids in a new result table. Note that the result of this operation contains only items that have bids. If you want all items, and NULL values instead of bid data when there is no corresponding bid, you use a (left) outer join, as shown in figure 14.3. You can think of a table join as working as follows. First, you take a product of the two tables, by taking all possible combinations of ITEM rows with BID rows. Figure 14.1 The ITEM and BID tables are obvious candidates for a join operation.
If you are looking for cheap and quality webhost to host and run your website check Jboss Web Hosting services.

CHAPTER 14 Querying with HQL and JPA QL Getting distinct results When you use a SELECT clause, the elements of the result are no longer guaranteed to be unique. For example, item descriptions aren t unique, so the following query may return the same description more than once: select item.description from Item item It s difficult to see how it could be meaningful to have two identical rows in a query result, so if you think duplicates are likely, you normally use the DISTINCT keyword: select distinct item.description from Item item This eliminates duplicates from the returned list of Item descriptions. Calling functions It s also (for some Hibernate SQL dialects) possible to call database specific SQL functions from the SELECT clause. For example, the following query retrieves the current date and time from the database server (Oracle syntax), together with a property of Item: select item.startDate, current_date() from Item item The technique of database functions in the SELECT clause isn t limited to database- dependent functions. it works with other more generic (or standardized) SQL functions as well: select item.startDate, item.endDate, upper(item.name) from Item item This query returns Object[]s with the starting and ending date of an item auction, and the name of the item all in uppercase. In particular, it s possible to call SQL aggregate functions, which we ll cover later in this chapter. Note, however, that the Java Persistence standard and JPA QL don t guarantee that any function that isn t an aggregation function can be called in the SELECT clause. Hibernate and HQL allow more flexibility, and we think other products that support JPA QL will provide the same freedom to a certain extent. Also note that functions that are unknown to Hibernate aren t passed on to the database as an SQL function call, as they are in the WHERE clause. You have to register a function in your org.hibernate.Dialect to enable it for the SELECT clause in HQL. The previous sections should get you started with basic HQL and JPA QL. It s time to look at the more complex query options, such as joins, dynamic fetching, subselects, and reporting queries.
If you are in need for chaep and reliable webhost to host your website, our recommendation is http web server services.

Basic HQL and JPA QL queries and operations to restrict and order the result. All you need now is the ability to project the data of this result to what you need in your application. 14.2.3 Projection The SELECT clause performs projection in HQL and JPA QL. It allows you to specify exactly which objects or properties of objects you need in the query result. Simple projection of entities and scalar values For example, consider the following HQL query: from Item i, Bid b This is a valid HQL query, but it s invalid in JPA QL the standard requires that you use a SELECT clause. Still, the same result that is implicit from this product of Item and Bid can also be produced with an explicit SELECT clause. This query returns ordered pairs of Item and Bid instances: Query q = session.createQuery(”from Item i, Bid b”); // Query q = em.createQuery(”select i, b from Item i, Bid b”); Iterator pairs = q.list().iterator(); // Iterator pairs = q.getResultList().iterator(); while ( pairs.hasNext() ) { Object[] pair = (Object[]) pairs.next(); Item item = (Item) pair[0]; Bid bid = (Bid) pair[1]; } This query returns a List of Object[]. At index 0is the Item, and at index 1 is the Bid. Because this is a product, the result contains every possible combination of Item and Bid rows found in the two underlying tables. Obviously, this query isn t useful, but you shouldn t be surprised to receive a collection of Object[] as a query result. The following explicit SELECT clause also returns a collection of Object[]s: select i.id, i.description, i.initialPrice from Item i where i.endDate > current_date() The Object[]s returned by this query contain a Longat index 0, a String at index 1, and a BigDecimal or MonetaryAmount at index 2. These are scalar values, not entity instances. Therefore, they aren t in any persistent state, like an entity instance would be. They aren t transactional and obviously aren t checked automatically for dirty state. We call this kind of query a scalar query.
Check Tomcat Web Hosting services for best quality webspace to host your web application.

CHAPTER 14 Querying with HQL and JPA QL Most of these HQL functions translate into a counterpart in SQL you ve probably used before. This translation table is customizable and extendable with an org.hibernate.Dialect. Check the source code of the dialect you re using for your database; you ll probably find many other SQL functions already registered there for immediate use in HQL. Keep in mind that every function that isn t included in the org.hibernate.Dialect superclass may not be portable to other database management systems! Another recent addition to the Hibernate API is the addSqlFunction() method on the Hibernate Configuration API: Configuration cfg = new Configuration(); cfg.addSqlFunction( “lpad”, new StandardSQLFunction(”lpad”, Hibernate.STRING) ); … cfg.buildSessionFactory(); This operation adds the SQL function lpad to HQL. See the Javadoc of Standard- SQLFunction and its subclasses for more information. HQL even tries to be smart when you call a function that wasn t registered for your SQL dialect: Any function that is called in the WHERE clause of an HQL statement, and that isn t known to Hibernate, is passed directly to the database, as an SQL function call. This works great if you don t care about database portability, but it requires that you keep your eyes open for nonportable functions if you do care. Finally, before we move on to the SELECT clause in HQL and JPA QL, let s see how results can be ordered. Ordering query results All query languages provide some mechanism for ordering query results. HQL and JPA QL provide an ORDER BY clause, similar to SQL. This query returns all users, ordered by username: from User u order by u.username You specify ascending and descending order using asc or desc: from User u order by u.username desc You may order by multiple properties: from User u order by u.lastname asc, u.firstname asc You now know how to write a FROM, WHERE, and ORDER BY clause. You know how to select the entities you want to retrieve instances of and the necessary expressions
If you are looking for affordable and reliable webhost to host and run your business application visit our ftp web hosting services.

Basic HQL and JPA QL queries Table 14.2 Standardized JPA QL functions Function Applicability UPPER(s), LOWER(s) String values; returns a string value CONCAT(s1, s2) String values; returns a string value SUBSTRING(s, offset, length) String values (offset starts at 1); returns a string value TRIM( [[BOTH|LEADING|TRAILING] char [FROM]] s) Trims spaces on BOTH sides of s if no char or other specification is given; returns a string value LENGTH(s) String value; returns a numeric value LOCATE(search, s, offset) Searches for position of ssin s starting at offset; returns a numeric value ABS(n), SQRT(n), MOD(dividend, divisor) Numeric values; returns an absolute of same type as input, square root as double, and the remainder of a division as an integer SIZE(c) Collection expressions; returns an integer, or 0 if empty All the standardized JPA QL functions may be used in the WHERE and HAVING clauses of a query (the latter you ll see soon). The native HQL is a bit more flexible. First, it offers additional portable functions, as shown in table 14.3. Table 14.3 Additional HQL functions Function Applicability BIT_LENGTH(s) Returns the number of bits in s CURRENT_DATE(), CURRENT_TIME(), CURRENT_TIMESTAMP() Returns the date and/or time of the database management system machine SECOND(d), MINUTE(d), HOUR(d), DAY(d), MONTH(d), YEAR(d) Extracts the time and date from a temporal argument CAST(t as Type) Casts a given type t to a Hibernate Type INDEX(joinedCollection) Returns the index of joined collection element MINELEMENT(c), MAXELEMENT(c), MININDEX(c), MAXINDEX(c), ELEMENTS(c), INDICES(c) Returns an element or index of indexed collections (maps, lists, arrays) Registered in org.hibernate.Dialect Extends HQL with other functions in a dialect
In case you need affordable webhost to host your website, our recommendation is ecommerce web host services.

CHAPTER 14 Querying with HQL and JPA QL Expressions with collections All expressions in the previous sections included only single-valued path expressions: user.email, bid.amount, and so on. You can also use path expressions that end in collections in the WHERE clause of a query, with the right operators. For example, let s assume you want to restrict your query result by the size of a collection: from Item i where i.bids is not empty This query returns all Item instances that have an element in their bids collection. You can also express that you require a particular element to be present in a collection: from Item i, Category c where i.id = ‘123′ and i member of c.items This query returns Item and Category instances usually you add a SELECT clause and project only one of the two entity types. It returns an Item instance with the primary key ‘123′ (a literal in single quotes) and all Category instances this Item instance is associated with. (Another trick you use here is the special .id path; this field always refers to the database identifier of an entity, no matter what the name of the identifier property is.) There are many other ways to work with collections in HQL and JPA QL. For example, you can use them in function calls. Calling functions An extremely powerful feature of HQL is the ability to call SQL functions in the WHERE clause. If your database supports user-defined functions (most do), you can put this to all sorts of uses, good or evil. For the moment, let s consider the usefulness of the standard ANSI SQL functions UPPER() and LOWER(). These can be used for case-insensitive searching: from User u where lower(u.email) = ‘foo@hibernate.org’ Another common expression is concatenation although SQL dialects are different here, HQL and JPA QL support a portable concat() function: from User user where concat(user.firstname, user.lastname) like ‘G% K%’ Also typical is an expression that requires the size of a collection: from Item i where size(i.bids) > 3 JPA QL standardizes the most common functions, as summarized in table 14.2.
Looking for affordable and reliable webhost to host and run your business application? Then look no more and go to servlet web hosting services.

Basic HQL and JPA QL queries The percentage symbol stands for any sequence of characters; the underscore can be used to wildcard a single character. You can define an escape character if you want a literal percentage or underscore: from User u where u.firstname not like ‘\%Foo%’ escape=’\’ This query returns all users with a firstname that starts with %Foo. HQL and JPA QL support arithmetic expressions: from Bid bid where ( bid.amount / 0.71 ) - 100.0 > 0.0 Logical operators (and parentheses for grouping) are used to combine expressions: from User user where user.firstname like ‘G%’ and user.lastname like ‘K%’ from User u where ( u.firstname like ‘G%’ and u.lastname like ‘K%’ ) or u.email in (’foo@hibernate.org’, ‘bar@hibernate.org’ ) You can see the precedence of operators in table 14.1, from top to bottom. The listed operators and their precedence are the same in HQL and JPA QL. The arithmetic operators, for example multiplication and addition, are self- explanatory. You ve already seen how binary comparison expressions have the same semantics as their SQL counterpart and how to group and combine them with logical operators. Let s discuss collection handling. Table 14.1 HQL and JPA QL operator precedence Operator Description . Navigation path expression operator +, -Unary positive or negative signing (all unsigned numeric values are considered positive) *, / Regular multiplication and division of numeric values +, -Regular addition and subtraction of numeric values =, <>, <, >, >=, <=, [NOT] BETWEEN, [NOT] LIKE, [NOT] IN, IS [NOT] NULL, Binary comparison operators with SQL semantics IS [NOT] EMPTY, [NOT] MEMBER [OF] Binary operators for collections in HQL and JPA QL NOT, AND, OR Logical operators for ordering of expression evaluation
From our experience, we can recommend PHP Web Hosting services, if you need affordable webhost to host and run your web application.