How to simulate NVL in HQL
The equivalent to the nvl command in HQL is the coalesce command. coalesce(a,b) will return a if a is not null, otherwise b. So you would want something on the lines of: from Table where col1 = coalesce(:par1, ‘asdf’)
hql
Hive Data Retrieval Queries: Difference between CLUSTER BY, ORDER BY, and SORT BY
In short, for your questions: Does CLUSTER BY guarantee a global order? No. DISTRIBUTE BY puts the same keys into same reducers but what about the adjacent keys? Depends on the hash function, which depends on your query. related question: How does the built-in Apache Hive hash function work and where can I find that … Read more
New object with HQL
I think that the section 15.6. The select clause covers what you’re trying to achieve: 15.6. The select clause … Queries can return multiple objects and/or properties as an array of type Object[]: select mother, offspr, mate.name from DomesticCat as mother inner join mother.mate as mate left outer join mother.kittens as offspr Or as a … Read more
Inserting data in one table using HQL in Hibernate
You don’t need to use hql to insert if the data is from another table. Simply get a reference to your entity, get a hold of a Hibernate session, and call save(). According to http://docs.jboss.org/hibernate/orm/4.0/devguide/en-US/html/ch04.html#d0e2116 Pseudo-syntax for INSERT statements INSERT INTO EntityName properties_list select_statement Only the INSERT INTO … SELECT … form is supported. You … Read more
HQL recursion, how do I do this?
You can’t do recursive queries with HQL. See this. And as stated there it is not even standard SQL. You have two options: write a vendor-specific recursive native SQL query make multiple queries. For example: // obtain the first node using your query while (currentNode.parent != null) { Query q = //create the query q.setParameter(“id”, … Read more
How to write a like query in HQL
Change your query to this: Query qry = session.createQuery(“From RegistrationBean as rb where rb.”+searchCriteria+” like ?”); qry.setString(0, “%”+searchField+”%”);
Is there a more efficient way of making pagination in Hibernate than executing select and count queries?
Baron Schwartz at MySQLPerformanceBlog.com authored a post about this. I wish there was a magic bullet for this problem, but there isn’t. Summary of the options he presented: On the first query, fetch and cache all the results. Don’t show all results. Don’t show the total count or the intermediate links to other pages. Show … Read more
No data type for node: org.hibernate.hql.internal.ast.tree.IdentNode HQL
SQL queries use column names while HQL queries use Class properties. You’re selecting artifact_id from Classification but the Classification class has no property named ‘artifact_id’. To fix it, use the class property in your HQL. SELECT artifactId FROM Classification
Date operations in HQL
Depending on your database, this can be trivially simple. HQL supports built-in vendor-specific features and functions, it also supports the ability to extend the dialect by registering new functions if they’re not already supported by HQL. Let’s say you’re using SQLServer (or Sybase). SQLServer has a function called ‘DATEADD’ that can do what you like … Read more
HQL – row identifier for pagination
this is one situation where hibernate shines: typical solution with hql query. int elementsPerBlock = 10; int page = 2; return getSession().createQuery(“from SomeItems order by id asc”) .setFirstResult(elementsPerBlock * (page-1) + 1 ) .setMaxResults(elementsPerBlock) .list(); hibernate will translate this to a pattern that is understood by the database according to its sql dialect. on oracle … Read more